Design with PostgreSQL — Staff-Level Technology Guide

The 60-Second Pitch

PostgreSQL is the default relational database for system design interviews. ACID transactions, MVCC for concurrent reads and writes without read locks, a write-ahead log for durability, and an extensibility model that supports full-text search, JSONB documents, geospatial queries, and custom types — all in one engine. When an interviewer says "pick a database," PostgreSQL is the safe, defensible first choice for any workload that values correctness over raw write throughput.

The Staff-level insight: PostgreSQL is not just a relational database — it is a platform. PostGIS for geospatial, pg_trgm for fuzzy text search, JSONB for semi-structured data, logical replication for CDC, and foreign data wrappers for federated queries. The moment you reach for a second database, ask whether PostgreSQL already handles the use case with an extension. The Staff trap: knowing when PostgreSQL is NOT enough — sustained write throughput beyond 50K TPS, datasets exceeding 10TB where vacuum overhead becomes unmanageable, or workloads requiring horizontal write scaling across regions.

Architecture & Internals

Process Model

PostgreSQL uses a process-per-connection architecture. The postmaster is the main daemon that listens for connections. For each client connection, the postmaster forks a dedicated backend process that handles all queries for that session — parsing, planning, executing, returning results. Background processes handle maintenance: the autovacuum launcher spawns workers to reclaim dead tuples, the WAL writer flushes write-ahead log buffers, the checkpointer writes dirty pages to disk, the bgwriter pre-flushes dirty buffers to reduce checkpoint I/O spikes, and the stats collector tracks query and table statistics for the query planner.

This process model is simple and robust but has a critical scaling implication: each backend process consumes 5-10MB of memory and holds OS resources (file descriptors, shared memory segments). A server with 500 direct connections uses 2.5-5GB of RAM just for connection overhead. At 1,000 connections, context-switching overhead degrades throughput. This is why PgBouncer (connection pooler) is not optional in production — it multiplexes thousands of application connections onto 50-100 actual PostgreSQL backends.

MVCC: Multi-Version Concurrency Control

PostgreSQL achieves concurrent reads and writes without read locks through MVCC. Every row (tuple) carries two hidden fields: xmin (the transaction ID that created it) and xmax (the transaction ID that deleted or updated it, zero if still live). When you UPDATE a row, PostgreSQL does not modify the existing tuple — it marks the old tuple's xmax with the updating transaction's ID and inserts a new tuple with a new xmin. Both versions exist simultaneously on disk.

Each transaction operates against a snapshot — a frozen view of which transactions were committed at the moment the snapshot was taken. A tuple is visible to a transaction if xmin is committed and before the snapshot, and either xmax is zero (not deleted) or xmax is uncommitted or after the snapshot. This means readers never block writers and writers never block readers — the fundamental concurrency advantage of MVCC over lock-based systems.

The cost of MVCC is dead tuples. Every UPDATE and DELETE leaves behind an old tuple version that is invisible to all current and future transactions but still occupies disk space. This is why PostgreSQL needs VACUUM — a process that identifies dead tuples (no active transaction can see them), reclaims their space, and updates the visibility map. Without vacuum, tables and indexes grow unbounded — a phenomenon called table bloat.

Write-Ahead Log (WAL)

The WAL is PostgreSQL's durability mechanism. Every data modification is first written to the WAL (a sequential, append-only log on disk) before the actual data pages are modified. If the server crashes, PostgreSQL replays the WAL from the last checkpoint to restore the database to a consistent state. The WAL guarantees that committed transactions are never lost, even if the server crashes mid-write.

WAL also enables streaming replication. The primary server streams WAL records to replica servers in real time. Replicas apply these records to their local copies, maintaining near-real-time consistency. Two modes: asynchronous (default — primary does not wait for replica acknowledgment, RPO = replication lag, typically <1 second) and synchronous (synchronous_commit = on with synchronous_standby_names — primary waits for at least one replica to confirm WAL write, RPO = 0, cost = latency of slowest replica added to every commit).

WAL segment size is 16MB by default. The wal_level setting controls how much information is logged: replica (default — enough for streaming replication and point-in-time recovery), logical (adds information for logical replication/CDC — required for Debezium, logical decoding). Archiving WAL segments to S3 or equivalent enables point-in-time recovery (PITR) to any moment within the archive retention window.

Key Memory Settings

shared_buffers (default 128MB): PostgreSQL's buffer cache — pages read from disk are cached here. Rule of thumb: set to 25% of system RAM (e.g., 16GB on a 64GB server). Too small = excessive disk I/O, too large = OS page cache gets squeezed.

work_mem (default 4MB): memory per sort/hash operation per query. A complex query with 5 hash joins uses up to 5 × work_mem. Set too low = sorts spill to disk (slow). Set too high with many concurrent queries = OOM risk. Start at 16-64MB, monitor with log_temp_files = 0 to see when operations spill.

effective_cache_size (default 4GB): tells the query planner how much memory is available for caching (shared_buffers + OS cache). Does not allocate memory — it is advisory. Set to 75% of system RAM. Affects whether the planner chooses index scans (faster with large cache) vs sequential scans.

PgBouncer modes: Transaction mode (recommended) — connection is returned to the pool after each transaction. Maximum multiplexing efficiency. Breaks session-level features (prepared statements, temp tables, advisory locks). Session mode — connection held for the entire client session. Less efficient but compatible with all PostgreSQL features. Use transaction mode for web applications; session mode for applications that rely on session state.

VACUUM

MVCC creates dead tuples on every UPDATE and DELETE. VACUUM reclaims this space. Without vacuum, a table that receives 10M updates per day accumulates 10M dead tuples per day — even if it only has 1M live rows. Table and index sizes grow linearly, query performance degrades, and eventually disk fills.

Autovacuum runs continuously in the background. It triggers when a table's dead tuple count exceeds autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × table size (default: 50 + 20% of table rows). For a 10M-row table, autovacuum triggers after ~2M dead tuples. For high-write tables, this default is too aggressive — the table accumulates significant bloat before vacuum runs.

Tuning for high-write tables: reduce autovacuum_vacuum_scale_factor to 0.01 (1%) or set autovacuum_vacuum_threshold to a fixed number. Increase autovacuum_vacuum_cost_delay to reduce I/O impact, or decrease it to make vacuum more aggressive. Monitor n_dead_tup in pg_stat_user_tables — a growing count means vacuum is falling behind.

Core Concepts for Interviews

ACID and What It Costs

The Staff insight: ACID is not free. Every guarantee has a performance cost. When candidates say "we need ACID," the follow-up is "for which operations?" — most systems need ACID for financial transactions but tolerate eventual consistency for read models, counters, and analytics.

Isolation Levels

Read Committed takes a new snapshot for every statement within a transaction — two identical SELECTs can return different results if another transaction committed between them. Repeatable Read takes one snapshot at transaction start — all reads see the same data regardless of concurrent commits. Serializable detects conflicts that would violate serial execution order and aborts one transaction — the application must retry.

Indexes

B-tree (default): ordered, supports equality and range queries (=, <, >, BETWEEN, ORDER BY). O(log N) lookup. The right choice for 90% of index needs.

GIN (Generalized Inverted Index): for composite values — arrays, JSONB, full-text search (tsvector). Maps each element/key to the set of rows containing it. Slower to write (every element indexed separately), fast for containment queries (@>, ?, @@).

GiST (Generalized Search Tree): for geometric, range, and spatial data. Used by PostGIS for geospatial queries, by pg_trgm for trigram similarity search, and for range type overlap queries.

BRIN (Block Range Index): stores min/max values per block range (128 pages by default). Tiny index size for naturally ordered data — timestamp columns on append-only tables. Not useful for randomly distributed data.

Hash: equality-only lookups, not WAL-logged before PostgreSQL 10 (crash-unsafe). Rarely used since B-tree handles equality just as efficiently with range support as a bonus.

Composite indexes: column order matters. Index on (tenant_id, created_at) supports queries filtering on tenant_id alone or tenant_id + created_at, but NOT created_at alone (leftmost prefix rule). Put the most selective column first unless the query pattern demands otherwise.

Covering indexes (CREATE INDEX ... INCLUDE (column)): add non-indexed columns to the index leaf pages, enabling index-only scans without touching the table heap. For read-heavy queries selecting a few columns, this eliminates the heap lookup entirely.

-- Covering index: answers SELECT email FROM users WHERE tenant_id = ? AND active = true
-- without touching the table at all
CREATE INDEX idx_users_tenant_active ON users (tenant_id, active) INCLUDE (email);

JSONB

PostgreSQL's JSONB type stores JSON documents in a binary format with indexing support. Use JSONB when: data is semi-structured (user preferences, feature flags, event metadata), schema varies per record, or you need to query nested fields without a predefined schema.

Do NOT use JSONB when: data is structured and queried frequently on specific fields (use columns), you need JOINs on JSON fields (use normalized tables), or JSON documents exceed 100KB (store in blob storage, reference by ID).

-- GIN index on JSONB for containment queries
CREATE INDEX idx_events_data ON events USING GIN (data);

-- Query: find events where data contains {"status": "failed"}
SELECT * FROM events WHERE data @> '{"status": "failed"}';

-- Query: extract nested field
SELECT data->>'user_id' AS user_id, data->'metadata'->>'source' AS source
FROM events WHERE data @> '{"type": "purchase"}';

Partitioning

PostgreSQL supports range (by date, ID range), hash (by hash of column value), and list (by discrete values) partitioning. Partitioning splits a logical table into physical sub-tables, each with its own indexes, vacuum, and storage.

When to partition: tables exceeding 100M rows where queries always filter on the partition key (time-range queries on date-partitioned tables), when you need to drop old data cheaply (DROP a partition vs DELETE + vacuum millions of rows), or when autovacuum on the full table is too slow.

When NOT to partition: when queries frequently span all partitions (partition pruning provides no benefit and adds planning overhead), when the table has fewer than ~50M rows (overhead exceeds benefit), or when you need unique constraints across partitions (only supported on the partition key).

Data Modeling Patterns

Normalization vs. Denormalization

Normalization (3NF) eliminates redundancy at the cost of joins. Denormalization embeds data to eliminate joins at the cost of write amplification and consistency risk. The Staff framework: normalize the write path (source of truth), denormalize the read path (materialized views, read-optimized tables).

-- Normalized: orders and order_items are separate tables
-- Good for: writes (update item quantity without touching order), consistency
SELECT o.id, o.total, i.product_id, i.quantity
FROM orders o JOIN order_items i ON o.id = i.order_id
WHERE o.user_id = 123;

-- Denormalized: order with embedded items as JSONB
-- Good for: reads (single row fetch), bad for: updating individual items
SELECT id, total, items FROM orders_denormalized WHERE user_id = 123;

The decision rule: if the query pattern is "fetch entity with all its children" and children are rarely updated independently, denormalize. If children are independently queryable, updatable, or shared across multiple parents, normalize.

Schema Migration Strategies

Expand-and-contract (zero-downtime migration): (1) Add new column with default, (2) backfill existing rows, (3) deploy code that writes to both old and new columns, (4) migrate reads to new column, (5) drop old column. Each step is independently deployable and rollback-safe.

Online DDL considerations: ALTER TABLE ADD COLUMN with a non-volatile default is instant in PostgreSQL 11+ (metadata-only change). ALTER TABLE ADD COLUMN with a volatile default rewrites the entire table (lock). CREATE INDEX CONCURRENTLY builds the index without blocking writes but takes 2-3x longer. ALTER TABLE ... SET NOT NULL requires a full table scan to verify — use a CHECK constraint instead (ADD CONSTRAINT ... CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT).

