Our Interleaved Backfill Wrote Three Times the WAL

TypeSafe opened early access to its Jev model on 15 September 2026, pricing input at $0.042 per million tokens and listing output tokens as free, so at our own estimate of 150 input tokens a support ticket, labelling ten million tickets comes to about $63. Storing those labels in new category and confidence columns is a Postgres backfill on a large table, and when we ran one on a million rows in ten batches, the WHERE clause that picked each batch decided most of what the table paid for it.

Range batches at the default fillfactor, with nothing run between them, took the heap from 269.3 MB to 548.3 MB and nearly doubled the three indexes to 70.1 MB. Only 11.5% of the updates were HOT when we repeated them at fillfactor 90 with a VACUUM after every batch, so the indexes still grew to 64.4 MB. Swapping id between lo and hi for id % 10 = r, and changing nothing else, lifted the HOT share to 96.1% and held the indexes at 36.4 MB against the 35.6 MB they started at. Where range batches wrote 850 MB of WAL, interleaving wrote 455 MB, a lead it kept only until a checkpoint followed every batch and the interleaved run wrote 3 022 MB against 1 025 MB for range batches.

Key Takeaways

• Ten range batches without VACUUM ended with the same heap as one UPDATE over all million rows, so batching alone did nothing for the heap.
• A VACUUM after each range batch kept the heap of a fillfactor 100 table to 306.0 MB, but the indexes still reached 65.0 MB and the WAL came out slightly higher than without it.
• Range batches stay mostly non-HOT even at fillfactor 90 because each asks every page it touches for new versions of all its rows at once, where an id % 10 batch asks for one row in ten.
• A hybrid that runs all ten id % 10 passes inside one id range before moving to the next kept interleaving's HOT share and wrote 544 MB of WAL with a checkpoint after every range.
• ALTER TABLE ... SET (fillfactor = 90) with no rewrite still gave 86.2% HOT on a table loaded at fillfactor 100, and a rewrite by VACUUM FULL on 18.6 or REPACK (CONCURRENTLY) on 19 Beta 4 took it to 96.1%.
• Nothing was HOT once a partial index named confidence in its predicate, and a probability map stored in the row next to the label cost more HOT as jsonb than as real[].

Each run built a fresh tickets table on PostgreSQL 18.6, loaded a million rows in id order, indexed created_at and customer_id next to the primary key, and only then added category text and confidence real, which no index covered, though one run's partial index named confidence in its predicate. A second pass on PostgreSQL 19 Beta 4 reran the core cases, and its HOT counts matched 18.6 to the row in all of them but one. At load the heap measured 269.3 MB at fillfactor 100 and 300.4 MB at fillfactor 90, with 35.6 MB of indexes. Before the first batch of each run we called pg_stat_reset() and issued one CHECKPOINT, and with autovacuum off, checkpoint_timeout at 3600 s and max_wal_size far above anything a run wrote, no other checkpoint happened unless the script asked for one. No model was called in these runs, and a SQL expression stood in for a Jev Choice answer by returning one of twelve category strings plus a confidence between 0.5 and 0.999, since the bytes written back decide what the table pays whichever label a model picks.

create table tickets (
  id bigint generated always as identity primary key,
  created_at timestamptz not null,
  customer_id int not null,
  subject text not null,
  body text not null
) with (fillfactor = 90);   -- 100 in the baseline runs

insert into tickets (created_at, customer_id, subject, body)
select now() - (g % 700) * interval '1 hour',
       ((g::bigint * 7919) % 50000)::int,
       'Ticket ' || g || ' about ' || (array['billing','login','export','api','invoice','sso'])[1 + g % 6],
       repeat(md5(g::text), 6)
from generate_series(1, 1000000) g;

create index tickets_created_at_idx on tickets (created_at);
create index tickets_customer_id_idx on tickets (customer_id);
alter table tickets add column category text, add column confidence real;
vacuum (analyze) tickets;

Seven of the 18.6 runs carry most of this article, and their heap, index and WAL figures below were read after the last batch.

Why a Postgres backfill doubles a large table

An ordinary UPDATE never changes a row where it sits. Postgres writes a complete new version of the row, leaves the old one in place until VACUUM, or the pruning that runs when a later query visits the page, can show that no snapshot still needs it, and then has to make the indexes find the new version, which normally costs one new entry in every index even when no indexed value changed. For our three-index table, labelling every row means a new heap tuple per row and three index entries to go with each, unless the update qualifies as HOT.

Whether an update is a heap-only tuple update gets settled inside heap_update in heapam.c, and the first thing it checks is whether the new version fits in the free space of the page that holds the old one.

	pagefree = PageGetHeapFreeSpace(page);

	newtupsize = MAXALIGN(newtup->t_len);

	if (need_toast || newtupsize > pagefree)

A version that fails this test is written to another page, and a version on another page cannot be HOT. Past the test, the code reaches a branch commented "Since the new tuple is going into the same page, we might be able to do a HOT update. Check if any of the index columns have been changed." It sets use_hot_update only when bms_overlap(modified_attrs, hot_attrs) comes back false, meaning that none of the columns the UPDATE changed is one an index depends on.

Neither category nor confidence is indexed here, so the second condition held in every run except the one designed to break it, and everything came down to free space on the page. Loaded at fillfactor 100, a table has almost none, because the load packs each page as full as it will go. Of the 1 000 000 updates in the range run without VACUUM, 58 found room on their own page, and every other new version went to a different page and added an entry to each of the three indexes on its way.

  heap  | indexes |  wal   
--------+---------+--------
 548 MB | 70 MB   | 876 MB
(1 row)

The heap in that output holds a live and a dead copy of every row. Splitting the work into ten statements did nothing for the heap on its own, since nothing between the batches reclaimed the dead versions, and one UPDATE over all million rows landed on the same heap size with 74.8 MB of indexes and zero HOT updates.

VACUUM between range batches

Batching exists mostly for the lock side of a backfill, keeping each transaction short so that writers are not left queueing behind row locks on the whole table, and it usually comes with advice to VACUUM between batches, which our runs bore out for the heap.

A plain VACUUM tickets after every range batch at fillfactor 100 kept the heap to 306.0 MB. After the first batch, each batch's new versions went into the space the previous batch's dead versions had left, which VACUUM had just handed back to the free space map, so the heap hardly grew from there on.

HOT finished at the same 58 updates as the run with no VACUUM at all, because the space VACUUM frees sits on pages whose rows are already labelled, while the next range batch rewrites rows on pages that are still packed. Those new versions landed in the freed space as ordinary updates, each one writing an entry into every index.

As a result the indexes still ended at 65.0 MB, keeping most of the growth the run without VACUUM showed. Although VACUUM removes dead entries from a B-tree, the leaf pages that split to make room for the new ones stay allocated, and a plain VACUUM does not shrink a B-tree index file, so the freed space waits inside the index for later entries whose keys belong on those pages. Nor did the WAL come down, since the VACUUM passes write records of their own and pushed the total slightly past the run without them.

The middle line of the chart is the same strategy at fillfactor 90, climbing a few megabytes per batch after its first-batch jump, mostly in the indexes.

What a 10% reserve does for range batches

Setting fillfactor below 100 is the standard answer to updates that cannot stay on their page, since inserts then leave part of each page empty for later versions of the rows already on it. A table at 90 starts bigger than one at 100, and settings below 90, which we did not measure, buy their extra room the same way, by making the whole table larger before the first label is written. The range batch that ran against the fillfactor 90 table used the label expression every run shared.

update tickets
   set category   = (array['billing','login','export','api','invoice','sso',
                           'refund','bug','feature','security','outage','other'])
                    [1 + abs(hashtext(subject)) % 12],
       confidence = (0.5 + (abs(hashtext(body)) % 500) / 1000.0)::real
 where id between :lo and :hi;   -- 100 000 ids per batch
