A schema that hums with ten thousand rows can fall over at ten million, and by then the fix is a migration under load rather than a line in a review. Most database performance work is not clever query rewriting; it is having modelled the data, the access patterns and the indexes correctly before the table filled up.
Index for the queries you actually run
An index is a bet that a particular query shape will be hot. Read your slow query log, look at the WHERE and ORDER BY clauses that appear together, and build composite indexes whose column order matches them. A covering index that answers a query from the index alone avoids the heap fetch entirely, and a partial index over just the active rows stays small and fast.
- Put the most selective, equality-filtered column first in a composite index.
- Use partial indexes to skip soft-deleted or archived rows the hot path never touches.
- Delete unused indexes — each one slows writes and bloats the table.
- Read EXPLAIN ANALYZE on real data volumes, not an empty dev database.
Let the database enforce what the database can enforce
Foreign keys, NOT NULL, unique and check constraints are not bureaucracy — they are the last line that keeps corrupt data out when an application bug slips through. Enforcing invariants in the schema means every writer obeys them, including the one-off script someone runs at midnight. Application-level validation is a convenience; the constraint is the guarantee.
Normalise until it hurts, then denormalise on purpose
Start normalised so there is one source of truth for every fact, and reach for denormalisation only when a measured read path demands it. When you do denormalise — a cached count, a materialised view, a duplicated column — write down how it stays consistent and what refreshes it. Undocumented denormalisation is how two numbers that should match slowly stop matching.
- Default to a normalised model; treat every duplicated value as a consistency liability.
- Use materialised views for expensive aggregates, with a clear refresh strategy.
- Reach for JSONB for genuinely variable attributes, not as an excuse to avoid modelling.