Step 2 of 13 · 1 min

How a table actually sits on disk

It's natural to picture a database table as a neat spreadsheet. On disk, it's closer to a stack of numbered pages.

Postgres stores each table as a series of pages, 8 KB each. A page holds as many rows as fit, typically a few dozen for a table like posts. New rows go wherever there's space, which mostly means at the end. Nothing is kept in any particular order: not by author, not by date.

So when you ask for "posts where author_id = 4821," the database has no way of knowing where those posts are. They could be on any page. The only way to be sure it found all of them is to read every page in the table and check every row.

That's called a sequential scan, and you can see it happening with EXPLAIN ANALYZE, which runs a query and reports what the database did:

EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 4821
ORDER BY created_at DESC LIMIT 20;

Limit  (actual time=198.4..198.4 rows=20)
  ->  Sort  (Sort Key: created_at DESC)
        ->  Seq Scan on posts  (actual time=0.03..197.9 rows=184)
              Filter: (author_id = 4821)
              Rows Removed by Filter: 1999816

Read it from the bottom up. The database scanned the whole posts table, looked at two million rows, threw away 1,999,816 of them, sorted the 184 that matched, and returned the top 20. To find 20 rows, it examined two million.

In March, that meant checking 50,000 rows, which was fast enough that nobody noticed. The cost grows in direct proportion to the table, so 40 times more posts meant 40 times more work. And since the work is mostly the processor checking rows, a few dozen of these queries at once kept every core busy, which is why the whole app slowed down in the evening, not just the profile page.

This is a pattern worth recognizing early: a query that gets slower as the table grows, even though it returns the same number of rows, is almost always scanning.

Step 3 of 13 · 2 min

The index

The fix was this:

CREATE INDEX posts_author_created ON posts (author_id, created_at DESC);

And the same query afterwards:

Limit  (actual time=0.05..0.11 rows=20)
  ->  Index Scan using posts_author_created on posts  (actual time=0.05..0.10 rows=20)
        Index Cond: (author_id = 4821)

From about 200ms to about 0.1ms. No sort step, either. To understand why, you need to know what that index actually is.

A sorted copy, organized as a tree

An index is a separate structure that keeps a sorted copy of some columns, each with a pointer to where the full row lives. This one is sorted by author_id, and within each author by created_at, newest first.

Sorted data can be searched without reading all of it. You already do this with a printed dictionary: you don't read every page to find "latency," you open somewhere in the middle and narrow down.

Postgres (like most databases) organizes indexes as a B-tree. Think of it as a dictionary with an extremely good table of contents:

• The top page (the root) says: "authors 1–10,000 are under this page, 10,001–20,000 under that one," and so on, for a few hundred ranges.
• Each page it points to splits its range again into a few hundred smaller ranges.
• At the bottom, the leaf pages hold the actual sorted entries, each pointing to a row in the table.

The important property is how wide each page is. Because one 8 KB page holds a few hundred entries, every level multiplies the reach by a few hundred:

1 level:   ~300 entries
2 levels:  ~300 × 300       = 90,000
3 levels:  ~300 × 300 × 300 = 27 million
4 levels:                     about 8 billion

So to find author 4821's newest post among two million posts, the database reads about three or four pages, not the tens of thousands that make up the table. And the top levels of the tree are used by every lookup, so they almost always stay in memory. In practice, most index lookups touch the disk once or not at all. That's the lesson 1.2 numbers at work: a few memory reads instead of hundreds of thousands of page reads.

Because the leaves are sorted by author_id and then by created_at DESC, all of author 4821's posts sit next to each other, already newest first. The database reads the first 20 entries in order and stops. That's why the sort step disappeared.

Column order matters

The index is on (author_id, created_at), in that order. It's sorted by author first and by time only within each author, like a phone book sorted by last name and then first name.

That means it helps these queries:

WHERE author_id = 4821                                  -- yes
WHERE author_id = 4821 ORDER BY created_at DESC         -- yes, and no sort needed
WHERE author_id = 4821 AND created_at > '2026-06-01'    -- yes

But not this one:

WHERE created_at > '2026-06-01'                         -- no

A phone book sorted by last name can't help you find everyone named "Priya," because the Priyas are scattered across every last name. An index can only be searched starting from its first column. Getting column order right is most of the skill in designing indexes.

Step 4 of 13 · 1 min

Indexes aren't free

After the profile page fix, there was a period where every slow query got a new index. By August, the posts table had nine of them, and the team noticed that creating a post had become slower.

That's the trade-off. Every index is its own sorted structure, and every insert, update, and delete has to update every index on the table. Inserting one post now meant writing to the table and to nine B-trees, each needing to find the right spot in its sort order.

Indexes also take space. Nine indexes on posts added up to more storage than the table itself.

A few rules the team ended up following:

• Index for the queries you actually run, not every column that might be searched someday. Postgres can report which indexes are never used (pg_stat_user_indexes), and unused ones should go.
• One well-ordered composite index often replaces several single-column ones. (author_id, created_at) covers queries on author_id alone, so a separate index on just author_id was pure overhead.
• Indexes don't help much when a query matches most of the table. An index on is_deleted is nearly useless if 99% of posts aren't deleted: looking up "not deleted" still returns almost everything, and the database will sensibly choose to scan. Indexes pay off when they narrow things down a lot.

This read-versus-write trade-off shows up throughout the course. Almost every technique that makes reads faster (indexes, caches, copies of data) makes writes slower or more complicated.

Step 5 of 13 · 3 min

Transactions: when two things must happen together

Following someone on Chirp involves two writes: add a row to follows, and increase the followed account's follower_count. One day the server restarted in the middle of a deploy, and a handful of users ended up with follower counts that didn't match their actual followers.

What happened was that some requests had finished the first write but not the second when the server stopped. The database had no way to know those two writes belonged together.

That's what transactions are for:

BEGIN;
INSERT INTO follows (follower_id, followee_id) VALUES (12, 4821);
UPDATE users SET follower_count = follower_count + 1 WHERE id = 4821;
COMMIT;

Everything between BEGIN and COMMIT happens completely or not at all. If the server dies halfway, the database undoes the partial work when it restarts.

The guarantees that make this work are usually summed up as ACID:

• Atomicity: all of the transaction happens, or none of it.
• Consistency: the database's rules (unique keys, foreign keys, checks) hold before and after.
• Isolation: transactions running at the same time don't see each other's half-finished work.
• Durability: once COMMIT returns, the data survives a crash.

Two of these are worth looking at more closely, because they explain things you'll run into constantly.

Isolation, and the lost likes

A few weeks after transactions went in, a popular post's like count was obviously too low. It showed 940 likes, but the likes table had 1,012 rows for it.

The code that handled a like looked like this:

count = db.query("SELECT like_count FROM posts WHERE id = %s", post_id)
db.execute("UPDATE posts SET like_count = %s WHERE id = %s", count + 1, post_id)

Read the count, add one, write it back. When two people liked the post at nearly the same time, both requests read 939, both added one, and both wrote 940. One like vanished. This is called a lost update, and wrapping it in a transaction doesn't fix it.

That surprises a lot of people. Postgres's default isolation level is Read Committed, which guarantees you never see another transaction's uncommitted data, but doesn't stop two transactions from reading the same value and both writing back a result based on it. Stronger levels exist that would catch this, at the cost of more transactions failing and needing to be retried.

The simplest fix is to let the database do the arithmetic in one step:

UPDATE posts SET like_count = like_count + 1 WHERE id = $1;

The database locks the row, adds one to whatever the current value is, and releases it. Two of these at the same time can't lose an update, because the second waits for the first.

The broader lesson: "read a value in your code, change it, write it back" is unsafe when two requests can do it at the same time. Either let the database do the change in one statement, lock the row first (SELECT ... FOR UPDATE), or use a stronger isolation level. This problem comes back at a much larger scale in lesson 4.5, when one post gets thousands of likes per second.

