PostgreSQL

Theory

PostgreSQL is an advanced open-source relational database with full ACID compliance, extensibility, and standards-compliant SQL. It uses MVCC (Multi-Version Concurrency Control) — readers don't block writers; each transaction sees a snapshot of row versions via xmin/xmax tuple headers.

MVCC creates dead tuples on UPDATE/DELETE — old row versions remain until VACUUM reclaims space. Autovacuum runs automatically but may lag on write-heavy tables, causing bloat and transaction ID wraparound risk. Monitor pg_stat_user_tables.n_dead_tup and tune autovacuum_vacuum_scale_factor.

EXPLAIN ANALYZE runs the query and shows actual row counts, timing, and scan types. Seq Scan on large tables is a red flag — add indexes. Index Scan uses B-tree (default). Bitmap Index Scan combines multiple indexes. Sort/Hash nodes show memory vs disk spill (work_mem tuning).

B-tree indexes support equality and range queries on scalar columns. GIN indexes excel at full-text search, JSONB containment (@>), and array overlap. GiST handles geometric data and nearest-neighbor. BRIN for very large sequentially-correlated tables (timestamps). Partial indexes index a subset: WHERE status = 'active'.

JSONB stores JSON in binary decomposed form — indexable with GIN, queryable with ->, ->>, @> operators. Prefer JSONB over JSON for querying. Don't replace normalized columns with JSONB blobs — use for semi-structured attributes that vary per row.

CTEs (WITH clauses) improve readability; PostgreSQL 12+ can inline them. Window functions (ROW_NUMBER, RANK, LAG/LEAD) compute per-partition aggregates without GROUP BY collapsing rows. Essential for analytics queries in OLTP databases.

pg_stat_statements extension tracks normalized query text, call count, total/mean time — find slow queries without log mining. Install via CREATE EXTENSION pg_stat_statements; query pg_stat_statements view ordered by total_exec_time DESC.

Connection pooling (PgBouncer) multiplexes many app connections onto fewer DB connections — Postgres doesn't handle 10k connections well (~500MB RAM each). Use transaction pooling mode for stateless apps. Streaming replication ships WAL to standbys for read replicas; logical replication replicates table-level changes for migrations and CDC.

Architecture Diagram

SQL client
         |
  Postmaster
         |
  Connection + query planner
         |
  Shared buffers + WAL
         |
  Data files / indexes / TOAST

Examples

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, u.name
ORDER BY COUNT(o.id) DESC
LIMIT 20;

-- Look for: Seq Scan (bad on big tables), actual rows vs estimated,
-- "Buffers: shared hit/read", Sort Method: external merge (work_mem too low)

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  attrs JSONB NOT NULL DEFAULT '{}'
);

CREATE INDEX idx_attrs_gin ON products USING GIN (attrs);

SELECT * FROM products
WHERE attrs @> '{"color": "red"}';

SELECT attrs->>'sku' AS sku FROM products
WHERE attrs->>'category' = 'electronics';

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Find bloated tables
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Key Concepts

Interview Questions

Best Practices

• Run EXPLAIN (ANALYZE, BUFFERS) on slow queries in staging before adding indexes — verify seq scans disappear.
• Use CREATE INDEX CONCURRENTLY in production — standard CREATE INDEX locks writes on large tables.
• Enable pg_stat_statements and review top queries weekly — indexes follow real access patterns, not guesses.
• Pool connections with PgBouncer in transaction mode — Postgres performance degrades beyond ~few hundred connections.
• Use JSONB with GIN indexes for semi-structured fields — but do not replace normalized columns for relational joins.
• Schedule VACUUM monitoring — bloat from heavy UPDATE/DELETE causes sequential scan slowdown over time.

Common Mistakes

• Creating indexes on every column — each index slows writes and may never be used by the planner.
• Using SELECT * in hot paths — fetches TOAST columns and blocks index-only scans.
• Opening a new connection per HTTP request without pooling — exhausts max_connections under moderate traffic.
• Ignoring lock timeouts on migrations — long ALTER TABLE blocks application writes indefinitely.
• Treating SERIAL as gap-free — sequences have gaps after rollback; use UUID or BIGINT if uniqueness across shards matters.

Cheat Sheet

Practical Exercises