Database Partitioning

A single table with 5 billion rows is slow and awkward. Every index is huge, a full scan reads everything, and routine work like deleting old data or rebuilding an index locks up the whole table. Partitioning solves this by cutting that one logical table into many physical pieces, each holding a subset of the rows. To your application it still looks like one table named orders, but underneath the database stores it as orders_2024_01, orders_2024_02, and so on.

The key idea is that you split rows along a column called the partition key. The database uses that key to decide which partition a row belongs to and, more importantly, which partitions a query needs to touch. A query for last week's orders only reads the recent partitions and skips the rest, an effect called partition pruning. That alone can turn a 30 second scan into a 200 millisecond one.

Partitioning is not the same as sharding, even though people use the words loosely. Partitioning keeps all the pieces inside one database instance on one machine. Sharding takes the same idea and puts each piece on a different server so you can scale write throughput and storage past what one machine can hold. Partitioning helps a single big database; sharding is how you escape a single big database.

There are three common strategies. Range partitioning groups rows by a continuous value, most often a date: January in one partition, February in the next. It is great for time-series data and makes dropping old data instant, because you just drop the whole partition instead of running a slow DELETE over millions of rows. Hash partitioning runs the key through a hash function and uses the result to spread rows evenly across a fixed number of partitions, which avoids hotspots when there is no natural range. List partitioning assigns specific values to specific partitions, for example one partition per country code or region.

Each partition is a real storage object with its own data files and its own indexes. A global query that does not filter on the partition key has to check every partition, which can be slower than a plain table, so the partition key has to match how you actually query. In Postgres you declare this with PARTITION BY RANGE on a parent table; in MySQL with PARTITION BY HASH; the planner then routes reads and writes automatically.

The choice of partition key is the decision that makes or breaks the design. Pick a key that your hot queries filter on so pruning kicks in. Avoid a key with skew, where one partition gets most of the rows or traffic, because that partition becomes a bottleneck and the others sit idle. Repartitioning later is expensive since it means rewriting and moving data, so the key is hard to change once the table is large.

Reach for partitioning when a single table grows past tens or hundreds of millions of rows, when most queries filter on one obvious column like a timestamp or tenant id, or when you need to expire old data cheaply by dropping whole partitions. Time-series tables, event logs, and per-customer data are the classic fits.

The cost is complexity and constraints. Unique constraints and primary keys usually have to include the partition key, which can force schema changes. Queries that ignore the partition key get slower, not faster, because they fan out to every partition. Cross-partition joins and aggregations cost more, and the number of partitions has a practical ceiling, since thousands of tiny partitions create their own planning overhead.

Do not partition prematurely. A table with a few million rows and a good index will outperform a badly partitioned one. Partitioning trades simplicity for scale, so it pays off only once the table is genuinely large or once data lifecycle management on one giant table has become painful.

Picture a SaaS app storing a logins table that grows by 200 million rows a year, and the team keeps two years of history. As one table, the monthly report query scans 400 million rows and an analytics dashboard times out. Deleting the oldest month means a DELETE that locks the table for an hour.

Range partition the table by month on the created_at column. Now the report for last month reads exactly one partition of about 17 million rows, so it runs in well under a second through pruning. Expiring data is a single DROP PARTITION that finishes instantly with no row-by-row delete and no lock storm. New months are added by creating the next partition ahead of time.

If that company keeps growing until one server cannot hold the data or absorb the write rate, the next step is sharding: take the same partitioning logic and place each tenant's partitions on a different physical database, routed by tenant id. The mental model is identical, the difference is that the pieces now live on separate machines.