Why COPY into a staging table instead of COPY directly into the target?

COPY cannot run ON CONFLICT logic or call ST_MakePoint on the fly, so a direct COPY into an indexed, geometry-bearing target would either reject duplicates outright or require the geometry to be precomputed client-side. Staging into an unlogged, index-free table lets COPY run at full speed, then a single INSERT ... SELECT ... ON CONFLICT builds the geometry and deduplicates in the database where it is fastest.

Is ST_GeomFromText faster than ST_MakePoint for point construction?

No. ST_MakePoint takes numeric lon and lat directly and skips WKT parsing entirely, so it is measurably faster and avoids locale-dependent decimal formatting bugs. Reserve ST_GeomFromText for non-point geometries or when your source already holds WKT strings. For point telemetry, always use ST_SetSRID(ST_MakePoint(lon, lat), 4326).

Should I drop the spatial index before a large COPY load?

For a full partition load, yes. Load into an index-free staging table, upsert into the target, then build the GiST index on the target partition once at the end. Maintaining a GiST index row-by-row during a multi-million-row insert is dramatically slower than a single bulk build. For incremental appends of a few thousand rows, leave the index in place.

Bulk-Loading Sensor Readings into PostGIS with COPY and ST_MakePoint

To load millions of environmental sensor rows into PostGIS quickly, stream them with COPY into an unlogged staging table, then run a single INSERT ... SELECT ... ON CONFLICT that builds each point with ST_SetSRID(ST_MakePoint(lon, lat), 4326) and deduplicates on the natural key. COPY is roughly an order of magnitude faster than row-by-row INSERT because it batches the whole payload into one server round-trip and bypasses per-statement parsing; deferring geometry construction and conflict handling to a set-based database step keeps that speed while still producing indexable, idempotent output for the PostGIS storage and spatial indexing schema.

Why COPY plus Staging Beats Direct Inserts

Three costs dominate a naive load. First, each INSERT statement is parsed, planned, and committed individually, so a million single-row inserts pay a million round-trips. Second, if the target table has a GiST index on its geometry column, every insert updates that index in place — cheap once, brutal a million times. Third, constructing geometry on the client and shipping WKB, or calling ST_GeomFromText per row, adds parsing overhead the load does not need.

COPY collapses the first cost: the entire batch travels as one stream and PostgreSQL writes it with minimal per-row overhead. But COPY is deliberately dumb — it cannot evaluate ON CONFLICT, cannot call a function like ST_MakePoint, and cannot target a subset of columns with expressions. That is why the fast pattern is two-phase. Phase one COPYs raw lon/lat numeric columns into a staging table that has no geometry column and no indexes, so nothing slows the write. Phase two runs one set-based INSERT ... SELECT from staging into the real partitioned table, and it is there that ST_MakePoint builds the point and ON CONFLICT DO UPDATE absorbs re-delivered readings.

ST_MakePoint matters specifically because it consumes the numeric lon and lat directly. ST_GeomFromText('POINT(...)') would force you to format those floats into a WKT string first, which is slower and exposes you to locale bugs where a comma decimal separator silently corrupts coordinates. Building the geometry inside the database, from numeric columns, is both faster and safer.

An unlogged staging table skips write-ahead logging entirely, which nearly doubles COPY throughput. The trade-off — unlogged tables do not survive a crash — is irrelevant for staging, because the data is transient and re-derivable from the source batch.

Production-Ready Implementation

The function below takes an iterable of reading tuples, COPYs them into an unlogged staging table via copy_expert, upserts into the partitioned target with ST_MakePoint, and truncates staging. It is self-contained and safe to drop into an ingestion worker.

# python 3.11 · psycopg2-binary==2.9.9 · shapely==2.0.4
import csv
import io
from typing import Iterable, Sequence

import psycopg2

# Column order for both staging COPY and the target INSERT.
COLUMNS = ("station_id", "observed_at", "metric", "value", "quality_flag", "lon", "lat")

DDL_STAGING = """
CREATE UNLOGGED TABLE IF NOT EXISTS sensor_readings_staging (
    station_id   text             NOT NULL,
    observed_at  timestamptz      NOT NULL,
    metric       text             NOT NULL,
    value        double precision,
    quality_flag smallint         NOT NULL DEFAULT 0,
    lon          double precision NOT NULL,
    lat          double precision NOT NULL
)
"""

# Build the point in the database; dedupe on the natural key.
UPSERT = """
INSERT INTO sensor_readings
    (station_id, observed_at, metric, value, quality_flag, lon, lat)
SELECT station_id, observed_at, metric, value, quality_flag, lon, lat
FROM sensor_readings_staging
ON CONFLICT (station_id, observed_at, metric)
DO UPDATE SET
    value        = EXCLUDED.value,
    quality_flag = EXCLUDED.quality_flag,
    lon          = EXCLUDED.lon,
    lat          = EXCLUDED.lat
"""


def bulk_load_readings(dsn: str, rows: Iterable[Sequence]) -> int:
    """
    Bulk-load sensor readings into a partitioned PostGIS table.

    Each row must be (station_id, observed_at, metric, value, quality_flag, lon, lat)
    with lon/lat as WGS 84 decimal degrees. Geometry is built server-side with
    ST_SetSRID(ST_MakePoint(lon, lat), 4326) via the target table's generated column.

    Strategy:
        1. COPY raw rows into an unlogged, index-free staging table (fast path).
        2. INSERT ... SELECT into the target, deduplicating on the natural key.
        3. TRUNCATE staging.

    Returns the number of rows written to staging. Time: O(n) per batch.
    """
    # Serialize rows to an in-memory CSV buffer for copy_expert.
    buffer = io.StringIO()
    writer = csv.writer(buffer)
    n = 0
    for row in rows:
        writer.writerow(row)
        n += 1
    buffer.seek(0)

    copy_sql = (
        f"COPY sensor_readings_staging ({', '.join(COLUMNS)}) "
        "FROM STDIN WITH (FORMAT csv)"
    )

    with psycopg2.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute(DDL_STAGING)
        cur.execute("TRUNCATE sensor_readings_staging")   # start clean
        cur.copy_expert(copy_sql, buffer)                 # fast bulk write
        cur.execute(UPSERT)                               # geometry + dedupe
        cur.execute("TRUNCATE sensor_readings_staging")   # release space
        conn.commit()

    return n

The target’s geom column is a GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(lon, lat), 4326)) STORED column, so the INSERT ... SELECT never names it — the point materializes automatically and can never disagree with the raw coordinates. If your schema does not use a generated column, add ST_SetSRID(ST_MakePoint(lon, lat), 4326) explicitly to the SELECT and the target column list.

For a full-partition load, drop or skip the GiST index during the upsert and build it once afterward; maintaining it per row erases most of the COPY advantage.

Parameter Tuning Guide

Batch size and session settings should scale with sensor throughput. Larger batches amortize round-trip and index-build cost but hold more memory and lengthen the transaction; the sweet spot rises with row volume.

Sensor throughput	Rows per batch	`COPY` buffer	Staging	Index strategy	Session tuning
Low (weather, < 10k rows/run)	5,000	in-memory `StringIO`	unlogged	keep GiST live	defaults
Medium (air quality, ~100k rows/run)	50,000	in-memory `StringIO`	unlogged	keep GiST live	`synchronous_commit=off`
High (dense network, ~1M rows/run)	100,000–250,000	streamed file object	unlogged	build GiST after load	`maintenance_work_mem=1GB`
Backfill (historical, 10M+ rows)	500,000	streamed file object	unlogged, per-partition	build GiST + BRIN after load	`max_wal_size` raised, `autovacuum` paused

maintenance_work_mem governs how fast the post-load GiST build runs; raising it to 512 MB–1 GB for the session shortens index creation on large partitions substantially.

synchronous_commit=off at the session level lets the upsert commit without waiting for WAL flush, safe for re-derivable telemetry because a lost transaction can simply be replayed from the source batch.