vacuum tickets;

Of the 1 000 000 updates, 115 059 (11.5%) were HOT. Heap and indexes grew about as much as in the fillfactor 100 run with the same VACUUM, as the second and third rows of the table show.

Because every row on a page falls inside the same id range, one range statement asks the page to hold new versions for all of its rows at once, and a reserve of a tenth of the page has room for only a few of them, which become the HOT updates. Beyond those few, the rows leave the page exactly as they would at fillfactor 100, each adding an entry to every index, and the VACUUM that follows frees their old space too late to matter, because the next range lives on other pages.

Interleaving the batches with id % 10

Interleaving asks less of each page at a time. Keep the same SET list, replace the range with a remainder, run the statement once for each value of r from 0 to 9, and VACUUM after each run. Since VACUUM refuses to run inside a transaction block, no function or DO block can drive the loop, which leaves psql or a small script.

update tickets
   set category   = (array['billing','login','export','api','invoice','sso',
                           'refund','bug','feature','security','outage','other'])
                    [1 + abs(hashtext(subject)) % 12],
       confidence = (0.5 + (abs(hashtext(body)) % 500) / 1000.0)::real
 where id % 10 = :r;
vacuum tickets;

Because the table was loaded in id order, a page holds a run of consecutive ids, and each remainder takes one row in ten from every page. That tenth mostly fits in the reserve, and by the time the next remainder comes around, VACUUM has freed the old versions of the last one, so the room is back on the same page. We did not measure a table whose rows have drifted out of id order, where each id range is already scattered across many pages and range batches should hurt less.

On 18.6, 961 194 of the 1 000 000 updates were HOT, and the fourth row of the table shows heap and indexes barely moving while the WAL came to a little over half of what range batches wrote. In the chart, the id % 10 line holds at 336 MB for the first six batches before ending at 349 MB.

At fillfactor 100 the same interleaving reached only 20.6% HOT and let the indexes grow to 52.5 MB, so the reserve matters even though VACUUM opens a gap on every page after the first batch, for a reason in hio.c that the section on existing tables comes back to. This run is also the one where 19 Beta 4 disagreed with 18.6, counting 206 449 HOT updates to 18.6's 206 441.

What n_tup_hot_upd tells you early

The HOT share lives in pg_stat_user_tables, and the time to read it is after the first batch, while a change of plan still costs little.

select n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) hot_pct,
       n_dead_tup, n_live_tup
  from pg_stat_user_tables
 where relname = 'tickets';

After the interleaved run at fillfactor 90 it returned this.

 n_tup_upd | n_tup_hot_upd | hot_pct | n_dead_tup | n_live_tup 
-----------+---------------+---------+------------+------------
   1000000 |        961194 |    96.1 |          0 |    1000075
(1 row)

For the range batches on an identical table, the same query came back with a different second column.

 n_tup_upd | n_tup_hot_upd | hot_pct | n_dead_tup | n_live_tup 
-----------+---------------+---------+------------+------------
   1000000 |        115059 |    11.5 |          0 |     683916
(1 row)

Leave n_live_tup out of any conclusion, since it is an estimate and the pg_stat_reset() at the start of each run zeroed it. One batch was enough to tell the two runs apart, and on the interleaved run the ratio read between batches never dropped below the final 96.1%. Because pg_stat_reset() clears the counters for the whole database, a shared server calls for pg_stat_reset_single_table_counters('tickets'::regclass), which resets only this table, or for recording n_tup_upd and n_tup_hot_upd before the first batch and subtracting afterwards, and size and WAL are measured the same way once the WAL position is saved up front.

create table _lsn as select pg_current_wal_lsn() l;   -- before the first batch

select pg_size_pretty(pg_relation_size('tickets')) heap,
       pg_size_pretty(pg_indexes_size('tickets')) indexes,
       pg_size_pretty(pg_current_wal_lsn() - (select l from _lsn)) wal;

  heap  | indexes |  wal   
--------+---------+--------
 312 MB | 36 MB   | 455 MB
