PostgreSQL Deep Dive (Relational Architecture & Performance Tuning)

PostgreSQL is one of the world's most battle-tested, feature-rich open-source relational database management systems. Understanding its internal execution model, indexing mechanics, and memory hierarchy is critical for designing scalable transactional systems.

1. Process Architecture & Memory Layout

PostgreSQL uses a multi-process architecture (rather than multi-threaded):

• Postmaster (Main Process): Listens for incoming connections on port 5432 and forks a dedicated backend process for each client connection.
• Shared Memory Area:
• Shared Buffers: Caches database disk pages (typically allocated 25–40% of total host RAM).
• WAL Buffers: Buffers Write-Ahead Log entries before flushing to disk.
• CLOG (Commit Log): Tracks transaction commit/rollback status bits.
• Per-Backend Private Memory:
• `work_mem`: Memory allocated for sorts, hash joins, and aggregations per query operation.
• `maintenance_work_mem`: Memory used for VACUUM, CREATE INDEX, and foreign key validations.

2. Multi-Version Concurrency Control (MVCC)

PostgreSQL implements MVCC to allow readers not to block writers, and writers not to block readers.

• Every row (tuple) contains hidden header fields:
• xmin: The transaction ID ($TxID$) that inserted the row.
• xmax: The transaction ID that deleted or updated the row (0 if live).
• Updates as Deletes + Inserts: When a row is updated, PostgreSQL does not overwrite the existing data in-place. Instead, it marks the current tuple with xmax = current_txid and writes a completely new tuple with xmin = current_txid.

The Problem: Table Bloat & Dead Tuples

Because dead tuples remain on disk to satisfy older active transaction snapshots, heavy update workloads cause table and index bloat.

• VACUUM: Scans pages, marks dead tuple space as reusable for future inserts.
• Autovacuum Tuning:
• Lower autovacuum_vacuum_scale_factor (e.g. from 0.2 to 0.05) on high-write tables to trigger vacuuming sooner.
• Increase autovacuum_cost_limit to prevent vacuum throttling during high I/O windows.

3. Write-Ahead Logging (WAL) & Durability

• Before any data page is modified in RAM (Shared Buffers), the change is sequentially written to the Write-Ahead Log on disk.
• fsync: Guarantees disk durability upon commit.
• Checkpointing: Flushes dirty shared buffer pages to the actual database files on disk and truncates old WAL logs.

4. Indexing Deep Dive: When to Use What

• B-Tree (Default): Best for equality (=) and range queries (<, >, BETWEEN).
• GIN (Generalized Inverted Index): Indexes composite elements (JSONB columns, full-text search tsvector, array intersections).
• GiST (Generalized Search Tree): Used for geometric shapes, geospatial coordinates (PostGIS), and range types.
• BRIN (Block Range Index): Extremely small index for multi-gigabyte tables where data is physically sorted by time (e.g. created_at).

Key Architecture Trade-Offs & Decision Matrix