Spatial Database Schema Migrations Jump to heading#

A schema mapping is only as durable as the database it lands in, and databases change: a column is renamed to match a standard, a geometry column’s SRID is corrected, a code list column becomes a foreign key, a table is partitioned because it outgrew its indexes. In an ordinary application database those changes are routine. In a spatial database they carry three complications that generic migration tooling knows nothing about — geometry columns have registered metadata, spatial indexes are expensive to rebuild, and the tables are usually being read by desktop clients and map services that do not tolerate a lock. This stage owns making those changes reversible, reviewable and safe to run against a live system. It operates inside Geospatial Schema Architecture & Standards Mapping, where the target schema itself is defined.

The scope boundary against neighbouring topics is clean. Cross-platform schema translation moves a schema between systems — PostGIS to a file geodatabase, and back. This page moves a schema forward in time within one system. Local government data dictionaries decides what the target schema should be; migrations are how a database that already holds data gets there without losing any.

Declarative Configuration Manifest Jump to heading#

Migrations are code, but the policy around them is configuration: which changes may run online, how long a lock may be held, and what must be true before and after.

# migration_policy.yaml — alembic >=1.13, PostGIS >=3.4
policy_version: "2.0.0"          # MANDATORY
target: "postgresql+psycopg://gis@db/cadastre"   # MANDATORY: resolved from the environment
locks:
  statement_timeout_ms: 5000     # MANDATORY: no migration statement may exceed this
  lock_timeout_ms: 2000          # MANDATORY: fail fast rather than queue behind readers
online_operations:               # MANDATORY: what may run against a live database
  - add_nullable_column
  - add_index_concurrently
  - add_check_constraint_not_valid
  - drop_index_concurrently
offline_operations:              # require a maintenance window, announced in advance
  - alter_column_type
  - set_geometry_srid
  - drop_column
  - rewrite_table
geometry_rules:
  require_srid: true             # MANDATORY: a geometry column without an SRID is rejected
  require_typmod: true           # OPTIONAL: geometry(Polygon,25832) rather than bare geometry
  index_method: gist             # OPTIONAL: gist | spgist
  rebuild_concurrently: true     # OPTIONAL: default true; false only in a window
preconditions:
  max_table_bloat_ratio: 2.0     # OPTIONAL: refuse to rewrite a heavily bloated table
  require_recent_backup_hours: 12  # MANDATORY for offline_operations
verification:
  golden_schema: "schema/cadastre.golden.sql"   # MANDATORY: the diff target after migration

The split between online and offline operations is the part worth arguing over with the team that runs the map services. ALTER TABLE ... ALTER COLUMN TYPE on a geometry column rewrites the whole table and holds an exclusive lock for the duration — on a two-million-row parcel table that is minutes, and every WMS request during that window fails. Listing it as offline is not pessimism; it is the difference between a planned five-minute outage and an unexplained one.

Preprocessing Requirements Jump to heading#

A golden schema exists and is committed. Every migration is verified by diffing the resulting database against a committed schema dump. Without that artefact there is no definition of “correct”, and drift accumulates silently as hotfixes are applied directly to production.

Geometry metadata is inspected, not assumed. In PostGIS 3, geometry_columns is a view over the catalogue rather than a table you maintain, but the typmod on the column is what actually constrains it. A migration that adds a geometry column without a type and SRID modifier creates a column that accepts a point, a polygon in a different projection, and a null with equal enthusiasm.

Index inventory is captured before the change. Spatial indexes are rarely recreated identically by ORM-generated migrations, and a missing GiST index turns a 40 ms query into a sequential scan nobody notices until the map is slow. Capture pg_indexes for the affected tables before and compare after.

Row counts and a checksum are recorded. For any operation that rewrites data, the row count and a cheap aggregate — sum(ST_NPoints(geom)) is a good one — recorded before and after prove that the rewrite moved the data rather than a subset of it.

Execution Engine & Precision Guards Jump to heading#

# migrations/versions/0042_rename_landuse_column.py — alembic >=1.13, PostGIS >=3.4
from alembic import op
import sqlalchemy as sa

revision = "0042"
down_revision = "0041"

BATCH = 50_000


def upgrade() -> None:
    conn = op.get_bind()
    conn.exec_driver_sql("SET lock_timeout = '2s'")
    conn.exec_driver_sql("SET statement_timeout = '5s'")

    # 1. EXPAND — additive only, no rewrite, no lock beyond a catalogue update.
    op.add_column("parcels", sa.Column("land_use_code", sa.Text(), nullable=True))

    # 2. BACKFILL — bounded batches so no single statement exceeds the timeout.
    #    The loop is restartable: it is driven by the null predicate, not by a cursor.
    while True:
        result = conn.exec_driver_sql(f"""
            WITH batch AS (
                SELECT ctid FROM parcels
                WHERE land_use_code IS NULL AND landuse IS NOT NULL
                LIMIT {BATCH}
            )
            UPDATE parcels p SET land_use_code = p.landuse
            FROM batch WHERE p.ctid = batch.ctid
        """)
        if result.rowcount == 0:
            break

    # 3. CONSTRAIN — NOT VALID first: it takes a brief lock and does not scan the table.
    op.execute("ALTER TABLE parcels ADD CONSTRAINT land_use_code_present "
               "CHECK (land_use_code IS NOT NULL) NOT VALID")
    op.execute("ALTER TABLE parcels VALIDATE CONSTRAINT land_use_code_present")


