Engineering Atlas › Foundations

Databases 101: Tables, Indexes and Transactions

15 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Tables, rows and keys

Picture a shop with two tables:

CREATE TABLE customers (
  id    BIGINT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city  TEXT
);
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  created_at  TIMESTAMP NOT NULL
);

Each order points to its customer through customer_id, a foreign key. The database enforces the rules you declare: no two customers share an email, no order points to a customer who does not exist, no total is negative. Rules in the schema hold no matter which service, script or engineer writes the data, which is why they are worth more than the same check in application code.

Asking questions with SQL

SQL is declarative. You say what you want:

SELECT c.email, SUM(o.total_cents) AS spent
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
GROUP BY c.email ORDER BY spent DESC LIMIT 10;

A component called the query planner decides how: which table to read first, whether to use an index, and which join algorithm to run. EXPLAIN shows you the plan it chose. Learning to read it is the single most useful database skill for an application engineer.

Indexes

A table's rows sit in no useful order. To find the orders of customer 42, the database has to read every row unless it has an index on customer_id. An index works like the index at the back of a book: a sorted list of values, each pointing to where the full row lives. Because it is sorted, the database can jump to the right place in a few steps, and it can also answer range queries (created_at between two dates) and ORDER BY without sorting.

The price is paid on writes. Every insert, and every update to an indexed column, must also update each index, and each index takes disk space and memory. A table with eight indexes does nine writes for one insert.

Transactions and ACID

Moving 100 dollars from Ana to Budi is two updates. If the server crashes between them, money vanishes. A transaction wraps them:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Concurrent writers

Two requests read a stock count of 1, both subtract one, both write 0, and two customers buy the last item. This is a lost update, and the default isolation level in Postgres, Read Committed, does not prevent it when the read and the write are separate statements. A single UPDATE is safe there, because a blocked updater re-checks its WHERE clause against the newly committed row (Postgres docs). The fixes: do the check and the change in one statement (UPDATE stock SET qty = qty - 1 WHERE id = 7 AND qty > 0, then check that one row changed), lock the row first with SELECT ... FOR UPDATE, or run at Serializable isolation and retry when the database reports a conflict.

The same shapes elsewhere

An index is "finding things fast", the same idea as a search engine's inverted index or a geospatial index for nearby drivers: pay at write time to build a structure that makes reads cheap. A transaction is "multi-step consistency": order, stock and payment must agree. When those steps span several services instead of one database, you no longer get BEGIN and COMMIT for free and need patterns such as sagas and the outbox, which later courses cover.

Down the stack

Layer 1The contract

When COMMIT returns successfully, the transaction's changes are durable and visible to transactions that start afterwards. If the client loses its connection before COMMIT returns, the outcome is unknown, just as with an HTTP timeout, so it must check rather than blindly retry. The isolation level defines what concurrent transactions may observe: Read Committed (the Postgres default) shows each statement only committed data but lets a second read in the same transaction see newer commits; Repeatable Read keeps one snapshot for the whole transaction and is the MySQL InnoDB default; Serializable promises the result equals some one-at-a-time order, aborting transactions when it cannot. Postgres reports those aborts, and Repeatable Read update conflicts too, as SQLSTATE 40001, and its docs say to retry the whole transaction (Postgres docs).

Layer 2The mechanism

Postgres stores each table as a heap file of 8 KB pages, and each row version carries a 23-byte header with t_xmin and t_xmax: the IDs of the transactions that created and deleted it (Postgres docs). An update writes a new row version instead of overwriting, so readers see the version that matches their snapshot without taking locks (MVCC, multi-version concurrency control). VACUUM later reclaims versions nobody can see. Indexes are B-trees: pages of sorted keys, where inner pages point to child pages and leaf pages point to row locations (a page number and item slot, the ctid). Before any data page changes, the database appends a record describing the change to the write-ahead log in pg_wal, in segment files that default to 16 MB. MySQL's InnoDB uses 16 KB pages, keeps rows inside the primary key B-tree itself, and stores old versions in an undo log that ROLLBACK replays backwards.

Layer 3The machine

A B-tree page of 8 KB holds a few hundred keys, so a tree only 3 levels deep covers tens of millions of rows and 4 levels covers billions. The top levels are read so often that they stay in the database's buffer pool in RAM; a lookup typically costs one or two real disk reads, each about 100 µs on an NVMe SSD, or nothing if the pages are cached. A full scan of a 10 GB table reads at perhaps 2 GB/s from SSD: about 5 seconds. On commit, the WAL is written sequentially and fsync forces it to stable storage, typically 0.05 to 2 ms depending on the drive. Databases batch the commits of many concurrent transactions into one fsync (group commit; Postgres exposes commit_delay and commit_siblings for it, see the WAL docs), which is how a single server sustains tens of thousands of commits per second. The ordering matters as much as the flush: SQLite's atomic commit write-up shows the journal must be durable before the database file changes, because the OS may reorder writes.

