sql-db

SQL Database

A row-oriented, ACID-compliant relational database — the default for transactional workloads.

Why it exists

For the last 40 years, SQL databases have been the standard answer for storing structured data with strong consistency. When you need to atomically move money from one account to another, when you need referential integrity across tables, when you want ad-hoc queries with JOINs — you reach for SQL. It's the boring, dependable, well-understood choice, and most projects reach for NoSQL prematurely.

How it works

A SQL database organizes data into tables with typed columns and enforces constraints (primary keys, foreign keys, unique, NOT NULL). Modern engines use B-tree indexes for point/range queries and MVCC (multi-version concurrency control) so readers don't block writers. Transactions provide ACID guarantees. Under the hood: a page cache in RAM, WAL (write-ahead log) for durability, and a query planner that turns SQL into an execution plan.

Scaling characteristics

A well-tuned Postgres or MySQL primary handles ~10K QPS read and ~1K QPS write on a single m5.2xlarge. Read scaling: read replicas (async replication, ~ms lag). Write scaling: partition tables OR shard across multiple databases (application-level or via Vitess/Citus/Aurora). Storage: single-instance limit ~64 TB on managed services; sharded fleets scale to petabytes.

When to use it

Transactional workloads (payments, inventory, bookings, anything with money)
Relational data with well-known query patterns
You need JOINs, ad-hoc queries, and reporting
Team is familiar with SQL and standard tools
You value correctness > raw throughput

When NOT to use it

Massive write throughput (>50K writes/sec) without sharding investment
Schemaless / rapidly-evolving data models (though Postgres JSONB narrows this gap)
Time-series ingest at millions of events/sec (use a TSDB)
Full-text search (use Elasticsearch, though pg_search exists)

Failure modes

Primary DB down → automatic failover to a synchronous replica (~30s outage window)
Replication lag → read replicas serve stale data; write-then-read from the same session fails
Long-running transactions → lock contention → cascading queue depth
Bad query without index → full table scan pegging CPU and blocking short queries
Connection storm → 'too many connections' errors; mitigate with PgBouncer/ProxySQL
MVCC bloat → table gets slow over time from dead-tuple accumulation; VACUUM must run

Alternatives

NoSQL wide-column (Cassandra, DynamoDB) for horizontal write scale with weaker consistency
Document store (MongoDB) for schemaless flexibility
NewSQL (Spanner, CockroachDB, TiDB) — SQL API with horizontal scale + strong consistency, at a latency cost
Time-series DB (InfluxDB, TimescaleDB) for append-heavy metric data

Interview questions

You're at 10K writes/sec on a single Postgres primary. What breaks first?
How would you shard by user_id, and what queries become hard?
How do you handle a schema migration on a 100 GB table with zero downtime?
What's the difference between REPEATABLE READ and SERIALIZABLE, and when does it matter?
Read replica lag is 30 seconds. What now?
You have a 2-column index on (a, b) — will a query on b alone use it? Why or why not?

Systems that use this component

See how the real designs on this platform put sql-db to work — concrete usage context per system.

URL Shortener

beginner

RDS Postgres Multi-AZ for URL storage; boring and correct

Open system

Payment System

intermediate

Postgres with strict transactions for ledger; Multi-AZ synchronous replication

Open system

Ticket Booking

intermediate

Postgres for seat inventory with row-level locks

Open system

Twitter/X timeline

intermediate

MySQL (historical) for user data; Manhattan for tweets at scale

Open system

Instagram

intermediate

Postgres for user metadata + photo references

Open system

Slack

intermediate

MySQL for workspace metadata + user data

Open system

The story of relational databases

1970, IBM San Jose: a paper changes how we store data forever

In 1970, IBM mathematician Edgar F. Codd published "A Relational Model of Data for Large Shared Data Banks." His argument was radical: data should live in tables of rows with mathematical relationships between them, not in the tangled navigational trees his colleagues at IBM had spent a decade building. Any query should be expressible as algebra over sets. IBM ignored him for 4 years.

Then in 1974, IBM's System R project proved Codd right. It introduced SQL (originally "SEQUEL") and demonstrated a working query optimizer. A young engineer named Larry Ellison read the papers, sensed the opportunity, and shipped Oracle v2 in 1979 — before IBM. Ellison's bet on Codd's ideas made him a billionaire.

For the next 25 years, the world ran on relational databases: DB2, Oracle, SQL Server, and then the open-source revolutionaries MySQL (1995) and PostgreSQL (1996). Then in ~2009 came the NoSQL rebellion — MongoDB, Cassandra, DynamoDB. "SQL is dead," declared the blogs. The rebellion lasted about 5 years. By 2015, everyone realized: if your data has relationships and you want money-adjacent correctness, you want ACID transactions. NoSQL added SQL-like layers. Distributed SQL was born (Spanner, CockroachDB, YugabyteDB). Today, SQL is winning again.

The core insight: SQL databases give you four guarantees you can bet a business on — ACID (atomicity, consistency, isolation, durability). If your account balance is stored in a SQL database and you follow the rules, you literally cannot lose or double-count money in a crash. That's not a marketing claim; it's a mathematical property enforced by mechanisms we'll unpack below: MVCC, WAL, isolation levels, and B-tree indexes.

The four ACID guarantees

A — Atomicity

All statements in a transaction commit, or none do. No partial state visible.

C — Consistency

Every commit leaves the DB in a valid state (foreign keys, constraints, triggers all satisfied).

I — Isolation

Concurrent transactions don't interfere. Each behaves as if it's alone.

D — Durability

Once committed, data survives any crash (WAL forces bytes to disk before ack).

ACID is why banks run on SQL. No amount of "eventually consistent" engineering is a substitute for "the money in your account" being correct right now.

Historical timeline

1970
Codd's relational paper
Ted Codd publishes the relational model. Every SQL database traces back to this paper.
1974
IBM System R + SQL
IBM San Jose builds the first working relational DB. SEQUEL (later SQL) is born.
1979
Oracle v2 ships (before IBM)
Larry Ellison's startup RSI (later Oracle) releases the first commercial SQL DB — beating IBM to market by 2 years.
1983
IBM DB2 launches
IBM commercializes what became DB2. Sets the standard for enterprise-grade SQL.
1986
SQL standardized (ANSI SQL-86)
First ANSI SQL standard. Codifies syntax across vendors — the reason SQL knowledge is portable to this day.
1989
PostgreSQL project starts (Berkeley)
Michael Stonebraker leads the "Postgres" project — the "post-Ingres" successor. Becomes PostgreSQL in 1996.
1995
MySQL released
Monty Widenius releases MySQL (named after his daughter, My). Free + fast + simple → default for early web.
1996
PostgreSQL 6.0
First release under the PostgreSQL name. MVCC-based. Sets the tone for correctness over speed.
2005
SQLite reaches ubiquity
D. Richard Hipp's embedded SQL DB gets built into every phone, browser, and app. Runs on more devices than any other DB.
2008
Sun buys MySQL for $1B
Sun acquires MySQL AB. Peak of MySQL's influence. Oracle acquires Sun in 2010, triggering MariaDB fork.
2012
Google Spanner paper
Google publishes Spanner — the first globally-distributed, externally-consistent SQL DB. Uses atomic clocks (TrueTime). Rewrites what's possible.
2015
CockroachDB launches
Ex-Googlers Andy Kimball + Peter Mattis build open-source Spanner. Distributed SQL becomes a category.
2018
AWS Aurora dominates
Amazon Aurora — cloud-native MySQL/Postgres — becomes AWS's fastest-growing service. Storage separated from compute changes economics.
2022
PlanetScale + Neon serverless SQL
Serverless MySQL (PlanetScale on Vitess) and Postgres (Neon) reimagine dev experience — branch a DB like Git.
2024
SQL wins the AI era
Vector extensions (pgvector, DuckDB) mean SQL databases become the substrate for AI apps too. Postgres runs the world's embeddings.

Transaction lifecycle — the atomic promise

