Tuning insertmanyvalues batches and RETURNING for bulk inserts

Pass a list of dictionaries to session.execute(insert(Model), rows) and SQLAlchemy batches them into multi-row INSERT statements, adding RETURNING when generated keys are needed — then tune insertmanyvalues_page_size against asyncpg's 32,767-parameter limit, which for wide rows is the binding constraint. This guide belongs to high-performance bulk inserts and updates.

Quick Answer

The ORM's unit of work runs per object. For rows that need no per-object Python, a batched insert is several times faster.

Before — objects through the unit of work:

from shop.models import PageView


async def record_views(session, rows: list[dict]) -> None:
    session.add_all(PageView(**row) for row in rows)
    await session.commit()
# 100,000 objects: 100,000 instances constructed, events fired, and the
# identity map populated — before a single row is written.

After — a batched insert, with and without keys:

from sqlalchemy import insert

from shop.models import PageView


async def record_views(session, rows: list[dict]) -> None:
    """Fastest path: nothing comes back."""
    await session.execute(insert(PageView), rows)
    await session.commit()


async def record_views_returning_ids(session, rows: list[dict]) -> list[int]:
    """Batched, with the generated keys returned in input order."""
    result = await session.scalars(insert(PageView).returning(PageView.id), rows)
    ids = list(result)
    await session.commit()
    return ids

And the tuning knob, set once on the engine:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://shop:secret@db/shop",
    # SQLAlchemy's batch size for multi-row INSERT statements. The default (1,000)
    # is a reasonable starting point; raise it for narrow rows, lower it for wide ones.
    insertmanyvalues_page_size=1_000,
)

Both forms run inside the session's transaction, so a batched insert and an ORM flush can commit together.

Execution Context & Async Workflow Integration

SQLAlchemy 2.0 introduced insertmanyvalues: when an insert() is executed with a list of parameter dictionaries, the statement is rewritten into multi-row INSERT ... VALUES (...), (...), (...) batches rather than executed once per row. That alone removes most of the round trips.

The harder problem it solves is generated keys. Before 2.0, getting the primary keys of many inserted rows meant one statement per row, because a plain executemany returns nothing usable. On backends with RETURNING — PostgreSQL among them — SQLAlchemy appends RETURNING to each batch, so the keys come back with the rows, and it maintains the correspondence between input dictionaries and returned keys. That is what makes add_all and bulk inserts with keys viable at scale on PostgreSQL, and why the same code is much slower on MySQL, which has no RETURNING — the point made in choosing between aiomysql and asyncmy for async MySQL.

Two limits bound the batch size, and the smaller one wins.

insertmanyvalues_page_size is SQLAlchemy's own, defaulting to 1,000 rows per statement. It can be set per engine, as above, or per statement with .execution_options(insertmanyvalues_page_size=500).

The driver's parameter limit is the one people hit without realising. asyncpg allows 32,767 bound parameters per statement, and a multi-row insert uses one parameter per column per row. A ten-column row therefore caps out at about 3,200 rows per statement regardless of the page size; a fifty-column row at about 650. SQLAlchemy accounts for this automatically, but it means raising the page size beyond that has no effect for wide rows.

Skipping RETURNING is the other significant lever. When the keys are not needed — event logs, metrics, append-only tables — omitting it removes the return traffic and the matching work:

# No keys needed: the fastest batched path.
await session.execute(insert(PageView), rows)

Under async, all of this is one await, and the batching happens inside SQLAlchemy — there is no partial-progress callback and no way to observe individual batches except through the engine's logging or an event listener. A very large list therefore executes as a single logical operation that takes a while, which is a reason to chunk in the application for anything where progress matters, as processing large tables in batches with partitions describes.

Resolving Warnings, Errors & Common Mistakes

The heterogeneous-keys point is worth understanding because it silently costs performance. SQLAlchemy compiles a statement from the keys of the first dictionary and expects the rest to match; when they do not, the execution is split into groups per key set, which can turn one batched insert into many. Normalising first keeps it to one statement shape:

COLUMNS = ("viewed_at", "customer_id", "path", "duration_ms")


def normalised(rows: list[dict]) -> list[dict]:
    return [{column: row.get(column) for column in COLUMNS} for row in rows]

Failure handling is the other decision a bulk load needs. One bad row aborts the statement and the transaction, which for a hundred thousand rows is an expensive way to discover a duplicate. Two options, depending on what a conflict means:

from sqlalchemy.dialects.postgresql import insert as pg_insert

from shop.models import PageView

# Skip duplicates, insert the rest, in one statement.
stmt = pg_insert(PageView).on_conflict_do_nothing(index_elements=[PageView.event_id])
await session.execute(stmt, rows)

ON CONFLICT DO NOTHING handles unique violations without aborting anything, and is almost always the right answer for idempotent loads — the mechanics are in bulk upserting rows with INSERT ON CONFLICT. For failures the database cannot resolve — a check constraint, a foreign key to a missing row — a savepoint per batch keeps the good batches, as described in using savepoints with begin_nested().

