Database Design for Scale: Five Schema Decisions That Outlive Your Application

The short answer

Your application code will be rewritten. Your framework will be replaced. Your infrastructure will migrate at least once.

Your schema will still be there, because it is where the data lives, and data outlives every layer built on top of it. That asymmetry is why schema decisions deserve more deliberation than they usually get and receive less.

Five decisions in particular are cheap before launch and progressively brutal afterwards:

1. Primary key strategy — sequential integers, UUIDs, or something in between

2. How you store time — the type, the zone, and whether you record when things happened or when you recordedthem

3. Mutable state or recorded history — whether a row is overwritten or a new fact is appended

4. The normalisation boundary — where you stop normalising and reach for JSON

5. Money, precision and units — the type you store amounts in, and whether the unit is explicit

Each is a five-minute conversation at the whiteboard. Each is a multi-week migration with production data and downtime once you have a few hundred million rows.

Why schema outlives everything else

Three properties make schema changes structurally harder than application changes, and they compound.

Data has inertia. Changing a function affects future behaviour. Changing a column type means rewriting every row that already exists — and on a large table that is a locking operation, a long one, or both.

The schema has many consumers. Not just your application. Reporting queries, the analytics pipeline, the data warehouse, the BI dashboard someone in finance built, the integration a customer wrote against your read replica. A column rename that takes ten seconds in code breaks four systems you did not know existed.

Correctness bugs are silent and retroactive. If you store money as a floating-point number, nothing fails. Rounding errors accumulate quietly, and three years later a reconciliation is out by a few thousand and nobody can say when it started. The bug is in every row you have ever written.

This is why the reversibility test from our multi-tenant architecture guide applies with even more force here. Does undoing this require touching existing rows? For all five decisions below, the answer is yes.

Decision 1 — Primary key strategy

The first column you write and the one hardest to change.

The options

Sequential integers (BIGSERIAL, AUTO_INCREMENT). Small, fast to index, human-readable, naturally ordered by insertion.

UUIDv4. 128-bit random. Globally unique, generatable client-side, no coordination needed.

UUIDv7 or ULID. Time-ordered 128-bit identifiers. Unique like UUIDv4, but the leading bits encode a timestamp so they sort chronologically.

How they compare where it matters

The one that surprises teams

UUIDv4 index fragmentation. Because the values are random, every insert lands at a random point in the B-tree index. On a large, high-write table this causes page splits, index bloat and progressively worse write performance — and it degrades gradually rather than failing, so it is usually diagnosed late.

Sequential keys append to the right-hand edge of the index. So do UUIDv7 and ULID, which is precisely why they exist.

What to actually choose

Default to UUIDv7 or ULID for anything customer-facing. You get the write locality of sequential integers plus global uniqueness, client-side generation and safe public exposure. The 8 extra bytes per key are not the problem people fear at typical scale.

Sequential integers are fine for internal-only tables with no external exposure and no multi-database merge in your future — reference data, lookup tables, join tables.

Avoid UUIDv4 as a primary key on high-write tables. Use it for external identifiers if you need unguessability without ordering, but keep the clustered key ordered.

Never expose sequential IDs publicly. /invoices/1041 tells a competitor you have issued roughly a thousand invoices, and lets anyone enumerate your data by incrementing. If you have sequential internal keys, add a separate public identifier.

The multi-tenant interaction

If you are building multi-tenant, this compounds with your isolation model. Sequential keys collide when merging tenant databases or promoting a tenant from a shared pool to a dedicated database — exactly the tier migration described in our multi-tenant guide. UUIDs make that migration a copy operation. Sequential keys make it a remapping project touching every foreign key in the system.

Decision 2 — How you store time

Time is the most consistently mishandled data type in software, and the errors are subtle enough to survive years in production.

Three separate questions

What type? TIMESTAMPTZ in PostgreSQL, or the equivalent that stores an absolute instant. Not TIMESTAMP without a zone, which stores a wall-clock reading with no anchor and is unrecoverable once written.

Which zone? Store UTC. Always. Convert at the presentation layer using the user's zone. Storing local time means every daylight-saving transition creates an hour that is either ambiguous or nonexistent.

Which time? This is the one that gets missed. There are at least two timestamps for most facts:

• Valid time — when the thing actually happened

