Spatial Schema Migrations & Evolution

Q: Can Alembic run CREATE INDEX CONCURRENTLY inside a migration?

Only outside a transaction block. CREATE INDEX CONCURRENTLY cannot run inside the implicit transaction Alembic wraps around each revision, so you must call op.execute with autocommit by setting the connection isolation level to AUTOCOMMIT, or run the statement in a dedicated non-transactional revision.

Q: What lock_timeout should a spatial migration use?

Set a short lock_timeout such as 3 to 5 seconds on any DDL that needs a brief ACCESS EXCLUSIVE lock, so a blocked ALTER fails fast instead of queueing behind long transactions and stalling every writer behind it. Retry the statement with backoff rather than letting it wait indefinitely.

A PostGIS schema is never finished. A transit-analytics platform that launched with a single stops table accreting latitude and longitude floats will eventually need a real geometry column; a routing service that stored everything in WGS84 will need projected coordinates for metre-accurate buffering; a rail_segments table that grew past a hundred million rows will need a fresh GiST index built without taking writes offline. Every one of these changes touches a table that is serving live traffic, and the naive ALTER TABLE that works on an empty development database can lock a production table for minutes and cascade into a full outage. This guide is for backend developers, GIS database administrators, and platform teams who need to evolve spatial schemas on running systems where an ACCESS EXCLUSIVE lock held for thirty seconds is an incident.

The core discipline is the same one that governs all safe PostgreSQL migrations, sharpened by the peculiarities of geometry: columns carry an SRID that the planner and every spatial function depend on, GiST indexes are expensive to build and easy to bloat, and CHECK constraints frequently call validity predicates like ST_IsValid that scan every row. Get the ordering wrong and you rewrite a heap you did not need to rewrite, or you validate a constraint under a lock that blocks the very inserts your application depends on.

Migration flow: phased rollout on a live table

Safe spatial evolution decomposes into short, independently reversible phases. Each phase either takes no long lock at all or takes a brief one that you bound with lock_timeout. The diagram below traces the canonical path a geometry change follows from an offline metadata edit through batched backfill to the final constraint validation.

Each rectangle in that flow is a separate Alembic revision or a separately committed SQL statement. Keeping them separate is what makes the migration resumable: if the backfill in Phase 2 is interrupted after processing four million of ten million rail_segments, you rerun it and it picks up exactly the rows still holding NULL. The rest of this page walks each phase in depth and links to the focused guides that cover the hardest ones end to end.

Concept overview: the four evolution surfaces

Every spatial migration you will run reduces to one of four surfaces, and each has its own page with runnable recipes. The first is adding geometry columns to live tables, where the goal is to introduce a typed, SRID-tagged geometry column and its supporting index onto a table that never stops accepting inserts. The second is in-place SRID reprojection, where an existing column stored in one coordinate reference system must be converted to another — the classic move from lon/lat WGS84 (SRID 4326) to Web Mercator (EPSG:3857) so that planar distance math is valid. The third is concurrent index builds, which is the mechanics of getting a GiST index onto a large table without the ACCESS EXCLUSIVE lock that a plain CREATE INDEX would hold for the entire build. The fourth is backfilling and zero-downtime migrations, which ties the others together into a repeatable expand-migrate-contract sequence that never blocks the application.

These four surfaces compose. Adding a projected geometry column, reprojecting data into it, indexing it, and cutting over is simultaneously an add, a reproject, an index build, and a backfill. The reason to treat them as distinct is that each has a different failure mode and a different lock profile, and thinking about them separately keeps you from accidentally combining a metadata-only change with a full-table rewrite in the same statement.

Underlying all four is a shared model of PostgreSQL locking. DDL that only touches the system catalogs — adding a nullable column with no default, renaming a column, adding a constraint marked NOT VALID — needs only a brief ACCESS EXCLUSIVE lock to swap catalog rows and returns almost instantly. DDL that touches every heap tuple — adding a column with a volatile default, changing a column’s type in a way that rewrites storage, validating a constraint — holds its lock for the duration of a full scan or rewrite. The entire craft of live migration is keeping every operation in the first category, or converting an operation from the second category into a batched sequence of small operations that each stay short.

Pattern catalogue

Adding a geometry column without a rewrite

The foundational pattern is adding a nullable, typed geometry column. Typing it with geometry(LineString, 4326) records the geometry type and SRID directly in the column’s atttypmod, which is what registers it in the geometry_columns view and lets PostGIS enforce dimensionality. Because the column is nullable with no default, PostgreSQL only edits the catalog:

sql

-- Metadata-only: completes in milliseconds even on a 100M-row table.
-- geometry(LineString, 4326) sets the type modifier that geometry_columns reads.
ALTER TABLE rail_segments
    ADD COLUMN shape geometry(LineString, 4326);

The Python side wraps this in an Alembic revision that sets a short lock_timeout so the brief lock cannot queue behind a long-running report:

python

from alembic import op

def upgrade() -> None:
    # Fail fast if we cannot get the lock in 3s rather than blocking writers.
    op.execute("SET lock_timeout = '3s'")
    op.execute(
        "ALTER TABLE rail_segments "
        "ADD COLUMN shape geometry(LineString, 4326)"
    )

The full treatment — including registering the column in geometry_columns, attaching validity constraints, and building the index without a long lock — is covered in adding geometry columns to live tables.

Backfilling geometry in bounded batches

Once the column exists, populate it. A single UPDATE rail_segments SET shape = ... would lock every row it touches for the length of one enormous transaction, bloat the table with dead tuples, and block autovacuum from reclaiming them. The correct shape is a loop of bounded updates, each its own transaction, each targeting only rows still holding NULL:

sql

-- One batch. Repeat until zero rows are affected.
-- The self-join on a keyset window keeps each UPDATE short and index-driven.
WITH batch AS (
    SELECT segment_id
    FROM   rail_segments
    WHERE  shape IS NULL
    ORDER  BY segment_id
    LIMIT  5000
    FOR UPDATE SKIP LOCKED
)
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;

Driving that loop from Python keeps each transaction small and lets you throttle to protect replication lag:

python

import time
import psycopg

