max_pred_locks_per_transaction has the naming problem of max_locks_per_transaction, and then one of its own. Like its namesake, it is a table size quoted per backend slot, not a limit on any transaction. Unlike its namesake, most of what its table holds at any given moment belongs to transactions that have already committed.
The default is 64, the minimum is 10, and the context is postmaster, so raising it takes a restart. It has been 64 since SSI arrived in 9.1, and 19 beta 3 leaves it there while doubling max_locks_per_transaction. It is not recorded in pg_control, so a standby can run with a different value, though a hot standby refuses SERIALIZABLE outright (“cannot use serializable mode in a hot standby”), so it would hardly matter. If nothing you run uses SERIALIZABLE, the table sits empty and the parameter does nothing.
max_pred_locks_per_page and max_pred_locks_per_relation covers what goes into the table and when PostgreSQL trades precise locks for coarse ones. This post is about the table.
Two tables and a headcount
PostgreSQL multiplies the parameter by every process slot that could hold a serializable transaction: max_connections, plus autovacuum_worker_slots (autovacuum_max_workers before 18), max_worker_processes, max_wal_senders, max_prepared_transactions, and two fixed system processes. A default 18 install has 136 such slots, so 8,704 locked objects. The documentation says “per server process,” which is correct, and which everyone reads as max_connections.
That is the size of the target table: one entry per thing locked, tuple, page, or relation. Beside it sits a second table of lock records, one per transaction per target, sized at twice the first on the source’s stated assumption that two transactions on average have read each target. When a workload has hot spots (every transaction crossing the same index pages, say), the second table fills first; in my runs it did so with the first one reading 94% full. Both are fixed-size hash tables, with no spillover and no summarization. When either is full, the next lock request fails:
1 ERROR: out of shared memory 2 HINT: You might need to increase "max_pred_locks_per_transaction".
That is SQLSTATE 53200, not 40001. The retry loop you wrote for serialization failures will not catch it. (One target entry is held back permanently for page splits, because a split has to copy the old page’s predicate locks, other transactions’ included, onto the new page, and the source would rather not abort a READ COMMITTED insert over someone else’s serializable bookkeeping.)
Now why 64 is the wrong number twice over. Its unit is wrong because SIREAD locks outlive their transaction: they are released only once every serializable transaction that overlapped the one that took them has finished, so the table holds the recent past as well as the present. Its size is wrong because 64 is smaller than the footprint of one ordinary transaction once you let it be precise. Forty rows fetched by primary key are 40 tuple locks and nearly as many index page locks. With max_pred_locks_per_relation = 1000, the setting the previous post arrives at, eight pgbench clients doing exactly that held between 900 and 1,200 targets in steady state on 18.6, with no idle session anywhere: 10 to 14% of a default table, with eight clients. Sixty-odd of them and it is full.
The docs say the default “has historically proven sufficient.” It has, in the sense that hardly anyone runs SERIALIZABLE, and those who do mostly run it at thresholds that throw the locks away before they can pile up.
What a bigger table costs, and what it cannot buy
The entries come to about 230 bytes each across the two tables, which on the 136-slot install is about 30 KB per unit of the parameter. 64 is under 2 MB. 640 took shared_memory_size on 18.6 from 150 MB to 169 MB; 6400 to 350 MB; 64000 to 2.3 GB. You can read the figure off before committing to it, provided the server is stopped, which during a restart it will be:
1 postgres -D $PGDATA -C shared_memory_size -c max_pred_locks_per_transaction=640
So 640 is ten times the capacity for 19 MB, and because max_pred_locks_per_relation defaults to a fraction of this parameter, the relation threshold moves from 31 to 319 in the same restart.
What it does not buy is protection from an idle serializable transaction. The previous post’s test, one session sitting in an open read-write serializable transaction while eight clients ran the 40-row workload, killed every client after 219 transactions at the default. At 640, it killed them after 4,158. On that machine that was a few seconds’ grace. Locks that cannot be released because one transaction has not finished will fill any table you give them, and the answer is idle_in_transaction_session_timeout or transaction_timeout, not another zero.
The same idle transaction exhausts a pool this parameter does not size. SSI records each read-write conflict between two transactions in a fixed pool of 50 entries per slot, 6,800 on the default install, with no parameter of its own. I filled it on 18.6 with one idle read-write transaction, the relation threshold set to 0 so that every read locked the whole table, and the same eight clients. The lock table held two targets the entire time, the transaction list stood at its full 1,360 entries, and four of the eight clients died with:
1 ERROR: not enough elements in RWConflictPool to record a read/write conflict 2 HINT: You might need to run fewer transactions at a time or increase "max_connections".
The hint is honest about the only knob there is, and it is still the wrong fix. The source sizes that pool for 200 connections all running serializable transactions flat out, and nobody holding one open across all of them.
To see where you stand:
1 SELECT count(*) AS locks, 2 count(DISTINCT (locktype, database, relation, page, tuple)) AS targets 3 FROM pg_locks 4 WHERE mode = 'SIReadLock';
Compare targets to the parameter times your slot count, and locks to twice that. Sample it through a peak. Leave max_pred_locks_per_transaction at 64 on a system that does not use SERIALIZABLE. On one that does, set it to 640 at the next restart, let the relation threshold follow, and size from there so that neither count crosses half its table under load. If the counts climb without the load changing, you are not short of table. Someone is holding a transaction open.