One process per connection

When PostgreSQL starts, it runs one parent process, the postmaster. It does no query work itself. When a client connects, the postmaster starts a new operating system process for it, called a backend. The backend parses, plans and runs every query from that connection until it disconnects. So 300 connections from the NestJS APIs means 300 processes on the database VM.

Each backend has its own private memory: memory for sorts and hashes, plus caches of table definitions and query plans. An idle backend costs a few MB. A backend that has touched hundreds of tables, or is running a big sort, costs much more. That is the reason underneath the pooling advice in Running Postgres in production: connections are processes, and processes cost memory and CPU time even while they wait.

Next to the backends run the background processes. On a PostgreSQL 16 primary you will see these:

• checkpointer: every few minutes, writes all changed pages from memory to the data files, spread out so the disk is not flooded.
• background writer: writes some changed pages ahead of time, so backends usually find a clean memory slot when they need one.
• WAL writer: flushes the write-ahead log to disk in the background, every 200 ms by default.
• autovacuum launcher: checks each database about once a minute (autovacuum_naptime) and starts autovacuum workers (3 at most by default) for tables that need cleaning.
• WAL sender: one per connected replica or backup tool, streaming the log to it (Replication and read replicas).

The process model has one consequence every DBA must know. All these processes share one region of memory. If any backend dies in an unclean way, for example killed by the Linux OOM killer, the postmaster cannot trust that shared memory any more. It logs terminating any other active server processes, disconnects every client, and runs crash recovery before it accepts connections again. One runaway report can drop every app order for a few seconds or longer, which is why the PostgreSQL docs recommend strict memory overcommit settings on Linux.

Memory: shared buffers, the OS cache and work_mem

PostgreSQL stores tables and indexes in 8 kB pages. A backend never works on the data file directly. It first loads the page into shared buffers, its own page cache, then reads or changes it there. A changed page that is not yet on disk is called a dirty page.

PostgreSQL reads files through the operating system, so Linux also keeps recently read blocks in its page cache. A hot page can sit in both places at once. That is why shared buffers usually starts near 25% of RAM and not 80%: the rest of RAM still works as a second cache. A miss in shared buffers that hits the OS cache is still fast; only a miss in both goes to disk. In EXPLAIN (ANALYZE, BUFFERS), "shared hit" means found in shared buffers, and "read" means asked from the OS, which PostgreSQL cannot split into cache and disk.

When shared buffers is full, a backend that needs a slot takes a page nobody used lately. If that page is dirty, it must be written out first. The background writer keeps clean slots ready so backends rarely wait for that; since PostgreSQL 16, pg_stat_io shows how often client backends had to write pages themselves.

The other memory setting that matters is work_mem, private to each backend. It is a limit for each sort or hash step in a plan, not for the query or the connection. A sort that needs more spills to temporary files, and the plan shows Sort Method: external merge Disk: 210MB. Turn on log_temp_files to see which queries spill. Hash steps may use work_mem times hash_mem_multiplier, which is 2 by default since PostgreSQL 15. Running Postgres in production shows how quickly these limits multiply across connections; Finding and fixing slow queries covers tuning them with real numbers.

The write path: WAL first, data pages later

Writing every changed page to its data file at commit would be slow: one order touches pages in orders, order_line and several indexes, scattered across the disk. Instead, PostgreSQL records each change in the WAL, an append-only log, and writes the data pages later. The rule is simple: the log record for a change must reach disk before the changed page does, and before the commit is confirmed.

Here is one UPDATE orders SET status = 'picked' from the Rider app:

1. The backend loads the page into shared buffers and changes it there. The page is now dirty.
2. It appends a WAL record describing the change to the WAL buffers in shared memory.
3. At COMMIT, the backend writes the WAL up to its commit record into the pg_wal directory and calls fsync, which waits until the disk confirms. Only then does the client see COMMIT.
4. Seconds or minutes later, the background writer or the checkpointer writes the dirty page to its data file.

One sequential write per commit is far cheaper than many random page writes, and commits arriving together can share one fsync.

A checkpoint writes every page that was dirty when it started. PostgreSQL starts one when checkpoint_timeout passes (5 minutes by default) or when the WAL written since the last one nears max_wal_size (1 GB by default). It spreads its writes over most of the interval (checkpoint_completion_target, 0.9 by default since PostgreSQL 14). Afterwards, older 16 MB WAL segment files can be recycled, unless archiving, a replication slot or wal_keep_size still needs them (Backups you have actually restored and Replication and failover).

After a crash, the data files may hold a mix of old and new pages. On restart, the startup process finds the last checkpoint's redo point in the control file and replays every WAL record after it; the log shows redo starts at 3A/41000028. Every committed transaction comes back, and nothing uncommitted survives. To survive pages torn in half by a power cut, the first change to each page after a checkpoint writes the whole 8 kB page into the WAL (full_page_writes). That is why WAL volume jumps right after each checkpoint.