• Transaction time — when your system recorded it

For a payment received on the 3rd but imported on the 5th, those differ. If you store only one, you cannot answer both "what happened in March" and "what did our books say on 31 March". Financial, compliance and reporting contexts need both, and adding the second one later means you have no history for it.

The exceptions to UTC

Future events with a local meaning. A meeting scheduled for "9am on 15 March in London" is not an instant — it is a wall-clock time in a zone, and if the UK changes its DST rules between now and then, the correct instant changes. Store the local time plus the zone identifier (Europe/London), not the resolved UTC instant.

Dates without times. A birth date is a DATE, not a timestamp. Storing it as midnight UTC means it shifts a day for anyone west of Greenwich.

The precision trap

Different engines default to different precision — seconds, milliseconds, microseconds. If your application generates timestamps at millisecond precision and the column truncates to seconds, ordering by timestamp becomes non-deterministic for events in the same second. On an event log or audit trail, that means you cannot reconstruct the true sequence.

Specify precision explicitly, and if ordering matters, add a monotonic sequence column rather than relying on timestamps alone.

Decision 3 — Mutable state or recorded history

Does a row get overwritten, or does a change append a new fact?

The two models

Mutable (current state). One row per entity, updated in place. UPDATE orders SET status = 'shipped'. Simple, compact, fast to query.

Append-only (history). Changes insert new rows. Current state is derived — the latest row, or a projection built from the sequence. The full event stream is preserved.

What the mutable model silently discards

Every UPDATE destroys information that cannot be recovered:

• When the change happened

• What the previous value was

• Who made the change

• Whether the value oscillated between states

That is invisible until someone asks a question that needs it. Disputed transaction, compliance audit, a customer claiming they never changed a setting, a bug where you need to know what a value was last Tuesday. At that point the data does not exist, and no amount of engineering recovers it.

What to actually do

Full event sourcing is a heavy commitment and wrong for most products. But the choice is not binary.

Practical middle ground, in increasing order of cost:

Audit columns everywhere. created_at, updated_at, created_by, updated_by on every table. Cheap, and it answers a surprising share of questions.

Soft deletes on core entities. deleted_at rather than DELETE, with a retention window before hard deletion. Essential for the per-tenant restore capability discussed in the multi-tenant guide.

History tables for entities where change matters. A orders_history table written by trigger or application code, capturing the previous row on every update. Costs storage, answers almost every historical question.

Append-only for genuinely event-shaped domains. Financial ledgers, inventory movements, state machines with regulatory significance. A ledger should never be updated — a correction is a new compensating entry, which is how accounting has worked for six hundred years and for good reason.

The rule worth adopting

Anything a regulator, auditor, or angry customer might ask about should be append-only. Everything else can be mutable with audit columns.

For financial services and healthcare work this is not optional — the audit trail requirement is usually explicit in the regulation, and retrofitting one means you have no history for the period before you added it.

Decision 4 — The normalisation boundary

Where do you stop modelling relationally and reach for JSON?

The genuine case for JSON columns

JSONB in PostgreSQL and its equivalents are excellent for a narrow set of problems:

• Tenant-defined custom fields, where the shape is defined by the customer at runtime

• Third-party API payloads stored verbatim for audit or reprocessing

• Sparse attributes where 90 percent of rows would have NULL in 40 columns

• Configuration and settings blobs read as a unit

The case against, which is stronger than most teams assume

The database cannot enforce anything. No type checking, no foreign keys, no NOT NULL. Every guarantee moves to application code, and application code changes more often than schemas.

Querying is worse. You can index JSONB paths, but the planner has poorer statistics and query plans are less predictable. A join against a JSON field is rarely as fast as a join against a column.

Schema drift is invisible. Three years in, that JSON column contains six different shapes written by three different application versions, and nothing told you. Every reader now needs defensive code for all six.

Analytics teams will hate you. Every downstream consumer must parse the same JSON independently, and each of them will parse it slightly differently.

The rule

If you know the field at design time, make it a column. JSON is for structure you genuinely cannot know in advance — not for moving faster now at the cost of a schema you cannot reason about later.

The honest failure mode: JSON columns get chosen because adding a column requires a migration and adding a JSON key does not. That is a migration tooling problem being solved with a data modelling decision, and it is a bad trade.