Advanced: Measuring and Choosing a Page Size

The default page size is a reasonable guess, and the right value depends on row width, network latency and how much the planner charges for a large statement. Measuring it takes a few minutes and is worth doing once per significant load path.

import asyncio
import statistics
import time

from sqlalchemy import delete, insert

from shop.db import engine
from shop.models import PageView


async def time_page_size(rows: list[dict], page_size: int, repeats: int = 3) -> float:
    timings = []
    for _ in range(repeats):
        async with engine.begin() as conn:
            await conn.execute(delete(PageView))
        async with engine.begin() as conn:
            started = time.perf_counter()
            await conn.execute(
                insert(PageView).execution_options(insertmanyvalues_page_size=page_size),
                rows,
            )
            timings.append(time.perf_counter() - started)
    return statistics.median(timings)


async def sweep(rows: list[dict]) -> None:
    for page_size in (100, 250, 500, 1_000, 2_000, 5_000):
        seconds = await time_page_size(rows, page_size)
        print(f"page_size={page_size:>5}  {seconds:.3f}s  "
              f"{len(rows) / seconds:,.0f} rows/s")

The shape to expect is a curve that improves steeply up to a few hundred rows, flattens, and then either plateaus or degrades slightly as statements get large enough to cost real parse time. Choose a value on the flat part rather than the exact minimum, because the minimum moves with row width and load.

Two refinements matter for wide rows. Compute the effective cap so the configured page size is not silently ignored:

from shop.models import PageView

PARAMETER_LIMIT = 32_767          # asyncpg
columns = len(PageView.__table__.columns)
max_rows_per_statement = PARAMETER_LIMIT // columns
print(f"{columns} columns → at most {max_rows_per_statement} rows per statement")

And consider whether RETURNING is needed at all. For an append-only table it rarely is, and dropping it is often a larger win than any page-size change — the numbers in the chart above put it ahead of a doubled batch size.

Past a few hundred thousand rows per load, the question stops being page size and becomes which mechanism to use. COPY through the driver is substantially faster than any INSERT path, because it has no per-statement protocol overhead at all, and a staging table plus a set-based merge handles the upsert case — both covered in loading rows with Postgres COPY through asyncpg. The batched insert() occupies a useful middle ground: much faster than the ORM, far simpler than COPY, and still returning keys when they are needed.

Keeping ORM Behaviour Where It Is Needed

A batched insert skips the unit of work, which is the source of both its speed and its hazards. Four ORM behaviours do not happen, and each has a replacement.

Python-side defaults on the model. A Core-style insert applies column defaults defined with default=, because those are part of the table metadata — but not @validates methods, not attribute events, and not anything computed in __init__. Where a value must always be set, a server_default moves the rule into the database and makes it apply to every writer, including COPY:

import datetime as dt

from sqlalchemy import DateTime, func
from sqlalchemy.orm import Mapped, mapped_column


class PageView(Base):
    __tablename__ = "page_views"

    id: Mapped[int] = mapped_column(primary_key=True)
    viewed_at: Mapped[dt.datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

Relationship cascades. Inserting a parent and its children as two batched inserts means the children need the parent's keys, which is exactly what RETURNING provides — insert the parents, read back the ids, then build the child dictionaries. For a nested structure that is a loop per level rather than a single call, and for one parent with many children, a data-modifying CTE does it in one statement.

Mapper events. before_insert and after_insert do not fire. If they maintain an audit table or a search index, do that work in bulk instead — one insert into the audit table from the same rows, which is faster than per-row events anyway.

The identity map. Nothing inserted this way is in the session, so a subsequent query re-reads it. That is usually desirable for a bulk load and surprising when the same request then tries to use the objects.

The practical arrangement in most codebases is to keep both paths and be explicit about which is which: the ORM for ordinary application writes, where per-object behaviour is the point, and a small number of clearly named bulk functions for loads. Mixing them inside one function — some objects added, some rows inserted — is what makes behaviour hard to reason about later.

# shop/bulk.py — the bulk paths live together, and their limitations are documented here.
from sqlalchemy import insert

from shop.models import PageView


async def bulk_insert_page_views(session, rows: list[dict]) -> None:
    """Batched insert. No ORM events, no cascades, nothing added to the identity map.

    Callers must supply every column that has no server default.
    """
    await session.execute(insert(PageView), rows)

Frequently Asked Questions

Related

• High-Performance Bulk Inserts and Updates — The parent guide: choosing a bulk write path.
• Benchmarking Core executemany bulk insert performance — Measuring these paths against each other.
• Loading rows with Postgres COPY through asyncpg — The faster mechanism when nothing needs returning.
• Bulk upserting rows with INSERT ON CONFLICT — Handling conflicts without aborting the load.