A Parade of Horribles, in Serial Order

Sooner or later, someone on the team reads about write skew. It is an unsettling read: two transactions, each correct on its own, each looking at data the other is about to change, both committing, and the database ending up in a state that no ordering of the two could have produced. READ COMMITTED allows it. REPEATABLE READ allows it. The cure is right there in the documentation, and it is one line:

1
ALTER DATABASE app SET default_transaction_isolation = 'serializable';

Every anomaly gone, no application changes, home in time for dinner.

Do not do this.

First, the case for the defense

PostgreSQL’s SERIALIZABLE is a good piece of engineering, and it deserves a fair hearing before the parade starts. Since 9.1 it has been implemented as Serializable Snapshot Isolation (SSI). It runs your transaction exactly the way REPEATABLE READ would, adds no blocking, and keeps track of what each transaction has read using predicate locks. (They show up in pg_locks as SIReadLock. They do not block anything; they are closer to flags than to locks.) When it sees a pattern of read/write dependencies that could produce an anomaly, it cancels one of the transactions with SQLSTATE 40001, and the application runs it again.

Dan Ports and Kevin Grittner’s paper on the implementation measured a 5% slowdown against snapshot isolation on an in-memory transaction-processing benchmark, a CPU overhead of 10 to 20% on a simple microbenchmark, and no measurable difference on a disk-bound one. The documentation says serializable transactions are “the best performance choice for some environments,” and that is true.

The environments in question are the ones built for it. Every transaction is short. Every transaction can be thrown away and rerun from the top by a generic retry wrapper. Read-only work is declared READ ONLY. There is a connection pool, and nobody wanders off with a transaction open. The documentation presents these as performance suggestions. They are entry requirements.

An application written against READ COMMITTED that has the default changed underneath it meets none of them. Here is what happens to that application.

(The numbers below are from PostgreSQL 18.6 on a small two-core test machine with fsync off: pgbench at scale 20, eight clients, unless I say otherwise. That is a CPU-bound setup, which is the least flattering kind for SSI. Read them as ratios.)

Anything can fail, including COMMIT

Under READ COMMITTED, a transaction whose statements all succeed will (deferred constraints aside) commit. Under SERIALIZABLE, this can come back from any statement:

1
ERROR:  could not serialize access due to read/write dependencies among transactions
2
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
3
HINT:  The transaction might succeed if retried.

That one arrived on the COMMIT. Every statement before it had succeeded. If the application sent the confirmation email somewhere in the middle, the email is now the application’s problem.

Read-only transactions are not exempt. I ran a variation on the Deposit Report example from the PostgreSQL wiki, and a READ ONLY transaction consisting of two SELECT statements was canceled on the second one. The documentation is explicit that nothing a serializable transaction reads should be trusted until it commits (READ ONLY DEFERRABLE transactions excepted).

Savepoints do not help either. I rolled back to a savepoint taken before a failing INSERT and ran the INSERT again. It failed again, with the same error. The conflict comes from what the transaction has already read, and rolling back to a savepoint does not unread anything. The only retry that works is the entire transaction, starting at BEGIN. A framework that wraps each operation in a savepoint and retries at that level runs straight back into the same conflict.

Some of these cancellations are false positives, and that is by design. SSI cancels on the possibility of an anomaly, because tracking enough to prove an actual one would cost more than the occasional unnecessary retry. How many false positives you get depends on how coarse the predicate locks are.

The query plan now sets your error rate

For an index scan, predicate locks start at the row level in the table and at the page level in the index. When a transaction holds more than two row locks on one page, they are promoted to a lock on the page. When it holds too many locks on one table (with default settings, on the thirty-second), they are promoted to a lock on the whole relation. A sequential scan skips the preliminaries and takes the relation lock up front. (The post on max_pred_locks_per_page and max_pred_locks_per_relation has the full tour. The numbers here are from fresh runs on a slightly different workload, so they will not match that post digit for digit.)

A relation-level predicate lock means “this transaction read everything in this table.” Every concurrent write to the table, to any row, is now a read/write dependency with that transaction.

I ran a transaction that reads 40 rows scattered around a two-million-row table by primary key, then updates one row somewhere else. The transactions almost never touched each other’s rows.

Forty rows is past the threshold, so every one of those transactions held a lock on the whole table, and every update conflicted with all of them.

It also means the planner has a say in your error rate. A statistics change that turns an index scan into a sequential scan used to cost you some milliseconds. Now it converts a row-level predicate lock into a table-level one. The documentation’s advice on this point is to adjust random_page_cost and cpu_tuple_cost to discourage sequential scans, which gives you some idea of how far we have travelled from “no application changes.”

It costs something even when nothing fails

Raise the promotion threshold (max_pred_locks_per_relation = 1000) and the failures in that test go to zero. Throughput comes back to between 3,700 and 4,000 TPS, against roughly 5,000 at READ COMMITTED. That is 20 to 25 percent for the bookkeeping on about 80 predicate locks per transaction (40 rows, plus the 40 index pages that led to them).

To be fair to SSI, stock pgbench tells a milder story: 9,231 TPS at READ COMMITTED, 6,879 at REPEATABLE READ, 6,283 at SERIALIZABLE. Most of that drop belongs to REPEATABLE READ, which refuses to update a row that changed after the transaction’s snapshot was taken (about 22% of transactions were retried at both levels). SERIALIZABLE came in a further 9% below that. How much you pay depends on how much each transaction reads.

One idle session takes everyone down

A predicate lock does not go away when its transaction commits. It has to stay until the serializable transactions that overlapped with it have finished, because any of them might still turn out to conflict.

So I opened a session, ran BEGIN and one SELECT, and left it there. Then I started the 40-row workload, with the promotion threshold raised as above. It completed 131 transactions. A tenth of a second in, every client got this:

1
ERROR:  out of shared memory
2
HINT:  You might need to increase "max_pred_locks_per_transaction".

The idle session was pinning every predicate lock taken since its snapshot, and the shared lock table filled up. Note the SQLSTATE: it is 53200, not 40001. A retry wrapper that looks for serialization failures does not catch it. (pgbench, which I had told to retry 40001, gave up on all eight clients.)

The idle session does not even have to be a serializable one. Cleanup waits on the oldest snapshot among the active serializable transactions, and any open transaction that holds a transaction ID keeps every later snapshot from moving past it. I repeated the test with an idle READ COMMITTED session that had run a single UPDATE, and all eight clients were dead in two seconds. (An idle session that had only run a SELECT, at READ COMMITTED or REPEATABLE READ, did no harm.)

With the promotion threshold at its default, the table does not fill, because nearly everything is promoted to a relation lock first. You get the 70% failure rate from two sections back instead. Those are the two choices.

On a READ COMMITTED system, a session left idle in a transaction is a nuisance that causes bloat. On a SERIALIZABLE system, it is a countdown.

An idle serializable session also stops anything that asks for a SERIALIZABLE READ ONLY DEFERRABLE snapshot, pg_dump --serializable-deferrable included. That request waits (wait event SafeSnapshot) for as long as the idle session lives.

The replicas stop answering

SERIALIZABLE is not available on a hot standby. ALTER DATABASE ... SET is stored in the catalogs, and the catalogs are replicated. So a moment after that one-line fix runs on the primary, this is what every new connection to a replica gets:

1
$ psql -c 'SELECT 1'
2
ERROR:  cannot use serializable mode in a hot standby
3
DETAIL:  "default_transaction_isolation" is set to "serializable".
4
HINT:  You can use "SET default_transaction_isolation = 'repeatable read'" to change the default.

That is the first query of every new session: the load balancer’s health check, the reporting tool, pg_dump. You cannot undo the setting on the replica, because the replica is read-only. Each client has to issue the SET in the hint (it works), or connect with PGOPTIONS that override it.

The README says to do it anyway

There is one serious argument for the one-line fix, and it comes from the people who built the feature. SSI only watches serializable transactions. A READ COMMITTED transaction writing to the same tables (a cron job, a migration, a fix someone types into psql at 2 a.m.) is invisible to it, and the guarantee is void. From which the SSI README in the source tree concludes:

If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.

The first half of that sentence is a fact about the mechanism. The second half is advice, and the advice is wrong.

Start with its age. That sentence arrived with the commit that added SSI, on February 7, 2011, seven months before PostgreSQL 9.1 was released, and it has not been touched since. The evidence the README had to offer on cost was “the two known prototype implementations,” which were built on Berkeley DB and InnoDB. It was a reasonable thing to write about a feature that nobody had run in production. It does see some of the costs coming: it says to declare read-only transactions as such, and to use a connection pool. What a forgotten session does to the lock table is not in it. Neither is the replica that stops answering.

Next, a default does not get you “all transactions.” default_transaction_isolation is an ordinary user-settable parameter. Any session, with no privileges at all, can step out from under it with a SET, with BEGIN ISOLATION LEVEL READ COMMITTED, or with an option in its connection string. The global default does not make every transaction serializable. It makes the ones that never asked for anything else serializable, and the guarantee needs all of them.

And “all transactions” is more than the mechanism requires. An anomaly needs transactions that touch the same data. The rule that matters is narrower: every transaction that reads or writes the tables behind the invariant has to be serializable. That is a statement about a set of tables, and it can be enforced with tools that enforce things. Give those tables to a role whose code was written for SSI, set the isolation level on that role (or, better, in that code), and use privileges to keep everything else out. The rest of the database, and everything that touches only the rest of the database, can stay at READ COMMITTED and never hear about any of this.

So when SSI is the right tool, it is a design decision about one part of a system, made on purpose and fenced in. The database’s default stays where it was.

Then someone finds the knobs

Three parameters control predicate locking. max_pred_locks_per_transaction sizes the shared lock table and needs a restart. max_pred_locks_per_relation and max_pred_locks_per_page set the promotion thresholds and need only a reload. Modest increases to all three are reasonable. But the error message, the hint, and the documentation all point in one direction, which is up, and the temptation is to keep going until the errors stop.

They do not stop. They change.

The memory is real, and you pay it at startup

The lock table is sized as max_pred_locks_per_transaction times the number of backends the server could ever have (plus prepared transactions), with a second table twice that size alongside it. So the cost of each unit scales with max_connections: about 33 kB at 100 connections, about 130 kB at 500.

Unlike shared_buffers, which the operating system backs with real memory only as pages are touched, the lock tables are initialized in full when the server starts. At 64,000 my test server had 2 GB of shared memory resident before the first connection (it was 35 MB at the default), and startup went from a tenth of a second to 1.3 seconds.

A bigger table is a slower table

Same 40-row workload, nothing idle, the lock table nearly empty. At the default of 64, between 3,731 and 3,876 TPS. At 64,000, between 2,898 and 3,071. That is about 22% slower for the size alone. READ COMMITTED throughput on the same server did not change. (This was without huge pages; I have not measured it with them.)

It does not fix the idle session

A thousand times the default and two gigabytes of RAM buy less than four minutes of tolerance for one forgotten session.

One nuance, because it is the real rule: on the smaller two-million-row table, the 64,000 setting got through a one-minute run without failing. It ended holding 2.15 million predicate locks, about one for every row and index page in the table, and that is roughly the ceiling, because locks left by finished transactions on the same row are folded together. So the table has to be big enough to hold a lock on everything anyone reads while the oldest transaction is open. For a database of any real size, it will not be.

pg_locks gets slow, then stops working

Any query against pg_locks copies the entire predicate lock table while holding all sixteen of its partition locks. With about 950,000 predicate locks, a count(*) on the view took 0.7 seconds. With two million and a write workload running, it took 2.5 seconds, and for about six tenths of a second of that, every serializable transaction that needed to take or release a predicate lock was stalled. Throughput for that second fell from about 16,000 TPS to 5,800, and the worst latency went from around ten milliseconds to 591.

With 8.8 million:

1
=# SELECT pid, relation::regclass, mode FROM pg_locks WHERE NOT granted;
2
ERROR:  invalid memory alloc request size 1479349704

The copy needs 168 bytes per lock in a single allocation, and a single allocation is capped at 1 GB. Past about 6.4 million predicate locks, no query against pg_locks succeeds, with any WHERE clause. That is the view you would use to find out what is wrong, and the view your monitoring is sampling.

Cleanup lands on everyone

When the idle session finally commits, every lock it was pinning is released at once, by that backend, while it holds a lock that every other serializable transaction needs in order to finish. With two million locks pinned, the COMMIT of a transaction that had run one SELECT took 0.73 seconds, and serializable commits that arrived during it waited for it to finish (the worst one for 734 milliseconds). The work is a loop over every pinned lock.

Promotion was the brake

The default for max_pred_locks_per_relation is -2, which means “half of max_pred_locks_per_transaction, less one.” So when you follow the hint and raise max_pred_locks_per_transaction to 64,000, you have also raised the relation promotion threshold from 31 to 31,999. The hint does not mention that.

Here is one query, an index scan over a million rows (forced, to keep the planner’s opinions out of it), in one transaction with nothing else running:

Every time a transaction takes a page-level predicate lock, which includes every index leaf page it visits, it walks its entire list of predicate locks looking for finer-grained ones that the new lock makes redundant. With promotion working, that list is a handful of entries. With promotion off, it is every row the transaction has read so far. Each doubling of the row count more than tripled the time. I attached a debugger and sampled the backend a dozen times; ten of the twelve samples were in DeleteChildTargetLocks.

So the reporting query that took a third of a second takes 12 to 23 seconds after nothing more than following the hint, and anywhere from 15 seconds to over two minutes once promotion has been tuned out of the way.

What to do instead

Leave default_transaction_isolation at read committed.

Most applications have a handful of invariants that write skew could break, not hundreds. Protect those directly: a unique or exclusion constraint where one fits, SELECT ... FOR UPDATE on the row that arbitrates the decision, an explicit lock where nothing else will do. At READ COMMITTED, these wait instead of failing (a constraint violation is still an error, but a predictable one), and they are visible in the code to the next person who reads it.

If you want SSI, and there are applications where it is the right call, build for it from the first day:

• A retry wrapper around the whole transaction, for every transaction.
• READ ONLY declared wherever it is true.
• Short transactions, behind a pooler.
• idle_in_transaction_session_timeout set short for every role, whatever isolation level it uses. On PostgreSQL 17 and later, transaction_timeout on the application’s roles as well.
• Every transaction that touches the protected tables at SERIALIZABLE, including the scripts, and privileges that keep everything else away from them.
• Replica connections that set repeatable read for themselves.

And keep the knobs modest. Set max_pred_locks_per_relation explicitly, to a few hundred, so that it stops following max_pred_locks_per_transaction around. I would not take max_pred_locks_per_transaction past the high hundreds. If you find yourself reaching for the next power of ten, the problem is an idle session or an oversized read, and no value you can afford will outlast either one.