REINDEX DATABASE CONCURRENTLY mydb;
which could take several hours, or even days, depending on the size of the database, is there anyway to get a rough estimate of its progress?
I've seen some forum posts claiming you can query index creation status with a query like:
SELECT now()::TIME(0), a.query, p.phase, p.blocks_total, p.blocks_done, p.tuples_total, p.tuples_done, FROM pg_stat_progress_create_index p JOIN pg_stat_activity a ON p.pid = a.pid;
The _done/_total columns in combination with phase does provide a rough progress percent. However, this only lists the progress of the currently updating index. It doesn't tell you how many other indexes are pending update, much less how much work there is to do for each.
Edit: I've tried combining the views
pg_index, which lists the
*_ccnew temporary indexes used by the concurrent process, with
SELECT relname, CASE WHEN blocks_total > 0 THEN (ci.blocks_done/ci.blocks_total::numeric*100)::int ELSE NULL END as blocks_percent, i.* FROM pg_class as pgc inner join pg_index as i on i.indexrelid = pgc.oid left outer join pg_stat_progress_create_index as ci on ci.index_relid = i.indexrelid WHERE i.indisvalid = false;
but this shows strange results. For my database, it lists ~300 indexes in pg_index that are temporary, and waiting to be updated. However, the one index cross referenced by pg_stat_progress_create_index that updates never is marked valid. It gets to 100% of blocks processed, and then disappears from
pg_stat_progress_create_index but its
indisvalid stays false. Why is this?