def downgrade() -> None:
    op.execute("ALTER TABLE parcels DROP CONSTRAINT IF EXISTS land_use_code_present")
    op.drop_column("parcels", "land_use_code")

Three guards in that migration are specific to running against a live spatial database. lock_timeout before anything else means a migration that cannot acquire its lock fails in two seconds instead of queueing — and, critically, instead of making every subsequent reader queue behind it. Bounded batches driven by a predicate make the backfill restartable: if it dies halfway, re-running continues from where it stopped without tracking state. NOT VALID then VALIDATE splits a full-table scan under an exclusive lock into a brief metadata change followed by a scan under a share lock that readers tolerate.

For geometry columns specifically:

-- PostGIS >= 3.4 — changing an SRID that was recorded wrongly.
-- This is an OFFLINE operation: it rewrites every row.
ALTER TABLE parcels
    ALTER COLUMN geom TYPE geometry(Polygon, 25832)
    USING ST_SetSRID(geom, 25832);          -- the data was always in 25832; only the tag was wrong

-- If the data is genuinely in another CRS, this is a reprojection, not a migration:
-- ALTER ... USING ST_Transform(geom, 25832);
-- Reprojection belongs to the CRS pipeline, which records accuracy and lineage.

The comment in that snippet is the point. ST_SetSRID relabels; ST_Transform moves coordinates. Doing the second one inside a schema migration hides a positional change in a DDL commit, where no lineage record is written and no accuracy statement is updated — the CRS normalization pipeline exists precisely so that coordinate movement is recorded, and a migration must not go around it.

Failure Modes & Fallback Routing Jump to heading#

MIG_GOLDEN_DIFF is the one that finds problems nobody reported. Hand edits to production schemas are common and almost always well-intentioned — someone adds an index during an incident — and the diff turns them into a visible, reviewable event rather than a permanent divergence between environments.

Indexes, Partitions and the Operations That Rewrite a Table Jump to heading#

Three operations account for almost every migration that turns into an incident, and all three are invisible in a diff that reads as a single line of DDL.

Rebuilding a spatial index. A GiST index on a two-million-row parcel table takes minutes to build and, built without CONCURRENTLY, holds a lock that blocks every write for the duration while readers queue behind the writers. The concurrent build is roughly twice as slow and takes no blocking lock, which makes it the only defensible choice on a live system. It has one sharp edge: a concurrent build that fails leaves an invalid index behind that the planner ignores while pg_indexes still lists it, so the table silently loses its index and nobody is told. Check for invalid indexes as part of the post-migration verification, not only when a query goes slow:

-- PostgreSQL >= 16 — invalid indexes left by a failed CONCURRENTLY build
SELECT c.relname AS index_name, t.relname AS table_name
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
  JOIN pg_class t ON t.oid = i.indrelid
 WHERE NOT i.indisvalid;

Changing a column type. ALTER TABLE ... ALTER COLUMN ... TYPE rewrites every row and every index on the table, under an ACCESS EXCLUSIVE lock, and there is no concurrent variant. For a geometry column that also means rebuilding the spatial index, which doubles the window. The expand-and-contract alternative — add a correctly typed column, backfill, switch readers, drop the old one — turns one long outage into three short, individually reversible deploys, and it is the reason the policy classifies type changes as offline in the first place.

Introducing partitioning. Converting a large table to a partitioned one is a full rewrite plus a period during which the old and new tables both exist and must be kept in step. It is worth doing for tables that have grown past the point where a vacuum completes in a maintenance window, and it is worth planning as its own project rather than as a step inside another migration. The one detail that catches spatial teams is that a partitioned parent carries no indexes of its own in the way a plain table does: every partition needs its GiST index, and a partition created later by an automation script that nobody reviewed will not have one until someone notices the map is slow.

The common thread is that the duration of a lock, not its existence, is what causes an outage. A migration that takes an exclusive lock for 40 ms is invisible; the same lock held for eleven minutes takes the map services down. That is why lock_timeout is set before every migration and why the operation list in the policy is split by whether an operation’s cost scales with the size of the table.

Verify what the migration actually did to the physical layout, not only to the catalogue:

-- Index inventory for the affected tables, captured before and diffed after.
SELECT tablename, indexname, indexdef
  FROM pg_indexes
 WHERE schemaname = 'public' AND tablename IN ('parcels', 'buildings')
 ORDER BY tablename, indexname;

-- Cheap, sensitive checksum over the geometry column: a rewrite that dropped
-- or altered rows changes this even when the row count is unchanged.
SELECT count(*) AS rows, sum(ST_NPoints(geom)) AS vertices FROM parcels;

