SQL Databases (PostgreSQL, MySQL) — ACID, Joins, Strong Consistency

Tier 1 — Building Block

1. What Is It?

A relational database stores data in tables with predefined schemas, enforces relationships between tables (foreign keys), and provides SQL as a query language for flexible data retrieval including multi-table JOINs, aggregates, and subqueries. The defining property is ACID: Atomicity, Consistency, Isolation, Durability — a guarantee that transactions execute as all-or-nothing, leave the database in a valid state, are isolated from concurrent transactions, and survive crashes.

SQL databases are the default starting point for most application data. They handle reads, writes, complex queries, and transactional consistency on a single node with battle-tested reliability. The question is never "should I use a relational database?" — it's "when do I need to move beyond one?"

2. How It Works

ACID Properties

MVCC (Multi-Version Concurrency Control)

Both PostgreSQL and MySQL/InnoDB use MVCC to allow concurrent reads and writes without locking.

-+ResetFullscreen

MVCC = readers don't block writers; writers don't block readers. The tradeoff: old versions accumulate and must be periodically cleaned up (VACUUM in PostgreSQL, InnoDB purge in MySQL).

Query Execution: Index Lookup

-+ResetFullscreen

PostgreSQL vs. MySQL Key Differences

3. Variants & Comparisons

Isolation Levels

SQL standard defines 4 isolation levels. Higher isolation = fewer anomalies, more locking/blocking.

PostgreSQL implements MVCC-based Serializable Snapshot Isolation (SSI) — full serializability without pessimistic locking.

When SQL Scales (with standard techniques)

When SQL Doesn't Scale

4. When to Use It (and When NOT To)

Use SQL When:

• Relational data with JOINs: Users have orders; orders have items; items have categories — relational structure maps naturally to SQL tables.
• ACID transactions required: Transfer money between accounts; place an order that atomically reduces inventory and creates a payment — multi-table atomicity is a SQL strength.
• Complex ad-hoc queries: Analytics, reporting, "give me all users who bought product X in the last 30 days who haven't bought product Y" — SQL is expressive and flexible.
• Strong consistency required: Financial balances, booking reservations, inventory counts — you cannot tolerate stale reads.
• Most web applications: CRUD operations, user records, content management — SQL handles 95% of web application data needs.

Decision triggers:

• "Default to PostgreSQL unless you have a specific reason to choose otherwise"
• "If you need ACID transactions that span multiple tables → SQL"
• "If your data is relational (entities with relationships) and fits within one node → SQL"

Do NOT Use SQL When:

• Massive write throughput (>100K writes/sec sustained): A single SQL node can't keep up — use Cassandra, Kafka, or shard.
• Very large unstructured/semi-structured data: 10+ columns that are frequently null or vary per record — use a document store or add JSONB columns (PostgreSQL handles this better than most).
• Pure key-value lookups with sub-ms latency requirement: Use Redis; SQL can't match in-memory latency.
• Hierarchical or graph-heavy relationships: 6+ levels of self-referential joins kill SQL performance. Use a graph database (Neo4j).

Anti-patterns:

• N+1 queries: Loading N parent records and then making N separate queries for their children. Fix: JOIN in one query or use WHERE id IN (...) batch query.
• SELECT * in production: Fetches all columns including large ones (BLOB, TEXT) you don't need. Always SELECT only needed columns.
• No indexes on foreign keys and common WHERE columns: Table scans kill performance at scale. EXPLAIN ANALYZE every slow query.
• Large transactions holding locks: A transaction that holds a row lock for seconds while doing business logic blocks all concurrent writers. Keep transactions short.
• Using SQL for queue/polling pattern: SELECT ... FOR UPDATE SKIP LOCKED works but is inefficient at scale compared to a real queue (Redis, SQS). Don't build a job queue in PostgreSQL unless it's truly low-volume.

5. Real-World Usage

Instagram (PostgreSQL at scale): Instagram ran PostgreSQL as their primary database for years, sharding by user_id at the application layer. They used PostgreSQL for user profiles, photos metadata, follower graphs (early), and direct messages. When they hit the single-node write ceiling, they sharded horizontally — but kept PostgreSQL as each shard's engine. As of Meta's ownership, Instagram's core relational data still runs on PostgreSQL.

GitHub (MySQL at massive scale with Vitess): GitHub uses MySQL as their primary database, scaled with read replicas and eventually Vitess for horizontal sharding. GitHub's "gh-ost" tool (GitHub's online schema change tool for MySQL) was open-sourced because large-scale MySQL table migrations are a genuine operational challenge. GitHub serves ~100M developers from a MySQL + Vitess core.

Airbnb (MySQL + PostgreSQL): Airbnb uses MySQL for its core transactional data (bookings, payments, users) and PostgreSQL for specific use cases needing JSONB, advanced indexes, or PostGIS (geospatial). Their booking system requires strict ACID transactions — a failed booking must atomically return inventory and void the payment. SQL's guarantees are non-negotiable here.

6. Interview Cheat Sheet

Key sentences to demonstrate depth:

1. "PostgreSQL is my default. ACID guarantees, mature query planner, JSONB for semi-structured data, WAL-based streaming replication, and decades of production hardening. I only deviate when a specific constraint (write throughput, schema flexibility, graph queries) pushes me elsewhere."
2. "MVCC is how PostgreSQL achieves high concurrency: each transaction sees a snapshot of the database at its start time. Readers don't block writers; writers don't block readers. The cost is VACUUM — periodic cleanup of dead tuple versions."
3. "For ACID transactions spanning multiple tables: BEGIN / your operations / COMMIT. If anything fails, ROLLBACK. This is the correct tool for 'charge the credit card AND reduce inventory AND create the order' — all or nothing."
4. "The SQL scaling ladder: (1) Add indexes, (2) Connection pool (PgBouncer), (3) Read replicas for read scale, (4) Vertical scale, (5) Functional decomposition (split by service), (6) Horizontal sharding. Exhaust each step before moving to the next."
5. "The N+1 query problem is the most common SQL performance issue in practice: a loop in application code issues one query per item instead of batching. Fix with JOINs or WHERE IN (ids) batching."

Common follow-up questions:

Connections to other building blocks:

• Write-Ahead Log: PostgreSQL and MySQL use WAL for both crash recovery (ACID durability) and replication (streaming WAL to replicas). WAL is the foundation of SQL database reliability.
• Read Replicas: SQL databases scale reads via streaming replication to read replicas. PostgreSQL streaming replication is WAL-based; MySQL uses binlog replication.
• Sharding: SQL databases can be sharded (Instagram, GitHub), but it must be done at the application layer since most SQL databases don't shard natively. NewSQL (Spanner, CockroachDB) adds automatic sharding.
• Caching: Redis cache-aside in front of PostgreSQL is the most common scaling pattern — absorb hot reads in Redis, keep the DB for writes and complex queries.
• CAP Theorem: SQL databases are CP (Consistency + Partition Tolerance). They sacrifice availability: if the primary is down, writes fail (unless you have automatic failover).