(1 row)

could not extend file: No space left on device

To see what the doubling does on a small disk, we put the fillfactor 90 table and all of its indexes into a tablespace on a 420 MB tmpfs, which was 81% full after the load with 337M used and 84M available, and then ran the backfill as a single UPDATE over every row.

psql:/tmp/disk.sql:24: ERROR:  could not extend file "pg_tblspc/16384/PG_18_202506291/5/16386": No space left on device
HINT:  Check free disk space.
Time: 4289.510 ms (00:04.290)

The statement rolled back and labelled nothing, yet it left the filesystem at 100% used, and a VACUUM afterwards got it back only to 355M, which is still more than the 337M it held before the attempt. On a fresh container with the same tablespace, all ten id % 10 batches finished, with a VACUUM after each.

Filesystem      Size  Used Avail Use% Mounted on
tmpfs           420M  349M   72M  84% /mnt/small
select count(*) filter (where category is not null) labelled, pg_size_pretty(pg_relation_size('tickets')) heap, pg_size_pretty(pg_indexes_size('tickets')) indexes from tickets
 labelled |  heap  | indexes 
----------+--------+---------
  1000000 | 312 MB | 36 MB
(1 row)

Each VACUUM had handed the space of the old versions back before the next batch needed it, so the run never asked the disk for room for a second copy of the table, and its n_tup_hot_upd came to the same 961 194 as the interleaved run on a filesystem with room to spare.

Checkpoints in the middle of a backfill

Our runs so far had one checkpoint each, issued before the first batch, but a backfill that runs for hours on a production server sees many more, because checkpoints arrive on a timer and whenever WAL passes max_wal_size. To measure what they cost, we added to the comparison a hybrid that walks ten ranges of 100 000 ids and runs all ten remainders inside each range, with a VACUUM after each pass, before moving on, and then reran all three batch orders with a CHECKPOINT after every 100 000 rows, which for the hybrid meant one per range.

Under those checkpoints the WAL of range batches rose only moderately, to 1 025 MB from 850 MB. The interleaved run nearly tripled the range figure at 3 022 MB, even though its HOT count stayed at 961 194 and the table and its indexes ended at the same sizes as before.

With full_page_writes on, as it is by default and was here, the first change to any page after a checkpoint writes a full-page image of it into the WAL, and an interleaved batch changes every heap page because every page holds rows of every remainder, so each checkpoint set up a fresh image of the entire heap for the next batch to write. Range batches paid correspondingly less for the same checkpoints, since each one changes its own tenth of the heap plus the pages its moved rows land on.

Turning on wal_compression = lz4 in the session that ran the updates, which takes superuser or a granted SET privilege, shrank those images without closing the gap, leaving range batches at 753 MB and the interleaved run at 1 011 MB. A checkpoint after every batch is a harsh schedule, and where a real run lands between the two halves of the chart depends on how many checkpoints its batches span.

A hybrid that interleaves one range at a time

Confining the interleaving to one slice of the table at a time is what keeps the hybrid cheap under checkpoints, because after a checkpoint between ranges only the pages of the next range, plus the few that moved rows land on, need a fresh image.

update tickets
   set category   = (array['billing','login','export','api','invoice','sso',
                           'refund','bug','feature','security','outage','other'])
                    [1 + abs(hashtext(subject)) % 12],
       confidence = (0.5 + (abs(hashtext(body)) % 500) / 1000.0)::real
 where id between :lo and :hi
   and id % 10 = :r;
vacuum tickets;
-- ten passes per range, one per remainder; after the tenth, move :lo and :hi on

The hybrid wrote 455.4 MB of WAL without mid-run checkpoints, about as much as plain interleaving did, and a checkpoint after every range raised that only to 544 MB, with 961 198 HOT updates that round to the same 96.1%.

