Why is my “CREATE INDEX CONCURRENTLY ..” command blocked by a “SELECT FROM” query? (Postgres 9.6)

I was running a migration to create an index, but the migration was blocked by another query. I resolved the problem after discovering that there was another query blocking my migrations; and after cancelling the blocking query, I was able to successfully run the migration.

(My server is using Postgres 9.6 on Linux)

Here is how I discovered that my migration was blocked:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

which returned the following result:

| pid | usename | query                                                                           | blocking_id | blocking_query                                                              |
|-----+---------+---------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------|
| 123 | my_user | CREATE  INDEX CONCURRENTLY "idx_orders_on_widget_id" ON "orders"  ("widget_id") | 456         | SELECT  "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2 |

How is it possible that my migration (the CREATE INDEX CONCURRENTLY .. query) was being blocked by the SELECT .. FROM .. query? Even if the process running the blocking query was in a zombie state, I don’t understand how my index creation query can be blocked by a “SELECT .. FROM ..” query.

Can anyone offer insights about how this is possible?

If it helps, here is my schema (simplified for this question):

Orders

id

widget_id

customer_id (FK to Customers)

Customers

id

company_id (FK to Companies)

Companies

id

Some additional notes:

I discovered that the index was blocked about ~8 hours after I ran the migration.
After killing the blocking query, I dropped the idx_orders_on_widget_id index and re-ran the migration to re-create the index.
After re-running the migration a second time, the index creation was finished in ~10 mins
The server is running on Linux with Postgres 9.6

Answer

The documentation says:

PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate.

Advertisement

Answer