op_bytes is gone and bytes are counted

A column that existed to be multiplied

When the I/O statistics view arrived, it came with a column whose only purpose was to be used in arithmetic. It reported the size of one operation, so that an operation count could be turned into a volume. In practice it always held the block size, on every row, on every cluster, which made it a constant dressed as data.

That was defensible while an operation really was one block. It stopped being defensible when the server learned to fetch several adjacent blocks in a single request, because the column went on reporting the block size and the multiplication went on producing a number that was too small by however much combining had happened. Nothing announced that. The column was still there, still eight thousand one hundred and ninety-two, still perfectly usable in a query that returned a plausible answer.

Version 18 deleted it and added three columns that report volume directly, one for reads, one for writes and one for the operations that grow a relation. This is the rare case where a breaking change is the kind operators should want: the old query stops rather than lying.

The multiplication, on the last version that allows it

The fixture is a hundred and twenty thousand short rows written into a new table, which produces relation extensions rather than reads, because extensions are where the difference is starkest.

CREATE TABLE invoice_lines (line_id bigint, description text);
INSERT INTO invoice_lines SELECT g, md5(g::text) FROM generate_series(1, 120000) AS g;
CHECKPOINT;

CREATE TABLE
INSERT 0 120000
CHECKPOINT

On 17, the question and the arithmetic that was the standard way to answer it.

SELECT backend_type, context, op_bytes, extends,
       pg_size_pretty(extends * op_bytes) AS volume_by_multiplication
FROM pg_stat_io
WHERE extends > 0 AND object = 'relation'
ORDER BY extends DESC;

    backend_type    |  context  | op_bytes | extends | volume_by_multiplication 
--------------------+-----------+----------+---------+--------------------------
 client backend     | normal    |     8192 |    1127 | 9016 kB
 standalone backend | normal    |     8192 |     665 | 5320 kB
 standalone backend | bulkwrite |     8192 |       8 | 64 kB
(3 rows)

Three rows, one constant repeated three times, and a volume computed from it. The first row is the load itself. The other two belong to the process that built the cluster before it started accepting connections, which is a detail of how these fixtures are made rather than anything about the workload.

The same question on 18

SELECT backend_type, context, op_bytes, extends,
       pg_size_pretty(extends * op_bytes) AS volume_by_multiplication
FROM pg_stat_io
WHERE extends > 0 AND object = 'relation'
ORDER BY extends DESC;

ERROR:  column "op_bytes" does not exist
LINE 1: SELECT backend_type, context, op_bytes, extends,
                                      ^

The parser stops at the first missing name and points at it. This is the good failure: it happens the first time the query runs on the new version, it happens in the foreground of whatever ran it, and it names the thing to fix. A collector that runs this query logs an error and drops a metric, which is noticeable; the alternative, which several other changes in this release take, is to keep returning a number nobody can tell is wrong.

The replacement reports the bytes rather than implying them.

SELECT backend_type, context, extends,
       pg_size_pretty(extend_bytes) AS volume_reported
FROM pg_stat_io
WHERE extends > 0 AND object = 'relation'
ORDER BY extends DESC;

    backend_type    |  context  | extends | volume_reported 
--------------------+-----------+---------+-----------------
 client backend     | normal    |    1123 | 9000 kB
 standalone backend | normal    |     598 | 5464 kB
 standalone backend | bulkwrite |       1 | 64 kB
(3 rows)

Compare the two tables row by row and the first two are boringly similar, which is the point: for ordinary single-block extensions the new number agrees with the old arithmetic, and anyone who was doing the multiplication was getting the right answer.

Then look at the last row. On 17, eight operations, sixty-four kilobytes. On 18, one operation, sixty-four kilobytes. Identical volume, one eighth the operations, because the bulk-write path now grows a relation by several blocks at once. Run the old multiplication against that row on 18 and you would report eight kilobytes for work that moved sixty-four. That is a factor of eight understatement sitting in the one context where the server is doing the largest, most sequential writes it ever does, which is to say precisely the work an operator most wants to see.

The type changed too, and that is not in the release note

The three new columns are not counters of the same kind as the ones beside them.

SELECT a.attname, format_type(a.atttypid, NULL) AS column_type
FROM pg_attribute a
WHERE a.attrelid = 'pg_stat_io'::regclass
  AND a.attname IN ('reads', 'read_bytes', 'writes', 'write_bytes', 'extends', 'extend_bytes', 'op_bytes')
ORDER BY a.attname;

 attname  | column_type 
