Memory — shared_buffers, work_mem, and the OS Cache

Postgres spends memory out of three pools that have almost nothing to do with each other, and confusing them is the most expensive configuration error in the catalogue. shared_buffers is one shared cache of 8 KB page slots, sized at startup and fixed until the next restart. work_mem is a grant handed to every sort, hash and bitmap in every query in every backend, independently and repeatedly. Underneath both sits the operating system's page cache, which Postgres neither sizes nor manages — but does describe to the planner.

pg-primary has 64 GB of RAM and is running shared_buffers = 128MB, which is what the packaged default has been for years and what the install left behind. 128 MB is 16,384 page slots, roughly one thousandth of the machine. effective_cache_size is at its 4 GB default too, so the planner is pricing every query as though it were running on a small virtual machine. Both numbers are wrong by more than an order of magnitude, in the same direction.

shared_buffers, the Database's Own Cache

shared_buffers is a single shared-memory region carved into 8 KB slots when the postmaster starts, and every backend reads and writes pages through it. Postgres manages that region itself with its own clock-sweep replacement policy, holds dirty pages in it until a checkpoint writes them out, and counts every request against it as the shared hit or shared read that a plan reports. It is set in blocks, so 16 GB is 2,097,152 slots, and changing it requires a restart — there is no way to grow the pool on a running server.

The starting point that has survived two decades of argument is 25% of RAM, which on this machine is 16 GB. The reason it is a quarter rather than everything is the second pool: Postgres uses ordinary buffered reads, so a page that is not in shared_buffers is very often still in the kernel's cache one memcpy away. Pushing the pool toward half of RAM buys a diminishing amount of genuinely new cache while making every checkpoint dirtier and the OS cache smaller. Nothing about 25% is enforced by the server; it is where the measurements have tended to land.

shared_buffers = 16GB           # 25% of 64 GB = 2,097,152 slots
effective_cache_size = 48GB     # planner arithmetic only, allocates nothing
work_mem = 8MB                  # per node, per backend, per worker
maintenance_work_mem = 2GB      # index builds, VACUUM, ALTER TABLE
huge_pages = try                # 2 MB pages behind the 16 GB region

Only the first line and the last need a restart. The other three take a reload, which is what makes measuring them practical: work_mem and effective_cache_size are the two settings on that list you will change more than once.

The Operating System Cache and effective_cache_size

Because Postgres reads through the kernel, the real cache on pg-primary is not 16 GB. It is 16 GB of buffer pool plus whatever the kernel is holding of the same files, which on a database server with nothing else running is most of the remaining 48. That layering is the whole reason MySQL's advice does not transfer: InnoDB opens its data files with O_DIRECT and bypasses the kernel cache, so its buffer pool has to be 70–80% of RAM to hold the same working set that Postgres holds across two caches.

effective_cache_size is how you tell the planner about the layer it cannot see. It defaults to 4 GB and it allocates nothing at all: the documentation is explicit that it neither sizes shared memory nor reserves kernel cache, and exists purely for estimation. The number feeds the cost of an index scan — a larger assumed cache means repeated index descents are expected to hit memory rather than disk, which makes index paths cheaper relative to a sequential scan. Setting it to 48 GB on this machine costs nothing, takes a reload, and changes plans; what the planner then does with the cheaper index path is Chapter 9's subject.

work_mem Is Per Operation, Not Per Query

The name suggests a per-connection budget. It is not. Each sort, hash join, hash aggregate, bitmap and materialize node in a plan may allocate up to work_mem before it spills to disk, every backend running that plan gets its own set, and each parallel worker gets its own again. A query with four such nodes running in 200 backends can therefore have 800 grants outstanding at once. At the 4 MB default that is 3.2 GB, and the default is 4 MB for exactly that arithmetic.

Do the same arithmetic at 256 MB and it comes to 200 GB against 64 GB of physical memory. Nothing warns you, because the grants are only taken as nodes actually run: the setting looks fine for weeks and then a Saturday spike puts enough concurrent sorts on the machine at once and the kernel's OOM killer picks a backend, which on Postgres means the postmaster restarts every session in the cluster. Hash-based nodes get more than that headline number as well — hash_mem_multiplier has defaulted to 2.0 since 15 (it was 1.0 in 14), so a hash join is allowed twice what a sort is, on the reasoning that a hash that spills degrades harder than a sort that does.

