Migrating a PostGIS OSM Schema Without Downtime

An OSM table that a map service reads from cannot be rebuilt in place, because the rebuild takes hours and holds a lock for all of them. The way round it is to build the new shape beside the old one and switch which name points at it.

Prerequisites

Conceptual minimum

The migration turns on one idea: readers resolve a name, and the name can be made to resolve somewhere else atomically. Postgres gives two mechanisms for this, and the choice between them is the main design decision.

A schema switch uses search_path. Readers query unqualified planet_osm_polygon; the new version lives in osm_v2 and the old in osm_v1; changing the role’s search_path changes which one an unqualified name finds. It is a catalogue update, so it takes microseconds, and it applies to new sessions or at the next SET.

A view switch keeps the tables named distinctly and points a view at whichever is current. CREATE OR REPLACE VIEW takes a brief ACCESS EXCLUSIVE lock on the view, which is fine because nothing holds a long lock on a view definition, and the switch is transactional so it can be rolled back with the transaction.

The view is the better default: it is explicit, it is transactional, and it does not depend on per-role session state that is easy to get wrong. The schema switch wins when the new version has to differ in more than one object at a time, because a whole schema swaps as a unit.

In-place ALTER versus shadow-and-switch, in what each holds Two panels. An in-place rewrite takes an ACCESS EXCLUSIVE lock on the live table for the whole rewrite, which on a continental polygon table is hours, blocks every reader, and cannot be undone once partly applied without another rewrite. The shadow approach creates the new table in a separate schema, backfills while readers continue against the old one untouched, verifies the two side by side, then switches a view in a transaction holding a lock for milliseconds, with the old table still present for rollback. What each approach locks, and for how long In-place ALTER Exclusive lock on live table Held for the whole rewrite Hours on a planet table Every reader blocked Rollback is another rewrite Shadow and switch New table, separate schema Readers untouched meanwhile Verify both sides at once Lock held for milliseconds Old table kept for rollback The shadow approach costs disk for a second copy, which is almost always cheaper than the outage the first one causes.
Both end with the same schema; only one of them can be done during working hours.

Runnable solution

sql 
-- 1. Build the new shape in its own schema. Nothing here touches the live table.
CREATE SCHEMA IF NOT EXISTS osm_next;

CREATE TABLE osm_next.polygon (
    osm_id      bigint PRIMARY KEY,
    osm_type    char(1) NOT NULL,
    -- new in v2: tags as jsonb rather than a wide column set
    tags        jsonb   NOT NULL DEFAULT '{}'::jsonb,
    -- new in v2: an explicit area, so consumers stop calling ST_Area ad hoc
    area_m2     double precision,
    geom        geometry(MultiPolygon, 3857) NOT NULL,
    updated_at  timestamptz NOT NULL DEFAULT now()
);

-- 2. Backfill in bounded batches so no single statement holds a long snapshot.
--    Run this repeatedly until it reports zero rows moved.
INSERT INTO osm_next.polygon (osm_id, osm_type, tags, area_m2, geom)
SELECT p.osm_id,
       CASE WHEN p.osm_id < 0 THEN 'r' ELSE 'w' END,
       jsonb_strip_nulls(jsonb_build_object(
           'building', p.building, 'landuse', p.landuse,
           'natural',  p."natural", 'name',    p.name)),
       ST_Area(p.way::geography),
       ST_Multi(p.way)
FROM   osm_live.planet_osm_polygon AS p
WHERE  p.osm_id > COALESCE((SELECT max(osm_id) FROM osm_next.polygon), -1)
ORDER  BY p.osm_id
LIMIT  200000
ON CONFLICT (osm_id) DO NOTHING;

-- 3. Index AFTER the bulk load, and CONCURRENTLY so it never blocks.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_next_polygon_geom
    ON osm_next.polygon USING GIST (geom);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_next_polygon_tags
    ON osm_next.polygon USING GIN (tags jsonb_path_ops);
