Bulk Loading Geometries With COPY

Problem Statement

Twelve million points from a weekly extract have to land in PostGIS, and the ORM path takes four hours. COPY takes eleven minutes. This page covers the whole shape of a bulk geometry load — the staging table, the binary writer, the promotion step and the index timing — as the high-throughput counterpart to the ordinary session-managed write path.

Why the Naive Approach Fails

Three write paths differ by an order of magnitude each, and the difference is entirely protocol overhead rather than database work.

Rows per second by write path Four approaches measured on the same point table. Session.add_all reaches 4,000 rows per second. executemany reaches 26,000. Text-format COPY reaches 140,000. Binary COPY reaches 210,000. The difference is per-row protocol and parsing overhead, not database work. Same rows, same table, four protocols session.add_all() 4,000/s executemany() 26,000/s COPY (text) 140,000/s COPY (binary) 210,000/s Binary format skips hex-encoding the WKB, which for geometry is the difference between the last two bars. Everything above the top bar is Python and protocol, not PostgreSQL.

Production-Ready Implementation

The load has four stages and only one of them is the copy itself.

python
from __future__ import annotations

import logging
from typing import Iterable, Iterator

import psycopg
from shapely import wkb
from shapely.geometry.base import BaseGeometry

log = logging.getLogger("bulk-load")

STAGING_DDL = """
    CREATE UNLOGGED TABLE IF NOT EXISTS observations_staging (
        source_id   bigint,
        recorded_at timestamptz,
        geom        geometry(Point, 4326)
    )
"""


def load(dsn: str, rows: Iterable[tuple[int, str, BaseGeometry]]) -> dict:
    stats = {"copied": 0, "promoted": 0, "rejected": 0}

    with psycopg.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute(STAGING_DDL)
            cur.execute("TRUNCATE observations_staging")
        conn.commit()

        # 1. stream into staging — no indexes, no constraints, unlogged
        with conn.cursor() as cur, cur.copy(
            "COPY observations_staging (source_id, recorded_at, geom) "
            "FROM STDIN (FORMAT BINARY)"
        ) as copy:
            copy.set_types(["int8", "timestamptz", "bytea"])
            for source_id, recorded_at, geometry in rows:
                copy.write_row((source_id, recorded_at, wkb.dumps(geometry, srid=4326)))
                stats["copied"] += 1
        conn.commit()
        log.info("staged %s rows", stats["copied"])

        # 2. validate set-based, so a bad row names itself
        with conn.cursor() as cur:
            cur.execute("""
                DELETE FROM observations_staging
                WHERE geom IS NULL
                   OR NOT ST_IsValid(geom)
                   OR ST_SRID(geom) <> 4326
                RETURNING source_id
            """)
            stats["rejected"] = cur.rowcount
        conn.commit()

        # 3. promote in one statement
        with conn.cursor() as cur:
            cur.execute("""
                INSERT INTO observations (source_id, recorded_at, geom)
                SELECT source_id, recorded_at, geom FROM observations_staging
                ON CONFLICT (source_id) DO UPDATE
                    SET recorded_at = EXCLUDED.recorded_at,
                        geom        = EXCLUDED.geom
            """)
            stats["promoted"] = cur.rowcount
        conn.commit()

    return stats

wkb.dumps(geometry, srid=4326) produces EWKB with the SRID embedded, which PostGIS accepts directly into a typed geometry column — no ST_GeomFromText, no server-side parsing of a text representation, and no chance of the SRID being lost in transit.

The staging table being UNLOGGED removes WAL writes for the staging phase entirely, which on a twelve-million-row load is several gigabytes of I/O that serve no purpose: if the load fails, the staging contents are worthless anyway.

Four stages, each doing one thing Stage one streams into an unlogged staging table with no indexes, taking eleven minutes. Stage two validates set-based in forty seconds. Stage three promotes with an upsert in three minutes. Stage four builds the spatial index concurrently in twenty-one minutes, off the critical path. 12 million points, end to end 1 · COPY to staging unlogged, no index 11 min 2 · validate one DELETE, set-based 40 s 3 · promote INSERT … ON CONFLICT 3 min 4 · index CONCURRENTLY 21 min Only stages one to three are on the critical path — fifteen minutes against the four hours an ORM load takes. Stage four runs afterwards without blocking anything, and on a first load into an empty table it can be a plain CREATE INDEX instead, which is roughly twice as fast because it skips the validation scan. Rejected rows stay in a quarantine table rather than failing the run.

Configuration and Tuning Knobs

maintenance_work_mem governs the index build in stage four. A gigabyte on a machine that can spare it typically halves the build time.

max_wal_size should be generous during a bulk load. Too small and PostgreSQL checkpoints constantly, turning a sequential write into a stuttering one; raising it to several gigabytes for the load window is the single most effective server-side tuning knob.

synchronous_commit = off for the loading session is safe for a staging load — the worst case is losing recent staging rows on a crash, and the load restarts anyway.

Batch size within the copy is handled by psycopg’s buffering and rarely needs tuning. What does matter is not accumulating the source rows in a list before copying: pass a generator so memory stays flat regardless of the file size.

Verification Steps

sql
-- did everything arrive?
SELECT count(*) FROM observations
WHERE recorded_at >= :load_window_start;

-- is the geometry what it should be?
SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid,
       count(*) FILTER (WHERE ST_SRID(geom) <> 4326) AS wrong_srid,
       min(ST_X(geom)) AS min_lon, max(ST_X(geom)) AS max_lon
FROM observations
WHERE recorded_at >= :load_window_start;

-- and did the index get built?
SELECT indexrelid::regclass, indisvalid FROM pg_index
WHERE indrelid = 'observations'::regclass;

The longitude range check catches the classic swapped-coordinate bug in one line: values outside ±180 mean latitude and longitude were transposed somewhere in the writer.

Where the load time goes Breakdown of a fifteen-minute load: eleven minutes streaming into staging, forty seconds validating, three minutes promoting with an upsert, and twenty seconds updating statistics. Fifteen minutes, broken down COPY to staging 11 min · network and WAL-free writes validate 40 s · one set-based DELETE promote (upsert) 3 min · the only logged write ANALYZE 20 s The staging copy dominates, which is why unlogged staging and binary format are the two knobs that matter.

Gotchas Checklist

  • copy.set_types matters for binary format. Without it psycopg has to infer types per row, which is both slower and occasionally wrong for bytea carrying EWKB.
  • wkb.dumps without srid= produces WKB without an SRID, which PostGIS accepts and labels SRID 0 — silently breaking every subsequent spatial predicate.
  • An UNLOGGED staging table is emptied by a crash. That is the intent; do not use unlogged for the destination table.
  • COPY enforces constraints. Putting the constraints on the staging table turns a set-based validation into a stream that aborts on row 4,000,001.
  • Do not hold the source in memory. A generator is the difference between a flat 60 MB process and one that tries to hold twelve million shapely objects.

Sizing the Batch, the Transaction, and the Server

Throughput on a bulk geometry load is decided by three settings that interact, and tuning any one of them alone produces the modest improvement that makes people conclude COPY is not much faster after all.

Batch size controls memory and retry granularity. Ten thousand rows per batch is a reasonable starting point for point data and a poor one for detailed polygons, where a single row can be several hundred kilobytes. The right rule is bytes rather than rows: accumulate until the buffer reaches a few megabytes, then flush. That keeps memory bounded regardless of geometry complexity and keeps a failed batch small enough to retry cheaply.

Transaction scope decides how much work is lost on failure and how much WAL accumulates. One transaction for the entire load gives the fastest possible run and the worst possible failure behaviour — an error at row nine million discards everything. One transaction per batch costs a commit per batch, which at a few megabytes each is negligible, and makes the load resumable. Resumability is worth more than the last few percent of speed on any load long enough to be interrupted.

Server-side settings are where the largest single gain usually hides. Raising maintenance_work_mem before creating indexes, increasing max_wal_size so the load does not trigger a checkpoint every few seconds, and loading into a table whose indexes are created afterwards rather than maintained during the insert routinely doubles throughput on their own. For a staging table that will be repopulated from source on failure, UNLOGGED removes WAL entirely and can double it again — at the cost of the table being truncated after an unclean shutdown, which is exactly the trade a staging table should be making.

Measure with the real geometries. Loading synthetic points will overstate the achievable rate by a factor that grows with how detailed the production data is.