In SQLAlchemy 2.1, why is Session.add_all() slower than an ORM bulk insert for 200,000 Product rows?
answer
- objects carry bookkeeping
- flush walks the unit of work
- every new key read back
- dicts skip the identity map
basics
~20 sadd_all() builds and tracks an instrumented Product per row; flush then sorts the unit of work, fires per-object events and reads every generated key back, with RETURNING where supported. An ORM bulk insert sends plain dicts in executemany batches and skips all of that.
solid answer
~40 sWith `add_all()`, every row becomes an instrumented `Product`: each attribute set goes through attribute events and any `@validates`, and the Session holds all 200,000 pending objects strongly until flush. At flush the unit of work orders the work across mappers, runs cascades and `before_insert`/`after_insert` events, and must put each new primary key back on its object, so on backends with RETURNING it emits `INSERT ... RETURNING id` with ordered results; SQLite runs that one row per statement, PostgreSQL batches it. Then every object gets an identity key and its state committed. `session.execute(insert(Product), rows)` sends the dicts as executemany batches and asks nothing back. In 2.1 the legacy `bulk_save_objects()` and `bulk_insert_mappings()` still exist, but the documented form is `session.execute(insert(...), dicts)`.
code
python · 27 linesfrom decimal import Decimal
from sqlalchemy import Numeric, create_engine, insert
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
class Product(Base):
__tablename__ = "product"
id: Mapped[int] = mapped_column(primary_key=True)
sku: Mapped[str] = mapped_column(unique=True)
price: Mapped[Decimal] = mapped_column(Numeric(10, 2))
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
rows = [{"sku": f"SKU-{n}", "price": Decimal("5.00")} for n in range(3)]
engine.echo = True
with Session(engine) as session, session.begin():
session.add_all([Product(**row) for row in rows]) # unit of work
with Session(engine) as session, session.begin():
session.execute(insert(Product), [{**r, "sku": r["sku"] + "b"} for r in rows])go deeper
Recall that add_all() takes objects and a bulk insert takes dicts through session.execute(insert(Model), rows).
Explain the per-object work: instrumentation, pending tracking, flush planning, events and reading every generated key back onto its object.
Show you read the echo log to see whether a flush is batching, and chunk large add_all() imports to bound memory.
Weigh a team rule for large imports: the speed of dicts against the validators, events and cascades that only the object path enforces.
## What add_all() costs per row `Session.add_all(objects)` is the **unit-of-work** path. For 200,000 feed rows it does all of this in Python before and during the flush: - **Builds instrumented objects.** `Product(**row)` runs the mapped constructor; every attribute assignment goes through SQLAlchemy's instrumentation, attribute events and any `@validates` method. - **Tracks each object.** The Session registers every object as **pending** and keeps a strong reference to it until the flush; normally loaded objects are only weakly referenced. - **Plans the flush.** The unit of work sorts the work by mapper dependencies, follows relationship cascades and fills foreign keys from parent objects. - **Fires mapper events** such as `before_insert` and `after_insert` per object. - **Reads back generated keys.** Each new object must learn its primary key so it can join the identity map. - **Commits object state** afterwards: identity keys assigned, attribute history reset, and on commit, with the default `expire_on_commit=True`, every object expired. ## What the flush actually sends Because the ORM must match each returned key to the right object, on a backend with RETURNING it asks for **ordered RETURNING**. On SQLite with server-generated keys that degrades to one statement per row: ``` INSERT INTO product (sku, price) VALUES (?, ?) RETURNING id [generated in 0.00005s (insertmanyvalues) 1/3 (ordered; batch not supported)] ('SKU-0', 5.0) INSERT INTO product (sku, price) VALUES (?, ?) RETURNING id [insertmanyvalues 2/3 (ordered; batch not supported)] ('SKU-1', 5.0) ``` On PostgreSQL the insertmanyvalues feature batches those rows into multi-row INSERTs and sorts the returned keys. That is why SQLAlchemy's performance FAQ says that since 2.0 the included drivers other than MySQL's give very fast ORM inserts: on such a backend the remaining gap is mostly the **per-object Python work**, not round trips. ## What the bulk path sends ``` INSERT INTO product (sku, price) VALUES (?, ?) [generated in 0.00004s] [('SKU-0b', 5.0), ('SKU-1b', 5.0), ('SKU-2b', 5.0)] ``` `session.execute(insert(Product), rows)` builds no objects, plans no flush, fires no mapper events and asks for no keys unless you add `.returning()`. On a quick SQLite run of 200,000 rows the bulk insert finished several times faster than `add_all()`; measure on your own database before quoting a ratio. ## Memory as well as time The cost is not only CPU. Pending objects are **strongly referenced** by the Session until they are flushed, so `add_all()` of 200,000 products keeps all 200,000 instrumented objects, plus their state tracking, alive at once. After the flush they are weakly referenced again, but your own list still holds them, and a commit with `expire_on_commit=True` expires every one, so touching an attribute afterwards emits a SELECT per object. The bulk path holds only your list of dicts, and SQLAlchemy keeps nothing once the statement returns. ## Measuring before you switch 1. Turn on `echo=True` and confirm what the flush sends: per-row INSERTs, or batched ones marked `insertmanyvalues 1/N`. 2. Time both paths against the real database with a realistic batch; SQLite numbers do not predict PostgreSQL ones. 3. Check what the objects were doing for you, such as cascades, validators and events, before you trade them away. ## Legacy bulk methods and their 2.1 forms | Legacy (still present, marked legacy) | 2.1 form | |---|---| | `session.bulk_insert_mappings(Product, rows)` | `session.execute(insert(Product), rows)` | | `session.bulk_update_mappings(Product, rows)` | `session.execute(update(Product), rows)` | | `session.bulk_save_objects(objects)` | `add_all()` for objects, or the dict forms above | `bulk_save_objects()` takes objects but persists them without attaching them to the Session afterwards, offers no RETURNING and no session synchronization, and its `return_defaults=True` falls back to one INSERT per row. Its own docstring recommends `add_all()` for objects. ## Choosing between them 1. Rows are flat and their foreign keys are known: use the **bulk insert**. 2. Rows form a graph, such as a new supplier with its products, and the ORM should fill `supplier_id`: use **`add_all()`**, flushing or committing in chunks of a few thousand so pending objects do not pile up. 3. Invariants live in `@validates` or mapper events: either keep `add_all()` or move those rules into the dicts, column defaults or a `do_orm_execute` hook. 4. You need the new objects afterwards: `add_all()`, or a bulk insert with `.returning(Product)`.
- What does `Session.bulk_save_objects()` still do in SQLAlchemy 2.1, and what replaced it?It is still present but marked legacy since 2.0. It persists the given objects without attaching them to the Session afterwards, offers no RETURNING or session synchronization, and with `return_defaults=True` inserts rows that need generated keys one at a time. For flat rows use `session.execute(insert(Model), dicts)`; for objects its docstring points back to `add_all()`.
- When is `add_all()` still the right call for a large SQLAlchemy import?When rows form a graph and the ORM must fill foreign keys from parents it just inserted, when validators or mapper events must run, or when the code needs the objects afterwards. Commit in chunks of a few thousand objects and drop references to finished ones, so the Session never holds the whole import as pending objects.
- add_all() can never batch its INSERTs, so it always sends one per object.
- bulk_save_objects() is the recommended fast path in 2.1.
- Bulk insert is faster because it skips the transaction.
- add_all() accepts plain dicts as well as mapped objects.
- The bulk path also fills each object's generated primary key.
Assumes SQLAlchemy 2.1; since 2.0 the insertmanyvalues feature batches unit-of-work INSERTs, and bulk_save_objects() and bulk_insert_mappings() are legacy.