10 — Postgres Internals: Memory, Buffers, and Vacuum

"Postgres magic" was my label for the internals, and it held until I needed to tune something. The model that organized them: one connection is one OS process; every process shares a single hot cache (the shared buffer pool) for data pages and a separate WAL buffer for log records; and VACUUM exists specifically because MVCC never overwrites rows, so dead versions accumulate and must be reclaimed. [1][2] Once those three pieces clicked, configuration, vacuuming, and lock waits stopped being mysterious and became consequences of the architecture.

Process-per-connection

Unlike databases with a threaded model, Postgres spawns a backend process per connection [1]. When a client connects, the postmaster (the supervisor) forks a backend that handles all SQL for that connection until it disconnects. That process has its own private memory for sorting, hashing, and query execution, and it shares memory with every other backend for the buffer pool and WAL.

The implication that mattered for me: every connection costs an OS process with its own memory, so connection count is a real resource. That's why connection poolers like PgBouncer exist — to multiplex many application connections onto a small set of backend processes.

The shared buffer pool

The shared buffer pool is the central cache — a fixed-size region of shared memory (sized by shared_buffers, typically 25% of RAM) holding 8KB data pages [3]. When a backend needs a page, it reads from the buffer pool first; only on a miss does it go to disk. Pages that are modified in memory become dirty and are written back to disk later, by the background writer and the checkpointer — not by the transaction that changed them. This is why shared_buffers is the single most impactful tuning knob.

Checkpoints and the background writer

A checkpoint is the act of flushing all dirty buffer pages to disk and recording a checkpoint record in the WAL [4]. On crash recovery, Postgres replays the WAL from the last checkpoint forward — anything before the checkpoint is already safely on disk. The checkpointer process runs periodically (checkpoint_timeout) or when WAL volume crosses max_wal_size. The background writer is a separate process that trickles dirty pages out continuously, smoothing I/O so checkpoints have less to do at once. Tuning checkpoint_completion_target spreads checkpoint writes to avoid I/O spikes.

WAL and the planner, briefly

Every modification appends a record to the WAL buffers, which is flushed to pg_wal/ on disk at commit (giving durability). The same WAL stream is what streaming replication ships to standbys. The query planner then decides, per query, the cheapest way to execute — which index, which join order, sequential scan vs. index scan — based on table statistics gathered by ANALYZE [5]. Stale statistics produce bad plans, which is why ANALYZE (run automatically by autovacuum) matters as much as reclaiming dead rows.

Vacuum: the price of MVCC

Because MVCC writes new row versions rather than overwriting, every UPDATE and DELETE leaves a dead row behind [2][6]. Dead rows accumulate as bloat — they consume disk and they slow scans, because Postgres has to skip over them. VACUUM reclaims them, marking the space reusable for future inserts. VACUUM ANALYZE also refreshes planner statistics. VACUUM FULL rewrites the table to physically shrink it, but takes an exclusive lock — use it rarely.

The practical mechanism is autovacuum, a background process that runs VACUUM and ANALYZE automatically based on thresholds (a table is vacuumed when a percentage of rows changed) [6]. The failure mode I had to learn: if autovacuum falls behind (long-running transactions block it, or the thresholds are wrong), bloat grows, indexes degrade, and performance craters. The fix is tuning autovacuum thresholds up or running manual vacuums on the worst tables, and identifying the long transactions blocking reclamation.

Locks: the currency of concurrency

Locks coordinate concurrent access [7]. The broad categories:

• Row-level locks — acquired by UPDATE/DELETE/SELECT ... FOR UPDATE on specific rows. Relatively cheap; MVCC means readers don't take them.
• Table-level locks — ACCESS EXCLUSIVE (blocks everything, taken by DROP/TRUNCATE/ALTER), SHARE UPDATE EXCLUSIVE (taken by VACUUM), down to ACCESS SHARE (taken by SELECT).
• Advisory locks — application-defined locks for coordination that isn't tied to a specific row.

The classic pain is a long-running transaction holding ACCESS EXCLUSIVE on a table while every other query waits. pg_locks and pg_stat_activity show who holds what; the fix is killing the blocker or, better, avoiding it with shorter transactions and online-schema-change patterns.

Physical storage: where it lives on disk

Underneath it all, $PGDATA has a known layout [8]:

• base/ — per-database files (each table and index is a file, segmented at 1GB).
• global/ — cluster-wide tables (roles, databases).
• pg_wal/ — the WAL files.
• pg_tblspc/ — symlinks to tablespaces on other volumes.
• postgresql.conf, pg_hba.conf, pg_ident.conf — config.

Pages are 8KB; large values use TOAST storage (oversized attributes are moved to a side table). I rarely touch this layer directly, but knowing it makes the size and performance conversation concrete — a "bloated" table is one whose files have grown with dead rows VACUUM hasn't reclaimed.

How I use this

Three habits fall out. First, I keep connection count low and put PgBouncer in front for anything with many clients — the process-per-connection model punishes uncontrolled connection growth. Second, I monitor bloat and autovacuum health: a table whose dead-row ratio climbs is a table I vacuum manually and whose autovacuum thresholds I tune. Third, when a query is slow, I check the planner via EXPLAIN first (is the plan reasonable?) and the statistics second (is ANALYZE fresh?) before blaming indexes or memory. The internals aren't magic; they're a small set of mechanisms that explain everything else.

References

[1] T. Austen, "Understanding the process and memory architecture of PostgreSQL," dev.to, 2023. [Online]. Available: https://dev.to/titoausten/understanding-the-process-and-memory-architecture-of-postgresql-5hhp

[2] PostgreSQL Global Development Group, "Routine Vacuuming," 2024. [Online]. Available: https://www.postgresql.org/docs/current/routine-vacuuming.html

[3] PostgreSQL Global Development Group, "pg_buffercache," 2024. [Online]. Available: https://www.postgresql.org/docs/current/pgbuffercache.html

[4] Cybertec, "What is a checkpoint?," 2024. [Online]. Available: https://www.cybertec-postgresql.com/en/postgresql-what-is-a-checkpoint/

[5] PostgreSQL Global Development Group, "Planner/Optimizer," 2024. [Online]. Available: https://www.postgresql.org/docs/current/planner-optimizer.html

[6] EnterpriseDB, "PostgreSQL VACUUM and ANALYZE best practice tips," 2024. [Online]. Available: https://www.enterprisedb.com/blog/postgresql-vacuum-and-analyze-best-practice-tips

[7] S. Sisodiya, "Understanding Postgres locks and managing concurrent transactions," Medium, 2023. [Online]. Available: https://medium.com/@sonishubham65/understanding-postgres-locks-and-managing-concurrent-transactions-1ededce53d59

[8] PostgreSQL Global Development Group, "Database Physical Storage," 2024. [Online]. Available: https://www.postgresql.org/docs/current/storage-toast.html