Backfills should load one monthly partition at a time in timestamp order so the target’s BRIN index on observed_at stays tightly correlated — see the GiST versus BRIN index decision guide for why out-of-order backfills wreck BRIN pruning.

Verification and Testing

The test below stands up the staging and target tables, loads a batch with a deliberate duplicate, and asserts that the row is upserted rather than duplicated and that the geometry was constructed with the correct SRID. It assumes a test PostGIS database reachable via TEST_DSN.

# python 3.11 · psycopg2-binary==2.9.9
import os
import pytest
import psycopg2


TEST_DSN = os.environ.get("TEST_DSN", "dbname=eiot_test")


def test_bulk_load_is_idempotent_and_builds_geometry():
    rows = [
        ("st-01", "2026-07-01T00:00:00Z", "pm25", 12.4, 0, -122.42, 37.77),
        ("st-02", "2026-07-01T00:00:00Z", "pm25", 9.1, 0, -122.27, 37.80),
        # Duplicate natural key with a corrected value — must update, not duplicate.
        ("st-01", "2026-07-01T00:00:00Z", "pm25", 13.0, 1, -122.42, 37.77),
    ]

    bulk_load_readings(TEST_DSN, rows)

    with psycopg2.connect(TEST_DSN) as conn, conn.cursor() as cur:
        # Exactly two logical observations survive the duplicate.
        cur.execute("SELECT count(*) FROM sensor_readings")
        assert cur.fetchone()[0] == 2

        # The duplicate resolved to the later value and flag.
        cur.execute("""
            SELECT value, quality_flag
            FROM sensor_readings
            WHERE station_id = 'st-01' AND metric = 'pm25'
        """)
        value, flag = cur.fetchone()
        assert value == pytest.approx(13.0)
        assert flag == 1

        # Geometry was built with SRID 4326 from the raw coordinates.
        cur.execute("""
            SELECT ST_SRID(geom), ST_X(geom), ST_Y(geom)
            FROM sensor_readings
            WHERE station_id = 'st-02'
        """)
        srid, x, y = cur.fetchone()
        assert srid == 4326
        assert x == pytest.approx(-122.27)
        assert y == pytest.approx(37.80)

For a throughput reference rather than a correctness check, load a synthetic million-row batch and compare wall-clock time against a row-by-row execute loop. On commodity hardware the COPY-plus-staging path typically completes in single-digit seconds where the loop takes minutes.

Gotchas

copy_expert needs an unclosed, rewound buffer. After writing rows to a StringIO, call buffer.seek(0) before copy_expert, or PostgreSQL receives an empty stream and silently loads zero rows. For batches too large to hold in memory, pass a real file object or a streaming generator wrapper instead.

CSV quoting corrupts free-text station IDs. If station_id or metric can contain commas, quotes, or newlines, the csv.writer defaults handle quoting, but a hand-built COPY string will not. Always serialize through csv.writer and load with FORMAT csv, never with a manual tab-join, which breaks on embedded delimiters.

Null coordinates reach the generated geometry. A row with null lon or lat produces a null point, which NOT NULL on lon/lat rejects at insert time. Filter or quarantine such rows before staging; do not rely on the geometry column to catch them, because a null point is a valid geometry value.

Timezone-naive timestamps drift on load. COPY into a timestamptz column interprets naive strings in the server’s TimeZone setting, so a naive 2026-07-01 00:00:00 can land an hour off. Emit explicit UTC offsets (...Z or +00:00) in the source rows so the parse is unambiguous.

PostGIS Storage & Spatial Indexing for Sensor Networks — the schema, partitioning, and index design this loader targets
Choosing GiST vs BRIN Indexes for Time-Series Sensor Geometry — which index to build after the bulk load, and why load order matters for BRIN
How to Sync MQTT Sensor Data to PostGIS with Python — the streaming ingestion source that produces the batches this function loads

Bulk-Loading Sensor Readings into PostGIS with COPY and ST_MakePoint #

Why COPY plus Staging Beats Direct Inserts #

Production-Ready Implementation #

Parameter Tuning Guide #

Verification and Testing #

Gotchas #

Related #