Relational Databases

You probably have a relational database on your machine right now and don't know it. Every Mac and Linux ships with SQLiteA serverless, zero-configuration relational database that's embedded directly into your application. It stores the entire database in a single file. There are over 1 trillion SQLite databases in active use — it's in every iPhone, every Android, every Mac, every web browser (Chrome, Firefox, Safari), every copy of Windows 10+, every Airbus A350, and every Dropbox client. Richard Hipp created it in 2000 for the US Navy. Run sqlite3 test.db in your terminal right now — you just created a relational database.. Open a terminal and type sqlite3 test.db — congratulations, you just created a relational database. Zero install, zero config, zero excuses for not following along.

# Create a database, a table, insert a row, query it — in 10 seconds
sqlite3 test.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);"
sqlite3 test.db "INSERT INTO users VALUES (1, 'Ada Lovelace', 'ada@example.com');"
sqlite3 test.db "SELECT * FROM users;"
# Output: 1|Ada Lovelace|ada@example.com

# That's it. You just used a relational database.
# The file 'test.db' IS the database. You can copy it, email it, back it up.

Here's the map of what we'll cover: where relational databases came from (a single paper by one IBM researcher in 1970), why they've survived 54 years of "NoSQL will replace everything" hype cycles, how ACID actually works under the hood (with commands you can break and fix), and when you should — and shouldn't — reach for one.

You're building a payment system for a small online store. Day 1, you make the perfectly reasonable decision that every beginner makes: store everything in a JSON file. Users, orders, products — one big data.json. It works. Your 10 beta testers love it.

{
  "users": [
    { "id": 1, "name": "Alice", "state": "CA", "balance": 500 },
    { "id": 2, "name": "Bob", "state": "NY", "balance": 300 }
  ],
  "orders": [
    {
      "id": 101,
      "user_id": 1,
      "user_name": "Alice",
      "user_state": "CA",
      "items": [
        { "product": "Mechanical Keyboard", "price": 149.99 }
      ],
      "total": 149.99,
      "status": "paid"
    }
  ]
}

Looks clean. Feels simple. You ship it. Here's what happens over the next 30 days.

Let's do the math on that search time. A 500 MB JSON file with 10,000 users and 50,000 orders. To find one order, your code has to:

1. Read the entire 500 MB file into memory (that's half a gigabyte of RAM, just to find one order)
2. Parse the JSON (turning raw text into objects — at ~50 MB/sec for a good JSON parser, that's 10 seconds just for parsing)
3. Loop through every order object until you find the one you want — a linear scanChecking every single item one by one, from first to last. If you have 50,000 orders and the one you want is #49,999, you check all 49,998 before it. Average case: you check half of them. This is O(n) complexity. A database with an index can find the same record in O(log n) — for 50,000 records, that's ~16 lookups instead of 25,000., O(n)

That's 3+ seconds to find a single order. A relational database with an index does the same thing in 0.05 milliseconds. That's 60,000 times faster. Not a typo.

But the speed isn't even the worst part. The worst part is the corruption. Here's exactly how it happens:

# Two customers click "Buy" at the EXACT same moment.
# Your server spawns two processes to handle them.

# Process A (Alice's order):                    # Process B (Bob's order):
data = read_file("data.json")    # t=0ms       data = read_file("data.json")    # t=1ms
data["orders"].append(alice_order) # t=50ms     data["orders"].append(bob_order)  # t=51ms
write_file("data.json", data)    # t=100ms     write_file("data.json", data)    # t=102ms
                                                # ^^^ Bob's write OVERWRITES Alice's.
                                                # Alice's order is gone. $149.99 lost.

# Process B read the file BEFORE Process A wrote.
# So Process B's copy doesn't include Alice's order.
# When Process B writes, it saves its version — without Alice.
# No error. No warning. The data is just silently wrong.

This is the moment every developer hits. The JSON file was fine for prototyping. But your store has real customers now, real money, and real consequences when data goes wrong. You need something that can handle thousands of reads and writes per second, keep data consistent even when multiple people are writing at the same time, and survive crashes without losing a single transaction.

You need a database. But which kind?

You ask around. A coworker says: "Just use MongoDBA document-oriented NoSQL database that stores data as JSON-like documents (called BSON). Created in 2009 by 10gen (now MongoDB Inc.). Very popular for prototyping because the schema is flexible — you can store any shape of document without defining columns first. However, it doesn't enforce relationships between documents by default (no foreign keys), which means your application code has to maintain data consistency instead of the database.. It's faster, it's easier, it stores JSON natively — perfect for your data." And honestly, it sounds great. Your data is already JSON. MongoDB stores JSON documents. No schema to define. No migrations to run. Just shove the documents in.

// MongoDB: each order is a self-contained document
db.orders.insertOne({
  _id: ObjectId("..."),
  user: {
    id: 1,
    name: "Alice",
    state: "CA",
    email: "alice@example.com",
    address: "123 Market St, San Francisco, CA 94105"
  },
  items: [
    { product: "Mechanical Keyboard", category: "Electronics", price: 149.99 },
    { product: "USB-C Cable", category: "Accessories", price: 12.99 }
  ],
  total: 162.98,
  status: "paid",
  created_at: ISODate("2024-01-15T10:30:00Z")
});

// Fast read — everything in one document, no JOINs needed
db.orders.findOne({ _id: ObjectId("...") });
// Returns the complete order with user info embedded. Quick!

This works beautifully for simple reads. Fetch one order? Lightning fast — everything is in one document. No JOINsA SQL operation that combines rows from two or more tables based on a related column. For example, SELECT * FROM orders JOIN users ON orders.user_id = users.id merges each order row with its corresponding user row. JOINs are a superpower of relational databases — they let you store data ONCE (normalized) and combine it on the fly. Document databases don't have native JOINs, so you either duplicate data across documents or do multiple queries and combine the results in your application code. needed. MongoDB is living up to the hype. Your coworker was right.

Then your boss asks a simple question: "Show me all orders from users in California who spent more than $100 in the last 30 days, grouped by product category."

In your head, you think: "That's a basic business question. How hard can it be?"

db.orders.aggregate([
  // Step 1: Only orders from last 30 days
  { $match: {
      created_at: { $gte: new Date(Date.now() - 30*24*60*60*1000) }
  }},
  // Step 2: Only users in California (embedded in every document)
  { $match: { "user.state": "CA" } },
  // Step 3: Unwind the items array (one row per item)
  { $unwind: "$items" },
  // Step 4: Group by category, sum the prices
  { $group: {
      _id: "$items.category",
      total_spent: { $sum: "$items.price" },
      order_count: { $sum: 1 }
  }},
  // Step 5: Only categories over $100
  { $match: { total_spent: { $gt: 100 } } },
  // Step 6: Sort by total
  { $sort: { total_spent: -1 } }
]);

// 23 lines. A pipeline of stages. Each stage transforms the data.
// You had to learn a whole new query language ($match, $unwind, $group).
// And if you want to JOIN data from another collection? $lookup. Good luck.

SELECT p.category, SUM(oi.price) AS total_spent, COUNT(*) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.state = 'CA'
  AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY p.category
HAVING SUM(oi.price) > 100
ORDER BY total_spent DESC;

-- 8 lines. Reads like English:
-- "From users in CA, join their orders from the last 30 days,
-- group by product category, show only categories over $100."
-- No special syntax to learn. SQL was DESIGNED for this.

Look at the difference. The MongoDB version requires you to learn a custom aggregation pipeline syntax — $match, $unwind, $group, $sort — a whole domain-specific language. The SQL version reads almost like English: "from users in California, join their orders, group by category, where total is more than $100." SQL was literally designed for exactly this kind of question. It's been doing this since 1974.

But the query complexity isn't even the real problem. The real problem is what's hiding inside every order document. Look at the MongoDB document again. Alice's name, state, email, and address are embedded inside every single order she's ever made.

This is called denormalizationStoring the same data in multiple places for faster reads. In the MongoDB example, Alice's address is copied into every order document so you can read one order without looking up the user separately. The trade-off: writes and updates become dangerous. Change Alice's address? You must update every copy. Miss one, and your data is inconsistent. Document databases denormalize by default. Relational databases normalize by default (store each fact once) and use JOINs to combine data on the fly. — duplicating data across documents for read speed. And it's fine when data rarely changes. But user addresses change. Names change (people get married). Email addresses change. Every time something changes, you have to find and update every copy, across every document that embedded it. The more copies, the more chances you miss one. The more you miss, the more your reports lie to you.

MongoDB is a great tool for specific use cases (we'll cover when to use it later). But for a payment system with relational data — users who have orders, orders that contain items, items that reference products — you're swimming upstream. You need a system that was designed for relationships.

Let's name the four specific things that go wrong when you store relational data in flat files or document stores. These aren't theoretical — each one has cost real companies real money.

Let's prove sin #4 with real PostgreSQL commands. Open two terminal windows and connect to the same database.

-- Setup (run this first in either terminal):
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, stock INT);
INSERT INTO products VALUES (1, 'Limited Edition Keyboard', 1);

-- Alice's transaction:
BEGIN;
SELECT stock FROM products WHERE id = 1;
-- Returns: stock = 1. Great, it's in stock!

SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- ^^^ FOR UPDATE locks this row. Bob's transaction will WAIT here.
-- This is how relational databases prevent the oversell.

UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
-- Alice gets the keyboard. Stock is now 0.

-- Bob's transaction (run while Alice's is still open):
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- ^^^ Bob's query HANGS here. It's waiting for Alice's lock to release.
-- In a flat file, both would read stock=1 and both would succeed.
-- In PostgreSQL, Bob waits his turn.

-- After Alice commits, Bob's query returns:
-- stock = 0. Out of stock.

-- Bob's app checks: if stock <= 0, abort
ROLLBACK;  -- Bob doesn't get the keyboard. But no oversell happened.

-- That's ACID Isolation in action. You just prevented a $149.99 lawsuit.

Try this yourself. It takes 60 seconds with two terminal windows. The FOR UPDATE clause is the secret — it tells PostgreSQL "lock this row until I'm done with it." Any other transaction that tries to read the same row with FOR UPDATE has to wait. That's pessimistic lockingA concurrency control strategy where you lock the resource BEFORE modifying it, preventing anyone else from touching it until you're done. Called "pessimistic" because you assume conflicts WILL happen, so you lock preemptively. The opposite is "optimistic locking" — you don't lock, but you check at write time whether anyone else changed the data. Pessimistic is safer but slower (other transactions wait). Optimistic is faster but requires retry logic. PostgreSQL supports both. — the database assumes conflicts will happen and prevents them proactively.

A flat file can't do this. MongoDB can do single-document transactions (since version 4.0), but multi-document transactions are slower and come with caveats. Relational databases have been doing this correctly, efficiently, and at scale since the 1980s.

The year is 1970. Computers fill entire rooms. Data is stored in hierarchical databasesIBM's IMS (Information Management System), released in 1966 for the Apollo space program. Data is organized as a tree: a parent record has child records, which have grandchild records, etc. To find data, you navigate the tree from the root down. The problem: if you want to query across branches (e.g., "all orders across all customers in California"), you have to traverse the ENTIRE tree. Restructuring the hierarchy requires rewriting every application that uses it. It was a nightmare. (IBM's IMS) and network databasesThe CODASYL model (Conference on Data Systems Languages), formalized in 1969. An improvement on hierarchical: records can have MULTIPLE parents, forming a graph instead of a tree. But you still navigate by following pointers from record to record. Want a new query? You might need to add new pointer chains. Programmers spent more time managing pointers than working with actual data. (CODASYL). Both work like a maze — to find data, you follow pointers from one record to the next. Want to ask a question the original designers didn't anticipate? Tough luck. You'd need to restructure the entire database and rewrite every application that uses it.

Then an IBM researcher named Edgar F. Codd publishes a paper that changes everything: "A Relational Model of Data for Large Shared Data Banks" (Communications of the ACM, June 1970). The idea is radical in its simplicity:

IBM ignored him. They had billions invested in IMS and didn't want to cannibalize their own product. But a young programmer named Larry Ellison read the paper and saw the future. He founded a company to build a relational database. That company is OracleOracle Corporation, founded in 1977 by Larry Ellison, Bob Miner, and Ed Oates. They read Codd's papers and IBM's System R research papers (which described SQL), then built the first commercial relational database. Oracle v2 was released in 1979 — before IBM's own SQL/DS (1981). Ellison literally beat IBM to market using IBM's own research. Oracle is now a $300B company. Larry Ellison is one of the richest people on Earth..

