AHMED ZBYR

AHMED ZBYR

Biker, EV Enthusiast

Welcome to Part 1 of our PostgreSQL Internals & Architecture Series.

When a production PostgreSQL database suddenly slows to a crawl, runs out of memory, or hits connection limits, guessing at configuration knobs in postgresql.conf rarely works. To tune, debug, and scale Postgres with confidence, you first need a clear mental model of what actually lives in processes and RAM when PostgreSQL is running.

In this first installment of the series, we will open the hood of a running PostgreSQL instance and trace:

  1. The Process Model: Why Postgres spawns a dedicated OS process per connection, how postmaster orchestrates the cluster, and what every background worker (bgwriter, checkpointer, walwriter, autovacuum, archiver, and stats) actually does.
  2. Shared Memory: How shared_buffers caches 8 KB pages, how the clock-sweep algorithm and buffer rings prevent a giant SELECT * from wiping out your cache, and how wal_buffers, CLOG (pg_xact), and the three-tier lock hierarchy coordinate concurrency.
  3. Backend Local Memory: How work_mem, maintenance_work_mem, and temp_buffers work—and why a misunderstanding of work_mem is the #1 cause of Linux Out-of-Memory (OOM) kills on Postgres servers.

High-Level Architecture Overview

Before examining each component individually, let’s look at the big picture of a running PostgreSQL cluster:

flowchart TB
    subgraph Clients["Client Applications"]
        C1["App Client 1"]
        C2["App Client 2"]
    end

    subgraph Processes["PostgreSQL OS Processes"]
        PM["postmaster (Main Supervisor Daemon)"]
        B1["Backend Process 1 (work_mem, temp_buffers)"]
        B2["Backend Process 2 (work_mem, temp_buffers)"]
        BG["bgwriter"]
        CK["checkpointer"]
        WW["walwriter"]
        AV["autovacuum launcher & workers"]
        AR["archiver"]
    end

    subgraph SharedMem["Shared Memory Segment (RAM)"]
        SB["Shared Buffers (8KB Pages + Clock-Sweep)"]
        WB["WAL Buffers"]
        CL["CLOG / pg_xact (Transaction States)"]
        LK["Lock Tables (Heavyweight, LWLocks, Spinlocks)"]
    end

    subgraph Storage["Persistent Disk Storage ($PGDATA)"]
        DF["Heap & Index Data Files (base/)"]
        WF["WAL Segments (pg_wal/)"]
        WA["WAL Archive Storage"]
    end

    C1 -->|"TCP / Unix Socket"| PM
    C2 -->|"TCP / Unix Socket"| PM
    PM -.->|"fork()"| B1
    PM -.->|"fork()"| B2

    B1 <--> SB
    B2 <--> SB
    B1 --> WB
    B2 --> WB
    B1 <--> CL
    B1 <--> LK

    BG -->|"Flush Dirty Pages"| DF
    CK -->|"Checkpoint All Dirty Pages"| DF
    WW -->|"Flush WAL Records"| WF
    AV <--> SB
    WF --> AR
    AR -->|"Copy Completed Segments"| WA

If your reader does not render Mermaid diagrams, here is the same architecture in plain text:

   [ Client 1 ]          [ Client 2 ]
        |                     |
        +----------+----------+
                   | (Initial Handshake on Port 5432)
                   v
        +---------------------+
        |     postmaster      |  (Main Supervisor Daemon)
        +---------------------+
           /               \     (fork() per connection)
          v                 v
+-------------------+ +-------------------+
| Backend Process 1 | | Backend Process 2 |
| - work_mem        | | - work_mem        |  <-- Private Backend Memory
| - temp_buffers    | | - temp_buffers    |
+-------------------+ +-------------------+
          |                 |
==========|=================|=======================================
          v                 v            SHARED MEMORY (RAM)
+------------------------------------------------------------------+
|  [ Shared Buffers (8KB Data/Index Pages, Clock-Sweep, Rings) ]   |
|  [ WAL Buffers ]   [ CLOG / pg_xact ]   [ Lock Tables & LWLocks ]|
+------------------------------------------------------------------+
   |              |               |                |
===|==============|===============|================|================
   v              v               v                v   BACKGROUND
[bgwriter]  [checkpointer]  [walwriter]  [autovacuum workers]
   |              |               |                |
   +------+-------+               v                |
          |               [pg_wal/ Disk]           |
          v                       |                |
  [base/ Data Files]         [archiver]            |
          ^                       |                |
          +-----------------------+----------------+

1. The Process Model (Process-per-Connection)

