# Schemas That Survive Their Second Year

By Antoine Debes, Principal Software Engineer · 2026-10-13

> The database decisions I make early so the schema still fits after the product has changed twice: model the business, let constraints do the policing, migrate without downtime, and give indexes a budget.

A schema's first year is easy. It is whatever the first features needed, and the first features were simple. The second year is where it meets reality: the product has pivoted once, two teams write to the same tables, and the report that mattered last quarter is a query nobody dares to run during the day.

The cost of a bad schema is rarely the rewrite. It is every feature after it that ships slower because a table has become something people route around. These are the decisions I make early to avoid that, in the order I make them.

## Model the business, not the screen

The first schema is usually a mirror of the first screen. That works until the second screen. I model the nouns the business actually uses, with their lifecycles, and let screens be views over them.

Lifecycles deserve particular care. A row that is "pending, then approved, then paid" wants a status column with a small, closed set of values, and a history table that records each transition with who and when. Three boolean columns called `is_approved`, `is_paid` and `is_cancelled` look simpler and are not: they allow states that cannot happen, and they lose the order in which things happened. The history table is the part people thank you for in year two, when the question is no longer "what is the state" but "how did it get there".

Derived data stays derived. A total that can be computed from its lines is computed, or materialised with a clear owner and a refresh rule. Two sources of truth are one source of arguments.

## Constraints are documentation that runs

Every rule I can express in the database, I express in the database: `NOT NULL`, foreign keys, unique constraints, check constraints on the closed sets. The application enforces the same rules, but the application changes weekly and has more than one writer. A rule that lives only in code decays the first time a batch job or a support script skips it.

The test I apply: if this invariant were violated, would someone lose money or trust? If yes, it is a constraint, not a comment.

## Migrate without downtime, every time

The migration process is set before the first migration, because the habits form immediately. The pattern is expand and contract. Add the new column or table; write to both old and new; backfill in batches small enough not to lock anything; switch reads behind a flag; then, and only then, drop the old. A rename is an add followed by a move. A rollback is a new migration forward, never a restore.

Backfills get their own attention. They run in batches, they are idempotent, they can be stopped and resumed, and they log their progress. A backfill that has to finish in one transaction is a bet against the size of the table a year from now.

## Give indexes a budget

Every index is paid for on every write, and a table with nine indexes is a table where inserts are slow and nobody knows which four indexes matter. I add indexes from query plans, not from intuition, and I review the slow-query log the way other people review pull requests: on a schedule, with a budget, with the right to delete.

Composite indexes follow the query: equality columns first, then the range, then whatever the query sorts by. Partial indexes cover the small, hot subset (the open orders, the unprocessed events) instead of the whole history. The p95 of the top ten queries is a number the team sees every week, and it is the number that decides whether we add an index or fix the query.

## Keys that stay boring

Surrogate keys are generated by the application and sort by time: ULIDs or UUIDv7, so new rows land at the end of the index instead of all over it. Natural keys, when they exist, become unique constraints, not primary keys, because natural keys change and foreign keys should not. Sequential integers never leave the system; an id in a URL that can be counted is a report nobody asked for.

## Caches and queues are not schema repairs

Redis earns its place for sessions, rate limits and the handful of reads that are hot enough to matter. It does not fix a model that is wrong. A cache in front of a bad schema hides the problem exactly until the day the cache misses, which is the day that matters. Queues move work out of the request, and they are the right place for anything that touches more than one aggregate, but they are an execution choice, not a modelling one.

## When relational is the wrong answer

Most of the time it is the right one. The exceptions are real: documents whose shape genuinely varies per customer, time series at a volume where row-per-sample stops making sense, search that needs ranking and tolerance. Each of those gets the store built for it, beside the relational core and fed from it, never instead of it. One system of record, several views.

## The ritual

What keeps a schema alive is not the design review at the start; it is a short, monthly look at four things: the slow-query log, the fastest-growing tables, the constraints that are missing, and the migrations that are still half done. Thirty minutes, the whole team, with the authority to change things. The schema is a product. It gets maintained like one.