Review the notifications design

DecisionExpert5 min read25 min hands-on#data-modeling#operations#indexing

The idea in 30 seconds

A full design review: a short design document for a notifications feature, with numbers you measured in the practice page. The skill being practised is not finding every flaw but sorting them: block on the one-way doors that will be expensive to undo, comment on the two-way doors, and ask for the decision and its triggers to be written down.

Helps to know first: Database reliability engineering, Secure database engineering, Database migrations at scale, Database testing and correctness, Database bottlenecks and capacity planning (coming soon), Partitioning (coming soon)

The situation

You are asked to review this design before the team starts building. It is short, as most real ones are.

Review taskDesign: in-app notifications

Goal. Show users notifications (order shipped, refund issued, price drop) with an unread badge on every page.

Expected load. 40 million notifications a month. 2,000 page views a second at peak, each showing the badge. Most users get a few a week; marketplace sellers can get thousands a day.

Schema.

CREATE TABLE notifications (
  id         uuid PRIMARY KEY DEFAULT uuidv4(),
  user_id    bigint NOT NULL,
  kind       text NOT NULL,
  payload    jsonb NOT NULL,
  read_at    timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz                  -- soft delete
);
CREATE INDEX ON notifications (user_id);

Queries.

-- badge, every page view
SELECT count(*) FROM notifications WHERE user_id = $1 AND read_at IS NULL AND deleted_at IS NULL;
-- list, newest first
SELECT * FROM notifications WHERE user_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 20;

Retention. Keep everything; storage is cheap. If the table gets too big we will partition it later.

Would you approve this design? Write the review you would leave.

Your call

Model review

Request changes, on two points that are hard to undo later. Everything else can be fixed after launch and should not hold it up.

Blocking 1: “partition later” is not a plan. At 40 million rows a month and the 174 MB per million rows we measured for this exact row shape, the heap alone grows by about 7 GB a month, 84 GB a year, plus about 18 GB a year of primary key. “Later” means converting a table of hundreds of millions of rows, and PostgreSQL cannot turn an existing table into a partitioned one: it means building a new table and copying everything across while the feature is live. Decide the retention now. If notifications older than, say, 12 months can go, partition by month from day one, and retention becomes dropping the oldest partition, which is far faster than a DELETE and leaves no vacuum work behind. If they truly must be kept, say why, and where old ones will live.

Blocking 2: the primary key. uuidv4() scatters inserts across the whole primary key index. In the B-tree lab, a million random UUID keys made the index 26% larger than a million time-ordered ones. At this insert rate that is permanent extra size and cache pressure on the busiest index. PostgreSQL 18’s uuidv7() gives the same type with time-ordered values. Changing a primary key type later means rewriting the table and every reference to it; choosing a different generator now costs nothing. (If partitioning by month, the primary key must also include the partition key, which is a second reason to decide both together.)

Non-blocking: the badge index. With only (user_id), we measured the badge for an ordinary user reading 101 pages in 0.27 ms, and for a user with 50,000 notifications 1,160 pages in 5.8 ms, at 2,000 page views a second. A partial index ON notifications (user_id) WHERE read_at IS NULL AND deleted_at IS NULL read 6 and 10 pages, and was 0.9 MB instead of 7.1 MB. Easy to add later; better to add now.

Non-blocking: the list query. It filters on user_id and sorts by created_at; an index on (user_id, created_at DESC) serves it directly. For sellers with thousands of notifications, also decide how far back the list paginates.

Non-blocking: the badge’s failure mode. It runs on every page. If this table is slow or locked, every page is slow. Give the query a short statement timeout and show the page without a badge when it fails.

Ask: record the retention and key decisions in a decision record, with the trigger that would make you revisit them, such as “any user above 1 million notifications” or “writes above 5,000 a second”.

Compare it with yours. Did your review catch the main problems?

Sorting what you find

A design review produces a list. Its value depends on how you sort it.

Blocking: one-way doors that are wrong or undecided. Here, retention and partitioning, and the primary key. They cost little to fix now and a project to fix later.

Non-blocking but important: two-way doors with a clear better option. Indexes, timeouts and query shapes can all change in a normal deploy. Say what you would do, and let the author decide when.

Questions: things you could not judge from the document. “What is the largest seller’s notification count?” is often more useful than any comment.

Marking every comment as blocking is the mistake Amazon’s letter warns about in decision-making generally: it slows everything down, and makes the comments that really matter harder to see.

Check yourself

Why is “we will partition later” a blocking comment, while the missing partial index is not?
Show answer
An existing table cannot be converted into a partitioned one in place, so partitioning later means copying hundreds of millions of rows into a new table while the feature runs. An index can be added at any time with CREATE INDEX CONCURRENTLY.
I could explain this without looking
What does choosing uuidv7() instead of uuidv4() cost now, and what would the change cost in two years?
Show answer
Now, nothing: same type, same size, a different default. In two years, rewriting the table’s primary key and every reference to it, or living with a permanently larger, randomly written index.
I could explain this without looking
Turn “the badge query might be slow for big sellers” into a comment a team can act on.
Show answer
For example: “With only an index on user_id, the badge read 1,160 pages for a user with 50,000 notifications against 101 for a typical user; at 2,000 page views a second, please add a partial index on unread, undeleted rows, which read 10 pages in our test.”
I could explain this without looking

Sources

Where each claim on this page comes from.

2015 Letter to Shareholders

SecondaryJeff Bezos (Amazon.com, Inc.)· Invention Machine

Irreversible (one-way door) decisions deserve careful deliberation; reversible (two-way door) decisions should be made quickly.

PostgreSQL documentation: 5.12 Table Partitioning

Primary· §5.12.1 Overview; §5.12.2 Declarative Partitioning, including Limitations

Removing a partition is far faster than a bulk DELETE and avoids its vacuum overhead; a regular table cannot be turned into a partitioned table; a primary key on a partitioned table must include all partition key columns.

PostgreSQL documentation: 11.8 Partial Indexes

Primary· §11.8 Partial Indexes

A partial index holds only rows matching its predicate.

PostgreSQL documentation: 9.14 UUID Functions

Primary· §9.14 UUID Functions

uuidv4() is random; uuidv7() is time-ordered.

PostgreSQL documentation: E.4 Release 18

Primary· E.4.1 Overview

uuidv7() is new in PostgreSQL 18.

Documenting Architecture Decisions

SecondaryMichael Nygard· whole article

Recording decisions with context and all of their consequences.

The situation

Sorting what you find

Check yourself

Sources

Go further