PostgreSQL Memory Tuning: shared_buffers, work

PostgreSQL's default configuration is designed to start on almost anything, which means it uses almost nothing: shared_buffers = 128MB and work_mem = 4MB. On a dedicated server with gigabytes of RAM, that leaves performance on the table.

But raising numbers blindly is how you get an OOM kill at peak traffic. The key is understanding which settings are fixed allocations, which are per operation, and which are just hints to the planner. (Linux OOM killer)

How Postgres uses memory

┌──────────────── Server RAM ────────────────┐
│ shared_buffers (fixed, allocated at start) │
│ per-connection memory × connections        │
│   └ work_mem × sort/hash nodes per query   │
│ maintenance_work_mem × maintenance workers │
│ OS page cache (everything left over)       │
│ your app, OS, other services               │
└────────────────────────────────────────────┘

Postgres reads data through two caches: its own shared_buffers, and the operating system's page cache underneath. A page not in shared buffers is often still in the OS cache, which is far cheaper than disk. That's why Postgres doesn't want all the memory for itself.

shared_buffers

The main cache of table and index pages, allocated once at startup (changing it needs a restart).

Rule of thumb: ~25% of RAM on a dedicated database server. Beyond ~40% returns usually diminish, because pages end up cached twice (shared buffers and page cache).
On a server that also runs your app, size it from the RAM you're willing to give Postgres, not from total RAM.
Check how it's doing:

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT c.relname, count(*) * 8 / 1024 AS mb_cached
FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname ORDER BY 2 DESC LIMIT 10;

SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database WHERE datname = current_database();

A hit ratio in the high 90s for an OLTP app is normal. Note that blks_read counts reads from the OS, which may still be served from page cache, so a lower ratio isn't automatically a disk problem.

work_mem — the dangerous one

work_mem is the memory a single sort or hash operation can use before spilling to temporary files on disk. It's not per connection and not per query:

One query can have several sort and hash nodes, each allowed work_mem.
Hash joins and hash aggregates may use work_mem × hash_mem_multiplier (default 2.0).
Parallel queries give each worker its own allowance.

Worst case is roughly connections × nodes per query × work_mem × multiplier. With 100 connections, 3 hash nodes and work_mem = 64MB, that's potentially ~38 GB. It rarely all happens at once — until a traffic spike runs your heaviest report on every connection.

How to set it:

Start modest (16–32 MB for a small VM).
Find queries that spill:

-- postgresql.conf: log_temp_files = 0  (log every temp file with its size)
EXPLAIN (ANALYZE) SELECT ... ORDER BY ...;
--  Sort Method: external merge  Disk: 51200kB   ← spilled
--  Sort Method: quicksort  Memory: 2048kB       ← fit

Raise it for the queries that need it, not globally:

ALTER ROLE analytics SET work_mem = '256MB';
-- or inside one transaction:
SET LOCAL work_mem = '256MB';

Fewer connections make larger work_mem safe, which is one of the less obvious benefits of a connection pooler. (Postgres connection pooling)

maintenance_work_mem and autovacuum_work_mem

Used by VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY and REINDEX. Bigger values make index builds and vacuum noticeably faster.

256 MB–1 GB is typical on servers with a few GB of RAM or more.
It's allocated by each autovacuum worker (up to autovacuum_max_workers, default 3) unless you set autovacuum_work_mem separately. Keep autovacuum_work_mem moderate so three workers don't take a big chunk of RAM at once.
Since Postgres 17, vacuum's dead-tuple storage is far more compact and no longer capped at 1 GB, so large values are more useful than before. (Postgres vacuum and bloat)

effective_cache_size — a hint, not an allocation

Tells the planner how much memory is likely available for caching data (shared buffers + OS page cache). It allocates nothing. A higher value makes index scans look cheaper.

Set it to roughly 50–75% of RAM on a dedicated server, less if the app shares the machine. Getting it wrong doesn't crash anything; it just nudges plans. (Postgres planner statistics)

Per-connection overhead

Each connection is a separate OS process with its own memory: a few MB baseline, growing with catalog caches, prepared statements and temporary buffers (temp_buffers, 8 MB default, only used if the session touches temp tables). A thousand idle connections can use gigabytes before they run a single query. Keep max_connections low (often 50–200) and pool in front.

Huge pages

With large shared_buffers, the kernel's page tables for each backend mapping it get big. Huge pages (2 MB instead of 4 KB) cut that overhead and TLB misses.

huge_pages = try     # default; 'on' refuses to start without them

You need to reserve them in the kernel (vm.nr_hugepages). Postgres can tell you how many it needs:

postgres -D $PGDATA -C shared_memory_size_in_huge_pages

Worth doing above a few GB of shared buffers. Disable transparent huge pages for Postgres hosts if you see latency spikes from THP compaction.

Worked starting points

For a server running Postgres plus a modest app (adjust down if your app is memory-hungry):

Setting	4 GB RAM	8 GB RAM	16 GB RAM
`shared_buffers`	768MB	2GB	4GB
`effective_cache_size`	2GB	5GB	11GB
`work_mem`	16MB	24MB	32MB
`maintenance_work_mem`	256MB	512MB	1GB
`autovacuum_work_mem`	128MB	256MB	256MB
`max_connections`	50	100	150

These are starting points, not answers. Tools like PGTune generate similar baselines; then measure: temp-file logs, cache hit ratio, and memory headroom on the host under real load.

Where memory problems show up

Swap activity or OOM kills → work_mem × concurrency is too high, or too many connections. Check dmesg and memory pressure. (Linux pressure stall information)
Lots of temp files → work_mem too low for specific queries, or queries that sort more than they should (missing index for an ORDER BY ... LIMIT). (Postgres EXPLAIN ANALYZE)
Slow index builds and long vacuums → maintenance_work_mem too low.
Plans that ignore good indexes → effective_cache_size too low, or random_page_cost still at the spinning-disk default of 4 on SSD storage (1.1–1.5 is common for SSDs).

Change one thing at a time, and reload with SELECT pg_reload_conf(); for settings that don't need a restart.

EasySpawn servers come with fixed, dedicated RAM — 4, 8 or 16 GB — so you can size Postgres's memory to the machine instead of guessing at a shared host, and Claude Code can help you read temp-file logs and tune from there. See pricing or join the waitlist.

Postgres Memory Tuning: shared_buffers, work_mem and the Rest