Meanwhile, at UC Berkeley, a professor named Michael Stonebraker built an open-source relational database called IngresInteractive Graphics and Retrieval System, built at UC Berkeley from 1973-1975. One of the first relational database implementations. Ingres used its own query language called QUEL (not SQL). Stonebraker later built its successor, Postgres (1986), which evolved into PostgreSQL. Several Ingres students went on to create major databases: Robert Epstein co-founded Sybase (which became the basis for Microsoft SQL Server), and the Ingres codebase was commercialized as CA-Ingres. (1974). Then he built its successor: Postgres (1986). "Post-Ingres." That project became PostgreSQL — the database that today powers Instagram, Stripe, Shopify, and Reddit. Stonebraker won the Turing AwardThe highest honor in computer science, often called "the Nobel Prize of computing." Named after Alan Turing. Awarded annually by the Association for Computing Machinery (ACM). Codd won it in 1981 for the relational model. Stonebraker won it in 2014 for his contributions to database systems. The award carries a $1 million prize (funded by Google). in 2014. Codd won it in 1981.

Now let's see what Codd's model actually looks like in practice. Remember that messy JSON document from Section 2 where Alice's address was embedded in every order? Here's how a relational database handles the same data.

Normalization — From One Giant Blob to Clean Tables

NormalizationThe process of organizing data to eliminate redundancy. "Third Normal Form" (3NF) means: (1) every column depends on the primary key (1NF), (2) every column depends on the WHOLE key (2NF), and (3) every column depends on NOTHING BUT the key (3NF). The famous mantra: "The key, the whole key, and nothing but the key, so help me Codd." In practice: if data is duplicated, break it into a separate table and link with a foreign key. is the fancy word for "don't store the same thing twice." Instead of cramming everything into one massive document, you split data into separate tables, each storing one type of thing. Then you link them with keys. Here's the transformation:

Now let's create these tables for real. Copy and paste this into psql, mysql, or sqlite3:

-- Create the four normalized tables
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    state CHAR(2) NOT NULL,
    addr  TEXT
);

CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    category TEXT NOT NULL,
    price    DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    user_id    INT NOT NULL REFERENCES users(id),   -- FK: must exist in users
    total      DECIMAL(10,2) NOT NULL DEFAULT 0,
    status     TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
    id         SERIAL PRIMARY KEY,
    order_id   INT NOT NULL REFERENCES orders(id),   -- FK: must exist in orders
    product_id INT NOT NULL REFERENCES products(id),  -- FK: must exist in products
    quantity   INT NOT NULL DEFAULT 1,
    price      DECIMAL(10,2) NOT NULL                 -- price at time of purchase
);

-- Insert sample data
INSERT INTO users (name, email, state, addr) VALUES
    ('Alice', 'alice@example.com', 'CA', '123 Market St, SF'),
    ('Bob',   'bob@example.com',   'NY', '456 Broadway, NYC'),
    ('Carol', 'carol@example.com', 'CA', '789 Sunset Blvd, LA');

INSERT INTO products (name, category, price) VALUES
    ('Mechanical Keyboard', 'Electronics',  149.99),
    ('USB-C Cable',         'Accessories',  12.99),
    ('Monitor Stand',       'Furniture',    89.50),
    ('Webcam HD',           'Electronics',  79.00);

INSERT INTO orders (user_id, total, status) VALUES
    (1, 162.98, 'paid'),    -- Alice's order
    (3, 89.50,  'paid'),    -- Carol's order
    (1, 79.00,  'pending'); -- Alice's second order

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
    (1, 1, 1, 149.99),  -- Alice: Keyboard
    (1, 2, 1,  12.99),  -- Alice: USB-C Cable
    (2, 3, 1,  89.50),  -- Carol: Monitor Stand
    (3, 4, 1,  79.00);  -- Alice: Webcam

Now try to break it. Try inserting an order for a user that doesn't exist:

