PostgreSQL

Overview

PostgreSQL (often called “Postgres”) is an open-source object-relational database system with over 35 years of active development. It has earned a reputation as the most feature-rich and standards-compliant open-source database, powering everything from single-server applications to globally distributed systems.

Originally developed at UC Berkeley as the successor to Ingres (hence “Post-Ingres” → PostgreSQL), it has become the default database choice for startups and enterprises alike. Instagram, Spotify, Reddit, Twitch, Notion, Discord, and Apple all rely on PostgreSQL in production.

Key capabilities include:

• Full ACID compliance: Serializable isolation, durable transactions, point-in-time recovery
• Extensibility: Custom types, operators, index methods, foreign data wrappers, procedural languages
• Advanced SQL support: Window functions, CTEs, lateral joins, full-text search, JSON/JSONB
• MVCC (Multi-Version Concurrency Control): Readers never block writers, writers never block readers
• Robust replication: Streaming replication, logical replication, synchronous and asynchronous modes
• Rich indexing: B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the pgvector extension for vector similarity search

Architecture & Core Components

Process Architecture

PostgreSQL uses a multi-process architecture (not multi-threaded):

graph TD
    Client1[Client] --> PM[Postmaster<br/>Main Process]
    Client2[Client] --> PM
    Client3[Client] --> PM

    PM --> BE1[Backend Process 1]
    PM --> BE2[Backend Process 2]
    PM --> BE3[Backend Process 3]

    subgraph Background Workers
        BW[Background Writer]
        WAL[WAL Writer]
        CP[Checkpointer]
        AV[Autovacuum]
        AR[Archiver]
        SL[Stats Collector]
    end

    subgraph Shared Memory
        SB[Shared Buffers<br/>Page Cache]
        WB[WAL Buffers]
        CL[CLOG<br/>Commit Log]
    end

    BE1 --> SB
    BE2 --> SB
    BE3 --> SB
    BW --> SB
    WAL --> WB

• Postmaster: The main process that accepts connections and forks a backend process per client
• Backend processes: One per connection, handles query parsing, planning, and execution
• Shared buffers: In-memory page cache (typically 25% of RAM)
• WAL (Write-Ahead Log): Durability mechanism — all changes written to WAL before data files
• Background writer: Periodically flushes dirty pages from shared buffers to disk
• Autovacuum: Cleans up dead tuples created by MVCC (critical for performance)

Storage Engine

PostgreSQL stores data in 8KB pages organized into tables (heaps) and indexes:

• Heap: Unordered collection of pages containing tuples (rows)
• TOAST (The Oversized-Attribute Storage Technique): Automatically compresses and stores large values out-of-line
• Tablespaces: Map logical storage to physical disk locations
• Fillfactor: Configurable per-table space reservation for HOT (Heap-Only Tuple) updates

MVCC Implementation

PostgreSQL’s MVCC uses tuple versioning rather than undo logs (unlike MySQL/InnoDB):

1. Each row version has xmin (creating transaction ID) and xmax (deleting/updating transaction ID)
2. An UPDATE creates a new tuple version and marks the old one as dead (sets xmax)
3. A DELETE marks the tuple as dead but doesn’t remove it
4. VACUUM is required to reclaim dead tuples — this is unique to PostgreSQL and a source of both power and operational complexity

graph LR
    subgraph "Table Page"
        T1["Tuple v1<br/>xmin=100, xmax=200<br/>(dead)"]
        T2["Tuple v2<br/>xmin=200, xmax=∞<br/>(live)"]
    end

    T1 -->|"UPDATE created"| T2
    V[VACUUM] -->|"Reclaims"| T1

This approach means:

• Readers never block writers: Old tuple versions remain visible to concurrent transactions
• Writers never block readers: New versions are invisible until committed
• Trade-off: Dead tuples accumulate and must be cleaned by VACUUM/autovacuum

Key Features

Indexing

PostgreSQL offers the richest indexing options of any database:

