PostgreSQL vector index corruption after standby promotion during failover

Problem

After a primary PostgreSQL cluster outage, the standby server was promoted and the database was restored from a recent base backup. Subsequent similarity‑search queries that rely on pgvector indexes start failing with errors such as:

ERROR:  could not read index file "base/16384/12345": Invalid argument
DETAIL:  Index "vector_idx" appears to be corrupted.
HINT:  Reindex the index or restore it from a backup.

These failures manifest as:

• SQL exceptions on SELECT ... ORDER BY vector <-> query_vector LIMIT 10 statements.
• Increased query latency as the planner falls back to sequential scans.
• Potential data inconsistency if the index is used for deduplication or recommendation logic.

Root Cause

The corruption originates from a mismatch between the pgvector extension version and the on‑disk index format after the standby promotion. During the failover:

1. The primary was running pgvector 0.4.0, which stores vector indexes using the vector_opclass format introduced in that release.
2. The standby was promoted after a delayed pg_basebackup that captured the data directory while the primary was still on pgvector 0.3.2.
3. When the promoted standby started, the newer pgvector binary (0.4.0) attempted to read the older index files. The on‑disk layout differs in the header checksum field, causing the index loader to abort and mark the index as corrupted.
4. Because the promotion also rewound the WAL timeline, the missing ALTER EXTENSION pgvector UPDATE command that would have migrated the index never ran.

Debug

Step‑by‑step investigation that reproduced the issue:

1. Identify corrupted indexes

SELECT schemaname, relname, indexrelid::regclass
FROM pg_indexes
WHERE indexdef LIKE '%vector%';

Result shows the suspect index public.vector_idx.

2. Inspect PostgreSQL logs

2024-09-12 03:14:27.123 UTC [12345] LOG:  index "vector_idx" (OID 12345) is marked as invalid
2024-09-12 03:14:27.124 UTC [12345] ERROR:  could not read index file "base/16384/12345": Invalid argument
2024-09-12 03:14:27.124 UTC [12345] DETAIL:  Index "vector_idx" appears to be corrupted.

3. Verify extension version on the promoted node

SELECT extname, extversion FROM pg_extension WHERE extname='pgvector';
-- Result:
 extname | extversion
---------+-----------
 pgvector| 0.4.0

4. Compare with the backup metadata

# grep pgvector /var/lib/postgresql/backup/metadata.txt
pgvector 0.3.2

5. Check index file format

# hexdump -C $PGDATA/base/16384/12345 | head
00000000  00 00 00 01 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000010  4d 61 67 69 63 20 76 65  63 74 6f 72 20 30 2e 33  |Magic vector 0.3|
...

The header contains the string Magic vector 0.3, confirming the older format.

6. Confirm that a simple REINDEX fails

# psql -d mydb -c "REINDEX INDEX public.vector_idx;"
ERROR:  could not read index file "base/16384/12345": Invalid argument

Solution

The fix consists of three coordinated actions:

1. Align the extension version with the on‑disk format

Downgrade the pgvector extension on the promoted node to the version that matches the index files (0.3.2). This can be done without a full cluster restart because extensions are loaded per session.

# psql -d mydb -c "DROP EXTENSION IF EXISTS pgvector CASCADE;"
DROP EXTENSION
# psql -d mydb -c "CREATE EXTENSION pgvector VERSION '0.3.2';"
CREATE EXTENSION

2. Rebuild the corrupted indexes using the correct binary

After the downgrade, the index loader can interpret the old format. Reindex each affected index.

# psql -d mydb -c "REINDEX INDEX public.vector_idx;"
REINDEX INDEX

3. Upgrade the extension and migrate indexes to the new format

Once the indexes are healthy, upgrade the extension to the desired version (0.4.0) and run the migration script provided by pgvector.

# psql -d mydb -c "ALTER EXTENSION pgvector UPDATE TO '0.4.0';"
ALTER EXTENSION
# psql -d mydb -c "SELECT pgvector.upgrade_vector_indexes();"
 pgvector.upgrade_vector_indexes 
-------------------------------
 t
(1 row)

Before / After comparison

State	Extension Version	Index Header	Query Result
Before fix	0.4.0	Magic vector 0.3	ERROR
After downgrade & reindex	0.3.2	Magic vector 0.3	Success
After upgrade & migration	0.4.0	Magic vector 0.4	Success

Verify

Run the following checks to confirm that the vector indexes are functional and consistent.

Functional query test

# psql -d mydb -c "
SELECT id, vector <-> '[0.1,0.2,0.3]' AS distance
FROM items
ORDER BY vector <-> '[0.1,0.2,0.3]' LIMIT 5;
"
 id | distance 
----+----------
 42 | 0.00123
 17 | 0.00456
...
(5 rows)

Index integrity check

# psql -d mydb -c "SELECT pg_amcheck('public.vector_idx');"
 pg_amcheck 
------------
 t
(1 row)

Log verification

Ensure no further index‑related errors appear in postgresql.log after a short observation window.

# grep vector_idx /var/log/postgresql/postgresql-15-main.log
2024-09-12 03:45:10.321 UTC LOG:  index "vector_idx" rebuilt successfully

Prevent

To avoid recurrence during future failovers:

• Pin extension versions in postgresql.conf using shared_preload_libraries='pgvector=0.3.2' or by managing extensions via pg_upgrade scripts.
• Include extension upgrades in the promotion workflow. Before promoting a standby, verify that the standby’s extension versions match the primary’s recorded versions (e.g., via SELECT extname, extversion FROM pg_extension;).
• Enable pg_checksums on the data directory to detect silent corruption early.
• Automate index migration. Add a post‑promotion hook that runs SELECT pgvector.upgrade_vector_indexes(); if the target version is newer.
• Monitor replication lag and WAL replay status. Use pg_stat_replication and alerts on pg_last_xlog_receive_location() vs pg_last_xlog_replay_location().
• Test failover in a staging environment. Simulate promotion with mismatched extension versions to verify that the upgrade path works.

Related Topic Hub: Data Infrastructure Troubleshooting Hub

FAQ

1. Why does the index work on the primary but not after promotion?
   Because the primary and standby were running different pgvector versions. The primary’s newer binary could read the newer index format, while the promoted standby used the newer binary against an older on‑disk format, leading to a mismatch.
2. Can I avoid downgrading the extension?
   If you have a recent base backup taken after the extension upgrade, you can promote the standby without downgrading. Otherwise, downgrading is the safest path to recover the existing indexes before migrating them.
3. Is REINDEX sufficient after a promotion?
   Only if the extension version matches the index format. With a version mismatch, REINDEX fails because the index loader cannot parse the old header.
4. How do I know which pgvector version created a given index?
   Inspect the index file header (e.g., hexdump -C $PGDATA/base/16384/12345 | grep -i magic) or query pg_extension history tables if you maintain a migration log.
5. Will this issue affect other extensions that use custom index types?
   Yes. Any extension that stores version‑specific metadata in the index file is vulnerable to the same problem. The general mitigation is to keep extension versions synchronized across primary and standby before promotion.