ANALYZE osm_next.polygon;

-- 4. Verify before switching. Counts, and a geometry equality sample.
SELECT (SELECT count(*) FROM osm_live.planet_osm_polygon) AS live_rows,
       (SELECT count(*) FROM osm_next.polygon)            AS next_rows;

SELECT count(*) AS mismatched
FROM   osm_live.planet_osm_polygon AS l
JOIN   osm_next.polygon            AS n USING (osm_id)
WHERE  NOT ST_Equals(ST_Multi(l.way), n.geom)
LIMIT  1000;

-- 5. Cut over. Transactional, milliseconds, reversible by rolling back.
BEGIN;
  CREATE OR REPLACE VIEW public.osm_polygon AS
      SELECT osm_id, osm_type, tags, area_m2, geom FROM osm_next.polygon;
  COMMENT ON VIEW public.osm_polygon IS 'v2 since 2026-09-17; v1 in osm_live';
COMMIT;

-- 6. Keep the old table until rollback stops being plausible. Then, separately:
--    DROP TABLE osm_live.planet_osm_polygon;
python 
"""Drive the batched backfill until it converges, with a lock-time guard."""
from __future__ import annotations

import logging
import time

import psycopg

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")
logger = logging.getLogger("osm.postgis.migrate")

BACKFILL = open("backfill.sql", encoding="utf-8").read()


def backfill(dsn: str, batch_pause_s: float = 0.5) -> int:
    total = 0
    with psycopg.connect(dsn, autocommit=True) as conn:
        # A statement that cannot get its lock quickly should fail, not queue:
        # a queued ACCESS EXCLUSIVE request blocks every reader behind it.
        conn.execute("SET lock_timeout = '3s'")
        conn.execute("SET statement_timeout = '10min'")
        while True:
            with conn.cursor() as cur:
                cur.execute(BACKFILL)
                moved = cur.rowcount
            total += moved
            logger.info("backfilled %d rows (%d total)", moved, total)
            if moved == 0:
                return total
            time.sleep(batch_pause_s)  # let autovacuum and readers breathe


if __name__ == "__main__":
    logger.info("backfill, verify, then switch the view in a transaction")

Step-by-step walkthrough

  1. Create the new schema, not a new column. Adding columns to the live table is the in-place path in disguise, and a rewrite-triggering ALTER holds the same lock as any other.
  2. Backfill in bounded batches. A single INSERT ... SELECT over a continental table holds one snapshot for hours, which bloats the source and blocks vacuum.
  3. Set lock_timeout. A migration statement that cannot acquire its lock immediately should fail rather than queue, because a queued exclusive request blocks every reader arriving behind it.
  4. Index after loading, and concurrently. Building indexes during the load slows it and a non-concurrent build blocks writes, neither of which the switch needs.
  5. Verify before switching, not after. Compare counts and sample geometry equality between the two tables while both exist, which is the only window in which that comparison is possible.
  6. Switch inside a transaction. CREATE OR REPLACE VIEW is transactional, so an unexpected result rolls the switch back with the transaction rather than leaving a half-migrated name.
  7. Repoint the loader separately. Readers and the writer switch at different times, and a writer still filling the old table after the switch produces a silently stale view.
  8. Keep the old table. Dropping it in the same change removes the only cheap rollback, and disk is a better thing to spend than an outage.
