What is a Postgres batched backfill and why does one big UPDATE hurt production?
A Postgres batched backfill is a background job that fills or rewrites a column across a large table in small, independent steps. It walks the table in primary-key ranges, updates one range per short transaction, commits, pauses, and continues. Each batch is idempotent, so running it twice gives the same result, and the job records its progress so it can resume after a crash or deploy without starting over. The job is throttled so production traffic always has priority.
The alternative is a single UPDATE across millions of rows, which is risky for several reasons:
- Row locks last until commit. Every updated row stays locked until the whole statement finishes. Application requests that update or delete those rows wait behind it, and connection pools can fill up with blocked sessions.
- MVCC multiplies the write volume. Postgres writes a new version of each row and leaves the old one as a dead tuple. One statement can roughly double the table's on-disk footprint before vacuum reclaims the space. While the transaction is open, it also holds back the cleanup horizon, so vacuum cannot remove dead tuples anywhere else in the database.
- WAL arrives in a burst. Replicas have to replay all of it, which increases replication lag for every read routed to them.
- A late failure wastes everything. If the statement hits a timeout, a deadlock, or a constraint error at 90%, the entire transaction rolls back. You are left with nothing applied and a table full of dead tuples.
This guide is for backend and platform engineers who need to change large tables in a monolith that serves live traffic and cannot take a maintenance window.
When should you use a batched backfill, and when should you avoid it?
A batched backfill fits when several conditions hold together:
- The table is large. A single statement would run long enough that its row locks, WAL volume and dead tuples become a problem.
- Writes keep arriving. Concurrent updates would otherwise queue behind the backfill's row locks.
- Replicas must stay current. One huge transaction produces a burst of WAL that replicas have to replay, and read traffic routed to them sees growing lag.
- There is no maintenance window. The work has to share the database with production.
Batching splits the work into short transactions. Each one holds locks briefly and commits on its own, so vacuum can reclaim dead tuples between batches and a failure loses one batch instead of hours of progress.
When to skip it
If the table is small enough that a single UPDATE finishes in a few seconds under realistic load, run it as one transaction. A batching harness adds code, monitoring and failure modes that buy nothing at that scale.
Two other cases call for something simpler:
- The change must be atomic. If no reader may ever see a mix of old and new values, such as a reinterpretation of a column's meaning that the application cannot tolerate halfway, batching is the wrong tool.
- A planned downtime window is available. You can usually do the work faster and with less risk in a single pass.
The non-atomic trade-off
While the job runs, old-form and new-form rows coexist. Some rows have the new column populated and others are still NULL or in the legacy format. Application code must read and write both shapes correctly until verification confirms that every row is converted. Only after that check passes should you drop the fallback logic or add constraints that assume the new form.
How does a safe batched backfill work end to end?
The lifecycle has six phases. Each one is a separate deploy or job, so you can pause or stop between any two without leaving the table half-migrated.
-
Add the column. Make it nullable and give it no default, so Postgres only updates the catalog and does not rewrite the table.
ALTER TABLEstill needs anACCESS EXCLUSIVElock for a moment. If a long-running transaction holds a conflicting lock, the DDL waits in the lock queue, and every query that arrives after it waits too. Set a shortlock_timeoutin the same session. The DDL then fails fast, and you retry at a quieter moment. -
Dual-write. Ship application code or a trigger that fills the new column on every insert and update. From this point no new row needs backfilling, so the backfill has a fixed amount of work.
-
Backfill in batches. The job walks the primary key in small ranges and commits after each range. It only touches rows where the new column is still null, so a restart skips rows already done. Progress is recorded in the data itself.
-
Verify. Count the rows that remain unconverted, and spot-check values against the old column. If you find gaps, rerun the backfill.
-
Constrain. Add
CHECKor foreign key constraints asNOT VALID, which skips the full-table scan while holding the strong lock. Then runVALIDATE CONSTRAINTas a separate step. Validation takes a weaker lock that allows normal reads and writes. -
Switch reads, then drop the old path. Flip reads behind a flag so you can roll back. Remove the old column and the dual-write code only after the new path has run cleanly in production.
+--------------------------------+
| 1 ADD COLUMN nullable, no |--> lock_timeout hit: DDL aborts,
| default; SET lock_timeout | nothing changed; retry later
+--------------------------------+
v
+--------------------------------+
| 2 DUAL-WRITE app code or |--> new and updated rows filled;
| trigger fills new column | backfill scope is now fixed
+--------------------------------+
v
+--------------------------------+
| 3 BACKFILL PK range, UPDATE, |--> touches only new_col IS NULL;
| COMMIT, next range | stop anytime, rerun resumes
+--------------------------------+
v ^ gaps found: rerun phase 3
+--------------------------------+
| 4 VERIFY count remaining NULL |--> clean: proceed to phase 5
| rows; spot-check vs old col | gaps: back to phase 3
+--------------------------------+
v
+--------------------------------+
| 5 CONSTRAIN ADD ... NOT VALID |--> ADD: brief lock, no full scan
| then VALIDATE CONSTRAINT | VALIDATE: reads/writes continue
+--------------------------------+
v
+--------------------------------+
| 6 SWITCH READS behind a flag; |--> flag flips back for rollback;
| then drop old column/trigger | drop only after clean prod run
+--------------------------------+
The six phases of a batched backfill, each shipped as its own deploy or job, with the safe stop-and-resume condition for each phase shown on the right.
How do you implement keyset batching with idempotent, resumable batches?
Iterate by key, not by position. A batch selects WHERE id > last_id ORDER BY id LIMIT n, and the next batch starts from the highest key it just processed. With OFFSET, Postgres must walk and discard every skipped row before returning the page. Each batch gets slower than the last, and rows inserted mid-run shift the pages so that some rows are skipped or done twice. A keyset predicate is a direct index seek, so batch 10,000 costs about the same as batch 1.
Make every batch safe to rerun. Add an idempotency predicate such as AND new_col IS NULL so a retried or overlapping batch touches only unfinished rows. Run each batch in its own short transaction and set SET LOCAL lock_timeout and statement_timeout inside it. A batch that hits a lock should fail fast and retry, not queue behind DDL while other sessions queue behind it. Write the last processed key to a checkpoint table in the same transaction as the update, so a crash resumes exactly where committed work stopped.
If hot rows cause lock waits, select candidates with FOR UPDATE SKIP LOCKED. This skips rows that other sessions hold. Then run a follow-up pass that uses only the IS NULL predicate to pick up those rows.
Plan ranges up front when keys are sparse, composite, UUIDs or skewed. Precompute boundaries that each cover a similar number of rows rather than a similar span of key values. For composite keys, use row comparison ((a, b) > ($1, $2)) against a matching index.
Finally, the runner must never sit idle in a transaction between batches. An open transaction holds back the xmin horizon, which blocks vacuum from removing the dead tuples your backfill is creating. Commit before sleeping, and consider idle_in_transaction_session_timeout as a backstop.
-- One-time setup: the checkpoint row is the job's resume point.
CREATE TABLE IF NOT EXISTS backfill_checkpoint (
job text PRIMARY KEY, last_id bigint NOT NULL, updated_at timestamptz NOT NULL DEFAULT now());
INSERT INTO backfill_checkpoint (job, last_id) VALUES ('orders_amount_cents', 0)
ON CONFLICT (job) DO NOTHING;
-- Main pass: run this whole transaction per batch, sleep only AFTER COMMIT.
BEGIN;
SET LOCAL lock_timeout = '2s'; -- fail fast instead of queueing behind DDL
SET LOCAL statement_timeout = '30s'; -- cap a single batch's runtime
WITH cp AS (
SELECT last_id FROM backfill_checkpoint
WHERE job = 'orders_amount_cents' FOR UPDATE -- one runner per job
), batch AS (
SELECT o.id FROM orders o, cp
WHERE o.id > cp.last_id -- keyset seek, never OFFSET
AND o.amount_cents IS NULL -- idempotency: only unfinished rows
ORDER BY o.id LIMIT 5000
FOR UPDATE OF o SKIP LOCKED -- pass over rows other sessions hold
), upd AS (
UPDATE orders o SET amount_cents = round(o.amount * 100)::bigint
FROM batch b WHERE o.id = b.id
RETURNING o.id
)
UPDATE backfill_checkpoint -- checkpoint commits atomically with the data
SET last_id = (SELECT max(id) FROM upd), updated_at = now()
WHERE job = 'orders_amount_cents' AND EXISTS (SELECT 1 FROM upd);
COMMIT; -- 0 rows updated means the main pass is done
-- Follow-up pass: no key bound, picks up rows skipped while locked.
-- Repeat until it updates 0 rows.
BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';
UPDATE orders SET amount_cents = round(amount * 100)::bigint
WHERE id IN (SELECT id FROM orders WHERE amount_cents IS NULL
ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED);
COMMIT;
Each batch runs as one short transaction. It applies per-batch timeouts and a keyset seek with an IS NULL idempotency guard, uses SKIP LOCKED for hot rows, and advances the checkpoint atomically with the update. A key-unbounded follow-up pass then sweeps up any rows that were skipped.
How do you choose batch size and throttle without hurting replicas or vacuum?
Pick a starting batch size, then let the database tell you whether to go faster or slower. A fixed sleep ignores load: it is too cautious at night and too aggressive during peak traffic. After each commit, check these signals:
- Replica replay lag from
pg_stat_replication - Batch duration: rising time usually means lock waits or cache misses.
- Dead tuples (
n_dead_tupinpg_stat_user_tables) - Autovacuum activity in
pg_stat_progress_vacuum
If any signal crosses a threshold you set, halve the batch size or pause. Grow the batch size again slowly once the signals recover.
Every updated row leaves a dead tuple behind. If you backfill faster than vacuum can clean up, the table and its indexes bloat. On large tables, the default scale-factor trigger fires too late, so lower autovacuum_vacuum_scale_factor for that table with ALTER TABLE ... SET (...). Indexed columns matter too. If you update a column that appears in any index, Postgres cannot do a HOT update, so every index gets a new entry and write volume rises sharply. Where you can, backfill first and create the index afterwards.
Illustrative estimate (assumed inputs: 50 million rows, one replica, timings guessed rather than measured):
| Batch size | Batches | Assumed time per batch (incl. pause) | Total runtime | Risk |
|---|---|---|---|---|
| 1,000 | 50,000 | 0.3 s | ~4.2 h | Low lag, short locks |
| 50,000 | 1,000 | 5 s | ~1.4 h | WAL bursts, longer row locks, lag spikes |
Large batches finish sooner but put load on the system in spikes. Small batches cost wall-clock time but produce a steady load that vacuum and replicas can keep up with.
-- 1. Before the backfill: make autovacuum trigger earlier on this table.
-- Default scale factor (0.2) waits for ~20% of rows to be dead.
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum after ~1% dead rows
autovacuum_vacuum_threshold = 10000
);
-- 2. One batch: a bounded key range, committed on its own.
UPDATE orders
SET region_code = 'EU' -- placeholder backfill value
WHERE id >= 1000000 AND id < 1001000
AND region_code IS NULL;
-- 3. Feedback checks to run after each commit.
-- Replica lag: shrink the batch or pause if it exceeds your threshold.
SELECT application_name,
replay_lag,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
-- Dead tuples and HOT ratio: a low hot/upd ratio means index writes on every row.
SELECT n_live_tup,
n_dead_tup,
n_tup_upd,
n_tup_hot_upd,
last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- Is autovacuum working on the table right now, and how far along is it?
SELECT phase,
heap_blks_scanned,
heap_blks_total
FROM pg_stat_progress_vacuum
WHERE relid = 'orders'::regclass;
-- 4. After the backfill: restore defaults.
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold);
Per-table autovacuum tuning and the feedback queries a backfill job can run after each batch to decide whether to shrink the batch, pause, or speed up. The checks cover replica lag, dead tuples, HOT update ratio and vacuum progress.
How do you handle concurrent writes, verify results and enforce constraints?
While the backfill runs, new and updated rows still need the new value, or the backfill chases a moving target. There are two common ways to keep them current.
| Approach | Overhead | Main pitfalls |
|---|---|---|
| Dual-write in application code | Extra column write per request; deploy coordination | Misses writes from scripts, cron jobs and other services |
| Database trigger | Runs on every write, inside the writing transaction | Lock contention with backfill batches; recursion if the trigger updates its own table |
A trigger catches every writer, which is why it is usually the safer choice. Write it as a BEFORE trigger that assigns the value to the incoming row. That adds no extra row lock. An AFTER trigger that issues its own UPDATE on the same table can fire itself again, and it can deadlock against a batch that holds the same rows.
When the last batch finishes, run a verification pass that counts rows where the new column is still NULL. Batches using SKIP LOCKED, or batches aborted by lock_timeout, leave gaps that the keyset cursor has already moved past. Re-run those ranges until the count reaches zero.
To enforce the invariant, add the CHECK constraint as NOT VALID. That step needs only a brief lock, and it applies to new writes immediately. Then run VALIDATE CONSTRAINT as a separate statement. It scans the table under a weaker lock that still allows reads and writes.
Finally, switch reads to the new column behind a flag and watch error rates. After that, remove the dual-write or drop the trigger, and drop the old column in a later deploy.
-- 1. Keep new and updated rows current while the backfill runs.
-- BEFORE trigger: set the value on the incoming row, so there is no extra UPDATE and no recursion.
CREATE OR REPLACE FUNCTION orders_set_amount() RETURNS trigger AS $$
BEGIN
NEW.amount := NEW.amount_cents / 100.0;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER orders_set_amount
BEFORE INSERT OR UPDATE OF amount_cents ON orders
FOR EACH ROW EXECUTE FUNCTION orders_set_amount();
-- 2. After the last batch, count gaps left by SKIP LOCKED or lock_timeout aborts.
SELECT count(*) AS remaining, min(id) AS first_gap, max(id) AS last_gap
FROM orders
WHERE amount IS NULL;
-- Re-fill the stragglers in small batches. Repeat until remaining = 0.
UPDATE orders SET amount = amount_cents / 100.0
WHERE id IN (SELECT id FROM orders WHERE amount IS NULL ORDER BY id LIMIT 1000);
-- 3. Enforce the invariant. NOT VALID takes only a brief lock and checks new writes immediately.
SET lock_timeout = '3s';
ALTER TABLE orders
ADD CONSTRAINT orders_amount_not_null CHECK (amount IS NOT NULL) NOT VALID;
-- 4. Validate separately. This scans existing rows under SHARE UPDATE EXCLUSIVE,
-- so normal reads and writes continue.
RESET lock_timeout;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_not_null;
-- 5. After reads have cut over to amount and error rates are stable, remove the old path.
DROP TRIGGER orders_set_amount ON orders;
DROP FUNCTION orders_set_amount();
-- In a later deploy: ALTER TABLE orders DROP COLUMN amount_cents;
A BEFORE trigger keeps the new column populated during the backfill. A verification query and a re-fill loop then catch skipped rows, and the CHECK constraint is added as NOT VALID and validated in a separate step so the table never takes a long exclusive lock.
What are the alternatives, and how does this approach affect engineering cost and time?
The batched job is not the only way to move data, and the alternatives are sometimes the better choice.
| Approach | Lock and outage risk | Good fit | Main drawback |
|---|---|---|---|
Single UPDATE in a maintenance window |
High: long transaction, row locks, bloat, replica lag | Small tables, or when planned downtime is acceptable | A failed run rolls back everything, and you start again from zero |
| Deploy-time migration | High: the release blocks until the update finishes | Small, fast changes | Ties data movement to release risk |
| New table plus swap | Low during the copy, brief lock at the swap | Rewriting most columns or changing the schema heavily | Needs dual-writes or replay to capture changes made during the copy |
| Lazy backfill on read or write | Very low | Hot rows that are accessed often | Cold rows never converge, so you still need a sweep |
| Batched background job | Low, tunable per batch | Most large in-place column changes | More moving parts to build and own |
Run the batched job as a background worker with a kill switch that it checks between batches. It should log each key range it processes and emit metrics for progress and rate. Schedule it off-peak. Give it one named owner, and put a cleanup ticket on the calendar to remove the worker, the flag and any trigger once the job is done.
The up-front cost is real: dual-writes, dashboards and verification queries take engineering time before any row moves. That time buys separation from releases. Deploys keep shipping while the job runs, and nobody holds a release for a migration. A failed run resumes from its last committed key instead of forcing an emergency rollback or a full restart.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to postgres batched backfill, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- The dependency graph answers "what does this touch?" in seconds instead of a manual audit.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.