PostgreSQL Internals

How Postgres actually stores rows, orders concurrent writers, and keeps a replica in sync underneath psql — storage layout, MVCC and vacuum, WAL and replication durability, connection pooling, and where the query planner's cost model can go wrong.

Storage Architecture

graph TD
    classDef proc fill:#2c3e50,stroke:#1a252f,color:#fff
    classDef mem fill:#3498db,stroke:#2471a3,color:#fff
    classDef wal fill:#e67e22,stroke:#ba6018,color:#fff
    classDef disk fill:#7f8c8d,stroke:#616a6b,color:#fff
    classDef bg fill:#8e44ad,stroke:#6c3483,color:#fff

    CLIENT["Client connection"] --> PROC["Backend process<br/>one forked per connection<br/>~10MB RSS each"]:::proc

    subgraph SHMEM["Shared Memory — shared across every backend"]
        BP["Buffer Pool (shared_buffers)<br/>8KB pages, clock-sweep eviction<br/>cache of heap/index pages"]:::mem
        WAL_BUF["WAL Buffers (wal_buffers)<br/>staging area before fsync"]:::wal
        LOCK["Lock table<br/>row-level, table-level, advisory"]:::mem
    end

    PROC -->|"read / modify page"| BP
    PROC -->|"append WAL record<br/>before touching the page"| WAL_BUF
    PROC -.->|"acquire before touching<br/>a row / table"| LOCK

    subgraph BGPROC["Background processes"]
        BGWRITER["bgwriter<br/>trickles dirty pages out early<br/>so checkpoints have less to flush"]:::bg
        CHECKPOINTER["checkpointer<br/>runs every checkpoint_timeout<br/>or max_wal_size worth of WAL"]:::bg
        WALWRITER["wal writer<br/>flushes WAL buffers on a timer,<br/>independent of any one COMMIT"]:::bg
    end

    BP -.->|"dirty pages"| BGWRITER --> HEAP
    BP -->|"flush all dirty pages"| CHECKPOINTER --> HEAP
    WAL_BUF --> WALWRITER --> WAL_FILES

    subgraph DISK["Disk"]
        HEAP["Heap files<br/>base/16384/12345<br/>8KB pages, unordered rows"]:::disk
        IDX["Index files<br/>B-tree, GiST, GIN, BRIN"]:::disk
        WAL_FILES["WAL segment files<br/>pg_wal/*.wal<br/>16MB each, sequential writes"]:::wal
        TOAST["TOAST files<br/>columns > 2KB stored separately"]:::disk
    end

    CHECKPOINTER -.->|"WAL before this point<br/>can now be recycled"| WAL_FILES
    HEAP -.-> IDX
    HEAP -.-> TOAST

The key thing this diagram makes explicit: a backend never writes straight to the heap file on disk. It modifies the page in the shared buffer pool and appends a WAL record — both in memory — and returns to the client once the WAL record is durable. Getting the actual heap page onto disk is a separate, asynchronous job handled by bgwriter (proactively, to keep checkpoints cheap) and the checkpointer (on its own schedule), decoupled from any single transaction's commit.

WAL — Write-Ahead Log in Detail

Every modification (INSERT, UPDATE, DELETE) writes a WAL record before the data page is changed.

sequenceDiagram
    participant TX as Transaction
    participant BP as Buffer Pool
    participant WALBUF as WAL Buffer
    participant WALDISK as WAL files (pg_wal/)
    participant CKPT as Checkpointer
    participant DF as Data files (heap)

    TX->>BP: UPDATE row → modify page in buffer pool (in memory only)
    TX->>WALBUF: Write WAL record {LSN, relation, block, old tuple, new tuple}
    Note over WALBUF,WALDISK: synchronous_commit = on
    WALBUF->>WALDISK: fsync WAL to disk before COMMIT is allowed to return
    WALDISK-->>TX: fsync confirmed
    TX-->>TX: COMMIT returns to client

    rect rgb(40, 60, 45)
    Note over BP,DF: Asynchronous — runs on its own schedule, not per-COMMIT
    loop every checkpoint_timeout (default 5min) or max_wal_size worth of WAL
        CKPT->>BP: request all dirty pages
        BP->>DF: flush dirty pages to heap files
        CKPT->>WALDISK: mark WAL segments before this point as recyclable
    end
    end

LSN (Log Sequence Number): 64-bit monotonically increasing number. Every WAL record has an LSN. Replicas track which LSN they've replayed — this is the replication lag.

-- Check current WAL position
SELECT pg_current_wal_lsn();

-- Check replication lag
SELECT
    client_addr,
    sent_lsn - replay_lsn AS lag_bytes,
    EXTRACT(EPOCH FROM (now() - replay_lag)) AS lag_seconds
FROM pg_stat_replication;

Try it yourself — WAL + checkpoint simulator. The sequence diagram above shows one commit's path end-to-end; this one lets you drive many commits and checkpoints yourself and watch the two housekeeping facts that fall out of it: a WAL segment is only safe to delete once a checkpoint has flushed everything it covers to the heap files, and a page counts as "dirty" from the moment it's modified until the next checkpoint clears it — not until the transaction that touched it commits.

MVCC — How PostgreSQL Handles Concurrent Reads/Writes

PostgreSQL never overwrites a row in place. Every UPDATE creates a new row version.

graph TD
    classDef dead fill:#7f8c8d,stroke:#616a6b,color:#fff
    classDef live fill:#27ae60,stroke:#1e8449,color:#fff
    classDef reader fill:#3498db,stroke:#2471a3,color:#fff
    classDef writer fill:#e74c3c,stroke:#c0392b,color:#fff

    subgraph HEAP["Table heap page — same logical row, two physical tuple versions"]
        V1["Tuple version 1<br/>xmin=100 xmax=200<br/>name='Alice'<br/>visible when xmin&lt;=snap&lt;xmax"]:::dead
        V2["Tuple version 2<br/>xmin=200 xmax=NULL<br/>name='Bob'<br/>visible when snap&gt;=200"]:::live
    end

    TX200["Transaction 200 (writer)<br/>UPDATE ... SET name='Bob'<br/>sets xmax=200 on V1, inserts V2"]:::writer
    TX200 -.->|"marks old version dead<br/>(not deleted yet)"| V1
    TX200 -.->|"inserts new version"| V2

    TX150["Transaction 150 (reader)<br/>snapshot xid = 150"]:::reader
    TX250["Transaction 250 (reader)<br/>snapshot xid = 250"]:::reader

    V1 -->|"100&lt;=150&lt;200 → visible"| TX150
    V2 -->|"200&lt;=250 → visible"| TX250

    V1 -.->|"eventually reclaimed by"| VACUUM["VACUUM<br/>marks dead tuple space reusable<br/>once no snapshot can see it"]:::dead

xmin/xmax system columns:

• xmin: transaction ID that created this tuple version
• xmax: transaction ID that deleted/updated this tuple (NULL = still live)
• A tuple is visible if xmin <= my_snapshot_xid < xmax

Try it yourself — MVCC visibility simulator. The stepper above walks one scripted example. This one's live: it models a single row's version chain plus up to two concurrent transactions, so you can drive the same xmin <= my_snapshot_xid < xmax rule against sequences you pick yourself. Begin a transaction to get a snapshot xid, Update to branch the chain (old version's xmax gets set, a new version gets pushed), and watch each transaction's readout to see exactly which version its own snapshot resolves to — including the write-lock and write-conflict cases when two transactions touch the same row at once.

Dead tuples: Old versions accumulate. VACUUM marks them as free space. VACUUM FULL rewrites the table (locks table, reclaims disk).

-- Check table bloat
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup::numeric/NULLIF(n_live_tup+n_dead_tup,0)*100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Force vacuum
VACUUM ANALYZE my_table;
VACUUM VERBOSE my_table;  -- shows what it freed

Isolation Levels and Serializable Snapshot Isolation (SSI)

MVCC (above) is the mechanism — the xmin/xmax bookkeeping that lets each transaction resolve its own view of a row. Isolation level is the policy built on top of it: how much cross-transaction interference a transaction is allowed to see, and how much of that Postgres will actually let happen versus reject.

Postgres implements all four SQL standard levels, but only three behaviors:

• READ UNCOMMITTED. The SQL standard permits dirty reads at this level. Postgres doesn't have dirty reads at all, at any level — a reader's snapshot only ever resolves to a tuple whose creating transaction has already committed, so there's nothing for READ UNCOMMITTED to relax. Requesting it silently gets you READ COMMITTED instead. This is worth calling out explicitly because it surprises people coming from databases where READ UNCOMMITTED is a real, distinct, dangerous mode.
• READ COMMITTED (the default). Each individual statement inside the transaction gets its own fresh snapshot, taken at the moment that statement starts — not one snapshot for the whole transaction. Two SELECTs in the same transaction can legitimately see different committed data if another transaction committed in between them.
• REPEATABLE READ. The whole transaction gets one snapshot, taken at its first statement, and every subsequent statement reuses it. Because Postgres's snapshot isolation is inherently row-based rather than lock-based, this also happens to block phantom reads — stronger than the SQL standard actually requires at this level.
• SERIALIZABLE. Everything REPEATABLE READ does, plus runtime detection of read/write dependency cycles between concurrent transactions that could not have arisen from any serial (one-at-a-time) execution order.

SSI — how SERIALIZABLE is actually implemented. This is the detail that trips people up: Postgres's SERIALIZABLE is not lock-based serializability in the traditional sense (no 2-phase locking, nothing blocks on read/write conflicts as they happen). It's Serializable Snapshot Isolation — REPEATABLE READ's ordinary snapshot mechanism, with an added layer that watches for "dangerous structures": specific patterns of read-write dependencies between concurrent transactions that are the necessary signature of a non-serializable outcome. It tracks these with predicate locks (SIREAD locks) that, unlike a normal lock, never block anything by themselves — a SIREAD lock just records "this transaction's result depended on this data," and the dependency graph built from those records gets checked only when a transaction tries to commit.

What a SERIALIZABLE failure actually looks like. Because the check happens at commit, not at the statement that created the conflicting dependency, the failure surfaces as a 40001 serialization_failure error returned from COMMIT — potentially on a transaction whose every individual statement executed and returned rows successfully. The application has to be prepared to catch that SQLSTATE and retry the entire transaction from its first statement. This isn't an edge case to shrug off — using SERIALIZABLE without a retry loop around it means occasional, load-dependent transaction failures in production that have nothing to do with a bug in the transaction itself.

Try it against the MVCC demo above: begin two overlapping transactions there, and picture SERIALIZABLE sitting on top of exactly that scenario. Nothing about which version each snapshot resolves to changes — what SSI adds is a dependency check at commit time that would refuse to let both transactions commit if doing so could never correspond to running them one after another in either order.

Try it yourself — SSI dependency-graph simulator. The MVCC visibility simulator above answers "which row version does a snapshot see." This one answers a different question: given a set of concurrent transactions and the rows each one reads and writes, does PostgreSQL's SSI implementation consider them dangerous? Add 2–4 transactions, type in which rows each one reads and writes (e.g. A, A, B), and watch the rw-antidependency graph get built live. The important thing to notice: SSI does not abort on just any conflict, or even on any graph cycle — it specifically watches for a pivot transaction sitting between one inbound and one outbound rw-antidependency edge. A lone rw-antidependency edge is normal and harmless under snapshot isolation; only a pivot with both edges present triggers the risk of a 40001 at commit.

Replication — Sync vs Async in Detail

sequenceDiagram
    participant APP as Application
    participant PRI as Primary (postgres-0)
    participant WAL_SEND as WAL Sender process
    participant WAL_RECV as WAL Receiver (replica)
    participant REP as Replica (postgres-1)

    APP->>PRI: BEGIN, UPDATE orders SET status='paid', COMMIT
    PRI->>PRI: Write WAL record to WAL buffer
    PRI->>PRI: Flush WAL to disk (always, for durability)

    rect rgb(40, 55, 75)
    Note over PRI,REP: ASYNC replication (default — synchronous_standby_names unset)
    PRI-->>APP: COMMIT returns immediately
    PRI->>WAL_SEND: Stream WAL to replica (best effort)
    WAL_SEND->>WAL_RECV: WAL data
    WAL_RECV->>REP: Apply WAL records
    Note over REP: Replica may be 0ms to minutes behind
    end

    rect rgb(65, 50, 30)
    Note over PRI,REP: SYNC replication (synchronous_standby_names set)
    PRI->>WAL_SEND: Wait for replica ACK before returning to app
    WAL_SEND->>WAL_RECV: WAL data
    WAL_RECV->>WAL_RECV: Write to replica's WAL (flush to disk)
    WAL_RECV-->>WAL_SEND: ACK (flushed)
    WAL_SEND-->>PRI: Replica confirmed
    PRI-->>APP: COMMIT returns
    Note over REP: Data guaranteed on at least 2 disks before client sees commit
    end

Synchronous commit levels (granular control):

-- Per-transaction override
SET synchronous_commit = 'remote_write';  -- stronger than off, weaker than on

-- Levels:
-- off          → WAL not even flushed locally (fastest, risk data loss on crash)
-- local        → WAL flushed locally only (default)
-- remote_write → replica received WAL in its OS buffer (not fsynced yet)
-- remote_apply → replica has replayed WAL and applied changes
-- on           → replica WAL flushed to disk (same as remote_apply for most uses)

Indexes

graph TD
    classDef btree fill:#3498db,stroke:#2471a3,color:#fff
    classDef gin fill:#8e44ad,stroke:#6c3483,color:#fff
    classDef brin fill:#16a085,stroke:#117a65,color:#fff
    classDef gist fill:#c0392b,stroke:#922b21,color:#fff

    subgraph BTG["B-tree (default)"]
        B["Root node"]:::btree --> L["Leaf nodes<br/>sorted keys + heap pointers<br/>O(log n) lookup, range scans"]:::btree
    end
    subgraph GING["GIN — Generalized Inverted Index"]
        G["Inverted index<br/>key → set of heap locations<br/>Used for: full-text search, jsonb, arrays"]:::gin
    end
    subgraph BRING["BRIN — Block Range Index"]
        BR["Min/max per block range<br/>Tiny index, good for sequential data<br/>timestamps, auto-increment IDs"]:::brin
    end
    subgraph GISTG["GiST — Generalized Search Tree (a framework, not one algorithm)"]
        GS["Balanced tree over union/consistent/distance<br/>Used for: geometric types, tsvector, range overlap"]:::gist
    end

-- B-tree (default): equality + range on most types
CREATE INDEX idx_users_email ON users(email);

-- Partial index: only index rows matching a condition (smaller, faster)
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

-- Composite: multi-column (order matters — most selective first)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- GIN: full-text search
CREATE INDEX idx_posts_fts ON posts USING gin(to_tsvector('english', body));

-- BRIN: large append-only tables (logs, time-series)
CREATE INDEX idx_events_created ON events USING brin(created_at);

-- GiST: range overlap ("find all bookings overlapping this date range")
CREATE INDEX idx_bookings_period ON bookings USING gist(during);
SELECT * FROM bookings WHERE during && tstzrange('2026-08-01', '2026-08-05');

-- Check index usage
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- unused indexes

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

-- Key things to look for:
-- Seq Scan on large table → missing index
-- Nested Loop with many rows → should be Hash Join
-- Buffers: hit=X read=Y → X from cache, Y from disk
-- actual rows >> estimated rows → stale statistics (run ANALYZE)
-- cost=X..Y: X=startup cost, Y=total cost (in arbitrary units)

Connection Pooling

PostgreSQL creates one OS process per connection (~10MB RAM each). At 1000 connections = 10GB just for processes.

graph LR
    classDef app fill:#34495e,stroke:#212f3c,color:#fff
    classDef pooler fill:#8e44ad,stroke:#6c3483,color:#fff
    classDef pgconn fill:#e67e22,stroke:#ba6018,color:#fff
    classDef pg fill:#2980b9,stroke:#1f618d,color:#fff

    subgraph APPTIER["Application tier"]
        APP_PODS["200 app pods<br/>×10 conn each = 2000 client connections"]:::app
    end

    subgraph POOLTIER["PgBouncer"]
        QUEUE["Waiting-client queue<br/>used only if every server<br/>connection is currently busy"]:::pooler
        POOL["Server connection pool<br/>default_pool_size = 50"]:::pgconn
    end

    subgraph PGTIER["PostgreSQL"]
        PG["postgres backends<br/>max_connections = 100"]:::pg
    end

    APP_PODS -->|"2000 client connections"| QUEUE
    QUEUE -->|"assigned per pool_mode's<br/>release point"| POOL
    POOL -->|"only 50 real OS connections"| PG

# pgbouncer.ini
[pgbouncer]
pool_mode = transaction      # connection returned after each transaction (most efficient)
max_client_conn = 10000      # app pods can open many client connections
default_pool_size = 50       # actual PostgreSQL connections
reserve_pool_size = 10       # emergency pool
server_idle_timeout = 300    # close idle server connections after 5min

Key Configuration Parameters

# postgresql.conf
shared_buffers = 4GB              # 25% of RAM for buffer pool
effective_cache_size = 12GB       # planner hint: how much OS cache is available
work_mem = 64MB                   # per-sort, per-hash operation (watch out: can multiply)
maintenance_work_mem = 512MB      # for VACUUM, CREATE INDEX, pg_restore
wal_level = replica               # needed for streaming replication
max_wal_senders = 10              # max replication connections
checkpoint_completion_target = 0.9 # spread checkpoint I/O
random_page_cost = 1.1            # SSD: set to 1.1 (same as seq scan)
effective_io_concurrency = 200    # SSD: number of concurrent I/O requests

Query Planner — How PostgreSQL Chooses Execution Plans

graph TD
    classDef input fill:#34495e,stroke:#212f3c,color:#fff
    classDef frontend fill:#3498db,stroke:#2471a3,color:#fff
    classDef planner fill:#e67e22,stroke:#ba6018,color:#fff
    classDef exec fill:#27ae60,stroke:#1e8449,color:#fff

    SQL["SELECT u.name, count(o.id)<br/>FROM users u JOIN orders o ON o.user_id=u.id<br/>WHERE u.created_at > '2024-01-01'<br/>GROUP BY u.id"]:::input

    subgraph FRONTEND["SQL frontend — one deterministic path"]
        PARSE["Parser: SQL text → parse tree<br/>syntax only, no table lookups yet"]:::frontend
        ANALYZE2["Analyzer: resolve table/column names,<br/>check types against the catalog"]:::frontend
        REWRITE["Rewriter: expand views,<br/>apply rules"]:::frontend
    end

    subgraph OPT["Optimizer — the only step with real choices"]
        PLAN["Planner<br/>enumerate possible plans<br/>estimate cost of each from pg_statistic<br/>choose the cheapest"]:::planner
    end

    EXEC["Executor: run the chosen plan,<br/>pulling rows through each node"]:::exec

    SQL --> PARSE --> ANALYZE2 --> REWRITE --> PLAN --> EXEC

Cost model: The planner assigns a cost (in arbitrary units) to each plan based on:

• seq_page_cost (default 1.0) — cost to read a page sequentially
• random_page_cost (default 4.0, use 1.1 for SSDs) — cost of a random page read
• cpu_tuple_cost (0.01) — cost per row processed
• Row count estimates from pg_statistic (updated by ANALYZE)

Why plans go wrong:

• Stale statistics → wrong row estimates → wrong plan choice
• Run ANALYZE table_name after bulk loads
• autovacuum runs ANALYZE automatically but may lag

-- Force statistics update
ANALYZE users;

-- See planner's row estimates vs actual
EXPLAIN (ANALYZE, FORMAT TEXT) SELECT * FROM users WHERE email = 'alice@example.com';
-- rows=1 (estimate) vs rows=1 (actual) ← good
-- rows=1000 (estimate) vs rows=1 (actual) ← bad — stale stats, will choose wrong plan

-- Increase statistics target for skewed columns
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; -- default 100
ANALYZE orders;

Join Types — When Planner Uses Each

graph TD
    classDef decision fill:#34495e,stroke:#212f3c,color:#fff
    classDef nl fill:#3498db,stroke:#2471a3,color:#fff
    classDef hash fill:#e67e22,stroke:#ba6018,color:#fff
    classDef merge fill:#8e44ad,stroke:#6c3483,color:#fff

    START["Planner costs every applicable<br/>join algorithm, picks the cheapest"]:::decision

    START -->|"inner side has a usable index,<br/>outer result is small"| NL["Nested Loop<br/>O(n×m)<br/>for every outer row, probe the inner index"]:::nl
    START -->|"both sides large,<br/>no usable index on join key"| HASH["Hash Join<br/>O(n+m)<br/>build a hash table from the smaller side<br/>in work_mem, probe with the larger side"]:::hash
    START -->|"both sides already sorted,<br/>or an index exists on the join key"| MERGE["Merge Join<br/>O(n log n + m log m)<br/>walk both sorted inputs in lockstep"]:::merge

-- Force a specific join type for testing
SET enable_hashjoin = off;    -- disable hash joins
SET enable_nestloop = off;    -- disable nested loops
EXPLAIN SELECT ... JOIN ...;  -- see what planner picks without preferred type
SET enable_hashjoin = on;     -- always reset after testing!

VACUUM and Autovacuum

PostgreSQL never updates or deletes rows in place (MVCC). Dead tuples accumulate and must be reclaimed.

graph TD
    classDef dead fill:#7f8c8d,stroke:#616a6b,color:#fff
    classDef live fill:#27ae60,stroke:#1e8449,color:#fff
    classDef trigger fill:#f39c12,stroke:#ba6018,color:#fff
    classDef vac fill:#3498db,stroke:#2471a3,color:#fff
    classDef full fill:#e74c3c,stroke:#c0392b,color:#fff

    UPDATE["UPDATE users SET name='Bob' WHERE id=1"] --> DEAD["Dead tuple left behind:<br/>xmin=50, xmax=200, name='Alice'<br/>still on page, invisible to new transactions"]:::dead

    DEAD --> THRESH{"n_dead_tup ÷ (n_live_tup+n_dead_tup)<br/>crosses autovacuum_vacuum_scale_factor?"}:::trigger
    THRESH -->|Yes| AUTOVAC["autovacuum worker<br/>launched automatically"]:::trigger
    THRESH -->|"No — or a manual VACUUM"| VACUUM

    AUTOVAC --> VACUUM["VACUUM<br/>scans heap, marks dead tuples reusable<br/>updates visibility map & free space map<br/>does NOT return space to OS (usually)"]:::vac
    VACUUM --> FSM["Free space map updated<br/>future INSERTs can reuse this space<br/>in the SAME table file"]:::live

    VACUUM -.->|"if bloat is severe and<br/>disk must be reclaimed"| VF["VACUUM FULL<br/>rewrites entire table to a new file<br/>reclaims disk space back to the OS<br/>ACCESS EXCLUSIVE lock — blocks everything"]:::full

Table bloat — pages fill with dead tuples → table grows → queries slow (more pages to scan).

-- Check bloat
SELECT relname,
       pg_size_pretty(pg_relation_size(oid)) AS table_size,
       n_dead_tup,
       n_live_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Manual vacuum with verbose output
VACUUM (ANALYZE, VERBOSE) users;

-- Autovacuum thresholds (per-table override)
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- vacuum when 1% dead (default 20%)
    autovacuum_analyze_scale_factor = 0.005 -- analyze when 0.5% changed
);

