How to fix Insufficient resources in PostgreSQL

The error message in the PostgreSQL log typically includes details about which resource was exhausted. Look for lines mentioning:

- Memory context (e.g., "memory exhausted")
- File path or disk (e.g., "/path/to/data" or temp file location)
- Connection count or locks

On Linux:

sudo tail -100 /var/log/postgresql/postgresql-*.log

Or check the server logs via the PostgreSQL client:

SELECT * FROM pg_read_file('/var/log/postgresql/postgresql.log', 0, 1000000);

Based on the server log, determine which resource failed:

Memory Issue: Look for "out of memory" or memory context references
Disk Issue: Look for "No space left on device" or temp file path references
Connection Limit: Error occurs during login, check max_connections
Lock Limit: Look for "max_locks_per_transaction" in the error

This determines which fix to apply.

If the error mentions sort or hash operations consuming memory:

# Connect to PostgreSQL and check current setting
psql -U postgres -d yourdb -c "SHOW work_mem;"

Edit /etc/postgresql/*/main/postgresql.conf (path varies by OS/version):

# Increase from default 4MB to match your workload
work_mem = 64MB  # for medium queries
# or
work_mem = 256MB # for larger operations

Reload the configuration without restarting:

sudo systemctl reload postgresql
# or
psql -U postgres -c "SELECT pg_reload_conf();";

If the error mentions max_locks_per_transaction:

psql -U postgres -d yourdb -c "SHOW max_locks_per_transaction;"

Edit /etc/postgresql/*/main/postgresql.conf:

# Default is 64, increase for bulk operations
max_locks_per_transaction = 256

Note: This requires more shared memory, so you may need to increase shared_buffers as well. Reload:

sudo systemctl reload postgresql
# or
psql -U postgres -c "SELECT pg_reload_conf();";

Alternatively, break large transactions into smaller chunks to use fewer locks at once.

Check available disk space:

df -h /var/lib/postgresql
du -sh /var/lib/postgresql/*/main/

If space is low:

Option 1: Clean up old data

-- Identify and archive old records
VACUUM FULL; -- Reclaim dead space
VACUUM ANALYZE; -- Update statistics

Option 2: Expand the disk
Add a new disk and create a tablespace:

CREATE TABLESPACE space_name LOCATION '/path/to/new/disk';
ALTER TABLE tablename SET TABLESPACE space_name;

If error occurs during login:

psql -U postgres -d postgres -c "SHOW max_connections;"

Edit /etc/postgresql/*/main/postgresql.conf:

# Default is 100
max_connections = 200

PostgreSQL requires a full restart for this parameter:

sudo systemctl restart postgresql

Consider using a connection pooler (pgBouncer, PgPool-II) instead of raising this value too high, as each connection consumes memory.

If the system is under overall memory pressure and you're getting frequent OOM errors:

SHOW work_mem;        -- Current setting
SHOW shared_buffers;  -- Current setting

If these are set too high for your available RAM, reduce them:

# Typical tuning:
shared_buffers = (Total RAM / 4)      # e.g., 8GB for 32GB system
work_mem = (Total RAM / (max_connections * 2)) # distribute memory

Edit and reload:

sudo systemctl reload postgresql

This trades some performance for stability on memory-constrained systems.