Free lesson · System Design Fundamentals

Relational Databases

Medium10 min readsystem-designhlddistributed-systems

Lesson 13.1 - Relational Databases

The first lesson of Module 13.

Relational databases have been the backbone of software engineering since Edgar Codd published "A Relational Model of Data for Large Shared Data Banks" in 1970. Fifty-five years later, they remain the default choice for the vast majority of applications - and for good reason. The relational model isn't just "tables with rows and columns" - it's a mathematically grounded framework for organizing, querying, and protecting data that has survived every technology hype cycle from object databases to XML databases to NoSQL to NewSQL to AI. Every competitor defines itself against the relational model, and most eventually adopt relational features (MongoDB added JOINs and ACID transactions; Cassandra adopted a SQL-like query language).

This lesson covers how relational databases actually work, why they're so resilient as a category, and when they're genuinely the right (or wrong) architecture choice.

— Brandon

The Relational Model: Why It Endures

The core insight of the relational model is data independence - the physical storage of data is separated from the logical view of data. You describe WHAT you want (SQL), not HOW to get it. The database engine decides the optimal execution strategy - which indexes to use, which join algorithm to apply, which order to scan tables. This separation means:

1. Applications don't break when the storage changes. You can add an index, repartition a table, change the storage engine, or migrate from HDD to SSD - the SQL queries don't change. Compare this with key-value stores (where you choose the access pattern at design time and changing it means redesigning the data model) or wide-column databases (where the table design IS the query design).

2. Ad hoc queries are free. A product manager asks "what was our revenue by region last quarter, excluding refunds over

3. Normalization prevents data anomalies. A customer's address is stored once (in the customers table), not duplicated across orders, shipments, and invoices. When the address changes, one UPDATE fixes it everywhere. In document databases where the address is embedded in every order document, the same change requires updating every document that contains it - and if you miss one, the data is inconsistent.

The Internal Architecture

flowchart TD
    Client["Client\n(application)"]
    
    Parser["SQL Parser\nParse SQL text into\nabstract syntax tree"]
    
    Planner["Query Planner / Optimizer\nConsider available indexes\nEstimate row counts\nChoose cheapest execution plan"]
    
    Executor["Query Executor\nExecute the chosen plan\nJoin algorithms, scans, sorts"]
    
    Buffer["Buffer Pool (shared memory)\nCache frequently accessed\ndata pages in RAM\n(avoid disk reads)"]
    
    Storage["Storage Engine\nRead/write data files on disk\nB-tree indexes\nWAL for durability"]
    
    WAL["Write-Ahead Log (WAL)\nSequential writes to disk\nAtomicity + Durability\n(crash recovery)"]
    
    Disk["Disk (SSD/HDD)\nData files + WAL files\nPersistent storage"]
    
    Client --> Parser --> Planner --> Executor --> Buffer
    Buffer <--> Storage
    Storage --> WAL --> Disk

    style Planner fill:#fbbf24,stroke:#f59e0b,color:#000
    style Buffer fill:#34d399,stroke:#10b981,color:#000
    style WAL fill:#a78bfa,stroke:#8b5cf6,color:#000

A diagram showing the internal architecture of a relational database. The client sends a SQL query. The SQL parser converts the text into an abstract syntax tree - the database's internal representation of the query. The query planner (or optimizer) is the most sophisticated component: it considers available indexes, estimates how many rows each table scan or index scan will produce, evaluates multiple possible execution plans, and chooses the one with the lowest estimated cost. This optimizer has been refined over decades and makes decisions that would take engineers hours to analyze manually. The executor runs the chosen plan - performing table scans, index lookups, joins (nested loop, hash, merge), sorts, and aggregations. Data is accessed through the buffer pool - a shared memory cache of frequently accessed data pages. If the needed page is in the buffer pool (cache hit), no disk I/O is needed. If not (cache miss), the page is read from disk into the buffer pool. For writes, the storage engine writes to the write-ahead log (WAL) first - a sequential, append-only file on disk. The WAL ensures atomicity (incomplete transactions can be rolled back on crash recovery) and durability (committed transactions are on disk before COMMIT returns). The actual data files are updated from the WAL asynchronously. This architecture means reads are often served from RAM (buffer pool), writes go to a sequential log (fast), and crash recovery is guaranteed by the WAL. It's been optimized for 40 years and is extremely efficient.

The Query Optimizer: The Secret Weapon

The query optimizer is what makes relational databases remarkable. Consider this query:

SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'US'
  AND o.created_at > '2026-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

The optimizer evaluates multiple strategies:

• Scan all users, filter by country, then join with orders? Or scan orders by date, then join with users?
• Use the index on users.country? The index on orders.created_at? Both? Neither?
• Nested loop join (good for small result sets)? Hash join (good for large)? Merge join (good when both sides are sorted)?
• Estimate: the country = 'US' filter selects 40% of users (too many for an index scan - sequential scan is cheaper). The created_at > '2026-01-01' filter selects 5% of orders (selective enough for an index scan).

