ALTER TABLE ADD COLUMN Geometry Without Locking

This page addresses one narrow, high-stakes case within adding geometry columns to live tables: a rail_segments table with a hundred million rows, thousands of inserts a minute, and no maintenance window, onto which you must add a shape geometry column without ever holding an ACCESS EXCLUSIVE lock long enough to block the write path. At this scale the difference between a metadata-only ALTER and a heap-rewriting one is the difference between a non-event and a paging incident.

Why the naive approach fails

The instinct is to add the column already populated, in one statement, so the schema is “done” immediately:

sql
-- DANGEROUS on a large, high-traffic table.
ALTER TABLE rail_segments
    ADD COLUMN shape geometry(LineString, 4326)
    DEFAULT ST_SetSRID(ST_MakeLine(
        ST_MakePoint(start_lon, start_lat),
        ST_MakePoint(end_lon,   end_lat)), 4326);

This is a table rewrite. A non-constant default forces PostgreSQL to compute shape for every one of the hundred million existing rows, and it does so while holding ACCESS EXCLUSIVE — the strongest lock, which blocks reads and writes — for the entire rewrite. On a large table that is minutes of total outage, and worse, the moment the statement starts waiting for the lock it queues behind any in-flight query and then blocks every transaction that arrives after it. A second failure hides in it too: even a plain ADD COLUMN ... DEFAULT with a constant is safe in modern PostgreSQL, but the instant the default is an expression over other columns, the fast-path optimization is disabled and the rewrite is unavoidable.

The fix is to separate the fast catalog change from the slow data change, and to make the slow part a sequence of small, interruptible steps that never hold a strong lock.

Production-ready implementation

The following is the complete, copy-paste sequence. Each phase is independently committed and resumable, and no phase holds a lock that blocks the write path for more than a few milliseconds.

python
import time
import psycopg

CONN = "postgresql://migrator@primary:5432/transit"

# ── Phase 1: catalog-only column add, bounded lock wait ──────────────────────
def add_column(conn_str: str) -> None:
    with psycopg.connect(conn_str, autocommit=True) as conn:
        with conn.cursor() as cur:
            # Fail fast if the brief ACCESS EXCLUSIVE lock cannot be had in 3s,
            # so we never queue behind a long transaction and stall writers.
            cur.execute("SET lock_timeout = '3s'")
            cur.execute(
                "ALTER TABLE rail_segments "
                "ADD COLUMN shape geometry(LineString, 4326)"  # nullable, no default
            )

# ── Phase 2: batched backfill; each batch its own short transaction ──────────
BACKFILL = """
WITH batch AS (
    SELECT segment_id
    FROM   rail_segments
    WHERE  shape IS NULL
    ORDER  BY segment_id
    LIMIT  %(n)s
    FOR UPDATE SKIP LOCKED          -- step over rows a writer is holding
)
UPDATE rail_segments r
SET    shape = ST_SetSRID(
                   ST_MakeLine(ST_MakePoint(r.start_lon, r.start_lat),
                               ST_MakePoint(r.end_lon,   r.end_lat)),
                   4326)
FROM   batch
WHERE  r.segment_id = batch.segment_id
"""

def backfill(conn_str: str, batch_size: int = 3000, pause: float = 0.15) -> int:
    total = 0
    with psycopg.connect(conn_str) as conn:
        cur = conn.cursor()
        cur.execute("SET statement_timeout = '30s'")  # cap a pathological batch
        while True:
            cur.execute(BACKFILL, {"n": batch_size})
            affected = cur.rowcount
            conn.commit()                 # release row locks each batch
            total += affected
            if affected == 0:
                return total
            time.sleep(pause)             # throttle for replica lag

# ── Phase 3: constraints NOT VALID, then validate under a weaker lock ─────────
def add_constraints(conn_str: str) -> None:
    with psycopg.connect(conn_str, autocommit=True) as conn:
        cur = conn.cursor()
        cur.execute("SET lock_timeout = '3s'")
        cur.execute(
            "ALTER TABLE rail_segments ADD CONSTRAINT chk_rail_shape_srid "
            "CHECK (ST_SRID(shape) = 4326) NOT VALID"
        )
        cur.execute(
            "ALTER TABLE rail_segments ADD CONSTRAINT chk_rail_shape_valid "
            "CHECK (shape IS NULL OR ST_IsValid(shape)) NOT VALID"
        )
        # VALIDATE scans rows under SHARE UPDATE EXCLUSIVE; writers proceed.
        cur.execute("ALTER TABLE rail_segments VALIDATE CONSTRAINT chk_rail_shape_srid")
        cur.execute("ALTER TABLE rail_segments VALIDATE CONSTRAINT chk_rail_shape_valid")

