Under SERIALIZABLE, PostgreSQL has to remember what every transaction read, and it has a fixed amount of shared memory to remember it in. These two parameters decide when it gives up remembering rows and remembers the page instead, and when it gives up on pages and remembers the whole table. Each step down in precision is paid for in serialization failures that did not need to happen. If nothing you run uses SERIALIZABLE, neither parameter does anything, and you can stop here.
max_pred_locks_per_page defaults to 2. max_pred_locks_per_relation defaults to -2, and the minus sign is deliberate: a negative value means max_pred_locks_per_transaction divided by its absolute value, less one, which at that parameter’s default of 64 comes to 31. (The documentation omits the “less one.” The source does not, and neither does a running server.) Zero and up is an absolute count. Both are sighup, so a reload is enough, and that matters more than usual here, because the only sensible way to set them is to try a value under real load and watch.
Both arrived in PostgreSQL 10, when Dagfinn Ilmari Mannsåker’s patch turned two hard-coded thresholds into parameters without changing their values; the documentation’s whole case for -2 is that it “keeps the behavior from previous versions of PostgreSQL.” So these are the numbers SSI shipped with in 9.1, in 2011, and what follows is how it has behaved ever since. PostgreSQL 19 beta 3 changes none of it.
default_transaction_deferrable covers what SSI is and why SIREAD locks exist. What matters here is that they come in three sizes (tuple, page, relation), and what decides which size you get.
The third row and the thirty-second
Fetch a row through a B-tree index in a serializable transaction and you hold a tuple lock on the heap row and a page lock on the index leaf you came through. Fetch a second row from the same heap page and you hold two tuple locks. Fetch a third:
1 -- after two rows: 2 locktype | relation | page | tuple 3 ----------+-----------+------+------- 4 tuple | acct | 0 | 1 5 tuple | acct | 0 | 2 6 page | acct_pkey | 1 | 7 8 -- after the third row on the same page: 9 locktype | relation | page | tuple 10 ----------+-----------+------+------- 11 page | acct | 0 | 12 page | acct_pkey | 1 |
That is max_pred_locks_per_page = 2: more than two tuple locks on one page and they are traded for a single lock on the page. There were 81 rows on that page. The transaction read three of them and is now on record as having read all 81.
max_pred_locks_per_relation does the same thing one level up, counting the tuple and page locks a transaction holds within one relation, index or table. On 18.6, after reading 31 rows from 31 different heap pages, I held 31 tuple locks on the table and 31 page locks on its primary key. The thirty-second row replaced all 62 with two relation locks. As far as SSI is concerned, that transaction read the table.
Some access paths never get the chance to be precise. A sequential scan takes the relation lock before it reads anything. An index-only scan that skips the heap takes a heap page lock, not a tuple lock. B-tree, hash, GiST and GIN lock index pages and nothing finer; BRIN, SP-GiST and bloom do no predicate locking of their own, so a scan through one of them locks the entire index. Tuple locks exist only in the heap, which makes max_pred_locks_per_page a heap-only parameter.
What coarse locks cost
A SIREAD lock blocks nobody. What it does is turn a later write to the locked thing into a recorded read-write conflict, and enough of those in the wrong shape gets a transaction cancelled with SQLSTATE 40001. A relation lock makes every write to the table a conflict.
I ran pgbench on 18.6 with eight clients, each transaction reading 40 random rows of a 100,000-row table by primary key and updating one other random row. Real conflicts in that workload are rare. At the defaults, 75.6% of transactions failed with serialization errors. Then max_pred_locks_per_relation = 1000 and a reload, nothing else: 0.08%, and close to four times the committed throughput.
max_pred_locks_per_page is the smaller lever. On a 50-page table, with five adjacent rows read and one random row updated, raising it from 2 to 100 took failures from 1.24% to 0.33%. It is also the lever that can make things worse, because every tuple lock counts toward the relation threshold and a page lock counts once. At the defaults, a range scan over 32 adjacent rows on one heap page ends up holding one page lock. With max_pred_locks_per_page = 100 and the relation threshold left alone, the same scan ends up holding a relation lock on the table. Do not raise the page threshold by itself.
What fine locks cost
The thresholds are stingy because the predicate lock table is a fixed piece of shared memory (8,704 lock targets for the entire cluster on a default 18 install), and because SIREAD locks outlive their transaction: they stay until every serializable transaction that overlapped it has finished. Throwing precision away early is how SSI stays inside that budget. I reran the 40-row test with max_pred_locks_per_relation = 1000 and one other session sitting idle in a serializable read-write transaction. Nothing could be released, the table filled within 219 transactions, and all eight clients died inside 11 milliseconds:
1 ERROR: out of shared memory 2 HINT: You might need to increase "max_pred_locks_per_transaction".
At the default thresholds, the same idle session never had more than 48 targets in the table when I looked. It also failed 67% of transactions, so that is a comparison of failure modes and no argument for the defaults.
The hint is right, and it is why -2 is negative. Raise max_pred_locks_per_transaction and the relation threshold follows it (at 640 it was 319 without my touching it), so capacity and precision grow together. That takes a restart. On a system that runs SERIALIZABLE in earnest, do it at the next one. Until then, a reload can put max_pred_locks_per_relation at a positive number comfortably above the rows your largest routine transaction reads from one table, with the understanding that you are spending the same 8,704 entries faster. Leave max_pred_locks_per_page at 2 until the relation threshold has moved. And set idle_in_transaction_session_timeout before any of it.
Then look at what you are holding:
1 SELECT relation::regclass, locktype, count(*) 2 FROM pg_locks 3 WHERE mode = 'SIReadLock' 4 GROUP BY 1, 2;
relation rows for tables you only ever reach by index are promotion at work. The 40001 rate you will have to count in the application or the log, because pg_stat_database counts deadlocks and not serialization failures.