Postgres has configuration settings that decide how predicate locks are handled during SERIALIZABLE transactions. These settings are very important for keeping transaction isolation and for using shared memory. Even though they belong to lock management, they are not the same as row locks, table locks or advisory locks that people use in database work.
Knowing these settings is helpful when working with SERIALIZABLE transactions because they tell Postgres how to track predicate locks and when to promote those locks to save memory.
Understanding how these settings behave also aids in looking at transaction conflicts and tuning systems that depend on the SERIALIZABLE isolation level.
In Postgres, there are mainly three parameters related to predicate locks in Postgres.
Let’s look at each one in more detail.
1.max_pred_locks_per_page
Check the current value of this parameter like this.
show max_pred_locks_per_page ;
Result:
max_pred_locks_per_page
-------------------------
2
(1 row)
Check this parameter’s metadata from pg_settings like this.
select * from pg_settings where name = 'max_pred_locks_per_page';
Result:
-[ RECORD 1 ]---+-------------------------------------------------------------------------------------------------------------------------------
name | max_pred_locks_per_page
setting | 2
unit |
category | Lock Management
short_desc | Sets the maximum number of predicate-locked tuples per page.
extra_desc | If more than this number of tuples on the same page are locked by a connection, those locks are replaced by a page-level lock.
context | sighup
vartype | integer
source | default
min_val | 0
max_val | 2147483647
enumvals |
boot_val | 2
reset_val | 2
sourcefile |
sourceline |
pending_restart | f
If you are using PostgreSQL version 19, you can see the guc parameter description from the file named guc_parameters.dat, or if you are using an older PostgreSQL version, then you can see this parameter definition from the file named guc_tables.c in the PostgreSQL source code.
{ name => 'max_pred_locks_per_page', type => 'int', context => 'PGC_SIGHUP', group => 'LOCK_MANAGEMENT',
short_desc => 'Sets the maximum number of predicate-locked tuples per page.',
long_desc => 'If more than this number of tuples on the same page are locked by a connection, those locks are replaced by a page-level lock.',
variable => 'max_predicate_locks_per_page',
boot_val => '2',
min => '0',
max => 'INT_MAX',
},This parameter is not related to table locks, row locks, etc. This is completely based on serializable transactions in Postgres.
We can manually create the predicate locks in Postgres like this.
Session 1:
Create a sample table for testing and insert some values like this.
CREATE TABLE orders (
id serial PRIMARY KEY,
amount int
);
INSERT INTO orders(amount)
SELECT generate_series(1,5000);
Now, begin a transaction with serializable mode.
BEGIN ISOLATION LEVEL SERIALIZABLE;
Inside this transaction, execute a SELECT query like this.
SELECT *
FROM orders
WHERE amount BETWEEN 1000 AND 1200;
Result:
id | amount
------+--------
1000 | 1000
1001 | 1001
1002 | 1002
1003 | 1003
1004 | 1004
1005 | 1005
1006 | 1006
...................
...................
Keep this transaction open.
Session 2:
SELECT * FROM pg_locksfsw3
WHERE mode='SIReadLock';
Result:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+------------+---------+----------+-----------
relation | 5 | 58077 | | | | | | | | 15/8 | 369897 | SIReadLock | t | f |
(1 row)
This parameter decides how many predicate locks PostgreSQL is willing to keep on a single heap/index page before promoting them into a relation-level predicate lock.
Check the current value of this parameter like this.
2.max_pred_locks_per_relation
show max_pred_locks_per_relation ;
Result:
max_pred_locks_per_relation
-----------------------------
-2
(1 row)
Check this parameter’s metadata from pg_settings like this.
select * from pg_settings where name = 'max_pred_locks_per_relation';
Result :
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------------------------------------------------
name | max_pred_locks_per_relation
setting | -2
unit |
category | Lock Management
short_desc | Sets the maximum number of predicate-locked pages and tuples per relation.
extra_desc | If more than this total of pages and tuples in the same relation are locked by a connection, those locks are replaced by a relation-level lock.
context | sighup
vartype | integer
source | default
min_val | -2147483648
max_val | 2147483647
enumvals |
boot_val | -2
reset_val | -2
sourcefile |
sourceline |
pending_restart | f
Check this parameter’s guc description from the file named guc_parameters.dat.
{ name => 'max_pred_locks_per_relation', type => 'int', context => 'PGC_SIGHUP', group => 'LOCK_MANAGEMENT',
short_desc => 'Sets the maximum number of predicate-locked pages and tuples per relation.',
long_desc => 'If more than this total of pages and tuples in the same relation are locked by a connection, those locks are replaced by a relation-level lock.',
variable => 'max_predicate_locks_per_relation',
boot_val => '-2',
min => 'INT_MIN',
max => 'INT_MAX',
},Purpose:
The max_pred_locks_per_relation parameter sets the number of predicate-locked pages and tuples that one SERIALIZABLE transaction can hold for a single relation before Postgres changes them into a single relation-level predicate lock. The main goal is to cut down on the shared memory used for managing predicate locks while still keeping the serializable snapshot isolation (SSI) working properly.
When a SERIALIZABLE transaction reads data from a table, Postgres places predicate locks to keep track of the data that has been read. As the transaction keeps reading tuples or pages from the same relation, the number of predicate locks can get very high. When the total of predicate-locked pages and tuples for that relation goes beyond the limit set by max_pred_locks_per_relation, then Postgres changes those locks into one relation-level predicate lock.
3.max_pred_locks_per_transaction
Check the current value of this parameter like this.
show max_pred_locks_per_transaction;
Result :
max_pred_locks_per_transaction
--------------------------------
64
(1 row)
Check this parameter’s metadata from pg_settings like this.
select * from pg_settings where name = 'max_pred_locks_per_transaction';
Result :
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name | max_pred_locks_per_transaction
setting | 64
unit |
category | Lock Management
short_desc | Sets the maximum number of predicate locks per transaction.
extra_desc | The shared predicate lock table is sized on the assumption that at most max_pred_locks_per_transaction * max_connections distinct objects will need to be locked at any one time.
context | postmaster
vartype | integer
source | default
min_val | 10
max_val | 2147483647
enumvals |
boot_val | 64
reset_val | 64
sourcefile |
sourceline |
pending_restart | f
Check this parameter’s metadata from pg_settings like this.
{ name => 'max_pred_locks_per_transaction', type => 'int', context => 'PGC_POSTMASTER', group => 'LOCK_MANAGEMENT',
short_desc => 'Sets the maximum number of predicate locks per transaction.',
long_desc => 'The shared predicate lock table is sized on the assumption that at most "max_pred_locks_per_transaction" objects per server process or prepared transaction will need to be locked at any one time.',
variable => 'max_predicate_locks_per_xact',
boot_val => '64',
min => '10',
max => 'INT_MAX',
},},Purpose:
The max_pred_locks_per_transaction parameter specifies the maximum number of predicate locks that a single SERIALIZABLE transaction is expected to hold. Its primary purpose is to determine the size of PostgreSQL's shared predicate lock table, ensuring that sufficient shared memory is allocated for predicate lock management.
As a transaction accesses more tables, pages, or tuples, the number of predicate locks it holds increases. The value of max_pred_locks_per_transaction is used by Postgres when sizing the shared predicate lock table during server startup. The shared predicate lock table is sized on the assumption that every server process or prepared transaction may need up to the configured limit of predicate locks. If an application frequently executes large SERIALIZABLE transactions that access many database objects, increasing this parameter allows PostgreSQL to allocate enough shared memory for those predicate locks.
This parameter only affects predicate locks created for SERIALIZABLE transactions. It does not control row locks, table locks, page locks, advisory locks, or any other lock types used by PostgreSQL.
The max_pred_locks_per_page, max_pred_locks_per_relation, and max_pred_locks_per_transaction parameters work together to manage predicate locks used by PostgreSQL's SERIALIZABLE isolation level. These settings determine how predicate locks are stored and when PostgreSQL promotes finer-grained locks to broader ones to reduce memory usage.