25

If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?

I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when it's finished.

This is PostgreSQL 9.2/3ish

0

2 Answers 2

23

Combining the comment from @Rory and info in @Zaytsev Dmitry's answer:

The CREATE INDEX CONCURRENTLY will not return until the index has finished building. So you know the index is done when your query returns.

However if you're building a large index that runs for hours you may wonder if it is 'really' still running.

You can use the query for 'invalid' indexes:

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

...if your index appears in the results then it either failed or is still in progress.

Another way to confirm the latter is to check the locks table:

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE mode = 'ShareUpdateExclusiveLock'
ORDER BY a.query_start;

Although CONCURRENTLY does not take exclusive lock on the table it will hold a weaker ShareUpdateExclusiveLock which you should see in the results from this query.

If there is no lock it implies that either the index has finished building, or the create failed and left an invalid index.

Between these two queries you can thus determine which of the three possible states (completed/in progress/failed) your index is in.

4
  • 11
    Note that with Postgres 12 there is a system view that shows the progress of a CREATE INDEX: postgresql.org/docs/current/…
    – user1822
    Nov 18, 2019 at 11:30
  • +1 Thanks for your answer. I've posted a related question: stackoverflow.com/q/74409202/470749
    – Ryan
    Nov 11, 2022 at 23:32
  • i'm still not convinced this is foolproof. If i use an EXPLAIN on my query I don't see my index used (it absolutely should be). You sure about your second query? i removed the WHERE mode = 'ShareUpdateExclusiveLock' from the query, and i see my new index using a RowExclusiveLock along with other indexes being updated on an insert into the table. I don't see it outputted at all in the first query. Using your method and logic, that would imply it failed?
    – friartuck
    Jan 25 at 6:30
  • i also used the query from dba.stackexchange.com/a/161992/225383, and i see my index is reported as 0 bytes, i guess implying it's not done or failed? if it has failed, where can i see why that is? it's a simple index, not unique or anything
    – friartuck
    Jan 25 at 6:31
14

You can get list of invalid indexes.

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

If you see your index in this query it means the index won't work and you have to recreate index.

Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it with CONCURRENTLY flag

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.