PostgreSQL's memory map · PostgreSQL

PostgreSQL’s memory divides into two regions with completely different properties, and nearly every memory misunderstanding comes from conflating them.

Shared memory

Allocated once, at startup, by the postmaster. Every backend maps the same region. Its size is fixed until the server restarts, which is why the settings that determine it are all postmaster context.

Region	Setting	Default
Buffer pool	`shared_buffers`	128 MB
WAL buffers	`wal_buffers`	1/32 of `shared_buffers`, capped at 16 MB
Lock table	`max_locks_per_transaction` × (`max_connections` + `max_prepared_transactions`)	64 × 100
Predicate lock table	`max_pred_locks_per_transaction` × the same	64 × 100
Replication slots, background workers, statistics	various

SELECT name, pg_size_pretty(allocated_size) AS size
  FROM pg_shmem_allocations
 ORDER BY allocated_size DESC
 LIMIT 10;

That view answers “where did my shared memory go” exactly, and it is the right first stop when a server refuses to start after a settings change.

Per-backend memory

Allocated per process, on demand, and this is where the surprises are.

Region	Setting	Bound
Sorts, hashes, materialised nodes	`work_mem`	Per node, per participant
Hash nodes specifically	`work_mem × hash_mem_multiplier`	Same, multiplied
Maintenance operations	`maintenance_work_mem`	Per operation
Temporary tables	`temp_buffers`	Per session
Catalogue and relation caches	—	Unbounded, and never returned

work_mem is per node per participant, not per query or per connection

The naive calculation is max_connections × work_mem. It is wrong, and it errs in the dangerous direction.

A single statement can allocate work_mem several times:

 Limit
   ->  Sort                                     <- work_mem
         ->  Finalize GroupAggregate
               ->  Gather Merge
                     Workers Planned: 2         <- times three participants
                     ->  Partial GroupAggregate
                           ->  Merge Join
                                 ->  Merge Join
                                 ->  Materialize   <- work_mem

That plan, captured on 18.6, has a Sort and a Materialize, each entitled to work_mem, and the parallel portion runs in the leader plus two workers.

So a defensible upper bound is closer to:

max_connections × work_mem × (nodes per query) × (1 + max_parallel_workers_per_gather)

Nobody can compute “nodes per query” for a real workload, which is why the honest approach is not arithmetic but measurement: set log_temp_files = 0, watch what actually spills, and raise work_mem per role or per session for the queries that need it rather than cluster-wide.

ALTER ROLE reporting SET work_mem = '256MB';
-- everyone else stays on the conservative default

Why RSS lies

Read-only / Safewhat ps reports for a cluster with 128 MB of shared buffers— Captured on 18.6 in the test container immediately after a large sequential scan.

$ ps -o pid,rss,vsz,comm -C postgres --sort=-rss | head -8

    PID   RSS    VSZ COMMAND
   30 146100 225468 postgres
   31 144960 225204 postgres
   27 142572 225204 postgres
   28 134936 225204 postgres
   29 114128 225204 postgres
    1  31788 225072 postgres
   33  11484 225204 postgres

Read-only / Safethe sum, against what is actually configured— Captured on 18.6. shared_buffers on this cluster is 128 MB.

$ ps -o rss= -C postgres | awk '{s+=$1} END {printf "%.0f MB\n", s/1024}'

728 MB

$ ps -o pid= -C postgres | wc -l
9

Nine processes, 728 MB of RSS, on a server whose shared memory segment is 128 MB.

RSS counts every page a process has touched, and the shared segment is counted in every process that has touched it. The same 128 MB is counted up to nine times.

The honest figure is:

shared memory (once)  +  the sum of each backend's private memory

The right way to measure, and what it costs

PSS — proportional set size — divides each shared page by the number of processes sharing it, so summing PSS across backends gives the true total.

# per process
grep ^Pss: /proc/PID/smaps_rollup

# summed across the cluster
for p in $(pgrep -x postgres); do
  awk '/^Pss:/ {s+=$2} END {print s}' "/proc/$p/smaps_rollup" 2>/dev/null