BATCH_SQL = """
WITH batch AS (
    SELECT segment_id FROM rail_segments
    WHERE shape IS NULL
    ORDER BY segment_id
    LIMIT %(n)s
    FOR UPDATE SKIP LOCKED
)
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 = 5000, pause: float = 0.2) -> int:
    total = 0
    with psycopg.connect(conn_str) as conn:
        while True:
            with conn.cursor() as cur:
                cur.execute(BATCH_SQL, {"n": batch_size})
                affected = cur.rowcount
            conn.commit()          # commit each batch so locks release promptly
            total += affected
            if affected == 0:
                return total
            time.sleep(pause)      # let autovacuum and replicas catch up

The complete zero-downtime backfill playbook, including how to keep new writes populated with a trigger while the historical backfill runs, lives in backfilling and zero-downtime migrations.

Reprojecting an SRID in place

When a stops table stores its geom column in SRID 4326 but the routing engine needs Web Mercator, the migration adds a second column, transforms into it, and cuts over. Reprojecting is never an in-place type change; it is an expand-and-contract:

sql

-- Add the projected sibling column (metadata-only).
ALTER TABLE stops ADD COLUMN geom_3857 geometry(Point, 3857);

-- Backfill it in batches (shown here as the whole set for brevity).
UPDATE stops
SET    geom_3857 = ST_Transform(geom, 3857)
WHERE  geom_3857 IS NULL;

python

from sqlalchemy import text

def reproject_stops(engine, batch_size: int = 10000) -> None:
    stmt = text(
        "UPDATE stops SET geom_3857 = ST_Transform(geom, 3857) "
        "WHERE stop_id IN ("
        "  SELECT stop_id FROM stops WHERE geom_3857 IS NULL "
        "  ORDER BY stop_id LIMIT :n FOR UPDATE SKIP LOCKED)"
    )
    with engine.connect() as conn:
        while conn.execute(stmt, {"n": batch_size}).rowcount:
            conn.commit()

The precise ordering — index the new column concurrently before the read cutover, keep both columns in sync during the transition, and drop the source column only after deploys settle — is detailed in in-place SRID reprojection.

Building the GiST index without blocking writes

A geometry column is useless to the planner until it has a spatial index. A plain CREATE INDEX ... USING gist locks the table against writes for the entire build; on a large rail_segments table that can be many minutes. The concurrent variant does two lighter heap passes under a SHARE UPDATE EXCLUSIVE lock that permits concurrent INSERT, UPDATE, and DELETE:

sql

-- Never holds a write lock; safe on a live table.
CREATE INDEX CONCURRENTLY idx_rail_segments_shape
    ON rail_segments USING gist (shape);

The trade-offs — the doubled build time, the need to run it outside any transaction, and the manual cleanup required when a concurrent build fails and leaves an INVALID index behind — are the subject of concurrent index builds. These techniques sit alongside the broader indexing strategy in advanced GiST indexing and optimization, which covers partial indexes, composite geometry-plus-scalar indexes, and how to read the resulting query plans.

Validating constraints without a long lock

Geometry columns earn CHECK constraints: an SRID guard and a validity guard. Adding a constraint that PostgreSQL must verify against every existing row holds a lock for the length of that scan. The two-step NOT VALID then VALIDATE pattern splits that into a fast catalog edit followed by a scan that runs under a weaker lock:

sql

-- Step 1: brief lock, no scan. New writes are checked immediately.
ALTER TABLE rail_segments
    ADD CONSTRAINT chk_rail_segments_srid
    CHECK (ST_SRID(shape) = 4326) NOT VALID;

-- Step 2: scans existing rows under SHARE UPDATE EXCLUSIVE (writers proceed).
ALTER TABLE rail_segments
    VALIDATE CONSTRAINT chk_rail_segments_srid;

The same two-step applies to a CHECK (ST_IsValid(shape)) guard that rejects self-intersecting linework at write time.

Indexing and query-planner verification

After a concurrent build completes, confirm the index is valid and that the planner will actually choose it. A concurrent build that was interrupted leaves an index marked invalid, which the planner silently ignores — the classic reason a freshly indexed table still shows sequential scans.

sql

-- Confirm the index built cleanly. indisvalid must be true.
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM   pg_index i
JOIN   pg_class c ON c.oid = i.indexrelid
WHERE  c.relname = 'idx_rail_segments_shape';

-- Refresh planner statistics before trusting any EXPLAIN output.
ANALYZE rail_segments;

-- Confirm a bounding-box query uses the GiST index, not a Seq Scan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT segment_id
FROM   rail_segments
WHERE  shape && ST_MakeEnvelope(-122.42, 37.74, -122.38, 37.80, 4326);

A healthy plan reads Index Scan using idx_rail_segments_shape with an Index Cond on the && operator. If you instead see Seq Scan, the usual culprits are stale statistics (fixed by the ANALYZE above), an invalid index (rebuild it), or an SRID mismatch between the query literal and the column that prevents the operator from being index-supported. For SSD-backed systems, nudging the planner toward index scans helps once the table is large enough to matter:

sql

SET random_page_cost = 1.1;        -- SSD random reads cost near sequential
SET effective_cache_size = '12GB'; -- advertise available OS cache to the planner

During a migration you frequently need to confirm no in-flight statement is still holding the old geometry column open. Watch pg_stat_activity for the migration’s own backend and for any lock waits it is creating:

sql

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

Continuous visibility into which spatial statements are slow — and whether a migration has regressed a hot query path — is the domain of spatial performance monitoring and observability, which pairs naturally with any schema change you ship.

Python data-flow during a migration

Migrations are not only SQL. The application code that reads and writes the evolving table must tolerate both the old and new shape of the schema during the transition window, and the migration driver itself must stream through large tables without exhausting memory.

Keeping the ORM tolerant across the expand phase

During expand-migrate-contract there is a window where both the old and new columns exist. Map both in SQLAlchemy and let the application prefer the new one while falling back to the old, so a rollback never breaks reads:

python

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from geoalchemy2 import Geometry

class Base(DeclarativeBase):
    pass

class Stop(Base):
    __tablename__ = "stops"
    stop_id:   Mapped[int] = mapped_column(primary_key=True)
    # Old column stays mapped until the contract phase drops it.
    geom:      Mapped[Geometry] = mapped_column(Geometry("POINT", srid=4326))
    # New projected column, nullable while the backfill is in flight.
    geom_3857: Mapped[Geometry] = mapped_column(
        Geometry("POINT", srid=3857), nullable=True
    )

This dual-column mapping is the ORM-side counterpart to the SQL expand-and-contract, and it connects directly to the model-mapping practices in SQLAlchemy and GeoAlchemy2 integration workflows, where declaring explicit SRIDs and index intent on every geometry column is the baseline.

Streaming a backfill with a server-side cursor

When the backfill logic must run row-by-row in Python — for example when the geometry is derived from an external geocoder rather than existing columns — use a named server-side cursor so the driver never pulls the whole table into client memory:

python

import psycopg

def stream_backfill(conn_str: str, itersize: int = 2000) -> None:
    with psycopg.connect(conn_str) as read_conn, \
         psycopg.connect(conn_str) as write_conn:
        # Named cursor => server-side; rows arrive in itersize chunks.
        with read_conn.cursor(name="seg_stream") as cur:
            cur.itersize = itersize
            cur.execute(
                "SELECT segment_id, start_lon, start_lat, end_lon, end_lat "
                "FROM rail_segments WHERE shape IS NULL"
            )
            with write_conn.cursor() as wcur:
                for i, row in enumerate(cur, 1):
                    seg_id, slon, slat, elon, elat = row
                    wcur.execute(
                        "UPDATE rail_segments SET shape = "
                        "ST_SetSRID(ST_MakeLine(ST_MakePoint(%s,%s), "
                        "ST_MakePoint(%s,%s)), 4326) WHERE segment_id = %s",
                        (slon, slat, elon, elat, seg_id),
                    )
                    if i % itersize == 0:
                        write_conn.commit()   # bounded transaction size
                write_conn.commit()

Reading and writing on separate connections keeps the read cursor’s snapshot from pinning dead tuples that the writes are producing, which matters when the table is under concurrent load.

Production hardening

Bound every lock and every batch

The single most important production setting for spatial DDL is a short lock_timeout. A geometry migration that needs even a brief ACCESS EXCLUSIVE lock will, if a long analytics query is running, queue behind it — and while it waits, it blocks every new writer behind itself, converting a fast catalog edit into a stall. Set the timeout so the DDL fails fast and can be retried:

python

def add_column_with_retry(engine, ddl: str, attempts: int = 5) -> None:
    from sqlalchemy import text
    for attempt in range(1, attempts + 1):
        try:
            with engine.begin() as conn:
                conn.execute(text("SET lock_timeout = '4s'"))
                conn.execute(text(ddl))
            return
        except Exception:
            if attempt == attempts:
                raise
            import time; time.sleep(2 ** attempt)  # exponential backoff

Control dead-tuple bloat during backfills

A large batched backfill produces one dead tuple per updated row. Left unchecked, that bloat inflates the table and its indexes and slows every subsequent scan. Tighten autovacuum on the migrating table for the duration so it reclaims space aggressively:

sql

ALTER TABLE rail_segments SET (
    autovacuum_vacuum_scale_factor  = 0.02,   -- vacuum after 2% churn
    autovacuum_analyze_scale_factor = 0.01
);

-- After the backfill completes, reclaim and refresh statistics in one pass.
VACUUM (ANALYZE) rail_segments;

Cap runaway statements with statement_timeout

Set a statement_timeout on the migration role so a single pathological batch — one that hits an unexpected sequential scan — cannot run unbounded and pin resources:

sql

ALTER ROLE migrator SET statement_timeout = '60s';

Track migration-induced regressions

After each phase ships, watch pg_stat_statements for spatial calls whose mean execution time jumped, which is the earliest signal that a migration changed a plan for the worse:

sql

SELECT left(query, 70) AS query,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms
FROM   pg_stat_statements
WHERE  query ILIKE '%rail_segments%'
ORDER  BY mean_exec_time DESC
LIMIT  15;

Partition before the table becomes unmigratable

A rail_segments table past roughly fifty million rows becomes painful to migrate as a monolith: every backfill scans the whole heap, every index build takes hours. Range-partitioning by an ingestion date turns each future migration into a per-partition operation you can run and validate one slice at a time, and lets the planner prune partitions before it ever touches a geometry predicate.

Conclusion: live spatial migration checklist

Before running any schema change against a live PostGIS table, confirm each item:

Frequently Asked Questions

Why does adding a geometry column with a default rewrite the whole table?

A volatile or expression default forces PostgreSQL to compute a value for every existing row, which rewrites the heap under an ACCESS EXCLUSIVE lock held for the entire scan. Adding the column as nullable with no default is a catalog-only change that completes in milliseconds regardless of table size, after which you backfill the geometry in bounded batches that each stay short.

Can Alembic run CREATE INDEX CONCURRENTLY inside a migration?

Only outside a transaction block. CREATE INDEX CONCURRENTLY cannot run inside the implicit transaction Alembic wraps around each revision, so you must execute it on a connection whose isolation level is set to AUTOCOMMIT, or place it in a dedicated revision that disables the transaction wrapper. Running it transactionally raises CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

How do I reproject a geometry column from SRID 4326 to 3857 without downtime?

Add a new nullable geometry column typed to EPSG:3857, backfill it in batches with ST_Transform(geom, 3857), build its GiST index concurrently, switch application reads to the new column, and only then drop the original. Rewriting the original column in place would hold a long lock and block every writer for the duration of the transform.

What lock_timeout should a spatial migration use?

Set a short lock_timeout such as 3 to 5 seconds on any DDL that needs even a brief ACCESS EXCLUSIVE lock, so a blocked statement fails fast instead of queueing behind a long transaction and stalling every writer behind it. Wrap the statement in a retry loop with exponential backoff rather than letting it wait indefinitely.

Adding Geometry Columns to Live Tables — typed columns, geometry_columns registration, validity constraints, and non-blocking index creation
In-Place SRID Reprojection — converting 4326 to 3857 with an add-transform-cutover sequence
Concurrent Index Builds — CREATE INDEX CONCURRENTLY mechanics, invalid-index recovery, and build monitoring
Backfilling and Zero-Downtime Migrations — expand-migrate-contract, dual-write triggers, and batched population
SQLAlchemy and GeoAlchemy2 Integration Workflows — declaring SRID-explicit geometry models that stay tolerant across a migration
Advanced GiST Indexing and Optimization — index type selection, partial and composite indexes, and plan analysis
Spatial Performance Monitoring and Observability — detecting plan regressions and slow spatial calls after a migration ships