Adding an index stops working eventually. Here's what we did when a nationwide logistics platform's core table crossed a billion rows — and the parts nobody warns you about.
There's a specific moment in a backend engineer's life when the usual advice stops working.
A query gets slow. You check the execution plan, you add an index, it gets fast again. This works for years. It works so reliably that it starts to feel like a law of nature.
Then one day you add the index and nothing happens. Or worse — the index takes six hours to build, locks the table while it does, and the query is still slow at the end of it.
That's roughly where we were on a nationwide logistics platform processing tens of thousands of orders a day. The tracking events table — one row per scan, per parcel, per status change — had crossed a billion rows. Every parcel generated a dozen or more events on its journey. The table only ever grew.
This is what we did about it, and more usefully, what nobody told us beforehand.
First: are you sure you need this?
Partitioning is not a performance trick you reach for when a query feels sluggish. It carries real operational cost, and most tables that people want to partition should just be indexed properly.
Some honest signals that you're actually at the boundary:
Your indexes no longer fit comfortably in memory, so index reads hit disk
Index maintenance — REINDEX, VACUUM, ANALYZE — takes so long you can't schedule it
Deleting old data is impossible in practice, because a DELETE of a hundred million rows will destroy your write throughput for hours
Your queries almost always filter on a single obvious dimension, usually time
That last one matters more than the rest. Partitioning only helps if your access pattern lines up with how you split the data. If your queries hit every partition anyway, you have added complexity and gained nothing.
For us the alignment was clean: nearly every query on the events table was scoped to a date range. Operations dashboards looked at today. Merchant tracking looked at the last few weeks. Analytics looked at a month. Almost nothing legitimately needed to scan two years of scan events at once.
That is the actual precondition. Not table size — access pattern.
The mental model that helped
The thing that made partitioning click for me: a partitioned table is not one table that's been made faster. It's many tables wearing a trenchcoat.
You keep querying tracking_events, but underneath, the database is maintaining tracking_events_2024_01, tracking_events_2024_02, and so on. When a query says WHERE created_at >= '2024-03-01', the planner works out that it only needs to open March's table and ignores the rest entirely.
That skipping is called partition pruning, and it is the entire point. Everything else you deal with is a cost you pay to get it.
This model also explains the failure mode immediately. If a query doesn't include the partition key, the database can't prune. It opens every partition. You have taken one large table scan and turned it into forty smaller ones plus coordination overhead — strictly worse than what you started with.
Range partitioning by time
For append-heavy, time-scoped data, range partitioning on a timestamp is the boring correct answer. In Postgres:
CREATE TABLE tracking_events (
id BIGSERIAL,
parcel_id BIGINT NOT NULL,
status VARCHAR(50) NOT NULL,
scanned_at TIMESTAMPTZ NOT NULL,
location_id INT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);
CREATE TABLE tracking_events_2026_01 PARTITION OF tracking_events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE tracking_events_2026_02 PARTITION OF tracking_events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
Note the first thing that bites people: the partition key has to be part of the primary key.
PRIMARY KEY (id, created_at)
You cannot have a globally unique constraint on id alone across a partitioned table in Postgres, because uniqueness would have to be enforced across every partition — exactly the cross-partition check partitioning exists to avoid.
This has knock-on effects. Foreign keys pointing at a partitioned table get awkward. If you have an ORM that assumes a single-column primary key — and most of them do — you will spend a while making peace with it. Worth knowing on day one rather than day forty.
The part nobody warns you about: the migration itself
Every partitioning tutorial shows you CREATE TABLE ... PARTITION BY. Almost none of them tell you how to get a live billion-row table into that shape without downtime you can't afford.
You cannot convert a table to a partitioned table in place. You create a new partitioned table and move the data. With a billion rows and a system that never stops writing, that's the actual project. The partitioning is the easy part.
The approach that worked for us:
1. Create the partitioned table alongside the original. New name, same shape, partitions pre-created for existing data plus a few months ahead.
2. Start dual-writing. Every new event writes to both tables. Application-level is fine and easier to reason about than triggers. This is the point of no return — from here the new table is being kept current while you deal with history.
3. Backfill in small batches, oldest first. Not one enormous INSERT INTO ... SELECT. Batches of 10–50k rows, with a pause between them, throttled to what your replication lag will tolerate. This part takes days. Let it take days. The single biggest mistake available here is trying to hurry it.
4. Verify before you commit. Row counts per partition, checksums on sampled ranges, and spot checks on the boundaries — the last row of January and the first of February. Boundary conditions are where range partitioning bugs actually live, because a row that lands in the wrong partition is invisible until someone queries it.
5. Cut reads over. Ideally behind a flag so you can go back within seconds.
6. Stop dual-writing and drop the old table — after a deliberate wait. Days, not minutes. The old table is your rollback, and it costs nothing but disk to keep it a little longer.
Steps 3 and 4 are most of the calendar time. Step 1 is what the tutorials cover.
What actually got faster (and what didn't)
Being specific about this matters, because partitioning is often described as if it makes databases faster in general. It doesn't. It makes some things dramatically faster and a few things slower.
Genuinely much faster:
- Time-scoped queries. A dashboard querying today's events reads one partition instead of an index over a billion rows. This was the large, obvious win.
- Dropping old data.
DROP TABLE tracking_events_2023_04is near-instant metadata work. The equivalentDELETEwas a multi-hour operation that hammered write throughput and left the table needing a vacuum. Going from "hours of degraded service" to "milliseconds" changed data retention from a quarterly ordeal into a scheduled job nobody thinks about. - Maintenance. Vacuum and reindex now run per-partition, so they fit in a maintenance window and can be staggered.
Unchanged:
- Point lookups by primary key. Already fast via index; still fast. No difference.
Slower:
- Queries that don't filter on the partition key. Now touching every partition. We found several of these only after cutting over, which is the honest version of events — we thought we had catalogued the query patterns, and we had missed some.
- Anything joining across many partitions at once.
That middle category is why the access-pattern question at the top is the whole decision. If a meaningful share of your queries can't prune, partitioning will make your system worse, and you'll have spent weeks getting there.
Two things I'd do differently
Automate partition creation on day one. We created partitions manually at first, on the reasonable-sounding theory that we'd automate it once the pattern settled. Then someone forgot, inserts started failing because no partition existed for the incoming range, and it was suddenly a production incident at an unwelcome hour. It's a scheduled job that creates the next few months ahead of time. Write it before you need it — pg_partman handles this well if you'd rather not maintain it yourself.
Check the ORM story before committing to the design. Partitioned tables interact badly with assumptions frameworks make — single-column primary keys, RETURNING id on insert, foreign keys pointing at the partitioned table, migration tooling that doesn't understand partitions. None of it is fatal. All of it is easier to design around at the start than to discover mid-migration with dual-writes already running.
The short version
Partitioning is a data lifecycle tool that happens to improve query performance when your access pattern cooperates. That framing predicts its behaviour better than "make big tables fast."
Before doing it, answer one question honestly: do nearly all of my queries filter on the same dimension? If yes, and the table is genuinely enormous, partitioning will pay for itself — most visibly in operations like dropping old data, which stop being frightening.
If no, fix your indexes and your queries first. Partitioning will not save you, and it will make everything else harder while it fails to.
I've spent 11 years on backend and data-heavy systems, mostly logistics platforms in Python/Django and PHP/Laravel. If you're working through something similar, or I've missed something in the above, I'd genuinely like to hear it in the comments.
Top comments (0)