Troubleshooting Low Free Memory in PostgreSQL

Low free memory on a PostgreSQL host comes from a few predictable sources: a shared_buffers region sized too large for the machine, per-connection work_mem that multiplies across many queries and sort nodes, and an operating system that overcommits memory until the Linux OOM killer terminates a backend. PostgreSQL allocates one shared region at startup and then a variable amount of private memory per connection, so total usage scales with concurrency, not just configuration. When the host runs out, you see FATAL: out of memory in the logs, swapping, or a killed postmaster child.

How PostgreSQL Allocates Memory

PostgreSQL splits memory into one shared region plus per-connection (per-backend) private memory. The shared region is shared_buffers, allocated once at server start and fixed until restart. It caches table, index, and TOAST pages in 8 KB blocks (the block_size preset). The default is 128MB; common guidance on a dedicated server is roughly 25% of RAM, though that figure is a starting point, not a target - see debugging low cache hit ratio for why.

The part that surprises people is the private memory. work_mem (default 4MB) is the limit for a single sort, hash, or materialize operation, not per query and not per connection. A query with several sort and hash nodes can allocate work_mem once per node, and a parallel query allocates it per worker. Multiply that by hundreds of connections and the peak is work_mem x nodes x parallel_workers x connections - far above what the single 4 MB number suggests. When an operation exceeds work_mem, PostgreSQL spills to temporary disk files instead of failing, which trades memory pressure for I/O.

Three more allocations round out the picture. maintenance_work_mem (default 64MB) bounds memory for VACUUM, CREATE INDEX, and ALTER TABLE ... ADD FOREIGN KEY; each autovacuum worker can use up to this amount. temp_buffers (default 8MB) is per-session memory for temporary tables. effective_cache_size (default 4GB) allocates nothing at all - it is a planner hint estimating how much cache (shared buffers plus OS page cache) is available, biasing the planner toward index scans when set higher.

Symptoms of Memory Pressure

The clearest symptom is FATAL: out of memory or ERROR: out of memory in the PostgreSQL log, usually followed by a DETAIL line reporting the failed allocation size and a memory context dump. On Linux, a more abrupt failure is the kernel OOM killer terminating a backend; the server log then shows server process ... was terminated by signal 9: Killed and the whole instance enters crash recovery, dropping every connection. Check dmesg or the journal for Out of memory: Killed process to confirm the OOM killer fired.

Before an outright failure, the host degrades. Swapping shows up as rising si/so columns in vmstat and latency that grows with no change in query plans. Heavy disk spills appear as growth in pg_stat_database.temp_files and temp_bytes, and as files under the base/pgsql_tmp directory - a sign that work_mem is too small for the workload even while total memory is tight. Memory pressure and disk-spill pressure pull in opposite directions, which is why raising work_mem to stop spills can push the host into swapping or the OOM killer.

How to Diagnose It

Start from the operating system, because PostgreSQL only sees the memory the kernel actually grants. Check overcommit and swap settings:

# Linux overcommit policy and current swap usage
cat /proc/sys/vm/overcommit_memory   # 0=heuristic, 1=always, 2=strict accounting
free -h
vmstat 1 5                           # watch si/so (swap in/out) columns
dmesg | grep -i "out of memory"      # did the OOM killer fire?

vm.overcommit_memory = 2 with a tuned vm.overcommit_ratio makes the kernel refuse allocations it cannot back, turning a silent OOM kill into a clean FATAL: out of memory that PostgreSQL can report and survive. The PostgreSQL docs recommend this for dedicated database hosts.

Inside the database, find the connections and queries driving allocation. High connection counts multiply work_mem, and disk spills reveal undersized operations:

-- Connection count by state - many idle connections still hold memory
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Databases generating the most temp-file spill (work_mem too small)
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp
FROM pg_stat_database
WHERE temp_bytes > 0
ORDER BY temp_bytes DESC;

To see which operations spill, run EXPLAIN (ANALYZE, BUFFERS) on suspect queries and look for Sort Method: external merge Disk: ... or a hash join reporting Batches: > 1. Those nodes would use more memory if work_mem allowed, but raising it globally is the wrong fix - set it per session or per role for the heavy queries instead.

Tuning Memory Parameters

Tune from the constraint inward: cap concurrency first, then size the shared region, then give individual queries enough work_mem without multiplying it across hundreds of connections. The heuristic work_mem = total_RAM / max_connections / 16 is a conservative starting point that assumes every connection runs a multi-node query at once - it is deliberately pessimistic, and most workloads can run higher. Treat it as a floor to begin from, then raise work_mem for the specific roles that spill while keeping max_connections low behind a pooler.

Two operating-system levers matter alongside the GUCs. Hugepages (set huge_pages = try or on, backed by vm.nr_hugepages) reduce page-table overhead for a large shared_buffers and let the kernel lock that memory; disable Transparent Huge Pages (THP) when using explicit hugepages, since THP's background compaction causes latency spikes. The most effective single change for memory multiplication is a connection pooler - PgBouncer in transaction mode lets thousands of clients share a small pool of backends, capping the work_mem multiplier at the pool size rather than the client count.

