Geospatial Schema Standardization & Mapping Automation

Zero-Downtime Column Renames in Live Spatial Tables

ALTER TABLE parcels RENAME COLUMN landuse TO land_use_code runs in milliseconds and takes only a brief lock. It is also the single most reliable way to break a production GIS, because the lock is not the problem — the problem is every WMS layer definition, every desktop project file, every reporting query and every application instance still running the previous release that names a column which no longer exists. This procedure renames the column without any of them noticing, implementing the expand-and-contract pattern from spatial database schema migrations inside Geospatial Schema Architecture & Standards Mapping.

The steps map to configure (Step 1, expand), execute (Step 2, backfill), validate (Steps 3–4, cutover and proof), and log (Step 5, contract and verification). It takes three deploys and typically two to four weeks of calendar time. That is the cost of not having an outage, and it is cheaper than the outage.

Prerequisites checklist

Step 1: Expand — add the new column and write to both

sql
-- Deploy 1, migration. PostgreSQL >= 16 — an additive, catalogue-only change.
SET lock_timeout = '2s';
ALTER TABLE parcels ADD COLUMN land_use_code text;   -- nullable: no rewrite, no scan

Adding a nullable column with no default is a catalogue update in modern PostgreSQL: no table rewrite, no long lock, safe at any size. Adding it with a default is also cheap since PostgreSQL 11, but adding it NOT NULL is not — that requires every existing row to satisfy the constraint, which is why the constraint comes later, in Step 2.

The application deployed alongside this migration writes both columns:

python
# app/repository.py — psycopg >=3.1 — Python 3.10+
# Deploy 1 only. Both columns are written; only `landuse` is read.
INSERT_PARCEL = """
    INSERT INTO parcels (stable_id, geom, landuse, land_use_code)
    VALUES (%(stable_id)s, ST_GeomFromWKB(%(wkb)s, 25832), %(code)s, %(code)s)
    ON CONFLICT (stable_id) DO UPDATE
        SET geom = EXCLUDED.geom,
            landuse = EXCLUDED.landuse,
            land_use_code = EXCLUDED.land_use_code
"""

Dual-writing in the application rather than with a trigger is a deliberate choice. A trigger is less code and it is also invisible: six months later nobody remembers it exists, and it fires on bulk loads that were meant to bypass application logic. Where the writers are too numerous to change, a trigger is acceptable — but it must be dropped by the contract migration, and that must be in the same change.

Step 2: Backfill in bounded, restartable batches

python
# migrations/versions/0044_backfill_land_use_code.py — alembic >=1.13
from alembic import op

revision, down_revision = "0044", "0043"
BATCH = 50_000


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

    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

    # Constrain in two steps: NOT VALID takes a brief lock and skips the scan;
    # VALIDATE scans under a share lock that concurrent readers tolerate.
    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")

The loop is driven by the IS NULL predicate rather than by a cursor or an id range, which makes it restartable: if it is killed at 60%, re-running continues from 60% with no bookkeeping. On a table with heavy write traffic, add a short pause between batches so autovacuum keeps up — a backfill that outruns vacuum turns into table bloat, which is its own migration problem later.

Do not use SET NOT NULL directly in place of the check constraint. SET NOT NULL requires a full table scan under an ACCESS EXCLUSIVE lock; the NOT VALID check followed by VALIDATE achieves an equivalent guarantee with locks readers can live with. (In PostgreSQL 12 and later you can then add SET NOT NULL cheaply, because the planner uses the validated constraint as proof.)

Step 3: Cut readers over, and shield the ones you cannot upgrade

sql
-- Deploy 2. A view keeps unupgradable clients working against the old name.
CREATE OR REPLACE VIEW parcels_compat AS
SELECT id,
       stable_id,
       geom,
       land_use_code AS landuse,       -- the old name, served from the new column
       land_use_code
  FROM parcels;

COMMENT ON VIEW parcels_compat IS
    'Compatibility view for desktop clients. Remove after 2026-12-01; see migration 0045.';

Desktop project files and saved WMS layer definitions are the readers nobody has an inventory of, and they cannot be redeployed on your schedule. Pointing those consumers at a view — and putting the removal date in a comment where the next person will find it — converts an unbounded compatibility obligation into a dated one.