----------+-------------
 extends  | bigint
 op_bytes | bigint
 reads    | bigint
 writes   | bigint
(4 rows)

And on 18:

   attname    | column_type 
--------------+-------------
 extend_bytes | numeric
 extends      | bigint
 read_bytes   | numeric
 reads        | bigint
 write_bytes  | numeric
 writes       | bigint
(6 rows)

The operation counts are integers. The byte counts are arbitrary-precision numerics, because a busy cluster can move more bytes in its lifetime than a sixty-four-bit integer holds and the project has been bitten by that before in the write-ahead log counters.

That choice is right and it has a consequence nobody writing a collector expects. Most client libraries hand a numeric back as a string or a decimal type rather than a native integer, because they cannot promise the value fits. A metrics exporter that reads the column into an integer variable, or a script that compares it to a number without converting, behaves differently from how it behaved with the operation counts beside it. It is a small thing and it is the sort of small thing that produces a metric which is correct on a test cluster and truncated on a production one, so it is worth checking what your own collection layer does with a numeric before trusting the first graph it draws.

What to change, and in what order

• Find every occurrence of the deleted column. It appears in exporter query files, in dashboard expressions, and in whatever script somebody wrote once to answer a capacity question and left in a repository. The upgrade will find them for you, loudly, which is the one respect in which this change is easier than the rest of the release.
• Replace the multiplication with the byte column rather than with an assumption. Writing the block size as a literal in place of the deleted column reproduces the old behaviour, including the understatement, and will look like it worked.
• Check the type handling once, on a cluster that has been up long enough for the numbers to be large. A truncation at a boundary you have not reached yet is not a bug you will find on a fresh test instance.

Where the understatement is worst

The measurement above puts the error in one place rather than spreading it evenly, and knowing which place saves a great deal of time.

Ordinary buffer-pool activity, which is what the normal context covers, still moves one block per operation most of the time. That is why the first two rows of the two outputs agree so closely. Anyone whose dashboards cover only that context was getting a correct number from the old multiplication and will get the same number from the new columns.

The bulk contexts are where it falls apart, and they are the ones that matter for capacity. Bulk writes are what a large load, a table rewrite, a restore or an index build does, and the measurement above shows a single operation moving eight blocks in that context. Bulk reads are what a sequential scan does and combine similarly. So the error is concentrated in exactly the operations that move the most data, which means a cluster’s largest I/O events were the ones being under-reported and the quiet steady-state ones were being reported correctly. A capacity model built on the old arithmetic is therefore not uniformly wrong by a percentage; it is right most of the time and badly wrong during the events that size the storage.

Write amplification is the calculation this ruins most thoroughly. The ratio of bytes written by the database to bytes of data changed is the number that decides whether a storage layer is a good fit, and both halves of it used to be assembled from operation counts. On 18 the database half can be read directly, which makes the ratio worth computing again for anyone who gave up on it.

One more asymmetry is worth recording. The extend counters, which are the ones shown above, describe the server growing a file, and they are the closest thing PostgreSQL has to a measure of how fast data is arriving. Watching extend bytes per unit of business work is a better growth signal than watching relation sizes, because it is a rate rather than a level and it responds the moment a load pattern changes rather than at the next time somebody looks at a size.

The one place the old query still looks right

A last trap, and it is the reason to grep rather than to wait for errors. The deleted column is gone from the statistics view, but the block size it reported is still available from the server as a run-time parameter, and a query that reads it from there instead compiles and runs on 18 perfectly well.

That is the repair somebody reaches for when the error above appears at eight in the morning: the column is missing, the value was always the block size, so read the block size from the settings and multiply by that. It restores the query, it restores the dashboard, and it restores the understatement described in the section above, permanently and invisibly, because now there is no column left to be missing and nothing will ever error again.

The same applies to the literal. Writing the number into the query in place of the column is the same mistake with fewer steps.

So the useful way to think about this change is not that a column was removed but that a piece of arithmetic stopped being valid. The removal is how the project told you. Reinstating the arithmetic by another route is the one response that undoes the telling, and it is the response that looks most like a fix.

What it costs to collect

Nothing changes in what the server does. The volumes were already being tracked internally, which is how the new columns can report them without a new setting, and reading the view is the same handful of rows assembled on request that it always was.

Timing remains the separately expensive thing, unchanged from earlier versions: the general I/O timing setting asks the operating system for a clock reading around each operation, it is off by default, and it can be switched on and off without a restart. It gates the time columns only. The byte columns are free whether timing is on or not, which makes them the first thing to graph on a cluster where timing has been judged too expensive to enable.