Practice: estimate, then measure

Before you start

You need PostgreSQL 18 (for uuidv4(); on older versions use gen_random_uuid()) and about 500 MB free in a throwaway database. Have a calculator or a scratch file ready: write each estimate down before you run the query that checks it. That is the whole exercise.

Our measurements are from PostgreSQL 18.3 on an Apple M1 Pro laptop; page counts should match yours closely.

The design

A notifications feature. Each row:

CREATE TABLE notifications (
  id         uuid PRIMARY KEY DEFAULT uuidv4(),
  user_id    bigint NOT NULL,
  kind       text NOT NULL,          -- e.g. 'order_shipped'
  payload    jsonb NOT NULL,         -- e.g. {"order_id": 123456, "title": "Your order has shipped", "url": "/orders/123456"}
  read_at    timestamptz,            -- NULL until read
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz             -- NULL unless the user deletes it
);

The hot query is the unread badge, shown on every page:

SELECT count(*) FROM notifications
WHERE user_id = $1 AND read_at IS NULL AND deleted_at IS NULL;

1. Estimate the table

Measure the parts you are unsure of:

SELECT pg_column_size('{"order_id": 123456, "title": "Your order has shipped", "url": "/orders/123456"}'::jsonb) AS payload,
       pg_column_size(uuidv4()) AS uuid;

We got 94 bytes for the payload and 16 for the UUID. Now, on paper, add up one row: a 4-byte pointer, a 24-byte header (23 plus a null bitmap, aligned), and each non-null column, padding each bigint and timestamptz to a multiple of 8 bytes. Divide the 8,168 usable bytes of a page by your row size to get rows per page, then work out the size of a million rows.

2. Build it and check

A million rows: 10,000 ordinary users with about 95 notifications each, one heavy user (user_id = 1) with 50,000, and about 10% unread:

SELECT setseed(0.42);
INSERT INTO notifications (user_id, kind, payload, read_at, created_at)
SELECT CASE WHEN g <= 50000 THEN 1 ELSE 2 + g % 10000 END,
       'order_shipped',
       jsonb_build_object('order_id', 100000 + g % 900000, 'title', 'Your order has shipped',
                          'url', '/orders/' || (100000 + g % 900000)),
       CASE WHEN random() < 0.1 THEN NULL ELSE now() END,
       now() - g * interval '1 minute'
FROM generate_series(1, 1000000) g;
VACUUM ANALYZE notifications;

SELECT pg_size_pretty(pg_relation_size('notifications')) AS heap,
       pg_relation_size('notifications') / 8192 AS pages,
       pg_size_pretty(pg_relation_size('notifications_pkey')) AS primary_key;

The estimate was 5% low: a little free space is left on each page and alignment cost a few more bytes than we counted. Close enough to decide with.

3. Predict the badge query

For each index design, predict how many pages the badge query reads for user 500 (95 notifications, 9 unread) and for user 1 (50,000 notifications, 5,024 unread). Then run, twice each so the second run is from cache:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM notifications WHERE user_id = 500 AND read_at IS NULL AND deleted_at IS NULL;

The designs, applied one after the other:

-- (a) no index beyond the primary key: run the queries first
-- (b)
CREATE INDEX n_user ON notifications (user_id);
-- (c)
DROP INDEX n_user;
CREATE INDEX n_unread ON notifications (user_id) WHERE read_at IS NULL AND deleted_at IS NULL;
VACUUM ANALYZE notifications;

Add up shared hit and read on the top Buffers line. Ours, from the second and third runs:

Design (b) is fine for an ordinary user and reads 11 times as many pages for the heavy one, because it finds all 50,000 of that user’s rows and then checks each in the table for read_at. The partial index only contains unread, undeleted rows, so it holds about a tenth of the entries, is about an eighth of the size, and answers both users from the index alone.

4. Scale it to the design

The design document expects 40 million new notifications a month, kept forever. Using your measured numbers, fill in:

None of this is a measurement at that size; it is arithmetic from the measured million. That is exactly what a design review runs on, and it is enough to say that this design needs a decision about retention and about the badge index before it ships. The next page asks you to make that review.

Clean up

DROP TABLE notifications;

Check yourself

1. Your estimate was within a few percent of the measured size. Where did the difference come from?

   Show answer

   Pages are not filled perfectly: there is leftover free space on each page, and alignment padding between columns. Both add a few bytes per row that a quick estimate rounds away.

   I could explain this without looking

2. Why did the index on user_id read 1,160 pages for user 1 but only 101 for user 500?

   Show answer

   It finds every one of the user’s rows, read or not, then visits the table to check read_at and deleted_at. User 1 has 50,000 rows to find and check; user 500 has 95.

   I could explain this without looking

3. Why is the partial index both smaller and faster?

   Show answer

   It only contains rows that are unread and not deleted, about 10% of the table, and it holds everything the badge query needs, so the query is answered from a small index without visiting the table.

   I could explain this without looking