Subtransactions

This one is worth reading even if you have never typed SAVEPOINT in your life, because your framework almost certainly has.

What creates a subtransaction

Three things, and only the first is obvious:

1. SAVEPOINT name - an explicit savepoint.
2. A BEGIN ... EXCEPTION WHEN ... END block in PL/pgSQL. Every exception block is a subtransaction, created every time the block is entered. A trigger or function that catches unique_violation in a loop creates one per iteration.
3. A nested transaction in an ORM. Most ORMs implement transaction() inside an existing transaction() as a savepoint - Prisma, Drizzle, ActiveRecord, SQLAlchemy and Django all do.

None of these are mistakes. They only become a problem in quantity, inside a single transaction.

The cliff

Every Postgres backend keeps a small cache of the subtransaction IDs belonging to its current transaction. The cache holds 64. Up to 64 subtransactions, everything is fast.

At 65, the cache overflows, and visibility checks for that transaction fall back to an on-disk structure called pg_subtrans. That part is expected. What is not obvious is the blast radius:

While any one backend has an overflowed subtransaction cache, every other query in the database starts consulting pg_subtrans too - once per row it examines.

One transaction with 65 savepoints makes unrelated queries, on unrelated tables, run by unrelated connections, do extra work per row. The cost is a lock acquisition each time and, once the structure no longer fits in memory, a disk read.

The shape of the incident is distinctive: throughput collapses by an order of magnitude, stays collapsed for exactly as long as the offending transaction is open, and then returns to normal the instant it commits. Nothing errors. No slow query stands out, because every query got slower by the same mechanism.

Checking whether it is you

Is anything overflowed right now:

SELECT pg_stat_get_backend_pid(id) AS pid, s.*
FROM pg_stat_get_backend_idset() id
JOIN LATERAL pg_stat_get_backend_subxact(id) AS s ON TRUE
WHERE s.subxact_count > 0
ORDER BY s.subxact_count DESC;

subxact_count is how many subtransactions that backend is holding (64 is the ceiling before overflow), and subxact_overflowed is the flag that matters.

Is it costing disk reads:

SELECT * FROM pg_stat_slru WHERE name IN ('subtransaction', 'Subtrans');

A blks_read that climbs while you watch means the fallback is missing memory and going to disk - the expensive version. blks_hit climbing on its own is the cheaper version, but it is still per-row work every query is paying.

Fixing it

The goal is fewer subtransactions per transaction, not fewer transactions.

Move exception handling out of the loop. This creates one subtransaction per row:

-- One subtransaction per iteration. 1,000 rows overflows sixteen times over.
FOR r IN SELECT * FROM staging LOOP
  BEGIN
    INSERT INTO target VALUES (r.a, r.b);
  EXCEPTION WHEN unique_violation THEN
    NULL;
  END;
END LOOP;

This creates none:

INSERT INTO target SELECT a, b FROM staging
ON CONFLICT DO NOTHING;

ON CONFLICT is not just tidier here, it is a different order of magnitude. The same applies to MERGE and to INSERT ... ON CONFLICT DO UPDATE.

Flatten nested ORM transactions. A helper that opens a transaction, called from inside another transaction, produces a savepoint you never asked for. If a batch loop calls such a helper per item, you have a savepoint per item. Either pass the existing transaction down, or batch the work so the loop is inside one statement.

Split long batches. If you genuinely need per-item error isolation, commit every N items rather than holding thousands of savepoints in one transaction. The cache resets with the transaction.

The advisory

CapyDB raises a Nested transaction overhead advisory when a backend's subtransaction cache has been overflowed for a sustained period. It is a dashboard advisory rather than an email, because it is diagnostic: it tells you what to look for the next time somebody says the database got slow for no reason.

If you see it, the two queries above will tell you which backend and whether it is reaching disk.