F.2. amcheck

The amcheck module provides functions that allow you to verify the logical consistency of the structure of indexes. If the structure appears to be valid, no error is raised.



amcheck functions may only be used by superusers.

F.2.1. Functions

bt_index_check(index regclass) returns void


test=# SELECT bt_index_check(c.oid), c.relname, c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'
AND c.relpersistence != 't'
-- Function may throw an error when this is omitted:
AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC LIMIT 10;
 bt_index_check |             relname             | relpages 
                | pg_depend_reference_index       |       43
                | pg_depend_depender_index        |       40
                | pg_proc_proname_args_nsp_index  |       31
                | pg_description_o_c_o_index      |       21
                | pg_attribute_relid_attnam_index |       14
                | pg_proc_oid_index               |       10
                | pg_attribute_relid_attnum_index |        9
                | pg_amproc_fam_proc_index        |        5
                | pg_amop_opr_fam_index           |        5
                | pg_amop_fam_strat_index         |        5
(10 rows)

This example shows a session that performs verification of the 10 largest catalog indexes in the database test. Since no error is raised, all indexes tested appear to be logically consistent. Naturally, this query could easily be changed to call bt_index_check for every index in the database where verification is supported.

bt_index_check acquires an AccessShareLock on the target index and the heap relation it belongs to. This lock mode is the same lock mode acquired on relations by simple SELECT statements. bt_index_check does not verify invariants that span child/parent relationships, nor does it verify that the target index is consistent with its heap relation. When a routine, lightweight test for corruption is required in a live production environment, using bt_index_check often provides the best trade-off between thoroughness of verification and limiting the impact on application performance and availability.

bt_index_parent_check(index regclass) returns void

bt_index_parent_check tests that its target, a B-Tree index, respects a variety of invariants. The checks performed by bt_index_parent_check are a superset of the checks performed by bt_index_check. bt_index_parent_check can be thought of as a more thorough variant of bt_index_check: unlike bt_index_check, bt_index_parent_check also checks invariants that span parent/child relationships. However, it does not verify that the target index is consistent with its heap relation. bt_index_parent_check follows the general convention of raising an error if it finds a logical inconsistency or other problem.



F.2.2. Using amcheck effectively


  • Structural inconsistencies caused by incorrect operator class implementations.



  • Corruption caused by hypothetical undiscovered bugs in the underlying PostgreSQL access method code or sort code.

    Automatic verification of the structural integrity of indexes plays a role in the general testing of new or proposed PostgreSQL features that could plausibly allow a logical inconsistency to be introduced. One obvious testing strategy is to call amcheck functions continuously when running the standard regression tests. See Section 32.1 for details on running the tests.






F.2.3. Repairing corruption

No error concerning corruption raised by amcheck should ever be a false positive. In practice, amcheck is more likely to find software bugs than problems with hardware. amcheck raises errors in the event of conditions that, by definition, should never happen, and so careful analysis of amcheck errors is often required.