-- Try to create an order for user_id 999 (doesn't exist):
INSERT INTO orders (user_id, total, status) VALUES (999, 50.00, 'paid');
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- DETAIL: Key (user_id)=(999) is not present in table "users".

-- Try to delete Alice while she has orders:
DELETE FROM users WHERE id = 1;
-- ERROR: update or delete on table "users" violates foreign key constraint
-- DETAIL: Key (id)=(1) is still referenced from table "orders".

-- The database REFUSES to let you create orphaned data.
-- This is Codd's gift: the database guards your data's integrity.
-- No amount of "careful coding" in your application gives you this guarantee.

That's referential integrityThe guarantee that every foreign key value in one table actually points to an existing row in the referenced table. It's enforced by the database engine itself, not by your application code. This means it's impossible to have an orphaned order (pointing to a deleted user) or a dangling reference. Even if your application has bugs, even if someone manually runs SQL against the database, the constraint holds. You'd have to explicitly drop the constraint to break it.. The database doesn't trust your application code. It doesn't trust you. It enforces the rules at the storage layer, where they can't be bypassed. Your application can have bugs, your ORM can have bugs, someone can connect directly with psql and run raw SQL — and the constraints still hold.

The Magic Query

Remember your boss's question from Section 3? "All orders from California users who spent over $100 in the last 30 days, grouped by product category." Here it is, running against your four normalized tables:

-- "Show me California spending by category, over $100, last 30 days"
SELECT
    p.category,
    SUM(oi.price * oi.quantity) AS total_spent,
    COUNT(DISTINCT o.id) AS order_count
FROM users u
JOIN orders o       ON u.id = o.user_id          -- link users → orders
JOIN order_items oi ON o.id = oi.order_id         -- link orders → items
JOIN products p     ON oi.product_id = p.id       -- link items → products
WHERE u.state = 'CA'
  AND o.created_at >= NOW() - INTERVAL '30 days'
  AND o.status = 'paid'
GROUP BY p.category
HAVING SUM(oi.price * oi.quantity) > 100
ORDER BY total_spent DESC;

-- Read it out loud: "From users in California, join their paid orders
-- from the last 30 days, join the items, join the products, group by
-- category, only show categories where total > $100, sort by total."
--
-- You just described WHAT you want. The database figures out HOW.
-- It decides: use the index on users(state)? Hash join or nested loop?
-- Sequential scan or bitmap index scan? You don't care. It's optimal.

This is the core breakthrough of the relational model. You describe what you want. The database's query plannerThe component of a relational database that takes your SQL query and figures out the fastest way to execute it. It considers: which indexes exist, how many rows each table has, what the data distribution looks like (are most users in CA or NY?), how much memory is available, and dozens of other factors. Then it generates an "execution plan" — a step-by-step recipe. You can see the plan yourself: EXPLAIN ANALYZE before any query. PostgreSQL's planner is one of the most sophisticated pieces of software ever written. figures out how. This is called declarative queryingYou declare the result you want without specifying the steps to get it. SQL is declarative: "give me all users in California" doesn't say HOW to find them. Compare with imperative code: "open file, read line by line, check if state field equals CA, add to results list." The database engine translates your declarative query into an optimal imperative execution plan. This is why SQL has survived 50 years — the underlying execution can improve with every database version without changing your queries., and it's the reason SQL has survived for 50 years. The execution engine gets better with every version of PostgreSQL, MySQL, and SQL Server — and your queries automatically get faster without you changing a single line.

Let's see the planner in action. Run the same query with EXPLAIN ANALYZE in front:

EXPLAIN ANALYZE
SELECT p.category, SUM(oi.price * oi.quantity) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.state = 'CA'
GROUP BY p.category;

-- Output (simplified):
-- Hash Join  (cost=2.15..3.28 rows=2) (actual time=0.089..0.093 rows=2)
--   -> Seq Scan on users (cost=0..1.04 rows=2) (actual time=0.008..0.010)
--        Filter: (state = 'CA')
--   -> Hash Join  (cost=1.08..2.15 rows=4) (actual time=0.043..0.048)
--        -> Seq Scan on order_items
--        -> Hash  (cost=1.03..1.03 rows=3)
--              -> Seq Scan on orders
-- Planning Time: 0.285 ms
-- Execution Time: 0.127 ms
--
-- 0.127 milliseconds. The database read 4 tables, joined them,
-- filtered by state, grouped by category — in 127 MICROSECONDS.
-- Your JSON file took 3 seconds for a simpler query.

Edgar Codd didn't just invent a way to store data. He invented a way to think about data. Separate the logical structure (tables, rows, keys) from the physical storage (files, indexes, memory). Describe what you want, not how to get it. Enforce rules at the data layer, not the application layer. Fifty-four years later, every major technology company on Earth still uses his model. Not because they're stuck in the past — because nobody has found anything better for structured, relational data.

Section 1 mentioned ACID. Now let's prove each property with commands you can run in a real PostgreSQL terminal. These aren't theoretical — every demo below works on a fresh psql session. If you set up the tables from Section 5, you're ready to go.

Let's dive into each one. Every card below has commands you can paste into psql and see for yourself.

-- Setup: create two accounts
CREATE TABLE accounts (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00), ('Bob', 500.00);

-- Transfer $500 from Alice to Bob — ATOMICALLY
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Alice: 1000 → 500
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- Bob:   500 → 1000
COMMIT;

SELECT * FROM accounts;
-- id | name  | balance
-- 1  | Alice |  500.00
-- 2  | Bob   | 1000.00
-- Both updates happened. Money is conserved. Everyone's happy.

-- Reset balances for the test
UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500 WHERE id = 2;

-- Start the transfer but DON'T COMMIT
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  -- Alice's balance is 500 INSIDE this transaction...
  -- NOW: close the terminal. Kill the process. Ctrl+C. Whatever.

-- Open a NEW terminal, reconnect to psql, and check:
SELECT * FROM accounts;
-- id | name  | balance
-- 1  | Alice | 1000.00  ← UNCHANGED! The debit was rolled back.
-- 2  | Bob   |  500.00  ← UNCHANGED!
-- The WAL (Write-Ahead Log) protected you. No money vanished.

BEGIN;
  INSERT INTO users (name, email, state) VALUES ('Dave', 'dave@x.com', 'TX');
  SAVEPOINT sp1;

  -- Try something risky:
  INSERT INTO users (name, email, state) VALUES ('Eve', 'dave@x.com', 'CA');
  -- ERROR: duplicate key value violates unique constraint on email

  ROLLBACK TO sp1;  -- Undo only Eve's insert. Dave's insert is still alive.
  INSERT INTO users (name, email, state) VALUES ('Eve', 'eve@x.com', 'CA');  -- Fixed!
COMMIT;
-- Both Dave and Eve are inserted. The failed attempt was surgically removed.

-- 1. FOREIGN KEY: references must be valid
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- ON DELETE CASCADE: if you delete a user, their orders are auto-deleted too.
-- Alternative: ON DELETE RESTRICT (refuse to delete user if orders exist)

INSERT INTO orders (user_id, total) VALUES (99999, 50.00);
-- ERROR: Key (user_id)=(99999) is not present in table "users".
-- The database says: "That user doesn't exist. I won't let you lie."

-- 2. CHECK: custom rules on column values
ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price > 0);

INSERT INTO products (name, category, price) VALUES ('Free Thing', 'Test', -5.00);
-- ERROR: new row violates check constraint "positive_price"
-- Negative prices? Not in this database.

-- 3. UNIQUE: no duplicates allowed
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

INSERT INTO users (name, email, state) VALUES ('Fake Alice', 'alice@example.com', 'TX');
-- ERROR: duplicate key value violates unique constraint "unique_email"
-- Two users with the same email? Nope.

-- 4. NOT NULL: mandatory fields
ALTER TABLE users ALTER COLUMN name SET NOT NULL;

INSERT INTO users (name, email, state) VALUES (NULL, 'nobody@x.com', 'CA');
-- ERROR: null value in column "name" violates not-null constraint
-- Every user must have a name. Period.

-- When an order item is inserted, automatically reduce product stock
CREATE OR REPLACE FUNCTION reduce_stock()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE products SET stock = stock - NEW.quantity
  WHERE id = NEW.product_id;

  -- Prevent overselling
  IF (SELECT stock FROM products WHERE id = NEW.product_id) < 0 THEN
    RAISE EXCEPTION 'Not enough stock for product %', NEW.product_id;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_reduce_stock
  AFTER INSERT ON order_items
  FOR EACH ROW EXECUTE FUNCTION reduce_stock();

-- Now, inserting an order item automatically updates stock.
-- No application code needed. The database enforces it.

-- Make sure we have stock data
CREATE TABLE IF NOT EXISTS inventory (
    id SERIAL PRIMARY KEY, name TEXT, stock INT
);
INSERT INTO inventory VALUES (1, 'Limited Hoodie', 10)
  ON CONFLICT (id) DO UPDATE SET stock = 10;

-- Start a transaction and change the stock — but DON'T COMMIT
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE id = 1;

-- Stock is 9 inside THIS transaction.
-- But we haven't committed yet.
-- Go to Terminal 2 and run the SELECT there.
-- DON'T close this terminal. Leave the transaction open.

-- While Terminal 1's transaction is still open (not committed)...
SELECT stock FROM inventory WHERE id = 1;

-- What do you see?
-- Under READ COMMITTED (PostgreSQL default): stock = 10
--   → You see the OLD value. Terminal 1's change is invisible.
--   → This is SAFE. You're reading committed data only.

-- Under READ UNCOMMITTED: stock = 9
--   → You see Terminal 1's UNCOMMITTED change. This is a "dirty read."
--   → If Terminal 1 rolls back, you just made a decision based on fake data.
--   → PostgreSQL doesn't even support this level. That's how bad it is.

-- Check YOUR database's isolation level:
SHOW transaction_isolation;
-- Probably: "read committed" — the sane default.

-- Set isolation level per-transaction:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your queries here ...
COMMIT;

-- The four levels (from weakest to strongest):
--
-- 1. READ UNCOMMITTED — see other transactions' dirty writes
--    PostgreSQL ignores this; treats it as READ COMMITTED.
--    MySQL supports it. Almost never used in production.
--
-- 2. READ COMMITTED — only see committed data (PostgreSQL default)
--    Safe for most apps. Instagram uses this for feeds.
--
-- 3. REPEATABLE READ — snapshot at transaction start
--    Same SELECT returns same results throughout the transaction,
--    even if other transactions commit changes in between.
--    MySQL InnoDB default. Good for reports.
--
-- 4. SERIALIZABLE — transactions execute as if one-at-a-time
--    Slowest but safest. No anomalies possible.
--    Stripe uses this for payment processing.
--    If a conflict is detected, PostgreSQL aborts one transaction
--    and you must retry it.

-- Check your WAL configuration
SHOW wal_level;
-- 'replica' (default) — enough for streaming replication
-- 'logical' — needed for logical replication (CDC, Debezium)
-- 'minimal' — fastest but no replication possible

-- See your current position in the WAL
SELECT pg_current_wal_lsn();
-- Returns something like: 0/15A3D40
-- This is the Log Sequence Number — a pointer into the WAL stream.
-- Every committed transaction advances this number.

-- See actual WAL files on disk (run from shell, not psql):
-- ls -la /var/lib/postgresql/14/main/pg_wal/
-- Each file is 16MB by default. They accumulate until checkpointed.

-- The durability vs speed trade-off:
SHOW synchronous_commit;
-- 'on' (default): fsync every commit. Safest. ~600us per commit.
-- 'off': don't wait for fsync. Faster but can lose last ~600ms
--         of commits on crash. Some teams use this for log tables
--         or analytics data where losing a few rows is acceptable.

If ACID is the safety net, indexing is the speed engine. A query on a table with 10 million rows can take 30 seconds without an index and 2 milliseconds with one. The difference between "our site is down" and "our site is fast" is often a single CREATE INDEX statement.

Here's the intuition: imagine a 500-page textbook with no table of contents and no index at the back. To find every mention of "B-tree," you'd read every page. That's a sequential scanThe database reads every single row in the table, one by one, checking each against your WHERE clause. For a 10 million row table, that's 10 million checks. It's the slowest access pattern, but it's always correct — it never misses a row. PostgreSQL falls back to sequential scan when no suitable index exists, when the table is tiny (faster than the index overhead), or when you're selecting most of the rows anyway (a full scan beats 8 million individual index lookups).. Now imagine the textbook has an index: "B-tree: pages 47, 112, 289." You jump straight to those pages. That's a database index.

-- Create a B+ tree index on email (this is the default type)
CREATE INDEX idx_users_email ON users(email);

-- Verify the query planner uses it:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Index Scan using idx_users_email on users
--   Index Cond: (email = 'alice@example.com'::text)
--   Planning Time: 0.085 ms
--   Execution Time: 0.042 ms

-- B+ trees also handle range queries:
EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > '2024-01-01';
-- Index Scan using idx_orders_created_at (if you create one)
-- The tree finds the starting point, then follows leaf-node links forward.

-- See which indexes exist and how often they're used:
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'users';
-- idx_scan: how many times this index has been used (higher = good investment)
-- If idx_scan is 0, you're paying storage cost for an unused index. Drop it.

-- See index sizes:
SELECT pg_size_pretty(pg_indexes_size('users'));
-- Shows total size of ALL indexes on the users table.

-- Create a hash index
CREATE INDEX idx_hash_email ON users USING hash(email);

-- This helps:
SELECT * FROM users WHERE email = 'alice@example.com';
-- Hash lookup: O(1). Faster than B-tree for exact equality.