Scaling PostgreSQL

Read Replicas

Streaming replication creates read replicas that stay within seconds (async) or zero (sync) of the primary. Use cases: read-heavy workloads (route reads to replicas, writes to primary), analytics queries (isolate expensive queries from production), geographic read locality (replica in each region).

The tradeoff for async replication: read-after-write inconsistency. A user writes to the primary, then reads from a replica that has not received the WAL yet — they see stale data. Solutions: (1) route reads-after-writes to the primary for N seconds, (2) use synchronous replication for critical reads (latency cost), (3) accept eventual consistency where business logic allows.

Connection Pooling

PgBouncer in transaction mode is the Staff default. It multiplexes application connections onto a small pool of PostgreSQL backends, reducing connection overhead from thousands to hundreds. Configuration: pool_size = 2 × CPU cores on the PostgreSQL server, max_client_conn = 5000 on PgBouncer, server_idle_timeout = 600 to reclaim idle backends.

Transaction-mode limitations: no prepared statements across transactions (use DEALLOCATE ALL or PgBouncer's server_reset_query), no session-level advisory locks, no LISTEN/NOTIFY, no temporary tables that persist across transactions. For applications that need these features, use session mode on a separate PgBouncer pool or direct connections.

When PostgreSQL Is Not Enough

PostgreSQL scales vertically to impressive limits — modern instances handle 10TB+ databases, 50K+ TPS, and 10K+ concurrent connections (with pooling). But certain workloads exceed single-node capacity:

Failure Modes

Connection Exhaustion

Symptom: FATAL: too many connections for role "app" or FATAL: remaining connection slots are reserved for non-replication superuser connections. Application errors spike, new requests cannot obtain a database connection.

Detection: numbackends in pg_stat_database approaching max_connections. PgBouncer cl_active and cl_waiting metrics. Alert at 80% of max_connections.

Staff Response: "Immediate: check for connection leaks (SELECT * FROM pg_stat_activity WHERE state = 'idle' AND query_start < now() - interval '1 hour'). Kill idle connections with pg_terminate_backend(). Structural fix: PgBouncer in transaction mode with pool_size capped at 2× CPU cores. Application-level fix: ensure connection pools release connections after each request — framework misconfiguration is the most common cause."

Vacuum Bloat

Symptom: Table size grows steadily despite stable row count. Query performance degrades (indexes must traverse dead tuples). Disk usage increases without corresponding data growth. pg_stat_user_tables.n_dead_tup in the millions.

Detection: pg_stat_user_tables.n_dead_tup / n_live_tup ratio. Alert when dead tuples exceed 10% of live tuples on any table. Monitor last_autovacuum — if it has not run in hours on a high-write table, vacuum is being blocked.

Staff Response: "Check for long-running transactions blocking vacuum: SELECT pid, age(xact_start), query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5. A 2-hour transaction prevents vacuum from reclaiming any tuple created after the transaction started. Kill it if it is a runaway analytics query. Tune autovacuum: autovacuum_vacuum_scale_factor = 0.01 for high-write tables. For emergency bloat remediation: pg_repack to rebuild the table online without lock."

Replication Lag

Symptom: Read replicas serve stale data. Users see inconsistent state after writes. pg_stat_replication.replay_lag shows seconds or minutes of delay.

Detection: replay_lag in pg_stat_replication on the primary. Alert when lag exceeds 5 seconds. Monitor pg_wal_lsn_diff(sent_lsn, replay_lsn) for bytes behind.

Staff Response: "Root causes in order of likelihood: (1) replica is running a long query that conflicts with WAL replay (hot_standby_feedback = on prevents cancellation but allows lag to grow), (2) replica disk I/O is saturated (WAL apply is sequential write but index updates are random I/O), (3) network bandwidth between primary and replica is saturated during peak write load. Fix: add more replicas to distribute read load, use max_standby_streaming_delay to bound lag by canceling conflicting queries, or upgrade replica hardware."

Lock Contention and Deadlocks

Symptom: Queries that normally take 5ms take 30 seconds. pg_stat_activity shows many connections in wait_event_type = Lock. Deadlock detection fires (logged as ERROR: deadlock detected).

Detection: pg_stat_activity.wait_event_type = 'Lock' with wait_event = 'transactionid' or 'tuple'. deadlocks counter in pg_stat_database. pg_locks joined with pg_stat_activity for lock graph visualization.

Staff Response: "Deadlocks are almost always caused by transactions updating the same rows in different orders. Fix: enforce consistent update ordering (always update parent before child, always lock rows in ID order). For hot-row contention without deadlocks: use SELECT ... FOR UPDATE SKIP LOCKED for queue patterns, or use advisory locks for application-level coordination. For high-contention counters: use Redis for the hot path and reconcile to PostgreSQL asynchronously."

Long-Running Transactions Blocking Vacuum

Symptom: pg_stat_user_tables.n_dead_tup grows linearly. Table sizes increase. oldest_xact_age in pg_stat_activity shows transactions hours old. Autovacuum runs but cannot reclaim tuples.

Detection: SELECT max(age(xact_start)) FROM pg_stat_activity WHERE xact_start IS NOT NULL. Alert when any transaction exceeds 30 minutes. Monitor xact_start of oldest active transaction.

Staff Response: "Long-running transactions are the number one operational hazard in production PostgreSQL. A single 4-hour analytics query prevents vacuum from reclaiming any dead tuple created after the query started — on EVERY table. Solutions: (1) route analytics queries to a replica with hot_standby_feedback = off (queries may be cancelled by WAL replay), (2) set idle_in_transaction_session_timeout to kill abandoned transactions, (3) set statement_timeout on analytics roles to prevent runaway queries."

When to Use vs. Alternatives

Decision rule: PostgreSQL is the default choice until proven otherwise. Choose MySQL when you have existing MySQL expertise and no need for PostgreSQL extensions. Choose DynamoDB when you need single-digit ms at any scale with zero operational overhead and can model your access patterns as key lookups. Choose CockroachDB when you need distributed ACID with horizontal write scaling. Choose MongoDB when your data is genuinely document-shaped with no relational patterns.

Deployment Topologies

Topology selection: Start with topology 1. Add read replicas (topology 2) when read QPS exceeds single-node capacity. Add Patroni (topology 3) when automated failover is required (>99.9% uptime SLA). Patroni uses etcd or ZooKeeper for leader election and handles promotion, DNS updates, and PgBouncer reconfiguration automatically.

Staff-Level Operational Concerns

Monitoring essentials: pg_stat_user_tables.n_dead_tup (vacuum health), pg_stat_activity (connection count, long-running queries, lock waits), pg_stat_replication.replay_lag (replication health), pg_stat_bgwriter.buffers_backend (if non-zero consistently, shared_buffers is too small), pg_stat_database.deadlocks (should be zero in healthy applications), and pg_stat_statements (top queries by total time, calls, rows — requires the extension).

Backup strategy: pg_basebackup for full base backups + WAL archiving to S3 for continuous PITR. Test recovery monthly from a random point in time. pg_dump for logical backups of individual databases or tables. For large databases (>1TB), use pgBackRest with incremental and parallel backups to reduce backup window and storage cost.

Index maintenance: unused indexes consume write amplification (every INSERT updates every index) and disk space for zero benefit. Query pg_stat_user_indexes for idx_scan = 0 indexes that have never been used. Drop them. Duplicate indexes (same columns, same order) are surprisingly common after multiple migrations — detect with pg_stat_user_indexes cross-referenced with pg_index.

Interview Application

Which Playbooks Use PostgreSQL

What L5 Says vs. What L6 Says

Common Interview Mistakes

Staff Insight