Add ALTER INDEX ENABLE/DISABLE for Temporarily Disabling Indexes - Mailing list pgsql-hackers
| From | Alaa Attya |
|---|---|
| Subject | Add ALTER INDEX ENABLE/DISABLE for Temporarily Disabling Indexes |
| Date | |
| Msg-id | CAB_VXgtJTLv7Z2Weam6hL0+gkMET=anekej52FasUjDqUjc-xg@mail.gmail.com Whole thread Raw |
| List | pgsql-hackers |
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:
Best regards,Alaa Attia
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.
- 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).
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.
- 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.
- 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.
- 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.
Best regards,Alaa Attia
pgsql-hackers by date: