Understanding Index State with PITR in YugabyteDB

Point-in-Time Recovery (PITR) in YugabyteDB protects databases from accidental DDL and DML by allowing recovery to a previous point within a configurable retention window.

When PITR is enabled, index behavior can appear confusing at first glance, especially when comparing what the UI shows versus what SQL tools like the \d meta-command in ysqlsh. Users commonly encounter situations such as:

● an index shown as Running in the Master UI but INVALID in \d
● an index that appears to persist after being dropped
● an index build that “seemed to finish” but is still unusable

This tip explains how index state is represented across different layers of YugabyteDB, how PITR intentionally changes index lifecycle behavior, and how to definitively determine whether an index is usable.

🧠 Key concept (read this first)

Index “state” depends on which layer you’re looking at.

Concept	Layer	Meaning
INVALID index	SQL (PostgreSQL catalogs)	Backfill or validation failed
Running	DocDB / Master	Index tablets exist and are healthy
HIDDEN index	DocDB / Master	Dropped under PITR, retained for recovery
Usable by planner	SQL	`pg_index.indisvalid = true`

📌 The UI reflects storage (DocDB) health / PostgreSQL catalogs determine query usability.

🧪 Demo environment: create a cluster

All examples below use a local cluster created with yugabyted.

				
					./bin/yugabyted start \
  --backup_daemon=true \
  --ui=true

📌 --backup_daemon=true is required for PITR.

Create a database and enable PITR

				
					./bin/ysqlsh -c "CREATE DATABASE pitr_demo;"

./bin/yugabyted configure point_in_time_recovery \
  --enable \
  --database pitr_demo \
  --retention 10

Reconnect to the database:

				
					./bin/ysqlsh pitr_demo

Scenario 1: UNIQUE index fails validation (duplicate data)

Step 1: Create test data with duplicates

				
					DROP TABLE IF EXISTS t;

CREATE TABLE t (
  id BIGSERIAL PRIMARY KEY,
  v  INT
);

INSERT INTO t(v)
SELECT g % 100
FROM generate_series(1, 10000) g;

This ensures a UNIQUE index will fail validation.

Step 2: Create a UNIQUE index

				
					CREATE UNIQUE INDEX t_v_uniq_idx ON t(v);

The statement fails due to duplicates:

				
					pitr_demo=# CREATE UNIQUE INDEX t_v_uniq_idx ON t(v);
ERROR:  ERROR:  duplicate key value violates unique constraint "t_v_uniq_idx"

Step 3: Inspect SQL-level index state

				
					SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 't_v_uniq_idx';

				
					pitr_demo=# SELECT
pitr_demo-#   c.relname AS index_name,
pitr_demo-#   i.indisvalid,
pitr_demo-#   i.indisready
pitr_demo-# FROM pg_index i
pitr_demo-# JOIN pg_class c ON c.oid = i.indexrelid
pitr_demo-# WHERE c.relname = 't_v_uniq_idx';
  index_name  | indisvalid | indisready
--------------+------------+------------
 t_v_uniq_idx | f          | t
(1 row)

Interpretation

● indisvalid = false
- ○ Validation failed; the planner will never use this index.
● indisready = true
- ○ The index structure exists and is registered.

📌 This state explains why:

● \d shows INVALID
● the Master UI can still show the index as Running

				
					yugabyte=# \d t;
                            Table "public.t"
 Column |  Type   | Collation | Nullable |            Default
--------+---------+-----------+----------+-------------------------------
 id     | bigint  |           | not null | nextval('t_id_seq'::regclass)
 v      | integer |           |          |
Indexes:
    "t_pkey" PRIMARY KEY, lsm (id HASH)
    "t_v_uniq_idx" UNIQUE, lsm (v HASH) INVALID

We can use curl to query the Master UI which runs on port 7000 by default:

				
					curl -s http://127.0.0.1:7000/tables | awk '
/<legend[^>]*>Index tables<\/legend>/ { in_idx=1; next }
in_idx && /<\/table>/ { in_idx=0 }
in_idx && (/<tr><th/ || /t_v_uniq_idx/) {
  line=$0
  gsub(/<\/th>[[:space:]]*<th[^>]*>/, "|", line)
  gsub(/<\/td>[[:space:]]*<td[^>]*>/, "|", line)
  gsub(/<[^>]+>/, "", line)
  gsub(/Total:/, "Total: ", line)
  gsub(/WAL Files:/, "; WAL Files: ", line)
  gsub(/SST Files Uncompressed:/, "; SST Files Uncompressed: ", line)
  gsub(/SST Files:/, "; SST Files: ", line)
  gsub(/[[:space:]]+/, " ", line)
  print line
}
' | column -s '|' -t

Example outout:

				
					Keyspace   Table Name    State    Message  UUID                              YSQL OID  On-disk size
pitr_demo  t_v_uniq_idx  Running           00004000000030008000000000004007  16391     Total: 1.00M; WAL Files: 1.00M; SST Files: 0B; SST Files Uncompressed: 0B

For a deeper dive on detecting invalid indexes, see: 🔗 Check for Invalid Indexes in YSQL

Scenario 2: Cancel a long-running CREATE INDEX mid-build

This simulates real operational interruptions (cancel, terminate, client disconnect).

Step 1: Create a large table

				
					DROP TABLE IF EXISTS big;
CREATE TABLE big (
  id BIGSERIAL PRIMARY KEY,
  v  INT
);

INSERT INTO big(v)
SELECT (random()*1000000)::int
FROM generate_series(1, 5000000);

Step 2: Start index creation (Session A)

				
					CREATE INDEX big_v_idx ON big(v);

Step 3: Cancel or terminate the build (Session B)

Find the backend:

				
					SELECT pid, state, query
FROM pg_stat_activity
WHERE query ILIKE 'create index%';

Cancel it:

				
					SELECT pg_cancel_backend(<pid>);

If necessary:

				
					SELECT pg_terminate_backend(<pid>);

Step 4: Inspect SQL-level index state

				
					SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'big_v_idx';

				
					pitr_demo=# SELECT
pitr_demo-#   c.relname AS index_name,
pitr_demo-#   i.indisvalid,
pitr_demo-#   i.indisready
pitr_demo-# FROM pg_index i
pitr_demo-# JOIN pg_class c ON c.oid = i.indexrelid
pitr_demo-# WHERE c.relname = 'big_v_idx';
 index_name | indisvalid | indisready
------------+------------+------------
 big_v_idx  | f          | t
(1 row)

Interpretation

● The index made it far enough to be registered (indisready = true)
● The build never completed successfully (indisvalid = false)
● The planner will not use the index
● The Master UI may still show the underlying DocDB table as Running

This is a second, very common reason operators see Running in the UI but INVALID in SQL.

				
					pitr_demo=# \d big
                            Table "public.big"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+---------------------------------
 id     | bigint  |           | not null | nextval('big_id_seq'::regclass)
 v      | integer |           |          |
Indexes:
    "big_pkey" PRIMARY KEY, lsm (id HASH)
    "big_v_idx" lsm (v HASH) INVALID

				
					[root@localhost ~]# curl -s http://127.0.0.1:7000/tables | awk '
/<legend[^>]*>Index tables<\/legend>/ { in_idx=1; next }
in_idx && /<\/table>/ { in_idx=0 }
in_idx && (/<tr><th/ || /big_v_idx/) {
  line=$0
  gsub(/<\/th>[[:space:]]*<th[^>]*>/, "|", line)
  gsub(/<\/td>[[:space:]]*<td[^>]*>/, "|", line)
  gsub(/<[^>]+>/, "", line)
  gsub(/Total:/, "Total: ", line)
  gsub(/WAL Files:/, "; WAL Files: ", line)
  gsub(/SST Files Uncompressed:/, "; SST Files Uncompressed: ", line)
  gsub(/SST Files:/, "; SST Files: ", line)
  gsub(/[[:space:]]+/, " ", line)
  print line
}
' | column -s '|' -t
Keyspace   Table Name  State    Message  UUID                              YSQL OID  On-disk size
pitr_demo  big_v_idx   Running           0000400000003000800000000000400f  16399     Total: 349.57M; WAL Files: 255.32M; SST Files: 94.25M; SST Files Uncompressed: 208.05M

PITR behavior… Dropping an index makes it HIDDEN (not INVALID)

With PITR enabled:

				
					DROP INDEX t_v_uniq_idx;

Immediately afterward, the SQL layer no longer shows the index:

				
					SELECT * FROM pg_indexes WHERE indexname = 't_v_uniq_idx';

Example:

				
					pitr_demo=# DROP INDEX t_v_uniq_idx;
DROP INDEX

pitr_demo=# SELECT * FROM pg_indexes WHERE indexname = 't_v_uniq_idx';
 schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)

➡️ No rows.

However, the Master UI /tables page will show the underlying DocDB object as:

				
					[root@localhost ~]# curl -s http://127.0.0.1:7000/tables | awk '
/<legend[^>]*>Index tables<\/legend>/ { in_idx=1; next }
in_idx && /<\/table>/ { in_idx=0 }
in_idx && (/<tr><th/ || /t_v_uniq_idx/) {
  line=$0
  gsub(/<\/th>[[:space:]]*<th[^>]*>/, "|", line)
  gsub(/<\/td>[[:space:]]*<td[^>]*>/, "|", line)
  gsub(/<[^>]+>/, "", line)
  gsub(/Total:/, "Total: ", line)
  gsub(/WAL Files:/, "; WAL Files: ", line)
  gsub(/SST Files Uncompressed:/, "; SST Files Uncompressed: ", line)
  gsub(/SST Files:/, "; SST Files: ", line)
  gsub(/[[:space:]]+/, " ", line)
  print line
}
' | column -s '|' -t
Keyspace   Table Name    State             Message  UUID                              YSQL OID  On-disk size
pitr_demo  t_v_uniq_idx  Running (HIDDEN)           00004000000030008000000000004007  16391     Total: 1.00M; WAL Files: 1.00M; SST Files: 0B; SST Files Uncompressed: 0B

➡️ State: Running (HIDDEN)

Interpretation

● The index is dropped from SQL catalogs
● The DocDB table is hidden, not deleted
● It is retained until the PITR retention window expires

📌 A HIDDEN index will never appear as INVALID because it no longer exists at the SQL layer.

Why customers report “INVALID in SQL, but Running in the UI”

This does not require DocDB and PostgreSQL to be out of sync.

The most common explanations are:

1. The UI does not show SQL validity: It reflects DocDB tablet health, not pg_index.indisvalid.
2. An index can be indisready = true but indisvalid = false: This occurs when validation fails or a build is interrupted.
3. PITR retains dropped objects as HIDDEN: Name reuse can be misleading… always correlate using OID / UUID, not name alone.

If we re-create the index with the same name, this time as a standard index, it will be create sucessfully.

				
					CREATE INDEX t_v_uniq_idx ON t(v);

The YB Master UI will now show two rows having the same index name:

				
					[root@localhost ~]# curl -s http://127.0.0.1:7000/tables | awk '
/<legend[^>]*>Index tables<\/legend>/ { in_idx=1; next }
in_idx && /<\/table>/ { in_idx=0 }
in_idx && (/<tr><th/ || /t_v_uniq_idx/) {
  line=$0
  gsub(/<\/th>[[:space:]]*<th[^>]*>/, "|", line)
  gsub(/<\/td>[[:space:]]*<td[^>]*>/, "|", line)
  gsub(/<[^>]+>/, "", line)
  gsub(/Total:/, "Total: ", line)
  gsub(/WAL Files:/, "; WAL Files: ", line)
  gsub(/SST Files Uncompressed:/, "; SST Files Uncompressed: ", line)
  gsub(/SST Files:/, "; SST Files: ", line)
  gsub(/[[:space:]]+/, " ", line)
  print line
}
' | column -s '|' -t
Keyspace   Table Name    State             Message  UUID                              YSQL OID  On-disk size
pitr_demo  t_v_uniq_idx  Running (HIDDEN)           00004000000030008000000000004007  16391     Total: 1.00M; WAL Files: 1.00M; SST Files: 0B; SST Files Uncompressed: 0B
pitr_demo  t_v_uniq_idx  Running                    00004000000030008000000000004010  16400     Total: 1.00M; WAL Files: 1.00M; SST Files: 0B; SST Files Uncompressed: 0B

Note that each has a different UUID and YSQL OID.

For advanced diagnostics and consistency checks, see: 🔗 Catching Index Inconsistencies in YugabyteDB

✅ The one command that never lies

If you need the authoritative answer to:

“Is this index usable by the planner?”

Run:

				
					SELECT indisvalid
FROM pg_index
WHERE indexrelid = 'index_name'::regclass;

Only indisvalid = true means the planner can use the index, regardless of what the UI shows.

🧾 Final takeaway

☑︎ With PITR enabled, dropped indexes are hidden, not deleted, at the DocDB layer.
☑︎ The Master UI reflects storage health, while PostgreSQL catalogs determine index validity.
☑︎ An index can be Running yet INVALID, and a HIDDEN index will never appear INVALID because it no longer exists at the SQL layer.

Have Fun!