By wall clock it was the slowest order on 18.6, taking 71.3 s where plain interleaving took 28.1 s and range batches 31.3 s. The figures are loose, since each includes eleven 0.7 s measurement pauses, one at the start and one after every 100 000 rows. Those pauses are identical across the three runs, so the gap comes from the hybrid issuing a hundred UPDATE and VACUUM pairs where the other two issue ten, and with ten times the UPDATEs as well as ten times the VACUUMs we would not put all of it down to VACUUM.

We would still run the hybrid for a long backfill on a live table, with each range small enough to finish well inside one checkpoint interval, although we measured it on one table shape only, so a table with wider rows or more indexes should have its n_tup_hot_upd read after the first range before anyone commits a night of batches to it.

Fillfactor on a table that already exists

A table created without a fillfactor setting carries the default of 100, and ALTER TABLE ... SET (fillfactor = 90) changes the storage parameter while leaving every existing page as it was. The ALTER alone, which we expected to do nothing for a backfill, still gave the same interleaved batches 86.2% HOT (861 784 updates) on a table loaded at fillfactor 100, with the indexes at 39.8 MB and 491.8 MB of WAL.

With every page still packed, almost none of the first batch's updates were HOT, which accounts for the missing tenth, and the VACUUM after it opened a gap of about one row in ten on every old page. What happened next was decided in hio.c, where RelationGetBufferForTuple chooses a page for a tuple that cannot stay where it was.

	/* Compute desired extra freespace due to fillfactor option */
	saveFreeSpace = RelationGetTargetPageFreeSpace(relation,
												   HEAP_DEFAULT_FILLFACTOR);
	...
		targetFreeSpace = len + saveFreeSpace;

Our reading of the two files together is that a tuple moving to another page needs its own length plus the fillfactor reserve free on the target page, while a tuple staying on its own page is compared only with the page's free space. That rule keeps rows arriving from elsewhere out of the small gaps VACUUM opened on a table whose target is 90, so the gaps stay free for HOT updates of the rows that live on those pages. Leave the target at 100 and the reserve is zero, so a row that leaves its page may drop into any gap that fits it, including gaps on pages the batch has not reached yet, and we take that to explain the 20.6% interleaving managed on a table created at 100.

On 18.6, a VACUUM FULL after the ALTER repacked every page at 90, and the backfill that followed came out at the same 961 194 HOT updates as a table created at 90, at the price of an ACCESS EXCLUSIVE lock held through the whole rewrite.

PostgreSQL 19 adds REPACK, and on 19 Beta 4 we ran its concurrent form after the same ALTER.

=> repack (concurrently) tickets
REPACK

Run after it, the backfill also reached 961 194 HOT updates, with 434.2 MB of WAL. Beta 4 came out on 24 September 2026 and several features were reverted during this beta cycle, so treat the result as a preview of a command that may still change before general availability, which is targeted for October 2026.

According to the REPACK documentation, the command rewrites the table "into a new disk file with no extra space (except for the space guaranteed by the fillfactor storage parameter)", the step the ALTER alone leaves undone. With CONCURRENTLY it takes the ACCESS EXCLUSIVE lock only to swap the table and index files, capturing the changes made during the copy through logical decoding. Unlike plain REPACK, the concurrent form cannot be used on a table that lacks a primary key and index-based replica identity, and either form needs free disk space at least equal to the table plus its indexes.

Its warning that REPACK with CONCURRENTLY is not MVCC-safe points to Section 13.6, which explains that once the rewrite commits, a transaction whose snapshot predates the commit, and which had not touched the table before REPACK started, sees the table as empty. In a long report that reads several tables in one snapshot, tickets can therefore come back empty beside tables that are not. Transactions that had already read the table are spared, because the lock they hold makes the file swap wait until they end.

What switches HOT off partway through a backfill

Some additions that look harmless took HOT away from runs otherwise set up to keep it, and a review-queue index is the easiest to add without noticing. Built before the backfill so that low-confidence rows can be found as soon as they are labelled, it is one line.

create index tickets_review_idx on tickets (id) where confidence < 0.6;

With that index in place, the interleaved run at fillfactor 90 recorded no HOT updates at all.

 n_tup_upd | n_tup_hot_upd | hot_pct | n_dead_tup | n_live_tup 
-----------+---------------+---------+------------+------------
   1000000 |             0 |     0.0 |          0 |    1000364
(1 row)

  heap  | indexes |  wal   
--------+---------+--------
 309 MB | 61 MB   | 690 MB
(1 row)

The index is on id and holds nothing while every confidence is NULL, yet it was enough, because Postgres counts a column named in an index predicate as an indexed column when it decides whether an update can be HOT, and the backfill changes confidence on every row. Together the three regular indexes and the review index came to 61.4 MB, where the same run without it finished at 36.4 MB. Building the review index after the last batch, with CREATE INDEX CONCURRENTLY if the table is taking writes, keeps it out of the way.

Work queues can bring the range problem back without anyone writing a range. Claiming batches with FOR UPDATE SKIP LOCKED is a common way to spread a backfill over workers, and we ran the claim from one session, in id order, at fillfactor 90 with a VACUUM after each batch.

with batch as (
  select id from tickets where category is null
  order by id limit 100000 for update skip locked)
update tickets t
   set category   = (array['billing','login','export','api','invoice','sso',
                           'refund','bug','feature','security','outage','other'])
                    [1 + abs(hashtext(subject)) % 12],
       confidence = (0.5 + (abs(hashtext(body)) % 500) / 1000.0)::real
  from batch
 where t.id = batch.id;

It matched the range batches to the row at 115 059 HOT updates, because order by id limit 100000 hands out a contiguous block of ids, and on this table a contiguous block of ids is a contiguous block of pages. Interleaving would survive a queue only if the claim itself interleaved, for instance by adding the remainder to its WHERE clause, which is one variant we have not run.

Keeping the model's whole answer in the row costs HOT in a different way. A Choice answer carries a probability for every option, and storing that vector next to the label makes each new version larger than the reserve was sized for. Held in a real[] column, the twelve values still left 77.7% of the updates HOT, though the heap grew to 387.6 MB. A jsonb object keyed by label does far worse, because it repeats every key name in every row, and storing the same numbers that way halved the HOT share to 50.0% while the heap doubled from 300.4 MB to 601.0 MB.

With the label and confidence written into tickets and the probabilities inserted into ticket_probs (id bigint primary key, probs real[] not null) alongside each batch, the HOT share came back, and tickets stayed at 96.1% while the side table took 134.2 MB.

Labels in a table of their own

The cheapest backfill we ran left the big table untouched and wrote the labels somewhere else.

create table labels (id bigint primary key, category text not null, confidence real not null);

insert into labels
select id,
       (array['billing','login','export','api','invoice','sso',
              'refund','bug','feature','security','outage','other'])
       [1 + abs(hashtext(subject)) % 12],
       (0.5 + (abs(hashtext(body)) % 500) / 1000.0)::real
  from tickets
 where id between :lo and :hi;

While the side table grew to 69.5 MB, tickets kept its 269.3 MB of heap and 35.6 MB of indexes, and no other strategy we tried wrote less WAL than this run's 138.0 MB. The price is a join on every read that wants a label, plus a delete path on tickets that has to reach labels as well.

For a copy of the table with the labels already filled in, an insert ... select into a new tickets_new, followed by the primary key and both indexes, wrote 330.7 MB of WAL and produced 314.7 MB of table and indexes while the old heap and indexes stayed on disk until dropped. Writes that reach the old table during the copy have to be blocked or replayed before a rename swaps the two, and the rename itself takes an ACCESS EXCLUSIVE lock.

Frequently asked questions

Related guides

• For the locks a batched backfill holds and the writers it keeps waiting, see migration testing at production volume.
• What pg_stat_statements forgets measures which statements that view silently drops.
• Before switching triggers and foreign keys off for a backfill, read what replica mode does not switch off.
• Copying data out and back in also rewrites a table, and the dump that breaks its own restore covers where that goes wrong.