-- This does NOT help (hash can't do ranges):
SELECT * FROM users WHERE email LIKE 'a%';
-- Falls back to sequential scan. Hash index is ignored.

-- This does NOT help (hash can't do ordering):
SELECT * FROM users ORDER BY email;
-- Also falls back to sequential scan.

-- Rule of thumb:
--   Use hash for: UUID lookups, session token lookups, exact key matches
--   Use B-tree for: almost everything else
--   In practice, B-tree is so good that hash indexes are rarely needed.

-- Create a composite index: user_id first, then created_at
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Uses the index (queries on the LEFT-most column):
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5;
-- Index Scan using idx_orders_user_date

-- Uses the index (both columns, left-to-right):
EXPLAIN ANALYZE SELECT * FROM orders
  WHERE user_id = 5 AND created_at > '2024-01-01';
-- Index Scan using idx_orders_user_date
-- Index Cond: (user_id = 5 AND created_at > '2024-01-01')

-- Does NOT use the index (skipped the left column):
EXPLAIN ANALYZE SELECT * FROM orders
  WHERE created_at > '2024-01-01';
-- Seq Scan on orders (index is useless here!)
-- You'd need a separate index on created_at for this query.

-- Pro tip: put the most-filtered column FIRST in the composite index.
-- If 90% of your queries filter by user_id, it should be the left column.

-- See how much disk space your indexes consume:
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(quote_ident(tablename))) AS total_size,
    pg_size_pretty(pg_relation_size(quote_ident(tablename))) AS table_size,
    pg_size_pretty(pg_indexes_size(quote_ident(tablename))) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(quote_ident(tablename)) DESC;

-- On a real 10M-row table, you might see:
-- tablename | total_size | table_size | index_size
-- users     | 1.8 GB     | 1.2 GB     | 600 MB
-- 600 MB of indexes! That's RAM they're competing for.

-- Find UNUSED indexes (wasting space + slowing writes):
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
-- idx_scan = 0 means the index has NEVER been used since last stats reset.
-- If it's been weeks/months: drop it.

Section 5 showed you normalized tables — "store each fact once." But how far do you take this? And when should you break the rules and store data in more than one place? This section gives you the theory (normal forms), the practice (when to break them), and the tools to see what's happening under the hood (the query planner).

-- UNNORMALIZED: one row per order, comma-separated items
CREATE TABLE bad_orders (
    order_id INT, customer_name TEXT, customer_state TEXT,
    items TEXT, item_prices TEXT
);
INSERT INTO bad_orders VALUES
  (1, 'Alice', 'CA', 'Keyboard, Mouse', '149.99, 29.99'),
  (2, 'Alice', 'CA', 'Cable', '12.99');
-- Problem: WHERE items LIKE '%Keyboard%' — slow, fragile, wrong.

-- 1NF: Every column holds ONE value (atomic).
CREATE TABLE orders_1nf (
    order_id INT, customer_name TEXT, customer_state TEXT,
    item_name TEXT, item_price DECIMAL(10,2)
);
INSERT INTO orders_1nf VALUES
  (1, 'Alice', 'CA', 'Keyboard', 149.99),
  (1, 'Alice', 'CA', 'Mouse',    29.99),
  (2, 'Alice', 'CA', 'Cable',    12.99);
-- Each row has one item. But Alice's name/state repeated 3 times.

-- 2NF: Remove partial dependencies.
CREATE TABLE customers_2nf (
    customer_id SERIAL PRIMARY KEY, name TEXT NOT NULL, state CHAR(2) NOT NULL
);
CREATE TABLE order_items_2nf (
    order_id INT, customer_id INT REFERENCES customers_2nf(customer_id),
    item_name TEXT, item_price DECIMAL(10,2)
);
-- Alice's name stored ONCE. Much better.

-- 3NF: Remove transitive dependencies.
CREATE TABLE products_3nf (
    product_id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL, price DECIMAL(10,2) NOT NULL
);
CREATE TABLE order_items_3nf (
    order_id INT, customer_id INT REFERENCES customers_2nf(customer_id),
    product_id INT REFERENCES products_3nf(product_id),
    quantity INT DEFAULT 1, price_at_purchase DECIMAL(10,2)
);
-- Product price in ONE place. price_at_purchase is a snapshot.

-- Pre-compute per-user stats with a materialized view
CREATE MATERIALIZED VIEW user_stats AS
SELECT u.id AS user_id, u.name, COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS lifetime_value, MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'
GROUP BY u.id, u.name;

-- Dashboard queries the view (instant):
SELECT * FROM user_stats WHERE user_id = 42;
-- No JOIN. No aggregation. Just a simple row lookup.

-- Refresh periodically (e.g., every 5 minutes via cron):
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;
-- CONCURRENTLY means the view stays readable during refresh.

EXPLAIN ANALYZE
SELECT u.name, o.total FROM users u
JOIN orders o ON u.id = o.user_id WHERE u.state = 'CA';

-- Output (annotated):
-- Hash Join  (cost=1.04..2.09 rows=2 width=68)
--   Hash Cond: (o.user_id = u.id)
--   -> Seq Scan on orders o (cost=0..1.03 rows=3)
--   -> Hash  (cost=1.03..1.03 rows=2)
--        -> Seq Scan on users u (cost=0..1.03 rows=2)
--             Filter: (state = 'CA')
-- Planning Time: 0.258 ms
-- Execution Time: 0.087 ms
--
-- Reading bottom-up:
-- 1. Scan users, filter state='CA' → 2 rows
-- 2. Build hash table from those rows
-- 3. Scan orders, look up each in hash table
-- 4. Return matches. 0.087ms total.

-- Force PostgreSQL to NOT use sequential scans (for testing only!):
SET enable_seqscan = off;
-- Re-run EXPLAIN ANALYZE — it'll pick an index scan if one exists.
SET enable_seqscan = on;  -- RESET after testing

-- Find your slowest queries automatically:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

-- Update table statistics (helps the planner make better decisions):
ANALYZE users;

Theory is nice, but you want to know: do relational databases actually work at internet scale? Not toy projects — we're talking billions of page views, millions of merchants, hundreds of millions of daily active users. The answer is yes, and here's the proof.

The internet is full of database advice. Most of it is wrong, and some of it is dangerously wrong. These three myths have caused more production outages, unnecessary rewrites, and wasted engineering months than any actual technical limitation. Let's kill them with evidence.

SELECT u.name, a.street, a.city, s.name AS state_name,
       c.name AS country_name, o.total, p.name AS product
FROM users u
JOIN addresses a ON u.address_id = a.id
JOIN cities ci ON a.city_id = ci.id
JOIN states s ON ci.state_id = s.id
JOIN countries c ON s.country_id = c.id
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.id = 42;
-- 7 JOINs. For ONE user's order page.
-- At 10M users, this runs in 2-5 seconds. Unacceptable.

-- Store city/state/country as text on the address row.
-- "New York" is not going to rename itself.
SELECT u.name, a.street, a.city, a.state, a.country,
       o.total, p.name AS product
FROM users u
JOIN addresses a ON u.address_id = a.id
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.id = 42;
-- 4 JOINs, runs in 5ms. The page loads instantly.
-- The "impure" schema is the better engineering decision.

# Ruby on Rails — looks innocent:
posts = Post.all  # 1 query: SELECT * FROM posts

posts.each do |post|
  puts post.author.name  # N queries! One for EACH post!
end

# What the ORM actually generated:
# SELECT * FROM posts;                           -- 1 query
# SELECT * FROM authors WHERE id = 1;            -- query 2
# SELECT * FROM authors WHERE id = 2;            -- query 3
# ... (for every single post)
# SELECT * FROM authors WHERE id = 100;          -- query 101
#
# 100 posts = 101 database round-trips.
# Total: 200-500ms for a page that should take 5ms.

# Fix: eager loading
posts = Post.includes(:author).all

posts.each do |post|
  puts post.author.name  # No extra queries! Already loaded.
end

# What the ORM now generates:
# SELECT * FROM posts;
# SELECT * FROM authors WHERE id IN (1, 2, 3, ..., 100);
#
# 2 queries instead of 101. 5ms instead of 500ms.
# One word changed: .includes(:author)

Every one of these has taken down a production system. They're easy to make, easy to miss in code review, and absolutely brutal at 3 AM when your pager goes off. The good news: they're all fixable in minutes once you know what to look for.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Output:
-- Seq Scan on orders  (cost=0.00..18904.00 rows=52 width=68)
--   (actual time=0.021..127.483 rows=48 loops=1)
--   Filter: (user_id = 42)
--   Rows Removed by Filter: 999952
-- Planning Time: 0.085 ms
-- Execution Time: 127.551 ms
--
-- "Seq Scan" = sequential scan = reading EVERY row.
-- "Rows Removed by Filter: 999952" = it read ~1M rows to find 48.
-- That's like reading an entire phone book to find one person.

CREATE INDEX idx_orders_user_id ON orders(user_id);

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Output:
-- Index Scan using idx_orders_user_id on orders
--   (cost=0.42..8.61 rows=52 width=68)
--   (actual time=0.028..0.043 rows=48 loops=1)
--   Index Cond: (user_id = 42)
-- Planning Time: 0.112 ms
-- Execution Time: 0.059 ms
--
-- "Index Scan" = jumped straight to the right rows.
-- 127ms → 0.059ms. That's 2,157× faster.
-- One CREATE INDEX statement. That's all it took.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 42;

-- Output:
-- Index Scan using users_pkey on users
--   Buffers: shared hit=4
--   (actual time=0.018..0.020 rows=1 loops=1)
-- Planning Time: 0.071 ms
-- Execution Time: 0.035 ms
--
-- 4 buffer hits — reads 4 pages (32KB) from shared memory.
-- Fetches ALL 20 columns including that 50KB bio field.

EXPLAIN (ANALYZE, BUFFERS) SELECT name, email, created_at
FROM users WHERE id = 42;

-- Output:
-- Index Scan using users_pkey on users
--   Buffers: shared hit=2
--   (actual time=0.015..0.016 rows=1 loops=1)
-- Planning Time: 0.065 ms
-- Execution Time: 0.028 ms
--
-- 2 buffer hits vs 4 — half the I/O.
-- Multiply by 10,000 requests/sec and it matters.

-- How many connections can your database handle?
SHOW max_connections;  -- Default: 100 (way too low for production)

-- How many are currently in use?
SELECT count(*) FROM pg_stat_activity;

-- What are they doing?
SELECT pid, state, query, wait_event_type
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432           ; App connects here (not 5432)
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction      ; Recycle after each transaction
default_pool_size = 50       ; 50 real DB connections
max_client_conn = 2000       ; Accept up to 2000 app connections
reserve_pool_size = 5        ; Emergency overflow
reserve_pool_timeout = 3     ; Wait 3s before using overflow

-- Check dead tuple count and last vacuum time
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1)
         AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders'
ORDER BY n_dead_tup DESC;

-- If dead_pct > 20%, you have a problem.
-- If last_autovacuum is NULL, autovacuum has NEVER run on this table.

-- Manual vacuum (reclaims space within the table):
VACUUM ANALYZE orders;

-- Nuclear option — rewrites the entire table (locks it!):
VACUUM FULL orders;  -- Use only during maintenance windows

-- Make autovacuum more aggressive for high-churn tables
ALTER TABLE events SET (
  autovacuum_vacuum_threshold = 1000,       -- Default: 50
  autovacuum_vacuum_scale_factor = 0.01,    -- Default: 0.2 (20%)
  autovacuum_analyze_threshold = 500,
  autovacuum_analyze_scale_factor = 0.005
);

-- Translation: "vacuum this table when 1% of rows are dead
-- (instead of waiting for 20%)."

-- ❌ The lazy way — no validation at all
CREATE TABLE users_bad (
  name        TEXT,       -- Could be 10MB, no limit
  email       TEXT,       -- No format check
  country     TEXT,       -- "US", "us", "United States", "USA" — inconsistent
  phone       TEXT        -- Could store "banana" and nobody would know
);

-- ✅ The intentional way — the schema IS the documentation
CREATE TABLE users_good (
  name        VARCHAR(100),   -- Names rarely exceed 100 chars
  email       VARCHAR(255),   -- RFC 5321 max is 254 characters
  country     CHAR(2),        -- ISO 3166-1 alpha-2: "US", "GB", "IN"
  phone       VARCHAR(20),    -- E.164 max is 15 digits + prefix
  bio         TEXT            -- Actually variable-length: TEXT is right here
);

-- Rule of thumb:
-- CHAR(n)    → fixed-length codes (country, state, currency)
-- VARCHAR(n) → bounded strings (name, email, phone)
-- TEXT       → truly unbounded content (comments, articles, bios)

-- Step 1: Check the plan BEFORE deploying
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 20;

-- Step 2: In production, auto-log slow query plans
-- postgresql.conf:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '1s'   -- Log plans for 1s+ queries
-- auto_explain.log_analyze = true        -- Include actual timing
-- auto_explain.log_buffers = true        -- Include I/O stats

-- Step 3: Check pg_stat_statements for repeat offenders
SELECT query,
       calls,
       mean_exec_time::int AS avg_ms,
       total_exec_time::int AS total_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This is one of the most common system design interview questions, and it's a perfect test of how deeply you understand relational databases. The same schema can be answered at three very different levels of depth. Let's walk through all three.

CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  description TEXT,
  price       DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  stock       INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER NOT NULL REFERENCES users(id),
  status      VARCHAR(20) DEFAULT 'pending',
  total       DECIMAL(10, 2) NOT NULL,
  created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id),
  product_id  INTEGER NOT NULL REFERENCES products(id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  DECIMAL(10, 2) NOT NULL  -- Price at time of order
);

-- Indexes on every foreign key + common query patterns
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- Composite index for "user's recent orders" (most common dashboard query)
CREATE INDEX idx_orders_user_created
  ON orders(user_id, created_at DESC);

-- Partial index: only index active products (saves space)
CREATE INDEX idx_products_active
  ON products(name) WHERE stock > 0;

-- Add an addresses table (normalized from users)
CREATE TABLE addresses (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER NOT NULL REFERENCES users(id),
  type        VARCHAR(10) CHECK (type IN ('billing', 'shipping')),
  street      VARCHAR(255) NOT NULL,
  city        VARCHAR(100) NOT NULL,
  state       CHAR(2),
  zip         VARCHAR(10),
  country     CHAR(2) NOT NULL DEFAULT 'US'
);
CREATE INDEX idx_addresses_user ON addresses(user_id);

-- 1. ISOLATION LEVELS for payments (the money path)
-- Use SERIALIZABLE for payment transactions to prevent double-spend
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;
  INSERT INTO orders (...) VALUES (...);
  INSERT INTO payments (order_id, amount, status) VALUES (...);
COMMIT;
-- If two users buy the last item simultaneously, one gets
-- a serialization failure → retry. No overselling.

-- 2. READ REPLICAS for dashboards (no impact on write path)
-- Point analytics/dashboard queries to read replicas:
-- primary: handles writes (orders, payments)
-- replica1, replica2: handle reads (product catalog, search, dashboards)

