Idle Connections Consuming Excessive Memory

Scenario

A PostgreSQL server with 64 GB RAM is running with max_connections = 500 and shared_buffers = 16GB. Even during low-traffic hours with only 50 active queries, the server is under memory pressure and the OOM killer occasionally terminates PostgreSQL backends. The free -m output shows < 1 GB available. Most connections are idle pools parked by the application.

How to Identify

Conditions:

• Large max_connections (300-1000) consuming significant memory per connection
• shared_buffers already large; connection overhead is the secondary memory consumer
• max_connections × 5-10 MB per connection ≈ memory pressure
• work_mem is multiplied by max active sort/hash operations (not just connections)
• /proc/meminfo or free -m shows low available memory
• OOM killer events in /var/log/kern.log or dmesg

Analysis Steps

-- Check current connection count and max_connections
SELECT count(*) AS current, current_setting('max_connections')::int AS maximum
FROM pg_stat_activity;

-- Estimate memory reserved for connections
SELECT
    current_setting('max_connections')::int AS max_conn,
    current_setting('shared_buffers')       AS shared_buffers,
    current_setting('work_mem')             AS work_mem,
    -- Each idle connection: ~5-10 MB process overhead
    -- Each active connection with sort: work_mem × sort_operations
    current_setting('max_connections')::int * 5 || ' MB min (idle overhead)' AS min_conn_mem_est;

-- Find connections by state and memory-relevant activity
SELECT state, count(*),
       count(*) FILTER (WHERE wait_event_type = 'IO') AS io_wait
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

-- Check work_mem setting
SHOW work_mem;
-- work_mem can be multiplied by max_connections × sort_operations_per_query
-- Example: 64 MB work_mem × 500 connections × 2 sorts = 64 GB potential usage

-- Check OS memory usage (from OS tools, shown as comments):
-- free -m
-- cat /proc/meminfo | grep -E "MemAvailable|MemFree|Cached"

-- Check if OOM occurred:
-- dmesg | grep -i "oom\|killed process"
-- journalctl -k | grep -i "out of memory"

Pitfalls

• Each PostgreSQL backend is a separate OS process — even idle connections consume memory for the process overhead (stack, shared library maps, etc.) even though they share shared_buffers.
• work_mem is per sort/hash node, not per connection. A single complex query with 5 sort steps uses 5× work_mem. High work_mem × many concurrent queries = OOM risk.
• Increasing max_connections linearly increases memory overhead. With a connection pooler, max_connections can be 50-100 instead of 500.
• huge_pages = on can significantly reduce the memory overhead per process by using 2 MB huge pages for shared memory.
• min_dynamic_shared_memory (PG 14+) pre-allocates dynamic shared memory, reducing per-connection startup cost.

Resolution Approach

Reduce max_connections to a number actually achievable on the server (≈ RAM in GB × 10, capped at 200-300 for typical workloads). Deploy PgBouncer to handle more application clients. Tune work_mem down or set it per-role for memory-intensive operations.