Concurrent Index Builds on Spatial Tables

Part of Spatial Schema Migrations & Evolution, this technique adds or rebuilds a GiST index on a geometry column while the table stays open to reads and writes. For a cadastral platform where survey_boundaries is continuously updated by field crews, a plain CREATE INDEX is not an option: it would freeze every write for the length of the build. CREATE INDEX CONCURRENTLY trades a longer build and two table scans for the guarantee that no INSERT, UPDATE, or DELETE is blocked while the index is constructed. The cost of that guarantee is a more fragile process — a failed build leaves an INVALID index behind — so operating it safely means monitoring progress and validity, not just firing the statement.

CREATE INDEX vs CREATE INDEX CONCURRENTLY — write availability Plain CREATE INDEX holds a SHARE lock that blocks writes for the whole build; CREATE INDEX CONCURRENTLY runs two scans while writes continue, ending with a validity check. CREATE INDEX SHARE lock writes BLOCKED for entire build time CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE scan 1: build scan 2: validate mark valid writes CONTINUE throughout time (longer, but non-blocking) on failure: index left INVALID — must be dropped concurrently

The distinction matters most on the tables that most need indexing. The bigger survey_boundaries grows, the longer a plain build holds its lock and the more write traffic it stalls. The concurrent variant is covered end to end for the extreme case — a hundred-million-row table — in CREATE INDEX CONCURRENTLY on Large Spatial Tables. This page establishes the mechanics, monitoring, and failure recovery that apply to any live spatial table.

Prerequisites and Infrastructure Validation

Confirm the target column carries an SRID. A GiST index over a geometry column with SRID 0 still builds, but the planner cannot combine it with SRID-qualified predicates, so validate first:

sql
-- Declared SRID and geometry type of the column to be indexed
SELECT f_table_schema, f_table_name, f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_name = 'survey_boundaries';
-- Expect e.g. srid = 25833, type = 'MULTIPOLYGON' (a projected UTM zone)

Confirm no leftover INVALID index blocks a fresh build:

sql
-- List existing indexes and their validity on the target table
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'survey_boundaries'::regclass;
-- indisvalid = false marks a failed concurrent build to clean up first

Python baseline: psycopg >= 3.1. The one non-obvious requirement is that the connection issuing the concurrent build must be in autocommit mode — the statement cannot run inside a transaction block. SQLAlchemy >= 2.0 users must reach for an AUTOCOMMIT execution option or the raw DBAPI connection.

Core Execution Workflow

Step 1 — Why the Plain Build Locks Writes

CREATE INDEX acquires a SHARE lock on the table. SHARE is compatible with reads (ACCESS SHARE) but conflicts with ROW EXCLUSIVE, the lock every INSERT, UPDATE, and DELETE takes. The result: reads proceed, writes queue behind the build. On a small lookup table that is a blink; on a growing survey_boundaries table it can be minutes of stalled field-crew updates.

sql
-- Blocking build — acceptable ONLY on a table with no concurrent writes
-- (e.g. during an offline maintenance window)
CREATE INDEX idx_survey_boundaries_geom
ON survey_boundaries
USING GIST (geom);

Use this form only when you can guarantee no writes for the build’s duration. Otherwise, reach for the concurrent variant.

Step 2 — Issue CREATE INDEX CONCURRENTLY

The concurrent build takes a weaker SHARE UPDATE EXCLUSIVE lock, which does not conflict with ROW EXCLUSIVE, so writes continue. It pays for that with two full table scans and a wait for in-flight transactions between them.

sql
-- Non-blocking build: reads and writes continue throughout.
-- Must NOT be wrapped in BEGIN/COMMIT.
CREATE INDEX CONCURRENTLY idx_survey_boundaries_geom
ON survey_boundaries
USING GIST (geom);

From Python, force autocommit before issuing the statement:

python
import psycopg

def build_gist_concurrently(dsn: str, index_sql: str) -> None:
    # autocommit=True is mandatory: CREATE INDEX CONCURRENTLY manages its
    # own transaction boundaries and errors inside an explicit transaction.
    with psycopg.connect(dsn, autocommit=True) as conn:
        with conn.cursor() as cur:
            cur.execute(index_sql)

build_gist_concurrently(
    "host=db-primary dbname=cadastre user=migrator",
    """
    CREATE INDEX CONCURRENTLY idx_survey_boundaries_geom
    ON survey_boundaries
    USING GIST (geom)
    """,
)

Step 3 — Monitor Progress

A concurrent GiST build on a large table can run for a long time. pg_stat_progress_create_index reports the live phase and block counts so you are not guessing:

