Database Architecture Decisions That Make or Break Scale

Sharding is the most over-prescribed remedy in database architecture. Teams reach for it because it's the most talked-about solution to 'the database is slow,' and it's usually the wrong first move — it adds enormous operational complexity and forecloses a lot of query flexibility, in exchange for solving a scaling problem that a well-chosen index or a read replica would have solved for a tenth of the cost.

Model Access Patterns Before You Model Entities

Classic entity-relationship modeling asks 'what are the things in my domain and how do they relate?' That's necessary but insufficient. The question that actually predicts scaling pain is 'what are the ten queries this system will run a million times a day, and does the schema make every one of them cheap?' We map hot-path queries before finalizing a schema, every time, even when it means a less 'normalized-looking' data model.

Denormalization Is a Tool, Not a Compromise

A fully normalized schema is a defensible academic answer and frequently a production liability. If a read path needs data from four joined tables ten thousand times a second, and the underlying data changes rarely, materializing that join into a denormalized read table isn't cutting a corner — it's correctly trading a small amount of write complexity for a large amount of read performance, which is almost always the right trade for read-heavy systems.

“Normalize for correctness. Denormalize for the ten queries that actually run a million times a day. Know which is which before you ship either one.”

The Scaling Ladder, in Order

Before reaching for sharding, we work through a fixed sequence: fix the query and add the missing index; move genuinely hot, rarely-changing data into a cache with a sane invalidation strategy; add read replicas for read-heavy workloads; denormalize the specific hot-path reads; and only then consider partitioning or sharding — and even then, only for the specific tables that actually need it, not the whole database wholesale.

-- Find the queries costing the most total time, not just the slowest single query.
-- This is almost always more useful than EXPLAIN ANALYZE on a query you already suspect.
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  total_exec_time / sum(total_exec_time) OVER () AS pct_of_total_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;