"out of shared memory" and lock monitoring in PostgreSQL

Question

I'm trying to monitor an application that uses PostgreSQL (13) to identify what can cause this error (occasionally):

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

I've increase the max_pred_locks_per_transaction (and max_locks_per_transaction), but I'm trying to find the potential cause in the application itself, to see if something better can be done about it.

When I monitor the locks, there are sometimes SIReadLock that seem to be running for a relatively long time, all hung up on COMMIT (although they do finish eventually). A number of the queries executed are likely to involve after-insert/update triggers.

Is there a way to monitor exactly which queries are creating those locks (as opposed to just seeing COMMIT at the end)? (Is there a general recommended way to investigate this sort of problems?)

Here is the query to monitor the locks:

SELECT pg_database.datname AS database,
       pg_namespace.nspname AS schema,
       pg_class.relname AS table,
       pg_locks.mode AS lock_mode,
       pg_locks.page AS lock_page,
       pg_locks.tuple AS lock_tuple,
       pg_locks.locktype AS lock_type,
       pg_locks.virtualxid AS virtual_xid,
       pg_locks.virtualtransaction AS virtual_transaction,
       pg_locks.transactionid AS transaction_id,
       AGE(now(), pg_stat_activity.query_start) AS time_running,
       pg_stat_activity.query_start AS time_started,
       pg_stat_activity.query
FROM pg_class
    INNER JOIN pg_locks ON pg_locks.relation = pg_class.oid
    INNER JOIN pg_database ON pg_database.oid = pg_locks.database
    INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    INNER JOIN pg_stat_activity ON pg_stat_activity.pid = pg_locks.pid
WHERE pg_class.relkind = 'r'
    ORDER BY 11 DESC;

Here is a sample output (more rows not displayed):

   table       |    lock_mode    | lock_page | lock_tuple | lock_type | virtual_xid | virtual_transaction| transaction_id |  time_running   |         time_started          |    query
---------------+-----------------+-----------+------------+-----------+-------------+--------------------+----------------+-----------------+-------------------------------+--------------
 test_table_a  | SIReadLock      |      8410 |            | page      |             | 25/298509          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8416 |            | page      |             | 25/295398          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_b  | SIReadLock      |           |            | relation  |             | 25/299949          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8411 |            | page      |             | 25/296128          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_c  | SIReadLock      |           |            | relation  |             | 25/297437          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8413 |            | page      |             | 25/300961          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8405 |            | page      |             | 25/294361          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8403 |        110 | tuple     |             | 25/300599          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8419 |            | page      |             | 25/298137          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_d  | SIReadLock      |           |            | relation  |             | 25/296086          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8413 |            | page      |             | 25/294361          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8421 |            | page      |             | 25/298137          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8422 |            | page      |             | 25/296692          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_b  | SIReadLock      |           |            | relation  |             | 25/300080          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8405 |            | page      |             | 25/299590          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_c  | SIReadLock      |           |            | relation  |             | 25/294222          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_b  | SIReadLock      |           |            | relation  |             | 25/299148          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8407 |            | page      |             | 25/296692          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_d  | SIReadLock      |           |            | relation  |             | 25/295640          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8431 |            | page      |             | 25/298779          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8426 |            | page      |             | 25/294976          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8410 |            | page      |             | 25/297792          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8416 |            | page      |             | 25/294361          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8426 |            | page      |             | 25/300585          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8417 |          1 | tuple     |             | 25/297792          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_c  | SIReadLock      |           |            | relation  |             | 25/295787          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_e  | SIReadLock      |           |            | relation  |             | 25/297764          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8421 |            | page      |             | 25/295893          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8427 |            | page      |             | 25/294719          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_d  | SIReadLock      |           |            | relation  |             | 25/297663          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_c  | SIReadLock      |           |            | relation  |             | 25/294139          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8431 |            | page      |             | 25/300947          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_d  | SIReadLock      |           |            | relation  |             | 25/295370          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8422 |            | page      |             | 25/297792          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_c  | SIReadLock      |           |            | relation  |             | 25/297064          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8410 |            | page      |             | 25/299949          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8415 |            | page      |             | 25/296128          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8428 |            | page      |             | 25/300080          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8431 |            | page      |             | 25/297423          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8417 |          1 | tuple     |             | 25/298509          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8408 |            | page      |             | 25/297064          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8427 |            | page      |             | 25/295384          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8425 |            | page      |             | 25/294236          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
 test_table_a  | SIReadLock      |      8415 |            | page      |             | 25/294361          |                | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT

(There's generally fewer than 30 connections to this database server, 10 to this database.)

Laurenz Albe · Accepted Answer · 2021-10-07 11:00:55Z

2

max_pred_locks_per_transaction determines the size of the “predicate lock target hashtable”. This is a different data structure than the lock table, so you can run out of shared memory there while the lock table is not full.

It is normal that SIReadLocks are held after the transaction commits. Your query is misleading: those COMMIT s are not hanging, they are done.

edited Oct 7, 2021 at 11:00

answered Oct 6, 2021 at 17:18

Laurenz Albe

62.7k4 gold badges58 silver badges94 bronze badges

1

Thank you. I've increased both max_pred_locks_per_transaction and max_locks_per_transaction (having seen both suggestions in error messages). As far as I understand, pg_locks is for both regular and predicate locks. Is there a way to track what those hanging COMMIT queries actually do (or perhaps track what the triggers actually do)?

Bruno
– Bruno

2021-10-07 10:54:54 +00:00
Commented Oct 7, 2021 at 10:54
1

Thanks, I'd not realised they were done. Are you effectively saying that those locks effectively have nothing to do with the OOM errors I'm getting? Is it also normal for those SIReadLocks to be held for such a long time?

Bruno
– Bruno

2021-10-07 11:18:18 +00:00
Commented Oct 7, 2021 at 11:18
1

Yes, that is normal. Sure, the number of locks (or predicate lock targets) makes you go OOM. But the COMMITs are completed. Just raise the limit and be happy.

Laurenz Albe
– Laurenz Albe

2021-10-07 11:38:46 +00:00
Commented Oct 7, 2021 at 11:38
1

I guess I'd generally be interested in having a way to monitor which queries make this number grow (possibly in order to try to improve the application that makes those queries in the first place). Somehow, I get the impression that increasing the limit (e.g. 500 at the moment for both) is only postponing the problem...

Bruno
– Bruno

2021-10-07 19:28:18 +00:00
Commented Oct 7, 2021 at 19:28
1

I don't know of a way to monitor that. 500 is high - do you have long running transactions? If there is really a leak, that would be a PostgreSQL bug.

Laurenz Albe
– Laurenz Albe

2021-10-08 04:09:06 +00:00
Commented Oct 8, 2021 at 4:09

| Show 1 more comment

Mikko Ohtamaa · Accepted Answer · 2021-12-05 17:55:10Z

After trying one week, I did not find any good solutions to it. Eventually I managed to pin down a potential query causing this by caughting it red handed.

Increase lock usage is usually tied to a long-running query or a query that behaves irrationally. It may happen in some PSQL versions and PSQL versions have bugs, though this is rare.

I had the same issue with Hint: You might need to increase max_pred_locks_per_transaction. and I had already increased max_pred_locks_per_transaction to 2048, a ridiculously high value.

How I tracked the underlying issue down:

Monitor PostgreSQL process memory. I monitored the Docker container where PSQL was running using Datadog. The issue was triggering somewhat randomly and I could not find a way to monitor per connection or per query lock and memory usage. Especially, in this case, as the problematic query pretty much brought the whole database down.

Monitor active queries using pg_activity. You can do this by hand from any PSQL IDE or psql cli prompt, but pg_activity makes it much easier.

After a lot of manual monitoring, I managed to pin down the query that caused exceed lock, and therefore memory usage, increase.

Fix the query

In my case, the solution was to get rid of the query in the first place. Other solutions you can do

If this application side query, split it to several query and commit batches. Make sure your application keeps the transaction open only for a short while and commits after updating hundreds or thousands of rows. Do not let a single transaction to touch multiple rows.
Re-engine the query e.g. to use materialised views

Ila · Accepted Answer · 2025-08-22 06:15:02Z

I’ve run into this exact situation before (PostgreSQL 12 and 13) where increasing max_pred_locks_per_transaction only postponed the inevitable.

A couple of points that helped me track it down:

Why it happens

Long-running transactions and large batch updates (especially with triggers) can hold a huge number of predicate locks.

Even though pg_locks shows COMMIT, those SIReadLocks are already finished — but they can still accumulate during the transaction.

Simply bumping max_pred_locks_per_transaction gives you more headroom, but if the underlying query pattern doesn’t change, you’ll hit the same ceiling.

How to track the culprit

Use pg_stat_activity and pg_locks together to correlate active queries with lock growth.

pg_activity is a great CLI helper, but it only shows you what’s happening “now.”

What really helped in my case was adding an observability layer (I used Middleware, though Datadog or similar works too). That gave me historical correlation between query patterns, memory, and lock counts, which made it obvious which background job was inflating locks.

Fixes that worked

Split large updates/inserts into smaller batches, committing after a few thousand rows instead of millions.

Rewrite certain trigger-heavy queries into materialized views or precomputed tables.

Keep transactions as short as possible — don’t hold them open while waiting for application logic.

So yes — increasing the config buys you time, but the durable fix is finding the query pattern that’s leaking locks. For me, the combination of pg_activity + Middleware observability was what made the root cause clear.

Stack Exchange Network

"out of shared memory" and lock monitoring in PostgreSQL

3 Answers 3

Your Answer

Linked

Hot Network Questions

"out of shared memory" and lock monitoring in PostgreSQL

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions