Sharding and Partitioning

Opening Scenario

Your team runs a SaaS platform. For the first two years, a single PostgreSQL instance handles everything fine. Then, user growth hits. The events table crosses 500 million rows. Queries that used to take 50ms now take 4 seconds. You add indexes — that helps briefly, but index size itself is now the problem. You throw more RAM at the server: the working set no longer fits. A DBA mentions "you should shard this." A colleague counters with "use table partitioning first." You're not sure what either actually involves, or why one might be better than the other.

---

Question Progression

Part 1: Why Does a Single Table Degrade at Scale?

Question: Your events table has 500M rows and a B-tree index on user_id. Why would queries slow down even though the index exists?

Reasoning: An index B-tree shrinks the search space. Instead of scanning 500M rows, the database traverses…