Part 4 — Fast at scale › Chapter 31

Big tables: partitioning, archiving, sharding

Partition elimination, the sliding window, and the honest answer about when you need any of it.

No advert asked for this

Adverts say “grandi moli di dati” without saying what to do about it. This is what to do about it.

Start by not needing any of this

✔ The answer that shows judgement

Before partitioning: is the working set the problem, or the table? A billion-row table where every query filters to last month is not a big-table problem — it is an indexing problem, and the right index makes the other 999 million rows irrelevant. Partitioning is for when you need to manage the data in chunks: load it, archive it, drop it, or compress it by age. Reaching for it as a performance fix is the classic mistake, and saying so is worth more than reciting the syntax.

Partitioning

🧒 Simple

One enormous filing cabinet becomes twelve smaller ones, one per month. It is still one filing system and you look things up the same way — but when somebody asks for March you open one drawer, and when the year ends you throw away a whole drawer instead of picking out sheets one at a time.

Scheme	Splits by	Good for
Range	A continuous key, usually a date	Time-series data. The default choice by a wide margin
List	An explicit set of values	Region, tenant, country
Hash	A hash of the key	Spreading write load evenly with no natural range

Declarative range partitioning, and the constraint that surprises everyone.

-- PostgreSQL 10+ declarative partitioning
CREATE TABLE orders (                                        -- the parent: no rows of its own
  id BIGINT, placed_at TIMESTAMPTZ NOT NULL,                 -- the partition key must be NOT NULL
  customer_id INT, total_cents BIGINT                        -- ordinary columns
) PARTITION BY RANGE (placed_at);                            -- how rows are routed to children

CREATE TABLE orders_2026_01 PARTITION OF orders              -- one child…
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');          -- …owning a half-open range

-- The partition key MUST be part of the primary key. That is not a quirk: the
-- engine cannot enforce global uniqueness across partitions without checking all
-- of them, which would defeat the point of partitioning.

What partitioning does and does not buy

FROM … TO … is half-open, exactly like a good WHERE clause: the upper bound belongs to the next partition. Adjacent partitions therefore cannot overlap by construction.
Partitioning is not a performance feature by itself. A well-indexed unpartitioned table is usually faster for point queries. What it buys is manageability: dropping a month, moving old data to cheap storage, and vacuuming or rebuilding one slice at a time.
The primary key must include the partition key, so a globally unique id alone is not possible. That is a genuine design cost, and it is the reason to decide this before the table has a hundred million rows in it.

Partition elimination is the payoff — and it is conditional

Partition elimination is the payoff — and it is conditional on the predicate.

-- Reads ONE partition: the planner can prove which one from the predicate.
SELECT * FROM orders                                            -- the parent table…
WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01';   -- a range on the key

-- Reads ALL of them: the key is wrapped in a function, so the planner cannot
-- prove which partitions can be skipped. The sargability rule, one level up.
SELECT * FROM orders WHERE YEAR(placed_at) = 2026;              -- same answer, every partition

-- Also reads all of them: no predicate on the partition key at all.
SELECT * FROM orders WHERE customer_id = 42;                    -- a fine query, badly partitioned

The rule this illustrates

Partition elimination happens at plan time and needs a predicate the planner can reason about: a bare comparison on the partition key. Wrap the key in anything and the guarantee is gone.
The third query is the design question, not a bug: if most queries filter by customer and not by date, then date is the wrong partition key — and possibly nothing should be partitioned.
Check it, do not assume it. The plan names the partitions it will read; a plan listing all of them is the whole feature failing silently.

⚠ Trap — partitioning can make queries slower

A query that does not filter on the partition key now touches n indexes instead of one, and merges n sorted streams instead of reading one. So partitioning by month and then querying by customer_id is worse than not partitioning at all. Choose the partition key from how the data is queried and managed, and accept that one key cannot serve every access pattern.

The sliding window

The real reason range partitioning earns its complexity: retention becomes metadata, not work.

The real payoff: retention becomes a catalogue operation instead of millions of deletes.

-- Retire January 2025 without a DELETE. This is a catalogue operation: no rows
-- are read, no log records are generated, and it completes in milliseconds.
ALTER TABLE orders DETACH PARTITION orders_2025_01;   -- PostgreSQL: it becomes a plain table
DROP TABLE orders_2025_01;                            -- …or keep it, compressed, on cheap storage

-- SQL Server: SWITCH the partition into a staging table, then truncate that.
ALTER TABLE orders SWITCH PARTITION 13 TO orders_archive;   -- metadata only, instant

Why this is worth the complexity

DELETE FROM orders WHERE placed_at < '2025-02-01' on forty million rows is a very large transaction: a very large log, a long lock, and dead space left behind. DETACH and SWITCH change a catalogue entry.
The switched-in table must match exactly — same columns, same types, same indexes, same filegroup, and a constraint proving its rows belong to that range. Getting that right is most of the work, and it is why this is scripted rather than typed.
Detaching does not delete anything. That is a feature: the data is still there if the retention decision turns out to have been wrong.

🎓 Compare that with the alternative

DELETE FROM orders WHERE placed_at < '2025-02-01' on 40 million rows: hours of work, a transaction log the size of the data, lock escalation, index fragmentation, and bloat to vacuum afterwards. The partition switch is a metadata update. That is the argument for partitioning, and it is an argument about operations, not about SELECT speed.

Archiving without partitioning

Most systems that think they need partitioning need this instead:

What most systems that think they need partitioning actually need: a batched delete.

-- Batched, resumable, and it never holds a long transaction or escalates locks.
-- Run it in a loop until it affects zero rows.
WITH doomed AS (                                     -- choose a bounded set first…
  SELECT id FROM orders                              -- only the keys are needed
  WHERE placed_at < '2025-01-01'                     -- old…
    AND status IN ('delivered','cancelled')          -- …and finished
  ORDER BY id                                        -- deterministic, and index-friendly
  LIMIT 5000                                         -- the batch size: the whole point
)                                                    -- end of the CTE
DELETE FROM orders WHERE id IN (SELECT id FROM doomed);   -- delete exactly those rows

Why the batch size is the whole design

One statement deleting forty million rows is one transaction: the log must hold all of it, the locks are held for all of it, and a failure at 95% rolls back everything. Five thousand at a time is a series of short transactions, each cheap to undo.
It is resumable: interrupted at any point, the next run continues from wherever it stopped, because the WHERE clause defines the work rather than a cursor position.
Below the lock-escalation threshold — around 5000 row locks in SQL Server — the engine keeps taking row locks instead of locking the table. Choosing 5000 rather than 50 000 is that threshold, not superstition.
Add a short pause between batches on a busy system, so replication and other writers can keep up.

Three details that make it safe

Bounded batch size, so each transaction stays under the lock escalation threshold and the log can truncate between batches.
Ordered by the key, so each batch is a contiguous index range rather than a scan of the whole table looking for survivors.
A pause between batches in a busy system, so other work gets through. Ten minutes of polite deletion beats one minute of blocking everything.

Sharding, and what it really costs

🎓 Partitioning splits a table. Sharding splits a database.

Sharding puts different rows on different servers, which is the only way past one machine's write capacity. What you give up is significant and permanent:

No cross-shard transactions. ACID stops at the shard boundary.
No cross-shard joins or foreign keys. Both move into application code.
Rebalancing is a project, not a maintenance task — which is why consistent hashing and "many more logical shards than physical servers" exist.
Every query needs the shard key, or it fans out to all of them.

✔ The honest ladder, in order

Index properly. Most "too big" tables are an indexing problem.
Archive what nobody queries.
Read replicas, if the load is reads.
Partition, for manageability and retention.
A bigger machine — genuinely, this buys years and costs less than the alternatives.
Shard, when writes exceed one machine and you have exhausted the rest.

Being able to give that ladder — and say “most systems never get past step 4” — is a much better answer than describing a sharding scheme.

Most big-table problems are indexing problems. Check that first.
Partitioning is for manageability — loading, archiving, dropping by age — more than for speed.
Partition elimination only happens when the predicate is on the raw partition key.
A query that does not filter on the partition key is slower when partitioned.
The partition key must be in the primary key, because uniqueness cannot be enforced across partitions cheaply.
Detaching a partition is a metadata operation; deleting the same rows is hours of logged work.
Batched, ordered, bounded deletes are the archiving strategy for everyone who is not partitioned.
Sharding costs cross-shard transactions, joins and foreign keys, permanently.
Index, archive, replicate, partition, bigger machine, and only then shard.

Interview questions this chapter answers

When would you partition a table?

When I need to manage the data in chunks — a retention policy where dropping a month should be a metadata operation, or a load that replaces one slice at a time. Not as a first response to slowness: if queries filter to a small subset, an index does that better, and partitioning actively hurts queries that do not filter on the partition key.

How would you delete 40 million old rows from a live table?

In batches of a few thousand, ordered by the primary key, each in its own transaction, looping until nothing is affected — so locks never escalate and the log can truncate between batches. If the table were range-partitioned by date I would detach and drop the partition instead, which is a metadata operation.

Partitioning or sharding?

Partitioning splits a table within one database and keeps transactions, joins and constraints. Sharding splits across servers and gives all three up, so it is the last step, not an early one. I would index, archive, add read replicas, partition and buy a bigger machine first — most systems never need to go further.