Durability, and why commits cost time

When COMMIT returns, the data has to survive even if the power fails a millisecond later. But updating the actual table and index pages on disk is slow: they're scattered all over the disk, which is the random-access pattern lesson 1.2 warned about.

So databases cheat, cleverly. Before changing any pages, they write a description of the change to the end of a file called the write-ahead log (WAL). Appending to the end of a file is sequential, which is fast. The database makes sure the log entry is physically on disk (a call named fsync) before telling you the commit succeeded. The real table and index pages get updated in memory and are written to disk later, in the background, in bigger batches.

If the server crashes, Postgres reads the log on restart and redoes any committed changes that hadn't made it into the table pages yet.

Two practical consequences:

• Every commit waits for a disk write. That's usually well under a millisecond on a good SSD, but it's why committing each row separately in a big import is far slower than committing a thousand rows at a time.
• The log is the source of truth. Later lessons use the same log for other things: copying data to replica databases (lesson 2.5) and feeding changes to other systems (lesson 3.4).

Step 6 of 13 · 1 min

Connections aren't free either

One more thing the founders learned the hard way in June: when the database slowed down, they added more application servers to handle the traffic, and things got worse.

Each Postgres connection is a separate process on the database server, using several megabytes of memory. Each new app server opened its own pool of connections, and soon the database was running hundreds of connections, most of them idle, using memory the database needed for caching data. Since less data now fit in memory, more queries went to disk, which made them slower, which kept connections busy longer.

Little's Law from lesson 1.2 tells you how many connections you actually need: requests per second × time each one holds a connection. For Chirp, that was a few dozen. The usual solution is a connection pooler like PgBouncer, which sits in front of the database and lets hundreds of app connections share a much smaller number of real database connections.

Step 7 of 13 · 1 min

From the real world: the counter that runs out

PostgreSQL gives every transaction that writes something an ID, a 32-bit number. Each row remembers the ID of the transaction that created it, and that's how the database decides who can see what (it's part of how two transactions can run at the same time without seeing each other's half-finished work).

32 bits is about 4 billion numbers, and a busy database can use that many in weeks. So PostgreSQL compares IDs on a circle: about 2 billion IDs count as "in the past," and about 2 billion as "in the future." That works as long as no row is older than about 2 billion transactions. To guarantee it, a background process called vacuum periodically freezes old rows: it marks them as visible to everyone, so their transaction ID no longer matters.

If vacuum falls too far behind, the oldest rows get close to looking like they're in the future, which would make them vanish from queries. PostgreSQL won't let that happen. Close to the limit, it refuses all new writes and tells you to vacuum.

On 20 July 2015, this took down Sentry, the error-tracking service, for most of a working day. Sentry is extremely write-heavy, and its biggest tables were so large that vacuum couldn't keep up. The age crept toward the limit without anyone watching it. When PostgreSQL stopped accepting writes, the only fix was a vacuum over those enormous tables, which takes hours, and you can't hurry it. Mailchimp's Mandrill service had a similar outage in 2019, for the same reason.

What it teaches:

• "Background maintenance" can be on the critical path. Vacuum isn't optional housekeeping; it's paying back debt that every write creates.
• Monitor the age, not just the symptoms. age(datfrozenxid) per database (and per table) tells you how close you are, weeks ahead. Alert at a fraction of the limit.
• Huge, hot tables make every maintenance job slow. Partitioning big tables (lesson 5.1) helps vacuum as much as it helps queries.

Step 8 of 13 · 2 min

Going deeper: what "durable" actually means

This lesson said a database writes the WAL, then calls fsync to force it onto the disk, and only then says "committed." That's the right model, but there's a layer in between that once fooled PostgreSQL itself.

When a program writes to a file, the data usually goes into the operating system's page cache in memory, not to the disk. The OS writes it out later. fsync asks the OS to write out everything pending for that file and to confirm when it's done. Databases rely on two promises: fsync only says "success" when the data is really on disk, and if it fails, the data is still pending and you can try again.

In 2018, a PostgreSQL contributor found that the second promise didn't hold on Linux. When a background write to disk failed, Linux (on ext4, XFS and others) would report the error, but it also marked the failed pages as clean, as if they'd been written. Nothing would try to write them again. PostgreSQL's habit, when an fsync failed during a checkpoint, was to log it and retry at the next checkpoint. The retry would succeed, because there was nothing left to write. PostgreSQL would believe the checkpoint was complete and recycle the WAL that could have repaired it. The changes were simply gone. The episode became known as "fsyncgate."

The fix was blunt and correct: PostgreSQL now crashes (PANICs) if fsync fails on a data file. When it restarts, it replays the WAL from the last checkpoint that really completed, and the lost pages are written again. Crashing turns silent data loss into a short outage. The change shipped in late 2018 and was backported to all supported versions in the February 2019 releases. Other databases checked their own fsync handling as a result.

The general lessons:

• Durability is a chain: your code, the database, the OS page cache, the filesystem, the disk's own cache. Each link has its own failure rules.
• When you can't tell what state you're in, stop and rebuild from something you trust. Here, the WAL.
• Error paths are the least-tested code in any system. They rarely run, so wrong assumptions in them survive for decades.

Step 9 of 13 · 1 min

What Chirp changed

• Added the (author_id, created_at DESC) index, which took the profile page from seconds to well under a millisecond.
• Dropped five of the nine indexes on posts after checking which ones were actually used.
• Turned on Postgres's slow query log, so any query over 100ms gets recorded with its plan.
• Wrapped multi-step writes like following in transactions.
• Changed every counter update to SET count = count + 1.
• Put PgBouncer in front of the database.

The database itself didn't change. Two million posts is still small for Postgres, and so is twenty million, as long as the queries can use good indexes. Most "the database can't keep up" problems at this size are really "the database is doing far more work than it needs to."

Step 10 of 13 · 1 min

In the simulation

Stage 0 asks you to choose Chirp's main database. Everything in this lesson is about relational databases like Postgres, because that's the most common choice and it's the best place to learn how databases work. Part 3 compares it with document and wide-column databases, which make different trade-offs. You'll make the Stage 0 choice before reaching Part 3, on purpose: real teams make this decision early too, often before they know everything they'll need. Stage 2 shows you how it played out.

Open the simulation →

Step 11 of 13 · 1 min

If you're here for an interview

Interviewers rarely ask how a B-tree works directly. But they will ask "how would you look up X quickly?" or "what happens when this table gets big?", and the strong answer names the index, including column order. "An index on (author_id, created_at) so we can fetch a user's recent posts without sorting" is far more convincing than "we'll add indexes."

Also worth having ready:

• Indexes speed up reads and slow down writes. Saying so, unprompted, shows you understand the trade-off.
• Counters and balances updated by concurrent requests need atomic updates, locking, or careful isolation. "Two people like the same post at the same moment" is a classic follow-up.
• The write-ahead log is how databases are both durable and fast, and it's the foundation for replication.

Step 12 of 13 · 2 min

Try these yourself

Step 13 of 13 · 1 min

Further reading

• Use The Index, Luke! by Markus Winand. Free online, and the clearest explanation of how indexes really work, with examples for several databases.
• The PostgreSQL documentation, especially the chapters "Indexes," "Using EXPLAIN," "Transaction Isolation," and "Write-Ahead Logging." They're unusually readable.
• Martin Kleppmann, Designing Data-Intensive Applications, Chapter 3 (storage and indexes) and Chapter 7 (transactions).
• Sentry, "Transaction ID Wraparound in Postgres," 2015, and Mailchimp, "What we learned from the recent Mandrill outage," 2019.
• PostgreSQL documentation, "Preventing Transaction ID Wraparound Failures" (in the Routine Vacuuming chapter).
• PostgreSQL wiki, "Fsync Errors," and LWN, "PostgreSQL's fsync() surprise" (2018), for the full story of the fsync problem and the fix.