Every SQL transaction goes through this pipeline. Understanding it explains why crashes don't lose data and why concurrent transactions can co-exist without stepping on each other:

Watch the transaction state advance

Phase 1: BEGIN

In progress

Transaction ID (XID) assigned. Snapshot taken (in MVCC). No changes visible to others yet.

The magic moment is WAL flush. The transaction isn't "committed" until its redo log is safely on disk. If the DB crashes mid-transaction, recovery replays WAL — pending transactions that logged COMMIT are re-committed; in-flight ones are rolled back. This is durability.

The four SQL isolation levels — a trade-off ladder

Isolation is the "I" in ACID — and it's a spectrum, not a boolean. Higher isolation = fewer concurrency bugs but lower throughput. The ANSI standard defines four levels:

READ UNCOMMITTED

Anomaly risk

Anomalies: Everything possible (dirty reads too)

Pros: Highest throughput. Reads see any concurrent writes.

Cons: Reads uncommitted data — can vanish if writer rolls back. Almost no useful semantics.

Use for: Almost nothing. Some analytics OK with dirty reads.

READ COMMITTED (Postgres/Oracle default)

Anomaly risk

Anomalies: Non-repeatable reads, phantoms, lost updates

Pros: Fast. Each statement sees only committed data.

Cons: Same query twice in one transaction can return different rows if a concurrent writer committed between.

Use for: Default for most workloads. OK when reads don't need consistency across statements.

REPEATABLE READ (MySQL InnoDB default)

Anomaly risk

Anomalies: Phantoms possible in some DBs; Postgres also prevents them

Pros: Same rows read twice in a transaction return same values. Great for reports.

Cons: Slightly slower. Longer-lived snapshots increase VACUUM debt.

Use for: Financial reports, complex read-heavy transactions where snapshot consistency matters.

SERIALIZABLE

Anomaly risk

Anomalies: None. Transactions behave as if run one at a time.

Pros: Correct by definition. No anomalies possible.

Cons: Slowest — under contention, some transactions retry due to serialization errors.

Use for: Money movement, inventory counts, anywhere correctness is non-negotiable.

Practical guide: Start with default (READ COMMITTED in Postgres, REPEATABLE READ in MySQL). Escalate to SERIALIZABLE for money movement. Never use READ UNCOMMITTED unless you have a truly weird analytics use case. When you see "serialization failure" errors — that's the DB doing its job. Retry the transaction; don't downgrade.

Concurrency: MVCC vs Locking (why Postgres readers never block)

Two philosophies compete for handling concurrent transactions. The choice shapes latency, throughput, and even VACUUM behavior:

Locking (SQL Server, older MySQL)

Readers acquire shared locks. Writers acquire exclusive locks. Readers block writers and vice versa. Simple, deterministic.

T1: SELECT * FROM users WHERE id=1
# holds S-lock
T2: UPDATE users SET name='X' WHERE id=1
# WAITS for T1

Pros: Simple to reason about. Fewer moving parts.

Cons: Readers block writers. Long-running SELECTs kill throughput. Deadlocks common.

Use for: Legacy SQL Server / MySQL MyISAM workloads.

MVCC (Postgres, Oracle, InnoDB)

Every write creates a new row version tagged with its XID. Readers see the version visible at their snapshot. Readers never block writers.

T1: SELECT * FROM users WHERE id=1 # sees v1
T2: UPDATE users SET name='X' WHERE id=1 # creates v2
T1: SELECT * FROM users WHERE id=1 # still sees v1

Pros: Readers never block writers. Snapshot isolation is trivial. High concurrency.

Cons: Multiple row versions → storage overhead. Requires VACUUM to reclaim.

Use for: Anything modern. Default in Postgres, InnoDB, Oracle.

MVCC is why Postgres feels magical: a long-running analytical query can run for minutes without blocking OLTP writes. The cost is that PG must periodically VACUUM dead row versions — neglect VACUUM and you get table bloat. Aurora/CockroachDB improve this with distributed GC.

WAL: how a crash never loses committed data

Write-Ahead Log (WAL) is the invention that made durability practical. Rule: every change is logged to disk BEFORE the actual data page is updated in memory. On crash, replay the log. Try the simulator:

Client

UPDATE accounts SET balance=100 WHERE id=42

Step 1 — buffer pool

DB updates row in memory (fast, not durable yet)

Step 2 — WAL (fsync)

Redo log entry forced to disk. Only now: ack client. THIS is the commit moment.

Step 3 — dirty page flush

Later, background writer flushes updated pages to data files. Not on the critical path.

DB is running normally. Every commit fsyncs WAL before ack.

Why fsync latency matters: the "COMMIT" call blocks on fsync. On spinning rust: 5-10ms. On modern NVMe: 50-100μs. This is why cloud DBs use log-structured storage on fast SSDs. Aurora amortizes it further by acking commits after 4-of-6 storage nodes acknowledge the WAL entry.

Eight index types — pick the right shape for your query

"Add an index" is the most common performance advice. The right index type matters as much as the columns you index. Postgres alone ships eight:

B-tree (default)

When: Equality + range queries: WHERE id=42, WHERE date BETWEEN ...

How: Balanced tree, O(log n) lookup. The workhorse.

Trade-off: Doesn't help with LIKE '%foo' (leading wildcard).

Hash

When: Only equality: WHERE id=42

How: O(1) lookup. Very fast for equality.

Trade-off: No range queries. Postgres hash index still trails B-tree in practice.

GIN (inverted)

When: Full-text search, JSONB queries, array containment

How: Maps each token/key to a list of rows. Great for many-value columns.

Trade-off: Slower writes. Larger index.

GiST

When: Geospatial (PostGIS), full-text, custom operators

How: Generalized search tree — pluggable index for spatial, range, etc.

Trade-off: Slower than B-tree for scalar equality.

BRIN

When: Very large tables with natural ordering (time-series)

How: Stores min/max per block range. Tiny index.

Trade-off: Only useful when data is physically ordered by indexed column.

Covering (INCLUDE)

When: Read-heavy queries: SELECT a, b WHERE c=?

How: B-tree that ALSO stores non-key columns → no heap fetch needed.

Trade-off: Larger index. Postgres 11+, MySQL 8+.

Bloom

When: Multi-column exact-match filters

How: Probabilistic — false positives OK, false negatives never.

Trade-off: Postgres bloom extension only. Special use cases.

Vector (IVF/HNSW)

When: AI embeddings: nearest-neighbor search

How: Approximates nearest-vector search in high-dim space (pgvector).

Trade-off: Recall vs speed trade-off. Rebuild-heavy.

Default: B-tree covers 90% of queries. Reach for GIN when you have JSONB or full-text. BRIN when the table is huge and time-ordered. Vector when you're building AI. Never add indexes "just in case" — every index slows writes and consumes disk.

Four replication topologies — how SQL scales beyond one machine

A single SQL server maxes out at ~10-50K writes/sec depending on workload. Beyond that, you need replication. Four topologies dominate:

Primary + async replicas

  Client
    ↓
  Primary → Replica 1 (read-only)
           → Replica 2 (read-only)

Reads: Route to replicas. Scale reads horizontally.

Writes: Only primary. Vertical scaling limit.

Failover: Manual or automated promote (Patroni, RDS multi-AZ) — ~30s-2min

Data loss on crash: Up to seconds of unreplicated commits if primary dies uncleanly.

Use when: Default. Most Postgres/MySQL deployments look like this.

Product comparison

