How to Check What Your CREATE INDEX Is Doing in PostgreSQL

When you run CREATE INDEX on a very large table, you might have to wait and watch the terminal load until Postgres completes creating the index; there is no feedback saying what is going on. Is the creation in progress, or have other transactions kept the index stuck, and so on. If you are using PostgreSQL 12 or above, you don't have to simply wait. The view pg_stat_progress_create_index shows what each index build is currently doing.

In this blog, we cover the main phases the view shows, using an example of an actual 730MB table.

pg_stat_progress_create_index View and its Columns:It has one row per backend that is running CREATE INDEX or REINDEX, and it covers both normal and CONCURRENTLY variants. The rows disappear when the command finishes.

db_test=# select * from pg_stat_progress_create_index ;
 pid | datid | datname | relid | index_relid | command | phase | lockers_total | lockers_done | current_locker_pid | blocks_total | blocks_done | tuples_total | tuples_done | partitions_total | partitions_done 
-----+-------+---------+-------+-------------+---------+-------+---------------+--------------+--------------------+--------------+-------------+--------------+-------------+------------------+-----------------
(0 rows)

These counters will be reset for each change in phase. Let’s watch the build with an example:

First, let’s create a sample table:

CREATE TABLE index_progress_test (
    id BIGINT,
    customer_id INTEGER,
    description TEXT,
    created_at TIMESTAMP
);

Insert around 10 million rows:

INSERT INTO index_progress_test
SELECT
    g,
    (g % 100000)::INTEGER,
    md5(random()::TEXT) || md5(random()::TEXT),
    NOW() - (g % 1000000) * INTERVAL '1 second'
FROM generate_series(1, 10000000) AS g;

Update the table statistics:

ANALYZE index_progress_test;

Now connect to the same database with the same port on the same Postgres instance in another terminal.

Run this query repeatedly:

SELECT
    pid,
    datname,
    relid::regclass AS table_name,
    index_relid::regclass AS index_name,
    command,
    phase,
    lockers_total,
    lockers_done,
    blocks_total,
    blocks_done,
    ROUND(
        100.0 * blocks_done / NULLIF(blocks_total, 0),
        2
    ) AS blocks_percent,
    tuples_total,
    tuples_done,
    partitions_total,
    partitions_done
FROM pg_stat_progress_create_index;

And in the other session, create an index and watch the status in another session. Run the below create index query:

CREATE INDEX idx_progress_description
ON index_progress_test (description);

You will find the phases keep changing after every few seconds, for example:

  pid   |  datname  |     table_name      | index_name |   command    |             phase              | lockers_total | lockers_done | blocks_total | blocks_done | blocks_percent | tuples_total | tuples_done | partitions_total | partitions_done 
--------+-----------+---------------------+------------+--------------+--------------------------------+---------------+--------------+--------------+-------------+----------------+--------------+-------------+------------------+-----------------
 698822 | to_delete | index_progress_test | -          | CREATE INDEX | building index: scanning table |             0 |            0 |       142858 |      124709 |          87.30 |            0 |           0 |                0 |               0
(1 row)
  pid   |  datname  |     table_name      | index_name |   command    |                phase                | lockers_total | lockers_done | blocks_total | blocks_done | blocks_percent | tuples_total | tuples_done | partitions_total | partitions_done 
--------+-----------+---------------------+------------+--------------+-------------------------------------+---------------+--------------+--------------+-------------+----------------+--------------+-------------+------------------+-----------------
 698822 | to_delete | index_progress_test | -          | CREATE INDEX | building index: sorting live tuples |             0 |            0 |       142858 |      142858 |         100.00 |            0 |           0 |                0 |               0
(1 row)
  pid   |  datname  |     table_name      | index_name |   command    |                 phase                  | lockers_total | lockers_done | blocks_total | blocks_done | blocks_percent | tuples_total | tuples_done | partitions_total | partitions_done 
--------+-----------+---------------------+------------+--------------+----------------------------------------+---------------+--------------+--------------+-------------+----------------+--------------+-------------+------------------+-----------------
 698822 | to_delete | index_progress_test | -          | CREATE INDEX | building index: loading tuples in tree |             0 |            0 |            0 |           0 |                |     10000000 |     1725481 |                0 |               0
(1 row)

These outputs come from three separate runs of the same query, and all of them belong to one Btree build. The counters are reset at each phase change, so each phase is a separate 0 to 100% run.

How the phase name is built

The phase is the combination of two values:

• The first one is the generic phase, which is the same for every index type you create. There are nine of them.
• For the building index phase only, a subphase will be appended after the colon as shown in the example. It comes from the index access method, which is why the text looks like a building index: a scanning table.

Generic phases:

This table contains all the generic phases and what they mean.

A plain CREATE INDEX or REINDEX always goes through initializing and building index phases. The seven other phases belong to the concurrent operation.

Sub-phases of building index

B-tree (the default index)

1. scanning table: this phase reads the table and adds each live row into a sort. blocks_total is the table size in blocks, and blocks_done will count the blocks read so far. In our first example, 124709 of 142858 blocks were read (that is about 87.30%). With parallel workers, the count means the blocks read by every worker.

2. sorting live tuples means sorting the collected entries. The second example shows the block counters frozen at 142858/142858, because they are left at their final values when the scan comes to an end. The sort itself reports no progress, so this row may look stuck on a large table. Actually, it is not stuck, and a larger maintenance_work_mem will reduce it. 

3. sorting dead tuples: this will only appear when the build keeps a second sort for dead rows. That happens when building a unique index, where it is needed to check uniqueness correctly. A nonunique build skips it.

4. loading tuples in tree: this phase writes the sorted rows into the index pages. The block columns go back to 0 here, and the tuple columns are used instead. Our third sample shows 1,725,481 of 10,000,000 tuples loaded. blocks_percent is empty because blocks_total is 0, so use tuples_done / tuples_total for the percentage in this phase.

GIN

In the case of a GIN index, the view reports its own subphases, which mainly matter for parallel builds:

1. scanning table: same meaning as above.

2. sorting tuples (workers): each parallel worker sorts its own partial results.

3. merging tuples (workers): in this phase, each worker will merge its sorted entries into larger chunks for the leader to process.

4. sorting tuples: the leader sorts the combined output from the workers.

5. merging tuples: the leader merges and inserts the entries into the index. This phase reports tuples_total and tuples_done.

And other indexes like hash, GiST, and Brin do not provide subphase names; thus, for those indexes the phase will be just building indexes in the view.

Other columns in the pg_stat_progress_create_index View:

• lockers_total, lockers_done and current_locker_pid :These columns are used by the waiting phases. They will show how many transactions the backend is waiting for and which process it is waiting on right now.
• partitions_total and partitions_done: They are set when you index any partitioned table. They count partitions as each one gets completed.
• command: this is one of CREATE INDEX, CREATE INDEX CONCURRENTLY, REINDEX or REINDEX CONCURRENTLY, whichever one you are using

When an index build seems to hang (actually not hanging), pg_stat_progress_create_index tells you why and what happens under the hood. A plain build goes through scanning, sorting, and loading. A concurrent build adds the waiting phases and a second table scan. Each phase resets its counters, so the progress bar goes from 0 to 100% several times. That is normal, and the table-scan phases are where the time goes on a large table. So with the pg_stat_progress_create_index view, you don't have to wait without any progress message when you are creating an index for a large table.