PostgreSQL

Why it exists

PostgreSQL (informally 'Postgres') is the SQL database engineers reach for when they want the richest possible type system, extensions ecosystem, and adherence to the SQL standard. It powers Stripe, Instagram (pre-Meta), Reddit, Notion, DoorDash, and thousands of others. Its combination of MVCC via xmin/xmax, mature JSONB with GIN indexes, and world-class extensions (PostGIS for geospatial, pgvector for embeddings, TimescaleDB for time-series, pg_stat_statements for observability) makes it the default relational DB for analytics-adjacent products and for teams that value standards compliance over raw throughput.

How it works

Postgres organizes rows in HEAP FILES separate from indexes. The primary key is a B-tree that points INTO the heap — a cold PK lookup is 2 I/Os (index + heap). MVCC is implemented via per-row xmin/xmax transaction IDs: on modification, a new row version is written to the heap with a new xmin; old versions stay until VACUUM (auto or manual) reclaims them. The Write-Ahead Log (WAL) captures every change before disk; sync tuned via `synchronous_commit` and `wal_sync_method`. `shared_buffers` is sized to 25% of RAM — Postgres relies on the OS page cache for the other 75%, a very different tuning philosophy from InnoDB. Replication is via streaming replication (binary WAL shipping) or logical replication (row-level, supports cross-major-version). Extensions load into the database and expose new data types, index types (GIN, GiST, BRIN), and query operators.

Scaling characteristics

A well-tuned Postgres m6i.large handles ~15-20K point-lookup QPS at 60-70% CPU with a 90% warm cache hit rate (slightly slower than MySQL due to heap dereference). Writes cap at ~5-10K QPS on Multi-AZ synchronous replication (same cross-AZ commit cost as MySQL). Read scaling: streaming-replicated read replicas — each adds ~15K reads. Write scaling: Citus (Postgres extension for horizontal sharding, Microsoft-owned since 2019, powers Azure Cosmos DB for Postgres) or CockroachDB (Postgres-wire-compatible NewSQL with horizontal scale). Storage: single-instance Aurora Postgres scales to ~128 TB; Citus sharded clusters scale to petabytes but with less production battle-testing than Vitess at the extreme scale.

When to use it

• You need PostGIS for geospatial queries (ride-sharing L4, food-delivery, mapping) — no MySQL equivalent is as mature
• You need pgvector for embeddings + similarity search co-located with your OLTP data
• You need JSONB with GIN indexes — Postgres JSONB indexing is more mature than MySQL JSON functional indexes
• You need full-text search co-located with rows via `tsvector` — no separate Elasticsearch tier needed at small-medium scale
• Your team hires from the data-analyst pool (Postgres is dominant in analytics + BI + DBA-heavy shops)
• You want the richest SQL standard compliance (window functions, CTEs, LATERAL joins were all mature in Postgres before MySQL)
• You want extension flexibility — TimescaleDB, Citus, pgvector, PostGIS, pg_stat_statements ship as extensions and are best-in-class

When NOT to use it

• You're a point-lookup-dominant workload and want the clustered-PK index efficiency — MySQL InnoDB's 1-I/O lookup is better
• You need L6+ horizontal sharding — Vitess (MySQL) has 5+ years more production battle-testing than Citus (Postgres)
• You want simpler defaults for a small ops team — MySQL's InnoDB behavior is more predictable out of the box
• You're all-in on AWS and want Aurora — Aurora MySQL is more mature and cheaper than Aurora Postgres at high scale

Failure modes

• Primary DB down → automatic failover to a synchronous replica (~30-90s outage on RDS)
• Long-running transactions block VACUUM → dead-tuple bloat accumulates → tables get slow over time (the #1 Postgres operational gotcha)
• Replication lag on streaming replication → replicas serve stale data
• Bad query without index → full table scan pegging CPU and blocking short queries
• Connection storm → 'too many connections' errors; mitigate with PgBouncer (transaction-mode is the sweet spot)
• XID wraparound → catastrophic if VACUUM lags behind ID generation; monitors and alerting on `age(datfrozenxid)` are mandatory
• shared_buffers too large → contention on buffer pool locks (unlike InnoDB, sizing it to 70% of RAM hurts Postgres)

Alternatives

• MySQL — clustered PK index gives 1 I/O per lookup; Vitess is the more mature sharding layer at L6+ scale
• Aurora Postgres — cloud-native shared-storage flavor with fast failover + up to 15 read replicas; higher cost
• CockroachDB — Postgres-wire-compatible NewSQL with built-in horizontal + multi-region scale; ~2x cost
• Google Cloud Spanner — different DB entirely but often compared for global consistency guarantees
• TimescaleDB — a Postgres extension that makes it excellent for time-series workloads

Interview questions

• Explain MVCC via xmin/xmax — why does VACUUM matter, and what happens if it lags?
• Why does Postgres take 2 I/Os per cold PK lookup where MySQL takes 1?
• You have a 500 GB table with 400 GB bloat. How did that happen and how do you fix it?
• What's XID wraparound and why is it Postgres's most catastrophic silent failure?
• Explain PgBouncer transaction mode vs session mode — when do you use each?
• Why is shared_buffers sized to 25% of RAM instead of 70-80% like InnoDB?
• You have a heavy analytical query that scans 100 M rows. How would you route it away from the OLTP primary?
• Compare Citus and Vitess — when would you pick each?