MVCC: every UPDATE writes a new row version

PostgreSQL never changes a row in place. An UPDATE writes a new version of the row and marks the old version as ended; a DELETE only marks it. This is MVCC, introduced in Running Postgres in production. Every row version carries hidden system columns you can select:

• xmin: the ID of the transaction that created this version.
• xmax: the ID of the transaction that deleted or replaced it, or 0. (Row locks such as SELECT … FOR UPDATE also use xmax, so a non-zero xmax does not always mean the row is gone.)
• ctid: where the version lives, as (page, item). A new version usually gets a new ctid.

Each statement (or each transaction under REPEATABLE READ, see Transactions, ACID, isolation and locks) takes a snapshot: in effect, the list of transactions that had committed at that moment. A version is visible if its xmin committed before the snapshot and its xmax is 0, aborted, or not yet committed for that snapshot. You can see a snapshot with SELECT pg_current_snapshot();. It prints the oldest running transaction, the next unused ID, and the running ones, such as 48211907:48211915:48211907,48211909.

When no snapshot anywhere can see an old version any more, it is a dead tuple. The ID before which every snapshot agrees is the xmin horizon. VACUUM can remove a dead version only if the transaction that ended it is older than the horizon. And the horizon is set by the oldest thing that holds a snapshot:

• a long-running query or transaction, including a session left idle in transaction (its backend_xmin in pg_stat_activity);
• a replication slot whose consumer is behind or gone (xmin in pg_replication_slots);
• a replica with hot_standby_feedback = on running a long query, which reports its snapshot to the primary;
• a forgotten prepared transaction (pg_prepared_xacts).

One old snapshot holds back cleanup in every table of the database: a 5-hour transaction on a quiet table still stops vacuum on the busiest one.

If no indexed column changes and the new version fits on the same page, PostgreSQL makes a HOT update: no new index entries, and the old version can be pruned during normal page reads. Compare n_tup_hot_upd with n_tup_upd in pg_stat_user_tables; a lower fillfactor leaves room on each page for HOT updates.

VACUUM, the visibility map and freezing

A plain VACUUM on a table does four jobs in one pass:

1. Removes dead versions older than the horizon: first their index entries, then the versions on the table pages. The space becomes free for new rows on the same table.
2. Records free space in the free space map, so inserts know which pages have room.
3. Updates the visibility map: two bits per page. "All-visible" means every version on the page is visible to everyone. Index-only scans use it to skip the table, and the next vacuum skips those pages. "All-frozen" means every version is also frozen (see below).
4. Freezes old row versions, so their transaction IDs can be reused safely.

Plain VACUUM takes a SHARE UPDATE EXCLUSIVE lock, so reads and writes continue. It does not shrink the file, except for empty pages at the very end. After a 2 million row cleanup, order_line is the same size on disk, with reusable space inside. That is normal: a table settles at a size where vacuum frees about as much as new rows use.

VACUUM FULL is a different tool. It copies the live rows into a brand-new file and drops the old one, which does return space to the OS. But it holds an ACCESS EXCLUSIVE lock the whole time, blocking every read and write, and it needs enough free disk for the full new copy plus its indexes. On the 800 GB order database that means hours of downtime; Upgrades, maintenance and on-call covers the online alternatives.

Freezing and wraparound. Transaction IDs are 32-bit and compared in a circle: about 2 billion behind count as the past, 2 billion ahead as the future. With orders, rider updates and jobs, the order database uses millions of IDs a day, so the circle comes round within a year or two. Before an old xmin could look like the future, vacuum freezes the row version: it marks it as visible to everyone, forever. Each table records its oldest unfrozen ID in pg_class.relfrozenxid; age(relfrozenxid) says how many transactions ago that was.

The settings form a ladder, checked for each table:

• vacuum_freeze_min_age (50 million): versions older than this get frozen when vacuum visits their page.
• vacuum_freeze_table_age (150 million): above this age, vacuum becomes aggressive and reads every page that is not already all-frozen.
• autovacuum_freeze_max_age (200 million): autovacuum starts an anti-wraparound vacuum even if autovacuum is turned off. It shows as autovacuum: VACUUM public.order_line (to prevent wraparound) and, unlike normal autovacuum, it does not give way when a migration or another session asks for a conflicting lock.
• vacuum_failsafe_age (1.6 billion, PostgreSQL 14 and later): vacuum drops its speed limits and skips index cleanup to finish freezing as fast as it can.

If freezing cannot finish (usually because an old snapshot, slot or prepared transaction holds the horizon), PostgreSQL logs warnings when about 40 million IDs remain, and refuses to assign new transaction IDs when about 3 million remain (the exact margins are version dependent). At that point every write fails until someone vacuums, which on order_line could take hours. Alert on age(datfrozenxid) far earlier, around 500 million to 1 billion.