Adding Geometry Columns to Live Tables

Introducing a geometry column is the most common step in spatial schema migrations and evolution, and on a table that is quietly serving thousands of inserts a minute it is also where a careless ALTER TABLE does the most damage. A transit platform whose stops table has stored latitude and longitude as plain doubles since launch eventually needs a real geom column so it can run ST_DWithin proximity queries and join against rail_segments; a segments table that has only ever held endpoint coordinates needs a shape LineString before any routing query can touch it. This guide walks the full sequence for getting a typed, SRID-tagged, indexed, constraint-guarded geometry column onto a live table without ever holding a lock long enough to matter.

The mistake that causes outages is treating the whole thing as one statement. Adding the column, populating it, constraining it, and indexing it each have a different lock profile, and the safe path deliberately separates them so that no single step scans the entire heap while holding a lock that blocks writers.

Column-addition flow on a live table

The diagram traces the four distinct operations and the lock each one takes, from the catalog-only column addition through to the concurrent index build. Reading it top to bottom is the exact order to run the migration.

Prerequisites and infrastructure validation

Confirm the PostGIS version, inspect the target table, and record what already exists before touching anything. A geometry column requires the postgis extension; the concurrent index build later requires that you are not inside a transaction.

Confirm PostGIS is present and recent enough:

-- Any 3.x is fine; geometry_columns has been a live view since 2.0.
SELECT PostGIS_Full_Version();

Inspect the table you are about to alter — its row count drives your batch size and whether a concurrent index build is worth the extra time:

-- Approximate row count (fast; reads pg_class, not the heap)
SELECT reltuples::bigint AS approx_rows
FROM   pg_class
WHERE  relname = 'stops';

-- What geometry columns already exist on this table, if any?
SELECT f_geometry_column, type, srid
FROM   geometry_columns
WHERE  f_table_name = 'stops';

Python packages — pin the driver and migration tooling so behaviour is reproducible across CI and production:

pip install "psycopg[binary]>=3.1" "sqlalchemy>=2.0,<3" "geoalchemy2>=0.14" alembic

Confirm the GiST operator class is installed with a throwaway table, so a mid-migration surprise does not abort the index build:

CREATE TEMP TABLE _gist_probe (g geometry(Point, 4326));
CREATE INDEX ON _gist_probe USING gist (g);   -- no error => operator class present
DROP TABLE _gist_probe;

Step 1 — Add the typed column without a rewrite

Add the column as nullable with no default. Typing it geometry(Point, 4326) writes the geometry type and SRID into the column’s type modifier, which is exactly what the geometry_columns view reads and what lets PostGIS reject a LineString or a mismatched SRID at write time. Because there is no default, PostgreSQL edits only the catalog and returns almost immediately even on a table with tens of millions of rows.

-- Metadata-only change. lock_timeout makes the brief lock fail fast if blocked.
SET lock_timeout = '3s';
ALTER TABLE stops
    ADD COLUMN geom geometry(Point, 4326);

Wrapping this in Alembic keeps it versioned alongside the rest of the schema history:

from alembic import op

def upgrade() -> None:
    op.execute("SET lock_timeout = '3s'")
    op.execute("ALTER TABLE stops ADD COLUMN geom geometry(Point, 4326)")

def downgrade() -> None:
    op.execute("ALTER TABLE stops DROP COLUMN geom")

The reason a bare ADD COLUMN geom geometry is wrong is subtle: it succeeds, but the column accepts any geometry type and any SRID, and geometry_columns reports its type as GEOMETRY and its SRID as 0. Every downstream ST_DWithin or spatial join that assumes SRID 4326 then breaks or silently skips the index. Always carry the type modifier. The narrow, high-traffic variant of this step — where even the brief lock is a concern and the exact lock-avoidance sequencing matters most — is covered in ALTER TABLE ADD COLUMN geometry without locking.

Step 2 — Confirm registration in geometry_columns

Modern PostGIS makes geometry_columns a view over the catalog, so registration is automatic. Verify it rather than assume it — a 0 SRID here is the earliest, cheapest place to catch a mistyped column:

SELECT f_table_name, f_geometry_column, coord_dimension, srid, type
FROM   geometry_columns
WHERE  f_table_name = 'stops' AND f_geometry_column = 'geom';
-- Expected: stops | geom | 2 | 4326 | POINT

If this returns 4326 and POINT, the type modifier took effect and you never need the legacy AddGeometryColumn() function. That function still exists for backward compatibility but layers on a redundant enforce_srid_geom CHECK constraint that duplicates what the type modifier already guarantees.

Step 3 — Backfill the geometry in bounded batches

The column exists but every row holds NULL. Populate it from the existing latitude/longitude columns in batches, each its own transaction, so no single statement locks a large row set or bloats the table with one giant burst of dead tuples.

-- One batch; repeat until it affects zero rows.
WITH batch AS (
    SELECT stop_id
    FROM   stops
    WHERE  geom IS NULL
    ORDER  BY stop_id
    LIMIT  5000
    FOR UPDATE SKIP LOCKED
)
UPDATE stops s
SET    geom = ST_SetSRID(ST_MakePoint(s.longitude, s.latitude), 4326)
FROM   batch
WHERE  s.stop_id = batch.stop_id;

Note the argument order in ST_MakePoint(longitude, latitude): PostGIS takes X (longitude) first. Reversing them is the single most common backfill bug — it silently produces points off the coast of West Africa where the equator meets the prime meridian, and nothing errors because both values are valid coordinates.

The Python driver loops the batch, commits each one, and pauses to protect replica lag:

import time
import psycopg

BATCH = """
WITH batch AS (
    SELECT stop_id FROM stops WHERE geom IS NULL
    ORDER BY stop_id LIMIT %(n)s FOR UPDATE SKIP LOCKED
)
UPDATE stops s
SET geom = ST_SetSRID(ST_MakePoint(s.longitude, s.latitude), 4326)
FROM batch WHERE s.stop_id = batch.stop_id
"""

def backfill_stops(conn_str: str, n: int = 5000) -> int:
    total = 0
    with psycopg.connect(conn_str) as conn:
        while True:
            with conn.cursor() as cur:
                cur.execute(BATCH, {"n": n})
                rows = cur.rowcount
            conn.commit()
            total += rows
            if rows == 0:
                return total
            time.sleep(0.1)

For the full zero-downtime treatment — keeping newly inserted rows populated with a trigger while this historical backfill runs so the two never race — see backfilling and zero-downtime migrations.

Step 4 — Attach SRID and validity constraints

The type modifier already enforces POINT and SRID 4326 for new writes, but an explicit SRID CHECK documents intent and guards against a future ALTER that loosens the type. A validity constraint additionally rejects degenerate geometry. Add both NOT VALID first, then validate, so the row-scanning half runs under a lock that lets writers continue:

-- Step 4a: brief lock, no scan. Applies to new writes immediately.
ALTER TABLE stops
    ADD CONSTRAINT chk_stops_geom_srid
    CHECK (ST_SRID(geom) = 4326) NOT VALID;

ALTER TABLE stops
    ADD CONSTRAINT chk_stops_geom_valid
    CHECK (geom IS NULL OR ST_IsValid(geom)) NOT VALID;

-- Step 4b: scans existing rows under SHARE UPDATE EXCLUSIVE (writers proceed).
ALTER TABLE stops VALIDATE CONSTRAINT chk_stops_geom_srid;
ALTER TABLE stops VALIDATE CONSTRAINT chk_stops_geom_valid;

For point data ST_IsValid is almost always true, but on a rail_segments.shape LineString it catches zero-length segments and repeated vertices that would otherwise poison length and routing calculations. If validation fails, find and repair the offenders before retrying:

-- Locate invalid geometries and see why they fail
SELECT stop_id, ST_IsValidReason(geom)
FROM   stops
WHERE  NOT ST_IsValid(geom)
LIMIT  20;

Step 5 — Build the GiST index concurrently

With the column populated and constrained, build the spatial index. A plain CREATE INDEX would lock stops against writes for the whole build; the concurrent form does two lighter heap passes under a SHARE UPDATE EXCLUSIVE lock that permits concurrent inserts and updates. It must run outside a transaction block:

-- Run this outside any explicit transaction (not inside BEGIN/COMMIT).
CREATE INDEX CONCURRENTLY idx_stops_geom
    ON stops USING gist (geom);

From Alembic, this needs a connection in autocommit mode because the default per-revision transaction would reject it:

from alembic import op

def upgrade() -> None:
    # Escape Alembic's transaction: switch the raw connection to AUTOCOMMIT.
    conn = op.get_bind()
    conn.execution_options(isolation_level="AUTOCOMMIT")
    conn.exec_driver_sql(
        "CREATE INDEX CONCURRENTLY idx_stops_geom ON stops USING gist (geom)"
    )

The concurrent build’s failure modes — an interrupted build that leaves an INVALID index needing manual cleanup, and how to monitor build progress on a huge table — are the focus of concurrent index builds. The wider question of which index type and which columns to combine belongs to advanced GiST indexing and optimization.

Performance considerations

Confirming the planner uses the new index

After the build, refresh statistics and confirm the planner chooses an index scan for a bounding-box query. Without a fresh ANALYZE, the planner works from stale row estimates and may ignore a perfectly good index:

ANALYZE stops;

EXPLAIN (ANALYZE, BUFFERS)
SELECT stop_id
FROM   stops
WHERE  geom && ST_MakeEnvelope(-122.42, 37.74, -122.38, 37.80, 4326);

A healthy plan reads:

Index Scan using idx_stops_geom on stops
  (cost=0.29..42.10 rows=48 width=8)
  (actual time=0.05..0.21 rows=51 loops=1)
  Index Cond: (geom && '...'::geometry)
Buffers: shared hit=7

Seeing Seq Scan instead points to one of three things: statistics are stale (the ANALYZE above fixes it), the index is INVALID from an interrupted concurrent build (check pg_index.indisvalid), or the table is still small enough that a scan is genuinely cheaper.

GUCs that shape the migration’s cost

Raise maintenance_work_mem only for the session running the index build; a large global value multiplied across many autovacuum workers can exhaust memory:

SET maintenance_work_mem = '1GB';

Common failure modes and fixes

The column reports SRID 0

Symptom: geometry_columns shows srid = 0 and spatial joins skip the index.

Cause: the column was added as bare geometry with no type modifier.

Fix: re-type the column so the modifier is applied. On a populated column this rewrites it, so schedule it or, better, drop and re-add before backfilling:

ALTER TABLE stops
    ALTER COLUMN geom TYPE geometry(Point, 4326)
    USING ST_SetSRID(geom, 4326);

VALIDATE CONSTRAINT fails on existing rows

Symptom: ERROR: check constraint "chk_stops_geom_valid" is violated by some row.

Diagnosis: locate the offending geometries with ST_IsValidReason, as shown in Step 4. Self-intersections and duplicated vertices are the usual causes on line and polygon data.

Fix: repair in place with ST_MakeValid, then retry the validation:

UPDATE stops
SET    geom = ST_MakeValid(geom)
WHERE  NOT ST_IsValid(geom);

CREATE INDEX CONCURRENTLY leaves an invalid index

Symptom: the build was cancelled or the session dropped, and now an index exists but is never used.

Diagnosis:

SELECT c.relname, i.indisvalid
FROM   pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE  c.relname = 'idx_stops_geom';
-- indisvalid = false means the build did not complete

Fix: drop the failed index (concurrently, to avoid a lock) and rebuild:

DROP INDEX CONCURRENTLY idx_stops_geom;
CREATE INDEX CONCURRENTLY idx_stops_geom ON stops USING gist (geom);

The backfill inflates the table and slows everything

Symptom: table and index size balloon during a large backfill; scans get slower as it runs.

Cause: each updated row leaves a dead tuple and autovacuum is not keeping up.

Fix: tighten autovacuum on the table for the duration and vacuum once at the end:

ALTER TABLE stops SET (autovacuum_vacuum_scale_factor = 0.02);
-- After the backfill completes:
VACUUM (ANALYZE) stops;

Verification

Run this end-to-end check after all steps complete to confirm the column is typed, registered, constrained, indexed, and index-using:

-- 1. Column registered with correct type and SRID
SELECT f_geometry_column, type, srid
FROM   geometry_columns
WHERE  f_table_name = 'stops';
-- Expected: geom | POINT | 4326

-- 2. No rows left unpopulated
SELECT count(*) AS unpopulated FROM stops WHERE geom IS NULL;
-- Expected: 0

-- 3. Constraints present and validated
SELECT conname, convalidated
FROM   pg_constraint
WHERE  conrelid = 'stops'::regclass AND conname LIKE 'chk_stops_geom%';
-- Expected: both rows convalidated = true

-- 4. Index built and valid
SELECT c.relname, i.indisvalid
FROM   pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE  c.relname = 'idx_stops_geom';
-- Expected: idx_stops_geom | true

-- 5. Planner uses it
EXPLAIN (ANALYZE, BUFFERS)
SELECT stop_id FROM stops
WHERE  geom && ST_MakeEnvelope(-122.42, 37.74, -122.38, 37.80, 4326);
-- Expected: Index Scan using idx_stops_geom

All five passing means the column is production-ready and the migration held no long lock at any point.

Frequently Asked Questions

Do I still need AddGeometryColumn() in modern PostGIS?

No. Since PostGIS 2.0 the geometry_columns view is a live catalog view, so a plain ALTER TABLE stops ADD COLUMN geom geometry(Point, 4326) registers automatically with the correct type and SRID. The legacy AddGeometryColumn() function still works but layers on a redundant SRID CHECK constraint and is retained mainly for backward compatibility with pre-2.0 scripts.

Why does my new geometry column show up as type GEOMETRY with SRID 0?

The column was declared as bare geometry without a type modifier. Declare it as geometry(Point, 4326) so the type and SRID are stored in the column’s type modifier. A bare geometry column accepts any geometry type and any SRID, and geometry_columns therefore reports its type as GEOMETRY and its SRID as 0, which disables SRID-dependent index use.

Can I add the ST_IsValid check constraint in a single statement?

You can, but on a large table it holds a lock while it scans every existing row to verify them. Add the constraint with NOT VALID first so it applies to new writes under a brief lock, then run VALIDATE CONSTRAINT separately; the validation scan runs under a weaker SHARE UPDATE EXCLUSIVE lock that allows concurrent inserts and updates to proceed.

Should I create the GiST index before or after backfilling?

After. Building the index while the column is empty and then having the backfill maintain it doubles the write cost of every batch and produces a more bloated index. Populate the column fully first, then build the index once with CREATE INDEX CONCURRENTLY so it is constructed from the final data in a single clean pass.

Related Topics

• Spatial Schema Migrations & Evolution — parent guide to evolving live PostGIS schemas across all four migration surfaces
• ALTER TABLE ADD COLUMN Geometry Without Locking — the narrow high-traffic case where even the brief lock must be minimized
• Concurrent Index Builds — CREATE INDEX CONCURRENTLY mechanics, invalid-index recovery, and progress monitoring
• In-Place SRID Reprojection — converting an existing column from 4326 to 3857 without downtime
• Advanced GiST Indexing and Optimization — index type selection, composite and partial spatial indexes, and plan analysis