It's the most boring migration there is. Add a column, backfill it, done. Instant on your laptop. Instant against the test suite's two hundred rows. Green checkmarks everywhere. You ship it with the confidence of someone who has clearly never been humbled by a production database before.
Then prod goes quiet.
The deployment is stuck. Everyone's in the channel asking the same question, the one that always arrives forty seconds after you start sweating: when's this coming back? You check pg_stat_activity and there it is — still running, still holding its lock, staring back at you like it has all night. How much longer? No idea. Ten minutes. Fifteen. Postgres will not tell you, because that's not how a single giant statement works: no percentage, no row count, nothing, right up until it finishes or you blink first and run SELECT pg_terminate_backend(pid) like a coward. Deployment rolled back. Now you get to actually fix it, in front of everyone, while your coffee gets cold.
This isn't a hypothetical, by the way. I've been the person squinting at pg_stat_activity, silently negotiating with a query that owes me nothing. More than once. You'd think I'd learn the first time. I didn't. If any of this sounds familiar, good — that's kind of the point of writing it down.
There's an old answer to "how do you eat an elephant": one bite at a time. Same idea here. Go chunk by chunk, make every bite small enough to fail safely, and never leave one oversized piece dangling at the end that chokes you exactly the way the original UPDATE just did.
And here's the part nobody warns you about before you learn it the expensive way: figuring out how to split the table can end up costing more than actually processing it. I know, because I personally paid that tax, more than once, before I noticed I was the one setting the price.
The clever ideas that are secretly traps
The option that looks cleanest, the most database-native, is computing every chunk boundary in one shot with a window function — something like NTILE. We tried it. It's not the shortcut it looks like: to hand each row a bucket number, Postgres first has to order and scan the entire table, and getting a fixed chunk size out of it means counting rows before you've processed a single one. You pay full price — a full scan — for buckets that come out perfectly even, an exactness the next section spends its entire time arguing you never needed.
Attempt one, the one that feels responsible: split by something that means something. created_at, a customer id, a status column. Feels like you're modeling the domain properly, like a serious engineer.
It falls apart the second you ask how those values are actually distributed — because you don't know, and finding out means counting or bucketing first, which on a hundred-million-row table is, again, a full scan. Congratulations: you've run an expensive query to find out how to run your real query. You haven't processed a single row and you're already in debt.
Attempt two, the one everyone reaches for next: OFFSET / LIMIT. Worker 1 takes offset 0, worker 2 takes offset 10,000. Looks beautiful on a whiteboard. In practice, OFFSET doesn't skip anything — it still walks and discards every row before it, so page 9,000 crawls compared to page 1. And the moment a row gets inserted or deleted mid-scan, your offsets quietly slide out from under you and you double-process or ghost rows without so much as an error. The pattern degrades exactly at the size that made you want to parallelize it in the first place. Deeply on brand for a lot of engineering shortcuts, honestly.
There's a more grown-up version of this — keyset pagination: WHERE id > :last_seen ORDER BY id LIMIT n, remembering the last key instead of counting positions. It's fast. It's stable. It doesn't lie to you. It's also completely sequential by construction: page 2 needs page 1's last id before it can exist. You can make it quick, but you cannot hand ten pages to ten workers and walk away, because nine of them are just standing around waiting for one number from a friend who's running late.
Three approaches, one shared disease: to figure out how to divide the work, you have to ask the database something first. Every single time.
The column that was doing this the whole time
And this is the part that stings a little: a lot of these tables already have a column sitting right there, quietly answering the question for free, and you've been ignoring it. A serial — almost always just called id, because nobody's feeling creative the day they add it — a dense, monotonically increasing integer, whether or not it's also your primary key. Don't have one? It's a one-line migration. Either way, you've probably been sitting on a free coordinate system this whole time and using it exclusively to fetch one row at a time — like owning a treasure map and only ever using it as a coaster.
Instead of asking "how are the rows distributed" — a question that requires reading the data — ask "what's the smallest and largest value in this column," a question the index answers by peeking at its own two ends. One lookup. Done. Doesn't care if the table has a hundred rows or a hundred million.
But first, the dumb thing, the one from the title, so we're looking at the exact same failure instead of just talking around it:
from sqlalchemy import text
session.execute(text("UPDATE big_table SET status = 'reviewed'"))
session.commit()
Fire that at a hundred-million-row table and go find something else to do with your afternoon. You'll need it. One statement, one lock, the entire table, until it finishes or you give up and kill it.
Once you have those two numbers from the index lookup, splitting the table stops being a database problem — and Python already ships the tool that solves it, plus tqdm, so you get a progress bar without having to invent one yourself:
from sqlalchemy import text
from tqdm import tqdm
min_id, max_id = session.execute(text("SELECT MIN(id), MAX(id) FROM big_table")).one()
chunk_size = 1000
for start in tqdm(range(min_id, max_id + 1, chunk_size)):
end = start + chunk_size - 1
session.execute(
text("UPDATE big_table SET status = 'reviewed' WHERE id BETWEEN :start AND :end"),
{"start": start, "end": end},
)
session.commit()
Same statement, same table, same result — just sliced. That's the entire trick, and SQLAlchemy barely shows up in it: one query to get the bounds, then range() hands you every chunk's starting point for free, and tqdm turns the loop into an honest progress bar without you writing a line of tracking code. No more asking the database how to divide the work, ever again. Whether you run these chunks one after another or hand them out to several workers at once is a separate decision, and it can wait — the point right here is that you never have to ask that first question twice. You could still work this out on a napkin. Python just saves you the napkin.
Not every job is a pure in-database UPDATE like this one. If you need to pull rows out, transform them in Python, and write something back, the same bounds still do the work — swap the UPDATE for SELECT id, status FROM big_table WHERE id BETWEEN :start AND :end, same loop, same tqdm, same chunk_size. Only the payload changes once the rows land in your hands.
Bounded, not exact
Now the detail that makes this survive contact with production instead of just looking clever in a blog post — this one, specifically: those ranges will not contain exactly chunk_size rows. Ever. Rows get deleted, transactions roll back, ids get burned by things that never made it into the table at all. A slice you sized for 10,000 might genuinely hold 9,400, or 200, or, on a bad day, basically nothing.
Except nobody actually asked for exactly 10,000. Go back and reread the requirement: it was never "hand me precisely n rows," it was "keep each chunk small enough that it doesn't fall over." A range that's at most chunk_size wide, gaps and all, satisfies that completely. The moment you stop demanding an exact count and settle for a bounded one, the whole scheme gets simpler for free: no rebalancing, no gap-filling, no bookkeeping to make the numbers come out even. You're trading a guarantee nobody downstream was cashing in for a scheme where one query decides how to slice the entire table, no matter how big it gets. That one query buys you the split — it doesn't buy you a pass on actually running the chunks.
The same slack covers the other end too, the one people worry about but rarely say out loud: what if the table keeps growing while the job is still running? Don't ask for the exact current max — pad it. SELECT MAX(id) + margin instead of SELECT MAX(id), where margin is however many ids your system could plausibly hand out before the job wraps up. A slow-moving table might only need +100. Something ingesting constantly might want +10,000. Either way, any chunk that reaches past what actually exists yet just comes back empty, and an empty indexed range scan costs about as much as asking someone a question they answer with a shrug. You're not buying insurance against growth here. You're just refusing to pay for precision nobody needed on either end of the table.
Zoom out for a second, because this is bigger than one trick, and it's the closest thing this article has to a life lesson: an embarrassing amount of coordination overhead in distributed systems exists purely to protect an exactness nobody is actually relying on. Find the constraint you're allowed to let go of, and often the expensive half of the problem goes with it.
Fine, but is it actually faster, or am I just proud of myself
Yes. And here's the actual math behind it, because "trust me, it's faster" is exactly the kind of claim this whole approach exists to avoid making. A round trip to Postgres for a simple indexed query — network plus parse plus plan plus execute — costs a fraction of a millisecond, and it costs about the same fraction of a millisecond whether it returns one row or a hundred. That's the entire argument. Process rows one at a time and you pay that fixed cost once per row: a million rows means roughly a million round trips, and even at a forgiving half a millisecond each, that's minutes spent purely on the tax before a single byte of real work happens. Process the same rows in chunks of a hundred, and the exact same round trip that used to move one row now moves a hundred — the fixed cost gets amortized across the whole chunk instead of paid per row, every row.
I'm not going to hand you a benchmark number here; it would depend on your network, your hardware, your query, and quoting one as if it were universal would just be a different flavor of lying to you. What doesn't depend on any of that is the arithmetic: fewer round trips, same fixed cost per round trip, less total tax.
The giant UPDATE is the other one I won't put a number on, for a different reason: how badly it degrades depends entirely on configuration — WAL settings, checkpoint distance, whether the planner can actually use an index for that predicate, how much it's fighting everything else running at the time. What doesn't depend on configuration is the shape of the problem: one statement, one lock, one transaction, for the entire duration, with zero visibility into how far it's gotten until it either finishes or you kill it.
Both of those are the actual baselines people reach for, not straw men picked to make chunking look good. The monolith loses to its own lock and its own silence. The row-by-row loop loses to the round trips above. Chunking wins precisely by refusing to be either extreme. No benchmark table follows this paragraph, on purpose — too many variables for a number anyone else could trust. The arithmetic above doesn't share that problem.
There's a second win that's easy to undersell, and you already watched it happen: that tqdm(range(...)) in the snippet wasn't decoration. tqdm can wrap any iterable in a progress bar, but it only knows how far along you are — and how much is left — when the iterable knows its own length up front, and range(min_id, max_id + 1, chunk_size) does, the moment it's created. That's the second payoff of the same free MIN/MAX lookup from before: not just cheap boundaries, but an honest progress bar over chunk count (chunk 428 of 812) that cost nothing extra to add.
Though "honest" has a limit, so let's not get smug about it. Chunks aren't guaranteed to hold the same number of live rows — that's the whole point of the last section — so a stretch of near-empty chunks reports progress faster than it's actually delivering work. Treat the count as real, the ETA as approximate. Compare that to the giant UPDATE, which gives you nothing until it either returns or your connection times out. It's an estimate, not a guarantee — spread the gaps evenly with nothing huge missing, and it's a good one. The real value isn't precision, it's confirmation: the bar is still moving, nothing's crashed, this will take about that long, go grab a coffee, you've earned it.
Pick your poison: one transaction, or actual speed
Run this single-threaded and something nice falls out for free: the whole job can live in one transaction. Which means you can dry-run it — process every chunk, inspect what would've changed, and roll the entire thing back like it never happened. Confident it's correct? Run it again for real, still one transaction, still all-or-nothing. Either every chunk lands, or none of them do. That's a genuinely comforting property to have on anything touching a hundred million rows.
Not free comfort, though — nothing ever is. A transaction that lives that long holds its snapshot open for the whole run, which means autovacuum can't reclaim dead rows behind it, and every lock you pick up along the way stays held until the very end. That's fine for an offline maintenance window. It's worth thinking twice about if this runs anywhere near live traffic.
Multithread it instead — hand chunks to a ThreadPoolExecutor if the work is mostly waiting on Postgres, a ProcessPoolExecutor if it's actually CPU-bound in Python — and you get real wall-clock speed, the kind that turns "run this overnight" into "run this over lunch." But the single transaction is exactly what you traded away to get there. Now every worker commits its own chunk independently, which means chunk 4 blowing up halfway through doesn't undo chunks 1 through 3. They already happened. They're already durable. There's no group rollback waiting to save you. If you already have a job queue your workers pull from, that's usually the right place to send these chunks rather than spinning up a bespoke thread pool for the occasion — no need to reinvent dispatch you already own. Just go in knowing which trade you made: atomic-and-slower, or fast-and-partial-failure-means-partial-state. Both are fine choices. Just make sure you know which one you're holding before it matters.
Where this falls apart (so you don't have to find out live)
Before any of the technical caveats, there's a more basic one, and it's the one people skip because admitting it feels bad: do you actually need this? If your system's steady state is a handful of objects moving through a workflow at a time — a form submission, a webhook, an order — this is overkill, and you should feel a little embarrassed building it. Arithmetic partitioning, a batching layer, a queue to feed it — that's real complexity to carry for a table that will never see a batch job touch a meaningful fraction of it in one go. This pattern earns its keep specifically when the shape of your problem is batch: a large volume of data arriving at once, or the case everyone eventually runs into — a migration that has to backfill or transform a big chunk of an existing table in one pass. If you're always processing a handful of records that showed up one at a time, skip all of this and don't overthink it. Not everything needs to be an engineering project. Some things just need an UPDATE WHERE id = :id.
This trick has a blast radius, and I'd rather tell you now than have you discover it during an incident. It assumes the table gets read far more than it gets written concurrently — periodic batch jobs, not a firehose of transactions all fighting over the same id range at 3am. And at genuinely extreme scale, this is a technique, not a replacement for real declarative partitioning or sharding. It's the thing that carries you through the awkward middle where dedicated infrastructure isn't worth the operational tax yet, not a forever solution.
Next time a migration's UPDATE has been running for forty minutes and you're just watching the cursor blink, you'll know exactly what to do about it: kill it, grab the MIN and MAX, and turn the one enormous statement into a hundred small ones instead. Same table, same result, same data, just sliced — and hours turn into minutes, sometimes seconds, not because the database got faster, but because you finally stopped asking it to hold its breath for you.