PostgreSQL 14.4 repairs incomplete concurrent index builds

A concurrent index build has to describe a table that keeps changing while the build runs. Avoiding a long write lock is useful only if the resulting index still represents every row it should find. An index can be internally readable and still give an incomplete answer because a row never received an entry.

The June 16 announcement of PostgreSQL 14.4 addresses exactly that failure. An optimization introduced in PostgreSQL 14 could make CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY miss rows updated during the build. A later SELECT using the affected index could fail to find those rows. The project has issued this release specifically for PostgreSQL 14, outside the scheduled update for all supported branches.

I value PostgreSQL for its transaction behavior and the visibility that EXPLAIN gives into query execution. This issue concerns the correctness of an access path that a plan can select. Understanding the chosen plan remains useful, but a sound-looking plan cannot compensate for index entries that the build omitted.

A concurrent build coordinates several stages

The PostgreSQL 14 CREATE INDEX documentation explains why concurrent construction is more involved than a normal build. A normal build blocks writes and scans the table once. Other sessions can read, but inserts and updates wait until the index construction finishes. On a large table, that write interruption can be unacceptable.

A concurrent build allows ordinary writes to continue. In exchange, it scans the table twice and waits for transactions that could affect the result. It first creates a catalog entry for an invalid index, then performs the scans in separate transactions. Before each scan it waits for existing transactions that modified the table. After the second scan, it also waits for transactions holding older snapshots before marking the index valid.

These stages solve different problems. Scanning provides entries from the table, while waiting coordinates the build with transactions whose view of the table or modifications can overlap it. Completing the scan alone does not establish that every other transaction can safely use the index. The catalog state records whether the index is ready for that use.

The documented tradeoff is additional work and a longer total build time in exchange for permitting normal data modification. Concurrent construction can still consume CPU and I/O that other queries need. Its benefit is a different locking arrangement, rather than an absence of operational cost.

This distinction also explains why a transaction boundary matters. An ordinary index creation can run inside a transaction block. CREATE INDEX CONCURRENTLY cannot, because its work includes multiple transactions and waits. A migration procedure needs to account for that documented restriction when it chooses the concurrent form.

Missing entries differ from a visibly failed build

The 14.4 release notes identify the repair as reverting an optimization added in version 14. Affected builds can miss rows updated while an index is being created or rebuilt with the concurrent option. Its recommendation is to rebuild indexes made through that path after installing the update.

A normal failed concurrent build has a different documented outcome. A deadlock or a uniqueness violation can leave an invalid index behind. PostgreSQL ignores that index for query planning, even though maintaining it still adds overhead to updates. The client can inspect an invalid index, and the documentation describes retrying construction or rebuilding it.

The corruption addressed by 14.4 is more deceptive. The announcement describes queries using an index and failing to find rows. That behavior is consistent with an incomplete index participating in normal access, not merely an invalid artifact that the planner refuses to use. An administrator therefore cannot reduce the response to looking for visibly failed index creations.

A table row and its corresponding index entry are separate representations. The row can remain present in the table even when an index-based lookup cannot reach it. This is why the release speaks about silent corruption of indexes and why rebuilding the index can repair the stated defect. The repair reconstructs the access structure from the table.

Verification must compare the heap with the index

The announcement recommends checking B-tree indexes using pg_amcheck with --heapallindexed. Its example accepts the database name as an argument.

pg_amcheck --heapallindexed database

The option matters because the relevant question is whether rows that belong in the index have corresponding entries. A check that only examines ordering within existing index pages cannot by itself establish that an omitted entry is present. It can verify a structure whose contents remain incomplete.

The amcheck documentation describes the additional verification phase. It checks hypothetical index entries derived from the table against an in-memory summary of entries found in the index. That summary uses fingerprints and has a size bounded by maintenance_work_mem. The approach limits the memory required to compare the two representations.

This design gives verification an explicit qualification. With less memory per tuple, the chance of missing an inconsistency rises. The documentation describes the test in probabilistic terms and says that amcheck can establish the presence of corruption but cannot prove its absence. A successful run is evidence from the checks performed, not a general certificate for every property of a database.

The extra heap comparison also takes longer than the basic checks. Its purpose is broader coverage of a specific consistency relationship. That cost follows from examining table rows as well as index contents, rather than only inspecting the index structure already present.

The release announcement limits this detection recommendation to B-tree indexes. For other index types created through the affected concurrent path, it directs administrators to rebuild. That scope matters for databases using GiST or GIN alongside ordinary B-tree indexes. Successful verification of one index method does not say anything about a different method outside that check.

I find the combination of mechanism and scope useful here. The guidance explains why the additional option is necessary and where the check applies. It gives an administrator a reason to distinguish indexes by their construction history and access method.

Update before using the repaired concurrent path

The announcement directs users to install 14.4 before repairing corruption with REINDEX CONCURRENTLY. That order ensures the reconstruction uses the fixed implementation. For an illustrative existing index named orders_customer_idx, the explicit form of the command is this.

REINDEX INDEX CONCURRENTLY orders_customer_idx;

The release notes also allow rebuilding without the concurrent option. The operational choice then returns to the locking and work tradeoff described by the index documentation. A concurrent rebuild preserves availability for ordinary writes while doing more coordination. A non-concurrent rebuild uses a different maintenance arrangement. The affected application determines which interruption its operators can accommodate.

For a cluster-wide rebuild, the announcement supplies reindexdb --all --concurrently and describes its --jobs option for parallelism. A cluster-wide operation has a much broader scope than rebuilding one identified index. The source gives it as an available mechanism, not a reason to choose an unbounded number of concurrent jobs.

The update itself remains a minor-version update. It does not require a dump and restore or pg_upgrade for an installation already running the 14 series. Replacing the binaries prevents future builds from using the reverted optimization. Reconstructing existing affected indexes repairs the representations that earlier builds produced. Those are distinct changes even though both belong to completing this update.

The release keeps the failure scope specific

The project describes the problem as introduced in PostgreSQL 14 and issues 14.4 alone for it. The announcement does not direct users of every supported major version to apply a nonexistent equivalent emergency release. It separately names the next scheduled maintenance date for the supported branches. This is useful evidence about which deployed systems need the specific index response.

Version 14.4 also includes other fixes, including changes to Memoize handling and cost estimates. Those entries do not change the reconstruction requirement attached to concurrent index creation. Treating this as an undifferentiated collection of maintenance fixes would hide the part that requires action after the new binaries are installed.

The central operational record is whether an index was created or rebuilt with CONCURRENTLY on PostgreSQL 14. Where that history is uncertain, the announcement advises reindexing. Where B-tree verification finds missing entries, the rebuild addresses the known inconsistency. The repaired implementation makes future concurrent construction safe with respect to this defect, while the rebuild supplies the entries that an affected existing index lacks.