Raising It Where It Belongs

The workloads that need a large work_mem are never the ones with 200 backends. Cartwheel's checkout path sorts nothing large; the analytics dashboard on pg-replica-a sorts millions of rows out of analytics.daily_orders and spills to disk at 8 MB. So the grant goes where the work is: a role-level default for the analytics role, and a transaction-scoped override for the one report that needs far more than everybody else.

-- every session that connects as this role starts with the larger grant
ALTER ROLE cartwheel_analytics SET work_mem = '256MB';
ALTER ROLE cartwheel_app       SET work_mem = '8MB';

-- and for one heavy statement, scoped to the transaction, not the session
BEGIN;
SET LOCAL work_mem = '1GB';
REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.daily_orders;
COMMIT;

A role-level setting is applied when the session starts, so it survives for as long as the connection does. SET LOCAL is scoped to the enclosing transaction and reverts at COMMIT, which makes it the form that is safe behind a transaction pooler; a plain session-level SET is not, for reasons the next topic makes concrete. Between the two, the global value stays at 8 MB and the 200 backends that were never the problem keep it.

maintenance_work_mem and Huge Pages

Maintenance work gets its own pool. maintenance_work_mem defaults to 64 MB and is what an index build, a table rewrite and a vacuum's dead-tuple bookkeeping draw from; because only a handful of those run at once, 1–2 GB is an ordinary production value and it makes CREATE INDEX on a 40-million-row table finish visibly sooner. The trap is autovacuum_work_mem, which defaults to -1, meaning "use maintenance_work_mem", while up to autovacuum_max_workers of them can be running, three by default. A 2 GB setting is therefore a 6 GB exposure unless you set the autovacuum value separately. What vacuum does with that memory is Chapter 7's argument; the sizing decision is here.

Huge pages are the other line worth understanding rather than copying. With 16 GB of shared memory and 4 KB pages, every backend's page tables have to map four million entries, and 200 backends mapping the same region separately is real CPU spent on address translation. Linux huge pages make each entry cover 2 MB instead, cutting the mapping to eight thousand entries. huge_pages defaults to try, which falls back to normal pages when the kernel has none reserved, and logs nothing about having done so.

postgres -C shared_memory_size_in_huge_pages -D /var/lib/postgresql/18/main
8264

# reserve them in the kernel (vm.nr_hugepages), restart, then verify:
grep Huge /proc/meminfo
HugePages_Total:    8500
HugePages_Free:      236     # most are in use = Postgres actually took them

The server computes the requirement itself from shared_buffers and the rest of the shared area, so there is no arithmetic to get wrong. Reserve slightly more than it asks for, restart, and read /proc/meminfo back: if HugePages_Free is still close to the total after Postgres starts, the reservation happened and the server did not use it. Once the pages are reserved and proven, huge_pages = on makes the server refuse to start rather than fall back, which is the behaviour you want on a machine that has been sized around them.

Measuring Instead of Guessing

Every number above has a counter behind it, and all of them are cumulative since the last reset. pg_stat_database carries blks_hit against blks_read for the cache picture and temp_files with temp_bytes for how much work has spilled. pg_stat_io, added in 16, splits reads, writes, extends, hits and evictions by backend type and by context, which is how you tell a vacuum's bulk reads from a client backend's. And since 18, buffer counts come with EXPLAIN (ANALYZE) without asking, so a single query's behaviour no longer needs a separate investigation.

SELECT blks_hit, blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1) AS hit_pct,
       temp_files, pg_size_pretty(temp_bytes) AS spilled
  FROM pg_stat_database WHERE datname = 'cartwheel';

 blks_hit  | blks_read | hit_pct | temp_files | spilled
-----------+-----------+---------+------------+---------
 812449311 |  60118204 |    93.1 |     41,207 | 3841 GB

Read the second half of that row, not the first. A 93.1% hit ratio on a 128 MB buffer pool is unremarkable and would still be unremarkable at 99%; it is a trend to watch, not a target to hit. The number that names a decision is 41,207 temporary files and 3.8 TB written to them since the last reset, which is work_mem saying it is too small for whatever is doing the sorting. Find the role behind those files and the fix is one ALTER ROLE; skip that step and the fix is a global raise multiplied by 200 backends.