Scaling problems are rarely about the database engine. They’re about decisions made in the first week of a project — schema shapes, key choices and access patterns — that were invisible at a thousand rows and painful at ten million. Here’s how we design data models that grow with the product instead of against it.
Model the access patterns, not just the entities
A schema that looks tidy on a whiteboard can still be slow in practice if it fights the queries the app actually runs. We start from the read and write patterns — what gets fetched, how often, filtered by what — and design tables and indexes to serve them. Normalise for correctness, then denormalise deliberately where a hot path demands it.
Choices that quietly decide your future
- Primary keys. Sequential integers leak information and hurt distribution; random UUIDs bloat indexes. We usually reach for time-ordered identifiers that stay both compact and unpredictable.
- Indexes. The right composite index turns a full table scan into a lookup. The wrong one — or ten redundant ones — slows every write. Index for your real queries, and measure.
- Money and time. Store money in integer minor units, timestamps in UTC. These are the bugs nobody notices until an audit.
Plan for change
Growth means migrations, and migrations on a live table with millions of rows are their own discipline: additive changes first, backfills in batches, and no locking operation that takes the app down at peak. Building that muscle early means later scaling is a routine tweak, not an emergency rewrite.
Measure before you optimise
Slow-query logs and EXPLAIN plans tell you where time actually goes. We optimise the queries that hurt, not the ones we assume are slow. Most “we need a bigger database” moments are really “we need one more index” moments.
Get the schema right early and scaling is boring — which is exactly what you want it to be.