Partial and Expression Indexes

-- Index only active users (partial index)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Index on expression
CREATE INDEX idx_lower_email ON users(lower(email));

JSONB

PostgreSQL’s JSONB type bridges the SQL/NoSQL divide:

-- Store and query JSON natively
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

-- GIN index for fast JSON queries
CREATE INDEX idx_events_data ON events USING GIN (data);

-- Query nested JSON
SELECT * FROM events WHERE data @> '{"type": "click", "source": "mobile"}';

Window Functions and CTEs

-- Running total with window function
SELECT date, revenue,
       SUM(revenue) OVER (ORDER BY date) as running_total,
       AVG(revenue) OVER (ORDER BY date ROWS 6 PRECEDING) as weekly_avg
FROM daily_revenue;

-- Recursive CTE for hierarchical data
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 as depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

Use Cases

When to Use PostgreSQL

• OLTP workloads with complex queries, joins, and transactions
• Applications needing strong consistency (financial, inventory, booking systems)
• Full-text search combined with relational data (often replaces Elasticsearch for simpler use cases)
• Geospatial applications via PostGIS extension
• JSON document storage with JSONB when you need both document flexibility and relational queries
• Vector similarity search for AI/ML applications via pgvector

When NOT to Use PostgreSQL

• Massive write-heavy workloads at 100K+ writes/sec — consider Cassandra or DynamoDB
• Simple key-value lookups at extreme scale — Redis or DynamoDB are better
• Real-time analytics on petabytes — consider ClickHouse, BigQuery, or Snowflake
• Graph traversals — Neo4j or Dgraph are purpose-built for this
• Global multi-region active-active writes — consider CockroachDB or Spanner (though Citus can help)

Comparison with Alternatives

Performance Characteristics

Typical Benchmarks

• Simple SELECT by PK: ~0.1ms (in shared buffers)
• Complex JOIN query: 1-100ms depending on data size and indexing
• INSERT throughput: 10K-50K rows/sec single node (depends on indexes, WAL settings)
• Connection overhead: ~2MB per connection (use pgbouncer for connection pooling)

Tuning Parameters

Production Operational Concerns

Monitoring Key Metrics

• Connection count: Monitor against max_connections, use pgbouncer for pooling
• Cache hit ratio: Should be >99% (SELECT sum(heap_blks_hit) / sum(heap_blks_hit + heap_blks_read) FROM pg_statio_user_tables)
• Dead tuple ratio: High dead tuples indicate VACUUM isn’t keeping up
• Replication lag: Monitor pg_stat_replication for replica delay
• Long-running queries: Kill queries running longer than expected
• Lock contention: Monitor pg_locks for waiting transactions

Common Failure Modes

1. VACUUM not keeping up → Table bloat, degraded performance, eventually transaction ID wraparound
2. Connection exhaustion → Use pgbouncer (not application-level pooling alone)
3. Replication lag spike → Check replica hardware, long transactions, or heavy write bursts
4. Lock contention → Review transaction isolation levels, reduce long transactions
5. OOM kills → Tune work_mem and maintenance_work_mem, monitor sort spills to disk

Interview Tips

Common interview questions about PostgreSQL:

• “How does MVCC work in PostgreSQL?” — Tuple versioning with xmin/xmax, readers never block writers, VACUUM required for cleanup
• “PostgreSQL vs MySQL — when would you choose each?” — PostgreSQL for complex queries, extensibility, JSONB; MySQL for simpler workloads, proven replication (Vitess)
• “How would you scale PostgreSQL?” — Read replicas for read scaling, Citus for horizontal sharding, pgbouncer for connection pooling, table partitioning for large tables
• “Explain isolation levels in PostgreSQL” — Read Committed (default), Repeatable Read (snapshot isolation), Serializable (SSI — serializable snapshot isolation, detects write skew)
• “How does PostgreSQL handle full-text search?” — tsvector/tsquery with GIN indexes; for simple cases, can replace Elasticsearch