sql
-- Live progress of any in-flight index build
SELECT
    p.phase,
    p.blocks_done,
    p.blocks_total,
    round(100.0 * p.blocks_done / NULLIF(p.blocks_total, 0), 1) AS pct,
    p.tuples_done,
    a.query
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a USING (pid);

The phase column walks through values such as building index: scanning table, building index: sorting, and waiting for writers before validation. A build parked in a waiting for ... phase is blocked by a long-running transaction — find and, if safe, terminate it, because concurrent builds wait for every transaction that started before them.

Step 4 — Verify Index Validity

A concurrent build can finish the SQL statement yet leave an unusable index if the second scan hit a problem. Always confirm validity before relying on it:

sql
-- The single most important check after any concurrent build
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'survey_boundaries'::regclass
  AND c.relname = 'idx_survey_boundaries_geom';
-- Success: indisvalid = true AND indisready = true

If indisvalid is false, the build failed and the index must be cleaned up (Step 5). A valid index still needs statistics:

sql
ANALYZE survey_boundaries;

Step 5 — Recover From an INVALID Index

A failed concurrent build does not roll itself back — it leaves an INVALID index that the planner ignores but that still consumes storage and slows writes, because every INSERT must maintain it even though queries never use it. Drop it concurrently and retry:

sql
-- Identify any invalid indexes across the database
SELECT c.relname AS index_name, n.nspname AS schema
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indisvalid = false;

-- Remove the failed index without blocking writes, then rebuild
DROP INDEX CONCURRENTLY idx_survey_boundaries_geom;

Only after the invalid index is dropped can you reissue CREATE INDEX CONCURRENTLY. Attempting to recreate it under the same name while the invalid one lingers raises a duplicate-relation error.

Step 6 — Rebuild an Existing Index With REINDEX CONCURRENTLY

Adding a new index is one case; refreshing a bloated or corrupted one is another. REINDEX CONCURRENTLY rebuilds an existing GiST index in place without the drop-and-recreate dance, keeping the same name and remaining non-blocking:

sql
-- Rebuild a bloated GiST index online; the old index serves queries until swap
REINDEX INDEX CONCURRENTLY idx_survey_boundaries_geom;

This is the right tool after a bulk load fragments the GiST tree, or after the in-place reprojection rewrites every geometry and leaves the index describing the old coordinate space. Like the build, REINDEX CONCURRENTLY leaves an INVALID index (suffixed _ccnew) on failure, so re-run the validity check afterward.

Step 7 — Building on Partitioned Spatial Tables

Large cadastral datasets are frequently partitioned — survey_boundaries split by municipality or by survey year. CREATE INDEX CONCURRENTLY cannot be run directly on a partitioned parent table, so the pattern is to build a concurrent index on each partition and then attach a matching index on the parent:

sql
-- 1. Build concurrently on every leaf partition, one at a time
CREATE INDEX CONCURRENTLY idx_sb_2025_geom
ON survey_boundaries_2025 USING GIST (geom);

CREATE INDEX CONCURRENTLY idx_sb_2026_geom
ON survey_boundaries_2026 USING GIST (geom);

-- 2. Create an INVALID parent index without a build, then attach the children.
-- ONLY builds the catalog entry; it does not scan or lock the partitions.
CREATE INDEX idx_survey_boundaries_geom
ON ONLY survey_boundaries USING GIST (geom);

ALTER INDEX idx_survey_boundaries_geom
    ATTACH PARTITION idx_sb_2025_geom;
ALTER INDEX idx_survey_boundaries_geom
    ATTACH PARTITION idx_sb_2026_geom;

The parent index flips from INVALID to valid automatically once every partition has an attached child index. Because each leaf build is concurrent, writes to individual partitions are never blocked. Iterate the leaf builds from Python by querying pg_inherits for the partition list and running one autocommit build per child.

python
import psycopg

def build_partition_indexes(dsn: str, parent: str) -> None:
    with psycopg.connect(dsn, autocommit=True) as conn:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT child.relname
                FROM pg_inherits
                JOIN pg_class parent ON parent.oid = inhparent
                JOIN pg_class child  ON child.oid  = inhrelid
                WHERE parent.relname = %s
                ORDER BY child.relname
                """,
                (parent,),
            )
            partitions = [r[0] for r in cur.fetchall()]
        for part in partitions:
            idx = f"idx_{part}_geom"
            with conn.cursor() as cur:
                cur.execute(
                    f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {idx} "
                    f"ON {part} USING GIST (geom)"
                )

Performance Considerations

Reading the Build’s Resource Profile

A concurrent GiST build is bounded by maintenance_work_mem and, for the sort phase, by parallel workers. Raise both before starting a large build:

Parameter Suggested value Effect on the build
maintenance_work_mem 1GB–2GB Larger in-memory sort batches; fewer temp-file spills
max_parallel_maintenance_workers 2–4 Parallelises the build scan and sort on large tables
deadlock_timeout leave default The build waits on writers; do not lower it reflexively
sql
-- Set at session level in the same autocommit connection before the build
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

Confirming the Planner Adopts the Index

After ANALYZE, verify a spatial predicate actually uses the new index rather than a sequential scan:

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT boundary_id
FROM survey_boundaries
WHERE geom && ST_MakeEnvelope(381000, 5811000, 386000, 5816000, 25833);

Look for a Bitmap Index Scan on idx_survey_boundaries_geom. If you still see Seq Scan, the index may be INVALID, statistics may be stale, or random_page_cost may be too high for the storage. For a deeper toolkit on shaping GiST indexes and reading plans, see Advanced GiST Indexing & Optimization.

Common Failure Modes and Fixes

Build Fails Inside a Transaction Block

Diagnosis: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

Fix: the connection is not in autocommit. In psycopg set autocommit=True on connect; in SQLAlchemy use engine.connect().execution_options(isolation_level="AUTOCOMMIT") or run the statement on the raw DBAPI connection. Never wrap it in with conn.transaction():.

Build Stalls in a “waiting” Phase

Diagnosis: pg_stat_progress_create_index.phase sits on waiting for writers before validation and does not advance.

Fix: a transaction older than the build is holding it up. Concurrent builds must wait for every snapshot that predates them. Find the culprit and decide whether to wait or cancel it:

sql
SELECT pid, state, xact_start, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_start ASC
LIMIT 10;
-- A long-idle "idle in transaction" session is the usual offender

Leftover INVALID Index After a Crash

Diagnosis: a build interrupted by a connection drop or a server restart leaves indisvalid = false.

Fix: drop it concurrently before retrying, as shown in Step 5. Do not DROP INDEX (non-concurrent) on a live table — that reintroduces a blocking lock the concurrent build was meant to avoid.

Concurrent Build Never Uses the SRID Predicate

Diagnosis: the index builds and validates, but queries with an SRID-qualified envelope still Seq Scan.

Fix: confirm the query envelope SRID matches the column SRID. An ST_MakeEnvelope(..., 4326) against a geometry(MultiPolygon, 25833) column forces an implicit transform the GiST index cannot serve. Build the envelope in the column’s CRS (25833 here), or reproject deliberately. Cross-check accumulating index bloat over time with detecting GiST index bloat.

Verification

sql
-- 1. Index exists and is valid
SELECT c.relname, i.indisvalid, i.indisready
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'survey_boundaries'::regclass
  AND c.relname = 'idx_survey_boundaries_geom';   -- both true

-- 2. No INVALID indexes remain anywhere
SELECT count(*) FROM pg_index WHERE indisvalid = false;   -- expect 0

-- 3. The planner uses it against a same-SRID envelope
EXPLAIN (ANALYZE, BUFFERS)
SELECT boundary_id
FROM survey_boundaries
WHERE geom && ST_MakeEnvelope(381000, 5811000, 386000, 5816000, 25833);

-- 4. Statistics are current
ANALYZE survey_boundaries;

A healthy result shows indisvalid = true, zero invalid indexes database-wide, and a Bitmap Index Scan on the new index in step 3.

Frequently Asked Questions

Why does a plain CREATE INDEX block writes on a spatial table?

A plain CREATE INDEX takes a SHARE lock on the table, and SHARE conflicts with the ROW EXCLUSIVE lock that INSERT, UPDATE, and DELETE acquire. Reads keep working because they only need ACCESS SHARE, but every write waits until the build completes. On a large geometry table that can mean minutes of stalled writes, which is why production builds use the concurrent form.

Why can’t CREATE INDEX CONCURRENTLY run inside a transaction block?

The concurrent build performs two separate table scans and waits for older transactions to finish between them, so it manages its own transaction boundaries and cannot be nested inside an explicit BEGIN/COMMIT. In psycopg you must set the connection to autocommit before issuing the statement; in SQLAlchemy you use the AUTOCOMMIT isolation level.

What happens if a concurrent GiST build fails partway through?

PostgreSQL leaves an INVALID index behind. It exists in the catalog, but the planner ignores it while every write still pays to maintain it, so it is pure overhead. You must remove it with DROP INDEX CONCURRENTLY and restart the build. Checking pg_index.indisvalid after every concurrent build is how you catch this before it silently degrades write throughput.

Is REINDEX CONCURRENTLY safe to run on a busy production table?

Yes, that is its purpose. It builds a fresh copy of the index alongside the old one, keeps the old one serving queries until the swap, and takes only a brief lock at the very end to exchange them. Like a concurrent build it can leave an INVALID transient index on failure, so verify validity afterward and drop any leftover _ccnew index concurrently.