Backfilling & Zero-Downtime Spatial Migrations

Backfilling a real geometry column onto a live table is the workhorse technique inside Spatial Schema Migrations & Evolution: it lets you retire fragile latitude / longitude float pairs in favour of a proper geometry(Point, 4326) column without ever taking the service offline. The pain is concrete. An IoT environmental sensor network writes into a sensor_pings table thousands of times a second; that table already holds hundreds of millions of historical rows keyed on legacy latitude and longitude doubles. You cannot lock it, you cannot pause ingestion, and you cannot afford a big-bang ALTER that rewrites every row. The answer is the expand-and-contract pattern — add the new column, dual-write from the application, backfill the past in bounded batches, index and constrain concurrently, then cut over and drop the old columns. This page walks the full sequence with runnable SQL and real psycopg code.

Expand, backfill, and contract timeline for a zero-downtime geometry migration A horizontal timeline split into three phases. Expand adds a nullable geom column and starts dual writes. Backfill fills historical rows in batches while adding the index concurrently. Contract enforces NOT NULL, cuts reads over, and drops the legacy latitude and longitude columns. A band underneath shows the service remaining available throughout. EXPAND ADD COLUMN geom nullable, no default app begins dual-writing geom BACKFILL keyset batches of historical rows CREATE INDEX CONCURRENTLY CONTRACT enforce NOT NULL cut reads to geom DROP latitude, longitude Service stays online — no lock held long enough to block ingestion

Prerequisites and Infrastructure Validation

Before touching the schema, confirm PostGIS is present, the driver stack is current, and you know exactly what shape the legacy data is in. A backfill that runs against dirty coordinates silently produces garbage points, so validation comes first.

Required PostgreSQL extensions:

sql
-- PostGIS supplies the geometry type and the GiST operator classes we rely on
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'postgis';

CREATE EXTENSION IF NOT EXISTS postgis;

Required Python packages:

psycopg[binary]>=3.1      # psycopg 3; psycopg2-binary>=2.9 works with minor API changes
sqlalchemy>=2.0
geoalchemy2>=0.14

Inspect the legacy columns and current geometry registration:

sql
-- What do the legacy float columns look like, and how many rows carry usable coordinates?
SELECT
    count(*)                                             AS total_rows,
    count(*) FILTER (WHERE latitude IS NULL
                        OR longitude IS NULL)            AS missing_coords,
    count(*) FILTER (WHERE latitude  BETWEEN  -90 AND  90
                       AND longitude BETWEEN -180 AND 180) AS in_range
FROM sensor_pings;

-- Is a geometry column already registered on this table?
SELECT f_table_name, f_geometry_column, type, srid
FROM geometry_columns
WHERE f_table_name = 'sensor_pings';

If missing_coords is non-zero, decide up front whether those rows should stay NULL in the new column or be excluded from the migration entirely — that decision drives whether you can later enforce NOT NULL. If in_range is smaller than total_rows minus missing_coords, you have out-of-bounds coordinates (swapped lat/lon is the usual culprit) that will produce points in the wrong hemisphere. Clean those before backfilling, never during.

Core Execution Workflow

The migration proceeds in six ordered steps. Each is independently deployable and reversible up to the final DROP. The one rule that binds them: never hold a lock long enough to stall ingestion.

Step 1 — Expand: Add a Nullable Geometry Column

Adding a column with an explicit type but no default and no NOT NULL is a catalog-only operation in modern PostgreSQL. It takes an ACCESS EXCLUSIVE lock for a few milliseconds to update the catalog, then returns — no table rewrite, no per-row work.

sql
-- Instant on any table size: no default, no NOT NULL, no rewrite
ALTER TABLE sensor_pings
  ADD COLUMN geom geometry(Point, 4326);

Registering the SRID in the column type definition (geometry(Point, 4326)) is deliberate. It stamps every future value with SRID 4326 at the type level, so a stray insert of an SRID-0 point is rejected instead of silently corrupting your spatial index. The mechanics of doing this safely on a hot table are covered in depth under Adding Geometry Columns to Live Tables, including why a typed geometry column beats a bare geometry and how ALTER TABLE ADD without locking avoids the rewrite trap.

Step 2 — Dual-Write from the Application

The instant the column exists, every new row must populate it. Update your write path so that each INSERT and each coordinate-changing UPDATE sets geom from the same latitude / longitude values it already writes. This is the load-bearing step: it freezes the set of rows the backfill must touch to “everything written before this deploy.”

python
import psycopg

INSERT_PING = """
    INSERT INTO sensor_pings (sensor_id, recorded_at, latitude, longitude, geom)
    VALUES (
        %(sensor_id)s,
        %(recorded_at)s,
        %(lat)s,
        %(lon)s,
        ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326)
    )
"""

def record_ping(conn: psycopg.Connection, sensor_id: int, recorded_at, lat: float, lon: float) -> None:
    with conn.cursor() as cur:
        cur.execute(
            INSERT_PING,
            {"sensor_id": sensor_id, "recorded_at": recorded_at, "lat": lat, "lon": lon},
        )
    conn.commit()

Note the argument order inside ST_MakePoint: longitude first, then latitude. PostGIS builds points as (X, Y), and for geographic coordinates X is longitude. Reversing them is the single most common backfill bug, and it produces points that land in the ocean off the coast of West Africa (the classic 0,0-adjacent artifact) or in the wrong hemisphere entirely. Keep writing the legacy latitude and longitude too — you have not cut over yet, and reads still depend on them.

For applications that use the ORM rather than raw SQL on the write path, the same dual-write must flow through your session lifecycle correctly so that the geometry and the scalar coordinates commit in one transaction; see Session Management for Spatial Data for how to keep the geometry assignment inside the unit of work.

Step 3 — Backfill Historical Rows in Batches

Now fill the past. A naive single-statement UPDATE sensor_pings SET geom = ... would lock hundreds of millions of rows in one transaction, bloat the table with dead tuples, and swamp replication. Instead, walk the primary key in bounded batches, committing after each one. This is keyset iteration: you track the last id processed and ask for the next window, which keeps every batch on the primary-key index rather than re-scanning from the start.

sql
-- One batch: fill a contiguous id window that still has NULL geom
UPDATE sensor_pings
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
WHERE id > %(last_id)s
  AND id <= %(last_id)s + %(batch)s
  AND geom IS NULL
  AND latitude  IS NOT NULL
  AND longitude IS NOT NULL;

The dedicated walkthrough at Backfilling Geometry From Latitude/Longitude Columns drives this loop from Python with per-batch commits, progress tracking, and a throttle. The essential shape:

python
import time
import psycopg

def backfill_geom(dsn: str, batch: int = 5000, pause_s: float = 0.05) -> int:
    """Backfill geom in keyset batches, committing after each. Returns rows updated."""
    total = 0
    with psycopg.connect(dsn) as conn:
        # Find the id range we need to cover
        with conn.cursor() as cur:
            cur.execute("SELECT min(id), max(id) FROM sensor_pings WHERE geom IS NULL")
            lo, hi = cur.fetchone()
        if lo is None:
            return 0  # nothing to do

        last_id = lo - 1
        while last_id < hi:
            with conn.cursor() as cur:
                cur.execute(
                    """
                    UPDATE sensor_pings
                    SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
                    WHERE id > %(last)s
                      AND id <= %(last)s + %(batch)s
                      AND geom IS NULL
                      AND latitude IS NOT NULL
                      AND longitude IS NOT NULL
                    """,
                    {"last": last_id, "batch": batch},
                )
                total += cur.rowcount
            conn.commit()          # release row locks, let autovacuum breathe
            last_id += batch
            time.sleep(pause_s)    # throttle to cap replication lag
    return total

Each UPDATE runs and commits in its own transaction. That bounds lock duration to a single batch, lets autovacuum reclaim the dead tuples an UPDATE leaves behind, and gives streaming replicas time to keep up. The time.sleep throttle is your pressure-release valve: if replication lag climbs, raise it; if the table is quiet, drop it to zero.

Step 4 — Add the Index and Constraint Concurrently

Only after the column is fully populated do you build the spatial index. Building it earlier means every backfill UPDATE also maintains the index, roughly doubling write cost for no benefit. Build it with CONCURRENTLY so it never takes a lock that blocks ingestion.

sql
-- Build the GiST index without blocking reads or writes
CREATE INDEX CONCURRENTLY idx_sensor_pings_geom
ON sensor_pings USING GIST (geom);

CREATE INDEX CONCURRENTLY scans the table twice and waits out in-flight transactions, so it runs longer than a plain build, but it holds only a SHARE UPDATE EXCLUSIVE lock that permits concurrent DML. The failure modes — an invalid index left behind by a crashed build, long-running transactions stalling the wait phase — and their fixes are the subject of Concurrent Index Builds, with the large-table specifics in CREATE INDEX CONCURRENTLY on Large Spatial Tables.

To promote the column to NOT NULL without a full-table validation scan holding a lock, add a NOT VALID check first, then validate it under a weaker lock:

sql
-- Phase A: add the constraint as NOT VALID — checked for new rows only, near-instant
ALTER TABLE sensor_pings
  ADD CONSTRAINT sensor_pings_geom_not_null CHECK (geom IS NOT NULL) NOT VALID;

-- Phase B: validate existing rows under a SHARE UPDATE EXCLUSIVE lock (allows writes)
ALTER TABLE sensor_pings
  VALIDATE CONSTRAINT sensor_pings_geom_not_null;

The VALIDATE step scans the table but only takes a SHARE UPDATE EXCLUSIVE lock, so ingestion continues throughout. A validated CHECK (geom IS NOT NULL) gives you the same guarantee as a NOT NULL column marker for query planning and data integrity.

Step 5 — Cut Reads Over and Verify Parity

With the column populated, indexed, and constrained, switch your read path to geom. Before flipping the flag, prove the two representations agree. A round-trip comparison catches any lat/lon transposition or precision loss introduced during dual-write:

sql
-- Any row where reconstructed coordinates diverge from the legacy floats is a red flag
SELECT count(*)
FROM sensor_pings
WHERE geom IS NOT NULL
  AND (
        abs(ST_X(geom) - longitude) > 1e-9
     OR abs(ST_Y(geom) - latitude)  > 1e-9
      );

A zero result means every geometry decodes back to the exact coordinates it came from. Now your spatial reads can use the geometry directly and lean on the new GiST index:

python
import psycopg
from psycopg.rows import dict_row

NEARBY = """
    SELECT id, sensor_id, recorded_at
    FROM sensor_pings
    WHERE ST_DWithin(
            geom::geography,
            ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326)::geography,
            %(radius_m)s
          )
    ORDER BY recorded_at DESC
    LIMIT 200
"""

def pings_near(dsn: str, lat: float, lon: float, radius_m: float) -> list[dict]:
    with psycopg.connect(dsn, row_factory=dict_row) as conn, conn.cursor() as cur:
        cur.execute(NEARBY, {"lat": lat, "lon": lon, "radius_m": radius_m})
        return cur.fetchall()

Run both the legacy lat/lon read path and the new geometry read path in parallel for a release or two (a shadow-read comparison) if you want extra confidence. Once the geometry path is trusted, the legacy columns are dead weight.

Step 6 — Contract: Drop the Legacy Columns

The final phase removes what you no longer need. First stop dual-writing (deploy a build that only writes geom), then drop the legacy columns once nothing references them.

sql
-- Catalog-only on modern PostgreSQL: marks the columns dropped without rewriting rows
ALTER TABLE sensor_pings
  DROP COLUMN latitude,
  DROP COLUMN longitude;

DROP COLUMN is fast because PostgreSQL marks the column dead in the catalog rather than rewriting every row; the space is reclaimed lazily by later VACUUM and row updates. Sequence it last, after the read cutover has been stable in production, so a rollback never has to resurrect a dropped column.

Performance Considerations

Reading the Backfill’s Impact with EXPLAIN

Before and during the backfill, confirm each batch UPDATE rides the primary-key index rather than sequentially scanning the whole table:

sql
EXPLAIN (ANALYZE, BUFFERS)
UPDATE sensor_pings
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
WHERE id > 4000000 AND id <= 4005000
  AND geom IS NULL;

A healthy plan shows an Index Scan (or Bitmap Index Scan) on the primary key driving the update:

Update on sensor_pings
  ->  Index Scan using sensor_pings_pkey on sensor_pings
        Index Cond: ((id > 4000000) AND (id <= 4005000))
        Filter: (geom IS NULL)
        Buffers: shared hit=812 read=64

If you instead see Seq Scan, your keyset predicate is not being used — usually because the batch window is so wide the planner estimates most of the table qualifies. Narrow the batch. After the index build, verify spatial reads pick up idx_sensor_pings_geom rather than falling back to a scan.

GUCs That Matter During Migration

sql
-- Give each backfill UPDATE and the index build more working memory
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- Keep dead tuples from the batched UPDATEs under control on this hot table
ALTER TABLE sensor_pings SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit   = 2000
);

SELECT pg_reload_conf();

Raising maintenance_work_mem speeds up CREATE INDEX CONCURRENTLY. Tightening the table’s autovacuum thresholds keeps the churn from a few hundred million UPDATEs from accumulating into bloat before the daemon reacts.

Replication Lag as the Real Governor

On a replicated cluster the backfill rate is bounded not by the primary’s CPU but by how fast replicas can apply the WAL each batch generates. Watch lag and let it drive your throttle:

sql
-- Run on a replica: how far behind is it applying WAL?
SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;

If replay_lag grows across successive batches, raise the pause_s throttle or shrink the batch size until it holds steady. A backfill that finishes an hour later but never lags replicas is strictly better than one that races ahead and triggers failover alarms.

Common Failure Modes and Fixes

Transposed Latitude and Longitude

Symptom: After backfill, ST_X(geom) returns values in the ±90 range and ST_Y(geom) in the ±180 range — the axes are swapped, and points land nowhere near the sensors.

Fix: ST_MakePoint takes (longitude, latitude). Audit every call — the dual-write insert, the backfill UPDATE, and any ad-hoc scripts — and confirm longitude is the first argument. Re-run the parity check from Step 5; if it fails, the geometry column must be recomputed from the legacy floats, which is why you keep those columns until after cutover.

Backfill Bloats the Table and Stalls Autovacuum

Symptom: n_dead_tup climbs into the tens of millions and table size balloons while the backfill runs.

Diagnosis:

sql
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'sensor_pings';

Fix: Each batch must commit in its own transaction — verify you are not wrapping the whole loop in one transaction. Lower the per-table autovacuum_vacuum_scale_factor as shown above, and if dead tuples still outrun the daemon, run an explicit VACUUM sensor_pings; partway through the backfill.

SRID 0 Sneaks Into the Column

Symptom: SELECT DISTINCT ST_SRID(geom) FROM sensor_pings; returns 0 for some rows, and spatial queries that cast to geography or join across SRID 4326 data error out or silently return nothing.

Fix: Because the column was declared geometry(Point, 4326), a direct insert of an SRID-0 point is rejected — but a raw ST_MakePoint without ST_SetSRID produces SRID 0. Always wrap coordinate construction in ST_SetSRID(ST_MakePoint(lon, lat), 4326). To repair existing rows: UPDATE sensor_pings SET geom = ST_SetSRID(geom, 4326) WHERE ST_SRID(geom) = 0;.

Invalid Index After a Failed Concurrent Build

Symptom: CREATE INDEX CONCURRENTLY was interrupted, and \d sensor_pings shows the index marked INVALID.

Fix: A failed concurrent build leaves an unusable index behind. Drop it and rebuild:

sql
DROP INDEX CONCURRENTLY IF EXISTS idx_sensor_pings_geom;
CREATE INDEX CONCURRENTLY idx_sensor_pings_geom ON sensor_pings USING GIST (geom);

Verification

After the migration completes, confirm every invariant holds before you consider the legacy columns safe to forget:

sql
-- 1. No NULL geometries remain among rows that had valid coordinates
SELECT count(*) AS null_geom
FROM sensor_pings
WHERE geom IS NULL
  AND latitude IS NOT NULL
  AND longitude IS NOT NULL;

-- 2. Every geometry is registered as SRID 4326
SELECT DISTINCT ST_SRID(geom) AS srid
FROM sensor_pings
WHERE geom IS NOT NULL;

-- 3. The GiST index exists and is valid
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE indrelid = 'sensor_pings'::regclass
  AND indexrelid::regclass::text = 'idx_sensor_pings_geom';

-- 4. Spatial reads actually use the index
EXPLAIN (ANALYZE)
SELECT id FROM sensor_pings
WHERE ST_DWithin(
        geom::geography,
        ST_SetSRID(ST_MakePoint(-122.27, 37.80), 4326)::geography,
        1000
      );

A clean migration returns null_geom = 0, a single srid = 4326, indisvalid = true, and an Index Scan using idx_sensor_pings_geom in the query plan. With those four confirmed, the DROP COLUMN in Step 6 is safe.

Frequently Asked Questions

Why add the geometry column as nullable instead of NOT NULL from the start?

A NOT NULL column with a default rewrites every existing row under an ACCESS EXCLUSIVE lock, which blocks all reads and writes on a large sensor_pings table. Adding a nullable column with no default is a catalog-only change that returns instantly. You populate it afterwards in small batches, then promote it to NOT NULL with a validated CHECK constraint that skips the full-table rewrite.

How large should each backfill batch be?

Size batches so a single UPDATE commits in well under a second and touches a few thousand rows. Between 2,000 and 10,000 rows per batch is a sound starting range for a point-geometry backfill. Each batch runs in its own transaction and commits before the next begins, so autovacuum can reclaim dead tuples and replication lag stays bounded.

Do I have to stop writes during the backfill?

No. That is the entire point of dual-writing. Once the application writes geom on every INSERT and UPDATE, new rows already carry correct geometry, so the backfill only has to fill rows written before the dual-write deploy. Reads continue against the legacy columns until you cut over, and neither phase requires a maintenance window.