Application readers switch in the same deploy:

python
# Deploy 2: read the new column, still write both.
SELECT_PARCEL = "SELECT stable_id, land_use_code, geom FROM parcels WHERE stable_id = %(id)s"

Step 4: Prove nothing reads the old column any more

This is the step that distinguishes a safe contract from a hopeful one.

sql
-- Any statement that mentions the old column, since stats were last reset.
SELECT calls, rows, query
  FROM pg_stat_statements
 WHERE query ILIKE '%landuse%'
   AND query NOT ILIKE '%land_use_code%'
 ORDER BY calls DESC;

-- Columns PostgreSQL has gathered statistics for tell you the column still exists,
-- not that it is read; pg_stat_statements is the evidence that matters.

Reset pg_stat_statements at the start of the observation window and let it run for at least one full business cycle — including month-end reporting, which is where the forgotten query always lives. An empty result after a complete cycle is the evidence the contract migration needs. A non-empty result names the query, and usually the team that owns it.

Three Deploys and the Observation Window Between Them A horizontal timeline marks three deploys. At deploy one the new column is added and both columns are written while the old one is read. At deploy two readers switch to the new column and a compatibility view serves the old name; both columns are still written. Between deploy two and deploy three an observation window runs for at least one full business cycle, during which pg_stat_statements is checked for any query still naming the old column. At deploy three the old column is dropped and dual-writing stops. The bar beneath the timeline shows that at no point does any reader require a column that does not exist. Deploy 1 add nullable column write both · read old Deploy 2 backfill complete · constraint valid write both · read new · compat view Deploy 3 drop old column write new · read new observation window — one full business cycle pg_stat_statements must show no query naming the old column

Step 5: Contract, then verify against the golden schema

python
# migrations/versions/0045_drop_landuse.py — alembic >=1.13
from alembic import op

revision, down_revision = "0045", "0044"


def upgrade() -> None:
    conn = op.get_bind()
    conn.exec_driver_sql("SET lock_timeout = '2s'")
    op.execute("DROP VIEW IF EXISTS parcels_compat")     # the dated obligation, discharged
    op.drop_column("parcels", "landuse")


def downgrade() -> None:
    # Reversible in structure, not in data: the values are recoverable from land_use_code.
    op.add_column("parcels", sa.Column("landuse", sa.Text(), nullable=True))
    op.execute("UPDATE parcels SET landuse = land_use_code")

DROP COLUMN is a catalogue operation — it does not rewrite the table, though the space is reclaimed only by a later vacuum — but it does take an ACCESS EXCLUSIVE lock briefly, so it belongs in a quiet period with the lock timeout set. Run the golden-schema diff immediately afterwards; it is the assertion that the three-deploy sequence landed exactly where the reviewed design said it would.

Verification

sql
-- 1. Every row carries the new value, and the constraint is validated.
SELECT count(*) FILTER (WHERE land_use_code IS NULL) AS nulls, count(*) AS total FROM parcels;
--  nulls | total
-- -------+--------
--      0 | 214338

SELECT convalidated FROM pg_constraint WHERE conname = 'land_use_code_present';
--  t
bash
# 2. The published schema matches the committed one.
pg_dump --schema-only --no-owner --no-privileges "$DATABASE_URL" | diff - schema/cadastre.golden.sql
# (no output)

Troubleshooting

Symptom Likely cause Fix
Backfill never finishes Batch size too large for the statement timeout, so every batch aborts Lower BATCH until a batch completes in well under the timeout
Table bloat grows during the backfill Updates outrunning autovacuum Pause briefly between batches, or raise the table’s autovacuum cost limit for the duration
VALIDATE CONSTRAINT blocks writers It takes a SHARE UPDATE EXCLUSIVE lock — readers are fine, some DDL is not Run it outside other migrations; never inside a window that also rebuilds indexes
A desktop client breaks after Deploy 2 It queried the table directly rather than the compatibility view Point it at parcels_compat and extend the window; do not roll back the cutover
pg_stat_statements shows the old column long after cutover A scheduled report or a materialized view definition Search pg_matviews and job definitions as well as application code
Space is not reclaimed after DROP COLUMN Dropped columns are marked, not removed, until a rewrite Schedule a VACUUM FULL or pg_repack in a window if the space matters