Database	Model	Strength	Weakness
PostgreSQL	OSS	Feature-richest OSS SQL. JSONB, full-text, extensions (PostGIS, pgvector, TimescaleDB)	MVCC bloat if VACUUM neglected. Single-writer scaling ceiling.
MySQL / MariaDB	OSS	Simple, fast, huge community. Battle-tested at web scale.	Less feature-rich than Postgres. Sub-tree of features are Oracle-controlled.
SQL Server	Commercial	Best-in-class tooling. Deep Windows/AD integration. Robust transactional workloads.	Expensive licenses. Linux support catching up but not first-class.
Oracle Database	Commercial	The enterprise standard. Unmatched performance tuning. RAC for HA.	Very expensive. Complex to operate. License audits are notorious.
SQLite	OSS (embedded)	Zero config. In-process. Runs on every phone. Correct + tiny.	Single writer. No network access. Not for concurrent server workloads.
Amazon Aurora	Managed	Cloud-native MySQL/Postgres. Storage/compute separated. 15 replicas, <100ms failover.	AWS lock-in. More expensive than raw RDS at low scale.
Google Cloud Spanner	Managed	First truly global, strongly-consistent SQL. TrueTime clocks. Powers Google Ads.	GCP-only. Expensive at low scale. Limited SQL subset historically (improving).
CockroachDB	OSS + Managed	Postgres-compatible distributed SQL. Multi-region OSS.	Higher latency than single-node PG. Complex to operate self-hosted.
YugabyteDB	OSS + Managed	Postgres compatible, distributed. Two APIs (SQL + Cassandra). Multi-region.	Younger community than Cockroach. Docs less mature.
PlanetScale (Vitess)	Managed	MySQL at YouTube scale. DB branches like Git. Zero-downtime schema changes.	MySQL-only. Managed-only after 2024. Foreign key gotchas in sharded mode.
Neon	Managed serverless	Serverless Postgres. Branch DBs like Git. Scale-to-zero cheap.	Cold start latency. Newer product.
TiDB	OSS + Managed	MySQL-compatible distributed SQL + HTAP (row + column store).	Complex operationally. Ecosystem outside China smaller.

How to choose: Default for new project → PostgreSQL. AWS shop, want managed → Aurora Postgres. Need global writes → Spanner (if on GCP) or CockroachDB. Web-scale MySQL → PlanetScale. Embedded / edge → SQLite. Serverless dev workflow → Neon. Enterprise + Windows → SQL Server.

12 real-world SQL patterns

Stripe

MongoDB → PostgreSQL migration for consistency

Stripe famously migrated key workloads from MongoDB to Postgres as their transaction volume grew. Reason: they needed strict serializable semantics for balance updates, refunds, disputes. MongoDB's consistency model was too weak for money. Today, Stripe runs on hundreds of Postgres clusters — most sharded by merchant ID.

Airbnb

MySQL at 4B searches/day

Airbnb runs on MySQL 8 — sharded, replicated, backed by Vitess for horizontal scale. Every booking, every search, every review passes through it. Migrated off huge Ruby-monolith DB to a set of service-owned databases. Vitess handles cross-shard queries. Reliability > feature-richness in their tech philosophy.

GitHub

MySQL at 100M+ users

GitHub runs on MySQL + Vitess too. Every commit, PR, issue, review — all ACID. They open-sourced gh-ost, an online schema migration tool for MySQL. Handles schema changes at their scale without downtime. Runs vast fleets of replicas.

Instagram (Meta)

PostgreSQL scaled to billions

Instagram famously stayed on PostgreSQL even after Meta acquisition. Sharded early (by user ID hash). Every photo upload, like, comment: ACID transaction. They wrote extensive blog posts on Postgres tuning for their scale. Proves single-machine SQL can go further than most think.

Uber

Postgres → MySQL → Postgres, then own DB

Uber's famous "Why We Left Postgres" blog post (2016) caused a firestorm. They moved to MySQL, then partially back to Postgres, and eventually built Schemaless — a MySQL-backed KV layer with schema evolution. Lesson: no DB is one-size-fits-all at extreme scale.

Notion

Postgres partitioning: 200x table split

Notion's "blocks" table hit 200TB. They partitioned it across 480 shards using consistent hashing. Zero downtime migration described in their engineering blog. Every page-load = ~50 partition lookups. Postgres routes them. Proves you can shard Postgres if you invest.

Discord

Cassandra → ScyllaDB → PG for latest features

Discord runs messages on ScyllaDB (Cassandra-compatible) but keeps user profiles, servers, permissions in PostgreSQL. Hybrid: right DB for right shape. Postgres for entities with relationships; ScyllaDB for the high-cardinality message store.