Capturing both before the migration and comparing after is thirty seconds of work that distinguishes “the schema changed” from “the data changed” — a distinction nobody can reconstruct afterwards, and the one an auditor will eventually ask about.

Compliance Reporting Output Jump to heading#

# migrations/audit.py — pyarrow >=14 — Python 3.10+
import pyarrow as pa

MIGRATION_AUDIT_SCHEMA = pa.schema([
    ("revision",        pa.string()),      # alembic revision id
    ("applied_at",      pa.timestamp("us", tz="UTC")),
    ("applied_by",      pa.string()),      # service account, not a person's laptop
    ("policy_version",  pa.string()),
    ("operations",      pa.string()),      # comma-separated operation names from the policy
    ("online",          pa.bool_()),
    ("duration_ms",     pa.int64()),
    ("rows_before",     pa.int64()),
    ("rows_after",      pa.int64()),
    ("geom_checksum_before", pa.int64()),  # sum(ST_NPoints(geom)) — cheap and sensitive
    ("geom_checksum_after",  pa.int64()),
    ("golden_diff",     pa.string()),      # "" when the schema matches exactly
])

Schema changes are lineage events, and this table is their record. Storing the geometry checksum on both sides is what lets an auditor distinguish “the schema changed” from “the data moved” months later — a distinction that matters enormously when a boundary dispute turns on when a coordinate last changed. These rows belong in the same store as the lineage manifest and feed the conformance scorecard as a schema-stability measure.

CI Integration Jump to heading#

# tests/test_migrations.py — pytest >=7, alembic >=1.13, testing.postgresql or a container
def test_every_migration_is_reversible(alembic_config, empty_db):
    command.upgrade(alembic_config, "head")
    command.downgrade(alembic_config, "base")
    command.upgrade(alembic_config, "head")          # round trip must succeed


def test_head_matches_the_golden_schema(alembic_config, empty_db):
    command.upgrade(alembic_config, "head")
    assert dump_schema(empty_db) == read_text("schema/cadastre.golden.sql")


def test_no_geometry_column_lacks_an_srid(migrated_db):
    rows = migrated_db.execute(
        "SELECT f_table_name, f_geometry_column FROM geometry_columns WHERE srid = 0"
    ).fetchall()
    assert rows == []


def test_policy_rejects_an_offline_operation_marked_online():
    # Negative control: the policy gate must refuse a mislabelled migration.
    with pytest.raises(PolicyViolation):
        check_policy(revision="9999", operations=["alter_column_type"], online=True)

Run these against a disposable database in the same workflow as the schema drift gate. The reversibility test is the one developers try to skip and the one that pays for itself the first time a migration has to be backed out at 18:00 on a Friday.

Deeper Implementation Walkthroughs Jump to heading#

Versioning PostGIS DDL with Alembic migrations sets up the migration environment so that geometry columns, spatial indexes and PostGIS extensions are handled correctly by autogeneration rather than silently dropped. Zero-downtime column renames in live spatial tables works the expand-and-contract sequence through in full, including how to prove no reader still references the old column before dropping it.

Frequently Asked Questions Jump to heading#

Can Alembic autogenerate handle PostGIS columns? Only with help. Out of the box, autogeneration frequently proposes dropping geometry columns it does not recognize and misses spatial indexes entirely, because both live outside the plain SQLAlchemy type system. The fix is to register the geometry types and to exclude PostGIS-managed objects from comparison — covered step by step in the Alembic guide above. Review every autogenerated migration by hand regardless; a proposed DROP COLUMN geom is easy to miss in a large diff.

Should migrations run automatically on deploy? Online ones, yes — additive changes with bounded batches and short lock timeouts are safer applied automatically than applied by a person under pressure. Offline ones, no: they need a window, a fresh backup and someone watching. The manifest’s split exists to make that decision mechanical rather than a judgement call made at deploy time.

How do we migrate a schema that a desktop client connects to directly? Treat the desktop as an unversioned reader that cannot be upgraded in step with the database, which makes expand-and-contract mandatory rather than merely advisable. Keep the old column present for at least one full delivery cycle, and use database views to present a stable shape to clients while the physical schema moves underneath.

What belongs in the golden schema — everything? Structure, constraints, indexes and geometry typmods; not data, and not anything the platform manages for itself such as extension internals. The test is whether a diff line would ever be worth a reviewer’s attention. A golden schema that produces noisy diffs on every deploy gets ignored, which is worse than not having one.

Related Jump to heading#

• Geospatial Schema Architecture & Standards Mapping — the parent section, where the target schema is defined
• Versioning PostGIS DDL with Alembic Migrations — environment setup so geometry columns survive autogeneration
• Zero-Downtime Column Renames in Live Spatial Tables — the expand-and-contract sequence in full
• Best Practices for Spatial Data Dictionary Versioning — versioning the definition the migration implements
• Gating Pull Requests on Geospatial Schema Drift — catching an unplanned schema change before it merges