Root-Cause Analysis and Prevention with Pulse

Pinning a low-memory incident on the right cause usually means correlating OS metrics (free, vmstat, dmesg), the PostgreSQL log's memory-context dump, pg_stat_activity connection counts, and pg_stat_database temp-file growth - often after the OOM killer has already restarted the instance and erased the live state. Pulse automates that correlation. It continuously tracks resident memory, swap activity, connection counts, and temp-file spill per database, detects when free memory trends toward exhaustion, and traces the pressure to its source: a backend holding a huge work area, a flood of idle connections multiplying work_mem, or an autovacuum run sized past available RAM. It then recommends a concrete change - lower work_mem for this role, add a pooler, cap max_connections - for a human to approve rather than acting blindly.

For prevention, connecting your database to Pulse's proactive monitoring catches the slow drift toward the OOM killer: a connection count creeping up after a deploy, a query whose plan started spilling, or a shared_buffers setting that no longer leaves headroom for per-connection memory. Alerting on swap usage and the temp-file rate surfaces the problem while it is still a latency blip, not a crash. See database monitoring best practices for the broader signal set.

Frequently Asked Questions

Q: Why does PostgreSQL run out of memory even though shared_buffers is small?
A: Out-of-memory failures in PostgreSQL are usually driven by per-connection memory, not shared_buffers. Each query can allocate work_mem once per sort, hash, and materialize node and once per parallel worker, so a few hundred connections running complex queries can consume many times the single work_mem value. Cap concurrency with a pooler and set work_mem per role rather than globally.

Q: What does FATAL: out of memory mean in PostgreSQL?
A: FATAL: out of memory means a backend asked the operating system for memory and the request was refused, usually because the host has no free RAM or the kernel's overcommit accounting denied it. The accompanying DETAIL line and memory-context dump show the failed allocation size. It is preferable to a silent OOM kill because PostgreSQL reports it and the backend exits cleanly instead of crashing the whole instance.

Q: How do I stop the Linux OOM killer from killing PostgreSQL?
A: Set vm.overcommit_memory = 2 with an appropriate vm.overcommit_ratio so the kernel refuses allocations it cannot back, converting OOM kills into recoverable FATAL: out of memory errors. Also reduce the postmaster's OOM score and lower total memory demand by capping max_connections and work_mem. Leaving swap available gives headroom but trades it for latency.

Q: How should I calculate work_mem?
A: A conservative starting point is total_RAM / max_connections / 16, which assumes every connection runs a query with many memory-hungry nodes at once. That is deliberately pessimistic; most workloads can run higher. The better approach is to keep max_connections low behind a connection pooler and raise work_mem only for the specific roles or sessions whose queries spill to disk.

Q: Does increasing work_mem reduce disk spills?
A: Yes - raising work_mem lets sorts and hash joins keep more data in memory instead of spilling to temporary files, which you can see as temp_files/temp_bytes growth in pg_stat_database. The risk is that work_mem is allocated per node per connection, so a global increase can multiply into an out-of-memory condition. Raise it per role for the heavy queries instead of server-wide.

Q: What is the difference between shared_buffers and effective_cache_size for memory?
A: shared_buffers allocates actual RAM for PostgreSQL's page cache and counts against the host's memory. effective_cache_size allocates nothing; it is a planner hint estimating total cache available (shared buffers plus OS page cache) so the planner chooses between index and sequential scans. Raising effective_cache_size never consumes memory or causes an OOM condition.

Q: How do I see if PostgreSQL is spilling to disk?
A: Query pg_stat_database for non-zero temp_files and temp_bytes, and run EXPLAIN (ANALYZE, BUFFERS) on suspect queries to find Sort Method: external merge Disk: or hash joins with Batches > 1. Spill files also appear under the base/pgsql_tmp directory. Frequent spilling means work_mem is too small for that workload.

Related Reading

• Debugging Low Cache Hit Ratio in PostgreSQL: How shared_buffers sizing affects disk reads and what a low ratio actually means.
• PostgreSQL Performance Tuning: Configuration and query-level levers beyond memory.
• PostgreSQL Connection Pooling with PgBouncer: Cap the per-connection memory multiplier with a pooler.
• PostgreSQL Too Many Connections: Diagnose and fix the connection counts that multiply work_mem.
• PostgreSQL Out of Shared Memory: A related error driven by lock and shared-memory limits.
• VACUUM in PostgreSQL: How maintenance_work_mem and autovacuum interact with memory.
• Partitioning in PostgreSQL: Shrink working sets so individual queries touch less data and less memory.
• Database Monitoring Best Practices: Which signals catch memory pressure before it becomes an OOM kill.