The optimizer does this cost estimation automatically, using table statistics (row counts, value distributions, correlation data) maintained by the ANALYZE command. It chooses the plan with the lowest estimated disk I/O + CPU cost. You can see the chosen plan with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) ...;

-- Output:
-- Limit (cost=1245.23..1245.25 rows=10)
--   -> Sort (cost=1245.23..1248.56 rows=1332)
--     -> HashAggregate (cost=1200.45..1213.77 rows=1332)
--       -> Hash Join (cost=156.78..1180.12 rows=4056)
--           Hash Cond: (o.user_id = u.id)
--           -> Index Scan using idx_orders_created on orders o
--               Filter: (created_at > '2026-01-01')
--               Rows Removed by Filter: 85432
--           -> Hash (cost=145.00..145.00 rows=940)
--             -> Seq Scan on users u
--                 Filter: (country = 'US')

The optimizer chose: index scan on orders (selective date filter), sequential scan on users (non-selective country filter - 40% of rows), hash join to combine them, hash aggregate for GROUP BY, sort for ORDER BY, limit for top 10. A different data distribution would produce a different plan - the optimizer adapts to your actual data.

No other database category has this. MongoDB's aggregation pipeline, Cassandra's CQL, DynamoDB's queries - they all require the developer to choose the access strategy. The relational optimizer does it automatically, and it's usually right.

The Canonical Examples

PostgreSQL - The Default Choice

PostgreSQL is the most advanced open-source relational database. It supports: MVCC for concurrent reads and writes without locking (readers never block writers), advanced indexing (B-tree, hash, GIN for full-text/JSON, GiST for spatial, BRIN for time-series), JSON/JSONB columns (document-model flexibility within a relational database), extensions (PostGIS for geospatial, pgvector for embeddings, TimescaleDB for time-series, Citus for horizontal sharding), and full SQL compliance including window functions, CTEs, lateral joins, and recursive queries.

Performance at scale: a single PostgreSQL instance on modern hardware handles 50,000-100,000 queries per second (simple lookups). With connection pooling (PgBouncer), read replicas, and proper indexing, PostgreSQL serves applications with millions of users. Stripe, Instagram (early years), Reddit, GitLab, and Shopify all run on PostgreSQL.

When PostgreSQL specifically: it's the default for any new project unless you have a specific reason to choose something else. Its extension ecosystem means you often don't need a second database - pgvector for embeddings, PostGIS for geospatial, TimescaleDB for time-series, all within PostgreSQL.

MySQL - The Web Workhorse

MySQL powered the LAMP stack era (Linux, Apache, MySQL, PHP) and remains the most deployed database globally. Facebook, Twitter (now X), Uber, Airbnb, and GitHub all run massive MySQL deployments. MySQL's InnoDB storage engine provides ACID transactions, MVCC, and crash recovery.

MySQL vs PostgreSQL (honest comparison):

