how to see progress of concurrent index creation in postgres?
- User
how to see progress of concurrent index creation in postgres?
Assistant
Here's how you can monitor concurrent index creation progress in PostgreSQL:
- Using
pg_stat_progress_create_indexview (PostgreSQL 12+):
SELECT pid, phase, blocks_total, blocks_done, tuples_total, tuples_done, round((blocks_done::float / blocks_total) * 100, 2) as progress_percentage FROM pg_stat_progress_create_index;- You can also check the process using
pg_stat_activity:
SELECT pid, state, query FROM pg_stat_activity WHERE query LIKE 'CREATE INDEX%';- For system-level monitoring, you can use the process ID (pid) to check I/O activity:
iostat -x 1 # or iotop -p <pid>The
pg_stat_progress_create_indexview is the most informative as it shows:- Current phase of index creation
- Number of blocks processed vs total blocks
- Number of tuples processed vs total tuples
- Calculated percentage of completion
- Using
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.