How to fix ERROR: out of shared memory in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 28, 2025· 207 views

PostgreSQL runs out of shared memory when the lock table exceeds capacity, typically due to excessive table locking or high concurrency. Increase max_locks_per_transaction or adjust connection settings to resolve.

Quick fix

Fixes ERROR: out of shared memory

SHOW max_locks_per_transaction;
SHOW max_connections;
SHOW max_prepared_transactions;
  1. Check current lock table usage with psql
  2. Identify long-running or idle transactions
  3. Increase max_locks_per_transaction
  4. Mark read-only transactions appropriately
postgresql — error
SHOW max_locks_per_transaction;
SHOW max_connections;
SHOW max_prepared_transactions;
ERROR: out of shared memory

What this error means

The "out of shared memory" error occurs when PostgreSQL exhausts its fixed-size shared memory area used for tracking locks on database objects. PostgreSQL allocates shared memory for locks based on the formula: max_locks_per_transaction * (max_connections + max_prepared_transactions). This is not about OS-level shared memory configuration, but PostgreSQL's internal lock management space. Once this limit is exceeded, any attempt to lock additional objects fails with this error. Common scenarios include touching thousands of tables in a single transaction, holding many concurrent connections with heavy locking patterns, or creating temporary tables repeatedly within one transaction. Row-level locks are stored on disk and don't consume this shared memory, so the issue is specifically about table-level and object-level locks.

Typical symptoms

  • PostgreSQL connections fail with "ERROR: out of shared memory" during operations
  • Bulk data operations or schema changes fail unexpectedly
  • Application experiences intermittent connection failures when under load
  • Database performance degrades with error logs showing shared memory exhaustion
  • ALTER TABLE or DDL operations fail on databases with many tables or partitions

Common causes

  1. Too many table locks from accessing thousands of tables in a single transaction
  2. Excessive partitioning without proper lock table sizing
  3. Long-running transactions not marked as READ ONLY that accumulate serialization read locks
  4. Orphaned or idle transactions holding locks that are never released
  5. max_connections set too high relative to max_locks_per_transaction
  6. High concurrency with many subtransactions consuming lock memory

How to fix "ERROR: out of shared memory"

1Check current lock table usage with psql

Connect to PostgreSQL and run:

sql
SHOW max_locks_per_transaction;
SHOW max_connections;
SHOW max_prepared_transactions;

Calculate total lock capacity: max_locks_per_transaction * (max_connections + max_prepared_transactions). If this is exhausted relative to your workload, proceed to the next step.

2Identify long-running or idle transactions

Check for idle transactions that may be holding locks:

sql
SELECT pid, usename, application_name, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND state_change < NOW() - INTERVAL '10 minutes';

Terminate these if safe:

sql
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND state = 'idle in transaction'
AND state_change < NOW() - INTERVAL '10 minutes';
3Increase max_locks_per_transaction

Edit your PostgreSQL configuration file (usually /etc/postgresql/*/main/postgresql.conf on Linux):

bash
sudo vi /etc/postgresql/15/main/postgresql.conf

Find the line with max_locks_per_transaction (or add it if missing) and increase it:

bash
max_locks_per_transaction = 256

Start with 256, then increase further if needed (256, 512, 1024). Reload the configuration:

bash
sudo systemctl reload postgresql

Verify the change:

sql
SHOW max_locks_per_transaction;
4Mark read-only transactions appropriately

If your application has long-running analytical queries, mark them explicitly as read-only to avoid unnecessary lock tracking:

sql
BEGIN READ ONLY;
-- Your SELECT queries here
COMMIT;

Or in application code, use:

sql
SET TRANSACTION READ ONLY;

This prevents PostgreSQL from accumulating serialization read locks for transactions that don't modify data.

5Reduce max_connections if excessive

If max_connections is set very high (hundreds or thousands), consider lowering it:

bash
sudo vi /etc/postgresql/15/main/postgresql.conf

Find and adjust:

bash
max_connections = 100

Instead of increasing raw connections, use a connection pooler like PgBouncer:

bash
sudo apt-get install pgbouncer

Configure PgBouncer to pool connections to PostgreSQL, reducing the actual server-side connection overhead. Then reload PostgreSQL:

bash
sudo systemctl reload postgresql

Advanced notes

For databases with thousands of tables or heavy partitioning, consider database sharding or splitting schemas across multiple PostgreSQL instances. The lock table formula max_locks_per_transaction * (max_connections + max_prepared_transactions) is fixed at startup, so tuning these parameters requires a service restart. On Unix-like systems, use ipcs -m to verify the operating system shared memory configuration isn't the limiting factor. Most "out of shared memory" errors are PostgreSQL lock table exhaustion, not OS kernel shared memory limits. If you have many temporary tables created in loops, consider materialized views or permanent staging tables instead. For managed PostgreSQL services (Heroku, AWS RDS, Azure), you may need to upgrade to a plan with higher lock limits or higher max_connections allocation.

Frequently asked questions

What does "ERROR: out of shared memory" mean?
The "out of shared memory" error occurs when PostgreSQL exhausts its fixed-size shared memory area used for tracking locks on database objects. PostgreSQL allocates shared memory for locks based on the formula: max_locks_per_transaction * (max_connections + max_prepared_transactions). This is not about OS-level shared memory configuration, but PostgreSQL's internal lock management space.
How do I fix "ERROR: out of shared memory"?
PostgreSQL runs out of shared memory when the lock table exceeds capacity, typically due to excessive table locking or high concurrency. Increase max_locks_per_transaction or adjust connection settings to resolve.
What causes "ERROR: out of shared memory"?
Too many table locks from accessing thousands of tables in a single transaction Excessive partitioning without proper lock table sizing Long-running transactions not marked as READ ONLY that accumulate serialization read locks
Is the "ERROR: out of shared memory" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading