Add ALTER INDEX ENABLE/DISABLE for Temporarily Disabling Indexes

Dear PostgreSQL Hackers,
I am writing to propose a new feature to allow temporarily disabling and
enabling indexes without dropping them. This would provide a safer, more
efficient alternative to the current drop/recreate workflow for testing and
maintenance purposes. I am aware of prior discussions on this topic (e.g.,
the September 2024 proposal by Shayon Mukherjee [1] and subsequent patches
in 2025), and I aim to build on those efforts with additional motivation
from production use cases.MotivationIn production environments,
administrators often need to verify if an index is actively used or to test
the performance impact of its removal. For instance:

- Assessing unused indexes via pg_stat_user_indexes might suggest
deletion, but confirming requires observing real workloads without the
index.
- During performance tuning, simulating index absence helps isolate
bottlenecks.
- In large-scale systems, dropping an index on a massive table and
recreating it (even with CONCURRENTLY) can take hours, consume
significant resources, and risk downtime if recreation fails.

The status quo forces users to either:

- Drop and recreate the index, which is resource-intensive.
- Use hacks like updating pg_index.indisvalid = false directly (risky
and not recommended).
- Disable index scans session-locally via GUCs like enable_indexscan =
off (limited scope and not index-specific).

This feature would address these gaps, similar to ALTER INDEX DISABLE in
other RDBMS like SQL Server.Proposed SyntaxIntroduce new options to ALTER
INDEX and optionally CREATE INDEX:

ALTER INDEX index_name DISABLE [CONCURRENTLY];ALTER INDEX index_name
ENABLE [REBUILD] [CONCURRENTLY];
CREATE INDEX index_name ON table_name (column) DISABLED;

- DISABLE: Marks the index as unusable by the planner and executor.
- ENABLE: Restores usability. The optional REBUILD rebuilds the index if
it became invalid during disablement.
- CONCURRENTLY: Performs the operation without exclusive locks, if
feasible.

Semantics

- A disabled index is ignored by the query planner and executor but
retains its definition and storage.
- Updates/inserts/deletes: The index could either continue being
maintained (low overhead but keeps it valid) or not (saves resources but
requires rebuild on enable). I suggest the former as default for
simplicity, with an option for the latter.
- Constraints: If the index enforces a constraint (e.g., unique/primary
key), disabling it should either be disallowed or also disable the
constraint temporarily (with warnings).
- Visibility: Add a disabled column to pg_index or pg_indexes view for
easy querying.
- Errors: Attempts to use a disabled index in queries should not error
but fall back to sequential scans.
- Compatibility: No impact on existing indexes; backward-compatible.

Benefits

- Efficiency: Re-enabling is faster than recreating, especially for
large indexes.
- Safety: Reduces risk in testing—easy rollback without data loss.
- Use Cases: Bulk data loads (disable before, enable after), index bloat
analysis, and A/B testing query plans.
- Performance Impact: Minimal, as it leverages existing invalid index
logic.

Potential Implementation Notes

- Build on indisvalid in pg_index, but wrap it in safe DDL commands.
- Integrate with REINDEX for rebuilds.
- Add regression tests for disable/enable cycles and concurrent
operations.
- If helpful, I can provide a proof-of-concept patch or benchmarks from
a large dataset.

I believe this aligns with PostgreSQL's goals of flexibility and
performance. I'd appreciate feedback on the syntax, semantics, or any
overlaps with ongoing patches (e.g., [2]). Is there interest in pursuing
this for PostgreSQL 18 or later?
Best regards,Alaa Attia