The Process Model

When app-01 opens a connection to pg-primary, Postgres forks an operating-system process for it. One process per connection, not one thread — a real process with its own address space, its own memory accounting, and its own line in ps. Around those backends sits a small crew of background processes that accept no client connections at all, and all of them meet in one shared region of memory that holds the buffer cache and the lock tables.

That picture is worth holding in your head, because almost every operational limit in this book traces back to it. Why 400 application connections cost more than 400 threads would. Why work_mem is not a server-wide budget. Why a session showing idle in transaction is the most expensive idle thing in the system. Why killing one backend with the wrong signal disconnects everybody.

The Postmaster and Its Children

One supervisor process, the postmaster, owns the data directory, listens on port 5432, and creates a backend process for each accepted connection. From that point the client and its backend talk to each other without the postmaster in the path. The backend parses, plans and executes every query for that session, holds the session's settings and temporary tables, and exits when the client disconnects. The supervisor keeps running, waiting for the next connection.

Because a session is a process rather than a thread, the operating system can see it. Its memory shows up in ps and in a cgroup accounting file, the OOM killer can pick it, and a crash inside it cannot scribble on another session's private memory. Postgres labels each process title so a plain ps is already a monitoring tool.

# on pg-primary
$ ps auxww | grep ^postgres
postgres   911 ... /usr/lib/postgresql/18/bin/postgres -D /var/lib/postgresql/18/main
postgres   917 ... postgres: checkpointer
postgres   918 ... postgres: background writer
postgres   919 ... postgres: walwriter
postgres   920 ... postgres: autovacuum launcher
postgres  4471 ... postgres: cartwheel_app cartwheel 10.0.1.21 idle
postgres  4482 ... postgres: cartwheel_app cartwheel 10.0.1.22 SELECT

The first line is the postmaster with its data directory. The four beneath it are auxiliary processes that exist whether or not anyone is connected. The last two are backends, and their titles follow a fixed shape: connecting role, database, client host, and current activity. That final field changes as the session works — idle while it waits for the next command, a command name such as SELECT while it runs one, and idle in transaction when the client has opened a transaction and then gone quiet. The word waiting is appended when the backend is stuck behind another session's lock.

One Process per Connection, and What It Costs

A backend is not free before it does any work. It maps the shared memory region, builds its own relation cache and catalogue cache as it touches objects, and occupies a slot in the shared arrays that Postgres sizes at startup from max_connections. That parameter can only be set at server start precisely because those allocations happen once, when the postmaster claims its shared memory.

The default max_connections is 100. Cartwheel runs 200, and app-01 and app-02 between them try to open 400. The failure is not subtle: connections past the ceiling are refused outright, and the ones that do get in are 200 processes competing for CPU time slices and for L3 cache on a 16-vCPU box that would run the same workload comfortably on 40 busy backends. Idle connections are not harmless either, because each one still holds its caches and its slot.

This is the whole argument for a connection pooler, and pgbouncer-01 arrives in Chapter 10 to make it. The pooler turns 400 application connections into a few dozen real backends that are almost always doing work. Raising max_connections to 400 instead removes the error message and leaves 400 processes on 16 vCPUs.

The Background Crew

The auxiliary processes each own one job, and every one of them reappears later as a tuning decision. The checkpointer flushes dirty shared buffers to disk at checkpoints, which is the operation Chapter 12 spends a topic pacing. The background writer trickles dirty pages out between checkpoints so backends rarely have to write a page themselves before they can read one. The WAL writer pushes write-ahead log records from shared memory to the log files. The autovacuum launcher is always present and starts autovacuum workers on tables that have accumulated enough dead rows, which is the entire subject of Chapter 7.

A second set appears once the system leaves one box. The WAL archiver copies completed WAL segments to archive storage, which is what makes point-in-time recovery possible in Chapter 12. A WAL sender is a special backend that streams WAL over the network to a replica, where a WAL receiver hands it to the startup process for replay — the same startup process that replays WAL after a crash. Chapter 13 puts pg-replica-a and pg-replica-b on the other end of those.

PostgreSQL 18 added one more group. An asynchronous I/O subsystem lets a backend queue several read requests instead of blocking on each in turn, which mainly helps sequential scans, bitmap heap scans and vacuum. The default io_method is worker, meaning the reads are executed by a small pool of I/O worker processes, with io_workers defaulting to 3, and the new pg_aios view shows what is in flight. On Linux kernels with io_uring available, io_method = io_uring submits the same requests without the extra processes.

Shared Memory Is the Meeting Point

Everything the processes need to agree on lives in one shared memory region that every backend maps at startup. shared_buffers is the largest piece of it: the database's own page cache, sized at 128 MB by default, which is still what pg-primary runs on its 64 GB of RAM. A page read into it by one backend is immediately available to all the others, which is why a second query for the same block of orders does no I/O at all.

The rest of the region is smaller and just as load-bearing. The WAL buffers hold log records on their way to disk. The shared lock table has room for max_locks_per_transaction objects per backend, 64 by default, and like max_connections it is sized once at server start, so a transaction that touches a partitioned table with hundreds of partitions can exhaust it. The cumulative statistics that pg_stat_user_tables and friends report are accumulated in shared memory too; a clean shutdown writes them out, and an unclean one resets every counter to zero.

Where a Query's Memory Comes From

Shared memory is fixed at startup. Query memory is not. Sorts, hash joins, hash aggregates and memoize nodes allocate from work_mem, and the documentation is explicit that the grant is per operation: a complex query can run several sorts and hashes at once, each allowed its own work_mem before it spills to temporary files, and several sessions can be doing that concurrently. Hash-based nodes get more still, because hash_mem_multiplier defaults to 2.0 and multiplies the base for them.

The number that matters is therefore not the setting. It is the setting times the number of memory-hungry nodes in the plan times the number of backends running such plans at the same time.

-- default: 4MB. Cartwheel's analytics role wants more.
ALTER ROLE cartwheel_analytics SET work_mem = '256MB';

-- one report: 3 sorts + 1 hash join (hash_mem_multiplier = 2.0)
-- worst case for ONE backend: 3 * 256MB + 1 * 512MB = 1.25GB

Granting 256 MB to the reporting role alone is a reasonable trade: one analyst running one report can reach a gigabyte and a quarter, and there is one analyst. Setting the same 256 MB globally on a server with 200 possible backends is a different statement entirely: it authorizes 200 backends to reach for a gigabyte and a quarter each, on a machine with 64 GB, and the machine finds out during the Saturday peak rather than during the change review.

Reading the Machine From Outside

pg_stat_activity shows the same process list from inside the database, with the query text, the wait event and the state attached. Its state column takes a fixed set of values, and two of them matter operationally: active means the backend is executing a query, and idle in transaction means a transaction is open with nothing running inside it.

SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
  FROM pg_stat_activity
 WHERE state = 'idle in transaction'
   AND xact_start < now() - interval '5 minutes'
 ORDER BY xact_age DESC;

Every row this returns is a session that has been holding an open transaction, and therefore an open snapshot, for more than five minutes while doing nothing at all. A snapshot that old pins the oldest row version vacuum is allowed to remove, so dead rows accumulate across every table in the cartwheel database while that connection sits there. Chapter 6 goes into what that snapshot actually is and Chapter 7 into what it costs; the usual source is a framework that opens a transaction when it checks a connection out of its pool and closes it when the request ends, whether or not the request touched the database.