Deleting a SQLAlchemy Post with 200,000 comments through Session.delete() takes minutes and huge memory; why, and how do passive_deletes and WriteOnlyMapped fix it?
answer
- the ORM must know every child
- a SELECT before the DELETEs
- let the foreign key do it
- a collection that refuses to load
basics
~20 sThe ORM SELECTs every unloaded comment so it can delete or null each one. Add ondelete="CASCADE" to the foreign key and passive_deletes=True to the relationship, and declare it WriteOnlyMapped so the collection is never loaded.
solid answer
~40 sWhatever the cascade, `Session.delete(post)` must delete or null every child, so SQLAlchemy first SELECTs all unloaded comments, builds 200,000 objects and sends a DELETE per row in one executemany batch. With `ForeignKey("posts.id", ondelete="CASCADE")` in the schema and `passive_deletes=True` on `Post.comments`, the ORM deletes only comments already in the Session, emits `DELETE FROM posts`, and the database removes the rest. That needs the constraint to exist, so a migration for an existing table, and enforced foreign keys (SQLite needs `PRAGMA foreign_keys=ON`). `WriteOnlyMapped[Comment]` stops the collection loading anywhere: you `add()`, `remove()` and page with `post.comments.select()`, iteration raises, and deleting the parent without `passive_deletes=True` raises `InvalidRequestError` instead of loading quietly.
code
python · 67 linesfrom __future__ import annotations
from sqlalchemy import ForeignKey, String, create_engine, event, func, select
from sqlalchemy.orm import (
DeclarativeBase,
Mapped,
Session,
WriteOnlyMapped,
mapped_column,
relationship,
)
class Base(DeclarativeBase):
pass
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(200))
comments: WriteOnlyMapped[Comment] = relationship(
back_populates="post",
cascade="all, delete-orphan",
passive_deletes=True,
order_by="Comment.id",
)
class Comment(Base):
__tablename__ = "comments"
id: Mapped[int] = mapped_column(primary_key=True)
body: Mapped[str] = mapped_column(String(500))
post_id: Mapped[int] = mapped_column(ForeignKey("posts.id", ondelete="CASCADE"))
post: Mapped[Post] = relationship(back_populates="comments")
engine = create_engine("sqlite://")
@event.listens_for(engine, "connect")
def _fk_on(dbapi_connection, connection_record):
dbapi_connection.execute("PRAGMA foreign_keys=ON") # SQLite enforces FKs only when asked
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
post = Post(title="Launch notes")
post.comments.add_all(Comment(body=f"comment {i}") for i in range(5000))
session.add(post)
with Session(engine) as session:
post = session.scalars(select(Post)).one()
# page through the collection instead of loading it
first_page = session.scalars(post.comments.select().limit(3)).all()
print([c.body for c in first_page])
statements = []
event.listen(engine, "before_cursor_execute", lambda *a: statements.append(a[2]))
with Session(engine) as session, session.begin():
post = session.scalars(select(Post)).one()
session.delete(post) # no SELECT of the 5,000 comments
print(statements)
with Session(engine) as session:
print(session.scalar(select(func.count()).select_from(Comment)))go deeper
Recall that deleting a parent makes SQLAlchemy load its children first, and that passive_deletes=True with ON DELETE CASCADE lets the database delete them instead.
Explain the statements the flush emits with and without passive_deletes, and what a WriteOnlyCollection offers in place of a loaded list.
Diagnose the slow delete from the SQL log, then verify both halves of the fix: the constraint in the live schema and foreign key enforcement on every connection.
Decide which collections are unbounded and must be write-only, so big children are paged and deleted by the database rather than discovered in production.
## What the unit of work does by default `Session.delete(post)` does not send a single DELETE and stop. Whatever the cascade, the ORM has to know every child that refers to the post, because it must either delete each one (delete cascade) or null its foreign key (the default). With a plain `Mapped[list[Comment]]` collection and nothing else configured, SQLAlchemy runs: 1. `SELECT ... FROM comments WHERE ? = comments.post_id`, loading **every** comment of the post that is not already loaded. With delete cascade this happens inside the `Session.delete()` call; without it, at flush. 2. One ORM object per row, each registered in the `Session`'s identity map. 3. `DELETE FROM comments WHERE comments.id = ?` for each comment, sent as one executemany batch (or `UPDATE ... SET post_id=NULL` without delete cascade). 4. `DELETE FROM posts WHERE posts.id = ?`. For 200,000 comments, steps 1 to 3 mean 200,000 rows over the wire, 200,000 Python objects held until commit, and a parameter set per row. That is the minutes and the memory. ## passive_deletes=True hands the children to the database The fix is to let the database do what it is good at, through an `ON DELETE CASCADE` foreign key, and tell the ORM to trust it: ```python comments: Mapped[list[Comment]] = relationship( back_populates="post", cascade="all, delete-orphan", passive_deletes=True ) post_id: Mapped[int] = mapped_column(ForeignKey("posts.id", ondelete="CASCADE")) ``` With `passive_deletes=True`, the ORM deletes only comments **already loaded** in the `Session` and emits no SELECT for the rest. The DELETE of the post row then fires the database cascade, which removes the remaining comments in the same statement. Keep `cascade="all, delete-orphan"` as well: it lets the ORM handle comments that are in memory, and keeps orphan deletion working. Prerequisites that are easy to miss: - `ondelete="CASCADE"` changes DDL. `create_all()` renders `FOREIGN KEY(post_id) REFERENCES posts (id) ON DELETE CASCADE` for a new table, but an existing table needs a schema migration that recreates the constraint. - The database must enforce foreign keys. SQLite does only after `PRAGMA foreign_keys=ON` on every connection, and MySQL only with a storage engine that supports them. - Without the database rule, `passive_deletes=True` leaves unloaded comments behind, or fails on the foreign key. `passive_deletes` also accepts `"all"`, which stops the ORM nulling child keys when the parent is deleted, even for children in memory (a child removed from the collection is still nulled), for schemas where a database trigger must see the parent DELETE. It cannot be combined with `delete` or `delete-orphan` cascade; mapper configuration raises an `ArgumentError` if it is. ## WriteOnlyMapped for a collection that should never load Deleting is one place a big collection is loaded. Others are reading `post.comments`, and some writes that need the existing members. `WriteOnlyMapped` declares a relationship that **never** loads its contents into memory; it applies the `lazy="write_only"` strategy and gives each instance a `WriteOnlyCollection`: | Need | With `WriteOnlyMapped[Comment]` | |---|---| | Add comments | `post.comments.add(c)` or `add_all([...])`; inserted at the next flush | | Remove one | `post.comments.remove(c)`; deleted or nulled per cascade | | Read a page | `session.scalars(post.comments.select().limit(50))` | | Bulk insert | `session.execute(post.comments.insert(), [{...}, ...])` | | Iterate | raises `TypeError`; use `select()` | | Replace the whole collection on a persistent post | raises `InvalidRequestError` ("does not support implicit iteration") | A write-only collection **requires** `passive_deletes=True` in this scenario. Without it, deleting the parent raises `InvalidRequestError: Attribute Post.comments can't load the existing state from the database for this operation; full iteration is not permitted. If this is a delete operation, configure passive_deletes=True ...`. The error is the point: the collection refuses to load silently. The older `DynamicMapped` (`lazy="dynamic"`) is the legacy form of this idea; it returns a legacy `Query` and can still iterate the whole collection in non-obvious cases, so the documentation prefers `WriteOnlyMapped` for truly large collections. ## Choosing between the tools - **Small collection, read often**: a plain `Mapped[list[Comment]]`; add `passive_deletes=True` and the `ON DELETE` rule only if deletes are slow. - **Unbounded collection** such as comments, events or audit rows: `WriteOnlyMapped` with `passive_deletes=True` and `ON DELETE CASCADE`, so nothing ever loads it whole. - **Many-to-many link table**: `ON DELETE CASCADE` on both link-table keys with `passive_deletes=True` saves SELECTs, but the ORM still loads some collections to find the related objects a delete cascade must remove. ## Checking the fix - Log statements during `session.delete(post)` and commit: the SQL should be the SELECT that loaded the post and one `DELETE FROM posts`. - With an ordinary list collection, comments already loaded into `post.comments` are still deleted one by one; that is expected. - A `delete(Post)` statement through `Session.execute()` never ran the ORM cascade anyway; with the database rule in place it now removes comments too.
- The team added passive_deletes=True but deleted posts now leave comments behind; what is missing?The database half. passive_deletes=True only stops the ORM loading unloaded children; something else must remove them. Either the foreign key has no ON DELETE CASCADE in the live schema, because the model changed but no migration recreated the constraint, or the database is not enforcing foreign keys, as SQLite does not without PRAGMA foreign_keys=ON.
- Why not just keep a normal collection and use a delete() statement for the comments first?It works, but it is a second statement every caller has to remember, and it bypasses the relationship's cascade rules, so the mapping no longer says what deleting a post does. passive_deletes with ON DELETE CASCADE keeps `Session.delete(post)` the single call, lets the unit of work handle comments already loaded, and has the database remove the rest in the same DELETE.
- What does passive_deletes="all" do differently from True?It also stops the ORM nulling child foreign keys when a parent is deleted with no delete cascade, even for children in memory, so a database trigger or ON DELETE rule sees the parent DELETE untouched. It cannot be combined with delete or delete-orphan cascade; mapper configuration raises an ArgumentError.
- Delete cascade makes SQLAlchemy issue one DELETE ... WHERE post_id = ? for all children.
- passive_deletes=True alone deletes the comments; the schema needs no change.
- SQLite enforces ON DELETE CASCADE without any extra setting.
- WriteOnlyMapped collections can be iterated like a normal list.
- passive_deletes="all" is a stronger form of True that works with delete cascade.