A reindex maintenance is a special maintenance for databases that are susceptible to index corruption during infrastructure upgrades. This corruption can occur when the collation of a locale changes during an operating system upgrade. See this Postgres article for more details.
This maintenance type causes increased downtime, as the system must block all writes until the database has reindexed the susceptible indexes. During the maintenance, we:
- Create a replacement follower database
- Wait for the follower to catch up to the leader database
- Push the follower's credentials to your app
- Reindex the critical indexes
- Unfollow the former leader and promote the follower database
- Repoint any followers to the new leader database
The amount of downtime depends on the size and number of indexes on columns with text data. You can monitor the status of the maintenance by connecting with heroku pg:psql <addon-name> --app <app-name> and running the query in this snippet.
After we reindex the critical indexes, we notify you that the maintenance is complete and your database is writable again. In the background, we also reindex all remaining indexes on columns with text data to ensure that your queries return accurate results. You can notice decreased database performance while we reindex the remaining indexes.