How to find progress of database reindexing on PostgreSQL?

When running:

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 pg_stat_progress_create_index like:

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?

After reviewing the columns in pg_index, it looks like Postgres uses indisvalid = false to denote all the indexes being rebuilt and the subset of those with indisready = true denotes the indexes that pg_stat_progress_create_index has processed.

Using that, I think I can calculate a total progress percent with a query like:

SELECT
    m.complete_steps,
    m.total_steps,
    m.step_percent,
    (m.complete_steps/m.total_steps::numeric*100)::int AS total_percent
FROM (
    SELECT
    MAX(CASE WHEN blocks_total > 0 THEN (ci.blocks_done/ci.blocks_total::numeric*100)::int ELSE NULL END) AS step_percent,
    COUNT(*) AS total_steps,
    COUNT(CASE WHEN i.indisready THEN 1 ELSE NULL END) AS complete_steps
    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
) AS m

StackExchange Database Administrators Q#295203, answer score: 2