Unlike MySQL (mysqld) or SQL Server, which use a single multi-threaded process where every client connection is a thread, PostgreSQL uses a multi-process architecture. Every active client connection and background task is a separate operating system process managed by the OS kernel scheduler.

Why did PostgreSQL choose processes over threads?

  • Crash Isolation: If a buggy C extension causes a segmentation fault inside a thread-based database, the entire database server crashes immediately. In Postgres, only that single backend process crashes; postmaster detects the abnormal exit, safely rolls back in-flight state via crash recovery, and restarts cleanly.
  • Memory Protection: Each backend process has its own private virtual address space for sorting and hashing (work_mem), while deliberately sharing only the coordinated Shared Memory region.

Architectural Trade-off: Because each OS process consumes ~5–10 MB of baseline overhead and forces the CPU to context-switch across process tables when max_connections climbs into the thousands, high-concurrency production setups place a lightweight connection pooler (PgBouncer or PgCat) in front of Postgres to multiplex thousands of application connections onto 50–200 active Postgres backend processes.

Let’s meet every process in the Postgres family.

1.1 postmaster (The Main Supervisor Daemon)

The postmaster is the parent process of the entire PostgreSQL instance (always the lowest PID in the cluster).

  • What it does:
    1. Allocates and initializes the shared memory segment on startup.
    2. Listens for incoming client connections on TCP port 5432 (and local Unix domain sockets).
    3. Whenever a client connects, postmaster performs the initial startup handshake and calls fork() to spawn a dedicated Backend Process for that client.
    4. Spawns and monitors all background auxiliary processes (bgwriter, checkpointer, walwriter, autovacuum launcher, etc.). If a background worker dies unexpectedly, postmaster restarts it or initiates cluster-wide crash recovery.
  • What it never does: postmaster never parses SQL or reads table files itself—keeping it insulated from query-level bugs.

1.2 Backend Processes (Client Query Executors)

Once postmaster forks a Backend Process (postgres: user dbname client_ip(port) idle), that backend talks directly to the client for the lifetime of the connection:

  • Runs the full query pipeline: Parser → Rewriter → Planner/Optimizer → Executor.
  • Reads and modifies 8 KB pages inside Shared Buffers.
  • Allocates private Backend Local Memory (work_mem, temp_buffers) for sorting, hashing, and temporary tables.
  • Terminates and releases all private RAM back to the OS when the client disconnects.

1.3 Background Workers & Auxiliary Processes

To keep client Backend Processes fast, PostgreSQL offloads disk flushing, cleanup, and maintenance to dedicated background processes:

Process Name Role in the Cluster Key Tuning Parameters
bgwriter (Background Writer) Continuously scans shared_buffers and writes a small batch of “dirty” (modified) pages to disk so client queries rarely have to pause and flush a dirty page themselves just to free up RAM. bgwriter_delay (default 200ms), bgwriter_lru_maxpages (100)
checkpointer Periodically flushes all dirty pages in shared_buffers to disk up to a specific point in time, establishing a safe REDO point so crash recovery only has to replay recent WAL logs. Spreads writes smoothly over time to avoid I/O spikes. checkpoint_timeout (5min–30min), max_wal_size, checkpoint_completion_target (0.9)
walwriter Periodically flushes the in-memory WAL Buffers to the pg_wal/ disk files. Especially critical when using synchronous_commit = off, allowing commits to return instantly while walwriter batches disk fsync calls every few milliseconds. wal_writer_delay (200ms), wal_writer_flush_after (1MB)
autovacuum launcher & workers The launcher wakes up every autovacuum_naptime (1min) and spawns autovacuum worker processes on tables with dead rows. Workers reclaim space from UPDATE/DELETE dead tuples, update the Visibility Map & Free Space Map, refresh planner statistics (ANALYZE), and prevent Transaction ID (XID) wraparound. autovacuum_max_workers (3—often raised to 6–10), autovacuum_naptime
archiver (PgArch) Awakened whenever a 16 MB WAL segment in pg_wal/ fills up and switches. Executes archive_command or archive_library (e.g., pgBackRest or WAL-G) to ship the WAL file to object storage (S3/GCS) for Point-in-Time Recovery (PITR). archive_mode, archive_command, archive_timeout
stats collector / Shared Memory Stats Tracks table row counts, index scans, dead tuples, and I/O hits (pg_stat_user_tables, pg_stat_io). (Note: In PostgreSQL 14 and older, this was a separate stats collector process communicating over a local UDP socket; in PostgreSQL 15+, stats are written directly to shared memory with zero process overhead!) track_activities, track_counts, track_io_timing

Inspecting All Running Processes via SQL

