skip to content

After an OS upgrade on a PostgreSQL 18 host, how would you use amcheck to check product_category's B-tree indexes for corruption without blocking writes?

level: seniorimportance: nice to knowfreq 21%

answer

  1. logical checks, not checksums
  2. AccessShareLock versus ShareLock
  3. heapallindexed costs time, not locks
  4. pg_amcheck for many databases

basics

~20 s

As a superuser, install amcheck and run bt_index_check on each B-tree index, optionally with heapallindexed => true. It takes only an AccessShareLock, so writes continue. Avoid bt_index_parent_check on a busy primary, because its ShareLock blocks writes.

solid answer

~50 s

An OS upgrade can change the C library's collation rules, which silently breaks the sort order of B-tree indexes on text columns such as `product_category.name`. Page checksums cannot catch that, because each page is still exactly as it was written. `amcheck` checks logical invariants instead. As a superuser, run `CREATE EXTENSION amcheck` (it is not trusted), then `SELECT bt_index_check(index => 'product_category_name_idx', heapallindexed => true)`. `bt_index_check` takes an `AccessShareLock`, the same lock as a `SELECT`, so writes continue and it also works on hot standbys. `heapallindexed` adds a pass confirming every heap tuple has an index entry, which takes longer but adds no locks. `bt_index_parent_check` also checks parent/child links, but it takes a `ShareLock` that blocks `INSERT`, `UPDATE` and `DELETE` and cannot run on a standby. Corruption is raised as SQLSTATE `XX002` (`index_corrupted`). Fix it by reindexing.

code

sql · 12 lines
sql
CREATE EXTENSION IF NOT EXISTS amcheck;   -- superuser: not a trusted extension

-- AccessShareLock only: writes keep flowing; also runs on a hot standby
SELECT c.relname,
       bt_index_check(index => c.oid, heapallindexed => true)
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am   ON am.oid = c.relam
WHERE i.indrelid = 'product_category'::regclass
  AND am.amname = 'btree'
  AND i.indisvalid AND i.indisready;
-- a corrupt index raises SQLSTATE XX002 (index_corrupted)

go deeper

for a junior

Know that amcheck is a contrib extension that checks indexes and tables for corruption, and that it is not installed by default.

for a middle

Explain bt_index_check versus bt_index_parent_check, their locks, heapallindexed, and that the B-tree checks raise errors while verify_heapam returns rows.

for a senior

Show the operational plan: collation drift after OS upgrades, checks on standbys, pg_amcheck sweeps without parent checks on busy primaries, and rebuilding what fails.

for a principal

Set the corruption-detection policy: scheduled sweeps, checksums plus logical checks, and OS upgrade procedures that treat collation changes as a data migration.

## Why amcheck exists PostgreSQL never cross-checks an index against its table during normal operation. A B-tree index is only correct as long as its comparison rules never change, and the rules for `text` come from the collation. When an OS upgrade ships a new C library with different sort rules, an index built under the old rules can be silently out of order. Lookups then miss rows, and a unique index can let duplicates in. **Data checksums**, which `initdb` enables by default in PostgreSQL 18, do not detect this: every page still matches the checksum it was written with. `amcheck` verifies **logical invariants** (key order, page links, and optionally index-to-heap agreement) using the same comparison routines that index scans use. ## The functions | Function | Checks | Lock taken | On a hot standby | |---|---|---|---| | `bt_index_check(index, heapallindexed, checkunique)` | key order within and across sibling pages | `AccessShareLock` on index and table | works | | `bt_index_parent_check(index, heapallindexed, rootdescend, checkunique)` | all of the above, plus parent/child downlinks | `ShareLock` on index and table: blocks `INSERT`/`UPDATE`/`DELETE` and `VACUUM` | not allowed | | `verify_heapam(relation, ...)` | heap pages and tuple headers, optionally TOAST | `AccessShareLock` | works | | `gin_index_check(index)` (new in 18) | GIN parent/child consistency and tree balance | `AccessShareLock` on index and table | works | The B-tree functions **raise an error** at the first problem (SQLSTATE `XX002`, `index_corrupted`). `verify_heapam` **returns one row per problem** instead. Both B-tree functions accept `heapallindexed => true`. That adds a pass which fingerprints every index tuple and confirms each live heap tuple would find an entry. It typically makes the check several times slower but takes no stronger lock. ## A plan for product_category after the OS upgrade 1. **Install and grant.** `amcheck` is not a trusted extension, so a superuser runs `CREATE EXTENSION amcheck`. Its scripts revoke `EXECUTE` from `PUBLIC`. If a monitoring role should run checks, grant it the specific functions, keeping in mind the documentation's warning that corruption reports can reveal something about the data. 2. **Check text-keyed indexes first**, since collation drift only affects collatable keys: `bt_index_check('product_category_name_idx', true)`. An `ltree` path index or a `bigint` key cannot drift with the OS collation. 3. **Check on a standby, too.** The manual notes that collation mismatches often show up between a primary and a standby running different OS major versions, and so can only be detected on the standby. `bt_index_check` runs there because it needs only `AccessShareLock`. 4. **Use parent checks off-peak or on a restored copy.** `bt_index_parent_check` finds more pathological cases, but its `ShareLock` stops writes while it runs. 5. **Check GIN indexes in 18.** If `product_category` has a GIN index (for example on a `jsonb` attributes column), `gin_index_check()` now covers it. An extension carried over by `pg_upgrade` may still be at 1.4 and need `ALTER EXTENSION amcheck UPDATE` first. ## pg_amcheck for the whole cluster The `pg_amcheck` client program runs these checks across databases in parallel: - `--all` checks every database, and `-j` sets the number of connections; - `--heapallindexed` and `--parent-check` map to the function options. Only `--parent-check` and `--rootdescend` take write-blocking locks; - `--install-missing` creates `amcheck` where it is absent. It checks tables, materialized views, sequences, TOAST and **B-tree indexes only**. GIN indexes need `gin_index_check()` in SQL. ## When it finds something - **An index error** usually means rebuilding the index. With a collation change, rebuild every index on the affected collatable columns. How to reindex without long locks, and how to track collation versions, belong to the index-upkeep and collation topics. - **A heap error** from `verify_heapam` is more serious: restore from backup or salvage the affected rows, and investigate the storage. - Silence proves less than it seems. Only the indexes you checked are verified, and with drifted collations an index can pass if its particular values happen to sort the same under both rule sets. ## Making it routine - **Run sweeps where they hurt least**: a hot standby or a restored backup takes the I/O load off the primary. `bt_index_check` and `verify_heapam` both work during recovery and skip unlogged relations there. - **Budget the I/O**: every check reads the whole index (and, with `heapallindexed`, the whole table). Keep `-j` modest on a busy server. - **Size memory for heapallindexed**: its fingerprint structure is bounded by `maintenance_work_mem`. With about 2 bytes per tuple, the chance of missing an inconsistency stays at 2% or less per tuple. With less memory it rises slowly. - **Treat OS upgrades as a trigger**: run the text-index checks before and after any upgrade that can change the C library, on every node.

  • In PostgreSQL's amcheck, when is bt_index_parent_check worth its stronger lock?
    When you need the deeper parent/child downlink checks, for example to investigate a suspected bug or a bad operator class, and can afford to stop writes on that table while it runs. Its `ShareLock` blocks `INSERT`, `UPDATE`, `DELETE` and `VACUUM`, and it cannot run on a hot standby. Run it in a maintenance window or on a restored copy of the database.
  • Why might amcheck report a collation-related index problem only on a PostgreSQL standby and not on the primary?
    A physical standby replays the primary's index pages byte for byte but compares keys using its own OS collation. If the standby runs a different OS major version, its sort rules can disagree with the order the primary built, so `bt_index_check` on the standby sees order violations the primary does not. The amcheck documentation calls this the more common case.

  • Data checksums already detect any index corruption, so amcheck adds nothing.
  • bt_index_check blocks INSERT, UPDATE and DELETE on the table while it runs.
  • heapallindexed => true takes a stronger lock than the plain check.
  • pg_amcheck also verifies GIN and GiST indexes in PostgreSQL 18.
  • A non-superuser database owner can install amcheck because it is a contrib module.

Assumes PostgreSQL 18: amcheck 1.5 adds gin_index_check(), and initdb enables data checksums by default.