One table is 400GB and everything that touches it is slow

The events table is 400GB. Inserts are fine. Every query that touches it is somewhere between painful and timing out, the nightly aggregate takes four hours, and VACUUM never finishes inside its window. Three people have said the word sharding this week and nobody has said what it would be sharded by.

Partition, shard, or archive. Three answers with three very different costs, and one question picks between them.

Predict

Before any arithmetic: which fact about the workload decides whether this table should be partitioned, sharded or archived?

Choose one answer

The size. Past a certain number of rows a single table has to be split, and the threshold is what you need to find.The write rate. Sharding exists to spread writes across machines, so the question is whether one primary can still take the inserts.What every query filters by. If they all carry a time range, partition. If they all carry one entity id, shard. If most of the data is never queried, archive.The hardware. Whether the working set still fits in memory on the largest instance you can buy.

Lock in answer

The three answers, and the predicate that picks one

Archive first, because it is the only one that removes work. If queries only ever touch the last ninety days and the table holds four years, then roughly 94% of the rows are being carried for nothing: indexed, vacuumed, backed up, and scanned whenever a query misses its index. Move them somewhere cheap, keep a path to read them for the rare case, and the remaining table may not need splitting at all. This is the answer that gets skipped because it is not architecture.

Partition when every query carries a range. Postgres declarative partitioning by range on a timestamp lets the planner prune: a query with created_at >= now() - interval '7 days' reads one or two partitions and ignores the rest. The nightly aggregate becomes per-partition work. VACUUM runs per partition. Dropping the oldest month becomes a DETACH and a DROP rather than a DELETE of a hundred million rows, which means archiving gets cheaper too. One database, one connection string, no application change.

The constraint to know before you commit: in Postgres the partition key must be part of every unique constraint and primary key on the table. If your events table has a unique constraint on an external id, that constraint either gains the partition column — weakening it — or goes away. This is the single most common reason a partitioning plan is discovered to be impossible in week two. MySQL has the same rule for its native partitioning, where every unique key must include all columns of the partitioning expression.

Shard when every query carries one entity id, and you have run out of machine. Sharding puts pieces of the table in different databases, so it is the only one of the three that raises your write ceiling and the only one that makes a cross-piece query a distributed join you have to write yourself. It is also the one that changes your failure model, which is what the model below is about. Citus does this for Postgres, Vitess for MySQL, and both are real products with real operational weight; doing it by hand in the application is a decision to maintain a database.

What sharding does to your failure model

Splitting a table across machines does not only change performance. It changes how much of your user base one dead machine takes with it, and it changes it in a way people consistently guess wrong.

Blast radius

≈ Hypothetical model, not a forecast

The share of users one failed node touches, given shard count and replication, next to the share a correlated failure touches. Rows step through shard counts by doubling, so period is the doubling index and the row's shards key carries the real count.

It assumes

• Users are spread evenly across shards, with no hot key anywhere in the data.
• Every shard carries the same number of replicas, placed across zones in turn.
• One node failing is independent of every other node failing in the fleet.
• A shard keeps serving as long as one of its replicas is still running.
• A correlated fault, such as a config push, reaches the share of nodes you set.
• Nothing here counts recovery time, which decides whether this is an incident or a blip.

It ignores

• Hot shards and celebrity keys, which make the even split a fiction
• Cross-shard requests, which let one dead shard fail requests on every shard
• Recovery time, which decides whether a blast radius is an incident or a blip
• The control plane, a shared dependency whose blast radius is always everything

Raise the shard count until one failed node touches no more than 5% of users, then look at the flat line. Find the shard count that meets the target and say what the correlated fault still reaches.

Users2,000,000

Shards1

Replicas per shard3

Total copies of each shard, so 1 means a single unreplicated node.

Share of users one failed node touches

1

Users one failed node touches

2,000,000

Share of users who lose service outright

0

Share of serving capacity one node takes with it

0.333

Share of capacity one zone takes with it

0.333