You can see every background process and client backend running inside your Postgres instance right now by querying pg_stat_activity:

SELECT
    pid,
    backend_type,
    usename,
    datname,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
ORDER BY backend_type, pid;

2. Shared Memory Architecture

Because PostgreSQL runs dozens or hundreds of separate OS processes, those processes need a single, unified workspace in RAM where they can see the same table pages, transaction statuses, and locks. That workspace is the Shared Memory Segment, allocated once by postmaster at startup.

2.1 Shared Buffers (shared_buffers)

PostgreSQL stores tables and indexes on disk as arrays of fixed-size 8 KB pages (blocks). A Backend Process never modifies an 8 KB page directly on disk—it first loads the 8 KB page into shared_buffers in RAM, reads or modifies it there, and marks the buffer “dirty.”

  • Rule of Thumb Sizing: Typically set to 25% to 40% of total system RAM on dedicated database servers (because Postgres also relies on the Linux OS kernel page cache for the remaining RAM).

How Postgres Decides Which Page to Evict: The Clock-Sweep Algorithm

When shared_buffers is full and a query needs to read a new 8 KB page from disk, which cached page should Postgres evict?

A naive Least Recently Used (LRU) linked list requires moving a page to the front of a global list on every single read—which creates a massive lock bottleneck when 64 CPU cores are reading pages simultaneously.

Instead, PostgreSQL uses the Clock-Sweep Algorithm (a lock-free-friendly approximation of LRU):

                  Usage Count (0 to 5)
                     [Page A: u=3]
                    /             \
       [Page F: u=0]               [Page B: u=1]
        (EVICT ME!)      ^          
                    \     \       /
                     \     \     /
                      \  Clock Hand
                       \
         [Page E: u=2] --- [Page D: u=5]

Every 8 KB slot in shared_buffers has a lightweight header (Buffer Descriptor) containing two key counters:

  1. refcount (Pin Count): How many backend processes are currently reading or modifying this page right this microsecond. If refcount > 0, the page is pinned and cannot be evicted under any circumstance.
  2. usage_count (Popularity Score, 0 to 5): Every time any query touches the page, its usage_count is incremented by 1 (capped at a maximum of 5 so an old table that was hot yesterday doesn’t stay immortal forever).

When a backend needs a free buffer slot, the Clock Hand sweeps around the circular array of buffer descriptors:

  • If the page has refcount > 0 (pinned), skip it.
  • If the page has usage_count > 0, subtract 1 from usage_count and move the clock hand to the next buffer.
  • The first unpinned page the clock hand finds with usage_count = 0 is chosen as the victim and replaced!

Buffer Rings: Why a 500 GB SELECT * Doesn’t Wipe Out Your Cache!

Imagine you have shared_buffers = 16GB holding your hottest customer and order indexes. Suddenly, an analyst runs SELECT * FROMaudit_logs on a 500 GB table (a Sequential Scan).

If that sequential scan loaded every 8 KB block into the main shared_buffers pool, it would push out every single hot index page in your database (cache pollution)!

To prevent this, PostgreSQL automatically isolates bulk operations inside a tiny Buffer Ring (a small circular loop of buffers inside shared_buffers that immediately reuses its own slots instead of evicting the rest of the cache):

Operation Type Internal Strategy Name Default Ring Buffer Size Behavior
Large Sequential Scans (when table > shared_buffers / 4) BAS_BULKREAD 256 KB (32 pages of 8 KB) The sequential scan reuses the same 32 pages in a loop, leaving 99.99% of shared_buffers untouched!
COPY FROM / CREATE TABLE AS SELECT BAS_BULKWRITE 16 MB (2,048 pages) Buffers bulk inserts in a 16 MB ring and flushes them out without polluting the read cache.
VACUUM / AUTOVACUUM BAS_VACUUM 256 KB (or vacuum_buffer_usage_limit, default 2 MB in PG 16+) Keeps background vacuuming from evicting hot application pages.

2.2 WAL Buffers (wal_buffers)

Before PostgreSQL allows a dirty page in shared_buffers to be written to disk—or before it tells a client that a COMMIT succeeded—it must record the change in the Write-Ahead Log (WAL).

Rather than making a slow disk write on every single row change, backend processes append their WAL records into wal_buffers in shared memory:

  • By default (wal_buffers = -1), Postgres automatically sizes wal_buffers to 1/32 of shared_buffers (up to a maximum of 16MB, matching the size of a single WAL segment file).
  • The buffer is flushed to disk (pg_wal/) whenever a transaction commits (if synchronous_commit = on), when walwriter wakes up, or when the buffer wraps around.

2.3 CLOG (Commit Log / pg_xact)

Because PostgreSQL uses MVCC (Multi-Version Concurrency Control), multiple versions (tuples) of the same row can exist on a page at the same time—each stamped with xmin (the Transaction ID that inserted it) and xmax (the Transaction ID that deleted/updated it).

When your query looks at a row with xmin = 482910, how does Postgres know whether transaction 482910 committed, rolled back (aborted), or is still running?

It checks the CLOG (Commit Log, stored on disk in $PGDATA/pg_xact and cached in shared memory):

  • CLOG stores only 2 bits per Transaction ID (XID), representing four possible states:
    • 00 = IN_PROGRESS
    • 01 = COMMITTED
    • 10 = ABORTED
    • 11 = SUB_COMMITTED (a subtransaction / SAVEPOINT committed, but parent transaction hasn’t finished yet)
  • Because each transaction takes only 2 bits, a single 8 KB CLOG page in shared memory holds the status of 32,768 transactions!
  • Optimization (Hint Bits): Once a backend checks CLOG and confirms that xmin = 482910 is committed, it stamps a Hint Bit (HEAP_XMIN_COMMITTED) directly onto the row header in shared_buffers so future queries never have to look up 482910 in CLOG again.

2.4 Lock Tables: The 3-Tier Synchronization Hierarchy

Not all locks in PostgreSQL are created equal. Postgres uses three distinct lock mechanisms depending on whether it is protecting a single byte in RAM for 10 nanoseconds or a table row for 10 seconds:

flowchart LR
    S["1. Spinlocks\n(CPU Atomic TAS/CAS)\nHold time: Nanoseconds\nProtects: Single structs/counters"] --> LW["2. LWLocks (Lightweight Locks)\n(Shared / Exclusive Queue)\nHold time: Microseconds\nProtects: Shared Memory Pages & Hash Tables"]
    LW --> HW["3. Heavyweight Locks\n(LockManager / pg_locks)\nHold time: Milliseconds to Hours\nProtects: SQL Tables, Rows, Transactions"]
Lock Tier What It Protects Features & Overhead How to Observe It
1. Spinlocks Tiny shared-memory fields (e.g., buffer descriptor header flags, free-list pointers). Uses CPU hardware atomic instructions (TAS / CAS). Busy-waits (spins) in a loop—never sleeps. Held for only a few dozen CPU instructions. No deadlock detection. wait_event_type = 'SpinDelay' in pg_stat_activity
2. Lightweight Locks (LWLocks) Shared memory data structures (e.g., BufferMapping hash table, WALInsert locks, reading/writing an 8 KB buffer page). Supports Shared (multiple readers) and Exclusive (single writer) modes. Puts waiting processes to sleep on an OS semaphore queue. No deadlock detection. wait_event_type = 'LWLock' in pg_stat_activity
3. Heavyweight Locks (LockManager) SQL-level objects: Tables, Pages, Tuples (rows), and Transaction IDs (SELECT FOR UPDATE, ALTER TABLE). 8 lock modes (ACCESS SHARE to ACCESS EXCLUSIVE), full deadlock detection (deadlock_timeout = 1s), held until transaction COMMIT/ROLLBACK. Querying the pg_locks system view

3. Backend Local Memory (And the work_mem Trap!)

In addition to shared memory, every individual Backend Process allocates its own private memory in RAM for query execution. This memory is not shared with other connections.

3.1 work_mem (The Per-Operation Multiplier!)

Whenever a query needs to sort rows or build a hash table in memory, the backend process allocates work_mem (default: 4MB).

Operations that use work_mem include:

  • ORDER BY, DISTINCT, and MERGE JOIN sorts (QuickSort / Top-N Heapsort)
  • HASH JOIN and HashAggregate (GROUP BY) hash tables
  • BitmapHeapScan bitmap creation when combining multiple indexes

⚠️ The #1 Production Pitfall: work_mem Is Allocated Per Plan Node, NOT Per Connection!

Many DBAs see a server with 64 GB of RAM and max_connections = 200, do quick math (200 × 100MB = 20GB), and set work_mem = 100MB globally. Days later, the Linux OOM Killer crashes the entire database.

Why? Because work_mem can be allocated multiple times inside a single query, and multiplied again by parallel query workers:

Max Query RAM = (Number of Sort/Hash Nodes in Query Plan)
                × (1 Leader + max_parallel_workers_per_gather)
                × (hash_mem_multiplier, default 2.0 in PG 13+)
                × work_mem
  • If a complex reporting query has 4 Hash Joins and uses 4 Parallel Workers, that single query can allocate up to 4 × 5 × 2.0 × 100MB = 4,000 MB (4 GB) of RAM!
  • If 20 users run that report at the same time, they will demand 80 GB of private RAM on top of shared_buffers.

