How to review a database design

Review the workload, not the diagram

A schema diagram shows what is stored. A design review needs to know how it is used. Before looking at any table, ask for:

• The access patterns. The five or ten queries that will run most often, with how often each runs and how fast it must be. A design is good or bad only relative to these.
• The growth. Rows per day, how long they are kept, and the largest single customer, user or tenant. Averages hide the account that has 500 times the median.
• The consistency needs. Which reads must see the latest write, which numbers may be a few seconds stale, which rules must never be broken.
• The failure story. What happens when this table is unavailable for a minute, or its data is wrong. Who notices, and how.

Most design problems are visible from these four answers before a single column is named. A table that is perfectly normalized can still be wrong for a query that runs ten thousand times a second.

Put numbers on it before it exists

You can estimate the size of a PostgreSQL table from its columns, because the storage format is documented. A heap page is 8 kB. Each row costs a 4-byte pointer in the page, a header of 23 bytes on most machines (24 once a null bitmap is added and it is aligned), and then its column values, each aligned to its type.

For a notifications row with a uuid (16 bytes), a bigint (8), a short text kind (14), a small jsonb payload (about 94) and one timestamptz (8), with two nullable timestamps usually null:

A page holds 8,192 bytes minus its 24-byte header, so that predicts 47 rows per page: about 21,300 pages, or 166 MB, per million rows. In the practice page you will build a million of these rows: we measured 174 MB, 45 rows per page. The estimate took five minutes and came within 5%. Scale it to the expected growth and you know the disk, cache and backup cost of a design before anyone writes it.

Do the same for the hot queries: how many rows does each one read, and through which index? A query that reads one user’s 50 rows is fine; the same query for the user with 50,000 is a different design problem.

Spend scrutiny where reversal is expensive

Not every decision deserves the same care. Amazon’s 2015 shareholder letter split decisions into two types: one-way doors, consequential and hard or impossible to reverse, which deserve slow, careful deliberation; and two-way doors, which are reversible and should be made quickly by a small group. The failure mode it warns about is treating every decision as a one-way door.

For databases, the line falls in fairly predictable places:

The table is a heuristic, not a law: dropping an index is easy, but changing a primary key on a billion-row table referenced by twelve others is not. The useful question in a review is: if this turns out wrong in a year, what does undoing it cost? If the answer is “a migration this afternoon”, decide fast. If it is “a quarter of work and a maintenance window”, slow down.

Write the decision down

A review that ends with “looks good” leaves nothing for the next person. Record the decision in a short note, an architecture decision record: the context, the decision, its status, and its consequences, all of them, not only the good ones. Two additions make them much more useful for database decisions:

• The alternatives considered, and why each lost.
• What would change your mind: “if any user exceeds 100,000 notifications”, “if writes pass 5,000 a second”. That turns a decision into something the team can revisit on evidence instead of opinion.

Check yourself

1. Why ask for the access patterns before looking at the schema?

   Show answer

   A design can only be judged against how it will be used. The same tables can be right for one set of queries and badly wrong for another, and the most frequent queries decide which indexes, keys and denormalisation make sense.

   I could explain this without looking

2. Roughly how big will 100 million rows of about 175 bytes each be, before indexes?

   Show answer

   About 17–18 GB: 100 million times 175 bytes is 17.5 GB, plus a little free space per page.

   I could explain this without looking

3. Give one database decision that is usually a one-way door, and explain why.

   Show answer

   Partitioning a large table, because a regular table cannot be converted into a partitioned one in place and the data must be copied; or the primary key type, because every referencing table, index and external system depends on it.

   I could explain this without looking