Share of users a zone outage takes down

0

Share of users a correlated fault touches

1

Users a correlated fault touches

2,000,000

Nodes in the fleet

3

Users on one shard

2,000,000

Shards needed to reach the target blast radius

32

Target reachable inside the charted range

1

• Share one failed node touches
• Share a correlated fault touches

◷ Not there yet — keep moving the controls.

Two readings.

Sharding is a blast-radius argument as much as a capacity one. At one shard, any failure that outlives your replicas is everybody. At thirty-two, it is about three percent of users, and the difference in how an incident feels is larger than the difference in the number. That is a real reason to shard, and it is a different reason from running out of write capacity.

It buys you nothing against the failures you actually have. The flat line is the point. A bad schema migration, a bad config push, a bad deploy, an expired certificate, a full disk on a shared dependency — none of them respect shard boundaries, and all of them are more common than independent node death. Replication divides an independent failure and does nothing to a correlated one.

Check

Your queries all filter on tenant_id, your largest tenant is 40% of the data, and one primary is at 30% CPU. What do you do?

Choose one answer

Shard by tenant_id. The predicate says so, and that is the rule from this lesson.Nothing yet. One primary at 30% CPU has not run out of machine, so partition by tenant_id inside the one database and keep the option open.Shard by a hash of the primary key, so the data spreads evenly and the hot tenant stops mattering.Give the largest tenant its own database and shard the remainder by tenant_id.

Check answer

The condition that breaks all three

Everything above assumes the load spreads roughly as evenly as the data. Once one key draws more traffic than one node can serve, no amount of splitting helps, because the smallest unit you can split to is one key and that key is already on one machine. The model says this in its first assumption and the real world says it with a single tenant who is a tenth of your revenue, or one viral piece of content, or a batch job that reads one customer's history in a loop.

The crossing is specific: when the busiest single value of your partition key needs more read or write throughput than one replica set can provide. Past that point the answers are different in kind — a cache in front of that key, a separate topology for that one tenant, splitting the key itself into sub-buckets with a composite key, or changing the access pattern so the hot key is not read on the critical path. All four are more work than sharding and none of them is sharding.

Which is why I would partition before sharding in almost every case, and measure the distribution of the partition key before either.

›What archiving actually means, operationallyOptional

"Move the old rows somewhere cheap" hides three decisions.

Where. A second table in the same database buys you nothing on backup size or memory pressure. Object storage as Parquet, queried by something like DuckDB or a warehouse, is the cheap answer and makes the old data a different system with a different query language. A separate database keeps SQL and keeps most of the cost.

How the read path finds it. If a query may need archived rows, something has to decide where to look. A union view across live and archive is the honest version, and every query that reaches the archive half pays object-storage latency for it. A feature flag saying the UI only shows ninety days is faster and is a product decision pretending to be a technical one. Say which you are making.

How you get the rows out without a long-running delete. This is the real reason partitioning and archiving belong in the same conversation: a DELETE FROM events WHERE created_at < … on a hundred million rows generates an enormous amount of write-ahead log, bloats the table, and leaves vacuum work behind. ALTER TABLE ... DETACH PARTITION followed by a DROP TABLE is a metadata operation. If you expect ever to archive, partition by the column you will archive on, even if the query pattern does not demand it.

Worth remembering

• What every query filters by picks the split; the table's size only says something hurts.
• Archive first, because it is the only one of the three that removes work.
• Partition when queries carry a range; the planner prunes and maintenance goes per piece.
• In Postgres the partition key must be in every unique constraint and primary key.
• Shard when queries carry one entity id and you have genuinely run out of machine.
• Partitions stay in one database; shards bring a cross-shard query layer with them.
• Sharding divides an independent failure and does nothing to a correlated one.
• Blast radius is one divided by the shard count, so twenty shards is five percent.
• A key hotter than one node serves defeats every split, because one key is the smallest piece.
• Partition by the column you will archive on, so the delete becomes a DETACH and a DROP.