When JSON is the right answer

Multi-tenant custom fields are the clearest case. Tenants define their own fields at runtime; you cannot have a column per tenant per field. The pattern that works: a custom_fields JSONB column paired with a per-tenant field definition tabledescribing the expected types, validated at write time by the application. You get flexibility with a schema you can still reason about, and analytics has something authoritative to read.

Decision 5 — Money, precision and units

The narrowest of the five and the one with the worst failure mode, because it fails silently and retroactively.

Never store money as a float

FLOAT and DOUBLE are binary floating-point types. They cannot represent 0.1 exactly, in the same way decimal cannot represent one-third exactly. Errors are individually tiny and they accumulate.

The consequence is not an exception. It is a reconciliation that is out by a small amount, discovered months later, with no way to determine which transactions were affected — because every one of them was, slightly.

Use DECIMAL/NUMERIC with explicit precision and scale, or store integer minor units (cents, paise) with the currency recorded alongside.

Integer minor units are the approach most payment systems use, and it has a real advantage: there is no ambiguity about rounding because there are no fractional units to round. The cost is that every read and write needs conversion, and a currency with three decimal places — the Kuwaiti dinar, among others — breaks a hard-coded assumption of two.

Currency must be stored with the amount

An amount column without a currency column is a bug waiting for your first international customer. Store them together, always, and never allow arithmetic across currencies without an explicit conversion carrying its own rate and timestamp.

Store the exchange rate used, not just the converted result. Someone will need to explain a historical figure, and "we converted at the rate that day" is not an answer without the rate.

The same logic applies to every measured quantity

Weight, distance, volume, duration, temperature. Store the value and the unit, or store canonically in one unit and document it in the column name — duration_seconds, weight_grams, distance_metres.

The failure mode is identical to money: a number without a unit is an assumption, and assumptions diverge between the three developers who touched the code.

Precision beyond money

Latitude and longitude need at least DECIMAL(9,6). Truncating to four decimal places moves a point by roughly 11 metres, which matters for anything routing-related.

Percentages — decide whether 0.15 or 15 means fifteen percent, put it in the column name, and never mix the conventions in one schema.

Migrations: the discipline that makes the rest possible

None of the above matters if you cannot safely change the schema, and migration discipline is what separates teams that evolve their data model from teams that avoid touching it.

The rules that prevent outages

Every migration is reversible, or explicitly documented as not. A migration you cannot roll back is a deployment you cannot roll back.

Additive first, destructive later. Adding a column is safe. Dropping one breaks any deployed code still selecting it. The safe sequence for a rename is: add the new column, write to both, backfill, switch reads, stop writing the old one, drop it — five deployments, no downtime.

Backfills run outside the migration. A migration that updates fifty million rows will lock the table and time out the deployment. Backfill in batches from a background job, with progress tracking and the ability to resume.

Never take a long lock on a large table. ALTER TABLE behaviour varies enormously by engine and version. Know what your specific version does before running it in production — some operations that are instant in one version rewrite the entire table in another.

Migrations are tested against production-shaped data. A migration that runs in two seconds against a hundred test rows may run for forty minutes against a hundred million.

The multi-location complication

If you run any form of per-tenant database isolation, every migration must apply to every location, in a controlled sequence, with a record of which have completed. Schema drift between tenant databases is one of the most expensive states a SaaS platform can reach — and it is what turns tier migration from a data copy into bespoke engineering, as covered in the multi-tenant architecture guide.

What actually breaks at scale

Ordered by how commonly we see it, not by severity.

The unbounded table. Events, logs, audit records, notifications. Growing indefinitely with no partitioning or retention policy. At a few hundred million rows, queries that were instant become minutes, and adding an index requires a maintenance window. Decide the retention policy when you create the table, not when it becomes a problem.

The missing composite index. A query filtering on three columns with three separate single-column indexes. The planner picks one and filters the rest in memory. Composite indexes matter more than most teams realise, and column order within them matters more still — equality columns first, range columns last.

The N+1 that only appears in production. Fine with ten test rows, fatal with ten thousand. ORMs make this easy to write and hard to see. Log query counts per request in development; a request issuing four hundred queries should fail CI.

