SQL CONCURRENTLY

What is the SQL CONCURRENTLY keyword in PostgreSQL?

CONCURRENTLY allows certain PostgreSQL index operations to run without blocking reads or writes on the target table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CONCURRENTLY: Supported: PostgreSQL. Not supported: MySQL, SQL Server, Oracle, SQLite, Snowflake.

SQL CONCURRENTLY Full Explanation

CONCURRENTLY is a PostgreSQL-specific modifier that can be appended to CREATE INDEX, DROP INDEX, and REINDEX commands (and indirectly via ALTER TABLE ADD CONSTRAINT ... USING INDEX). When specified, the operation executes in multiple phases that avoid taking a strict ACCESS EXCLUSIVE lock on the table. Queries and data-modification statements can continue normally, greatly reducing downtime on large or mission-critical tables. Because the work is split into phases, the command takes longer overall, writes a bit more WAL, and cannot be executed inside an explicit transaction block (BEGIN … COMMIT). If the operation is interrupted or fails, leftover “invalid” index entries are cleaned up automatically by a subsequent DROP INDEX, REINDEX, or a fresh CONCURRENTLY attempt.

SQL CONCURRENTLY Syntax

CREATE INDEX CONCURRENTLY index_name ON table_name(columns);
DROP INDEX CONCURRENTLY index_name;
REINDEX INDEX CONCURRENTLY index_name;

SQL CONCURRENTLY Parameters

Example Queries Using SQL CONCURRENTLY

-- Create a new index without blocking traffic
CREATE INDEX CONCURRENTLY idx_orders_created_at
ON orders(created_at);

-- Drop an obsolete index online
DROP INDEX CONCURRENTLY idx_orders_old_status;

-- Rebuild a bloated index online (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

Expected Output Using SQL CONCURRENTLY

The statement returns CREATE INDEX, DROP INDEX, or REINDEX confirmation once all phases finish
Application sessions can read and write the target table during the entire process

Use Cases with SQL CONCURRENTLY

Adding indexes on large production tables during business hours
Dropping no-longer-needed indexes with zero downtime
Reindexing bloated or corrupted indexes without a maintenance window

Common Mistakes with SQL CONCURRENTLY

Running the command inside a BEGIN … COMMIT block (PostgreSQL throws: "cannot run CREATE INDEX CONCURRENTLY within a transaction block")
Forgetting that CONCURRENTLY makes the operation slower overall and consumes extra disk space while building the new index
Assuming replication lag will not increase (it often does because of extra WAL)

First Introduced In

PostgreSQL 8.2 (CREATE INDEX CONCURRENTLY)

Frequently Asked Questions

What happens if CREATE INDEX CONCURRENTLY fails?

PostgreSQL leaves a temporary invalid index. Rerun DROP INDEX or REINDEX to clean it up, then retry.

Why can’t I use CONCURRENTLY in a transaction block?

The operation needs to commit after each phase to make catalog changes visible. Wrapping it in a user transaction would block these commits, so PostgreSQL forbids it.

Does CONCURRENTLY lock the table at all?

It takes only a SHARE UPDATE EXCLUSIVE lock, which allows normal SELECT, INSERT, UPDATE, and DELETE but blocks other schema changes like ALTER TABLE.

When should I avoid CONCURRENTLY?

Skip it for small tables where a quick ACCESS EXCLUSIVE lock is negligible or when you require maximum build speed over availability.