cubic
Try cubic for free

Create PostgreSQL Indexes Concurrently on Live Tables

Flags PostgreSQL index builds and drops that lock live tables: CREATE INDEX without CONCURRENTLY, concurrent builds in transactions, INVALID leftovers.

Catch this in your code

What it flags

Flaggedmigrations/20260912_orders_indexes.sql
BEGIN;

-- Blocks all writes to orders until COMMIT
CREATE INDEX orders_customer_id_created_at_idx
  ON orders (customer_id, created_at DESC);

CREATE UNIQUE INDEX orders_external_ref_key
  ON orders (external_ref)
  WHERE external_ref IS NOT NULL;

DROP INDEX orders_customer_id_idx;

COMMIT;
Passesmigrations/20260912_orders_indexes.sql
-- No BEGIN/COMMIT: CONCURRENTLY cannot run inside a transaction block.
-- Applied with psql -f, which sends each statement separately.
CREATE INDEX CONCURRENTLY orders_customer_id_created_at_idx
  ON orders (customer_id, created_at DESC);

CREATE UNIQUE INDEX CONCURRENTLY orders_external_ref_key
  ON orders (external_ref)
  WHERE external_ref IS NOT NULL;

DROP INDEX CONCURRENTLY orders_customer_id_idx;

Why it matters

A plain CREATE INDEX on a large table blocks every INSERT, UPDATE, and DELETE for as long as the build runs, which can be minutes on production data and milliseconds on a development database. CONCURRENTLY keeps writes flowing, but it fails inside a transaction block, and a build that fails partway leaves an INVALID index that adds write overhead without serving any query. A one-line index migration looks harmless in a diff, so reviewers rarely ask how large the table is or how the migration runner wraps the statement.

Agent instructions

cubic checks every pull request against these instructions. Paste them into AGENTS.md or Cursor rules and your AI coding tool follows them while it writes code.

AGENTS.md

Add this section to AGENTS.md at the repository root. Codex, Cursor, and other AI coding tools read it.

## Create PostgreSQL Indexes Concurrently on Live Tables

Applies to `**/*.sql`, `**/migrations/**`, `**/migrate/**`.

Review PostgreSQL migrations for index builds and drops that block reads or writes on live tables.

Flag changes that:
- Run `CREATE INDEX` or `CREATE UNIQUE INDEX` on an existing table without `CONCURRENTLY`. A plain build holds a SHARE lock that blocks INSERT, UPDATE, and DELETE until the transaction ends.
- Put `CREATE INDEX CONCURRENTLY`, `DROP INDEX CONCURRENTLY`, or `REINDEX ... CONCURRENTLY` inside a transaction block: an explicit `BEGIN`, a framework's per-migration transaction (Rails without `disable_ddl_transaction!`, Django without `atomic = False`), or a multi-statement file the runner sends as one query string, which PostgreSQL runs as one implicit transaction.
- Use `CREATE INDEX CONCURRENTLY IF NOT EXISTS` with no cleanup of a failed earlier build. A failed concurrent build leaves an INVALID index that queries ignore and writes still maintain, and `IF NOT EXISTS` skips the rebuild because the name exists.
- Drop an index on a live table with plain `DROP INDEX`, which takes an ACCESS EXCLUSIVE lock on the table, instead of `DROP INDEX CONCURRENTLY`.
- Rebuild an index with plain `REINDEX`, which blocks writes to the table and reads that use the index, instead of `REINDEX ... CONCURRENTLY`.

Do not flag:
- Indexes on tables created earlier in the same migration.
- Schema dumps, seed files, and test fixtures, such as `structure.sql` or `schema.sql`, that describe the schema and do not run against a live database.
- Lookup or enum tables that the schema or PR shows are small and rarely written.

Checks files matching **/*.sql, **/migrations/**, **/migrate/**.

More PostgreSQL agents

See all PostgreSQL code review standards

Run this agent on every pull request

cubic reviews every pull request against the agents you turn on and comments on the lines that break them.

Catch this in your code