Why is my Postgres 9.6 Create Index Concurrently INVALID?

Question

Am trying to create an index concurrently on production. However when doing so the index when viewing via \d says INVALID.

Why is this the case? Never seen it happen before.

Have tried re-creating but still get the same issue:

Index:

create index concurrently idx_wallet_customer_id_credit_stake_expires on wallet (customer_id,wallet_credit_stake,wallet_expires)
where wallet_closed is null and wallet_staked is null;

Answer 1

With CONCURRENTLY the index is being built in the background, not taking exclusive locks The final step is to make the index "valid", but that cannot happen until the last concurrent transaction has finished. Check for long-running transactions. Prime suspect would be those with state = 'idle in transaction':

SELECT * FROM pg_stat_activity;

See:

• Estimate end time for a long update

Postgres 12 or later has the system view pg_stat_progress_create_index. See:

• How to find progress of database reindexing on PostgreSQL?

Postgres 9.6 reaches EOL in November 2021, consider upgrading to a current version!