YSQL online index backfill consistency issue

Description

When creating an index on a table with existing data, concurrent writes from an active workload may be missed. In very specific edge cases, this can result in inconsistent data in the new index.

However, if the concurrent activity on the table during the index backfill only involves appending new rows, this advisory can be safely disregarded.

Detection

• yb_index_check(): yb_index_check() is a utility function that checks if an index is consistent with its base relation and can be used to validate the specific index.

  yb_index_check() is available in YugabyteDB starting from v2024.2.3, v2.20.11, and v2024.1.6.

• PL/PgSQL function: If yb_index_check() is not available, then a PL/PgSQL function can be installed on any affected YugabyteDB version and used to check all the indexes of a table (or only a specified index) by comparing the expected (table) and actual (index) row counts.

Mitigation

It is recommended to upgrade to a version with the fix to avoid the potential issue for any new indexes.

If any existing indexes are affected, the standard mitigation is to re-create the affected index(es).

You can do this in two ways, depending on the preferred order of operations:

• Create the new index first. Create the index with the same definition as the previous one, but a different name, then drop the old index. Afterwards, you can rename the new index to the old name to fully reset to the initial state. This avoids performance degradation by ensuring an index is available to be used for reads at any point in the process.

• Drop the old index first, then create a new index with the same name and definition. This ensures any read queries that use the index will immediately start returning the correct results, as they will fall back to either a sequential scan or a different index (if applicable). However, this may cause any read queries that need the index to be much slower until the new (recreated) index is fully created and caught up.

Examples

For the following table, assume the index t_v1_v2_v3_idx has an issue and needs to be re-created.

yugabyte=# \d t

                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 k1     | integer |           | not null |
 k2     | text    |           | not null |
 v1     | integer |           |          |
 v2     | text    |           |          |
 v3     | integer |           |          |
Indexes:
    "t_pkey" PRIMARY KEY, lsm (k1 HASH, k2 ASC)
    "t_v1_v2_v3_idx" lsm (v1 HASH, v2 ASC) INCLUDE (v3) WHERE v1 >= 0

Option 1: Create the new index first

CREATE INDEX t_v1_v2_v3_idx1 ON t(v1, v2) INCLUDE (v3) WHERE v1 >= 0;
DROP INDEX t_v1_v2_v3_idx;
-- Optional in general but needed if index name is used as a hint (that is, via pg_hint_plan)
ALTER INDEX t_v1_v2_v3_idx1 RENAME TO t_v1_v2_v3_idx;

Option 2: Drop the old index first

DROP INDEX t_v1_v2_v3_idx;
CREATE INDEX t_v1_v2_v3_idx ON t(v1, v2) INCLUDE (v3) WHERE v1 >= 0;

Related articles

• CREATE INDEX statement [YSQL]
• Online index backfilling