Does CREATE INDEX lock the table in PostgreSQL?

Short answer

CREATE INDEX orders_customer_id_idx ON orders (customer_id) takes a SHARE lock on the table and holds it while it reads every row and builds the index. During that time:

• SELECT keeps working.
• INSERT, UPDATE and DELETE wait, all of them, until the build finishes.

The table isn’t rewritten. On our 3-million-row table the build took 0.65 to 0.75 s; on a large production table it can take minutes, and writes are blocked for all of it. Use CREATE INDEX CONCURRENTLY on any table that takes writes.

What it locks

• SHARE on the table. It conflicts with ROW EXCLUSIVE, the lock every write takes, and with schema changes and VACUUM. It doesn’t conflict with the ACCESS SHARE lock of a SELECT, or with another SHARE lock, so two plain CREATE INDEX on the same table can run at the same time.
• ACCESS EXCLUSIVE on the new index itself. Nobody else can see the index until you commit, so this doesn’t affect anyone.

The lock is held until the transaction ends. A plain CREATE INDEX can run inside BEGIN … COMMIT, unlike the concurrent kind, so keep that transaction short.

Waiting for the lock blocks writes too. If a transaction has written to the table and not yet committed, CREATE INDEX waits for it, and new writes queue behind the CREATE INDEX even though they wouldn’t conflict with the open transaction. Reads still get through. We saw exactly this on all five versions: with an uncommitted INSERT open, a waiting CREATE INDEX made a new INSERT wait, while a SELECT ran.

Does it rewrite the table?

No. The table’s data file is untouched; PostgreSQL reads it once and writes a new index file. The read is a full scan of the table, so the time grows with the table size, not with how selective the index is.

Run it safely

On a table that takes writes, use CREATE INDEX CONCURRENTLY. It doesn’t block reads or writes, though it takes longer, can’t run in a transaction, and needs a check afterwards. See CREATE INDEX CONCURRENTLY.

If you use a plain CREATE INDEX (a small table, a quiet period, or inside a transaction with other DDL), limit both the wait and the build:

SET lock_timeout = '2s';         -- give up if a writer holds the table
SET statement_timeout = '30s';   -- give up if the build runs longer than you can block writes
CREATE INDEX orders_created_at_idx ON orders (created_at);

If the lock wait times out you get ERROR: canceling statement due to lock timeout and nothing has changed; retry after a pause (lock timeout). If the build times out you get ERROR: canceling statement due to statement timeout, the index isn’t created, and you know the table is too big to index this way (statement timeout).

Give the build more memory. Index builds sort in maintenance_work_mem; raising it for the session can make a large build faster:

SET maintenance_work_mem = '1GB';

SET changes it for your session only. Pick a value the server has free, since the build can use that much memory.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), in a rolled-back transaction on a 3-million-row table:

CREATE TABLE seo_mig.big (id bigint, n int, s text);
INSERT INTO seo_mig.big SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 3000000) g;

BEGIN;
SELECT pg_relation_filenode('seo_mig.big');
CREATE INDEX big_n_idx ON seo_mig.big (n);
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT pg_relation_filenode('seo_mig.big');   -- unchanged
ROLLBACK;

CREATE UNIQUE INDEX on id took the same locks. Then a second session held CREATE INDEX t_a_idx ON seo_mig.t (a) open on a 1,000-row table while a third ran statements with lock_timeout = '1s'. Identical on all five versions:

For the queue, one session kept an uncommitted INSERT open for 4 s; a CREATE INDEX started half a second later and waited 3.5 s; meanwhile a SELECT ran and a new INSERT timed out waiting.

In Inlet

The structure editor shows the CREATE INDEX statement before it runs. If writes start piling up behind an index build, the Activity monitor shows which session blocks which, and lets you cancel the build.