debug_postgres_index.md

Debugging index concurrent creation in Postgres.

Check if an index is still being created.

The following should return something if an index is still being created on another process.

SELECT 
  a.datname, 
  l.relation::regclass, 
  l.transactionid, 
  l.mode, 
  l.GRANTED, 
  a.usename, 
  a.query, 
  a.query_start, 
  age(now(), a.query_start) AS "age", 
  a.pid 
FROM pg_stat_activity a 
JOIN pg_locks l 
ON l.pid = a.pid
WHERE mode = 'ShareUpdateExclusiveLock' 
ORDER BY a.query_start

If the result is empty (and the index still isn't there), then it's likely that the index creation failed.

Check for invalid indexes in pg_index

SELECT * 
FROM pg_class, pg_index 
WHERE pg_index.indisvalid = false 
  AND pg_index.indexrelid = pg_class.oid;

This might return something like:

oid	relname	relnamespace	....
123	my_index	1234	....

In this case, you can tell postgres to reindex this index to make it valid:

REINDEX INDEX CONCURRENTLY my_index;

Docs: https://www.postgresql.org/docs/current/sql-reindex.html