---
title: "Create the new column's index after the backfill | Proofline"
description: "An index on the new column makes every backfill update add index entries, because PostgreSQL can no longer use its cheaper heap-only update."
url: https://proofline.sh/learn/postgres-backfill-hot-updates-index-order/
---

[Learning center](/learn/)

# Create the new column's index after the backfill

An index on the new column makes every backfill update add index entries, because PostgreSQL can no longer use its cheaper heap-only update. In our lab, creating the index before backfilling a million rows produced 16% to 28% more WAL and an index about twice the size of one built afterwards. Backfill first, then build the index with CREATE INDEX CONCURRENTLY.

Capacity overruns The Proofline team November 22, 2026 3 min read

## The lab

`orders` has 1,000,000 rows and an empty `state`. The new release will query by `state`, so the migration adds an index. We backfilled in batches of 10,000 with the index created before and after, on PostgreSQL 16.15, with autovacuum off so that each run started from the same table. We repeated it on a table created with `fillfactor = 70`, which leaves free space in each page, as a table with past updates and vacuums usually has.

| Table | Index | WAL | Heap-only updates | Index size |
| --- | --- | --- | --- | --- |
| Full pages | before | 423 MB | 0 | 13 MB |
| Full pages | after | 364 MB | 2,454 | 6.8 MB |
| Fillfactor 70 | before | 402 MB | 0 | 15 MB |
| Fillfactor 70 | after | 314 MB | 388,200 | 6.8 MB |

Each run updated 1,000,000 rows. The WAL figures include building the index afterwards, which took 0.2 to 0.3 s. Heap-only counts come from `pg_stat_user_tables.n_tup_hot_upd`, and the index size is that of the `state` index. The backfill itself took 5.3 s and 6.3 s with the index present, against 4.5 s and 3.9 s without it.

## Why the order matters

A heap-only tuple (HOT) update writes the new row version in the same page and adds no index entries, but only when no indexed column changes and the page has room. The [HOT documentation](https://www.postgresql.org/docs/16/storage-hot.html) describes both conditions. With an index on `state`, every backfill update changes an indexed column, so none can be heap-only: each one adds an entry to the `state` index and to the primary key index.

The `state` index built during the backfill also ends up larger. It received a million inserts in key order with old entries still present, so its pages split and stayed half full. Built afterwards, it is written once, packed, from the final values.

On a table with full pages, heap-only updates are rare either way, because there is no room in the page for the new version. The index order still saves WAL and index size there. On a table with free space, building the index afterwards also lets PostgreSQL keep about 39% of the updates out of the indexes.

## What it changes in the release order

The new release cannot rely on the index until it exists, so its queries by `state` need to wait for the backfill and the index build. Build it with `CREATE INDEX CONCURRENTLY` so writes continue, and check that it is valid afterwards; [retrying a failed CREATE INDEX CONCURRENTLY](/learn/postgres-create-index-concurrently-retry/) shows why. If the new release has to query by `state` from its first pod, a partial index `WHERE state IS NOT NULL`, created before the backfill, avoids neither cost: its predicate changes for every row, so each update still adds an entry.

## The rehearsal

On a copy at production size, run one batch with the index present and one without, and compare `pg_current_wal_lsn()` before and after each and `n_tup_hot_upd`. Pass when the order you choose keeps WAL within what your replicas and backups absorb, as [backfill replica lag comes from replay conflicts too](/learn/postgres-backfill-replica-lag/) discusses. The table growth that follows a backfill is in [vacuum between backfill batches to keep the table from doubling](/learn/postgres-backfill-dead-tuples-vacuum/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Vacuum between backfill batches to keep the table from doubling](/learn/postgres-backfill-dead-tuples-vacuum/)
    
    Capacity overruns The Proofline team November 22, 2026 3 min read
    
-   ### [Retrying a failed PostgreSQL CREATE INDEX CONCURRENTLY](/learn/postgres-create-index-concurrently-retry/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
