Intermediate LevelPostgreSQL
How does the amcheck extension detect physical B-Tree index corruption?
❓ Interview Prompt: Explain `amcheck` extension, `bt_index_check()`, `bt_index_parent_check()`, verifying B-Tree page invariants, and detecting disk corruption.
The 'amcheck' extension verifies the physical structural integrity of B-Tree indexes, detecting silent hardware or disk corruption before query errors occur:
•
'bt_index_check(index_oid)': Verifies index page invariants.
•
'bt_index_parent_check(index_oid)': Verifies parent/child key order relationships.
Code Implementation Example
sql
CREATE EXTENSION IF NOT EXISTS amcheck;
-- Verify all B-Tree indexes in database
SELECT c.relname, bt_index_check(c.oid)
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relam = 403 AND n.nspname = 'public'; -- 403 = B-Tree AM💡Verifying B-Tree index integrity using the amcheck extension.
🎯 Interview Key Takeaway
Run amcheck periodically in background maintenance scripts to detect silent physical B-Tree index corruption early.
#amcheck#Index Corruption#B-Tree#Intermediate
Back to all PostgreSQL Questions→