How indisvalid, indisready, and indislive Become False in PostgreSQL Indexes

In PostgreSQL, the three boolean flags in the pg_index system catalog, indislive, indisready, and indisvalid, form a distributed state machine. The pg_index also contains other flags like indnullsnotdistinct, indisprimary, indisunique, indisclustered, and more. This blog discusses the given three flags, indislive, indisready, and indisvalid. These flags are the core mechanism that allows PostgreSQL to perform Concurrent Index Creation (CREATE INDEX CONCURRENTLY) and Concurrent Index Deletion (DROP INDEX CONCURRENTLY) without taking out global, blocking locks on tables.

The Three Flags Defined

Before getting into the lifecycles, it is important to understand exactly what each bit signals to the PostgreSQL storage engine (mdval, heap), executor, and planner:

1. indislive:

• If set true: The index is alive and is now part of the schema. It must be considered in HOT (Heap Only Tuple) safety calculations, which determine whether an update can skip creating a new index entry.
• If set false: The index is in the process of being dropped. It is excluded from HOT-safety calculations and should be ignored by all processes.

2. Indisready:

• If set true: Indicates index is ready for Inserts. The executor must actively update this index whenever an INSERT or UPDATE happens on the base table.
• If set false: Ignore Inserts. Thus write operations to the base table completely ignore this index.

3. indisvalid:

• If set true: Means that the index is valid for queries. The query planner can safely use this index to build scan paths and answer user queries.
• If set false: This means that the index is invalid, that is, the index is considered incomplete or corrupted. The planner will never select it for query execution. Note that an index with indisvalid = false still receives all write updates, it continues to be maintained on every INSERT and UPDATE, but it simply cannot be used for query planning.

Why Do We Need These Distinct Stages?

If you build a standard index, PostgreSQL takes a ShareLock on the table and sets all flags to true instantly, scans the table, and commits. A ShareLock allows concurrent reads but blocks all writes (INSERT, UPDATE, DELETE) for the duration of the build, which can be unacceptably long on large tables.

To prevent this blocking behavior, CONCURRENT operations use a lower-strength lock (ShareUpdateExclusiveLock). Toggling an index from "does not exist" to "fully active" atomically would result in serious data corruption or missing index entries since concurrent sessions are reading and writing to the table at the exact moment the index is changing.

PostgreSQL splits the lifecycle into three distinct transactions, using the flags to tell concurrent processes how to handle the index transition step-by-step.

The Lifecycle: Transitions to TRUE (CREATE INDEX CONCURRENTLY)

When you execute CREATE INDEX CONCURRENTLY, the flags transition through three distinct phases across three separate transactions.

Phase 1: Catalog Registration

• States of the three flags in this phase: indislive = true, indisready = false, indisvalid = false
• The first transaction creates the catalog entry for the index. It marks indislive = true so the system knows the index exists and includes it in HOT-safety decisions, even though it is not yet being written to or used for reads. This is critical: any session planning an update to the table must already account for this index when deciding whether a HOT update is safe.
• Transaction 1 commits. PostgreSQL then deliberately waits for all active transactions that had the base table open to finish (using internal function WaitForLockers). This ensures that any new transaction starting up from this point forward is forced to reload its relation cache (relcache) and see that this index exists.

Phase 2: Open for Modifications

• States will change like: indislive = true, indisready = true, indisvalid = false
• A second transaction starts and toggles indisready = true. From this millisecond onward, any INSERT or UPDATE executed by any user session will write a corresponding index tuple into this new index. This ensures no future data modifications are missed. Still, indisvalid remains false because the index does not have the old historical data.
• Transaction 2 commits and performs the first full table scan, building index entries for all rows visible to its snapshot. PostgreSQL then waits for all older transactions to finish, guaranteeing every active backend has switched to actively maintaining the index.

Phase 3: Validation and Activation

• Finally state changes to: indislive = true, indisready = true, indisvalid = true
• A third transaction performs the second full table scan using a fresh MVCC snapshot, indexing any rows that were inserted or modified during Phase 2's scan and therefore missed by it. PostgreSQL then waits for all transactions holding a snapshot predating this second scan to terminate. Once complete, indisvalid is set to true and the transaction commits. The index is now fully online and available to the query planner.

The Lifecycle: When these flags change to FALSE (DROP INDEX CONCURRENTLY)

Dropping an index concurrently is the reverse architecture, working downward to peel layers off safely so active queries don't break mid-flight.

Phase 1: Stop Reads (Clear Valid)

• Starting state: indislive = true, indisready = true, indisvalid = true
• State after Phase 1: indislive = true, indisready = true, indisvalid = false
• The first transaction clears indisvalid. This immediately tells the query planner to stop planning new queries using this index. However, writing backends must keep updating it because older, long-running queries might still be executing an index scan on it right now.
• The transaction commits, and PostgreSQL waits for all running queries that might be using the index to terminate.

Phase 2: Stop Writes and Mark Dead

• Then states changes to: indislive = false, indisready = false, indisvalid = false
• The second transaction clears both indisready and indislive. This tells all incoming write transactions that updating this index is no longer a waste of performance. The index is officially "dead."
• The transaction commits, and PostgreSQL waits for all active writing transactions to finish to ensure no backend has an open physical write handle to the index files.

Phase 3: Physical Removal

• States will be like: The index is dropped entirely from the catalogs.
• With no one reading and no one writing, PostgreSQL can safely call the storage manager to unlink the physical files (relfilenode) from the disk and delete the row from pg_index and pg_class.

How to Check the Current Values of these Boolean Flags

You can run these commands in your terminal by replacing the index name you need to analyze with “your_index_name” given in the query:

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

The result will look something like:

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

The next time you run CREATE INDEX CONCURRENTLY or DROP INDEX CONCURRENTLY, remember that PostgreSQL is doing much more than simply creating or deleting an index. Behind the scenes, indislive, indisready, and indisvalid coordinate a multi-stage transition that carefully balances planner visibility, write maintenance, and transactional safety.