How do I rebuild a non-UNIQUE corrupted index?

Issue

You need to rebuild a corrupted non-UNIQUE index, and are concerned about doing this with minimal downtime.

Resolution

Small indexes can be rebuilt quickly using the REINDEX command as discussed in the following article:

https://help.heroku.com/RKRZV2NO/how-do-i-fix-a-corrupted-index

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 CONCURRENTLY.

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

Using pg_get_indexdef you can generate a CREATE INDEX definition:

SELECT pg_get_indexdef('<index-oid>');

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>;

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support