Reading a backend's subtransaction cache

A performance cliff with no instrument in front of it

Some PostgreSQL slowdowns are gradual and some are a cliff. The subtransaction one is a cliff, and for years it was a cliff you could only identify by elimination.

The shape is this. Each backend keeps a small fixed-size array of the subtransaction identifiers it currently holds open, which is what savepoints and exception blocks in stored procedures create. While the identifiers fit in that array, other backends can decide whether a row version is visible to them by looking at shared memory. When a backend holds more than the array can keep, the array is marked as having overflowed, and every other session that needs to judge visibility against that backend has to go to the on-disk subtransaction map instead. That map has a cache in front of it, the cache is not large, and a workload with many overflowed backends turns a memory lookup into a page fetch on a hot path.

The symptom is a cluster that is suddenly slow at everything, with no single slow query, no lock waits worth the name, and a wait-event profile that points at a cache nobody has heard of. The cause is an application that opens savepoints in a loop, or an exception handler inside a loop, which is the same thing wearing different clothes.

PostgreSQL 16 added a function that reports what a backend is holding. The Monitoring section of the PostgreSQL 16 release notes names it in one line, alongside a second change in the same area that matters for using it.

On 15, there is nothing to call

SELECT pg_stat_get_backend_subxact(1);

ERROR:  function pg_stat_get_backend_subxact(integer) does not exist
LINE 1: SELECT pg_stat_get_backend_subxact(1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

There was no supported way to ask. The overflow condition was visible only in its effects: a rise in reads against the subtransaction cache in pg_stat_slru, and a wait-event profile with that cache in it. Both are downstream of the cause and neither names the session responsible, so the investigation ran through the application rather than through the database.

Addressing a backend on 16

The function takes a backend identifier rather than a process id, which is the second change worth knowing about. Backend identifiers used to be positions in an array that could shift while a session was alive, so a monitoring query that captured one and came back later might be asking about a different session. From 16 the identifier belongs to the session for its lifetime, which makes the two-step join below safe rather than merely likely to work.

CREATE TABLE savepoint_probe (n integer);
CREATE VIEW my_subxact AS
SELECT x.subxact_count, x.subxact_overflowed
FROM pg_stat_get_backend_idset() AS b(backend_id),
     LATERAL pg_stat_get_backend_pid(b.backend_id) AS s(pid),
     LATERAL pg_stat_get_backend_subxact(b.backend_id) AS x
WHERE s.pid = pg_backend_pid();

CREATE TABLE
CREATE VIEW

The view above resolves the current session’s own identifier by matching on process id. In production you would drop the final predicate and keep the process id in the output, so every session is listed and the noisy one can be named.

SELECT * FROM my_subxact;

 subxact_count | subxact_overflowed 
---------------+--------------------
             0 | f
(1 row)

Outside a transaction there is nothing held, which is the answer you want from a healthy idle session.

The reading that does not move

Now open a transaction, create ten subtransactions that each write something, and ask.

BEGIN;
\o /dev/null
SELECT format('SAVEPOINT s%s; INSERT INTO savepoint_probe VALUES (%s);', g, g)
FROM generate_series(1, 10) AS g \gexec
\o
SELECT * FROM my_subxact;
\o /dev/null
SELECT format('SAVEPOINT t%s; INSERT INTO savepoint_probe VALUES (%s);', g, g)
FROM generate_series(1, 60) AS g \gexec
\o
SELECT * FROM my_subxact;
SELECT pg_stat_clear_snapshot();
SELECT * FROM my_subxact;
COMMIT;

BEGIN
 subxact_count | subxact_overflowed 
---------------+--------------------
            10 | f
(1 row)

 subxact_count | subxact_overflowed 
---------------+--------------------
            10 | f
(1 row)

 pg_stat_clear_snapshot 
------------------------
 
(1 row)

 subxact_count | subxact_overflowed 
---------------+--------------------
            64 | t
(1 row)

COMMIT

Three readings in one transaction, and the middle one is the trap. Sixty more subtransactions were created between the first reading and the second, and the second reading is identical to the first. Nothing is broken and nothing is lagging: the per-backend status information is snapshotted once per transaction and held for the rest of it, so the second call returned the same snapshot the first call materialised.

The third reading, after explicitly discarding the snapshot, shows the true state. The count has stopped at the size of the array and the overflow flag has turned true, which is the condition the whole page is about. Seventy subtransactions were opened; the array holds sixty-four of them; the sixty-fifth is what tipped this backend into the expensive regime.

A savepoint that writes nothing is free here, incidentally. A subtransaction only consumes one of those slots once it has been assigned a transaction identifier, which happens on its first write. An application that opens a savepoint, runs a read, and releases it will not appear in this counter at all.

What this means for a collector

The freeze is not a quirk of this function. It applies to every per-backend surface, pg_stat_activity included, and it is the reason a query watched inside an open transaction appears to have stopped updating. For a monitoring session the consequence is simple and easy to get wrong: a collector that opens a transaction, reads several views and commits is reading one instant, which is usually what you want. A collector that holds a long-lived transaction and polls inside it is reading the same instant over and over.

Two habits follow.

• Poll from short transactions, or call the clearing function between reads. Autocommit is enough on its own, because each statement is its own transaction.
• Do not compare a per-backend reading against a cumulative counter fetched in the same transaction and expect them to be equally fresh. They come from different mechanisms with different snapshot rules.

What to watch, and what to do about it

The signal worth alerting on is not the count. It is the number of sessions whose overflow flag is true, sampled often enough to catch a burst, because one overflowed backend is harmless and a few dozen concurrently overflowed backends is the cliff. Sum the flag across sessions, graph it beside the subtransaction cache’s read rate, and the correlation either exists on your workload or it does not.

When it does, the fix is almost never in the database. It is an application that wraps each row of a batch in a savepoint so that one bad row does not abort the batch, or an exception handler inside a loop in a stored procedure, which the server implements as a subtransaction whether or not an exception is ever raised. The remedy is to move the granularity up: one savepoint per chunk rather than per row, or validation before the write rather than recovery after it. Both are application changes, which is why finding the session responsible matters so much more here than in most performance work.

There is a second-order effect worth naming. A long-running transaction that has overflowed is worse than a short one, because every visibility check made against it while it lives pays the cost. The two conditions are usually found together, and a session list sorted by transaction age with the overflow flag beside it is the single most useful view this function supports. That combination also sits behind the cache whose rows were renamed a release later, so a fleet crossing both versions needs to read one signal by a new name and the other through a new function.

Where the cost actually lands

It is worth being precise about who pays, because the intuition that the session with the savepoints is the slow one is wrong and sends people to the wrong place.

The backend holding many subtransactions is not especially penalised. What it has done is make itself expensive to reason about. Every other session that encounters a row version written by that backend has to decide whether the subtransaction that wrote it committed, and while the identifiers fit in shared memory that decision is a scan of a small array. Once the array has overflowed, the answer is not in shared memory, and the deciding session goes to the on-disk map for it.

That is why the symptom is cluster-wide. The sessions that slow down are the ones reading rows the noisy session wrote, and they may have nothing to do with the code that opened the savepoints. A read-only reporting query can be the thing that gets slower, on a table it merely selects from, because of a writer three services away.

The second-order effect is the cache in front of that map. It is small, it is shared, and it is sized for a workload where lookups are rare. Turn them into a hot path and the cache misses, which turns a memory lookup into a page read from a file the server otherwise barely touches. This is why the classic presentation of this problem includes disk reads that no query plan accounts for.

Three things follow for an investigation.

• The session to find is the one with the flag set, not the one that is slow. They are usually different sessions.
• Transaction age multiplies the damage, because every visibility decision made while the transaction lives pays the cost. A short overflowed transaction is nearly harmless.
• The fix is a change to how the application groups its work, so the finding has to be specific enough to hand to whoever owns that code.

What it costs

The function reads shared memory that the server maintains regardless, so there is no collection cost and nothing to switch on. The only cost is the one every per-backend query carries: enumerating every session on a cluster with a very large connection count is proportional to that count, so poll it on the interval you would poll the activity view and no faster.