Partitioning

For very large tables (100M+ rows), partitioning divides the table into smaller physical pieces.

graph TD
    classDef parent fill:#34495e,stroke:#212f3c,color:#fff
    classDef scanned fill:#27ae60,stroke:#1e8449,color:#fff
    classDef skipped fill:#7f8c8d,stroke:#616a6b,color:#fff

    PARENT["orders (partitioned table)<br/>PARTITION BY RANGE (created_at)<br/>holds no rows of its own"]:::parent
    P2023["orders_2023<br/>Jan–Dec 2023"]:::skipped
    P2024Q1["orders_2024_q1<br/>Jan–Mar 2024"]:::scanned
    P2024Q2["orders_2024_q2<br/>Apr–Jun 2024"]:::skipped
    PARENT --> P2023 & P2024Q1 & P2024Q2

    QUERY["WHERE created_at = '2024-02-15'"] -.->|"planner prunes to<br/>the one matching partition"| P2024Q1
    QUERY -.->|"skipped entirely — never opened"| P2023
    QUERY -.->|"skipped entirely — never opened"| P2024Q2

-- Declarative partitioning (PostgreSQL 10+)
CREATE TABLE orders (
    id BIGINT NOT NULL,
    user_id BIGINT,
    amount NUMERIC,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2024_q1
    PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2
    PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Query partition pruning: WHERE created_at = '2024-02-15'
-- PostgreSQL scans ONLY orders_2024_q1 — skips all other partitions
EXPLAIN SELECT * FROM orders WHERE created_at = '2024-02-15';
-- → Seq Scan on orders_2024_q1 (not the others)

-- Drop old data: drop a partition instantly (no row-by-row DELETE)
DROP TABLE orders_2023;  -- instant, reclaims disk space immediately

Logical Replication

Streaming replication replicates everything. Logical replication lets you replicate specific tables to specific databases — useful for migrations, ETL, and multi-cloud.

graph LR
    classDef pub fill:#2980b9,stroke:#1f618d,color:#fff
    classDef decode fill:#e67e22,stroke:#ba6018,color:#fff
    classDef sub fill:#27ae60,stroke:#1e8449,color:#fff

    subgraph PUBSIDE["Publisher (source DB)"]
        PUB["CREATE PUBLICATION my_pub<br/>FOR TABLE users, orders"]:::pub
        WALDEC["WAL logical decoding<br/>reads WAL, decodes into<br/>row-level INSERT/UPDATE/DELETE events"]:::decode
        SLOT["Replication slot<br/>pins WAL so it can't be<br/>recycled before the subscriber reads it"]:::decode
    end

    subgraph SUBSIDE["Subscriber (destination DB)"]
        APPLY["Apply worker<br/>replays decoded events<br/>as normal SQL writes"]:::sub
        SUB["CREATE SUBSCRIPTION my_sub<br/>CONNECTION '...' PUBLICATION my_pub"]:::sub
    end

    PUB --> WALDEC --> SLOT -->|"decoded change stream"| APPLY --> SUB

-- On source database
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- On destination database (different server, different DB)
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=source-db user=replicator dbname=myapp'
  PUBLICATION my_pub;

-- Check replication lag
SELECT subname, received_lsn, latest_end_lsn,
       received_lsn - latest_end_lsn AS lag_bytes
FROM pg_stat_subscription;

Use cases: Zero-downtime major version upgrades (replicate to new version, cut over), selective table replication to data warehouse, real-time CDC without Debezium.

Useful Diagnostic Queries

-- Long-running queries (> 5 minutes)
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE query_start < now() - interval '5 minutes'
  AND state != 'idle'
ORDER BY duration DESC;

-- Locks and who is blocking whom
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- Missing indexes (sequential scans on large tables)
SELECT relname, seq_scan, seq_tup_read,
       idx_scan, seq_tup_read / seq_scan AS avg_seq_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Cache hit rate (should be > 99% for OLTP)
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Table sizes including indexes and TOAST
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
       pg_size_pretty(pg_relation_size(oid)) AS table_size,
       pg_size_pretty(pg_indexes_size(oid)) AS index_size
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;