TID Range Scans for Batched Table Processing #

A backfill must update a column on all 900 million rows of an events table in small batches. The table has no convenient monotonic key: id values are UUIDs, and created_at is not indexed. Batching by LIMIT … OFFSET gets slower with every batch, and a new index for the job would take hours to build. There is a way to slice the table by physical location that needs no index at all.

Access paths are usually chosen among sequential, index and bitmap scans, as described in sequential vs index scans. PostgreSQL 14 added a fourth for exactly this job: the TID Range Scan.

The Planner Condition #

Every row version has a ctid — its tuple identifier, (block, offset): the page number and the slot within that page. A ctid is not a stable key: updates and VACUUM FULL move rows. But at any moment, ctid order is physical order, and a range of ctids is a contiguous range of pages.

From PostgreSQL 14, a predicate of the form

WHERE ctid >= '(100000,0)' AND ctid < '(200000,0)'

is planned as a Tid Range Scan: the executor seeks directly to block 100000 and reads sequentially until block 200000. No index is needed, and the cost is proportional to the pages in the range, not to the table. Before 14, the same predicate forced a full sequential scan with a filter.

That makes ctid ranges ideal for batch processing where the order of processing does not matter:

The number of pages is available from pg_relation_size('events') / current_setting('block_size')::int, so the full set of batches can be computed up front.

Slicing a table by physical page ranges A horizontal axis of heap block numbers from 0 to 11.2 million. Markers at block 0, 100 thousand, 200 thousand, and so on show batch boundaries. Each batch is a TID Range Scan from one boundary to the next, reading only those pages. block 0 batch 1 starts block 100000 batch 2 block 200000 batch 3 … more batches block 11200000 last page each batch reads only its own pages, whatever the table size

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE ctid >= '(100000,0)'::tid AND ctid < '(200000,0)'::tid;

PostgreSQL 14 and later:

Aggregate  (actual time=1804.2..1804.2 rows=1 loops=1)
  ->  Tid Range Scan on events  (actual time=0.03..1622.8 rows=8040112 loops=1)
        TID Cond: ((ctid >= '(100000,0)'::tid) AND (ctid < '(200000,0)'::tid))
        Buffers: shared hit=2210 read=97790
Execution Time: 1804.6 ms
-- 100,000 pages read: exactly the requested range

PostgreSQL 13, same query:

Aggregate  (actual time=162104.2..162104.2 rows=1 loops=1)
  ->  Seq Scan on events  (actual rows=8040112 loops=1)
        Filter: ((ctid >= '(100000,0)'::tid) AND (ctid < '(200000,0)'::tid))
        Rows Removed by Filter: 892004120
        Buffers: shared hit=40210 read=11159790
Execution Time: 162104.9 ms
-- whole table read and filtered
Confirming the range is read directly Three fields are annotated. The node name Tid Range Scan confirms the executor seeks to the range. TID Cond shows the ctid bounds. Buffers of about 100 thousand match the page count of the range, not the table. Tid Range Scan on events seek to range, PostgreSQL 14+ TID Cond: ((ctid >= '(100000,0)') …) bounds applied as a scan range Buffers: shared hit=2210 read=97790 ≈ pages in range, not in table a Seq Scan with a ctid Filter means the server predates this node

Step-by-Step Resolution #

  1. Compute the number of heap blocks.

    SELECT pg_relation_size('events') / current_setting('block_size')::int AS blocks;

  2. Choose a batch size in blocks. Rows per block varies with row width; derive it from reltuples / relpages and pick a range that yields a manageable number of rows — 10,000 to 50,000 rows per transaction is typical for updates.

  3. Process each range in its own transaction.

    UPDATE events
SET region_code = upper(region)
WHERE ctid >= '(100000,0)'::tid AND ctid < '(100250,0)'::tid
  AND region_code IS NULL;

    The idempotence condition AND region_code IS NULL makes the batch safe to re-run.

  4. Account for moved rows. An UPDATE writes new row versions, possibly to other pages — including pages in ranges not yet processed or already processed. The idempotence predicate handles re-visits; a final pass of WHERE region_code IS NULL catches rows moved backwards into finished ranges.

  5. Let vacuum keep up. Each batch creates dead tuples; pause between batches or run VACUUM periodically so the table does not bloat, as described in autovacuum thresholds and dead-tuple drift.

  6. Parallelise reads, not overlapping writes. Exports can run several ranges concurrently; concurrent updates on adjacent ranges can contend for the same pages when rows move.

Cost of the fifty-thousandth batch With LIMIT and OFFSET on an unordered table, the fifty-thousandth batch of ten thousand rows must skip 500 million rows first. With a TID range, the same batch reads about 1,250 pages, the same as the first batch. OFFSET batch #50,000 ≈ 6.2M pages skipped + read TID range batch #50,000 ≈ 1,250 pages TID range cost is constant per batch; OFFSET cost grows with position

Before and After #

-- BEFORE: LIMIT 10000 OFFSET n batching, late batch
Limit → Seq Scan on events  (actual rows=500010000 …)                  Execution Time: 118204.6 ms

-- AFTER: ctid range batch, same position
Tid Range Scan on events  TID Cond: (ctid >= '(5000000,0)' AND ctid < '(5001250,0)')   Execution Time: 38.4 ms

What ctid is not #

A ctid identifies a row version’s current location, and that location changes. Never store a ctid as a reference, never use it to re-find a row later in another transaction, and never expose it to an application as an identifier. It is safe for batching only because each batch re-evaluates the predicate against current data and the job is written to be idempotent. Within a single statement, a ctid is stable, which is why patterns such as DELETE … WHERE ctid IN (SELECT ctid … LIMIT 10000) work for deleting in chunks without a key — though with an index on a real key, keyset batching is clearer and survives concurrent updates better, as covered for application paging in pagination plans.

Partitioned tables have one ctid space per partition, so run the batching per partition rather than against the parent.

Sizing batches and pacing the job #

Physical batching makes cost per batch predictable, which is exactly what a long-running backfill needs. Measure the first few batches: rows touched, execution time, WAL generated (EXPLAIN (ANALYZE, WAL) on a representative range inside a rolled-back transaction gives a good estimate), and replication lag on any standbys while the job runs. Then choose a block range that keeps each transaction short — a few hundred milliseconds is a comfortable target — so row locks are held briefly and autovacuum can reclaim dead tuples between batches. A job that finishes each batch in 300 ms and sleeps for 100 ms processes around nine million rows an hour on a table with 25 rows per page and 1,250-page ranges, while leaving headroom for foreground traffic. Record the last completed block in a small progress table after each batch commits; if the job is interrupted, it resumes from that block, and the idempotence predicate makes the overlap harmless.

Common Pitfalls #

Using ctid ranges on PostgreSQL 13 or earlier. The predicate becomes a full-table filter. Diagnostic signal: Seq Scan with Filter: (ctid …). Fix: upgrade, or batch on an indexed key.

Assuming each range contains the same rows on re-run. Updates move rows between pages. Diagnostic signal: rows processed twice or missed. Fix: idempotent predicates and a final sweep.

Very small ranges on sparse tables. After large deletes many pages are empty. Diagnostic signal: batches returning zero rows. Fix: size batches in blocks but log rows touched, and widen ranges where density is low.

Batching the partitioned parent. ctids are per partition. Diagnostic signal: ranges returning rows from many partitions unpredictably. Fix: iterate over partitions and batch within each.

Frequently Asked Questions #

What is a TID Range Scan? #

A scan node, added in PostgreSQL 14, that reads only the heap pages between two ctid bounds. It lets a query process a physical slice of a table without an index and without reading the rest of the table.

Is it safe to use ctid for batching updates? #

Yes, if each batch is idempotent and you accept that updated rows can move to other pages. Re-evaluate a condition such as “column still null” in every batch and finish with a sweep for any rows that were missed.

Why not use LIMIT and OFFSET to batch through a table? #

OFFSET has to read and discard every skipped row, so each batch costs more than the last. TID ranges and keyset conditions seek directly to the next slice, keeping batch cost constant.

Up: Sequential vs Index Scans