<@U0675Q5MTL5> are there any risks when doing REINDEX INDEX CONCURRENTLY for UNIQ indexes?

There are certain risks and considerations to keep in mind when performing REINDEX INDEX CONCURRENTLY on UNIQUE indexes in Postgres:

1. Transaction Conflicts: PostgreSQL enforces uniqueness using unique indexes, which disallow multiple entries with identical keys. If a conflicting row is inserted by an as-yet-uncommitted transaction, the transaction attempting to insert the new row must wait to see if the conflicting transaction commits. If it does commit and doesn't delete the conflicting row, a uniqueness violation occurs (PostgreSQL Documentation: Index Uniqueness Checks).

2. MVCC and Deferred Violation Checks: Given the MVCC (Multi-Version Concurrency Control) model of PostgreSQL, duplicate entries may physically exist in the index because they refer to different versions of the same logical row. For deferred unique constraints, PostgreSQL may insert an index entry that violates uniqueness with the expectation that a future event (like the end of a transaction) will resolve the discrepancy. Such situations can complicate the reindexing process, as concurrent modifications may interact with the reindexing transaction in complex ways.

3. Dead Tuples: Immediately before reporting a uniqueness violation, the index access method must recheck the liveness of the row being inserted. If the row is found to be "dead" (no longer visible to any transactions), then no violation should be reported. This logic is particularly relevant during CREATE UNIQUE INDEX CONCURRENTLY operations, which could apply similarly to a concurrent reindex operation.

Overall, while REINDEX INDEX CONCURRENTLY allows for maintenance operations without locking out concurrent operations, it does so at the cost of increased complexity in managing potential conflicts especially involving unique constraints. Allowing concurrent operations means that changes could be happening to the index or the data it references during the reindex, posing a risk of encountering uniqueness conflicts that must be resolved according to the transaction states and visibility rules of the tuples involved.

Before proceeding with reindexing, careful consideration and testing in a non-production environment is recommended to understand the impact on the specific workload and system configuration.

<@U0675Q5MTL5> Can I learn more about the “risk of encountering uniqueness conflicts”? In the end, is it safe to do REINDEX INDEX CONCURRENTLY for UNIQ index or not?

Regarding the risk of encountering uniqueness conflicts when using REINDEX INDEX CONCURRENTLY for UNIQUE indexes in Postgres, the essential details are as follows:

1. Deferrable Constraints: If the unique constraint is deferrable, a row's index entry can be inserted with potential duplication allowed temporarily. A deferred check will be scheduled to enforce uniqueness at a later time. This interim state might complicate the reindex process as it introduces periods where duplicates are allowed within the index for deferrably constrained entries (PostgreSQL Documentation: Index Uniqueness Checks).

2. Transaction Dependencies and Visibility: The enforcement of uniqueness involves checking the visibility of conflicting rows based on transaction states. An existing row that was deleted or inserted by a concurrent, not yet committed, transaction might cause the current transaction to wait or raise a uniqueness violation based on whether the concurrent transaction commits or rolls back. This interplay adds complexity and potential risk when reindexing concurrently.

3. Race Conditions: The necessity to reach into the heap to verify the commit status of rows to satisfy uniqueness constraints can introduce race conditions; particularly when concurrently creating a unique index or potentially reindexing one. These race conditions must be handled appropriately by the system to prevent any violation of uniqueness constraints.

While REINDEX INDEX CONCURRENTLY is generally safe and designed to maintain the availability of the index during maintenance, it is important to recognize that the very nature of concurrent operations introduces complexities and the potential for race conditions, particularly related to enforcement of unique constraints. The operation must account for concurrent transactions that could affect uniqueness and resolve potential conflicts accordingly. This is one of the key differences between concurrent and non-concurrent reindexing operations.

In practice, concurrent reindexing is used without widespread issues, but it is always prudent to test such operations in a non-production environment, especially for databases with a high volume of writes or complex transaction patterns that could increase the likelihood of encountering such complexities.

<@U0675Q5MTL5> If a record is added to the database CONCURRENTLY during reindexing that violates the unique constraint of the new index (i.e. inserting a duplicate value), the REINDEX operation will fail. In this case, the old index will remain intact and the new index will not be created. This only means that we can delete the (new) invalid index and try later.

Ultimately, there is no risk as the current bloat index remains unchanged until a new index is successfully created.

Do you agree with my opinion?