• 8 min read
  • 1 code sample

asyncpg bulk COPY vs executemany benchmarks

Inserting a million parsed customs-document rows through asyncpg’s executemany can take minutes, while the same rows through copy_records_to_table land in seconds — but only if you handle type coercion correctly, because COPY binds values differently than the parameterized path and will reject a str where executemany quietly accepted it. This page benchmarks the two insert methods head to head for high-volume ingestion of parsed invoice and manifest lines: throughput in rows per second, peak resident memory, and the coercion rules copy_records_to_table enforces on Decimal, date, and nullable columns. It ends with a batch-sizing rule so you can size the COPY chunk to your worker rather than guessing. This is a comparison of two methods within one driver, not a driver-versus-driver shootout.

Prerequisites

This benchmark assumes a specific database and record shape. Confirm each before running it:

  • PostgreSQL 13+ reachable over asyncpg 0.27+. copy_records_to_table and its records iterable protocol are stable in these versions; older builds differ in how they stream and in error reporting.
  • Rows already parsed into native Python types. The document parsers upstream must emit Decimal for money and rates, datetime.date for dates, and None for nulls — not strings. COPY in binary mode binds by type, so a stray "12.50" string into a numeric column raises where the parameterized path silently cast it. The queue that feeds these rows is described in Implementing async queues for bulk customs docs.
  • A target table with a known column order. copy_records_to_table matches tuple position to the columns= list, not by name. An off-by-one in that list silently writes the entered value into the weight column. Pin the order once and share it between the parser and the loader.
  • A benchmark table you can TRUNCATE between runs. Never benchmark into a table with live indexes and triggers you cannot reset; a leftover unique index turns run two into a constraint-violation profile, not a throughput profile.
  • Structured logging configured (stdlib logging with the %(asctime)s | %(levelname)s | %(name)s | %(message)s format, or structlog) so each run’s rows-per-second and byte count are recorded for later comparison.

Implementation

The harness generates a deterministic batch of parsed customs-document rows, then times both insert paths against the same rows and the same connection settings. executemany sends one parameterized INSERT per row over the extended-query protocol; copy_records_to_table streams the whole batch through PostgreSQL’s COPY machinery in one pass.

import asyncio
import logging
import time
from dataclasses import dataclass
from datetime import date
from decimal import Decimal
from typing import Iterable, Optional, Sequence

import asyncpg

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s",
    handlers=[logging.StreamHandler()],
)
logger = logging.getLogger("asyncpg_copy_bench")

COLUMNS: tuple[str, ...] = (
    "doc_id", "line_no", "hts_code", "description",
    "quantity", "unit_price", "entered_value", "entry_date",
)

DDL = """
CREATE TABLE IF NOT EXISTS parsed_doc_line (
    doc_id        text        NOT NULL,
    line_no       integer     NOT NULL,
    hts_code      text,
    description   text,
    quantity      integer     NOT NULL,
    unit_price    numeric(14,4) NOT NULL,
    entered_value numeric(14,2) NOT NULL,
    entry_date    date        NOT NULL
);
"""


@dataclass(frozen=True)
class DocLine:
    doc_id: str
    line_no: int
    hts_code: Optional[str]
    description: str
    quantity: int
    unit_price: Decimal          # Decimal, never float — numeric(14,4)
    entered_value: Decimal
    entry_date: date

    def as_record(self) -> tuple:
        # Position must match COLUMNS exactly; COPY binds by position.
        return (
            self.doc_id, self.line_no, self.hts_code, self.description,
            self.quantity, self.unit_price, self.entered_value, self.entry_date,
        )


@dataclass(frozen=True)
class BenchResult:
    method: str
    rows: int
    wall_seconds: Decimal
    rows_per_sec: Decimal


def make_batch(n: int) -> list[DocLine]:
    """Deterministic parsed rows with native types — the COPY-ready contract."""
    unit = Decimal("12.5000")
    return [
        DocLine(
            doc_id=f"DOC-{i // 20:06d}",
            line_no=i % 20,
            hts_code=None if i % 97 == 0 else "8517620000",
            description=f"line item {i}",
            quantity=i % 50 + 1,
            unit_price=unit,
            entered_value=(unit * (i % 50 + 1)).quantize(Decimal("0.01")),
            entry_date=date(2026, 7, 16),
        )
        for i in range(n)
    ]


async def bench_executemany(
    conn: asyncpg.Connection, rows: Sequence[DocLine]
) -> BenchResult:
    stmt = (
        "INSERT INTO parsed_doc_line "
        "(doc_id, line_no, hts_code, description, quantity, "
        " unit_price, entered_value, entry_date) "
        "VALUES ($1,$2,$3,$4,$5,$6,$7,$8)"
    )
    payload = [r.as_record() for r in rows]
    await conn.execute("TRUNCATE parsed_doc_line")
    start = time.perf_counter()
    await conn.executemany(stmt, payload)     # one bind+exec per row
    return _result("executemany", len(rows), start)


async def bench_copy(
    conn: asyncpg.Connection, rows: Sequence[DocLine], *, batch: int = 50_000
) -> BenchResult:
    await conn.execute("TRUNCATE parsed_doc_line")
    start = time.perf_counter()
    for chunk in _chunked(rows, batch):
        await conn.copy_records_to_table(
            "parsed_doc_line",
            records=(r.as_record() for r in chunk),   # streamed, not listed
            columns=list(COLUMNS),
        )
    return _result("copy_records", len(rows), start)


def _chunked(rows: Sequence[DocLine], size: int) -> Iterable[Sequence[DocLine]]:
    for i in range(0, len(rows), size):
        yield rows[i : i + size]


def _result(method: str, n: int, start: float) -> BenchResult:
    wall = Decimal(str(time.perf_counter() - start)).quantize(Decimal("0.001"))
    rps = (Decimal(n) / wall).quantize(Decimal("1")) if wall else Decimal(0)
    res = BenchResult(method, n, wall, rps)
    logger.info(
        "method=%-13s rows=%d wall=%ss rows_per_sec=%s",
        res.method, res.rows, res.wall_seconds, res.rows_per_sec,
    )
    return res


async def main(dsn: str, n: int = 500_000) -> None:
    conn = await asyncpg.connect(dsn)
    try:
        await conn.execute(DDL)
        rows = make_batch(n)
        em = await bench_executemany(conn, rows)
        cp = await bench_copy(conn, rows)
        speedup = (cp.rows_per_sec / em.rows_per_sec).quantize(Decimal("0.1"))
        logger.info("copy_records is %sx executemany throughput", speedup)
    finally:
        await conn.close()


if __name__ == "__main__":
    import os
    asyncio.run(main(os.environ["CUSTOMS_DSN"]))

copy_records_to_table accepts a generator for records, so it never materializes a second copy of the batch the way the executemany payload list does. That single difference is why COPY wins on memory as well as throughput — it streams tuples straight into the wire protocol.

Throughput of executemany versus copy_records_to_table for parsed document rows A horizontal bar comparison of insert throughput in rows per second for the same batch of parsed customs-document rows. The top bar, labelled executemany, is short: it sends one parameterized insert per row over the extended-query protocol, so each row pays a bind-and-execute round-trip cost and throughput is low. The bottom bar, labelled copy_records_to_table, is many times longer: it streams the whole batch through PostgreSQL's COPY machinery in a single pass with type-coerced binary binding, reaching far higher rows per second. A bracket spans the difference between the two bar ends and is annotated as the COPY speedup. A side note lists the trade the longer bar demands: rows must arrive as native Decimal, date, and None values, and tuple position must match the column list, because COPY binds by type and position rather than casting strings like the parameterized path does. 0 rows / second → high executemany one bind+exec per row copy_records single streamed COPY pass COPY speedup COPY demands the contract: native Decimal / date / None · tuple position = column order · binds by type, not by cast
copy_records_to_table streams the batch in one pass and far outpaces per-row executemany — provided every value arrives as a native Decimal, date, or None in the exact column order.

Verification steps

Run these checks before adopting a method or a batch size in production:

  1. Row-count parity. After each method, SELECT count(*) FROM parsed_doc_line must equal n. A COPY that silently dropped a chunk on a swallowed exception will show a shortfall here, not an error at the call site.
  2. Confirm the throughput gap is real, not warm-cache noise. Run each method three times, discard the first, and compare medians. If copy_records is not several times faster, check that executemany is not benefiting from a prepared-statement cache that would not exist on a cold connection pool.
  3. Type-coercion negative test. Feed one row with unit_price="12.50" as a string. executemany may cast it; copy_records_to_table must raise. If COPY accepts the string silently, you are on a text-mode fallback that defeats binary binding — investigate before trusting any number it wrote.
  4. Null handling. Confirm rows with hts_code=None land as SQL NULL, not the literal text None. Then confirm a NULL in the NOT NULL entered_value column is rejected by both methods identically. A gap here means the parser is emitting placeholders that only one path catches.
  5. Column-order guard. Deliberately swap two entries in the columns= list and confirm the resulting data is visibly wrong (entered value in the quantity column). This proves your schema tests would catch a real position drift; then restore the order.
  6. Peak-memory comparison. Sample resident-set size during each run. The executemany payload list holds a full second copy of the batch; the COPY generator should show a materially lower peak. If they match, something is materializing the COPY generator into a list before the call.
  7. Batch-size sweep. Run bench_copy at 10k, 50k, and 100k row chunks. Throughput should rise then plateau while memory rises linearly; pick the smallest chunk on the plateau so a retry re-sends less work.

Edge cases & gotchas

  • COPY binds by position, not by name. copy_records_to_table maps tuple index to the columns= list. There is no field-name safety net, so a parser change that reorders its output tuple corrupts every row without an error. Build the record tuple from a single shared column definition, and assert its length equals len(COLUMNS) before the call.
  • String-typed numbers are the classic COPY failure. The parameterized path forgives "12.50" into a numeric column; binary COPY does not. Coerce to Decimal at parse time, never at load time, so the same rows that pass executemany do not surprise you when you switch to COPY.
  • executemany is not one round trip. It is convenient but still binds and executes per row under the hood, so it does not approach COPY throughput no matter the batch size. Reach for it only for small batches or where you need per-row RETURNING, which COPY cannot give you.
  • COPY bypasses row triggers and RETURNING. If your table depends on BEFORE INSERT triggers to fill columns, or you need generated ids back, COPY will not run them. Move that logic into the parser or a post-load UPDATE, or the switch to COPY silently changes semantics.
  • A single bad row fails the whole COPY chunk. COPY is all-or-nothing per call. One un-coercible value rolls back the entire chunk, so validate rows before the call and route rejects to a dead-letter path rather than losing a 50k batch to one malformed line — the same resilience posture used by Async Batch Processing for High Volume.
  • Batch size trades throughput against retry cost. Bigger chunks amortize protocol overhead but make a failed chunk more expensive to re-send and hold a larger transaction lock. Size the chunk to leave worker-memory headroom and to keep a retry cheap, and treat that size as part of the ingestion contract so a re-run splits the work identically.
  • Idempotency lives above the insert. Neither method deduplicates. A retried batch double-inserts unless the table carries a unique key and you COPY into a staging table then upsert, or you make the whole batch a single transaction that a retry can safely repeat. Decide this before benchmarking, because a unique index changes the throughput numbers.

Up: Async Batch Processing for High Volume