Course · 06

How does a database keep data correct and still find it fast?

Transactions, isolation, indexes and storage engines, then the SQL vs NoSQL choice. Facts are checked against the PostgreSQL 18 docs (current on 2026-10-25), MySQL 8.4, Cassandra, DynamoDB and MongoDB docs.

—Words you need

Each jargon word once, in plain words glossary

01The relational model

Tables, rows, keys, and joins core

A relation is a table. Each row is one fact. Each column has one type. A primary key names a row uniquely. A foreign key points at another table's primary key. A join combines tables on those keys at query time.

CREATE TABLE users  (id bigint PRIMARY KEY, email text UNIQUE NOT NULL);
CREATE TABLE orders (id bigint PRIMARY KEY,
                     user_id bigint REFERENCES users(id),
                     total numeric(10,2), created_at timestamptz);
SELECT u.email, o.total FROM orders o JOIN users u ON u.id = o.user_id;

Relational is the default choice when data has many relationships and you need ad-hoc queries.

Source: Codd 1970, CACM · PostgreSQL tutorial: concepts

02ACID, one letter at a time

What a transaction promises interview favourite

When an interviewer says "ACID", the real follow-up is "which isolation level?". Be ready for it.

Source: PostgreSQL tutorial: transactions

03Isolation levels and the anomalies they allow

The four standard levels, and what goes wrong at each interview favourite

Read Committed is the common default, and it allows lost updates. Fix with atomic UPDATE ... SET x = x - 1, SELECT FOR UPDATE, a version column, or Serializable + retry.

Source: PostgreSQL Transaction Isolation · MySQL 8.4 InnoDB isolation levels

04MVCC basics

Keep old versions so readers and writers do not block each other deep dive

MVCC means multi-version concurrency control. An update does not overwrite a row in place. It writes a new version and marks the old one as ended. Each transaction reads the versions that were committed when its snapshot was taken.

MVCC is why a long report query does not freeze your checkout page, and why long-open transactions bloat tables.

Source: PostgreSQL MVCC intro · MySQL InnoDB isolation

05Locking: pessimistic vs optimistic

Lock first, or check at the end core

BEGIN;
SELECT * FROM seats WHERE id = 7 FOR UPDATE;          -- others wait here
UPDATE seats SET booked_by = 42 WHERE id = 7;
COMMIT;
-- job queue: many workers, no waiting on each other
SELECT id FROM jobs WHERE state='new' LIMIT 10 FOR UPDATE SKIP LOCKED;

Default to an atomic UPDATE; use a version column for user edits; use FOR UPDATE only for short, hot critical sections.

Source: PostgreSQL explicit locking · PostgreSQL SELECT locking clause · Fowler, Optimistic Offline Lock

06Write-ahead log (WAL)

How commits are durable and still fast deep dive

Changing a table touches pages scattered all over the disk. Writing them all at each commit would be slow. So the database first appends a small change record to the WAL and flushes only that. The changed pages stay in memory and reach the data files later, at a checkpoint.

"Durable" means "in the log on disk", not "in the table file". Replication and recovery both read the log.

Source: PostgreSQL WAL intro

07Indexes: B-tree vs hash vs LSM-tree

Three shapes, three trade-offs interview favourite

A B-tree keeps keys sorted in pages. Each page points to child pages. You walk from the root to a leaf. Because keys are sorted, ranges like BETWEEN and ORDER BY are cheap.

An LSM-tree (log-structured merge-tree) never edits files in place. Writes go to a log and a sorted in-memory table. Full tables are flushed as sorted, immutable files called SSTables. Compaction merges them in the background. Reads may check several files, so engines use Bloom filters to skip files.

Read-heavy with ranges: B-tree. Write-heavy firehose (logs, metrics, events): LSM. Exact lookup only: hash (or just a B-tree).

Source: PostgreSQL index types · PG page layout · Cassandra storage engine · RocksDB compaction · O'Neil et al. 1996

08Composite and covering indexes, and when indexes hurt

The leftmost-prefix rule interview favourite

CREATE INDEX idx_orders ON orders (user_id, created_at);
-- uses the index well:  WHERE user_id = 42
-- uses the index well:  WHERE user_id = 42 AND created_at > now() - interval '7 days'
-- poor fit:             WHERE created_at > now() - interval '7 days'   -- leading column missing
CREATE INDEX idx_cover ON orders (user_id) INCLUDE (total);  -- covering: SELECT total WHERE user_id=?

Design indexes from your top queries, not from your columns. Drop the ones nobody uses.

Source: PG multicolumn indexes · PG index-only scans · PG indexes intro

09Query planning and EXPLAIN

Ask the database how it will run your query core

EXPLAIN SELECT * FROM tenk1;
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 10;   -- really runs it

In an interview, "I'd check EXPLAIN ANALYZE for a Seq Scan on a big table" is the right first step for a slow query.

Source: PostgreSQL Using EXPLAIN

10Normalization vs denormalization

Store each fact once, or copy it for speed core

Normalize the source of truth. Denormalize read models (caches, search indexes, materialized views) that you can rebuild.

Source: Codd 1970 · Cassandra data modeling · MongoDB data modeling

11NoSQL families

Four shapes of non-relational database core

Name the family by the access pattern: by key → KV; whole object → document; huge time-ordered writes → wide-column; "friends of friends" → graph.

Source: DynamoDB core components · DynamoDB quotas · MongoDB limits · Cassandra data modeling · Neo4j graph database

12SQL vs NoSQL, and BASE

A decision, not a religion decision

Start from the access patterns and consistency needs, then pick. "NoSQL scales, SQL doesn't" is a weak answer.

Source: Pritchett, BASE: An Acid Alternative (ACM Queue) · MongoDB data modeling

13Connection pooling

Reuse a few open connections core

Opening a DB connection costs a TCP handshake, TLS, auth, and (in PostgreSQL) a server process. A pool keeps some connections open and lends them out. It also caps how many connections hit the database.

More connections is not more throughput. Size the pool to the database, and queue in the app.

Source: PG connection settings · PgBouncer features

14The N+1 query problem

One query for the list, one more per item interview favourite

# N+1: 1 query for 10 books + 10 queries for authors = 11 queries
books = Book.limit(10)
books.each { |b| puts b.author.last_name }
# Eager loading: 2 queries total
books = Book.includes(:author).limit(10)
# SELECT authors.* FROM authors WHERE authors.id IN (1,2,...,10)

Batch with JOIN or IN, or use a DataLoader in GraphQL. Count queries per request in tests.

Source: Rails guide: Active Record Query Interface (N + 1)

15Pagination at the database level

OFFSET is easy; keyset is fast core

-- OFFSET: page 1000 still reads and throws away 20,000 rows
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20000;
-- Keyset (cursor): start after the last row you saw
SELECT * FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC LIMIT 20;

Infinite scroll and APIs: keyset with an opaque cursor. Admin tables with page numbers: OFFSET is fine for small data.

Source: PostgreSQL LIMIT and OFFSET

—When to use what

Situation → pick → why decision table

—Common mistakes

What weak answers say traps

—Interview questions

21 questions, easy to design-level, with model answers practice

—Cheat sheet

Facts to memorise memorise

—Sources

All checked 2026-10-25 sources

—Keep going

Previous and next pages course