PostgresqlHow-ToBeginner · 3 min read

How to Create Concurrent Index in PostgreSQL Quickly

In PostgreSQL, you create a concurrent index using CREATE INDEX CONCURRENTLY. This allows the index to be built without locking writes on the table, so your database stays responsive during the operation.

📐

Syntax

The basic syntax to create a concurrent index is:

CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);
CREATE INDEX: Command to create an index.
CONCURRENTLY: Keyword to build the index without locking writes.
index_name: Name you give to the new index.
table_name: The table on which the index is created.
column_name: The column(s) to index.

sql

CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);

💻

Example

This example shows how to create a concurrent index on the email column of a users table. It lets you keep inserting or updating rows while the index builds.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);

-- Insert some sample data
INSERT INTO users (email) VALUES ('alice@example.com'), ('bob@example.com');

-- Create concurrent index on email
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Output

CREATE TABLE INSERT 0 2 CREATE INDEX

⚠️

Common Pitfalls

Common mistakes when creating concurrent indexes include:

Trying to run CREATE INDEX CONCURRENTLY inside a transaction block, which is not allowed.
Not waiting for the index creation to finish before querying the index.
Using CREATE INDEX without CONCURRENTLY on large tables, which locks writes and can cause downtime.

Always run CREATE INDEX CONCURRENTLY as a standalone statement, not inside BEGIN ... COMMIT.

sql

BEGIN;
CREATE INDEX CONCURRENTLY idx_wrong ON users (email);
COMMIT;

-- This will cause an error: "CREATE INDEX CONCURRENTLY cannot run inside a transaction block"

-- Correct way:
CREATE INDEX CONCURRENTLY idx_right ON users (email);

📊

Quick Reference

Keyword	Description
CREATE INDEX	Command to create an index
CONCURRENTLY	Builds index without locking writes
index_name	Name of the index
table_name	Table to index
(column_name)	Column(s) to index

✅

Key Takeaways

Use CREATE INDEX CONCURRENTLY to build indexes without blocking writes.

Do not run CREATE INDEX CONCURRENTLY inside a transaction block.

Concurrent index creation is slower but keeps your database responsive.

Always name your indexes clearly for easier maintenance.

Wait for the concurrent index creation to complete before relying on it.