PostgreSQL has the ability to monitor the progress of index creation, which is very helpful for long-running queries.
PostgreSQL added the ability to monitor the progress of index creation in v12.0 (2019) by querying the pg_stat_progress_create_index view . It requires the statistics collector to be enabled (which it is by default). Superusers and users with the pg_read_all_stats role can query the progress of every index being created, and all other users will be restricted to only seeing the progress of indexes they are creating.
Index creation is broken down into "phases" or steps, and the number increases if you use the CONCURRENTLY option. Different phases process different entities (lockers, tuples, and blocks), so the pg_stat_progress_create_index view breaks these out into different columns. See the progress reporting docs for information about what entity is relevant when.
Here is a query that shows the progress of phases and the progress within each phase during index creation:
1SELECT p.pid
2 , now() - a.query_start AS duration
3 , n.nspname AS schema_name
4 , c.relname AS table_name
5 , (
6 SELECT relname
7 FROM pg_class
8 WHERE oid = p.index_relid
9 ) AS index_name
10 , '(' || CASE
11 WHEN p.phase = 'initializing' THEN '1'
12 WHEN p.phase = 'waiting for writers before build' THEN '2'
13 WHEN p.phase = 'building index: scanning table' THEN (CASE WHEN p.command LIKE '%CONCURRENTLY%' THEN '2' ELSE '3' END)
14 WHEN p.phase = 'building index: sorting live tuples' THEN '4'
15 WHEN p.phase = 'building index: sorting dead tuples' THEN '5'
16 WHEN p.phase = 'building index: loading tuples in tree' THEN '6'
17 WHEN p.phase = 'waiting for writers before validation' THEN '7'
18 WHEN p.phase = 'index validation: scanning index' THEN '8'
19 WHEN p.phase = 'index validation: sorting tuples' THEN '9'
20 WHEN p.phase = 'index validation: scanning table' THEN '10'
21 WHEN p.phase = 'waiting for old snapshots' THEN '11'
22 WHEN p.phase = 'waiting for readers before marking dead' THEN '12'
23 WHEN p.phase = 'waiting for readers before dropping' THEN '13'
24 ELSE '?'
25 END || '/' || CASE
26 WHEN p.command LIKE '%REINDEX CONCURRENTLY%' THEN '13'
27 WHEN p.command LIKE '%CONCURRENTLY%' THEN '11'
28 ELSE '5'
29 END || ') ' || p.phase AS phase_progress
30 , format('%s%% (%s/%s)',
31 coalesce(round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 2)::TEXT, '0'),
32 p.blocks_done,
33 p.blocks_total
34 ) AS blocks_progress
35 , format('%s%% (%s/%s)',
36 coalesce(round(100.0 * p.tuples_done / nullif(p.tuples_total, 0), 2)::TEXT, '0'),
37 p.tuples_done,
38 p.tuples_total
39 ) AS tuples_progress
40 , a.query
41FROM pg_stat_progress_create_index p
42JOIN pg_stat_activity a
43 ON p.pid = a.pid
44JOIN pg_class c
45 ON p.relid = c.oid
46JOIN pg_namespace n
47 ON c.relnamespace = n.oid;