Why it's built this way

  1. Disks and SSDs are slow at random small writes and fast at sequential ones, so commits append to a log instead of updating table pages in place.
  2. A log alone is slow to search, so data also lives in pages with B-tree indexes, updated lazily and rebuilt from the log after a crash.
  3. Disk reads come in pages, so B-trees are wide and shallow, packing hundreds of keys into each page to minimise reads per lookup.
  4. Locking every row a reader touches would make readers block writers, so engines keep multiple versions and give each transaction a snapshot.
  5. Old versions pile up, so the engine needs background cleanup (VACUUM, undo purge), and long-running transactions that pin old snapshots make that cleanup fall behind.

Diagram

flowchart TD
  C["Client: SQL"] --> P["Parser and planner"]
  P -->|"index chosen"| B["B-tree index"]
  P -->|"no useful index"| S["Full table scan"]
  B --> H["Table pages (heap)"]
  S --> H
  H --> BP["Buffer pool in RAM"]
  BP -->|miss| D["Data files on SSD"]
  C -->|"COMMIT"| W["Append to WAL"]
  W --> F["fsync, then reply OK"]
  F -.->|"later checkpoint"| D

Worked example

An orders table has 50 million rows of about 200 bytes each, so about 10 GB. The customer page runs SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20. With no index on customer_id, EXPLAIN shows a sequential scan: the database reads all 10 GB, about 5 seconds from SSD, and the query blocks a connection the whole time. At 50 requests per second this would need hundreds of seconds of disk reading per second, so the site falls over.

Add CREATE INDEX ON orders (customer_id, created_at). The B-tree has 4 levels. The database walks from the root to the first leaf entry for customer 42 (the upper levels are cached, so maybe one disk read), reads the 20 newest entries already in created_at order, and fetches 20 rows from the heap. That is about 20 random page reads, under 3 ms even with a cold cache, and no sort. The query got about 2,000 times faster.

The cost: the index is about 1.5 GB, and each of the 300 inserts per second now also writes to it. That is fine here. Ten more indexes "just in case" would multiply write work and memory pressure, so index for the queries you actually run.

Now the payment path. Checkout inserts an order, decrements stock and records a payment. Inside one transaction, a crash after the stock update rolls all three back. Outside one, the shop can sell stock it never took payment for, and someone has to write a reconciliation script to find those rows.

In the wild

Trade-offs

ChoiceStrengthsWeaknessesChoose when
No index on a columnFast writes, no extra spaceEvery lookup scans the tableSmall tables, columns never filtered on
B-tree indexFast equality, range and sorted readsSlower writes, uses memory and diskColumns in frequent WHERE, JOIN, ORDER BY
Composite index (a, b)One index serves filter plus sortUseless for queries on b aloneQueries that filter on a and sort by b
Read CommittedFew conflicts, good throughputLost updates possible without careDefault for most web workloads
SerializableBehaves like one transaction at a timeAborts under conflict, needs retry logicMoney, inventory, invariants across rows
Relational databaseSchema, constraints, joins, transactionsHarder to scale writes beyond one nodeDefault choice for business data
Key-value storeSimple, very fast single-key accessNo joins, weak multi-key transactionsCaches, sessions, very high write rates

Pitfalls in production

Leadership lens

Check yourself

  1. Why does adding an index make reads faster but writes slower?

    Show answer

    The index is a sorted structure the database can search in a few page reads instead of scanning every row, but every insert or change to the indexed column must also update that structure.

  2. Two requests both run "read stock, subtract one, write stock" at Read Committed. What can go wrong and how do you fix it?

    Show answer

    Both can read the same value and one write overwrites the other, selling stock twice. Do the check and subtraction in one conditional UPDATE, lock the row with SELECT ... FOR UPDATE, or use Serializable and retry on conflict.

  3. How can a database promise durability on commit without writing every changed table page to disk?

    Show answer

    It appends a description of the changes to the write-ahead log and fsyncs that sequential log before replying; after a crash it replays the log to rebuild the pages.

  4. Why can a B-tree over a billion rows answer a lookup in about four page reads?

    Show answer

    Each page holds hundreds of keys, so each level multiplies coverage by hundreds; four levels of a few hundred keys each reach billions of entries.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →