Eight Kilobytes

Chase Pierce
First edition · PostgreSQL 18 · 2026-10-05
Publisher: Flagon

Start reading · Print the whole book

Almost everything about PostgreSQL performance comes down to one number: 8,192. That’s the size of a page, the unit Postgres reads, writes, and caches. A fast query touches few pages. A slow one touches many, or waits on someone else who is. Once you can see the pages, most of the mystery goes away.

You’ve probably met the symptoms already. The dashboard that was instant at launch and crawls now. The migration that “does nothing” and took the site down anyway. The table that keeps growing faster than the data in it. And the standing offer to fix all of it with a bigger instance. This book is the alternative to that offer.

Count the pages, then earn the bigger box

The book starts at the bytes on disk and works up: how rows are stored and changed, how indexes and the planner find them, how to change a schema without an outage, and how to run Postgres in production. Along the way it builds two habits. Before you add an index, raise a setting, or blame the ORM, ask how many pages the work touches. And before you buy a bigger database, or a second one, use up the cheap fixes and prove in numbers that you’ve outgrown them.

That includes knowing where Postgres stops. On the same two million events, two column stores kept the data in under a fifth of the bytes and answered about ten times faster than a tuned Postgres. The book shows how far one Postgres goes, and the signal that tells you it’s time for something else.

It’s written for application developers who use Postgres every day, often through an ORM. You need basic SQL, not internals.

Every claim that matters comes with a check

Every query ran against PostgreSQL 18, on a sample schema you can load in a minute, and every claim that matters comes with a lab check you can rerun on your own machine. Each chapter is a short essay you can read on its own; deep dives hold the machinery and the full measurements for when you want them.

Free, and written in the open

Read it here, chapter by chapter, or download the whole thing as a PDF. It’s versioned in git like everything else we publish, so when Postgres changes, the book does too. If something’s wrong or unclear, that’s a bug: open an issue or send a fix.

Contents

Figures

1. Figure 2-1 — One server, many databases, a process per connection. Each connection gets its own backend process, which works in one database; inside it, schemas hold the tables. Beside the backends, the server runs its own processes, and roles and settings belong to the whole server.
2. Figure 3-1 — A page lives in one of three places. A hit in shared_buffers is a memory lookup; a miss, which EXPLAIN calls "read", costs a system call and a copy if the kernel has the page cached, and a trip to the device if it doesn't. Postgres can't tell those last two apart.
3. Figure 4-1 — Line pointers from the front, tuples from the back. The two grow toward each other, and the page is full when they meet.
4. Figure 5-1 — Finding a page is one hash lookup. The tag lands in one of 128 separately locked partitions. A hit pins the buffer and is done; a miss has to evict a victim, maybe write it out, and read the page in.
5. Figure 5-2 — The hand takes a point from every buffer it passes. It skips pinned buffers and evicts the first one it finds at zero. A page read constantly is back at 5 before the hand returns, so it never gets there.
6. Figure 6-1 — An update is an insert plus a stamp. The old version stays in slot 1, stamped with the updater's XID and a ctid pointing at (0,4); the new version is a complete copy in slot 4. Four tuples now hold three rows.
7. Figure 7-1 — HOT keeps the new version where the indexes can already find it. A cold update puts the new version on another page and adds an entry to every index. A HOT update writes it on the same page, chained from the old one through t_ctid, and no index changes; after pruning, lp1 becomes a redirect to the new version.
8. Figure 8-1 — A commit waits for the log, not the pages. The update dirties a page in shared_buffers and appends a WAL record. COMMIT waits only until that record is flushed to the sequential WAL; the checkpointer or background writer writes the page to the data files later.
9. Figure 8-2 — Recovery starts at the redo point. A checkpoint begins at its redo point, writes the dirty pages, and logs its checkpoint record when done. Everything before the redo point is already in the data files, so after a crash, replay starts there.
10. Figure 8-3 — Every checkpoint resets the images. The first change to a page after a checkpoint carries a full 8 KB image of it; later changes to that page are small records. After the next checkpoint, the first change pays again.
11. Figure 11-1 — InnoDB finds rows by key, Postgres by address. In InnoDB the table is the primary key B-tree, so a secondary index entry holds the key and every row found through it descends that tree again. In Postgres every index, the primary key included, points straight at a page in the heap.
12. Figure 13-1 — Increasing keys append; random keys scatter. New identity or UUIDv7 keys all land on the rightmost leaf, which stays in memory. UUIDv4 keys land on any leaf, a full leaf splits 50/50, and pages settle around 70 percent full against 90 for appends.
13. Figure 17-1 — A lookup reads one page per level, then the heap. The primary key of events is 1 root page, 19 internal pages and 5,465 leaves over a heap of 35,173 pages, so finding a row reads three pages in the tree and one in the heap.
14. Figure 21-1 — Find the node, then fix the estimate or the work. If estimated and actual rows differ 10x or more, fix the statistics or the query that misleads them; if the rows are right, fix the index, the shape or the prices. Reach for enable_* and hints only at the end.
15. Figure 22-1 — OFFSET counts from the front; keyset starts at the bookmark. OFFSET 1000000 reads 1,000,020 index entries and 21,422 pages to show 20. The keyset query seeks to where the last page ended and reads the next 20 in 5 pages, the same on every page.
16. Figure 25-1 — The second write wins, silently. Both transactions read 100, and tx2 waits for tx1's row lock, then writes a value computed from that stale read. The balance ends at 80, and tx1's spend is lost with no error.
17. Figure 25-2 — One waiting lock stops every query. The migration waits for the idle reporting transaction, and every query after it, even a plain read, waits behind the migration. When reporting commits, the migration runs in a millisecond and the line drains.
18. Figure 32-1 — Recovery is a base backup plus replay. Restore the base backup, then replay archived WAL forward until recovery_target_time. Replay stops just before the 14:02 mistake and the server opens there.
19. Figure 38-1 — A replica replays the primary's WAL forever. The walsender streams WAL to the standby's walreceiver, the startup process replays it into pages, and the standby reports its LSN positions back to pg_stat_replication. It takes reads and refuses writes with 25006.
20. Figure 39-1 — Deleting a month is work; dropping one is a file. The delete left 164,383 dead tuples and wrote 32 MB of WAL, and the vacuum after it wrote another 12 MB. Detaching and dropping the month's partition unlinks its files and wrote under 150 KB.
21. Figure 40-1 — The shard key decides who does the work. A query that names account_id goes to the one shard holding that account, where its rows sit together. A query without it goes to every shard, and the results are merged.

Tables

1. Table 1-1 — The machine comes first; then jump to what hurts
2. Table 1-2 — One ordinary SaaS schema runs through every chapter
3. Table 2-1 — A handful of psql commands cover this book
4. Table 3-1 — Memory is cheap; everything else is expensive
5. Table 3-2 — Round trips cost more than queries
6. Table 4-1 — A row costs more than its columns
7. Table 5-1 — Your query shouldn't be the one writing dirty pages
8. Table 5-2 — Big operations get a ring
9. Table 7-1 — A cold update pays for every index
10. Table 7-2 — Every column an index mentions is indexed
11. Table 7-3 — Leave room on the page, or every update moves
12. Table 7-4 — Leave room on the page, or every update moves
13. Table 7-5 — A long transaction turns HOT off
14. Table 7-6 — Bottom-up index deletion is the safety net
15. Table 8-1 — wal_compression is a cheap win
16. Table 8-2 — Commit means flush
17. Table 9-1 — Four promises, four mechanisms
18. Table 9-2 — Only serializable rules out every anomaly
19. Table 10-1 — The defaults are wrong for big tables
20. Table 10-2 — Wraparound stops writes before anything is corrupted
21. Table 11-1 — Where rows and old versions live decides the rest
22. Table 11-2 — Secondary lookups cost two descents in InnoDB
23. Table 11-3 — Integer division truncates, and NULLs sort last
24. Table 11-4 — Most MySQL idioms have a direct Postgres equivalent
25. Table 12-1 — Integers run out sooner than you think
26. Table 12-2 — Pick a collation provider whose order never changes
27. Table 13-1 — Random keys write nearly four times the WAL
28. Table 13-2 — Random keys write nearly four times the WAL
29. Table 13-3 — Every id format trades order against what it leaks
30. Table 14-1 — Store each fact once
31. Table 14-2 — The insert pays for every check
32. Table 14-3 — History is a table you design
33. Table 15-1 — Each model is one shape, made fast
34. Table 15-2 — Entity-attribute-value is a trap
35. Table 15-3 — Closure tables store every ancestor pair
36. Table 16-1 — Contrib is the safest shelf, though some of it is obsolete
37. Table 16-2 — A crashing extension drops every connection, so vet it
38. Table 17-1 — Every index is a tax on writes
39. Table 18-1 — Beyond the B-tree
40. Table 18-2 — For containment, jsonb_path_ops beats the default
41. Table 18-3 — GIN filters trigrams; GiST ranks by similarity
42. Table 18-4 — A thousandth of the size, nearly the same pages
43. Table 18-5 — The operator picks the index type
44. Table 20-1 — Every price is relative to one sequential page
45. Table 20-2 — Extended statistics measure columns together
46. Table 21-1 — The 5% rule is wrong in both directions
47. Table 21-2 — Join order is a search, and searches get expensive
48. Table 22-1 — OFFSET reads every row it skips
49. Table 23-1 — Store the tsvector; don't index the expression
50. Table 23-2 — Search extensions put BM25 in the index
51. Table 23-3 — ef_search trades latency for recall
52. Table 24-1 — The primary is your most expensive CPU
53. Table 24-2 — A row trigger costs about a microsecond a row
54. Table 25-1 — Row locks live in the row, not in pg_locks
55. Table 26-1 — Pick the index build that matches the table's traffic
56. Table 26-2 — Pick the index build that matches the table's traffic
57. Table 26-3 — Run a concurrent build alone, untimed, and check it's valid
58. Table 26-4 — Widening a type is free; most other type changes rewrite
59. Table 28-1 — Transaction pooling breaks session state
60. Table 29-1 — Ask one question
61. Table 30-1 — Context decides reload or restart
62. Table 30-2 — Autovacuum's defaults suit small tables and slow disks
63. Table 30-3 — Cheap logging answers most 3 a.m. questions
64. Table 32-1 — Write down RPO and RTO first
65. Table 32-2 — pg_dump leaves out roles; dump them separately
66. Table 32-3 — pg_upgrade --link is fast and one-way
67. Table 33-1 — The app never owns its tables
68. Table 34-1 — Some jobs stay yours whoever runs the box
69. Table 34-2 — Some jobs stay yours whoever runs the box
70. Table 34-3 — The provider adds its own gate on CREATE EXTENSION
71. Table 34-4 — Throttled I/O shows as flat IOPS and rising latency
72. Table 34-5 — Failover is DNS, and your pool has to notice
73. Table 35-1 — Know the shape and you know the tradeoffs
74. Table 35-2 — Superuser access and retention rule services out
75. Table 36-1 — 10. Scale out only for the signal that asks for it
76. Table 36-2 — Eight changes, in the playbook's order
77. Table 36-3 — Eight changes, in the playbook's order
78. Table 37-1 — Measure before you buy anything
79. Table 37-2 — Precompute what you show
80. Table 38-1 — Synchronous commit costs a round trip
81. Table 38-2 — Streaming for failover, logical for a job
82. Table 39-1 — Range fits time; list and hash have narrow jobs
83. Table 39-2 — Retention: detach, then drop
84. Table 39-3 — Every partition a query can't prune costs planning time
85. Table 39-4 — Every partition a query can't prune costs planning time
86. Table 40-1 — Each option moves the hard parts somewhere else
87. Table 40-2 — Each option moves the hard parts somewhere else
88. Table 41-1 — Column stores keep a tenth of the bytes
89. Table 41-2 — Postgres read every page for every query
90. Table 41-3 — Column stores were about ten times faster, and still faster on one core
91. Table 41-4 — Column stores were about ten times faster, and still faster on one core
92. Table 41-5 — Rollups and a column extension close much of the gap
93. Table 41-6 — Know the signal before you need it

Parts

About this edition

© 2026 Flagon, Inc. All rights reserved. This English web edition reproduces the first edition of Eight Kilobytes: PostgreSQL from the page up, published 2026-10-05, with examples run against PostgreSQL 18. The original book text and figures are available online at Flagon. The book’s license permits online reading and reserves republication rights to Flagon, Inc.; this project records the source and version explicitly.

The lab kit and its MIT-licensed source files remain available from the original Eight Kilobytes lab.

1 Before you start

Here are two queries against the same table, on the same server, a few seconds apart:

select * from events where id = 777777;
select count(*) from events where project_id = 4242;

The first touched 4 pages. The second touched 35,173.

Both return almost nothing, both look simple, and an ORM would generate either without complaint. One is how a database idles at 3% CPU. The other is how someone gets paged at 2 a.m.

That gap is why this book exists.

Count the pages

Most PostgreSQL advice arrives as folklore. Add an index. Raise shared_buffers. Run vacuum. Each is right some of the time, and without a model of the server you can’t tell which time this is.

The model is smaller than you’d think. Postgres stores everything (tables, indexes, the bookkeeping beside them) in 8 KB pages, and it reads, writes and caches whole pages. A slow query nearly always did one of two things: touched far more pages than it needed, or made far more round trips than it needed.

So the first habit this book builds is one sentence long: count the pages.

• When you design a table, ask how many rows fit on a page.
• When you add an index, ask how many pages a lookup touches before and after.
• When you read a plan, read the Buffers: line first. It counts pages.
• When a table grows faster than its data, ask what is sitting in those pages that you didn’t put there.

You don’t need to think about bytes all day. You need a feel for what fits in 8 KB, because nearly every performance property of Postgres follows from it. Hence the title.

Earn the bigger box

The second habit is about money. When a database gets slow, the easiest thing to buy is a bigger one, and it works, for a while. Then the data catches up, and you’re paying more every month for the same problem.

An index costs nothing per month. A rewritten query costs an afternoon. A pooler, a vacuum setting, or a smaller row costs a little reading. So we hold one opinion harder than any other in this book: use up the cheap fixes before you buy hardware, and when you do upgrade, prove the need in numbers (Earn the bigger box). Every chapter is, one way or another, a cheap fix and the evidence that it worked.

Start with one database, and leave on evidence

A growing product collects databases: a cache, a document store, a queue, a search engine, a vector store. Each brings its own backups, upgrades, failure modes, and pager, and its data has to stay in sync with all the others.

Postgres covers most of that list from one server, in the same transaction as the rest of your data. Documents go in jsonb. A queue is a table and for update skip locked. Full-text search and vectors live next to the rows they describe. Time series fit BRIN and monthly partitions, and maps run on PostGIS. It’s one size that fits most, and most reaches further than you’d guess (You don’t need another database (yet)).

Most isn’t all, and this book says where the line is. On the same two million events, two column stores kept the data in under a fifth of the bytes and answered about ten times faster than a tuned Postgres, and still at least twice as fast on one core. Search relevance at scale, streams, global writes, and very large vector sets have lines of their own (Where Postgres loses).

So we start every product on one Postgres, and add a second system only for a property we can name and a number that proves we need it, with Postgres still the source of truth. It’s the bigger box again, applied to architecture: start grounded, and leave on evidence.

Every claim comes with a proof

You shouldn’t take a database book’s word for anything, this one included. So every claim that matters ships with its evidence.

Every query here ran against a real PostgreSQL 18 server, on a sample schema you can load in a minute. The plans are real plans, the sizes are real sizes, and when a chapter says an index helps, it shows the pages read before and after. Prove it boxes sit next to the claims that matter. Each holds the exact check from that chapter’s lab script, a lab.prove(...) that fails loudly if the claim isn’t true, so you can rerun the evidence instead of trusting us.

That’s also how the book stays honest. When Postgres changes, a check fails, and the chapter gets fixed.

You know SQL; this is what’s underneath it

You write application code and use Postgres every day, through an ORM or by hand. You know select, join, group by, insert and update. Maybe you recently became “the database person” because someone had to.

What you lack is a model of what’s underneath: why an update makes a table bigger, why last month’s fast query is slow now, why the planner ignores the index you just built. Without it, tuning is guessing. Every internal concept is explained the first time it appears. No C, no source code, no production experience required.

Read it at two depths

Every chapter is two books in one. The simple track is a short essay: the argument, the one piece of evidence that proves it, and what to do. Read only that, in every chapter, and you get the whole book. The deep dives hold the internals, the full measurements, and the edge cases, each one placed right after the paragraph it deepens.

On the site, the Simple / Deep dive switch at the top of a chapter sets the depth for the whole book; in Simple mode each deep dive folds to one line you can open on its own. On paper, every deep dive is printed, set apart so you can skip it, and each Prove it box prints as the claims its checks make, plus the lab file that holds them.

Why I wrote this

I’m Chase Pierce, and I started Flagon to build software, and a company, in the open.

I don’t have a war story about how I got here. I picked Postgres for plain reasons and kept picking it. Over MySQL, because it does more: transactional schema changes, richer types, and an extension system that lets the database grow into what you need. Over Oracle and SQL Server, because it’s open source, runs anywhere, and has no license to count cores against. Those reasons have held up on every project since.

What never held up was the advice around it. The book I wanted didn’t exist in one place. Internals books assume you want to work on the server itself. Tuning guides hand you settings without the model that explains them. Blog posts are right for someone’s workload, on someone’s version, in some year. I wanted the model, the evidence, and the opinions together, written for the person who just inherited the database. So I wrote it, and made every claim carry a check, because a claim you can’t rerun is one more piece of folklore.

The rest of the book says “we”. That’s Flagon’s voice, and it’s also you and me at the same terminal, looking at the same plan. When we hold an opinion, we say so plainly and show the numbers behind it. When you find a mistake, it’s a bug: open an issue or send a fix.

The machine comes first; then jump to what hurts

First comes a primer, Know the nouns first: a crash course in SQL and the server’s vocabulary: tables, queries, updates, joins, indexes, databases, schemas, roles, the catalog. Skip it if search_path and pg_class are old friends. The machine is the foundation. If you read nothing else, read Everything is a page and Updates are inserts; almost every later chapter leans on them. Then jump to whatever is hurting. A slow query ruining your week? Start with Read the plan, not the clock. Coming from MySQL? Postgres is not MySQL flags the habits that don’t transfer.

Every chapter ends with “The short version” and “Further reading”.

Every example ran on PostgreSQL 18

The book targets PostgreSQL 18. Almost every example ran on 18.6; the few chapters that needed a third-party image pinned another minor release and say so (Citus in Shard last on 18.4, pg_duckdb in Where Postgres loses on 18.1). Most of it applies to any release from 13 on, and newer features are tagged with the version that introduced them, like MERGE (PostgreSQL 15+). Page layout, MVCC, vacuum, the planner and the write-ahead log have changed in detail, not in shape.

Output from psql and EXPLAIN sits in its own block right after the query that produced it, trimmed with ... where long. Some of it will differ on your machine, and that’s expected:

• Transaction IDs, OIDs and file names are assigned by the server as it runs. Yours will differ; the relationships between them won’t.
• Timings depend on hardware and load. Ours ran on a laptop, inside Docker, next to other workloads, and a few labs that write heavily turn on synchronous_commit = off to finish faster (they say so), so treat every millisecond as rough.
• Buffer counts and row counts are the evidence. For a given data set and plan, they come out the same every time. A whole query’s count can run a few dozen pages past the table itself, for the catalog and other small reads: a full scan of the 35,173-page events heap reports about 35,249.
• Sizes are the binary values pg_size_pretty reports: the 288,137,216-byte events heap is 275 MB, not 288.

Callouts flag what you must not miss. Read every warning; each one describes a way to take down a production database.

Run your own PostgreSQL 18

You need Docker, and bash: any Linux or macOS terminal, or Git Bash or WSL on Windows. The lab kit is a small Compose project: PostgreSQL 18 with the example data, plus one script per chapter that turns the chapter’s claims into checks. Download it and run a chapter:

curl -L https://www.flagon.io/books/eight-kilobytes/lab.tar.gz | tar xz
cd eight-kilobytes-lab
./lab 03-storage

A chapter’s number or name works too (./lab 3, ./lab storage), and ./lab alone lists them all. The first run starts the server and builds the example database, which takes about a minute. Each run gets a fresh copy of the data, prints PROVED: for every claim that holds, and stops at the first one that doesn’t. ./lab all runs every chapter and lists any that failed; the lab page explains the rest.

The server listens only on your machine, at 127.0.0.1:5418, if you’d rather use a GUI client. A few chapters (replication, sharding, the MySQL comparison) need more than one server; for those, ./lab starts the extra containers and removes them afterwards.

The kit’s postgres user is a superuser. That matters: some of the book’s best tools (pageinspect above all, which reads the raw bytes of a page) are superuser-only by default, and managed services generally don’t give you one. When you’re finished, docker compose down -v throws it all away.

One ordinary SaaS schema runs through every chapter

Every chapter uses the same small SaaS schema: accounts, their users, the projects they own, and a large table of events (deploys, builds, comments, alerts, logins) that grows forever. It’s deliberately ordinary, and big enough for the planner to make real decisions and for mistakes to cost something you can measure:

Every table has a bigint identity id and a created_at. The data is generated from a fixed random seed and dated back from one fixed moment, October 6, 2026, so your plans, counts and dates will match ours.

Two things are intentional, so don’t “fix” them yet:

• events has no secondary indexes, only its primary key. The indexing chapters add them one at a time, so you can see exactly what each one buys.
• Only one foreign key column is indexed, and only by accident. users.account_id leads the unique (account_id, email) constraint’s index; no other referencing column has one. Postgres doesn’t index referencing columns for you, and that’s one of the most common mistakes in real schemas. Constraints, chosen on purpose shows what it costs.

create table events (
  id          bigint generated always as identity primary key,
  account_id  bigint not null references accounts (id),
  project_id  bigint not null references projects (id),
  user_id     bigint references users (id),
  kind        text not null,
  payload     jsonb not null default '{}',
  created_at  timestamptz not null default now()
);

select setseed(0.42);
-- ... accounts, users and projects first ...

insert into events (account_id, project_id, user_id, kind, payload, created_at)
select p.account_id, p.id, p.owner_id, r.kind,
       jsonb_build_object('status', r.status,
                          'duration_ms', r.duration_ms,
                          'region', r.region),
       r.created_at
from (
  select g,
         1 + floor(random()*20000)::bigint as project_id,
         (array['deploy','deploy','build','build','build',
                'comment','alert','login'])[1 + floor(random()*8)::int] as kind,
         (array['ok','ok','ok','failed'])[1 + floor(random()*4)::int] as status,
         floor(random()*5000)::int as duration_ms,
         (array['iad','sfo','ams','fra','syd'])
           [1 + floor(random()*5)::int] as region,
         timestamptz '2026-10-06' - interval '365 days'
           + (g * interval '365 days' / 2000000) as created_at
  from generate_series(1, 2000000) g
) r
join projects p on p.id = r.project_id
order by r.g;

vacuum analyze;

The kit loads it into a database called book (about 345 MB) and marks it as a template. Copying a template is far cheaper than rerunning the seed, so explore in a copy:

docker compose exec postgres psql -c "create database scratch template book"
docker compose exec postgres psql -d scratch

Break things here, not in production

Some examples are destructive on purpose: they update millions of rows, hold transactions open, or delete half a table to watch vacuum work. Run them on your disposable server, never on a database anyone depends on. Where a technique is meant for production (a migration pattern, a monitoring query, a setting), the text says so, with what it locks and what it costs.

After a short primer on the nouns, the first stop is the hardware, because a page costs wildly different amounts of time depending on where it is when you ask for it.

The short version

• Nearly every Postgres performance question reduces to pages touched and round trips made. Count the pages.
• Use up the cheap fixes before you buy hardware, and prove the need in numbers when you do. Earn the bigger box.
• Start on one Postgres. Add a second system only for a property you can name and a number that proves you need it.
• Read the Buffers: line of a plan before the timing. Buffer and row counts are repeatable; milliseconds aren’t.
• Every claim that matters has a Prove it check. Rerun it instead of trusting us.
• Read the simple track for the argument; open a deep dive when you want the internals or the full measurements.
• Get the lab kit for a disposable PostgreSQL 18 with the example data in a template called book.
• Rerun any chapter’s evidence with ./lab <chapter> from the kit’s folder; the Prove it boxes show the checks.
• Work in a copy (create database scratch template book) and drop it when you’ve made a mess.

Further reading

• PostgreSQL 18 documentation, PGDG. The reference this whole book leans on; learn to find your way around it.
• PostgreSQL 18 release notes, PGDG. The source for every “(PostgreSQL 18+)” tag in the book.
• Versioning policy, PGDG. Five-year support windows, and how long your major version has left.
• psql reference, PGDG. The client behind every example; its backslash commands repay an hour of reading.
• Set returning functions, PGDG. How generate_series builds the seed, and how to build your own test data.

2 Know the nouns first

This book argues about pages, plans, and locks. Those arguments assume some SQL and a handful of Postgres nouns. If you’ve written select, insert, and update against Postgres and know what a schema is, skim the headings below and move on; you lose nothing. If you haven’t, this primer is your crash course: the least you need to follow every chapter after it.

Fair warning about what comes next: the rest of the book assumes everything here and goes deep fast, down to the bytes on disk, because that’s where the answers to “why is this slow?” live. Every claim in this chapter runs against the lab server, like the rest of the book, so even the basics come with proof.

A table is rows that share the same columns

Postgres keeps your data in tables. A table has named, typed columns, and every row holds one value for each column, or NULL, which means “no value” (not zero, and not an empty string). Here’s the book’s biggest table, events:

create table events (
  id          bigint generated always as identity primary key,
  account_id  bigint not null references accounts (id),
  project_id  bigint not null references projects (id),
  user_id     bigint references users (id),
  kind        text not null,
  payload     jsonb not null default '{}',
  created_at  timestamptz not null default now()
);

Read it line by line and you’ve met most of what a schema says. bigint is a large integer, text is a string of any length, jsonb is a JSON document, and timestamptz is a moment in time. not null forbids NULL, and default fills in a value when you don’t give one. The id is the primary key: a value no other row shares, which Postgres numbers for you, so any row can be found by it. Each references line is a foreign key: account_id must name a row that exists in accounts.

events holds two million rows, every one with the same seven columns. A table is the shape; the rows are the data.

select lab.prove('events has 2,000,000 rows, every one with the same seven columns',
  (select count(*) from events) = 2000000
  and (select count(*) from information_schema.columns
       where table_schema = 'public' and table_name = 'events') = 7);

SELECT asks, and changes nothing

You read with select. Name the columns you want, the table they come from, and a where clause that keeps only the rows you care about; order by sorts them and limit stops early:

select id, kind, created_at
from events
where project_id = 4242
order by created_at desc
limit 3;

Two more words cover most reports. count(*) counts rows, and group by counts them per value:

select kind, count(*)
from events
group by kind;

A select never changes anything. It doesn’t even take a transaction ID (XID), the number Postgres stamps on every change. That doesn’t make it free. The whole book is about how many pages a select has to read to answer you.

select lab.prove('a select takes no transaction id: reading writes nothing',
  pg_current_xact_id_if_assigned() is null);

INSERT adds a row, and in Postgres so does UPDATE

Three statements change data. insert adds rows, update changes them, and delete removes them. Here they are on notes, a scratch table with an id and a body:

insert into notes (id, body) values (1, 'first draft');
update notes set body = 'second draft' where id = 1;
delete from notes where id = 1;

Always give update and delete a where. Without one, they apply to every row in the table, and Postgres won’t ask whether you meant it.

Here’s the first thing that makes Postgres Postgres. An update doesn’t change the row where it sits. It writes a complete new copy of the row, on the same page if there’s room, and marks the old copy as replaced. A delete marks the row dead and leaves it in place. Both old copies stay on the page until vacuum, a background cleanup, clears them. On a one-row table, one update left two copies on the page, and after the delete there were still two copies of a row the table no longer has.

That one fact explains why tables grow, why vacuum exists, and why a busy table can be twice the size of its data. The book calls it Updates are inserts, and it comes back in nearly every chapter.

select lab.prove('an update gives the row a new address',
  (select ctid::text from notes where id = 1) <> :'ctid_before');
select lab.prove('and leaves the old version on the page: one row, two copies',
  (select count(*) from notes) = 1
  and (select count(*) from heap_page_items(get_raw_page('notes', 0))) = 2);
select lab.prove('a delete leaves the row on the page too, marked dead, for vacuum to clear',
  (select count(*) from notes) = 0
  and (select count(*) from heap_page_items(get_raw_page('notes', 0))) = 2);

JOIN puts the tables back together

A good schema stores each fact once: a user’s email lives in users, an account’s plan in accounts. A join brings them back together by matching rows across tables, almost always on a foreign key:

select u.email, a.plan
from users u
join accounts a on a.id = u.account_id
where u.id = 295;

The u and a are short names for the tables, and on says which rows match: here, the account whose id is the user’s account_id. User 295 belongs to account 296. A plain join keeps only rows that match on both sides; left join keeps every row on the left, with NULLs where nothing matched.

select lab.prove('a join follows account_id from a user to its account: user 295 is in account 296',
  (select a.id from users u join accounts a on a.id = u.account_id where u.id = 295) = 296);

An index makes one read cheap and every write dearer

Ask for one user by email, and without help Postgres reads the whole users table to find it: 1,126 pages for one row. (The unique (account_id, email) constraint has an index, but it’s sorted by account first, so finding an email through it means searching it once per account, and the planner reads the table instead.) An index is a separate, sorted structure that points at rows, like the index at the back of a book. Build one on email and the same lookup reads 4 pages:

create index users_email_idx on users (email);

Indexes aren’t free. Every insert, and every update that changes an indexed column, has to update each index too. So index the columns your queries actually search on, not every column you have. The B-tree does the heavy lifting shows how an index finds a row in a handful of pages.

select lab.prove('finding one user by email reads hundreds of pages without an index and a handful with one',
  :before_index > 300 and :after_index < 10);

That’s enough SQL to follow the book. The rest of this chapter is the server underneath it.

One server runs many databases, and they don’t share tables

A Postgres server is one data directory and the processes running on it. The documentation calls it a cluster, which has nothing to do with several machines; it’s one server. It holds many databases. The lab server has four:

 datname   | datistemplate
-----------+---------------
 book      | t
 postgres  | f
 template0 | t
 template1 | t

A connection picks one database and sees only that one. Try to read another and Postgres refuses:

ERROR:  cross-database references are not implemented: "book.public.events"

That’s a design decision, not a missing feature: each database has its own tables, its own catalog, and its own extensions. Roles, tablespaces, and the server’s configuration file are the exceptions, shared by the whole server. If two parts of your app need to join their data, they belong in one database, in two schemas.

create database copies template1 unless you name another template; template0 is a pristine spare. The lab marks book as a template too, which is why create database scratch template book is far cheaper than loading the data again.

select lab.prove('the server holds several databases, book among them, and book is a template',
  (select count(*) from pg_database) >= 4
  and (select datistemplate from pg_database where datname = 'book'));
select lab.prove('a query can''t reach into another database',
  lab.try($$ select count(*) from book.public.events $$)
    like '0A000: cross-database references are not implemented%');

Every connection is its own process

When your app connects, the server starts a new operating-system process to serve it, a backend, and that process lives until the connection closes. Ten connections are ten processes, each with its own memory. That one fact explains why connections are expensive, why max_connections is a ceiling and not a capacity, and why every production app ends up behind a pooler (Connections aren’t free).

The server runs its own processes too. On the lab server, beside the one client:

 backend_type                 | count
------------------------------+-------
 autovacuum launcher          |     1
 background writer            |     1
 checkpointer                 |     1
 client backend               |     1
 io worker                    |     3
 logical replication launcher |     1
 walwriter                    |     1

Most of the book is about what those do: the WAL writer flushes the write-ahead log in the background, the checkpointer and background writer write changed pages back to their files, the autovacuum launcher starts vacuum workers, and the io workers (PostgreSQL 18+) do reads on the backends’ behalf.

select lab.prove('a second connection is served by a different server process',
  (select pid from dblink('other', 'select pg_backend_pid()') as t(pid int)) <> pg_backend_pid());
select lab.prove('the server runs its own processes too: a checkpointer, a WAL writer, an autovacuum launcher',
  (select count(distinct backend_type) = 3 from pg_stat_activity
   where backend_type in ('checkpointer', 'walwriter', 'autovacuum launcher')));

Schemas are folders, and search_path picks the one you meant

Inside a database, tables live in schemas: named folders, one level deep. Every new database has one called public, and if you’ve never created another, everything you own is in it. public.invoices and billing.invoices are two different tables.

When you write a bare invoices, Postgres looks through the schemas named in search_path, in order, and uses the first match. Put billing first and the same query reads a different table. That’s convenient for organizing a big schema. It’s also how a function can end up calling something you didn’t write, which is why the security chapter pins search_path on every function that runs with elevated rights.

select lab.prove('with the default search_path, invoices means public.invoices',
  (select note from invoices) = 'public');
set search_path = billing, public;
select lab.prove('put billing first and the same query reads billing.invoices',
  (select note from invoices) = 'billing');

A role is a user, a group, or both

Postgres has one kind of principal, the role. A role with the LOGIN attribute is what you’d call a user; a role without it is a group; and any role can be a member of another and inherit its privileges. Every table has an owner role, which can do anything to it, and every other role gets only what it’s granted.

The habit to build now: your application should connect as a role that owns nothing and isn’t a superuser, so a bug in the app can’t drop a table. Trust no tenant shows the setup.

select lab.prove('a role with LOGIN is a user; one without is a group, and membership links them',
  (select rolcanlogin from pg_roles where rolname = 'ana')
  and not (select rolcanlogin from pg_roles where rolname = 'reporting')
  and pg_has_role('ana', 'reporting', 'member'));

Every statement runs in a transaction

Postgres never runs a statement outside a transaction. Send one update on its own and Postgres wraps it in a transaction that commits when the statement finishes; that’s autocommit, and it’s what your driver does unless you say begin. Two statements sent that way are two transactions, each paying for its own commit.

Inside begin and commit, everything lands together or not at all, and in Postgres “everything” includes schema changes. A create table rolled back is gone, so a migration that runs in one transaction and fails leaves nothing half-done (ACID, honestly). It also means a long transaction holds everything it touched, locks included, until it ends. You’ll meet that open transaction again and again in this book.

select lab.prove('a rolled-back transaction takes its create table with it',
  to_regclass('scratch_t') is null);
select lab.prove('two statements outside begin run as two transactions, with two transaction ids',
  :xid1 < :xid2);

The server describes itself in tables

Everything Postgres knows about your schema lives in ordinary tables you can query, the system catalog. pg_class lists every table and index, pg_attribute their columns, pg_roles the roles, pg_settings every setting. The pg_stat_ views count what the server has been doing. psql’s backslash commands, like \d events, are queries over these tables.

The catalog already counts pages for you:

 relname | relkind | relpages | reltuples
---------+---------+----------+-----------
 events  | r       |    35173 |   1999995

That’s the planner’s picture of events: 35,173 pages and about two million rows, updated by vacuum and analyze. That page count is the number this whole book keeps returning to.

select lab.prove('pg_class already knows events is 35,173 pages and about 2 million rows',
  (select relpages = 35173 and reltuples between 1990000 and 2010000 from pg_class where relname = 'events'));

A setting has a value, a source, and a scope

Postgres has a few hundred settings, like work_mem, the memory one sort may use (4 MB by default). Each one has a value, a place that value came from, and a scope. The configuration file sets the server’s default. alter database and alter role override it for one database or one role. set changes it for your session, and set local for one transaction, after which it snaps back.

You’ll use the narrow scopes most. A report that needs more memory for one big sort should set local work_mem inside its transaction, not raise it for every connection on the server. show work_mem reads the current value, and pg_settings tells you where it came from (The settings that matter).

select lab.prove('work_mem defaults to 4MB',
  current_setting('work_mem') = '4MB');
begin;
set local work_mem = '64MB';
select lab.prove('set local changes it for this transaction only',
  current_setting('work_mem') = '64MB');
commit;
select lab.prove('and it''s back at commit',
  current_setting('work_mem') = '4MB');
select lab.prove('pg_settings says where each value came from',
  (select source from pg_settings where name = 'work_mem') = 'default');

Extensions arrive with one command

An extension adds types, functions, operators, or index methods to one database with create extension. Some ship with Postgres (the “contrib” modules); others you install on the server first. The lab database has the five this book leans on: pg_stat_statements to rank queries, pageinspect to read raw pages, pgstattuple and pg_buffercache to measure tables and the cache, and pg_trgm for text search.

Managed services allow a fixed list, so check it before you design around one (Extensions worth knowing).

select lab.prove('the lab database has the book''s five extensions installed',
  (select count(*) from pg_extension
   where extname in ('pg_stat_statements', 'pg_trgm', 'pageinspect', 'pg_buffercache', 'pgstattuple')) = 5);

Your data is files you never touch

Under it all, every table and index is a file in the server’s data directory, named by numbers, not by your table names:

       file       |   bytes
------------------+-----------
 base/16841/16644 | 288137216

288,137,216 bytes is 35,173 pages of 8,192 bytes, the same count the catalog gave. Postgres reads and writes those files a page at a time, and that’s where the rest of the book begins. Everything is a page opens one up.

Never edit, copy, or delete anything in the data directory by hand, least of all pg_wal. The server is the only thing that understands those files, and a copy taken from under a running server is not a backup.

select lab.prove('events is a file under base/ of exactly 35,173 pages of 8,192 bytes',
  pg_relation_filepath('events') like 'base/%'
  and pg_relation_size('events') = 35173 * 8192);

A handful of psql commands cover this book

Every example in the book ran in psql, the command-line client that ships with Postgres. GUI clients work fine, but a handful of backslash commands are worth knowing by heart:

Add \? for the rest. When a backslash command shows you something useful, \set ECHO_HIDDEN on prints the catalog query behind it, which is the fastest way to learn the catalog.

The short version

• A table is rows that share typed columns. The primary key finds a row; a foreign key points at one in another table.
• select reads and changes nothing. In Postgres an update writes a new copy of the row, and a delete leaves the old row in place, until vacuum clears them.
• An index turned 1,126 pages into 4 for one lookup, and every write pays to keep it current.
• One server holds many databases that can’t query each other; inside one, schemas are folders and search_path picks among them. Roles are shared by the whole server, and apps should connect as one that owns nothing.
• Every connection is a process with its own memory, which is why connections cost what they do.
• Every statement runs in a transaction. Schema changes roll back too, and an open transaction holds what it touched.
• The catalog is queryable tables, settings have scopes (use the narrowest), and tables are files of 8 KB pages you never touch by hand.

Further reading

• The SQL Language, PostgreSQL documentation. The manual’s own tutorial: tables, queries, joins, and updates, one short page each.
• Architectural Fundamentals, PostgreSQL documentation. The two-page version of the process model, from the tutorial.
• Schemas, PostgreSQL documentation. Schemas, search_path, and the usage patterns the manual recommends.
• Database Roles, PostgreSQL documentation. Roles, attributes, and membership.
• System Catalogs, PostgreSQL documentation. Every catalog table, column by column, for when \d isn’t enough.
• psql, PostgreSQL documentation. Every backslash command and variable.

3 Where the time goes

An endpoint that lists a team’s projects takes 180 ms in production. You copy the query from the logs, run it in psql, and it finishes in 0.4 ms. Again: 0.3 ms. The database is clearly fine.

Then you count. The endpoint doesn’t run one query. It runs one to fetch 100 projects, one per project for the owner, and one per project for the latest deploy. That’s 201 queries, each fast on the server, each crossing the network and back. At half a millisecond per round trip, the network alone costs 100 ms before the database does any work.

Nothing in that story is slow. It’s counted wrong. Know roughly what each kind of operation costs and you can estimate a query, an access pattern, or a loop in your code before you measure it.

Memory is cheap; everything else is expensive

You can’t estimate a query until you know what its parts cost. Here is the latency ladder for a database. Rows marked “measured” come from the lab kit on our laptop; the rest are orders of magnitude, not guarantees.

Three lessons drive almost every database decision. Memory is cheap; everything else is expensive, so the first question about any query is how many pages it touches, and where they are. The network isn’t free, even nearby: a same-zone round trip costs about as much as a network disk read. And durability is the slowest thing on every write, which is why the write-ahead log exists.

A query is pages and round trips

A rough model, good enough for most decisions:

query time  ~  round trips x network latency
            +  pages touched x cost per page (depends on where the page is)
            +  CPU work on the rows in those pages

For a typical application query the CPU term is small and the first two dominate. To see the page term, take the two queries from the preface and ask Postgres to report what it did:

explain (analyze, costs off, timing off, summary off)
select * from events where id = 777777;

 Index Scan using events_pkey on events (actual rows=1.00 loops=1)
   Index Cond: (id = 777777)
   Index Searches: 1
   Buffers: shared hit=4

That’s a query plan, and you’ll read hundreds in this book. Four rules get you through them until Read the plan, not the clock covers the rest. Each line is a step, a node; the most indented runs first and feeds the one above. actual rows is what a node produced, per loop. Buffers: counts the 8 KB pages it touched: hit means the page was already in Postgres’s memory, read means Postgres had to ask the operating system. And read that line first: timings bounce with cache and load, page counts don’t. (PostgreSQL 18 shows it by default; before 18, write explain (analyze, buffers).)

Four pages, all in memory: three in the primary key index (the B-tree chapter shows its shape) and one in the table, holding the row.

select lab.prove(
  'a primary key lookup touches 4 pages: root, internal, leaf, and the table page',
  lab.buffers($$ select * from events where id = 777777 $$) = 4
  and (select level from bt_metap('events_pkey')) = 2);

Now a filter on a column with no index:

explain (analyze, costs off)
select count(*) from events where project_id = 4242;

 Finalize Aggregate (actual time=26.707..28.186 rows=1.00 loops=1)
   Buffers: shared hit=2049 read=33124
   I/O Timings: shared read=8.623
...
         ->  Parallel Seq Scan on events (actual ... rows=28.33 loops=3)
               Filter: (project_id = 4242)
               Rows Removed by Filter: 666638
...
 Execution Time: 28.211 ms

35,173 pages to find 85 rows: every page of the table. It still took only about 30 ms, because three processes shared the work and the “read” pages came from the operating system’s memory, not the disk.

Four pages against 35,173, both “simple”. Once an hour in a report, 30 ms is fine. On every page view, it’s the most expensive thing your database does. The skill here is noticing the ratio.

select lab.buffers($$ select count(*) from events where project_id = 4242 $$) as scan_pages \gset

select lab.prove(
  'a filter on project_id (no index) touches all 35,173 pages to find 85 rows',
  :scan_pages = 35173
  and (select count(*) from events where project_id = 4242) = 85
  and lab.nodes($$ select count(*) from events where project_id = 4242 $$)
      && array['Seq Scan', 'Parallel Seq Scan']);

-- pg_temp.explain_default runs plain explain (analyze, format json)
select lab.prove(
  'since PostgreSQL 18, EXPLAIN ANALYZE reports buffers without being asked',
  pg_temp.explain_default($$ select * from events where id = 777777 $$) ? 'Shared Hit Blocks');

A page lives in one of three places

When Postgres needs a page, it looks in three places, each far slower than the last: a desk, a filing cabinet across the room, a warehouse across town.

shared_buffers is Postgres’s own cache of pages, shared by every server process. The default is 128 MB, tiny on purpose; the usual production starting point is about 25% of RAM (The settings that matter). The buffer cache decides what stays shows how it chooses what to keep.

The OS page cache is the kernel’s cache of file contents, in whatever RAM is otherwise free. Every page Postgres reads also lands there, so a hot page can sit in both caches at once. That double buffering is why shared_buffers isn’t set to 90% of RAM.

“Read” does not mean “from disk”

In a plan, read means “not in shared_buffers, so Postgres asked the kernel”. Whether the kernel went to the device, Postgres can’t see. The clock can: the lab kit turns on track_io_timing, which adds an I/O Timings line. Here are 2,000 primary key lookups scattered across the table:

 Aggregate (actual time=21.062..21.064 rows=1.00 loops=1)
   Buffers: shared hit=4861 read=3139
   I/O Timings: shared read=15.037
...

3,139 pages read in 15.0 ms: about 5 µs per page (4 to 5 µs across our runs). No storage device does random reads that fast; those pages came from the OS page cache. Tens of microseconds or more per page means a device.

explain (analyze, costs off, summary off)
select sum(length(kind)) from events
where id = any(array(select (g * 997)::bigint from generate_series(1, 2000) g));

select lab.plan($$
  select sum(length(kind)) from events
  where id = any(array(select (g * 997)::bigint from generate_series(1, 2000) g)) $$) as p \gset

select lab.prove(
  'thousands of "read" pages at under 20 us each came from the OS page cache',
  (:'p'::jsonb -> 'Plan' ->> 'Shared Read Blocks')::int > 1000
  and (:'p'::jsonb -> 'Plan' ->> 'Shared I/O Read Time')::numeric
      / (:'p'::jsonb -> 'Plan' ->> 'Shared Read Blocks')::numeric < 0.02);

Big scans don’t flush the cache

You might expect that full scan of the 275 MB events table to push everything else out of a 128 MB cache. It doesn’t. A big sequential scan recycles a small private ring of buffers (2,304 KB in PostgreSQL 18 with default settings), so one report can’t evict the pages your application lives on (Big operations get a ring).

Every commit waits for the disk

Reads can be cached. Commits can’t, not if they must survive a crash. On commit, Postgres writes a record to its write-ahead log and waits for the disk to confirm it’s durable. That wait is an fsync, and your hardware sets its price: about 0.65 ms on our quiet laptop SSD, 18 ms with other containers writing to the same disk, and well under a millisecond on good server storage.

A client that commits one row at a time pays one flush per row. At 0.65 ms that’s about 1,500 commits per second at the very best; at 18 ms, about 55. So 10,000 rows in 10,000 transactions cost 10,000 flushes, and in one transaction, one.

Batch your writes (Load it fast), and for write-heavy systems buy storage with fast fsync before fast reads. Commit means flush measures the commit rate, and the two ways around the wait: many sessions sharing one flush, and not waiting at all.

/usr/lib/postgresql/18/bin/pg_test_fsync -s 1 -f /var/lib/postgresql/fsynctest.out
rm /var/lib/postgresql/fsynctest.out

Compare file sync methods using one 8kB write:
...
        fdatasync                          1524.249 ops/sec     656 usecs/op
...
Non-sync'ed 8kB writes:
        write                           2870550.553 ops/sec       0 usecs/op

IOPS are capped by reads in flight, not by what you paid

Every read that misses both caches, and every commit, lands on storage. If you’re on a managed service, your data most likely lives on network block storage: a volume that looks like a local disk but sits on servers down the hall, so every cache miss is a network request. That storage is sold by three numbers that are easy to confuse: latency (how long one I/O takes), IOPS (I/Os per second), and throughput (bytes per second).

Queue depth, the number of I/Os in flight, connects them: IOPS achieved = I/Os in flight / latency. A process that waits for one 1 ms read at a time gets 1,000 IOPS, however many you paid for. (That’s Little’s law; it holds for the line at the post office too.) Big scans can keep many reads in flight; an index lookup that misses the cache is one round trip to the storage servers.

Do the arithmetic. AWS gp3 starts at 3,000 IOPS and 125 MiB/s: about 3,000 random cold page reads per second, for every connection combined. Buy faster volumes when you’ve measured that you’re I/O bound, never instead of an index. On a managed service this is usually the first ceiling you hit (The volume is your bottleneck now).

Burst credits run out in the middle of the job

gp2 volumes and many smaller instance types run on burst credits: fast for a while, then back to a much lower baseline when the credits run out. Testing goes fine, and so does the first half hour of a heavy job; then latency climbs with no change in traffic. If an I/O cliff lines up with time under load rather than with load, check your burst balance before your queries.

Local NVMe is fast and dies with the host

Local NVMe reads in tens of microseconds and dies with the host. Use it only with replication and backups built for losing a node at any moment (Local NVMe: fast, and gone with the host). Network volumes survive the host and charge you latency on every miss.

Size RAM to the working set

The most effective hardware decision for Postgres is enough RAM for your working set: the pages your workload touches over, say, an hour. Not the whole database; the part that’s used.

All of events is 318 MB with its index. But suppose the application mostly shows the last week. Events arrive in time order, so the latest 7 days sit together at the end of the table: 675 pages, about 5 MB, smaller than users. That’s the common shape: a big tail that’s rarely read and a hot head that’s read constantly. Your working set is the hot head of each table, plus the index pages that find it, plus whatever periodic jobs touch.

If the working set fits in RAM (shared_buffers plus the OS page cache), almost every read takes microseconds. If it doesn’t, every query becomes a storage-latency query, and no amount of IOPS will make it feel like memory. RAM is usually cheaper than IOPS, and much cheaper than engineering time.

select relname,
       pg_size_pretty(pg_relation_size(relid))       as heap,
       pg_size_pretty(pg_indexes_size(relid))        as indexes,
       pg_size_pretty(pg_total_relation_size(relid)) as total
from pg_stat_user_tables
order by pg_total_relation_size(relid) desc;

 relname  |  heap   | indexes |  total
----------+---------+---------+---------
 events   | 275 MB  | 43 MB   | 318 MB
 users    | 9008 kB | 8336 kB | 17 MB
 projects | 1528 kB | 456 kB  | 2024 kB
 accounts | 72 kB   | 40 kB   | 152 kB

select count(distinct (ctid::text::point)[0]) as pages,
       pg_size_pretty(count(distinct (ctid::text::point)[0]) * 8192) as size
from events
where created_at > (select max(created_at) from events) - interval '7 days';

 pages |  size
-------+---------
   675 | 5400 kB

select lab.prove(
  'the latest 7 days of events sit in about 675 pages (5 MB), smaller than users',
  count(distinct (ctid::text::point)[0]) between 640 and 710
  and count(distinct (ctid::text::point)[0]) * 8192 < pg_relation_size('users'))
from events
where created_at > (select max(created_at) from events) - interval '7 days';

Round trips cost more than queries

Back to the endpoint from the start. Timed end to end with pgbench, the load generator that ships with Postgres (pgbench is already installed), a primary key lookup took 78 µs, trip included, with client and server on one laptop. Another host in the same zone adds roughly 100 to 500 µs per query; another zone, up to a couple of milliseconds; another region, 30 ms or more.

Now the N+1 math. A page showing 100 projects that loads each owner separately runs 101 queries:

The queries didn’t get slower. Only the distance changed. That’s why N+1 bugs are invisible on a laptop and painful in production, and why moving the database to “a better region” without moving the app makes everything slower.

The ORM version looks innocent:

const projects = await db.project.findMany({ where: { accountId }, take: 100 });
for (const p of projects) {
  // one round trip per project: 100 in all
  p.owner = await db.user.findUnique({ where: { id: p.ownerId } });
}

Fetch them in one query instead, with a join or an array parameter:

select * from users where id = any($1::bigint[]);

Fetching 100 rows by primary key this way took about a millisecond, in one round trip. Most ORMs can ask for it (“eager loading”, “includes”, “preload”); Queries that age well covers the SQL side.

explain (analyze, costs off, summary off)
select * from projects
where id = any(array(select generate_series(1, 20000, 200)));

 Index Scan using projects_pkey on projects (actual ... rows=100.00 loops=1)
   Index Cond: (id = ANY ((InitPlan 1).col1))
   Index Searches: 55
   Buffers: shared hit=57 read=156 written=156

select lab.prove(
  'one query fetches 100 projects by id in one index scan, a few hundred pages at most',
  lab.buffers($$ select * from projects
                 where id = any(array(select generate_series(1, 20000, 200))) $$) < 300
  and 'Index Scan' = any(lab.nodes($$ select * from projects
                 where id = any(array(select generate_series(1, 20000, 200))) $$))
  and (select count(*) from projects where id = any(array(select generate_series(1, 20000, 200)))) = 100);

\set id random(1, 2000000)
select * from events where id = :id;

pgbench -n -c 1 -T 5 -f lookup.sql scratch

number of transactions actually processed: 64088
latency average = 0.078 ms
tps = 12820.761600 (without initial connection time)

Estimate before you measure

You can now estimate most queries without running them. Three same-zone round trips touching 50 cached pages: about 1.5 ms, nearly all network. 2,000 uncached pages on a network volume: 2 seconds, and more CPUs won’t help. A batch job committing every row on a disk with 2 ms fsync: 500 rows per second, at most.

When something is slow, ask which term is large: round trips, pages, or where the pages came from. The rest of the book shrinks each one, starting with the pages themselves.

The short version

• Query time is roughly round trips times network latency, plus pages touched times the cost of wherever those pages live: microseconds in memory, up to milliseconds on a network volume.
• Read the Buffers: line of a plan first; page counts repeat, timings don’t. read means “missed shared_buffers”, not “came from disk”.
• A commit waits for one WAL flush, whatever it contains. Batch rows into transactions, and measure your fsync with pg_test_fsync.
• Size RAM to the working set, not the whole database. On cloud volumes, IOPS achieved is I/Os in flight divided by latency; watch burst credits.
• N+1 queries cost round trips, not server time. Fetch related rows in one query, and keep the app in the same region and zone as the database.

Further reading

• Resource consumption settings, PGDG. The primary source for shared_buffers, work_mem and the I/O settings, including io_method.
• WAL reliability, PGDG. What a durable commit requires from the disk, and how write caches can lie.
• pg_test_fsync, PGDG. Measure your own fsync latency before choosing storage.
• Amazon EBS volume types, AWS. Real IOPS, throughput and latency figures for network block storage; the General Purpose SSD page documents gp3 baselines and gp2 burst credits.
• Numbers Every Programmer Should Know By Year, Colin Scott. The latency ladder, adjustable by year.

4 Everything is a page

Here are two tables with the same six columns, holding the same million rows:

 relname | size  | pages
---------+-------+-------
 padded  | 73 MB |  9346
 packed  | 57 MB |  7353

The only difference is the order the columns were declared in. One is 27% bigger than the other: 27% more pages to scan, cache, back up, and replicate, forever.

Postgres doesn’t really store tables. It stores pages, and a table is whatever fits in them. Picture the page and you can say how many bytes any row takes, and how to make it take fewer. We’ll go from the outside in, mostly through pageinspect, an extension that reads raw pages from SQL.

A table is a file of pages

Every table and index is one or more ordinary files in the server’s data directory. You can ask which:

select pg_relation_filepath('events');

 pg_relation_filepath
----------------------
 base/16764/16644

16764 is the database’s ID and 16644 the table’s relfilenode, the name of its file (yours will differ). From a shell in the container (docker compose exec postgres bash, then cd /var/lib/postgresql/18/docker):

-rw------- 1 postgres postgres 288137216 Oct  6 04:07 base/16764/16644
-rw------- 1 postgres postgres     90112 Oct  6 04:07 base/16764/16644_fsm
-rw------- 1 postgres postgres     16384 Oct  6 04:07 base/16764/16644_vm

As Know the nouns first showed, 288,137,216 bytes divided by 8,192 is exactly 35,173. The file is nothing but pages, one after another, numbered from 0. (Past 1 GB, a table continues in 16644.1, 16644.2, and so on.)

select lab.prove(
  'the events file is nothing but 8 KB pages: 288,137,216 bytes is exactly 35,173 pages',
  pg_relation_size('events') = 288137216
  and pg_relation_size('events') % 8192 = 0
  and pg_relation_size('events') / 8192 = 35173);

Anything that rewrites a whole table (truncate, vacuum full, some alter tables) writes a fresh file under a new name, so never assume the name is stable.

A table is more than its main file

events had three files, not one. Each is a fork with its own job: main holds the rows, _fsm is the free space map, and _vm is the visibility map; both maps get sections below. Unlogged tables add an empty _init fork that’s copied over them after a crash.

That’s why the honest answer to “how big is this table?” is rarely the main file alone:

select pg_size_pretty(pg_relation_size('events'))       as main,
       pg_size_pretty(pg_total_relation_size('events')) as total;

  main  | total
--------+--------
 275 MB | 318 MB

pg_relation_size is the main fork only. pg_table_size adds the other forks and the table’s TOAST storage (below), and pg_total_relation_size adds every index. Use the total.

Every page has the same layout

Every one of those 35,173 pages is laid out like this. It’s the most important picture in this book:

It works like a notebook page with a table of contents written from the top and the entries written from the bottom up.

A 24-byte page header records the page’s last change, a checksum, and where the free space starts and ends. Line pointers, 4 bytes each, grow from the front; each points at a tuple. Tuples grow from the back; a tuple is one version of a row, as stored on the page. The page is full when the two meet. (Indexes keep bookkeeping in the special space at the end; table pages leave it empty.)

select lsn, checksum, flags, lower, upper, special, pagesize
from page_header(get_raw_page('events', 0));

    lsn     | checksum | flags | lower | upper | special | pagesize
------------+----------+-------+-------+-------+---------+----------
 0/AF5BFA50 |    27634 |     4 |   248 |   384 |    8192 |     8192

select lab.prove(
  'lower = 24-byte header + 56 line pointers x 4 bytes = 248; upper = 384; 136 bytes free',
  lower = 24 + 4 * 56 and lower = 248 and upper = 384 and upper - lower = 136)
from page_header(get_raw_page('events', 0));

select lab.prove(
  '136 free bytes is too few for another events row (136 to 144 bytes, plus a line pointer)',
  (select upper - lower from page_header(get_raw_page('events', 0)))
    < (select min(lp_len) + 4 from heap_page_items(get_raw_page('events', 0))));

A line pointer lets a tuple move without changing its address

The line pointer lets Postgres move a tuple within its page, to compact free space, without changing its address. That address is the row’s ctid, a page number and a line pointer number, and every table has it as a hidden column:

select ctid, id, kind from events order by id limit 3;

 ctid  | id |  kind
-------+----+---------
 (0,1) |  1 | build
 (0,2) |  2 | deploy
 (0,3) |  3 | comment

(0,1) means page 0, line pointer 1. Indexes store ctids: when an index lookup finds id = 2, what it finds is “page 0, slot 2”. Following a ctid to its table page is the fourth read in the four-page lookup from A query is pages and round trips.

8 KB is a compile-time choice, and you should keep it

Eight kilobytes isn’t a law of nature. It’s a constant, BLCKSZ, fixed when Postgres is compiled (./configure --with-blocksize, a power of two from 1 to 32 KB), and every file in the data directory is written in it. A server built for 16 KB pages can’t open a data directory made with 8 KB ones, and pg_upgrade won’t cross between them. A running server tells you what it was built with, and no setting changes it:

show block_size;

 block_size
------------
 8192

Bigger pages sound like fewer pages to count, and for some work they are. A sequential scan makes fewer, larger reads; an index fits more keys per page, so the tree is a little shallower; and wide rows stay inline longer, because the threshold for compressing and moving big values (TOAST, below) scales with the page.

But Postgres reads and caches whole pages, and sometimes logs them whole, so everything else gets worse. At 32 KB, a lookup that wants one row pulls four times the bytes into memory. The same shared_buffers holds a quarter as many pages, so your hot rows share the cache with four times as many cold neighbors. And the first change to a page after a checkpoint logs the whole page, so each of those full-page images can be up to four times the size.

So we keep 8 KB, and so should you. Managed services don’t let you choose it anyway. Every lever in this book changes how many rows fit on a page without a custom build: column order, narrower types, fillfactor, TOAST, and the right index. Larger pages pay off mainly for scan-heavy analytics on a server you compile yourself, and that’s the workload where a column store wins anyway (Where Postgres loses).

select lab.prove('the page size is fixed when Postgres is built: block_size is 8192, and no setting can change it',
  current_setting('block_size') = '8192'
  and (select context from pg_settings where name = 'block_size') = 'internal');

select lab.prove('WAL blocks are a separate compile-time constant, also 8 KB here',
  current_setting('wal_block_size') = '8192'
  and (select context from pg_settings where name = 'wal_block_size') = 'internal');

A row costs more than its columns

Open a page and count what each row actually stores. heap_page_items decodes every tuple on a page:

select lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid, t_hoff
from heap_page_items(get_raw_page('events', 0))
limit 3;

 lp | lp_off | lp_len | t_xmin | t_xmax | t_ctid | t_hoff
----+--------+--------+--------+--------+--------+--------
  1 |   8048 |    144 |    766 |      0 | (0,1)  |     24
  2 |   7904 |    144 |    766 |      0 | (0,2)  |     24
  3 |   7760 |    144 |    766 |      0 | (0,3)  |     24

Tuple 1 is 144 bytes at offset 8048, ending exactly at the end of the page; tuple 2 sits right below it. Each starts with a 23-byte tuple header.

The header’s two most important fields are t_xmin, the transaction that inserted this version, and t_xmax, the one that deleted or locked it (0 if none). They’re the heart of Postgres’s concurrency control and get a chapter of their own (Updates are inserts). Here, every row was inserted by transaction 766, the seed, and none has been deleted. t_ctid is the tuple’s own address, or its newer version’s. t_hoff says the column values start at byte 24, after the header and a byte of alignment, in declaration order, end to end.

Twenty-four bytes of header and a 4-byte line pointer, on every row. A row of two integers is mostly overhead.

select raw_flags
from heap_page_items(get_raw_page('events', 0)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;

                        raw_flags
----------------------------------------------------------
 {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}

select substr(t_data, 1, 16)
from heap_page_items(get_raw_page('events', 0))
where lp = 1;

               substr
------------------------------------
 \x0100000000000000a903000000000000

select lab.prove(
  'pageinspect decodes t_infomask 2306 as HASVARWIDTH, XMIN_COMMITTED, XMAX_INVALID',
  t_infomask = 2306
  and raw_flags = '{HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}')
from heap_page_items(get_raw_page('events', 0)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;

select lab.prove(
  'the next 8 bytes are account_id: 0x03a9 = 937',
  substr(t_data, 9, 8) = '\xa903000000000000'::bytea
  and (select account_id from events where id = 1) = 937
  and x'03a9'::int = 937)
from heap_page_items(get_raw_page('events', 0)) where lp = 1;

NULLs cost one bit

A NULL takes no space in the data area. A row with any NULL carries a null bitmap in its header instead: one bit per column, 0 for NULL. In projects, an unarchived project has a NULL archived_at:

 lp |  t_bits  | lp_len
----+----------+--------
  1 | 11110100 |     72
  2 |          |     80
  3 | 11110100 |     72

Rows 1 and 3 aren’t archived: their fifth bit is 0, and they’re 8 bytes shorter than row 2, which stores its timestamp. One bit instead of eight bytes. (Up to 8 columns, the bitmap fits in a spare byte of the header. Past 8, a row with any NULL needs a longer bitmap, which can push the start of its data from byte 24 to byte 32.)

select lp, t_bits, lp_len
from heap_page_items(get_raw_page('projects', 0))
limit 3;

select lab.prove(
  'projects rows 1 and 3 (not archived) carry a null bitmap 11110100; row 2 (archived) has none',
  array_agg(coalesce(t_bits, '') order by lp) = '{11110100,"",11110100}')
from heap_page_items(get_raw_page('projects', 0)) where lp <= 3;

select lab.prove(
  'row 2 is archived and 8 bytes longer, because it stores the timestamp',
  (select lp_len from heap_page_items(get_raw_page('projects', 0)) where lp = 2)
    = (select lp_len from heap_page_items(get_raw_page('projects', 0)) where lp = 1) + 8
  and (select archived_at is not null from projects where ctid = '(0,2)')
  and (select archived_at is null from projects where ctid = '(0,1)'));

Column order is free space

Now the mystery from the start of the chapter. CPUs read a value fastest at an address that’s a multiple of its size, so Postgres lines values up that way: a bigint or timestamptz starts at a multiple of 8 bytes, an integer or date at a multiple of 4, a smallint at a multiple of 2, and a boolean anywhere. When an 8-byte column follows one that ended at an odd offset, Postgres pads the gap. Picture a parking lot where trucks may only park at every eighth line: let a motorbike take the first space, and the next seven go empty.

Here are the two tables from the start, column by column. pg_column_size says a padded row is 72 bytes and a packed one 52:

padded (72 bytes)              packed (52 bytes, 56 on the page)
offset  contents               offset  contents
   0    header (24)               0    header (24)
  24    is_active (1)            24    id (8)
  25    padding (7)              32    created_at (8)
  32    id (8)                   40    score (8)
  40    is_admin (1)             48    flags (2)
  41    padding (7)              50    is_active (1)
  48    created_at (8)           51    is_admin (1)
  56    flags (2)                52    end
  58    padding (6)
  64    score (8)
  72    end

It compounds: each tuple also starts at a multiple of 8 on the page (so packed’s 52 bytes occupy 56), and each needs a line pointer. That’s 107 rows per page against 136, and 9,346 pages against 7,353.

The rule that falls out:

1. Fixed-width 8-byte columns first: bigint, timestamptz, double precision.
2. Then 4-byte ones: integer, real, date.
3. Then 2-byte, then 1-byte: smallint, boolean.
4. Variable-length columns (text, jsonb, numeric) last. Short values need no alignment, so they pack tightly at the end.

create table padded (
  is_active  boolean,
  id         bigint,
  is_admin   boolean,
  created_at timestamptz,
  flags      smallint,
  score      bigint
);

create table packed (
  id         bigint,
  created_at timestamptz,
  score      bigint,
  flags      smallint,
  is_active  boolean,
  is_admin   boolean
);

insert into padded
  select true, g, false, now(), 1, g from generate_series(1, 1000000) g;
insert into packed
  select g, now(), g, 1, true, false from generate_series(1, 1000000) g;

padded: 8168 usable bytes / (72 + 4) per row  =  107 rows per page
packed: 8168 usable bytes / (56 + 4) per row  =  136 rows per page

select lab.prove(
  'a padded row is 72 bytes; the same columns packed take 52',
  (select pg_column_size(p.*) from padded p limit 1) = 72
  and (select pg_column_size(p.*) from packed p limit 1) = 52);

select lab.prove(
  'page 0 of padded holds 107 tuples, page 0 of packed holds 136',
  (select count(*) from heap_page_items(get_raw_page('padded', 0))) = 107
  and (select count(*) from heap_page_items(get_raw_page('packed', 0))) = 136
  and 8168 / (72 + 4) = 107 and 8168 / (56 + 4) = 136);

select lab.prove(
  '9,346 pages versus 7,353: padded is about 27% bigger',
  pg_relation_size('padded') / 8192 = 9346
  and pg_relation_size('packed') / 8192 = 7353
  and pg_relation_size('padded')::numeric / pg_relation_size('packed') between 1.26 and 1.28);

Large values are compressed, then moved out of the row

A row must fit in an 8 KB page, yet a text value can be up to 1 GB. TOAST (The Oversized-Attribute Storage Technique) squares the two. When a row about to be stored is larger than about 2 KB (2,032 bytes with 8 KB pages, so at least four rows fit on a page), Postgres works through its variable-length columns, largest first:

1. Compress the value in place.
2. If the row is still too big, move the value into the table’s own TOAST table, cut into chunks of just under 2 KB, leaving an 18-byte pointer behind.

In a test table, 100,000 repeated characters compressed to 1,156 bytes and never left the row. 32,000 characters of random hex didn’t compress, so they moved out in 17 chunks, leaving a 50-byte row behind. Reading that value back means an index lookup in the TOAST table and 17 more tuple fetches, invisible in your SQL.

What that means for your queries:

• select * isn’t free on wide tables. A list view that doesn’t show a document’s body shouldn’t fetch it, and most ORMs fetch every column unless told otherwise.
• Changing a toasted value rewrites all of it. Change one key in a big jsonb document and Postgres stores the whole document again. Keep a hot counter out of the document; better still, give it a narrow table of its own (Move hot counters to a narrow table).
• Medium values are the awkward case. A 1 to 2 KB value isn’t toasted and makes every row large, so only a handful fit per page.

create table docs (
  id   bigint generated always as identity primary key,
  body text
);
insert into docs (body) values (repeat('a', 1000));
insert into docs (body) values (repeat('a', 3000));
insert into docs (body) values (repeat('a', 100000));
insert into docs (body)
  select string_agg(md5(random()::text), '') from generate_series(1, 100);
insert into docs (body)
  select string_agg(md5(random()::text), '') from generate_series(1, 1000);

select id, length(body) as chars, pg_column_size(body) as stored,
       pg_column_compression(body) as compression
from docs order by id;

 id | chars  | stored | compression
----+--------+--------+-------------
  1 |   1000 |   1004 |
  2 |   3000 |     44 | pglz
  3 | 100000 |   1156 | pglz
  4 |   3200 |   3200 |
  5 |  32000 |  32000 |

 chunk_id | chunks | bytes | max_chunk
----------+--------+-------+-----------
    17446 |      2 |  3200 |      1996
    17447 |     17 | 32000 |      1996

create table toast_edge (body text);
insert into toast_edge values (repeat('a', 2000)), (repeat('a', 2010));
select lab.prove(
  'a 2,028-byte row is stored as is; a 2,038-byte one crosses 2,032 bytes and is compressed',
  (array_agg(lp_len order by lp))[1] = 2028
  and (array_agg(lp_len order by lp))[2] < 100
  and (select array_agg(pg_column_compression(body) is not null order by length(body))
       from toast_edge) = '{f,t}')
from heap_page_items(get_raw_page('toast_edge', 0));

select lab.prove(
  'random hex doesn''t compress, so rows 4 and 5 move out of line',
  bool_and(pg_column_compression(body) is null)
  and bool_and(pg_column_toast_chunk_id(body) is not null)
  and array_agg(pg_column_size(body) order by id) = '{3200,32000}')
from docs where id in (4, 5);

select reltoastrelid::regclass as toast_table from pg_class where relname = 'docs' \gset
select lab.prove(
  'out-of-line values are cut into chunks of at most 1,996 bytes: 2 chunks for row 4, 17 for row 5',
  (select array_agg(n order by n) from
     (select count(*) as n from :toast_table group by chunk_id) c) = '{2,17}'
  and (select max(length(chunk_data)) from :toast_table) = 1996);

alter table docs add column title text;
select pg_column_toast_chunk_id(body) as chunk_before from docs where id = 5 \gset
update docs set title = 'renamed' where id = 5;
select lab.prove(
  'an update that doesn''t change a toasted value keeps the same TOAST pointer',
  pg_column_toast_chunk_id(body) = :chunk_before)
from docs where id = 5;

update docs set body = body || 'x' where id = 5;
select lab.prove(
  'an update that changes a toasted value stores the whole new value, every chunk',
  pg_column_toast_chunk_id(body) <> :chunk_before
  and (select count(*) from :toast_table t
       where t.chunk_id = pg_column_toast_chunk_id(d.body)) = 17)
from docs d where id = 5;

alter table docs alter column body set compression lz4;
insert into docs (body) values (repeat('a', 100000));
select id, pg_column_size(body), pg_column_compression(body)
from docs where id = 6;