How to fix ERROR: out of shared memory in PostgreSQL

Connect to PostgreSQL and run:

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.

Check for idle transactions that may be holding locks:

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:

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';

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

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

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

max_locks_per_transaction = 256

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

sudo systemctl reload postgresql

Verify the change:

SHOW max_locks_per_transaction;

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

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

Or in application code, use:

SET TRANSACTION READ ONLY;

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

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

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

Find and adjust:

max_connections = 100

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

sudo apt-get install pgbouncer

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

sudo systemctl reload postgresql