The count query. SELECT COUNT(*) on a large table with a filter is a full scan in most engines. Pagination UIs that show a total page count are a common cause of slow endpoints. Use cursor pagination, or an approximate count.

Connection pool exhaustion. Not strictly schema, but it is where scale problems surface first. Every serverless function opening its own connection will exhaust a database that handled ten times the traffic from a pooled application.

The report against production. Analytics queries competing with customer traffic. Move them to a replica or a warehouse before they cause an incident rather than after.

Indexing decisions that compound

Indexes are more reversible than schema, but the decisions still accumulate.

Index for the queries you run, not the columns you have. A column that is never filtered, sorted or joined on does not need an index. Every index slows writes and consumes storage.

Composite index column order is not arbitrary. An index on (tenant_id, status, created_at) serves queries filtering on tenant_id alone, or tenant_id + status, or all three. It does not efficiently serve a query filtering only on status. Leftmost prefix rule — know it before designing indexes.

In multi-tenant systems, tenant_id usually belongs first in almost every composite index, because almost every query filters on it.

Partial indexes are underused. If 95 percent of your rows are status = 'completed' and you constantly query for the other 5 percent, a partial index on the active subset is dramatically smaller and faster.

Unused indexes are pure cost. Most engines expose index usage statistics. Review them quarterly and drop what nothing reads.

Failure patterns

Money as float. Silent, retroactive, and it affects every row you have written.

TIMESTAMP without a zone. Unrecoverable once written. You cannot determine what instant a wall-clock reading referred to.

UUIDv4 primary keys on high-write tables. Index fragmentation that degrades gradually and gets diagnosed late.

JSON as a migration shortcut. Choosing a JSON column because adding a real one requires a migration. The tooling problem should be fixed rather than routed around.

Deleting instead of soft-deleting core entities. No restore, no audit trail, no answer when a customer asks what happened.

Enum types in the database. Changing a database enum requires a migration in most engines, and some do not support removing values at all. A reference table with a foreign key is more flexible and barely slower.

Nullable columns that should not be. NULL means "unknown" and it propagates through every comparison. If a value is always required, enforce it — retrofitting NOT NULL requires cleaning every existing row first.

One schema for OLTP and analytics. Operational tables optimised for writes, queried by analysts running full scans. The two workloads have opposing requirements and should not share infrastructure.

A pre-build checklist

Before the first table is created:

• Primary key strategy chosen, with a stated reason

• Public identifiers separate from internal keys if internal keys are sequential

• All timestamps TIMESTAMPTZ, stored UTC, with precision specified

• Future scheduled events store local time plus zone identifier, not a resolved instant

• Valid time and transaction time separated wherever they can differ

• Audit columns on every table — created, updated, by whom

• Soft deletes on core entities with a defined retention window

• Append-only history for anything auditable or regulated

• Money as DECIMAL or integer minor units, never float, always with currency

• Every measured quantity carries its unit, in the value or the column name

• JSON used only for genuinely unknowable structure, with a definition table alongside

• Retention and partitioning policy decided for every append-heavy table

• Migration tooling supports batched backfills and multi-location application

• Composite index column order matches actual query patterns

• Analytics reads separated from the operational database

Fifteen items. Most take under a minute to decide, and each one is a multi-week migration if deferred past a few hundred million rows.

Frequently asked questions

Should I use UUID or auto-increment for primary keys?

Use UUIDv7 or ULID for anything customer-facing — they give global uniqueness, client-side generation and safe public exposure while retaining the index write locality that UUIDv4 loses. Sequential integers remain fine for internal-only tables with no external exposure and no future multi-database merge.

Why is UUIDv4 bad for primary keys?

Because the values are random, every insert lands at a random point in the B-tree index, causing page splits and index bloat on high-write tables. Write performance degrades gradually rather than failing, so it is usually diagnosed late. UUIDv7 and ULID solve this by encoding a timestamp in the leading bits so values sort chronologically.

How should I store dates and times in a database?

Use a timestamp type that stores an absolute instant, such as TIMESTAMPTZ, always in UTC, converting to local time at the presentation layer. Two exceptions: future scheduled events should store local time plus a zone identifier, since DST rules can change; and dates without times should use a DATE type rather than a midnight timestamp.

Should I use soft deletes?

