When a PostgreSQL system catalog index is damaged, the metadata lookup needed to start a repair can fail as well. You need to rebuild the index, but the server may try to use that same index before the repair can begin.

ignore_system_indexes exists to break that loop temporarily. A post by Christophe Pettus brought this long-standing -P recovery path back into focus.

So when should an operator enable it, and how much behavior can be trusted while it is active? The useful mental model is an emergency bypass from an isolated session to index reconstruction, not a normal tuning setting.

Bypassing a damaged catalog index makes REINDEX reachable

System catalogs such as pg_class and pg_attribute store the metadata PostgreSQL needs to work with tables and columns. Catalog indexes normally make those lookups fast, but a damaged index can prevent even REINDEX from obtaining the catalog information it needs.

A session started with ignore_system_indexes ignores system indexes when reading system tables while continuing to update those indexes when the tables are modified. PostgreSQL 18 Developer Options classifies it as a developer option that can assist with recovery from severe damage and says it cannot be changed after the session starts.

This is not a setting to flip with SET ignore_system_indexes = on after connecting. The short-option mapping shows that -P is equivalent to ignore_system_indexes = on at backend startup. Its job is to provide a path to the repair command.

Start with -P and move directly to REINDEX SYSTEM

If damaged catalog indexes cause connections to fail, the basic path in the PostgreSQL 18 REINDEX documentation is to stop the normal server and open the target database in single-user mode with -P. The exact single-user syntax is documented in the official postgres command reference.

postgres --single -P -D "/path/to/pgdata" "database_name"

Once the backend starts, rebuild the system catalog indexes. PostgreSQL 18 allows the database name to be omitted here.

REINDEX SYSTEM;

REINDEX SYSTEM rebuilds system catalog indexes in the current database, including indexes on shared catalogs. It does not rebuild all user-table indexes. It also cannot use CONCURRENTLY, because PostgreSQL does not support concurrent reindexing of system catalogs.

If normal sessions can still start, the official documentation also describes passing -P through PGOPTIONS with a libpq-based client.

PGOPTIONS="-P" psql "database_name"

That does not make ordinary traffic safe during the repair. PostgreSQL recommends considering blocking other users from the damaged database until the work is complete. On a managed PostgreSQL service, customers may not control single-user mode or the data directory, so the provider's recovery procedure, restore path, and support channel become part of the decision.

PostgreSQL 14 and 18 use different REINDEX SYSTEM syntax

This recovery pattern is not new. The PostgreSQL 7.1 REINDEX documentation already described starting stand-alone Postgres with -O and -P to repair corrupted system indexes. By PostgreSQL 8.2, ignore_system_indexes appeared as a developer option with the same read-bypass, write-maintenance behavior and the same session-start constraint.

A long history does not mean the command syntax is identical across versions. PostgreSQL 14 REINDEX requires the current database name after REINDEX SYSTEM, while PostgreSQL 18 makes it optional.

-- PostgreSQL 14
REINDEX SYSTEM my_database;

-- PostgreSQL 18
REINDEX SYSTEM;

During an incident, copying syntax from the newest search result can therefore fail on the affected cluster. A recovery runbook should link to the documentation for the deployed major version.

ignore_system_indexes does not eliminate every index access

The name can suggest a complete ban on system-index use, but its operational scope is narrower. The documented guarantee is that reads of system tables ignore their indexes while writes continue maintaining those indexes. Unnecessary DDL in a damaged session can still write to the structures you are trying to repair.

The source article goes further and reports implementation-level exceptions: some internal paths that require index ordering may still use an index with a warning, and trigger or rule execution order can differ under -P. Those findings are another reason not to expect normal workload compatibility in this mode.

Sequential catalog scans can also be slow. -P is not a degraded-service mode for keeping an application online; it is a short path to REINDEX SYSTEM followed by a return to normal startup.

Verify the rebuild with amcheck and investigate the cause

A successful restart is not enough to declare the incident closed. The PostgreSQL 18 amcheck documentation includes an example that uses bt_index_check on B-tree indexes in pg_catalog, providing a way to look for structural inconsistencies after reconstruction.

The same documentation is explicit about the limit: amcheck can prove that corruption is present, but it cannot prove that corruption is absent. It also says there is no universal repair for everything it detects and that REINDEX may not repair every form of corruption.

The practical endpoint is therefore not merely a rebuilt index. Investigate logs, storage, the file system, memory, recent changes, and the backup path to identify what allowed the inconsistency to appear and how to prevent a repeat.

Keep it as the last-resort path in a recovery runbook

The opening question has a narrow answer. Enable ignore_system_indexes only when damaged system catalog indexes prevent the normal lookup path and you need to reach REINDEX SYSTEM. It is neither a performance setting nor an availability feature.

A usable runbook should connect version-correct single-user startup, connection isolation, REINDEX SYSTEM, normal restart, amcheck, and root-cause analysis. In that complete sequence, this old switch becomes a practical recovery tool.

Source

• Title: Christophe Pettus: All Your GUCs in a Row: ignore_system_indexes
• URL: https://postgr.es/p/9rP