I'm having 'out of shared memory' issue in PostgreSQL 13.3 on x86_64-pc-linux-gnu (The database has 41G data), first, it suggested me to increase max_locks_per_transaction.

After increasing max_locks_per_transaction to 1024, I still get 'out of shared memory', but now it suggests:

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

It seems very confusing to me. So my question:

  • Why does max_pred_locks_per_transaction affect on shared_memory ?
  • The shared_buffers is already 4GB, should I increase it ?
  • The 'out of shared memory' relates to which: max_locks_per_transaction * (max_connections + max_prepared_transactions) or shared_buffers ?

Thank you all.

  • Is the query execution plan using parallel query? Are you using the SERIALIZABLE transaction isolation level? Commented Mar 29 at 12:55

1 Answer 1


These error messages indicate that the hash table to store locks has been exhausted. This table is located in the shared memory segment and has a constant size, determined when the database starts as:

max_locks_per_transaction * (
  (max_connections + autovacuum_max_workers + 1 +
     max_worker_processes + max_wal_senders) # this is MaxBackends
  + max_prepared_transactions

Not related to shared_buffers at all. This and many other structures in shared memory structures are added in addition to the amount required to accommodate shared_buffers.

Look at your pg_locks to find out exactly what data the lock table stores in your database.

Common reasons:

  • excessive use of partitioning, when a query on a table with 5 indexes instead of 6 locks requires >6000 locks due to the existing 1000 partitions
  • widespread use of serializable transaction isolation. Predicate locks are stored in pg_locks as mode=SIReadLock; there can be a lot of them
  • many advisory locks
  • It seems that max_pred_locks_per_transaction is not in max_locks_per_transaction * ( (max_connections + autovacuum_max_workers + 1 + max_worker_processes + max_wal_senders) # this is MaxBackends + max_prepared_transactions ). So why the error message suggests increasing max_pred_locks_per_transaction ?
    – MiH
    Commented Mar 31 at 12:52
  • Damn, the second error message is really about another hash table. github.com/postgres/postgres/blob/REL_13_3/src/backend/storage/… This hash table is only about Serializable Snapshot Isolation.
    – Melkij
    Commented Mar 31 at 13:39
  • Thank you, but is it (max_pred_locks_per_transaction) relative to the shared memory ?
    – MiH
    Commented Apr 1 at 1:53
  • This is another structure that should be accessible from all database processes, therefore it is also located in the shared memory segment. There are quite a few such structures in shared memory, plus extensions can add their own.
    – Melkij
    Commented Apr 1 at 7:42
  • So the error indeed is the hash table reaches the limit, not exactly out of shared memory ? So I just need to increase max_pred_locks_per_transaction.
    – MiH
    Commented Apr 1 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.