PostgreSQL 14.2 repairs index maintenance around HOT and TOAST

An index can become incorrect even when the application has done nothing unusual with its SQL. PostgreSQL uses internal storage structures to avoid unnecessary index writes and to store values that cannot fit inside an ordinary row. Those structures still need to obey the rules that let concurrent maintenance identify which rows an index should reference.

The February 10 maintenance announcement includes PostgreSQL 14.2 and updates to the other supported branches. Two index-corruption repairs deserve particular attention. One concerns heap-only tuple chains during cleanup. The other concerns the locks held while updating TOAST data during a concurrent index rebuild.

I value PostgreSQL for making ordinary relational work predictable. A maintenance release that explains a storage failure in enough detail to distinguish prevention from repair supports that confidence. It also requires a more specific response than assuming that replacing the executable reconstructs existing data structures.

HOT avoids unnecessary index entries

The PostgreSQL 14 explanation of heap-only tuples starts with the cost of multiversion concurrency control. Updating a row creates another row version. Without an optimization, that can require another set of index entries as well as eventual cleanup of the old versions.

HOT applies when an update changes no column referenced by an index and the old row page has enough free space for the new version. Index references can continue to lead into the row-version chain without a new index entry for each eligible update. The restriction includes columns referenced by expression indexes and partial indexes. An apparently unindexed business field may therefore still participate in an index definition.

This design makes page-local relationships significant. A reference into the page must continue to identify the intended row versions even after cleanup removes obsolete versions. Reusing storage is normal, but an old reference cannot acquire a new meaning merely because another row occupies the same space.

The documentation also explains the space tradeoff. Reducing a table fillfactor can leave more room for subsequent updates on the same page, increasing the opportunity for HOT. That is a property of the storage arrangement, not a guarantee that every update will avoid index work. Index dependencies still determine whether the update qualifies.

Cleanup must preserve the meaning of a reference

The 14.2 release notes describe a rare transition during pruning. A recently dead tuple could become fully dead and be removed while a redirect still pointed at its item slot. If another tuple later reused that slot, the redirect could make it appear to belong to the earlier HOT chain.

The danger is the relationship between an index lookup and a row version. A location alone does not carry enough meaning after reuse. The repair must preserve the chain rules as tuples change state and their slots become available again. This is a correctness condition inside the optimization, independent of whether the application deliberately requested any special maintenance behavior.

The notes say reindexing repairs damage from this case. They also call the scenario extremely unlikely and do not recommend rebuilding indexes merely because it might have occurred. That distinction limits the operational recommendation to the evidence available for an installation.

TOAST has its own indexed rows

The TOAST documentation explains a different storage problem. PostgreSQL uses fixed-size pages and does not allow a tuple to span pages. Large field values can therefore require compression or storage outside the main row. A small reference in the main row identifies the out-of-line value.

For on-disk out-of-line storage, PostgreSQL divides a value into chunk rows in an associated TOAST table. A chunk identifier groups the pieces belonging to one value, and a sequence number orders those pieces. A unique index over those two fields supports retrieving the value. An application can see one logical field while the database maintains several physical rows and their index entries.

That separation keeps the main table smaller. Queries that need only small fields can work with the main row without fetching every large value. The storage mechanism handles reconstructing an out-of-line field when it is needed. Its index is therefore part of reading an ordinary application value, even if the application never names the TOAST table.

The 14.2 notes explain that TOAST updates released their row-exclusive locks too early. Concurrent reindexing depended on those locks lasting until commit. The fix restores that lifetime, and the notes direct administrators to reindex again to repair an already corrupted TOAST index.

I find this case useful because it identifies the failed assumption precisely. Concurrent maintenance coordinates with transactions through locking rules. An internal table that relaxes those rules can invalidate a maintenance operation even though the logical field looks unchanged to the application interface.

Applying a repair has two parts

Updating within the 14 series does not require a dump and restore. The announcement describes stopping PostgreSQL and updating its binaries, while noting that older skipped maintenance releases can have their own additional steps. That changes the implementation used for future operations.

Existing index damage is a separate matter. The release identifies the relevant cases and gives different guidance for them, so a blanket rebuild recommendation would obscure useful information. The TOAST locking defect and the rare HOT-chain defect explain why an administrator needs to connect maintenance history with the affected storage structures when deciding which indexes require repair.