PostgreSQL follows a client-server, multi-process architecture. Unlike MySQL's multi-threaded model or Oracle's mix of threads and processes, PostgreSQL spawns a dedicated OS process for each client connection. The architecture has three distinct layers: the client application layer, the server process layer, and the storage layer.

When PostgreSQL starts, the Postmaster process launches and listens on port 5432. For every new client connection, Postmaster calls fork() to create a dedicated Backend process. That backend handles the entire session lifecycle — parsing queries, planning, executing, and returning results — all within its own private process address space.

Every piece of data in PostgreSQL is ultimately stored as 8KB pages in heap files. A table is a collection of heap pages. An index is a separate collection of index pages. The storage is entirely on the filesystem — there is no raw device mode.

A SELECT statement passes through 5 stages inside the backend process before results reach the client. No WAL is written for a pure SELECT. Data is served from shared buffers if cached, or read from disk if not.

An INSERT must write both the WAL record and the tuple into a shared buffer page. The page is written to disk lazily (by bgwriter or checkpointer) — not immediately on INSERT. The WAL record however must be flushed to disk before the COMMIT returns (when synchronous_commit = on).

This is one of the most important things to understand about PostgreSQL: UPDATE does not modify the row in place. Instead, it creates a brand new row version and marks the old row as "dead". This is the foundation of MVCC — multiple transactions can read different versions of the same row concurrently without blocking each other.

DELETE in PostgreSQL is purely logical. No data is physically removed from the page when you DELETE. The row is simply marked as deleted by setting t_xmax. The physical space is reclaimed only when VACUUM runs.

MVCC is the mechanism that allows concurrent readers and writers to never block each other. Each transaction sees a consistent snapshot of the database as of its start time (or statement start time, depending on isolation level). The visibility of a tuple is determined entirely by its t_xmin and t_xmax values.

WAL is the backbone of PostgreSQL's durability and replication story. The principle is simple: write the log record before changing the data. This guarantees that even if PostgreSQL crashes mid-write, no committed data is lost — recovery replays the WAL to reconstruct any missing changes.

# WAL files in $PGDATA/pg_wal/
ls -la $PGDATA/pg_wal/

# Filename format: 000000TT XXXXXXXX YYYYYYYY
# TT = Timeline ID (increases on failover/PITR)
# XXXXXXXX YYYYYYYY = 64-bit LSN (Log Sequence Number) split

000000010000000000000091    # Timeline 1, LSN segment 0x91
000000010000000000000092    # Timeline 1, LSN segment 0x92
000000010000000000000093    # After DROP TABLE — segment 0x93

# Each WAL segment = 16MB (default wal_segment_size)

# Check current WAL position (LSN)
SELECT pg_current_wal_lsn();        -- e.g., 0/93007FF0
SELECT pg_walfile_name(pg_current_wal_lsn());  -- convert LSN to filename

# Measure WAL generated in last 5 minutes
SELECT pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), '0/91000000')
) AS wal_since_checkpoint;