You'll Query It More Than You Write It

Run this thought experiment on any table you own: take one row and tally its lifetime. It was written once. Maybe updated a handful of times. But read? It's been read by the application on every page load, by the nightly warehouse sync, by four dashboards, by a support engineer investigating a complaint, by you last Thursday with a WHERE id = and a furrowed brow. The ratio isn't close — over a table's life, reads outnumber writes by orders of magnitude, and the variety of readers is even more lopsided than the count.

Yet almost all schema design effort goes into the write path: what fields does the form collect, what does the API accept, how do we validate the insert. The questions people will ask of the data get answered later, ad hoc, at query time — often at incident time. I want to argue for flipping a portion of that effort: design for the reads, because the reads are the product.

Start from the questions, not the fields

When I design or review a new table, I write down — literally, in the PR description — the questions it will need to answer. Not just the application's queries. All three families of readers:

The application: "active subscriptions for this user," "latest invoice for this org." These usually get attention already.

The business: "how many trials converted last month?", "revenue by plan, by week." If the schema can't answer these without heroics, the heroics will be performed anyway — badly, at quarter-end, by someone with a deadline.

The debugger: "what happened to this order?", "what state was this account in on Tuesday?" This is the reader everyone forgets and everyone eventually is.

Then I check the schema against the list. Every question needs an access path: the columns to filter on exist, they're indexed for the pattern, and the answer doesn't require reconstructing history that was never recorded. A schema that stores the data but can't answer its own questions isn't done — it's half done, and the second half gets built during an outage.

Store facts you can't recompute — especially state and history

The most common read-path failure isn't a missing index. It's a missing fact — something the system knew at write time and didn't keep.

The classic is deriving state at read time. An order's status computed from a five-way join ("it has a shipment row but no delivery confirmation, unless refunded...") makes every reader — app, dashboard, and debugger alike — re-implement the same brittle inference, and they will disagree. Store the status explicitly; it's the single most-asked question about the row. Guard it with a CHECK constraint, and let the joins be the supporting evidence rather than the source of truth.

The subtler one is history. UPDATE is a destructive operation: the previous value is simply gone. For anything with a lifecycle — orders, subscriptions, applications — the questions are overwhelmingly historical ("when did it ship?", "who changed the plan, and from what?"), and a bare status column answers none of them. Lifecycle timestamps (shipped_at, canceled_at) are the cheap version; an append-only events or transitions table is the thorough one:

CREATE TABLE order_events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   bigint NOT NULL REFERENCES orders(id),
  event_type text   NOT NULL,   -- 'placed', 'paid', 'shipped', ...
  actor_id   bigint REFERENCES users(id),
  metadata   jsonb  NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);

Ten minutes to create. Then "what happened to order 48213?" — the sentence that opens half of all support investigations — becomes one indexed query instead of an archaeology dig through application logs that rotated out last month. Debuggability is a schema feature; you either design it in or you improvise it at 2am.

Read models: denormalize on purpose, not by accident

Normalization is the right default — one fact, one place, no update anomalies. But some questions are genuinely expensive to answer from normalized data on every request: "orders shipped per region per day," "each account's lifetime value." The answer isn't to denormalize the source tables; it's to add a read model — a separate, derived structure that exists only to be queried:

• Summary tables refreshed on a schedule or maintained incrementally (daily_order_stats(day, region, orders_count, revenue_cents)).
• Materialized views when the derivation is pure SQL and periodic refresh is acceptable.
• Cached columns (orders_count on customers) via counter caches or triggers — the small end of the same idea.

The discipline that keeps this honest is a bright line: normalized tables are the source of truth; read models are disposable derivations. You can drop a summary table and rebuild it from the sources. The moment writes start treating the read model as authoritative — or worse, the sources get bent to match a dashboard — you've traded correctness for speed, which is a trade you only notice when the numbers stop matching.

create_table :daily_order_stats do |t|
  t.date    :day,           null: false
  t.string  :region,        null: false
  t.integer :orders_count,  null: false, default: 0
  t.bigint  :revenue_cents, null: false, default: 0
  t.index [:day, :region], unique: true   # idempotent refresh, safe re-runs
end

A review-era note: AI assistants are curiously polarized here. Ask for a schema and you'll typically get textbook-normalized tables with no lifecycle timestamps, no events, no thought given to reporting — the write path, perfected. Ask for a fast version of something and you may get counter caches and duplicated columns sprinkled directly into the source tables, with no refresh story. Both are read-path review failures, and the reviewer's question is the same one this whole essay is about: "show me the queries this schema will serve." A design that can't name its readers isn't finished, whoever — or whatever — drafted it.

Questions to ask before the schema ships

• List the questions: application, business, debugging. Every one needs an access path — a column that exists, indexed for how it's asked.
• Store state explicitly; derive-at-read-time means every reader reinvents (and eventually disagrees about) the truth.
• Record lifecycle: _at timestamps at minimum, an append-only events table for anything support will ever ask about. UPDATE erases; readers remember.
• Denormalize into read models, never into sources. Truth lives in normalized tables; summaries are disposable and rebuildable.
• In review — of human or generated designs — ask for the queries, not just the columns. "How will we ask this data questions?" is the whole game.

You'll write each row once. Everything else the data ever does for you — every dashboard, every investigation, every decision — happens on the read path. Design like it.