Batch ST_Transform Reprojection in Python

This page implements the data-rewrite half of In-Place SRID Reprojection for tables too large to reproject in a single statement: a cadastral land_parcels table with tens of millions of polygons that must move from EPSG:25833 (ETRS89 / UTM 33N) to EPSG:32633 (WGS 84 / UTM 33N). The goal is to run ST_Transform over the whole table without ever holding a transaction open long enough to bloat the heap, stall autovacuum, or lag physical replicas.

Why the Naive Approach Fails

The obvious one-liner is also the one that takes the table down:

python
# DO NOT run this on a 40-million-row table
import psycopg

with psycopg.connect("dbname=cadastre") as conn:
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE land_parcels SET geom = ST_Transform(geom, 32633)"
        )
    conn.commit()   # reached only after every row is rewritten

Three things go wrong at scale. First, the statement runs in one transaction, so every dead tuple it generates — one per row, because an UPDATE writes a new row version — stays visible to that transaction’s snapshot and cannot be vacuumed until commit. The table can nearly double on disk. Second, autovacuum cannot advance its dead-tuple horizon while the long transaction is open, so bloat accrues database-wide, not just on land_parcels. Third, if the connection drops after 30 minutes, the entire rewrite rolls back and you start over. There is no progress and no resumability.

Fetching every geometry into Python and reprojecting client-side with pyproj is worse: it adds a full read and a full write of the geometry payload over the wire and abandons PostGIS’s PROJ pipeline. Keep the transform server-side; use Python only to schedule bounded chunks.

Production-Ready Implementation

The driver below walks the primary key in fixed-width windows, runs one ST_Transform UPDATE per window, and commits after each. A server-side (named) cursor enumerates the id boundaries without loading them all into client memory. The ST_SRID(geom) = 25833 guard makes every chunk idempotent, so an interrupted run resumes cleanly.

Before running it, relax the column typmod once (as covered in the parent) so the column can hold mixed SRIDs during the rewrite:

sql
ALTER TABLE land_parcels ALTER COLUMN geom TYPE geometry(Polygon);
python
"""Chunked in-place reprojection of land_parcels.geom from EPSG:25833 to 32633.

Each id-range chunk is its own transaction. The heap rewrite stays server-side
via ST_Transform; Python only chooses ranges and commits.
"""
import time
import logging
import psycopg

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(message)s")
log = logging.getLogger("reproject")

DSN = "host=db-primary dbname=cadastre user=migrator"
SOURCE_SRID = 25833
TARGET_SRID = 32633
CHUNK = 40_000          # rows per transaction; tune to a few-seconds commit
PAUSE_SECONDS = 0.25    # let autovacuum and replicas breathe between chunks


def id_bounds(conn: psycopg.Connection) -> tuple[int, int]:
    with conn.cursor() as cur:
        cur.execute("SELECT min(parcel_id), max(parcel_id) FROM land_parcels")
        lo, hi = cur.fetchone()
    return (lo or 0, hi or -1)


