Building Unique PostgreSQL Indexes Concurrently

If PostgreSQL must reject duplicate values in a busy table, create a unique index, a data structure PostgreSQL uses to find rows and enforce uniqueness, with CONCURRENTLY when a regular build’s write block is unacceptable. A unique index makes PostgreSQL reject writes that would create duplicate indexed values. Concurrent creation lets ordinary inserts, updates, and deletes continue while PostgreSQL builds the index, but takes longer and can leave an invalid index behind if it fails. Check PostgreSQL’s recorded status for the index before treating the change as complete. PostgreSQL documents these behaviors in its CREATE INDEX reference.

The index sets which values must be unique. A multicolumn index makes each combination of values unique, while a partial index applies only to rows that meet its WHERE condition. By default, PostgreSQL treats null values as distinct for uniqueness, so multiple nulls are allowed.

Choose how PostgreSQL builds the index

A regular build is the simpler option when the table can stop accepting writes until the index is ready:

CREATE UNIQUE INDEX users_email_key
    ON public.users (email);

Readers can query the table during a regular index build, but PostgreSQL blocks inserts, updates, and deletes on the table until the build finishes. PostgreSQL's write block may be unacceptable on a busy production table.

To let ordinary writes continue, create the index concurrently:

CREATE UNIQUE INDEX CONCURRENTLY users_email_key
    ON public.users (email);

PostgreSQL performs two table scans and pauses at several points for transactions or older snapshots, which are transaction views of the data from an earlier time, that could affect the result. The scans and waits make concurrent creation slower and use more CPU and disk input/output, which can affect other database activity. PostgreSQL still coordinates with transactions and does not allow table schema changes during the build.

Run concurrent index creation outside a transaction block, a group of commands that PostgreSQL commits or rolls back together. Run no more than one concurrent index build on the same table at a time. PostgreSQL does not directly support concurrent index creation on a partitioned table, a table split into smaller tables called partitions. See the documentation for the per-partition approach and check the details for your PostgreSQL version. The CREATE INDEX documentation covers these rules.

Track the build and its waits

PostgreSQL exposes the current phase of an index build through pg_stat_progress_create_index. Use the view to distinguish scanning from waits on other transactions:

SELECT
    pid,
    command,
    phase,
    lockers_done,
    lockers_total,
    blocks_done,
    blocks_total,
    tuples_done,
    tuples_total,
    current_locker_pid
FROM pg_stat_progress_create_index
WHERE relid = 'public.users'::regclass;

The phase shows whether PostgreSQL is waiting for writers, building the index, checking it, or waiting for old snapshots, which are transaction views of the data from an earlier time. Progress counters depend on the current phase, so unchanged scan counters do not by themselves mean the command is stuck. The progress reporting documentation explains the fields and phases.

Treat a failed build as a catalog change

A concurrent build can fail because PostgreSQL finds duplicate values or encounters another problem, such as a deadlock, where transactions wait for each other to release resources. PostgreSQL may leave an index entry marked INVALID in its system catalog, which records database objects. PostgreSQL cannot safely use an invalid index when choosing how to run queries, but writes can still incur overhead from it.

PostgreSQL can begin enforcing uniqueness before a unique concurrent index becomes valid for queries. Writes may receive duplicate-key errors while the build is still running. If the build fails during its second scan, the invalid index can continue enforcing uniqueness. A failed command therefore does not prove that the table is unconstrained or that the remaining index is usable.

Check the index before retrying or removing it. If the index is invalid, fix the underlying problem and drop the index before trying again. For example:

DROP INDEX CONCURRENTLY IF EXISTS public.users_email_key;

Run DROP INDEX CONCURRENTLY outside a transaction block as well. Then retry the concurrent build after correcting the duplicate data or other failure condition.

Verify the index’s status and definition

The pg_index system catalog records whether an index is unique, valid for queries, and ready to receive changes. Query it after the command completes:

SELECT
    ns.nspname AS table_schema,
    tbl.relname AS table_name,
    idx.relname AS index_name,
    pg_get_indexdef(i.indexrelid) AS definition,
    i.indisunique,
    i.indisvalid,
    i.indisready,
    i.indislive
FROM pg_index AS i
JOIN pg_class AS idx
    ON idx.oid = i.indexrelid
JOIN pg_class AS tbl
    ON tbl.oid = i.indrelid
JOIN pg_namespace AS ns
    ON ns.oid = tbl.relnamespace
WHERE ns.nspname = 'public'
  AND tbl.relname = 'users'
  AND idx.relname = 'users_email_key';

For a completed unique index that is still in use, check that indisunique, indisvalid, indisready, and indislive are all true. indisvalid shows whether PostgreSQL considers the index safe to use for queries. When it is false, the catalog documentation warns that uniqueness is not guaranteed. indisready indicates whether inserts and updates maintain the index. The pg_index catalog reference explains these flags.

Check the definition as well as the flags. Confirm that the index covers the intended columns or calculated expressions, and see whether a condition limits which rows it applies to. Columns added with INCLUDE are stored in the index but do not participate in its uniqueness test. In psql, \d also marks an invalid index as INVALID; the catalog query shows the status flags.

Convert the index into a constraint when needed

A unique index enforces uniqueness. A unique constraint records that rule in the table schema and uses a unique B-tree index to enforce it. If you want the schema to show the rule as a constraint, create the unique index concurrently and then attach it:

ALTER TABLE public.users
    ADD CONSTRAINT users_email_key
    UNIQUE USING INDEX users_email_key;

PostgreSQL accepts only certain indexes for this conversion. The index must be a B-tree index with default sort ordering, an ordered index structure, and cannot be partial or use calculated expressions instead of columns. Check the constraint documentation and ALTER TABLE reference before choosing this option.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com