# ── Phase 4: GiST index built concurrently, outside any transaction ──────────
def build_index(conn_str: str) -> None:
    # autocommit is mandatory: CREATE INDEX CONCURRENTLY rejects a txn block.
    with psycopg.connect(conn_str, autocommit=True) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "CREATE INDEX CONCURRENTLY idx_rail_segments_shape "
                "ON rail_segments USING gist (shape)"
            )

if __name__ == "__main__":
    add_column(CONN)
    backfill(CONN)
    add_constraints(CONN)
    build_index(CONN)

Phase 1 returns in milliseconds because a nullable column with no default only edits the catalog. Phase 2 never locks more than batch_size rows at once and commits each batch, so autovacuum can reclaim dead tuples between iterations and the write path is never blocked. Phase 3 pays the row-scan cost of validation under SHARE UPDATE EXCLUSIVE, which permits concurrent inserts and updates. Phase 4 builds the spatial index without a write lock. The batched-backfill mechanics here — the keyset window, SKIP LOCKED, and derivation from the endpoint columns — are the same pattern generalised in backfilling geometry from latitude/longitude columns, and the concurrent build has its own deeper treatment in CREATE INDEX CONCURRENTLY on large spatial tables.

Configuration and tuning knobs

lock_timeout. The most important knob for Phases 1 and 3. Set it to 3–5 seconds so a brief-lock DDL that cannot acquire its lock fails fast and can be retried, rather than queueing behind a long transaction and blocking every writer that arrives after it. Wrap each locking statement in a retry loop with exponential backoff.

batch_size. Governs the trade-off between throughput and contention in Phase 2. On a high-traffic table, 2,000–5,000 rows per batch keeps each transaction short. Watch pg_stat_replication lag; if replicas fall behind, lower the batch size or raise the inter-batch pause.

statement_timeout on the backfill session. Caps a single batch so an unexpected sequential scan cannot run unbounded. Thirty seconds is generous for a well-indexed keyset batch and still catches pathological cases.

maintenance_work_mem for Phase 4. Raise it for the index-building session only — SET maintenance_work_mem = '1GB' — to speed the GiST build. Keep it session-scoped so it does not multiply across autovacuum workers.

Autovacuum on the table. During the backfill, tighten it so dead tuples from the updates are reclaimed promptly:

sql
ALTER TABLE rail_segments SET (autovacuum_vacuum_scale_factor = 0.02);

Verification steps

Confirm each phase landed before relying on the column:

sql
-- Column registered with the intended type and SRID
SELECT type, srid FROM geometry_columns
WHERE  f_table_name = 'rail_segments' AND f_geometry_column = 'shape';
-- Expected: LINESTRING | 4326

-- Backfill complete — no NULLs remain
SELECT count(*) FROM rail_segments WHERE shape IS NULL;   -- Expected: 0

-- Both constraints validated
SELECT conname, convalidated FROM pg_constraint
WHERE  conrelid = 'rail_segments'::regclass AND conname LIKE 'chk_rail_shape%';

-- Index built and valid (an interrupted build leaves indisvalid = false)
SELECT c.relname, i.indisvalid
FROM   pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE  c.relname = 'idx_rail_segments_shape';

-- Planner uses it after a fresh ANALYZE
ANALYZE rail_segments;
EXPLAIN (ANALYZE, BUFFERS)
SELECT segment_id FROM rail_segments
WHERE  shape && ST_MakeEnvelope(-122.42, 37.74, -122.38, 37.80, 4326);
-- Expected: Index Scan using idx_rail_segments_shape

While the migration runs, keep an eye on lock waits it may be creating so you can abort before it affects traffic:

sql
SELECT pid, wait_event_type, state, left(query, 50) AS query
FROM   pg_stat_activity
WHERE  query ILIKE '%rail_segments%' AND pid <> pg_backend_pid();

Gotchas checklist

  • An expression default triggers a full rewrite. ADD COLUMN shape geometry(...) DEFAULT ST_MakeLine(...) rewrites every row under ACCESS EXCLUSIVE. Add the column nullable with no default and backfill separately — always.
  • Reversed coordinate order. ST_MakePoint takes X then Y, i.e. longitude then latitude. Passing (lat, lon) produces valid-but-wrong geometry that no constraint catches. Double-check the argument order against a known stop before running the full backfill.
  • Forgetting autocommit for the concurrent build. CREATE INDEX CONCURRENTLY errors out inside a transaction block. The psycopg connection must be opened with autocommit=True, and an Alembic revision must switch the bind to AUTOCOMMIT isolation.
  • Skipping the final ANALYZE. A freshly built index is invisible to the planner until statistics are refreshed; a query that still shows Seq Scan right after the build usually just needs ANALYZE rail_segments.
  • Leaving a failed concurrent build in place. An interrupted CREATE INDEX CONCURRENTLY leaves an INVALID index that consumes space and is never used. Check indisvalid, and if false, DROP INDEX CONCURRENTLY before rebuilding.