Hi, On Thu, Mar 17, 2022 at 11:16:24PM -0700, Paul Martinez wrote: > > Adding and removing indexes is a regular part of database maintenance, > but in a large database, removing an index can be a very risky operation. > Removing the wrong index could have disastrous consequences for > performance, and it could take tens of minutes, or even hours, to rebuild > the entire index. > > I propose adding an ALTER INDEX command that can enable or disable an > index on a global level: > > ALTER INDEX index_name ENABLE; > ALTER INDEX index_name DISABLE; > > A disabled index is still updated, and still enforces constraints, but it > will not be used for queries. > > Whether or not the index is disabled could also be specified at index > creation: > > CREATE INDEX index_name ON table_name (col1, col2) ENABLED; -- default > CREATE INDEX index_name ON table_name (col1, col2) DISABLED; > > This would be useful if a user anticipates index creation to take a long > time and they want to be able to carefully monitor the database once the > index starts getting used. > > It would also be useful to be able to enable and disable indexes locally > in the context of a single session to easily and safely verify that a > query can still be executed efficiently without an index: > > ALTER INDEX index_name DISABLE SESSION;
For the record, all of that is already doable using plantuner extension: https://github.com/postgrespro/plantuner.