`CREATE INDEX CONCURRENTLY` and other non-blocking operations

Capsule overview

You now master the expand-contract pattern (capsule 03). But there's one specific operation that shows up in almost every real expand-contract and that has a critical gotcha in Alembic: creating indexes on tables with active traffic. If you run CREATE INDEX directly, you take a SHARE lock on the table for seconds or minutes, which blocks all the writes (INSERT/UPDATE/DELETE) during that time. The solution is CREATE INDEX CONCURRENTLY, but it CANNOT run inside a transaction — and Alembic, by default, wraps every migration in a transaction. If you copy the pattern without understanding this, you're going to have a migration that fails with ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block right when you need it in production.

This capsule solves that gotcha in two ways (autocommit_block() and transaction_per_migration=False in env.py), and covers the rest of the non-blocking DDL operations you need to know: DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, ALTER TABLE ... VALIDATE CONSTRAINT. You're also going to learn what to do if a CREATE INDEX CONCURRENTLY fails halfway (it leaves an invalid index) and how to clean it up.

By the end you'll have the reflexes to create indexes in production without blocking traffic, and you'll know how to configure Alembic correctly so these operations work with no surprises at deploy time.

Mental model: the index as a parallel guide built without getting in the way

Imagine the tasks table is a library and an index is the catalog list (books ordered by title). A direct CREATE INDEX is like closing the library, making the list in silence, and reopening when it finishes. Nobody consults books while it lasts.

CREATE INDEX CONCURRENTLY is like building the catalog while the library stays open: the librarian makes two passes (the first to record what exists, the second to capture any new book or change that occurred during the first pass). It takes more total time, but nobody notices their work. The readers keep consulting books, the librarians keep adding new ones.

The price of not getting in the way is:

1. It's slower. Two passes vs one.
2. It can't be in a transaction. The two passes need to be individually visible; a transaction would isolate both in an atomic unit that breaks the model.
3. It can fail halfway. If the second pass finds an inconsistency (concurrent writes creating a temporary conflict), the index gets marked as invalid and needs a drop + retry.

Those three trade-offs are what you're going to manage in production. The capsule breaks them down with real code.

The concrete problem: CREATE INDEX on a live table

Let's see it on the migration_test table with 1M rows we created in capsule 02. Assume you need a new index on migration_test.title to speed up searches.

The blocking version (don't use in production)

CREATE INDEX idx_test_title ON migration_test(title);

What happens:

Lock taken: SHARE (on migration_test)
Conflicts with: ROW EXCLUSIVE (INSERT, UPDATE, DELETE)
Compatible with: ACCESS SHARE (SELECT)

Approximate time for 1M rows: 3-8 seconds. During that time:

• ✅ The SELECTs keep working.
• ❌ The INSERTs queue up.
• ❌ The UPDATEs queue up.
• ❌ The DELETEs queue up.

If your app is write-heavy (a CRM, e-commerce, audit logs), those seconds are visible to the customers: requests that normally take 50ms now take 5 seconds. If there are timeouts (typically 30s in HTTP), there are no errors. If there are shorter timeouts (the application, the load balancer), errors start firing.

The non-blocking version (the correct one)

CREATE INDEX CONCURRENTLY idx_test_title ON migration_test(title);

What happens:

Lock taken: SHARE UPDATE EXCLUSIVE (on migration_test)
Conflicts with: another SHARE UPDATE EXCLUSIVE (another CREATE INDEX CONCURRENTLY, VACUUM, ANALYZE)
Compatible with: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE

Approximate time for 1M rows: 5-15 seconds (slower). During that time:

• ✅ The SELECTs keep working.
• ✅ The INSERTs keep working.
• ✅ The UPDATEs keep working.
• ✅ The DELETEs keep working.

Zero impact on production traffic. The price is only more total time. In production, this is always the correct trade-off.

The Alembic gotcha: implicit transactions

If you try to write this "naive" migration:

# alembic/versions/00X_add_index_naive.py
def upgrade():
    op.create_index(
        "idx_test_title",
        "migration_test",
        ["title"],
        postgresql_concurrently=True,  # ← what SQLAlchemy/Alembic offers for CONCURRENTLY
    )

And you run it:

alembic upgrade head

You're going to see:

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Why: Alembic, by default, wraps every migration in a transaction. The intent is safety: if the migration fails halfway, the automatic rollback leaves the DB in a consistent state. For almost all DDL, that's correct. But CREATE INDEX CONCURRENTLY is the explicit exception: PostgreSQL forbids running it inside a transaction because its two passes need to be individually visible.

There are two ways to solve it:

Solution 1: op.get_context().autocommit_block() (recommended for specific cases)

It lets you escape the transaction for a specific operation, keeping the rest of the migration transactional.

# alembic/versions/00X_add_index_concurrently.py
"""add concurrent index on migration_test.title

Revision ID: 00X_add_index
Revises: previous_revision
Create Date: 2026-05-02 16:00:00
"""
from alembic import op


revision = "00X_add_index"
down_revision = "previous_revision"
branch_labels = None
depends_on = None


def upgrade():
    # Exit the transaction so CONCURRENTLY works.
    # After the block, the normal transaction comes back.
    with op.get_context().autocommit_block():
        op.execute(
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_test_title "
            "ON migration_test (title)"
        )


def downgrade():
    with op.get_context().autocommit_block():
        op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_test_title")

Characteristics:

• autocommit_block() isolates the operation. Only the CREATE INDEX CONCURRENTLY runs outside the transaction. If you had other DDL operations in the same migration, they could stay inside the transaction.
• IF NOT EXISTS for idempotence. If the migration runs twice (a deploy with a retry), it doesn't fail the second time. Critical for production (capsule 06 goes deeper into idempotence).
• A symmetric downgrade. DROP INDEX CONCURRENTLY also needs to be outside a transaction.

Solution 2: transaction_per_migration=False in env.py (for apps that create many indexes)

If your app creates CONCURRENTLY indexes frequently and you'd rather not write with autocommit_block() in every migration, you can disable the transactional wrapping globally.

# alembic/env.py
from alembic import context

def run_migrations_online() -> None:
    """Run migrations in 'online' mode."""
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            # The critical change: do NOT wrap each migration in a transaction
            transaction_per_migration=False,
            # The default was True (each migration in its own transaction).
        )

        with context.begin_transaction():
            context.run_migrations()


run_migrations_online()

This option's trade-off:

• Pro: every migration can run CREATE INDEX CONCURRENTLY without autocommit_block().
• Con: if a migration fails halfway, there's no automatic rollback. Each migration is responsible for its own consistency. That demands very small migrations (ideally one operation per migration).

Recommendation: use autocommit_block() (Solution 1) unless your team has adopted atomic migrations as a convention. The block's explicitness makes it obvious to the reader that the operation is outside a transaction.

Other non-blocking operations you need to know

CREATE INDEX CONCURRENTLY isn't the only operation with a "concurrent" version. There are others that show up in production migrations.

DROP INDEX CONCURRENTLY

The same principle: dropping an index takes ACCESS EXCLUSIVE (blocking everything) in the normal version. With CONCURRENTLY, it takes SHARE UPDATE EXCLUSIVE.

def upgrade():
    with op.get_context().autocommit_block():
        op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_test_title")

When to use it: when you need to remove an unused index on a production table. The same trade-off (slower but non-blocking).

REINDEX CONCURRENTLY (PostgreSQL 12+)

Rebuilding an index (useful when it's bloated or corrupt) took ACCESS EXCLUSIVE in the normal version. Since PG 12, there's a CONCURRENTLY version:

def upgrade():
    with op.get_context().autocommit_block():
        op.execute("REINDEX INDEX CONCURRENTLY idx_test_title")

When to use it: when an index is corrupt (rare) or has a lot of bloat after massive UPDATEs/DELETEs. In production, avoid non-concurrent REINDEX.

ALTER TABLE ... VALIDATE CONSTRAINT

You saw it in capsule 03 with the NOT VALID trick. The idea is to add a CHECK constraint as NOT VALID (a short lock, it doesn't validate existing rows), and then VALIDATE it (a lighter lock than a direct SET NOT NULL).

-- Step 1: add it as NOT VALID (a short lock)
ALTER TABLE tasks ADD CONSTRAINT tasks_priority_positive
    CHECK (priority >= 0) NOT VALID;

-- Step 2: validate (SHARE UPDATE EXCLUSIVE — compatible with writes and reads)
ALTER TABLE tasks VALIDATE CONSTRAINT tasks_priority_positive;

VALIDATE CONSTRAINT takes SHARE UPDATE EXCLUSIVE, just like CONCURRENTLY. It doesn't block traffic. That's what makes hardening constraints on large tables safe.

def upgrade():
    op.execute(
        "ALTER TABLE tasks ADD CONSTRAINT tasks_priority_positive "
        "CHECK (priority >= 0) NOT VALID"
    )
    op.execute("ALTER TABLE tasks VALIDATE CONSTRAINT tasks_priority_positive")

(This operation does NOT require autocommit_block() because it can run in a transaction.)

ALTER TABLE ... ATTACH PARTITION

If you have partitioned tables (covered in guide #14), adding a new partition with CONCURRENTLY-like behavior is via ATTACH PARTITION. A short lock on the parent table.

ALTER TABLE tasks ATTACH PARTITION tasks_2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

(Mentioned for completeness; it goes deeper in guide #14.)

What happens if CREATE INDEX CONCURRENTLY fails?

When CREATE INDEX CONCURRENTLY fails halfway (a timeout, a deadlock, a conflict in the second pass), PostgreSQL leaves the index marked as INVALID. The index exists in the catalog but doesn't get used for queries and doesn't protect constraints.

This is a subtle operational problem: the next day you try to run the migration again and you get:

ERROR: relation "idx_test_title" already exists

The IF NOT EXISTS saves you from the error... but the index that exists is INVALID, not valid. Your "successful" new migration created nothing useful.

Diagnosing invalid indexes

-- List every invalid index
SELECT
    n.nspname AS schema,
    c.relname AS table,
    i.relname AS index_name
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE x.indisvalid = false;

Example output:

 schema |     table      |       index_name
--------+----------------+------------------------
 public | migration_test | idx_test_title

Remediating: drop + retry

-- 1. Drop the invalid index
DROP INDEX CONCURRENTLY IF EXISTS idx_test_title;

-- 2. Recreate it
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_test_title ON migration_test (title);

A robust Alembic pattern

For production, write the migration so it handles the case of a pre-existing invalid index:

def upgrade():
    with op.get_context().autocommit_block():
        # Drop it if it exists and is invalid (it does no harm if it doesn't exist)
        op.execute(
            """
            DO $$
            BEGIN
                IF EXISTS (
                    SELECT 1 FROM pg_index x
                    JOIN pg_class i ON i.oid = x.indexrelid
                    WHERE i.relname = 'idx_test_title'
                      AND x.indisvalid = false
                ) THEN
                    EXECUTE 'DROP INDEX CONCURRENTLY idx_test_title';
                END IF;
            END $$;
            """
        )

        # Create it if it doesn't exist (valid or not)
        op.execute(
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_test_title "
            "ON migration_test (title)"
        )

This pattern survives deploy retries where the first attempt failed leaving an invalid index.

Why does this matter in real work?

1. It's Alembic's second most common gotcha in production. The first is ADD COLUMN ... NOT NULL DEFAULT ... that rewrites (capsule 02). The second is CREATE INDEX CONCURRENTLY failing because of Alembic's transaction. Knowing how to solve it saves you a failed deploy iteration.

2. Any non-trivial expand-contract requires new indexes. When you add a column and the app is going to query by it, you need an index. That index gets created with CONCURRENTLY. If you don't know the pattern, it becomes blocking.

3. Index maintenance is the senior backend engineer's responsibility. Indexes become obsolete (queries change), bloated (lots of UPDATE/DELETE), or redundant (duplicates). Cleaning them up in production requires DROP CONCURRENTLY. Without this capsule, you're going to postpone the cleanup out of fear, and accumulate technical debt.

4. It shows up in code review constantly. PRs with op.create_index() without postgresql_concurrently=True are one of the easiest patterns to spot in code review. Having the reflex and being able to explain the why (the transaction block) makes your seniority obvious.

Traps and common mistakes

Mistake 1 (critical operational): copying the code without autocommit_block()

Symptom: the migration fails at deploy with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. You've lost deploy time and you have to redo it.

Why it happens: Alembic wraps every migration in a transaction by default. The dev doesn't know about the gotcha and writes the migration "like any other."

How to tell: any migration using CONCURRENTLY (in CREATE INDEX, DROP INDEX, REINDEX) MUST have autocommit_block() or a modified env.py. If not, it's going to fail.

How to fix it: wrap the operation in with op.get_context().autocommit_block():. There's no case where you don't need this (unless you configured transaction_per_migration=False in env.py for the whole project).

Mistake 2 (conceptual): thinking "a short lock" equals "it doesn't block"

Symptom: a dev measures that CREATE INDEX (non-concurrent) takes 2 seconds locally. They decide to run it in production arguing "it's only 2 seconds." In production the table is 10x bigger, it takes 20 seconds. During those 20 seconds, thousands of INSERTs queue up.

Why it happens: the intuition of "2 seconds doesn't matter" is misleading when there's continuous traffic. An API receiving 100 INSERTs per second is going to have 2000 INSERTs queued by the end of the 20 seconds. Those 2000 requests got 20 seconds of latency.

How to tell: any operation taking a lock more restrictive than SHARE UPDATE EXCLUSIVE (CONCURRENTLY, normal VACUUM) has to be used in production. Non-concurrent CREATE INDEX takes SHARE, which blocks writes. Unviable for apps with constant writes.

How to fix it: a simple rule: CREATE INDEX always with CONCURRENTLY in production. Except in initial migrations over an empty table.

Mistake 3 (operational): not handling the invalid index case

Symptom: the "successful" migration didn't create the expected index. The query that depended on the index is still slow. Investigating, you discover the index is marked INVALID and the CREATE INDEX CONCURRENTLY IF NOT EXISTS didn't recreate it because "it already exists."

Why it happens: a previous CREATE CONCURRENTLY attempt failed (a timeout, a deadlock, a statement_timeout) and left the index invalid. The new run detects the name exists and skips.

How to tell: after every migration that creates an index, verify with the "invalid indexes" query (shown above). If you find invalid ones, they're latent bugs.

How to fix it: use the "drop if invalid + create" pattern shown above. Or, alternatively, monitor pg_index.indisvalid in healthchecks and alert.

Mistake 4 (conceptual): using IF EXISTS / IF NOT EXISTS without understanding idempotence

Symptom: a dev adds IF NOT EXISTS "just because," thinking it's a best practice. But the migration has several steps and the IF NOT EXISTS masks a badly-done intermediate state.

Why it happens: confusion between "idempotent" (re-running produces the same final result) and "tolerant of a partial state" (it doesn't fail if something is already created). The first is desirable, the second can hide bugs.

How to tell: IF NOT EXISTS is fine for individual operations (CREATE INDEX, ADD COLUMN). It's wrong when it masks the fact that the migration should verify consistency before continuing.

How to fix it: use IF NOT EXISTS only for genuine idempotence. For state checks, use DO $$ ... $$ blocks with explicit logic (like the "drop if invalid + create" example).

Mistake 5 (conceptual): assuming CONCURRENTLY is always better

Symptom: a dev uses CONCURRENTLY even in an initial migration over an empty table. The migration takes 30 seconds when the normal version would have taken 1 second.

Why it happens: the "always concurrent" reflex doesn't distinguish contexts. CONCURRENTLY is for tables with active traffic. On an empty table or in an initial setup, there's no traffic to protect.

How to tell: is the table empty or nearly empty? Are you in an initial migration where the app hasn't been deployed yet? If so, a normal CREATE INDEX is correct and faster.

How to fix it: CONCURRENTLY for tables with production traffic. Normal for initialization or over empty tables.

Exercises

Exercise 1: identify and fix a migration that fails from the gotcha

This migration fails at deploy. Identify the cause and propose a fix.

# alembic/versions/wrong_concurrent_index.py
def upgrade():
    op.create_index(
        "idx_tasks_priority_concurrent",
        "tasks",
        ["priority"],
        postgresql_concurrently=True,
    )
    op.execute("ANALYZE tasks")  # update the stats after the index

def upgrade():
    # Take it out of the transaction for CONCURRENTLY
    with op.get_context().autocommit_block():
        op.execute(
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tasks_priority_concurrent "
            "ON tasks (priority)"
        )
        op.execute("ANALYZE tasks")


def downgrade():
    with op.get_context().autocommit_block():
        op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_tasks_priority_concurrent")

Exercise 2: configure Alembic to auto-detect invalid indexes

Implement a scripts/check_invalid_indexes.py script that runs as part of the CI pipeline. It has to query pg_index and fail (exit code != 0) if it finds invalid indexes in the DB.

# scripts/check_invalid_indexes.py
"""Verifies there are no invalid indexes in the DB.
Runs as part of the CI/CD pipeline to detect
CONCURRENTLY indexes that failed silently.
"""
import os
import sys

import psycopg2


DATABASE_URL = os.environ.get("DATABASE_URL")
if not DATABASE_URL:
    print("ERROR: DATABASE_URL is not set", file=sys.stderr)
    sys.exit(1)


def main() -> int:
    conn = psycopg2.connect(DATABASE_URL)
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT
                    n.nspname AS schema_name,
                    c.relname AS table_name,
                    i.relname AS index_name
                FROM pg_index x
                JOIN pg_class c ON c.oid = x.indrelid
                JOIN pg_class i ON i.oid = x.indexrelid
                JOIN pg_namespace n ON n.oid = c.relnamespace
                WHERE x.indisvalid = false
                  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
                ORDER BY n.nspname, c.relname, i.relname
                """
            )
            invalid_indexes = cur.fetchall()
    finally:
        conn.close()

    if not invalid_indexes:
        print("OK: 0 invalid indexes detected")
        return 0

    print(f"ERROR: {len(invalid_indexes)} invalid index(es) detected:")
    for schema, table, index in invalid_indexes:
        print(f"  - {schema}.{table} -> {index}")
    print()
    print("Recommended action: drop the invalid index and recreate it:")
    for schema, table, index in invalid_indexes:
        print(
            f"  DROP INDEX CONCURRENTLY {schema}.{index};\n"
            f"  -- Then recreate it according to the original migration"
        )
    return 1


if __name__ == "__main__":
    sys.exit(main())

# .github/workflows/ci.yml
- name: Check invalid indexes
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
  run: python scripts/check_invalid_indexes.py

Exercise 3: implement the robust "drop if invalid + create" pattern

Write an Alembic migration that creates the index idx_tasks_tenant_priority on tasks(tenant_id, priority) idempotently and robustly against deploy retries. If a previous attempt left an invalid index, it has to clean it up and recreate.

# alembic/versions/00X_add_tasks_tenant_priority_index.py
"""add idx_tasks_tenant_priority CONCURRENTLY (idempotent)

Revision ID: 00X_tasks_tenant_priority_idx
Revises: previous_revision
Create Date: 2026-05-02 17:00:00
"""
from alembic import op


revision = "00X_tasks_tenant_priority_idx"
down_revision = "previous_revision"
branch_labels = None
depends_on = None

INDEX_NAME = "idx_tasks_tenant_priority"
TABLE_NAME = "tasks"
COLUMNS = "(tenant_id, priority)"


def upgrade():
    with op.get_context().autocommit_block():
        # Step 1: if it exists and is invalid, drop it first.
        # This handles the case of a retry after a previous failure.
        op.execute(
            f"""
            DO $$
            BEGIN
                IF EXISTS (
                    SELECT 1 FROM pg_index x
                    JOIN pg_class i ON i.oid = x.indexrelid
                    WHERE i.relname = '{INDEX_NAME}'
                      AND x.indisvalid = false
                ) THEN
                    RAISE NOTICE 'Dropping invalid index {INDEX_NAME}';
                    EXECUTE 'DROP INDEX CONCURRENTLY {INDEX_NAME}';
                END IF;
            END $$;
            """
        )

        # Step 2: create the index if it doesn't exist (valid or not).
        # IF NOT EXISTS makes the operation idempotent for retries.
        op.execute(
            f"""
            CREATE INDEX CONCURRENTLY IF NOT EXISTS {INDEX_NAME}
            ON {TABLE_NAME} {COLUMNS}
            """
        )

        # Step 3: update the statistics so the query planner
        # uses the new index from the start.
        op.execute(f"ANALYZE {TABLE_NAME}")

        # Step 4: post-creation check: the index has to be valid.
        # If for some reason it got created invalid, fail the migration.
        result = op.get_bind().execute(
            sa.text(
                """
                SELECT x.indisvalid
                FROM pg_index x
                JOIN pg_class i ON i.oid = x.indexrelid
                WHERE i.relname = :name
                """
            ),
            {"name": INDEX_NAME},
        ).scalar()

        if result is None:
            raise RuntimeError(f"Index {INDEX_NAME} was not created correctly")
        if result is False:
            raise RuntimeError(
                f"Index {INDEX_NAME} was created but is marked INVALID. "
                f"Investigate pg_stat_activity for the cause of the failure."
            )


def downgrade():
    with op.get_context().autocommit_block():
        op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {INDEX_NAME}")

Exercise 4: use VALIDATE CONSTRAINT instead of a direct SET NOT NULL

On the tasks table with 30M rows, you need to make the priority column NOT NULL (assuming the backfill already completed and every row has a value). A direct ALTER TABLE tasks ALTER COLUMN priority SET NOT NULL takes a prolonged lock from the full scan. Rewrite the operation using the NOT VALID + VALIDATE trick.

# alembic/versions/00X_priority_set_not_null_with_validate.py
"""make tasks.priority NOT NULL using NOT VALID + VALIDATE trick

Revision ID: 00X_priority_not_null
Revises: previous_revision
"""
from alembic import op


revision = "00X_priority_not_null"
down_revision = "previous_revision"


def upgrade():
    op.execute("SET lock_timeout = '5s'")

    # Step 1: add the CHECK constraint as NOT VALID — a brief ACCESS EXCLUSIVE lock.
    # PostgreSQL accepts the constraint without checking existing rows (it's metadata-only).
    op.execute(
        """
        ALTER TABLE tasks
        ADD CONSTRAINT tasks_priority_not_null_check
        CHECK (priority IS NOT NULL) NOT VALID
        """
    )

    # Step 2: validate the constraint — a SHARE UPDATE EXCLUSIVE lock (compatible with writes).
    # PostgreSQL scans the table but does NOT block reads or writes.
    # If it finds a row with priority IS NULL, the operation fails.
    # This is the step that takes time (proportional to the size), but without blocking.
    op.execute("ALTER TABLE tasks VALIDATE CONSTRAINT tasks_priority_not_null_check")

    # Step 3: now that the constraint is validated, SET NOT NULL is metadata-only.
    # PostgreSQL already knows the property holds, it just records it as NOT NULL.
    # A brief lock.
    op.alter_column("tasks", "priority", nullable=False)

    # Step 4: the CHECK constraint is now redundant (NOT NULL implies the check).
    # We drop it for cleanliness.
    op.execute("ALTER TABLE tasks DROP CONSTRAINT tasks_priority_not_null_check")


def downgrade():
    # Going back to nullable is metadata-only (a brief lock).
    op.execute("SET lock_timeout = '5s'")
    op.alter_column("tasks", "priority", nullable=True)

Exercise 5: drop an unused index in production

Your monitoring (pg_stat_user_indexes) shows the index idx_tasks_old_status hasn't been used in 6 months. You want to drop it to reduce maintenance overhead (every UPDATE/INSERT updates every index). The table has 100M rows, in 24/7 production. Design the migration.

# alembic/versions/00X_drop_unused_idx_tasks_old_status.py
"""drop unused index idx_tasks_old_status (CONCURRENTLY)

Revision ID: 00X_drop_old_status_idx
Revises: previous_revision
Create Date: 2026-05-02 18:00:00

Justification:
pg_stat_user_indexes shows this index hasn't been used in 6 months.
Check with:
    SELECT idx_scan FROM pg_stat_user_indexes
    WHERE indexrelname = 'idx_tasks_old_status';
Result: 0 (in the last 180 days).
"""
from alembic import op


revision = "00X_drop_old_status_idx"
down_revision = "previous_revision"


INDEX_NAME = "idx_tasks_old_status"


def upgrade():
    # A direct DROP INDEX takes ACCESS EXCLUSIVE — it blocks EVERYTHING during the drop.
    # CONCURRENTLY takes SHARE UPDATE EXCLUSIVE — compatible with traffic.
    with op.get_context().autocommit_block():
        op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {INDEX_NAME}")


def downgrade():
    # Recreate the index: also CONCURRENTLY so as not to block.
    # Assumes we know the original definition.
    with op.get_context().autocommit_block():
        op.execute(
            f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {INDEX_NAME} "
            "ON tasks (status) "
            "WHERE deleted_at IS NULL"  # Assuming it was a partial index
        )

-- 1. Confirm the index isn't used
SELECT
    schemaname,
    relname AS table,
    indexrelname AS index,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_tasks_old_status';

-- Expected: idx_scan = 0 or very low

-- 2. Confirm there are no foreign keys or constraints depending on the index
SELECT conname, contype
FROM pg_constraint
WHERE conindid = (
    SELECT oid FROM pg_class WHERE relname = 'idx_tasks_old_status'
);

-- Expected: 0 rows

Summary and next step

In this capsule you learned:

• CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, compatible with reads and writes. It's the "non-blocking" version for production.
• CONCURRENTLY can't run inside a transaction. Alembic wraps every migration in a transaction by default. You have to escape with op.get_context().autocommit_block() or configure transaction_per_migration=False globally.
• The most common gotcha: copying op.create_index(..., postgresql_concurrently=True) without autocommit_block() produces the error cannot run inside a transaction block at deploy.
• DROP INDEX CONCURRENTLY and REINDEX CONCURRENTLY follow the same non-blocking pattern.
• ALTER TABLE ... VALIDATE CONSTRAINT is the other key operation: combined with ADD CONSTRAINT ... NOT VALID, it lets you harden constraints on large tables without a prolonged lock.
• If CONCURRENTLY fails, it leaves an invalid index that IF NOT EXISTS doesn't detect. You have to drop and recreate, or monitor pg_index.indisvalid.
• The correct reflex: CREATE INDEX always with CONCURRENTLY in production, except in an initial migration over an empty table.

Before moving on you should be able to:

• Write an Alembic migration that uses autocommit_block() for CREATE INDEX CONCURRENTLY correctly.
• Decide between a targeted autocommit_block() or a global transaction_per_migration=False.
• Diagnose and clean up invalid indexes with queries over pg_index.
• Apply the "drop if invalid + create" pattern, robust against retries.
• Use the NOT VALID + VALIDATE trick for SET NOT NULL on large tables.
• Articulate why CONCURRENTLY takes more total time but less lock per unit of time.

Next capsule — lock_timeout and statement_timeout in migrations. You've learned to do non-blocking DDL operations. But even CONCURRENTLY can hang if there's another active lock on the table. Capsule 05 teaches you the defensive reflex of setting timeouts at the start of every dangerous migration: if for some reason the operation can't take the lock it needs, fail fast instead of hanging. It's what separates migrations that break "cleanly" (an immediate rollback) from migrations that hang deploys for hours.

Resources

1. PostgreSQL — CREATE INDEX CONCURRENTLY — the official documentation, including what happens when it fails halfway.
2. Alembic — autocommit_block — the exact API for the auto-commit block in Alembic.
3. PostgreSQL — Monitoring index usage — queries for detecting unused or invalid indexes.
4. Crunchy Data — Index maintenance in PostgreSQL — an operational guide on index maintenance at scale.
5. GitLab — Adding indexes safely — GitLab's playbook for adding indexes in production.
6. Strong Migrations — Adding indexes — the most common anti-patterns in CREATE INDEX.

Module 5 — SQL Patterns for Production APIs Guide

Next capsule: lock_timeout and statement_timeout in migrations — the defensive reflex for making migrations fail fast instead of hanging deploys.