Postgres + Cassandra hybrid

Reddit stores votes, comments, submissions in Postgres, and hot-page rendering data in Cassandra. Postgres gives them ACID for authoritative data. Cassandra gives them denormalized read paths. The pattern: SQL as system of record, NoSQL as fast-serving cache.

Robinhood

Postgres + Debezium CDC to Kafka

Robinhood uses Postgres as the source of truth for accounts + orders. Debezium streams every commit to Kafka via logical replication. Downstream services (fraud, analytics) consume the stream. This is the modern "SQL as event source" pattern.

Cloudflare

PostgreSQL for control plane, KV for data

Cloudflare runs PostgreSQL for their control plane (accounts, DNS zone metadata, WAF rules). The edge (300+ PoPs) reads distributed KV. Any config change in PG propagates to the edge in seconds via change stream. ACID control + high-availability delivery.

Figma

Vertical PG → horizontal sharding

Figma ran on a single vertically-scaled Postgres until 2023 (r6i.32xl!). Then they sharded — described in "How Figma's databases team lived to tell the scale." Postgres-native logical replication for zero-downtime migration. Proves how far vertical PG can go.

Aurora at Coinbase

Managed Postgres for money movement

Coinbase runs a huge fleet of Aurora PostgreSQL instances. Money movement requires strict ACID; Aurora gives them PG semantics + AWS-managed HA + 15 replicas. Every buy/sell/withdraw = ACID transaction. Aurora's 6-way replicated storage means zero data loss on AZ failure.

Key takeaways

1SQL databases give you ACID — the 4 guarantees your business logic depends on. Never trade this for perf unless you know exactly why.
2MVCC is why modern DBs feel non-blocking. Every write creates a version; readers see their own snapshot. Pay attention to VACUUM.
3Isolation levels are a ladder of correctness vs throughput. Default (READ COMMITTED / REPEATABLE READ) works for 90% of workloads. SERIALIZABLE for money.
4The WAL is what makes crashes safe. Every commit fsyncs the log before ack. Recovery replays it. Fast SSDs make this practical.
5Indexes beyond B-tree matter more than most engineers know. GIN for JSONB, BRIN for time-series, vector for AI, covering for hot read paths.
6Vertical Postgres scales further than you think — Figma ran on one r6i.32xl to $10B valuation. Only shard when you must.
7Managed cloud SQL (Aurora, Cloud SQL, Neon) removes 90% of operational pain. Unless you have a strong DBA, just use it.
8Distributed SQL (Spanner, CockroachDB, Yugabyte) is now viable for multi-region correctness. Comes with new latency profile — measure carefully.

References & further reading

• Codd, E. F. (1970). "A Relational Model of Data for Large Shared Data Banks." CACM 13(6). The paper that started it all.
• Chamberlin, D. & Boyce, R. (1974). "SEQUEL: A Structured English Query Language." IBM San Jose. The birth of SQL.
• Gray, J. & Reuter, A. (1993). Transaction Processing: Concepts and Techniques. Morgan Kaufmann. The bible of transactional systems.
• Berenson et al. (1995). "A Critique of ANSI SQL Isolation Levels." ACM SIGMOD. Explains why isolation levels are trickier than they seem.
• Corbett, J. et al. (2012). "Spanner: Google's Globally Distributed Database." OSDI. The paper that redefined distributed SQL.
• Kleppmann, M. (2017). Designing Data-Intensive Applications. O'Reilly. Modern SQL context, especially chapters 7 & 9.
• PostgreSQL Docs: "Concurrency Control" chapter. The clearest explanation of MVCC anywhere.
• Aurora Whitepaper (SIGMOD 2017): "Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases."
• Uber Engineering (2016): "Why We Left Postgres." The (in)famous post. Read alongside the many rebuttals.
• Instagram Engineering: "Sharding & IDs at Instagram." How they scaled Postgres to billions.
• Figma Engineering (2023): "How Figma's databases team lived to tell the scale." Real-world migration to sharded PG.