How to Spot When work_mem Is Too Small (Disk Spills)

If a sort or hash table exceeds work_mem, Postgres doesn’t fail—it quietly spills temporary files to disk (base/pgsql_tmp), which makes the query 10x–50x slower.

You can see this immediately using EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

Look at the Sort Method line in the output:

  • Bad (Spilling to Disk because work_mem is too small): 
    Sort Method: external merge  Disk: 64280kB
  • Good (Fitting cleanly in RAM after raising work_mem): 
    Sort Method: quicksort  Memory: 48120kB

Pro Tip: Keep global work_mem conservative (16MB–64MB on OLTP systems), and boost it only for heavy reporting sessions or specific queries using SET LOCAL:

BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ... ; -- Heavy analytical query runs entirely in RAM
COMMIT;      -- work_mem automatically resets to the safe default!

3.2 maintenance_work_mem (and autovacuum_work_mem)

While work_mem is used by everyday SELECT/INSERT queries, maintenance_work_mem (default: 64MB) is allocated only by heavy maintenance operations:

  • VACUUM / AUTOVACUUM (to store the array of dead tuple IDs before scanning indexes)
  • CREATE INDEX and REINDEX (sorting index keys before writing B-Tree pages)
  • ALTER TABLE ADD FOREIGN KEY

Because a server rarely runs dozens of CREATE INDEX commands simultaneously, it is safe and recommended to set maintenance_work_mem much higher than work_mem—typically 512MB to 2GB.

(Note: Autovacuum workers also use maintenance_work_mem per worker unless you explicitly set autovacuum_work_mem.)

3.3 temp_buffers

When a session creates a temporary table (CREATE TEMP TABLE my_staging ...), those pages are never placed in shared_buffers because no other connection can ever see a session’s temp table.

Instead, each backend caches its own temporary table pages in temp_buffers (default: 8MB):

  • Lazy Allocation: Postgres does not allocate temp_buffers when a client connects—it allocates pages up to temp_buffers only if and when that session actually uses a temporary table.
  • Because temp tables live in backend-local memory, they require zero LWLocks or spinlocks, making small in-memory temp tables remarkably fast.

Summary & Production Tuning Cheat Sheet

Here is a quick-reference guide for how all the pieces fit together on a typical 64 GB RAM production PostgreSQL server:

Parameter / Component Memory Scope Default Value Recommended Starting Point (64 GB RAM Server) Primary Purpose
shared_buffers Shared (Cluster-wide) 128MB 16GB – 24GB (~25%–35% of RAM) Caches 8 KB table and index pages across all connections.
wal_buffers Shared (Cluster-wide) -1 (Auto) 16MB (Auto-tuned) Stages Write-Ahead Log records in RAM before flushing to pg_wal/.
work_mem Local (Per Plan Node!) 4MB 16MB – 64MB (Use SET LOCAL for big reports) In-memory sorts (ORDER BY), Hash Join, and GROUP BY hash tables.
maintenance_work_mem Local (Per Maintenance Job) 64MB 1GB – 2GB Speeds up CREATE INDEX, VACUUM, and ALTER TABLE.
temp_buffers Local (Per Session using Temp Tables) 8MB 16MB – 64MB Caches session-local CREATE TEMP TABLE pages in RAM.

Coming Up Next in the PostgreSQL Deep Dive Series

Now that we know how PostgreSQL organizes its Processes and Memory, in Part 2 of this series we will dive down to the disk layer: Storage Internals, Heap Page Layout, MVCC Tuple Visibility (xmin/xmax), HOT Updates, and How VACUUM Prevents Transaction ID Wraparound.

References & Further Reading

  1. PostgreSQL 17 Official Documentation: Chapter 19: Server Configuration — Resource Consumption (Memory).
  2. Hironobu Suzuki: The Internals of PostgreSQL — Chapter 1 (Cluster & Databases) & Chapter 8 (Buffer Manager).
  3. Egor Rogov (Postgres Professional): PostgreSQL 14 Internals — Part I (Isolation & MVCC) and Part II (Buffer Cache & WAL).
  4. PostgreSQL Source Tree (src/backend/storage/buffer/freelist.c): Upstream implementation of the ClockSweepTick() page replacement algorithm and GetAccessStrategy() ring buffers (BAS_BULKREAD, BAS_BULKWRITE, BAS_VACUUM).

You May Also Enjoy