yb_index_check()

Synopsis

yb_index_check() is a utility function that checks if an index is consistent with its base relation. It is useful to detect inconsistencies that can creep in due to faulty storage, faulty RAM, data files being overwritten or modified by unrelated software, or hypothetical undiscovered bugs in YugabyteDB.

It performs checks to detect spurious, missing, and inconsistent index rows. It also validates uniqueness on unique indexes.

If executed on a partitioned index, it recursively checks every child partition. It does not yet support vector and ybgin indexes.

By default the function runs in multi-snapshot mode: it splits the scan into batches and takes a fresh snapshot for each batch so that a long-running check does not hit Snapshot too old. Pass single_snapshot_mode => true to scan the whole index under one snapshot.

By default, log_num_errors is 0 and the function errors out on the first inconsistency. Set it to a positive integer to collect up to that many inconsistencies as result rows and in the server log, then stop. A positive log_num_errors requires multi-snapshot mode.

If no inconsistencies are found, the function returns zero rows.

Function interface

yb_index_check(
    indexrelid oid,
    single_snapshot_mode boolean DEFAULT false,
    log_num_errors integer DEFAULT 0
) RETURNS TABLE(
    tablerelid oid,
    indexrelid oid,
    ybctid bytea,
    table_cols jsonb,
    ybbasectid bytea,
    index_cols jsonb,
    error_category text
)

The log_num_errors parameter and the table return type are available in v2026.1.3 and later.

You can pass an index name with a regclass cast ('my_idx'::regclass); YSQL converts it to oid.

yb_index_check() is a set-returning function. Use SELECT * FROM yb_index_check(...) to return each inconsistency as a row with the result columns. A consistent index returns zero rows.

A LIMIT clause on the outer query does not stop the checker early, so pass log_num_errors when you want the scan to stop after N findings.

Snapshot modes

single_snapshot_mode selects how yb_index_check() pins read time.

Multi-snapshot mode (single_snapshot_mode is false, the default) uses a sequence of snapshots so a long-running check does not hit Snapshot too old.

Single-snapshot mode (single_snapshot_mode is true) scans the index and base table under one snapshot. Use this for smaller indexes where Snapshot too old isn't expected. It's faster, but only reports missing rows as a row-count mismatch, not which rows, and so doesn't support log_num_errors > 0.

Row-level security

Row-level security enforcement in yb_index_check() is available in v2026.1.3 and later.

yb_index_check() reads every physical row of the base table. If row-level security is enabled on that table and the caller cannot bypass it, the function fails before scanning.

The check is allowed for superusers, table owners (unless FORCE ROW LEVEL SECURITY is set), and roles with BYPASSRLS. Tables without RLS are unaffected. On a partitioned index, the same requirement applies to each partition: the caller must be allowed to read every row of that partition's base table.

Reporting issues

When log_num_errors is 0 (the default), the first inconsistency raises an error with SQLSTATE XX002 (ERRCODE_INDEX_CORRUPTED) and the check stops. A consistent index returns zero rows.

When log_num_errors is greater than 0, each inconsistency is returned as a result row and written to the server log at LOG level (still with ERRCODE_INDEX_CORRUPTED). The check stops after that many findings and raises a NOTICE:

yugabyte=# SELECT * FROM yb_index_check('abcd_b_c_d_idx'::regclass, false, 3);

NOTICE:  index checker aborted: maximum reportable errors (3) reached. Consider dropping and recreating the index.
 tablerelid | indexrelid |         ybctid         |         table_cols          |       ybbasectid       |        index_cols        |   error_category
------------+------------+------------------------+-----------------------------+------------------------+--------------------------+---------------------
      19968 |      19973 | \x47fca048800000032121 | {"b": 9999, "c": 3, "d": 3} | \x47fca048800000032121 | {"b": 3, "c": 3, "d": 3} | BINARY_KEY_MISMATCH
      19968 |      19973 |                        |                             | \x47121048800000012121 | {"b": 1, "c": 1, "d": 1} | SPURIOUS_ROW
      19968 |      19973 | \x471c99488000000b2121 |                             |                        |                          | MISSING_ROW
(3 rows)

The result columns are:

error_category values:

ybuniqueidxkeysuffix is a hidden unique-index column. Unique indexes store the base-table ybctid there when nulls-are-distinct semantics apply and at least one key column is NULL; otherwise the column is null.

The base table is the source of truth. The checker reports at most one inconsistency per (base-table row, index row) pair.

Repairing corruption

There is no general method of repairing problems that yb_index_check() detects. It is best to drop and recreate inconsistent indexes.

Examples

Set tables as follows:

CREATE TABLE abcd(a int primary key, b int, c int, d int);
CREATE INDEX abcd_b_c_d_idx ON abcd (b ASC) INCLUDE (c, d);
CREATE INDEX abcd_b_c_idx ON abcd(b) INCLUDE (c) WHERE d > 50;
CREATE INDEX abcd_expr_expr1_d_idx ON abcd ((2*c) ASC, (2*b) ASC) INCLUDE (d);
INSERT INTO abcd SELECT i, i, i, i FROM generate_series(1, 10) i;

Perform a consistency check on index 'abcd_b_c_d_idx':

yugabyte=# SELECT * FROM yb_index_check('abcd_b_c_d_idx'::regclass);

 tablerelid | indexrelid | ybctid | table_cols | ybbasectid | index_cols | error_category
------------+------------+--------+------------+------------+------------+----------------
(0 rows)

Zero rows means the index is consistent with its base table.

Perform a consistency check on an index with oid = 16906:

yugabyte=# SELECT * FROM yb_index_check(16906);

 tablerelid | indexrelid | ybctid | table_cols | ybbasectid | index_cols | error_category
------------+------------+--------+------------+------------+------------+----------------
(0 rows)

Report up to 100 inconsistencies without failing on the first one. This requires multi-snapshot mode (single_snapshot_mode => false, the default):

yugabyte=# SELECT * FROM yb_index_check('abcd_b_c_d_idx'::regclass, false, 100);

 tablerelid | indexrelid | ybctid | table_cols | ybbasectid | index_cols | error_category
------------+------------+--------+------------+------------+------------+----------------
(0 rows)

If the index is inconsistent, each row is one finding. For example:

yugabyte=# SELECT * FROM yb_index_check('abcd_b_c_d_idx'::regclass, false, 100);

 tablerelid | indexrelid |         ybctid         |         table_cols          |       ybbasectid       |        index_cols        |   error_category
------------+------------+------------------------+-----------------------------+------------------------+--------------------------+---------------------
      19968 |      19973 | \x47fca048800000032121 | {"b": 9999, "c": 3, "d": 3} | \x47fca048800000032121 | {"b": 3, "c": 3, "d": 3} | BINARY_KEY_MISMATCH
      19968 |      19973 |                        |                             | \x47121048800000012121 | {"b": 1, "c": 1, "d": 1} | SPURIOUS_ROW
      19968 |      19973 | \x471c99488000000b2121 |                             |                        |                          | MISSING_ROW
(3 rows)

table_cols and index_cols are omitted from the PostgreSQL log; they appear only in the SQL result. The corresponding log for the example above is:

LOG:  inconsistent index row due to binary mismatch of key attribute
DETAIL:  index: 'abcd_b_c_d_idx', ybbasectid: '\x47fca048800000032121', index attnum: 1
LOG:  index contains spurious row
DETAIL:  index: 'abcd_b_c_d_idx', ybbasectid: '\x47121048800000012121'
LOG:  index 'abcd_b_c_d_idx' is missing row corresponding to ybctid '\x471c99488000000b2121'

Force single-snapshot mode (all rows under one snapshot):

yugabyte=# SELECT * FROM yb_index_check('abcd_b_c_d_idx'::regclass, true);

 tablerelid | indexrelid | ybctid | table_cols | ybbasectid | index_cols | error_category
------------+------------+--------+------------+------------+------------+----------------
(0 rows)

Perform consistency check on all the indexes of relation 'abcd' where pg_table_size() < 1GB:

CREATE OR REPLACE FUNCTION check_all_indexes_in_table(table_oid regclass)
RETURNS TABLE(indexname NAME, yb_index_check TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    indexrelid oid;
BEGIN
    FOR indexrelid, yb_index_check IN
        SELECT pg_index.indexrelid, 'OK'
        FROM pg_index
        WHERE indrelid = table_oid
          AND pg_table_size(pg_index.indexrelid) < 1024 * 1024 * 1024
    LOOP
        PERFORM yb_index_check(indexrelid::regclass);
        indexname := indexrelid::regclass::name;
        RETURN NEXT;
    END LOOP;
END;
$$;

SELECT * FROM check_all_indexes_in_table('abcd'::regclass);

       indexname       | yb_index_check
-----------------------+----------------
 abcd_b_c_d_idx        | OK
 abcd_b_c_idx          | OK
 abcd_expr_expr1_d_idx | OK
(3 rows)

Note:

• In YugabyteDB, there is no separate storage for PK indexes. Consequently, pg_table_size() returns null for them, they are not included in the above output. Moreover, PK indexes will always be consistent because the base relation itself acts as the PK index.

Perform consistency check on all the indexes in the current database whose pg_table_size() < 1GB:

CREATE OR REPLACE FUNCTION check_all_indexes_in_db()
RETURNS TABLE(indexname NAME, yb_index_check TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    FOR indexname, yb_index_check IN
        SELECT cls.relname, 'OK'
        FROM pg_class cls
        JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
        WHERE cls.relkind = 'i'  -- 'i' = index
          AND nsp.nspname NOT IN ('pg_catalog', 'information_schema')
          AND pg_table_size(cls.oid) < 1024 * 1024 * 1024
        ORDER BY cls.relname
    LOOP
        PERFORM yb_index_check(indexname::regclass);
        RETURN NEXT;
    END LOOP;
END;
$$;

SELECT * FROM check_all_indexes_in_db();

       indexname       | yb_index_check
-----------------------+----------------
 abcd_b_c_d_idx        | OK
 abcd_b_c_idx          | OK
 abcd_expr_expr1_d_idx | OK
(3 rows)

Troubleshooting

Read restart error

yb_index_check() is not concerned with read-after-commit-visibility. It picks a read time and uses that snapshot (or, in multi-snapshot mode, a sequence of snapshots) to scan both the index and the base relation. If a write that committed before the chosen read time is missing from the snapshot because of clock skew, that is acceptable: its effects are absent from both scans.

Outside a transaction block, yb_index_check() sets yb_read_after_commit_visibility to relaxed for the duration of the call, so this error should not appear.

If you call yb_index_check() from inside a transaction block and hit a Restart read required error, set the following outside the transaction block and retry:

SET yb_read_after_commit_visibility = relaxed;

Snapshot too old error

Any operation that takes longer than timestamp_history_retention_interval_sec (TServer flag, default 900 seconds) under a single snapshot is susceptible to Snapshot too old.

The default multi-snapshot mode is the recommended way to check large indexes.

If you still see Snapshot too old:

1. Confirm you are not forcing single-snapshot mode (single_snapshot_mode => true).
2. Ensure that YSQL configuration parameter yb_bnl_batch_size is 1024 or larger. yb_index_check() uses batched nested loop join and honors this parameter.
3. If you must use single-snapshot mode, increase the runtime-updatable flag timestamp_history_retention_interval_sec for the duration of the check. Make sure to reset the flag value after the index check completes, as not doing so impacts the system's performance and resources. yb_index_check() on an index with pg_table_size() of 3GB took 700 seconds in a single region, multi-AZ 3-node cluster. You can use it as a benchmark to estimate the flag's value.

Multi-snapshot mode was added in issue #26283 to reduce this risk.