DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL XX002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error XX002: Index Corrupted — Diagnosis and Recovery Guide

PostgreSQL error code XX002 (index corrupted) indicates that the internal structure of a database index has become inconsistent or unreadable, preventing normal query execution. This error can surface during SELECT, UPDATE, or DELETE operations that rely on the affected index, as well as during routine maintenance tasks like VACUUM or ANALYZE. While this error does not necessarily mean your underlying table data is lost, it requires immediate action to restore query reliability.

Top 3 Causes

1. Hardware Failure or Storage Corruption

Bad disk sectors, SSD firmware bugs, or RAID controller failures are the most common root causes of index corruption. PostgreSQL trusts the OS and storage layer to write data correctly — if they don't, index pages get silently corrupted.

-- Verify index integrity using amcheck (PostgreSQL 10+)
CREATE EXTENSION IF NOT EXISTS amcheck;

-- Basic index structure check
SELECT bt_index_check('your_index_name'::regclass);

-- Full check including heap consistency
SELECT bt_index_parent_check('your_index_name'::regclass, true);
Enter fullscreen mode Exit fullscreen mode

2. Unclean PostgreSQL Shutdown (Crash Recovery Failure)

When PostgreSQL is killed via SIGKILL, an OOM Killer event, or a forced server reboot before WAL changes are fully flushed, index pages can be left in an incomplete state. Although WAL replay normally handles recovery, certain edge cases — such as file system errors or version mismatches — can leave indexes inconsistent.

-- Disable index scans temporarily to read data via sequential scan
-- when an index is known to be corrupted
SET enable_indexscan = OFF;
SET enable_bitmapscan = OFF;

SELECT COUNT(*) FROM your_table_name;

-- Re-enable after data extraction
RESET enable_indexscan;
RESET enable_bitmapscan;
Enter fullscreen mode Exit fullscreen mode

3. PostgreSQL Bugs or Extension Version Mismatch

Known bugs in older PostgreSQL versions — particularly around B-Tree page-split logic — or incompatible third-party extensions (e.g., PostGIS, pg_trgm) can gradually corrupt indexes under high write workloads. Always check PostgreSQL release notes and CVE advisories before staying on a minor version for extended periods.

-- Check current PostgreSQL version
SELECT version();

-- List all installed extensions and their versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

The fastest and most reliable fix is to rebuild the corrupted index using REINDEX.

-- Rebuild a single index (causes brief lock)
REINDEX INDEX your_index_name;

-- Rebuild all indexes on a table
REINDEX TABLE your_table_name;

-- Rebuild without downtime (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY your_index_name;
REINDEX TABLE CONCURRENTLY your_table_name;

-- If REINDEX fails, drop and recreate manually
DROP INDEX IF EXISTS your_index_name;
CREATE INDEX CONCURRENTLY your_index_name ON your_table (your_column);
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Enable data checksums at cluster initialization

Data checksums allow PostgreSQL to detect silent storage corruption at the page level before it escalates into full index corruption.

# Enable checksums on a new cluster
initdb --data-checksums -D /var/lib/postgresql/data

# Enable on an existing cluster (requires downtime, PostgreSQL 12+)
pg_checksums --enable -D /var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode 
-- Verify checksums are active
SHOW data_checksums;
Enter fullscreen mode Exit fullscreen mode

2. Schedule regular index health checks with amcheck

Run the following check weekly via cron or pgAgent to catch corruption early before it impacts production queries.

-- Batch check all B-Tree indexes in the database
CREATE EXTENSION IF NOT EXISTS amcheck;

DO $$
DECLARE
    idx RECORD;
BEGIN
    FOR idx IN
        SELECT c.oid, c.relname
        FROM pg_class c
        JOIN pg_am a ON c.relam = a.oid
        WHERE a.amname = 'btree'
          AND c.relkind = 'i'
    LOOP
        BEGIN
            PERFORM bt_index_check(idx.oid);
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING 'Corrupted index detected: % — %',
                idx.relname, SQLERRM;
        END;
    END LOOP;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • XX001 (heap corrupted): Table heap file corruption, often occurring alongside XX002 during storage-level failures.
  • 58030 (io_error): I/O errors at the OS level are frequently a precursor to XX002 — treat any io_error as a signal to run amcheck immediately.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)