done | awk '{t+=$1} END {printf "%.0f MB\n", t/1024}'

Two caveats, stated because they matter in practice.

Reading smaps_rollup requires privilege — the owning user or root — and it is unreadable in some hardened container configurations. It was not readable in the container these captures came from, which is why this lesson shows the RSS sum as the problem and describes PSS as the method rather than presenting PSS numbers.

Reading smaps is not free. The kernel walks the page tables to produce it, which on a large process takes measurable time. It is a diagnostic, not something to poll every ten seconds.

Where PSS is unavailable, the defensible estimate is:

shared_buffers + wal_buffers + lock tables
  + (active backends × observed private memory per backend)

and the private figure is best obtained by watching RSS grow on a backend as it works, rather than by reading its absolute value.

The caches that never shrink

A backend’s catalogue cache and relation cache grow as it touches tables, functions and types, and they are never returned to the operating system while the session lives.

For most workloads this is a few megabytes and irrelevant. It becomes relevant in three situations:

Many tables. A schema with tens of thousands of tables — a multi-tenant design with a schema per tenant is the classic — can give each long-lived connection hundreds of megabytes of cache, purely from having touched them.

Long-lived pooled connections. A pooler in session mode keeps connections for days. Each accumulates cache for every table any client has ever used through it. The cluster’s memory grows for weeks and then plateaus at a number nobody predicted.

Partitioned tables with many partitions. Each partition is a relation and each is cached.

The fix is not a setting, because there is none. It is connection recycling: most poolers can retire a server connection after a number of uses or a period of time, and doing so returns the memory.

Huge pages, and why huge_pages = try hides a problem

On Linux the default page size is 4 kB. A 64 GB shared_buffers therefore needs about 16 million page table entries per backend, because each process maps the segment separately. With 500 connections that is real memory spent on page tables and real CPU spent walking them.

Huge pages — 2 MB on x86-64 — reduce that by a factor of 512.

PostgreSQL computes the requirement for you. On the test cluster, with a 150 MB shared memory segment:

SHOW shared_memory_size;                  -- 150   (MB)
SHOW shared_memory_size_in_huge_pages;    -- 75

Then reserve that many, with headroom:

sysctl -w vm.nr_hugepages=80

ALTER SYSTEM SET huge_pages = 'on';   -- not 'try'

The default is try, and that is the part worth understanding.

With try, the server attempts huge pages and silently falls back to 4 kB pages if it cannot get them. The cluster starts, works, and quietly loses the benefit — so a server that was configured for huge pages months ago may not be using them today, because a kernel upgrade reset vm.nr_hugepages and nothing reported it.

With on, the server refuses to start if huge pages are unavailable. That is louder and it is honest: you find out at the moment the configuration became wrong, rather than during a capacity investigation a year later.

Check what actually happened:

SELECT * FROM pg_shmem_allocations LIMIT 1;   -- confirms the segment exists

grep -i huge /proc/meminfo    # HugePages_Total, HugePages_Free

If HugePages_Total is non-zero and HugePages_Free is nearly equal to it, PostgreSQL did not take them.

Two further notes. Transparent huge pages (THP) are a different mechanism and are widely recommended to be disabled for database workloads, because the kernel’s background compaction can introduce latency spikes; explicit huge pages are the supported approach. And huge pages are not swappable, which for a database is a feature.

What to take from this

Shared memory is fixed at startup; per-backend memory is on demand and effectively unbounded.
work_mem is per node per participant. max_connections × work_mem understates the exposure.
Measured: nine backends summing to 728 MB of RSS on 128 MB of shared buffers. Summing RSS double-counts the shared segment.
PSS from smaps_rollup is the correct measurement, needs privilege, and is not free to read.
Catalogue caches never shrink. On many-table schemas with pooled connections, recycle connections.
huge_pages = try fails silently. on fails loudly, which is better.

Cross-course references

Linux for Production Sysadmins — Part XL (Memory Performance) covers what the operating system means by resident memory, and why summing it across backends double-counts the shared segment.
Docker & Containers — Part XV (Resource controls) covers the cgroup limit that decides what “out of memory” means for a containerised cluster.