Catching Index Inconsistencies in YugabyteDB

Maintaining data integrity in a distributed SQL database is critical, especially when indexes are involved. Indexes speed up query performance but are only useful when they’re correct and in sync with the underlying table data.

While YugabyteDB is built for strong consistency and correctness, things can still go wrong due to hardware failures, cosmic rays, or bugs. That’s where the new yb_index_check() function comes in handy.

In today’s tip, we’ll explore how to use the yb_index_check() function in YugabyteDB to detect and diagnose index-table mismatches in your YSQL database.

What is it?

yb_index_check() is a powerful diagnostic function that verifies the consistency between a table and its index in YugabyteDB. It checks for missing, extra, or mismatched index entries compared to the base table. This feature can help identify subtle corruption or inconsistency issues, especially useful in debugging or validating correctness after cluster failures or restores.

				
					yugabyte=# \df yb_index_check
                              List of functions
   Schema   |      Name      | Result data type | Argument data types | Type
------------+----------------+------------------+---------------------+------
 pg_catalog | yb_index_check | void             | oid                 | func
(1 row)

How It Works

The function compares the rows in a given index with those in the corresponding base table. If no corruption is found, it returns no output. However, if yb_index_check() detects actual index corruption, it throws an error with:

				
					SQLSTATE: XX002
ERRCODE: ERRCODE_INDEX_CORRUPTED

Example:

				
					-- Create a table and index
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name TEXT,
  department TEXT
);

CREATE INDEX idx_department ON employees(department);

-- Run the index check
SELECT * FROM yb_index_check('public.idx_department'::reglass);

When Should You Use It?

Use yb_index_check() when:

You suspect index corruption or mismatch.
After cluster restore, upgrade, or major failure.
During advanced troubleshooting by support or engineering.
As part of health checks or automation pipelines in large clusters.

Real-World Scenarios Where Inconsistencies Can Occur

Manual File or Data Corruption: Hardware failure, disk corruption, or bit rot could lead to index corruption.
Improper Restore or Clone Operations: If table data is restored without corresponding indexes (or vice versa), inconsistencies may occur.
YSQL Bugs (Rare but Possible): An internal bug in index write logic could cause an index to miss a row or write it incorrectly.
Manual DELETE/INSERT Using Lower-Level Tools: If you bypass the SQL layer (e.g. using yb-admin, file system tricks, or direct internal RPCs), you can create inconsistencies.
Concurrent DDL Bugs or Rollbacks: Under unusual timing conditions (e.g., crash during index build), the index may be incomplete or stale.

Fixing Issues

If inconsistencies are detected, the common resolution is to rebuild the index:

				
					DROP INDEX idx_department;
CREATE INDEX idx_department ON employees(department);

Notes

The yb_index_check() is avaible from YugabyteDB 2.20.11 and above
Official doc page: yb_index_check()

Summary

YugabyteDB continues to add tooling that helps operators and developers ensure correctness in distributed environments. The yb_index_check() function is a valuable new addition for power users who need to verify that what’s indexed truly reflects what’s in their tables.