Expire on Commit: Freshness vs Memory for Long-Lived SQLAlchemy Sessions

Short answer

For a long-lived worker, expire_on_commit is a freshness knob, not a memory knob. Keep the default True unless you have measured the refresh-query cost and can tolerate staleness; bound memory with explicit session lifecycle (scoped_session.remove(), session.close(), or periodic session.expire_all()/clear()). The identity map dominates retention either way: expiring an object clears its loaded attribute state but the instance stays referenced in the map until the session is cleared or closed.

What each setting actually does

With expire_on_commit=True (the default in SQLAlchemy 1.4 and 2.x), commit marks all instances expired. The next attribute access emits a SELECT to reload state. You get freshness at the cost of extra round-trips — and, if you touch many objects individually right after commit, an N+1 pattern.

With False, instances keep their loaded state after commit, so no refresh queries occur. The risk is real in workers: if another process updates a row your session already loaded, your session keeps serving the old snapshot until you call session.refresh(obj), session.expire(obj), or session.expire_all(). In a multi-process deployment this is a consistency bug waiting to happen, not just a tuning choice.

Why memory grows regardless of the setting

The session's identity map holds a reference to every persistent instance it has loaded. Expiration only discards the attribute values on the instance; the instance itself, its identity key, and its bookkeeping remain. So a worker that loads millions of rows over its lifetime will grow with either setting. The documented mechanisms that actually release retention are:

• scoped_session.remove() — disposes of the thread-local session entirely; the correct per-task/per-request teardown.
• session.close() — releases connections and expunges all objects.
• session.expunge_all() or session.clear() — drops identity-map references without closing.

Note there is no general "query cache" in the ORM that retains results across queries; result objects become identity-map entries, so identity-map cleanup covers them. (The separate query_cache_size on create_engine in 1.4+ only bounds the compiled-SQL cache, which is small and not your leak.)

Recommended pattern for a worker

SessionFactory = sessionmaker(bind=engine, expire_on_commit=True)
registry = scoped_session(SessionFactory)

def handle_task(task):
    try:
        ...  # load, mutate, commit
    finally:
        registry.remove()  # releases identity map + connection

If tasks are long and must reuse one session, call session.expunge_all() at batch boundaries instead.

Verify before tuning

Enable engine echo (create_engine(url, echo=True)) or an event listener on before_cursor_execute and count SELECTs after commit with each setting. For memory, snapshot len(session.identity_map) (or count via gc.get_objects()) in a loop; if it grows monotonically, the fix is lifecycle cleanup, not expire_on_commit. If your worker is single-process and the only writer, expire_on_commit=False plus explicit expiry at known staleness points is a defensible optimization — but measure the query savings first.