def reproject_chunk(conn: psycopg.Connection, lo: int, hi: int) -> int:
    """Reproject one half-open [lo, hi) parcel_id window in a single txn."""
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE land_parcels
            SET geom = ST_Transform(geom, %(target)s)
            WHERE parcel_id >= %(lo)s
              AND parcel_id <  %(hi)s
              AND ST_SRID(geom) = %(source)s
            """,
            {"target": TARGET_SRID, "lo": lo, "hi": hi, "source": SOURCE_SRID},
        )
        affected = cur.rowcount
    conn.commit()   # bound the transaction to this chunk only
    return affected


def run() -> None:
    # autocommit=False so each chunk's UPDATE + commit is one transaction.
    with psycopg.connect(DSN, autocommit=False) as conn:
        lo, hi = id_bounds(conn)
        total = 0
        window = lo
        while window <= hi:
            top = window + CHUNK
            t0 = time.perf_counter()
            n = reproject_chunk(conn, window, top)
            total += n
            log.info(
                "chunk [%d, %d) reprojected %d rows in %.2fs (total %d)",
                window, top, n, time.perf_counter() - t0, total,
            )
            window = top
            time.sleep(PAUSE_SECONDS)
        log.info("done: %d rows reprojected to EPSG:%d", total, TARGET_SRID)


if __name__ == "__main__":
    run()

If parcel_id is sparse or non-contiguous, replace the arithmetic windows with a server-side cursor that yields real id boundaries, so empty ranges do not waste round trips:

python
def keyset_bounds(conn: psycopg.Connection, chunk: int):
    """Yield (lo, hi) pairs on actual parcel_id values using a named cursor."""
    # A server-side cursor streams ids without buffering them all in Python.
    with conn.cursor(name="parcel_id_stream") as cur:
        cur.itersize = 10_000            # server -> client fetch batch
        cur.execute(
            "SELECT parcel_id FROM land_parcels "
            "WHERE ST_SRID(geom) = %s ORDER BY parcel_id",
            (SOURCE_SRID,),
        )
        batch_lo = None
        seen = 0
        for (pid,) in cur:
            if batch_lo is None:
                batch_lo = pid
            seen += 1
            if seen >= chunk:
                yield (batch_lo, pid + 1)   # half-open upper bound
                batch_lo, seen = None, 0
        if batch_lo is not None:
            yield (batch_lo, batch_lo + 1)

After the loop finishes, re-apply the SRID typmod and rebuild the GiST index exactly as the parent describes; the Python driver deliberately stops at the data rewrite.

Configuration and Tuning Knobs

  • CHUNK size. Target a per-chunk commit of two to five seconds. Time the first few chunks from the log output and adjust. Bigger chunks cut loop overhead but enlarge each transaction’s dead-tuple footprint.
  • cur.itersize on the named cursor controls how many ids the server ships per network round trip. 10_000 is a sane default; raising it reduces round trips at the cost of client memory.
  • autocommit=False with explicit conn.commit() is the load-bearing choice. It guarantees the transaction boundary is exactly one chunk. Never wrap the while loop in a single with conn.transaction(): block — that recreates the one-giant-transaction problem.
  • maintenance_work_mem matters later, for the index rebuild, not the UPDATE. Set SET maintenance_work_mem = '1GB'; before the CREATE INDEX CONCURRENTLY step.
  • Aggressive autovacuum on the table so dead tuples are reclaimed between chunks:
sql
ALTER TABLE land_parcels SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay   = 2
);
  • statement_timeout. Set a per-chunk ceiling so a pathological chunk fails fast rather than hanging the migration: SET statement_timeout = '60s'; at session start.

Verification Steps

sql
-- 1. No source-SRID rows remain
SELECT count(*) FROM land_parcels WHERE ST_SRID(geom) = 25833;   -- expect 0

-- 2. Everything is now in the target CRS
SELECT ST_SRID(geom) AS srid, count(*)
FROM land_parcels GROUP BY ST_SRID(geom);                        -- one group: 32633

-- 3. Bloat check after the run, before/after a manual vacuum
SELECT n_dead_tup, n_live_tup
FROM pg_stat_user_tables WHERE relname = 'land_parcels';
VACUUM (ANALYZE) land_parcels;

A quick Python assertion confirms the whole table converged and no chunk was skipped:

python
import psycopg

with psycopg.connect(DSN) as conn, conn.cursor() as cur:
    cur.execute("SELECT count(*) FROM land_parcels WHERE ST_SRID(geom) <> 32633")
    remaining = cur.fetchone()[0]
    assert remaining == 0, f"{remaining} rows not in EPSG:32633"
    print("all parcels reprojected to EPSG:32633")

Gotchas Checklist

  • Relax the typmod first. If the column is still geometry(Polygon, 25833), the very first chunk fails with a SRID constraint error before writing a row.
  • Keep the idempotency guard. Without AND ST_SRID(geom) = 25833, a resumed run re-transforms already-moved rows, corrupting their coordinates by applying a second projection.
  • Do not batch by OFFSET. LIMIT/OFFSET paging shifts as rows change and re-scans the heap each time; page by the indexed parcel_id key instead.
  • Watch replication lag. Each chunk generates WAL; on a heavily replicated cluster, raise PAUSE_SECONDS so standbys keep up rather than falling behind the primary.
  • Rebuild the index after, not during. Leave the stale GiST index until the rewrite completes, then recreate it concurrently — reprojecting with a live-but-wrong index only wastes maintenance on soon-to-be-invalid bounds.