-- 3. MATERIALIZED VIEWS for expensive dashboard aggregates
CREATE MATERIALIZED VIEW dashboard_stats AS
SELECT DATE_TRUNC('day', o.created_at) AS day,
       COUNT(DISTINCT o.id)            AS orders,
       SUM(o.total)                    AS revenue,
       COUNT(DISTINCT o.user_id)       AS unique_buyers
FROM orders o
WHERE o.status = 'completed'
GROUP BY 1;

-- Refresh every 5 minutes (not real-time, but dashboards don't need it)
REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_stats;

-- 4. SHARDING STRATEGY (when single-node isn't enough)
-- Shard key: user_id (keeps all of a user's data on one shard)
-- Tool: Citus extension or Vitess
-- Avoid sharding until you NEED it — vertical scaling + read
-- replicas + connection pooling handles more than most people think.

Reading about databases is one thing. Running commands and watching what happens is where the learning actually sticks. Start with Exercise 1 — you can do it right now with nothing but sqlite3.

PRAGMA foreign_keys = ON;

CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id),
  title      TEXT NOT NULL,
  body       TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
  id         INTEGER PRIMARY KEY,
  post_id    INTEGER NOT NULL REFERENCES posts(id),
  user_id    INTEGER NOT NULL REFERENCES users(id),
  body       TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users VALUES (2, 'Bob', 'bob@example.com');
INSERT INTO users VALUES (3, 'Charlie', 'charlie@example.com');

INSERT INTO posts (id, user_id, title, body)
VALUES (1, 1, 'SQL is not scary', 'Here is why...'),
       (2, 2, 'My first JOIN', 'I finally get it...'),
       (3, 1, 'Indexes changed my life', 'Let me explain...'),
       (4, 3, 'Why I switched from MongoDB', 'It started when...'),
       (5, 2, 'ACID in plain English', 'Imagine a bank...');

INSERT INTO comments (post_id, user_id, body)
VALUES (1,2,'Great post!'),(1,3,'Thanks for this'),
       (2,1,'Nice work'),(2,3,'Same here'),
       (3,2,'So true'),(3,3,'Game changer'),
       (3,1,'Updated my app today'),
       (4,1,'Bold move!'),(4,2,'How did it go?'),
       (5,3,'Best explanation I have seen');

-- The JOIN query:
SELECT p.title,
       u.name AS author,
       COUNT(c.id) AS comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id
ORDER BY comment_count DESC;

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100),
  price DECIMAL(10, 2)
);

INSERT INTO products (name, price)
SELECT 'Product ' || i, (random() * 1000)::numeric(10,2)
FROM generate_series(1, 100000) AS s(i);

-- BEFORE index:
EXPLAIN ANALYZE SELECT * FROM products
WHERE price BETWEEN 50.00 AND 51.00;
-- → Seq Scan, ~25ms, reads all 100K rows

-- Add the index:
CREATE INDEX idx_products_price ON products(price);

-- AFTER index:
EXPLAIN ANALYZE SELECT * FROM products
WHERE price BETWEEN 50.00 AND 51.00;
-- → Index Scan (or Bitmap Index Scan), ~0.3ms
-- The speedup should be 50-100×.

-- === TERMINAL A ===
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM accounts WHERE id = 1;  -- balance = 1000

-- (pause — switch to Terminal B)

-- After Terminal B commits:
SELECT * FROM accounts WHERE id = 1;  -- balance = 500!
-- Same transaction, different result → non-repeatable read.
COMMIT;

-- === TERMINAL B ===
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;  -- ← Terminal A's 2nd SELECT sees this change

-- === FIX: use REPEATABLE READ ===
-- Terminal A:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;  -- 1000
-- Terminal B commits the update...
SELECT * FROM accounts WHERE id = 1;  -- STILL 1000
-- REPEATABLE READ gives you a consistent snapshot.
COMMIT;

Pin this section. Everything you'll reach for during debugging, interviews, and production firefighting — in one place.

A — Atomicity     → BEGIN; ... COMMIT;
    (all or nothing — ROLLBACK undoes everything)

C — Consistency   → CHECK, NOT NULL, UNIQUE, FK
    (rules enforced BEFORE data is written)

I — Isolation     → SHOW transaction_isolation;
    (concurrent txns don't see each other's mess)

D — Durability    → WAL (Write-Ahead Log)
    (committed = survives crash. Period.)

B+ Tree  (default)
  = , < , > , BETWEEN, ORDER BY, LIKE 'abc%'
  CREATE INDEX idx ON t(col);

Hash
  = only (no range). Rarely used.
  CREATE INDEX idx ON t USING hash(col);

GIN  (Generalized Inverted)
  JSONB, arrays, full-text search
  CREATE INDEX idx ON t USING gin(col);

GiST (Generalized Search Tree)
  Geometry, ranges, nearest-neighbor
  CREATE INDEX idx ON t USING gist(col);

\dt          List all tables
\di          List all indexes
\d  table    Show table schema
\d+ table    Schema + sizes + descriptions
\x           Toggle expanded output
\timing      Show query execution time

EXPLAIN ANALYZE SELECT ...;
  → Shows actual execution plan + timing

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
  → Plan + I/O stats (buffer hits/reads)

1NF — Atomic values
  No arrays, no comma-separated lists.
  Every cell = one value.

2NF — No partial dependencies
  Every non-key column depends on the
  WHOLE primary key (not part of it).

3NF — No transitive dependencies
  Non-key columns depend ONLY on the
  primary key, not on other non-key cols.

Rule of thumb: "Every non-key column
provides a fact about the key, the whole
key, and nothing but the key."

-- Dead tuples (bloat):
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables;

-- Slow queries (needs pg_stat_statements):
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;

-- Auto-log slow plans:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '1s'

-- Cache hit ratio (should be > 99%):
SELECT sum(heap_blks_hit) /
       sum(heap_blks_hit + heap_blks_read)
FROM pg_statio_user_tables;

SHOW max_connections;     -- Default: 100

Per-connection RAM: ~10MB (PostgreSQL)
100 conns  = ~1GB RAM overhead
1000 conns = ~10GB RAM overhead

Fix: PgBouncer (connection pooler)
  pool_mode = transaction
  default_pool_size = 50
  max_client_conn = 2000

Result: 2000 app connections routed
through 50 real DB connections.
RAM saved: ~19.5GB.

Relational databases don't live in isolation. Every topic below builds on what you've just learned — and several of them were mentioned throughout this page. Pick whichever sounds most interesting and keep going.