• MySQL is simpler to operate and has better replication tooling out of the box (MySQL's binlog replication is battle-tested at extreme scale). Facebook runs thousands of MySQL instances.
• PostgreSQL has a more complete SQL implementation, richer indexing, better JSON support, and a more extensible architecture. For new projects with no existing MySQL infrastructure, PostgreSQL is usually the better choice.
• Both are excellent. The difference matters less than engineers think - pick the one your team knows.

SQL Server - The Enterprise Standard

Microsoft SQL Server dominates enterprise Windows environments. It includes built-in business intelligence (SSRS, SSAS), a mature management studio (SSMS), and tight integration with the .NET ecosystem. If your organization is a Microsoft shop, SQL Server is the natural choice.

When Relational Databases Win

When Relational Databases Struggle

1. Horizontal write scaling. When write volume exceeds a single machine's capacity (tens of thousands of writes per second on complex tables), sharding is required - and sharding a relational database breaks cross-shard JOINs, complicates transactions, and requires application awareness. Wide-column databases (Cassandra, DynamoDB) handle horizontal write scaling more naturally because they're designed for it from the ground up.

2. Schema evolution at high velocity. Adding a column to a billion-row table in PostgreSQL can lock the table for minutes (for some ALTER TABLE operations) or require online migration tooling (pg_repack, gh-ost for MySQL). Document databases handle schema evolution trivially - just write documents with the new fields.

3. Hierarchical/deeply nested data. A 5-level nested JSON object (product → variants → options → prices → discounts) requires 5 tables with JOINs in a relational model. A document database stores it as one document - one read, no JOINs. PostgreSQL's JSONB column type partially addresses this (store the nested data as JSON within a relational table), but querying deep inside JSON is less efficient than querying normalized columns.

4. Graph traversals at depth. "Find all users within 4 degrees of connection from Alice" requires 4 self-JOINs on a large users/connections table - each JOIN multiplies the row count. Graph databases (Neo4j) traverse these paths in constant time per hop. At depth 4-6, the performance difference is orders of magnitude.

A Brandon Story: The Database That Handled Everything

At Amazon, I watched a team over-engineer their architecture. They planned: PostgreSQL for user data, MongoDB for product catalog (flexible schema), Redis for sessions, Elasticsearch for search, and Cassandra for event logging. Five databases for a product that had 2,000 users.

I suggested: start with PostgreSQL for everything. User data - obviously relational. Product catalog - use JSONB columns for flexible attributes within a relational table (each product has attributes JSONB for brand-specific fields that vary). Sessions - PostgreSQL with a TTL-based cleanup job (or Redis if latency is critical, but measure first). Search - PostgreSQL's full-text search (tsvector/tsquery) for moderate search needs. Event logging - PostgreSQL partitioned tables with time-based partitions and automatic partition pruning.

They launched with one PostgreSQL instance. It handled everything - 50,000 daily active users within 6 months. The JSONB columns gave them MongoDB's schema flexibility within PostgreSQL's transaction guarantees. Full-text search handled 95% of search queries; they added Elasticsearch later only for advanced features (faceted search, typo tolerance) at 200,000 users. They never needed Cassandra - PostgreSQL partitioned tables handled event logging at their scale.

What they saved: four fewer databases to operate, monitor, back up, upgrade, and debug. Four fewer failure modes. Four fewer sets of connection pools, credentials, and operational runbooks. The engineering team spent time building product features instead of managing database infrastructure.

When they DID add a second database (Elasticsearch at 200K users): they had clear evidence that PostgreSQL's full-text search couldn't handle the query complexity their product required (faceted filtering across 50 dimensions with typo tolerance). The decision was data-driven, not speculative.

Lessons:

• PostgreSQL handles far more than most engineers assume. JSONB, full-text search, partitioning, and PostGIS cover use cases that engineers reflexively assign to specialized databases.
• Every additional database is an operational cost - monitoring, backups, failover, credentials, connection pooling, expertise. Don't add one without evidence that the primary database can't handle the workload.
• "Start with PostgreSQL, add specialized databases when measured need arises" is the right default strategy. Not because PostgreSQL is the best at everything - it's not - but because the operational cost of polyglot persistence is real, and most applications don't need it until they're much larger than engineers expect.
• The most common database architecture mistake isn't choosing the wrong database - it's choosing too many databases too early.

Six Takeaways from This Lesson

1. The relational model's enduring strength is data independence - applications describe WHAT they want (SQL), the optimizer decides HOW. Ad hoc queries, schema changes, and storage optimizations don't break applications.

2. The query optimizer automatically evaluates execution plans and chooses the cheapest one - using table statistics, available indexes, and cost models refined over decades. No other database category has this.

3. PostgreSQL is the default choice for new projects. JSONB for flexible attributes, PostGIS for geospatial, pgvector for embeddings, full-text search for moderate search needs - one database covers most use cases.

4. Relational databases win when you need multi-entity transactions, complex queries (JOINs, aggregations, window functions), data integrity (constraints, foreign keys), or ad hoc reporting.

5. Relational databases struggle with horizontal write scaling beyond one machine, high-velocity schema evolution on large tables, deeply nested data (5+ levels), and deep graph traversals (4+ hops).

6. Start with one database (PostgreSQL). Add specialized databases only when measured evidence shows the primary can't handle a specific workload. Every additional database is an operational cost.

Try It

Exercise: Warmup (15 minutes). Run EXPLAIN ANALYZE on a query in a PostgreSQL database (or an online playground like SQLFiddle/db-fiddle.com). Read the output: what join algorithm did the optimizer choose? Did it use an index? What was the actual vs estimated row count?

Exercise: Core (30 minutes). Design a PostgreSQL schema for a multi-tenant SaaS project management tool. Include: organizations, users, projects, tasks, comments. Define foreign keys, constraints (task status must be one of 'todo', 'in_progress', 'done'), and at least one JSONB column for flexible metadata. Write a query that JOINs 3+ tables.

Exercise: Stretch (45 minutes). Read the PostgreSQL documentation on JSONB indexing. Create a table with a JSONB column, insert 10,000 rows with varied JSON structures, create a GIN index on the JSONB column, and compare query performance with and without the index using EXPLAIN ANALYZE.

What's Next

Lesson 13.2 covers document databases - MongoDB, CouchDB, and Firestore. The document model, when it fits naturally, when it creates problems, and the honest trade-offs against relational.

Back to System Design Fundamentals