You need to rebuild a corrupted non-UNIQUE index, and are concerned about doing this with minimal downtime.
Small indexes can be rebuilt quickly using the
REINDEX command as discussed in the following article:
However larger indexes require an alternative approach if you need to avoid downtime.
How can I reindex with minimal downtime?
For large indexes where the time required to
REINDEX would be such that blocking of writes would result in unacceptable response times or even downtime for your database, you can attempt to rebuild them
When rebuilding your index in this manner you will continue to be able to write and read from the index and the affected table. The trade-off is that during the time to rebuild, your database will experience a higher amount of IO and CPU utilization.
Get OID of Corrupted Index
To get the
oid of the corrupted index using the following command:
SELECT oid FROM pg_class where relname='<index-name>';
Generate CREATE statement for index
pg_get_indexdef you can generate a CREATE INDEX definition:
Copy this definition to a text editor or your clipboard, but do not execute the definition at this time.
Modify the CREATE statement for your index
The above query will have generated a statement that looks similar to:
CREATE INDEX <original-index-name> ON <schema-name>.<table-name> USING btree (<column-name(s)>)
You will want to modify this statement to change the index name and tell Postgres you want to create the index
CONCURRENTLY. After doing so your statement will look similar to this:
CREATE INDEX CONCURRENTLY <new-index-name> ON <schema-name>.<table-name> USING btree (<column-name(s)>)
Recreate the Index
Paste the modified index definition generated above and execute the query.
DROP Existing Index
DROP INDEX <index-name>;
Rename the new index
If preferred, you can rename the new index to have the same name as the index which was replaced:
ALTER INDEX <new-index-name> RENAME TO <index-name>;
I have questions, where can I read more?
We have an article answering FAQs and providing more detail about index corruption that occurred in March 2019: