Index inconsistency during concurrent expression index creation

Description

Concurrent creation of an expression index and updates to the columns included in that expression index, can lead to index inconsistency. This issue is caused by a YugabyteDB planner bug which causes an UPDATE query to skip writing to the index in a specific time window when concurrent index creation is in progress. Queries causing inconsistency could be simple UPDATE queries, or use the INSERT ... ON CONFLICT ... DO UPDATE clause.

The root cause is a planner optimization to skip index updates if the columns in the SET clause do not affect the index. During concurrent creation of an expression index, the planner fails to read the index's expression details and assumes the index has no expressions. This sequence results in incorrectly skipping the required writes to the index.

Mitigation

1. Find the relevant expression Indexes in a specific database:

   SELECT
       pi.schemaname,
       pi.tablename,
       pi.indexname,
       pg_get_expr(i.indexprs, i.indrelid) AS expression,
       pi.indexdef  -- Shows the full CREATE INDEX statement
   FROM
       pg_indexes AS pi
   JOIN
       pg_index AS i ON i.indexrelid = (pi.schemaname || '.' || pi.indexname)::regclass
   WHERE
       i.indexprs IS NOT NULL
       AND pi.schemaname NOT IN ('pg_catalog', 'information_schema');
   

2. Using the yb_index_check() function, check each expression index.

3. If yb_index_check() reports any index as inconsistent, then drop and recreate the affected index.

   If the YugabyteDB version does not include a fix for this issue, then update queries involving expression indexes should be avoided while CREATE INDEX is in progress.

Workarounds

Use one of the following workarounds / solutions to the issue:

• Upgrade to a database version containing the fix (refer to the summary above).

• Disable the optimization to skip index updates by setting the yb_skip_redundant_update_ops YB-TServer flag to false for the duration of the expression index creation.

  Note that this will result in increased latencies and reduced throughput for UPDATE queries.

• Include the columns making up the expression as covering columns in the index. For example:

  CREATE INDEX name ON table ( func(a, b) )
  

  should instead be defined as

  CREATE INDEX name ON table ( func(a, b) ) INCLUDE (a, b)  
  

Details

The planner optimization relies on checking an index's metadata to see which columns it references. For expression indexes, this data is stored in an attribute called rd_indexprs.

When the attribute rd_indexprs is empty at the time of examination, the planner concludes that the given index has no expressions. The bug causes rd_indexprs to not be populated, which leads to an incorrect assumption by the planner that the given index has no expressions. This causes the updates to the index to be missed. This state occurs during concurrent index creation, where the index is ready for inserts (indisready=true) but not yet valid (indisvalid=false).

Example

The following sequence demonstrates the bug:

1. Session A begins: CREATE INDEX ON t ( f(j) ).

2. Session A progresses. The index is built and marked indisready=true. The backfill stage begins.

3. Session B runs: UPDATE t SET j = j + 100.

4. Bug: The planner in Session B examines the new index. It sees the unloaded expression data, assumes the UPDATE to column j does not affect the index, and skips writing the update to the index.

5. Session A completes the backfill and marks the index indisvalid=true.

Result: The index is now active but does not contain the changes from Session B. The index is inconsistent with the table data.