Where the risk sits across a shadow migration A timeline of five marks. Creating the new schema carries no risk because nothing live is touched. The backfill is long but low risk, with the only hazards being disk consumption and vacuum pressure on the source. Index building is slow and safe when done concurrently. Verification is the decision point, and skipping it moves all the risk past the switch where it cannot be undone cheaply. The switch itself is milliseconds and reversible. Dropping the old table is the one irreversible step and should happen days later. Risk across the five phases Create no risk at all nothing live touched Backfill long, low risk disk and vacuum only Verify the decision point only comparison window Switch milliseconds transactional, reversible Drop old irreversible days later, separately Skipping verification does not remove risk; it relocates it past the switch, where the cheap remedy no longer exists.
Only the last mark cannot be undone, and it is the one with no deadline attached.
Choosing between a view switch and a schema search_path switch A decision with two branches. A view switch keeps tables distinctly named and repoints a view, which is transactional, explicit in the catalogue and rolls back with its transaction, but it swaps one object at a time. A search_path switch moves a whole schema at once, which suits a migration changing several related objects together, but it depends on per-role session state and applies at the next session or SET rather than instantly for connections already open. Which switch mechanism How many objects change together? One view, or a whole schema Both atomic; one is transactional One object: replace the view Transactional, explicit, rolls back with the transaction Several at once: move the search_path A schema swaps as a unit, but open sessions keep the old one Existing connections keep their old search_path until they reconnect or issue SET, which is the usual surprise with the second option.
Prefer the view unless the migration genuinely changes several related objects together.

Verification

  • Counts match. Live and shadow row counts should agree, or differ only by rows added since the backfill started.
  • Geometry survives. A sample ST_Equals comparison across joined identifiers should find no mismatches.
  • Query plans are sane. EXPLAIN a representative query against the view and confirm it uses the new indexes.
  • The switch is fast. Time the cutover transaction; anything beyond a few milliseconds means something held a lock.
  • Rollback works. Before dropping anything, point the view back at the old table and confirm readers recover.

Common errors and fixes

Symptom Root cause One-line fix
Readers block for hours In-place ALTER rewriting the table Build a shadow table and switch a view
Backfill bloats the source One statement holding a long snapshot Batch with LIMIT and commit between batches
Cutover hangs No lock_timeout, request queued behind a reader SET lock_timeout = '3s' and retry
View shows stale data Loader still writing to the old table Repoint the writer as a separate, explicit step
Index build blocks writes CREATE INDEX without CONCURRENTLY Build concurrently, after the bulk load
Queries slow after cutover Statistics never gathered ANALYZE the new table before switching
No way back Old table dropped in the same change Keep it for days; drop it as its own change

Specification reference

CREATE OR REPLACE VIEW replaces the definition of an existing view. The new query must generate the same columns in the same order with the same types, though columns may be added at the end. The operation is transactional. CREATE INDEX CONCURRENTLY builds an index without taking any locks that prevent concurrent inserts, updates or deletes on the table, at the cost of two table scans. See the PostgreSQL documentation for CREATE VIEW, CREATE INDEX and lock_timeout.

Frequently Asked Questions

Why not just add a column to the existing table?

Adding a nullable column with no default is instant and perfectly safe. The trouble is that OSM schema changes are rarely that: they change a type, add a NOT NULL with a computed default, or restructure wide columns into jsonb, and each of those rewrites the whole table under an exclusive lock. On a continental polygon table that is hours of blocked readers. The shadow approach costs disk and buys the ability to do it during the day.

Should the loader write to both schemas during the migration?

Only if the backfill will run long enough that the gap matters. Dual-writing doubles the write path’s failure surface and introduces the question of what to do when one side succeeds and the other does not. For a backfill measured in hours against data that updates daily, a simpler answer is to run the backfill, switch, and then let the next normal load populate the new table’s recent changes.

How long should the old table be kept?

Until a full cycle of every consumer has run against the new one — which usually means at least one of whatever your slowest periodic job is, plus a working week. The cost is disk, and the thing it buys is that a problem discovered on day three has a one-statement remedy rather than a re-migration. Drop it as its own deliberate change, never as the tail of the migration.

Does the same approach work for a GeoParquet export?

It works better, because object storage has no locks at all. Write the new-shape files to a new prefix, verify, and repoint whatever resolves the dataset path — a catalogue entry, a manifest, or a symlink-like alias. The structure of the migration is identical; only the atomic switch mechanism differs.

Up one level: Exporting OSM to GeoParquet and PostGIS.