Detect and Repair Index Corruption

TSB stores its configuration in PostgreSQL. PostgreSQL uses indexes to speed up lookups, for example to find an object by its primary key.

Note that indexes are internal data structures that PostgreSQL itself maintains on disk and TSB does not manage them.

If an index gets corrupted, it can drift out of sync with the table it indexes. The table data is still there, but queries that go through the index can return wrong results. This page shows how to spot index corruption, confirm it with the PostgreSQL amcheck extension, and repair it.

Before you get started, make sure:

✓ You can connect to the PostgreSQL database used by TSB.
✓ You can connect as a superuser, or as a user allowed to create extensions.
✓ You have a recent backup of the database.

Symptoms

Index corruption can show up in different ways, and the symptoms are often confusing because the data looks both present and missing at the same time. Common signs include:

• TSB returns errors, or does not find an object, even though the object shows up when you list objects of that type.
• Operations fail with duplicate key errors for objects that do not exist.
• The same object appears in the UI or in tctl get output in one place but not in another.
• Management Plane components log errors that reference records you can see in the database.

Check index integrity with amcheck

Use the amcheck extension, included with PostgreSQL, to find every corrupted index in the TSB database.

1. Connect to the TSB database (tsb by default; see spec.dataStore in the ManagementPlane spec) on the primary instance and enable the extension.

   CREATE EXTENSION IF NOT EXISTS amcheck;

2. Run pg_amcheck against the TSB database. The tool is included with PostgreSQL 14 and later.

   pg_amcheck -d tsb

   tip

   To also detect table rows missing from an index, add --heapallindexed. The check is more thorough but slower.

3. Review the output. If pg_amcheck does not report any relations, no corruption was detected. Otherwise, note the names of all reported indexes so you can repair them later. For example:

   btree index "tsb.public.<index_name>":
       ERROR:  item order invariant violated for index "<index_name>"

Repair corrupted indexes

REINDEX builds an index again from the table data, which replaces the corrupted copy.

1. To avoid errors while you repair the database, it is recommended to scale down the tsb and iam deployments first:

   kubectl scale deployment tsb iam -n tsb --replicas 0

   Scaling down these deployments only stops configuration changes while the repair is in progress. It does not affect the data plane or running services.

2. On the primary instance, rebuild each index that pg_amcheck reported:

   REINDEX INDEX <index_name>;

3. Run pg_amcheck again, as described in Check index integrity with amcheck, and confirm that it no longer reports any corruption.

4. Scale the TSB components back up.

   kubectl scale deployment tsb iam -n tsb --replicas 1

You can leave the amcheck extension installed for future checks. It has no effect on normal operation. To remove it, run DROP EXTENSION amcheck;.

Find the root cause

Index corruption is likely to be related to underlying issues in the PostgreSQL database itself, the OS, or its persistent storage. It is therefore advised to work with your database team to find the root cause in order to prevent future corruption.

Check database integrity around upgrades

Upgrades exercise the database more than normal operation: corruption that is already there can make an upgrade fail in ways that are hard to diagnose.

Add a database integrity check to your upgrade runbook:

1. Before the upgrade: run pg_amcheck and repair any corruption before you start. This makes sure the system is in a good state.
2. After the upgrade: run pg_amcheck again to confirm that the database is still consistent.

See TSB Upgrade Best Practices for the full upgrade checklist.