The temp reads that were really extends

The upgrade that changes a number without changing a column

Most monitoring breakage at a major upgrade is loud. A column is renamed or removed, a query fails, somebody reads the error and fixes it. The expensive kind is quieter: the query still runs, the column still exists, and the number it returns now means something slightly different from what it meant last week.

PostgreSQL 16 contains one of those, and it sits underneath the most widely graphed derived metric in the ecosystem. The per-database counters that feed the cache hit ratio changed what they count for temporary relations. The Monitoring section of the PostgreSQL 16 release notes gives it one line about correcting the I/O accounting for temporary relation writes, which is accurate and considerably understates what an operator sees.

The workload

A temporary table larger than the session’s temporary buffer pool, so that the server has to put pages on disk rather than keeping them all in memory. Nothing exotic: this is what a reporting query does when it materialises an intermediate result.

SET temp_buffers = '1MB';
SELECT pg_stat_reset();
SELECT pg_sleep(1);
CREATE TEMP TABLE scratch_rows (id integer, body text);
INSERT INTO scratch_rows SELECT g, repeat('z', 100) FROM generate_series(1, 40000) AS g;
SELECT count(*) FROM scratch_rows;
SELECT pg_sleep(2);
SELECT blks_read, blks_hit,
       round(blk_read_time::numeric, 1)  AS read_ms,
       round(blk_write_time::numeric, 1) AS write_ms
FROM pg_stat_database WHERE datname = current_database();

That block ran on both servers. On 15 the counters look like this:

SET
 pg_stat_reset 
---------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

CREATE TABLE
INSERT 0 40000
 count 
-------
 40000
(1 row)

 pg_sleep 
----------
 
(1 row)

 blks_read | blks_hit | read_ms | write_ms 
-----------+----------+---------+----------
      1382 |    41881 |     5.8 |      0.0
(1 row)

And on 16, for exactly the same work:

SET
 pg_stat_reset 
---------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

CREATE TABLE
INSERT 0 40000
 count 
-------
 40000
(1 row)

 pg_sleep 
----------
 
(1 row)

 blks_read | blks_hit | read_ms | write_ms 
-----------+----------+---------+----------
       690 |    41891 |     2.3 |     30.3
(1 row)

Two differences, and neither of them is a column that moved.

The first is the read count, which is twice as large on 15 for identical work. The block did not read anything from storage in any ordinary sense; it created a temporary table and filled it. The surplus is the work of adding new blocks to the end of that table, and on 15 that work was counted as reading.

The second is where the time went. On 15 the write column is zero, so a session pushing temporary pages out of memory contributed nothing at all to the database’s write timing. On 16 a write time has appeared, and the read time has fallen with it.

What the new view says the work actually was

PostgreSQL 16 also introduced a view that reports I/O by process type, object type and context, and it is worth asking it what those several hundred operations were.

SELECT backend_type, object, context, reads, writes, extends
FROM pg_stat_io
WHERE object = 'temp relation' AND (reads > 0 OR writes > 0 OR extends > 0);

  backend_type  |    object     | context | reads | writes | extends 
----------------+---------------+---------+-------+--------+---------
 client backend | temp relation | normal  |   690 |   1253 |     693
(1 row)

Three operations, counted separately, and the third is the one the older accounting had nowhere to put. An extend is the act of adding a new block to the end of a relation, which is neither a read nor a write of an existing page. Its count here is almost exactly the surplus between the two read figures above, which is the confirmation that what 15 called a read was an extend wearing the wrong label.

The writes are the other half of the correction. They happened on 15 too, because the temporary buffer pool was smaller than the table and pages had to go somewhere, and on that version they contributed no time to any column anybody graphs. The work is now reported as what it is, in a view with a column for each kind, and the view that introduced those columns is what made the correction possible at all.

Why this lands on the hit ratio

The familiar cache hit ratio is hits divided by hits plus reads, taken from these two columns. On 15, every temporary relation extend added one to the denominator and nothing to the numerator, so a session doing heavy temporary work pushed the ratio down. On 16 the extends are gone from both terms and only genuine reads remain.

SELECT round(blks_hit::numeric / nullif(blks_hit + blks_read, 0), 4) AS cache_hit_ratio
FROM pg_stat_database WHERE datname = current_database();

On 15 the same workload yields:

 cache_hit_ratio 
-----------------
          0.9684
(1 row)

On 16:

 cache_hit_ratio 
-----------------
          0.9840
(1 row)

The query is identical, it succeeds on both, and the answer is different. Nothing in the output flags that the definition moved. A dashboard carrying a year of history shows a step at the upgrade, and the step is in the flattering direction, which is the direction nobody investigates.

This is the reason the page carries a silent-break badge rather than a clean one. Nothing errors. A threshold alert on the ratio stops firing, or starts firing less, on clusters that do a lot of temporary work, and the change has nothing to do with how well the cache is performing.

What to do about it

The honest answer is that the cache hit ratio was always a poor alert and this is a good moment to stop treating it as one. It cannot see the operating system page cache, so a block the kernel served still counts as a read and the ratio understates the real caching. It averages every kind of work the database does, so one large sequential scan drags it down and looks like a regression. And now it has a discontinuity at a major version boundary.

If you keep it, three things make it defensible.

• Mark the upgrade on the graph. A stated break in a series is worth more than a smooth line that is lying.
• Do not carry a threshold across the boundary. Whatever number represented “normal” on 15 was measured against a denominator that included work 16 does not count.
• Read temporary activity separately. The temporary file counters in the same view, and the temporary rows in the new I/O view, say directly what the ratio was only hinting at.

If you are replacing it, the replacement on 16 is not another ratio. It is read counts split by process type and context, which distinguishes a bulk read from cache pressure, and read time per read once timing is enabled, which is the latency signal the ratio was being used as a proxy for.

Which workloads move the most

The size of the discontinuity depends entirely on how much temporary relation work a database does, and that varies enormously between clusters that otherwise look alike.

The heaviest users are rarely the obvious ones. A reporting query that materialises an intermediate result into a temporary table is the textbook case, and on most fleets it is not the biggest contributor. The bigger contributors tend to be stored procedures that use a temporary table as a scratchpad and are called thousands of times an hour, and extract jobs that build a staging table, work on it, and drop it. Both create and fill relations constantly, which is exactly the operation whose accounting changed.

Two things separate a cluster that will see a large step from one that will not.

The first is the temporary buffer setting. It is per session and it is allocated lazily, so a session that touches a temporary table gets its own pool and keeps it for the life of the connection. If that pool is large enough to hold the working set, pages never leave memory and the write side of this accounting stays near zero. If it is not, every page goes out and comes back.

The second is connection lifetime. Behind a pooler with long-lived server connections, that per-session pool is allocated once and reused, which is efficient but also means the memory is held by every pooled connection that has ever touched a temporary table. On a cluster with hundreds of pooled connections and a generous setting, that is a quantity of memory nobody accounted for.

Neither of those changed in this release. What changed is that on 16 the counters let you see which of the two situations you are in, because write time against temporary relations is a direct measurement of pages leaving memory. On 15 that signal did not exist, and the only evidence was the read count, which as this page has shown was not counting reads.

The narrower lesson

A column that keeps its name across an upgrade is not a promise that it counts the same events. This one changed because the old accounting was wrong, which makes the change an improvement and makes the discontinuity no less real. The inventory people run before an upgrade almost always asks whether a column still exists. It rarely asks whether it still means the same thing, and there is no catalog query that answers that.

What does answer it is a container of the target version and the workload that matters to you. The block at the top of this page took a few seconds to run on both servers and it produced a difference nobody would have predicted from reading the release note. That is the argument for running the comparison rather than reading about it, on whichever handful of numbers your alerts actually depend on.

What it costs

Nothing new. The counters are maintained regardless, the corrected accounting costs no more than the incorrect accounting did, and the I/O view is a handful of rows built on request. Timing is the only part with a real cost, and it is off by default; on 16 it feeds both the per-database columns above and the per-context rows in the new view, so switching it on buys considerably more than it did on 15.