Schema design is the only design that lasts

Look at any system that has been running for a decade. The code has been rewritten, possibly twice. The framework changed. The language may have changed. The API has three versions.

The database schema is largely the same, with accretions.

That asymmetry is the most underappreciated fact in software architecture, and it should change how you allocate design effort.

why schemas are sticky#

Data outlives code. You can rewrite a service over a weekend. You cannot rewrite ten years of production data.

Migrations are risky and get riskier with volume. A schema change on a table with a hundred rows is instant. On a table with two billion rows it is a project with a rollback plan and a maintenance window you cannot get.

Everything couples to it. Reports, exports, downstream consumers, that one analytics job nobody owns, the integration a customer built against your read replica five years ago.

Bad shapes calcify into application logic. If a column means two things depending on another column, every piece of code that touches it encodes that rule. Fixing the schema means fixing all of them, and by then nobody knows where they all are.

the decisions that are expensive to reverse#

Identity. Integer, UUID, or something else. Integers are compact and leak information — an incrementing ID tells competitors your order volume. Random UUIDs destroy index locality on insert. UUIDv7 is time-ordered and mostly solves that.

Pick early, because changing a primary key type across a live system with foreign keys is one of the genuinely hardest migrations there is.

Cardinality. "A user has one address" is a decision you will regret. Almost every one-to-one relationship eventually becomes one-to-many: one email, one phone, one payment method, one team.

The cost of modeling it as one-to-many from the start is a join. The cost of changing it later is a migration plus every query plus every piece of application logic that assumed singularity.

Nullability. A nullable column means every consumer must handle null forever. Make columns NOT NULL with a default unless the absence of a value is genuinely meaningful — and if it is, ask whether "unknown" and "not applicable" are the same thing, because they usually are not and you have just conflated them.

Enumerated values in the schema versus a lookup table. A native enum type is fast and adding a value is a migration. A lookup table is flexible and costs a join. For a set that changes rarely (order status), enum. For a set that business users will want to edit (categories), table.

Soft delete. deleted_at IS NULL on every query, forever, and one place that forgets it is a data leak. Sometimes required by regulation. Often adopted by default without thinking, and then it is permanent.

If you need it, consider a separate archive table instead — moving the row rather than flagging it. That keeps the hot table clean and makes the "forgot the filter" bug impossible.

Timestamps. Always store UTC. Always store the timezone separately if local time matters semantically ("the meeting is at 9 a.m. in Berlin" is not the same fact as an instant). Always timestamptz, never timestamp, in Postgres.

the practices that make it survivable#

Constraints in the database, not just the application. A CHECK constraint, a foreign key, a UNIQUE index. Application-level validation is bypassed by the migration script, the admin tool, the data fix someone ran by hand at 2 a.m., and the second service somebody wrote.

The database is the only place a rule is actually enforced.

Expand-contract for every change. Add nullable, backfill, dual-write, switch reads, remove old. Four deploys, every intermediate state reversible. This is not optional discipline; it is the only way to change a schema without a maintenance window.

Never reuse a column for a new meaning. The old data is still in there with the old meaning. Add a column.

Name things fully. status is not a name. subscription_status is. Abbreviations that were obvious to you in 2019 are not obvious to anyone in 2029.

Comment the schema. Postgres has COMMENT ON COLUMN. Almost nobody uses it. The place to record what a column means is on the column.

the exercise worth doing#

Take your main table. For each column, ask:

1. What does it mean, precisely?
2. Can it be null, and what does null mean?
3. Who writes it, and is there more than one writer?
4. What enforces its validity?
5. If we needed two of these, what would break?

You will find at least one column that means different things in different rows. That column is going to be the source of an incident, eventually, and it was a design decision someone made in ten minutes six years ago.

Spend the extra day on the schema. It is the only artifact you are going to be living with.