Yes for core entities, with a defined retention window before hard deletion. Soft deletes preserve the audit trail, enable per-customer restore, and answer questions that a hard delete makes permanently unanswerable. Reference and lookup data generally does not need them.

When should I use a JSON column instead of normal columns?

Only when the structure is genuinely unknowable at design time — tenant-defined custom fields, verbatim third-party API payloads, or sparse attributes where most rows would be NULL. If you know the field at design time, make it a column: the database can then enforce types, constraints and foreign keys that JSON cannot.

How should I store money in a database?

Use DECIMAL/NUMERIC with explicit precision and scale, or integer minor units such as cents. Never use FLOAT or DOUBLE — binary floating point cannot represent decimal fractions exactly, and the errors accumulate silently across every row. Always store the currency alongside the amount.

What is the difference between valid time and transaction time?

Valid time is when something actually happened; transaction time is when your system recorded it. A payment received on the 3rd but imported on the 5th has different values for each. Storing only one means you cannot answer both "what happened in March" and "what did our records show on 31 March" — which financial and compliance reporting both require.

How do I run a schema migration without downtime?

Use an additive sequence. To rename a column: add the new one, write to both, backfill in batches from a background job, switch reads, stop writing the old one, then drop it. Five deployments, no downtime. Never run large backfills inside the migration itself, since it will lock the table and time out the deployment.

Should analytics queries run against my production database?

No. Operational tables are optimised for writes and point lookups; analytics runs full scans and aggregations. The two workloads have opposing requirements, and reporting queries competing with customer traffic is a leading cause of performance incidents. Use a read replica or a warehouse.

What is the leftmost prefix rule for indexes?

A composite index on (a, b, c) can efficiently serve queries filtering on a, or a + b, or all three — but not queries filtering only on b or c. Column order therefore has to match actual query patterns, with equality-filtered columns first and range-filtered columns last.

Should I use database enum types?

Generally no. Changing a database enum requires a migration in most engines, and some do not support removing values at all. A reference table with a foreign key gives the same integrity guarantee, is far easier to change, and lets you attach additional attributes such as display labels or sort order later.

How do I decide a retention policy for event tables?

Decide it when you create the table, not when it becomes a problem. Determine how far back queries genuinely need to reach, partition by time so old partitions can be dropped cheaply, and archive rather than delete if there is any compliance requirement. Unbounded append-only tables are the most common scale failure in production systems.

Conclusion

Schema decisions get made quickly because they feel like implementation detail. They are not. They are the longest-lived commitments in the system, and they are the only ones where a mistake is retroactive — affecting not just future behaviour but every row already written.

The five above are worth an hour of deliberate discussion before the first table exists: how you identify rows, how you record time, whether you keep history, where you stop normalising, and how you represent quantities. None is difficult. All are expensive to revisit.

The same test applies as in the previous two articles in this series: does reversing this require touching existing data? For schema, the answer is almost always yes — which is exactly why it deserves the deliberation that application decisions do not.

How Akoode approaches this

We run a schema review before any build starts, and it is a fixed part of our discovery phase rather than something that happens if there is time. The five decisions above are on the agenda every time, along with the retention policy for every append-heavy table.

Across 180+ software products delivered in 15+ industries, the systems that scaled without a rewrite shared a small set of traits: ordered identifiers, UTC timestamps with explicit precision, history preserved where it mattered, money in exact types, and migration tooling that could backfill safely.

Our enterprise HRMS platform is a case where decision three drove everything. Payroll requires an audit trail by regulation, so the ledger is append-only and corrections are compensating entries rather than updates. That decision was made in week one, and it is why the platform can answer what any employee's entitlement was on any past date — a question that a mutable schema simply cannot answer.

Our AI quantity takeoff platform is a case for decision four. Construction drawings arrive with wildly inconsistent metadata, so extracted attributes are stored as JSON alongside a definition table — flexibility where the structure is genuinely unknowable, columns everywhere else.

We are rated 4.9 out of 5 from 110 Google reviews and 5.0 on GoodFirms.

If you are designing a data model that needs to last, book a call with me directly. Bring your entity list and your expected growth rate — that is usually enough to identify which of the five decisions matters most for your case.

You can also read how we structure custom software development engagements and enterprise application builds, or post your requirement for a response within one business day.