Issue
In some rare cases an index may become corrupted leading to queries returning bad data. This issue may present itself in queries returning too many or too few rows.
Resolution
Does Heroku fix index corruption automatically?
In the event that index corruption was detected on your instance, we will first determine what type of index the corruption occurred on. If the corruption occurred on a non-unique index, we will attempt to remediate the corruption automatically.
The automatic remediation process will use CREATE INDEX CONCURRENTLY to create a new, uncorrupted index. After creation, it will then swap names with the corrupted index before removing the corrupted index entirely.
What is the simplest way to rebuild a small to medium sized index?
For small to medium sized indexes reindexing in place is the simplest option. This reindexing will lock writes to the table as well as reads which leverage the index for the amount of time it takes to reindex the table.
REINDEX INDEX <index-name>
If you do not want to lock the table or need to fix a non-unique index you will need to read the following sections.
How do I remediate a corrupted non-unique Index?
In the event that automated index remediation fails you will need to manually reindex using the process outlined in the following article:
https://help.heroku.com/H3LU3G7Z
How do I remediate a corrupted Unique Index?
If uniqueness constraints have been violated, creating a new index will not succeed. Customers must attempt to reindex unique indexes themselves, and will need to manually resolve issues if they arise as documented in the following KB article:
https://help.heroku.com/6876UZ3L
Will I experience downtime?
The REINDEX command will block all writes to the table and reads which utilize the index on that table. As such, you will experience an inability to write and a limited ability to read from that table while the index is being rebuilt. The affect of this depends on the applications reliance on the affected table as well as the amount of time the index takes to REINDEX. During this time you can expect errors on your dynos relying on this database in the form of H12 timeouts.
The CREATE CONCURRENTLY and DROP CONCURRENTLY commands alternatively will not lock the affected table. You may still experience an increase in H12 timeout errors during this time if your database is CPU and/or IO constrained.
How can I tell how large my index is?
If you have (or install) the heroku-pg-extras plugin, then you can run the command heroku pg:index-size to see a list of all indexes from largest to smallest.
For an individual index you can calculate the size in a psql session using the following query:
SELECT pg_size_pretty(pg_relation_size('<index-name>'));
Should I rebuild concurrently?
Running DROP and CREATE with the CONCURRENTLY command will cause the total execution time to increase significantly. During this time you will still be able to read and write to the table with the corrupted index.
This option is probably best for customers who cannot tolerate downtime on the affected table(s).
https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
We describe this procedure in more detail here:
https://help.heroku.com/H3LU3G7Z/how-do-i-rebuild-a-non-unique-corrupted-index
What should I do if rebuilding the index concurrently fails?
It is also possible for CREATE INDEX CONCURRENTLY or DROP INDEX CONCURRENTLY to fail, if this happens you will need to reattempt the rebuild concurrently or alternatively rebuild the index with REINDEX.