SQLAlchemy Async Relationship Loading: Escaping the MissingGreenlet and the N+1

Good evening. I see you have encountered MissingGreenlet.

You are not alone. In fact, if I were to rank the errors that drive developers away from async SQLAlchemy, MissingGreenlet would occupy the top position with considerable daylight between it and second place. It is not merely common. It is, for all practical purposes, inevitable. Every developer who migrates from sync to async SQLAlchemy encounters this error. Most encounter it within the first hour. Some, within the first ten minutes.

The error appears the moment you access a relationship attribute on a model loaded inside an async session. You had working code in sync SQLAlchemy. You migrated to async. You changed Session to AsyncSession, added some await keywords, and then this:

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called;
can't call await_only() here. Was IO attempted in an unexpected place?

# This appears when you access a lazy-loaded relationship
# inside an async session. Every async SQLAlchemy developer
# hits this error. Usually within the first hour.

The message is technically accurate and practically useless. It tells you that greenlet-based IO was attempted outside of a greenlet context. What it means, in plain terms, is: you tried to lazy-load a relationship, and async SQLAlchemy will not permit that.

This is not a bug. It is a design constraint with good reasons behind it. But understanding those reasons — and knowing the alternatives — is the difference between an afternoon of frustration and a properly architected async data layer. If you will permit me, I should like to walk you through every loading strategy available, when each one earns its place, and the production pitfalls that await even experienced developers.

Why does lazy loading fail in async?

In sync SQLAlchemy, lazy loading is the default behavior. When you access book.author, SQLAlchemy silently emits a SELECT query, waits for the result, hydrates the related object, and returns it — all inside a Python property access. The thread blocks. Nobody notices. It works.

# Sync SQLAlchemy — lazy loading works transparently:
# When you access book.author, SQLAlchemy silently runs
# SELECT * FROM authors WHERE id = ?
# This blocks the thread, but it works.

# Async SQLAlchemy — lazy loading cannot work:
# There is no thread to block. The event loop is single-threaded.
# SQLAlchemy cannot emit an awaitable query from inside a
# property access (book.author). Python properties cannot be async.
# So it raises MissingGreenlet instead.

In async SQLAlchemy, the engine runs on an asyncio event loop. Database IO must be awaited. But Python property accessors (book.author) cannot be async. There is no syntax for await book.author on a mapped attribute. SQLAlchemy cannot emit an awaitable query from inside a synchronous property access, so it raises MissingGreenlet instead.

The reason this boundary exists requires a brief look at how SQLAlchemy bridges the sync and async worlds:

# What actually happens under the hood:
#
# SQLAlchemy async uses greenlet to bridge sync and async code.
# The ORM internals are synchronous — they were written long before
# asyncio existed. The async extension wraps them in greenlets
# that can yield back to the event loop when IO occurs.
#
# When a lazy load fires, the ORM tries to emit a SQL query
# from inside a synchronous property accessor. The greenlet
# bridge cannot intercept this because there is no active
# greenlet context — you are in plain synchronous Python.
#
# Hence: "greenlet_spawn has not been called."
# Translation: "You triggered IO outside the greenlet bridge."

The greenlet library is what makes async SQLAlchemy possible in the first place. The entire ORM — decades of synchronous code — runs inside greenlets that can yield to the event loop when a query needs to be sent. But that bridge only works when the code is executing within the greenlet context. A lazy-loaded property access happens in plain Python, outside that context, and so the bridge cannot intercept it.

This is a fundamental limitation of Python's type system. Properties are synchronous. There is no async @property that would allow await book.author to emit a query transparently. Until that changes — and there is no PEP proposing it — lazy loading and async are structurally incompatible.

The solution: load relationships before you need them, using explicit loading strategies. No lazy loading. No silent queries. Every relationship access must be planned in advance.

Here is the code that triggers the error:

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import Session, relationship, Mapped, mapped_column
from sqlalchemy import ForeignKey, select
import asyncio

class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    books: Mapped[list["Book"]] = relationship(back_populates="author")

class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
    author: Mapped["Author"] = relationship(back_populates="books")

async def get_books():
    async with AsyncSession(engine) as session:
        result = await session.execute(select(Book))
        books = result.scalars().all()

        for book in books:
            # MissingGreenlet error. Every single time.
            print(book.author.name)

And here is the fix:

from sqlalchemy.orm import selectinload

async def get_books():
    async with AsyncSession(engine) as session:
        result = await session.execute(
            select(Book).options(selectinload(Book.author))
        )
        books = result.scalars().all()

        for book in books:
            # Works. No error. No additional queries.
            print(book.author.name)

# What selectinload sends to PostgreSQL:
# Query 1: SELECT * FROM books
# Query 2: SELECT * FROM authors WHERE id IN (1, 2, 3, 4, 5, ...)
# That second query is the key. One round trip, all authors loaded.

That selectinload(Book.author) tells SQLAlchemy: "When you load the books, also load their authors in a second query using an IN clause." Both queries execute inside the async session, inside the greenlet context. By the time you access book.author, the data is already in memory. No lazy load. No MissingGreenlet.

One query to fetch the books. One query to fetch their authors. Two round trips, both awaited properly. This is the pattern that will appear throughout the rest of this article, and it is the foundation of every async relationship loading strategy.

Which loading strategy should you use?

SQLAlchemy 2.0 provides six loading strategies. Each generates different SQL, carries different performance characteristics, and suits different relationship shapes. Choosing the wrong one will not raise an error — it will simply make your application slower than it needs to be. And in async code, where you have already accepted the complexity cost of explicit loading, choosing poorly adds insult to effort.

The short version: use selectinload for collections, joinedload for single objects, and raiseload as your safety net. That covers 95% of async relationships. The remaining 5% is where subqueryload and write_only earn their keep, and we shall attend to those shortly.

selectinload vs joinedload: when does it matter?

joinedload uses a SQL JOIN to fetch related data in a single query. For many-to-one relationships (e.g., each book has one author), this is elegant and efficient:

from sqlalchemy.orm import joinedload

async def get_books():
    async with AsyncSession(engine) as session:
        result = await session.execute(
            select(Book).options(joinedload(Book.author))
        )
        # unique() is REQUIRED with joinedload on collections
        books = result.unique().scalars().all()

        for book in books:
            print(book.author.name)

# What joinedload sends to PostgreSQL:
# SELECT books.*, authors.*
# FROM books
# LEFT OUTER JOIN authors ON authors.id = books.author_id
# One query. One round trip. But the result set is wider.

One query. One round trip. The LEFT OUTER JOIN adds the author columns to each book row, and SQLAlchemy assembles the objects in memory. For many-to-one relationships, this is the ideal strategy — the join does not duplicate data because each book has exactly one author.

Where joinedload becomes a problem

For one-to-many relationships (e.g., each author has many books), joinedload becomes a different beast entirely. The JOIN duplicates parent rows:

from sqlalchemy.orm import joinedload

async def get_authors_with_books():
    async with AsyncSession(engine) as session:
        result = await session.execute(
            select(Author).options(joinedload(Author.books))
        )
        # unique() is MANDATORY here
        authors = result.unique().scalars().all()

# What PostgreSQL actually returns:
#
# author_id | author_name | book_id | book_title
# ----------+-------------+---------+------------------
#     1     | Dostoevsky  |    1    | Crime and Punishment
#     1     | Dostoevsky  |    2    | The Brothers Karamazov
#     1     | Dostoevsky  |    3    | The Idiot
#     1     | Dostoevsky  |    4    | Notes from Underground
#     2     | Tolstoy     |    5    | War and Peace
#     2     | Tolstoy     |    6    | Anna Karenina
#
# Dostoevsky's name, transmitted four times. Tolstoy's, twice.
# With 200 authors averaging 50 books each: 10,000 rows.
# The author columns are duplicated in every single one.

With 200 authors averaging 50 books each, your result set contains 10,000 rows instead of 200. Every author column — name, bio, email, avatar URL, whatever you are storing — is duplicated once per book. You must call .unique() to deduplicate, and the database transfers substantially more data over the wire. For wide author rows with text columns, this duplication can be enormous.

selectinload avoids this entirely:

from sqlalchemy.orm import selectinload

async def get_authors_with_books():
    async with AsyncSession(engine) as session:
        result = await session.execute(
            select(Author).options(selectinload(Author.books))
        )
        authors = result.scalars().all()  # No unique() needed

# Query 1: SELECT * FROM authors
# Returns: 200 rows (one per author, no duplication)
#
# Query 2: SELECT * FROM books WHERE author_id IN (1, 2, 3, ..., 200)
# Returns: 10,000 rows (one per book, no duplication)
#
# Total data: exactly what you need. Nothing repeated.
# Two round trips, but the total bytes transferred is far less.

Two clean queries. No duplication. No .unique() required. The total data transferred is exactly the data you need — nothing more.

The rule of thumb, which I would encourage you to commit to memory:

• Many-to-one (book → author): joinedload. One query, no duplication.
• One-to-many (author → books): selectinload. Two queries, no bloat.
• Many-to-many: selectinload. Always. The JOIN produces a cartesian product that is ruinous at scale.

There is an exception: if your parent query returns very few rows (under 10) and the collection is small, joinedload on a collection is fine. The duplication is negligible. But as a default, selectinload is the safer choice for collections. When in doubt, selectinload is the answer.

An honest counterpoint about round trips

I should be forthcoming about a scenario where joinedload wins even for collections: high-latency database connections. If your application connects to a PostgreSQL instance across a wide-area network — say, 40ms round-trip latency to a cloud database in another region — then every additional query costs 40ms of wall-clock time. Two selectinload queries at 40ms each is 80ms of latency alone, before PostgreSQL even begins executing. A single joinedload query with duplicated data might still be faster if the duplication is modest and the latency is severe.

This is rare. Most applications connect to a database in the same region, often the same availability zone, with sub-millisecond latency. But if you are debugging unexpected slowness with selectinload, check your connection latency before blaming the strategy. The strategy is fine. Your network topology might not be.

subqueryload: the strategy nobody talks about

You will notice that subqueryload appears in the strategy table but rarely in tutorials. It occupies a narrow niche, but when that niche applies, it is the only correct choice.

from sqlalchemy.orm import subqueryload

async def get_authors_with_books():
    async with AsyncSession(engine) as session:
        result = await session.execute(
            select(Author)
            .where(Author.active == True)
            .options(subqueryload(Author.books))
        )
        authors = result.scalars().all()

# Query 1: SELECT * FROM authors WHERE active = true
# Query 2: SELECT books.* FROM books
#          WHERE books.author_id IN (
#              SELECT authors.id FROM authors WHERE active = true
#          )
#
# Notice: the parent query is re-executed as a subquery.
# If the parent query is expensive, this doubles its cost.
# If the parent query is cheap but returns many IDs,
# the subquery avoids the IN-list size limit.
#
# selectinload sends WHERE id IN (1, 2, 3, ..., 847)
# subqueryload sends WHERE id IN (SELECT id FROM ...)
#
# For most cases, selectinload wins. But if your parent query
# returns 10,000+ IDs, the IN-list can exceed PostgreSQL's
# effective parameter limit. subqueryload sidesteps this.

The key difference from selectinload: instead of collecting all parent IDs and sending WHERE id IN (1, 2, 3, ..., 847), subqueryload re-executes the parent query as a subquery. This matters in two scenarios.

First, when your parent query returns a very large number of rows. PostgreSQL handles IN lists efficiently up to a point, but at 10,000+ values, the query planner starts to struggle. The IN list must be parsed, transmitted, and matched. A subquery avoids this entirely — the database handles the filtering internally.

Second, when your parent query is complex and its result set cannot be easily represented as an IN list. selectinload collects the literal ID values from the parent result; subqueryload preserves the parent query's logic.

The cost: the parent query executes twice. If the parent query is expensive — a complex join, a full-text search, a CTE — subqueryload doubles that cost. For most applications, selectinload is the better default. Reserve subqueryload for the cases where selectinload's IN list becomes the bottleneck.

raiseload: the discipline your async codebase requires

The most disciplined approach to async relationship loading is to make lazy loading impossible. Not just broken — impossible. raiseload converts the silent MissingGreenlet failure into an explicit, unmistakable InvalidRequestError at the point of access:

from sqlalchemy.orm import raiseload

class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))

    # raiseload: if you forget to eager load, you get an
    # explicit error instead of a silent N+1 or MissingGreenlet
    author: Mapped["Author"] = relationship(
        back_populates="books",
        lazy="raise"
    )

# Now every query MUST specify a loading strategy:
# select(Book).options(selectinload(Book.author))  — works
# select(Book)  — raises InvalidRequestError immediately

This is the approach I recommend for any async codebase with more than one developer. It turns relationship loading from an implicit behavior into an explicit decision. Every query must declare what it needs. Forgotten relationships surface immediately in development, not three months later when a rarely-accessed code path triggers a MissingGreenlet in production at two in the morning.

The wildcard pattern

You need not set lazy="raise" on every relationship individually. SQLAlchemy provides the wildcard raiseload("*") that applies to all relationships at once:

from sqlalchemy.orm import raiseload

# Apply raiseload to ALL relationships on a model, at query time:
result = await session.execute(
    select(Book).options(
        raiseload("*"),
        selectinload(Book.author),
        # Only Book.author is loaded. All other relationships
        # raise InvalidRequestError if accessed.
    )
)

# Or set it as the model-level default:
class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))

    author: Mapped["Author"] = relationship(lazy="raise")
    reviews: Mapped[list["Review"]] = relationship(lazy="raise")
    tags: Mapped[list["Tag"]] = relationship(
        secondary=book_tags, lazy="raise"
    )

# Every relationship defaults to raise. Every query declares
# exactly what it needs. No surprises. No MissingGreenlet.
# This is the "deny by default" pattern — the only sensible
# default for async codebases.

This is the "deny by default, allow explicitly" pattern. It pairs naturally with async code because async already demands explicitness — you are already writing await before every database operation. Extending that explicitness to relationship loading is not a burden. It is consistency.

The SQLAlchemy documentation on wildcard loading covers additional options for applying raiseload selectively to specific relationship types.

An honest objection: raiseload adds verbosity

I should acknowledge the objection, because it is legitimate. With raiseload as your default, every query must enumerate the relationships it needs. A query that previously said select(Book) now says select(Book).options(selectinload(Book.author), selectinload(Book.reviews), selectinload(Book.tags)). This is more code. It is more verbose. It is more things to remember.

The counterargument is straightforward: that verbosity is documentation. When you read a query and see its .options() clause, you know exactly which relationships it loads. You know the SQL it generates. You can reason about its performance without running it. In a lazy-loading codebase, the same query loads an unknowable set of relationships depending on which attributes the downstream code happens to access. That implicitness is not simplicity. It is hidden complexity.

I will not pretend the trade-off is free. But the cost is typing. The benefit is predictability. In production systems, that exchange is always favourable.

Chaining eager loads across multiple levels

Real applications rarely load a single level of relationships. You need the book, its author, and the author's publisher. You need the order, its items, and each item's product. SQLAlchemy handles this with chained loading options:

from sqlalchemy.orm import selectinload, joinedload

# Loading a chain: Book -> Author -> Publisher
result = await session.execute(
    select(Book).options(
        selectinload(Book.author).selectinload(Author.publisher)
    )
)
# Query 1: SELECT * FROM books
# Query 2: SELECT * FROM authors WHERE id IN (...)
# Query 3: SELECT * FROM publishers WHERE id IN (...)
# Three queries, three round trips, full chain loaded.

# Mixing strategies in a chain: Book -> Author (join) -> Publisher (selectin)
result = await session.execute(
    select(Book).options(
        joinedload(Book.author).selectinload(Author.publisher)
    )
)
# Query 1: SELECT books.*, authors.* FROM books
#           LEFT OUTER JOIN authors ON ...
# Query 2: SELECT * FROM publishers WHERE id IN (...)
# Two queries. The first hop uses a JOIN (efficient for many-to-one),
# the second uses selectinload (efficient for the next level).

# Loading multiple branches from the same root:
result = await session.execute(
    select(Author).options(
        selectinload(Author.books).selectinload(Book.reviews),
        selectinload(Author.awards),
    )
)
# Query 1: SELECT * FROM authors
# Query 2: SELECT * FROM books WHERE author_id IN (...)
# Query 3: SELECT * FROM reviews WHERE book_id IN (...)
# Query 4: SELECT * FROM awards WHERE author_id IN (...)
# Four queries, one per level per branch. Each is clean and indexable.

Each level in the chain can use a different strategy. The general principle: use joinedload for many-to-one hops (where the join does not duplicate data) and selectinload for one-to-many hops (where it would). Mix them freely within a chain.

A note of caution: deeply chained eager loads can generate many queries. A three-level chain with two branches produces four or five queries. This is usually fine — each query is simple and fast. But if you find yourself chaining five or six levels deep, consider whether your API is returning too much data. The loading strategy is not the problem; the data shape is.

The self-referential recursion trap

Self-referential relationships — categories with subcategories, comments with replies, org charts with managers — present a special challenge in any ORM, and in async SQLAlchemy they present a particularly pointed one. How deep do you load? And how do you load without lazy-loading the next level?

class Category(Base):
    __tablename__ = "categories"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    parent_id: Mapped[int | None] = mapped_column(
        ForeignKey("categories.id")
    )
    children: Mapped[list["Category"]] = relationship(
        back_populates="parent"
    )
    parent: Mapped["Category | None"] = relationship(
        back_populates="children", remote_side=[id]
    )

# Loading the full tree with selectinload:
result = await session.execute(
    select(Category).options(
        selectinload(Category.children, recursion_depth=-1)
    )
)

# recursion_depth=-1 loads ALL levels. Be careful.
# recursion_depth=3 loads 3 levels deep, then stops.
# Without recursion_depth, selectinload loads ONE level only.
#
# This replaced the old join_depth parameter.
# If you see join_depth in Stack Overflow answers from 2023,
# that is the old API. recursion_depth is the 2.0 way.

The recursion_depth parameter (introduced in SQLAlchemy 2.0) controls how many levels of self-referential relationships to load. Set it too low and you get incomplete trees. Set it to -1 and you load the entire graph, which can be enormous. Each level fires an additional query — recursion_depth=5 means five SELECT ... WHERE parent_id IN (...) queries, one per level.

If you encounter older Stack Overflow answers mentioning join_depth — that was the pre-2.0 parameter name. It still works but recursion_depth is the current API and handles the async case correctly.

When to abandon the ORM for recursive CTEs

For most applications, recursion_depth=3 or recursion_depth=4 is sufficient. Shallow menus, two-level comment threads, simple org charts — the ORM handles these gracefully. But if you genuinely need the full tree of an unknown depth, the ORM is the wrong tool:

from sqlalchemy import text

async def get_full_category_tree(root_id: int):
    async with AsyncSession(engine) as session:
        result = await session.execute(
            text("""
                WITH RECURSIVE tree AS (
                    SELECT id, name, parent_id, 0 AS depth
                    FROM categories
                    WHERE id = :root_id

                    UNION ALL

                    SELECT c.id, c.name, c.parent_id, t.depth + 1
                    FROM categories c
                    JOIN tree t ON c.parent_id = t.id
                    WHERE t.depth < 10  -- safety limit
                )
                SELECT * FROM tree ORDER BY depth, name
            """),
            {"root_id": root_id}
        )
        return result.fetchall()

# One query. One round trip. Any depth.
# No N+1. No selectinload firing one query per level.
# PostgreSQL handles the recursion internally.
#
# The depth < 10 guard prevents runaway recursion
# if someone creates a cycle (which foreign keys
# alone do not prevent).

One query. One round trip. Any depth. The database handles the recursion internally, which it is extremely good at — this is what recursive CTEs were designed for. PostgreSQL's query executor can traverse millions of rows in a hierarchical structure faster than any application-level loop firing one query per level.

The trade-off: the recursive CTE returns flat rows, not nested objects. You will need to assemble the tree structure in application code. But for deep hierarchies, that assembly cost is trivial compared to the network round trips you have saved.

write_only relationships: when you never need to read the collection

Some relationships exist purely for writes. An Author model might have thousands of Book records. Loading all of them into memory every time you add a new book is wasteful and slow. SQLAlchemy 2.0 introduced WriteOnlyMapped for exactly this case:

from sqlalchemy.orm import WriteOnlyMapped, write_only

class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]

    # write_only: no loading at all. Add/remove only.
    books: WriteOnlyMapped[list["Book"]] = relationship(
        back_populates="author"
    )

async def add_book(author_id: int, title: str):
    async with AsyncSession(engine) as session:
        author = await session.get(Author, author_id)

        new_book = Book(title=title)
        author.books.add(new_book)
        await session.commit()

        # To read books, use an explicit query:
        stmt = author.books.select().where(Book.title.ilike("%postgres%"))
        result = await session.execute(stmt)
        matching = result.scalars().all()

write_only relationships cannot be loaded — not lazily, not eagerly, not at all. You can add items, remove items, and query them explicitly using .select(), but you never accidentally load 50,000 records into memory. This is particularly valuable in async code, where a lazy-loaded massive collection would not just be slow — it would raise MissingGreenlet.

The .select() method on a write_only relationship returns a query that you can filter, order, and paginate. It is the best of both worlds: a relationship that knows how to add and remove items, and a query interface that lets you read them selectively rather than all at once.

I would recommend write_only for any relationship where the collection routinely exceeds a thousand records. Audit logs, event histories, user activity streams — these should never be eagerly loaded in their entirety, and write_only makes that impossible by construction rather than by convention.

The expire_on_commit trap (and other async session gotchas)

There is one more MissingGreenlet trigger that catches experienced developers, and it is arguably more insidious than the relationship loading issue because it affects scalar columns, not just relationships.

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

# Production-ready async engine setup
engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/mydb",
    pool_size=20,
    max_overflow=10,
    pool_pre_ping=True,
)

# Session factory with sane defaults
async_session = sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,  # prevents MissingGreenlet after commit
)

# expire_on_commit=False is critical. Without it, accessing
# any attribute after commit() triggers a lazy load —
# which raises MissingGreenlet in async context.

By default, SQLAlchemy expires all loaded attributes after session.commit(). In sync mode, accessing an expired attribute triggers a lazy reload — invisible and fine. In async mode, that lazy reload raises MissingGreenlet. You commit a transaction, access a simple attribute like book.title, and the error appears on a column that is not even a relationship.

Let me illustrate exactly how this confounds developers:

async def update_book_title(book_id: int, new_title: str):
    async with AsyncSession(engine) as session:
        book = await session.get(Book, book_id)
        book.title = new_title
        await session.commit()

        # WITH expire_on_commit=True (the default):
        # book.title is now expired. Accessing it triggers a lazy load.
        # In async: MissingGreenlet. On a SCALAR COLUMN, not a relationship.
        print(book.title)  # MissingGreenlet!

        # WITH expire_on_commit=False:
        # book.title still holds "new_title" in memory. No lazy load.
        print(book.title)  # Works. Returns the value you just set.

# The confusion: you expect MissingGreenlet on relationships.
# You do not expect it on book.title — a simple string column.
# But expire_on_commit expires EVERYTHING, not just relationships.

Set expire_on_commit=False on your async session factory. The loaded data remains valid after commit. If you need fresh data, call await session.refresh(instance) explicitly. This is the same pattern as the rest of async SQLAlchemy: explicit is better than implicit. Refresh when you know you need fresh data. Do not expire everything speculatively.

Session sharing across coroutines

The second gotcha is subtler and more dangerous. An AsyncSession is not coroutine-safe. Sharing a single session across concurrent asyncio.gather() calls is a recipe for corrupted state:

import asyncio

# WRONG: sharing a session across concurrent coroutines
async def bad_concurrent_access():
    async with AsyncSession(engine) as session:
        # These run concurrently, sharing the same session
        results = await asyncio.gather(
            fetch_users(session),
            fetch_orders(session),
            fetch_products(session),
        )
        # This can corrupt the session's internal state.
        # Symptoms: wrong data returned, InterfaceError,
        # "This session is in a rollback state" errors,
        # or — worst case — silently returning stale data.

# CORRECT: one session per coroutine
async def good_concurrent_access():
    async def fetch_with_own_session(query_fn):
        async with AsyncSession(engine) as session:
            return await query_fn(session)

    results = await asyncio.gather(
        fetch_with_own_session(fetch_users),
        fetch_with_own_session(fetch_orders),
        fetch_with_own_session(fetch_products),
    )

One session per request, one request per session. This is not a recommendation — it is a hard constraint. The SQLAlchemy async documentation is explicit about this, and the consequences of violating it are unpredictable: wrong data, mysterious errors, or — worst of all — silently stale results that look correct but are not.

Connection pool exhaustion in async

A third gotcha, specific to async: connection pool exhaustion happens faster than you expect. In sync code, a thread that holds a connection for 200ms processes one request during that time. In async code, a coroutine that holds a connection for 200ms while other coroutines are running is blocking a pool slot while the event loop continues accepting new requests. If your pool has 20 connections and 21 coroutines need one simultaneously, the 21st coroutine waits — and if it waits too long, you get a timeout.

The fix is not a larger pool (though that can help temporarily). The fix is shorter transaction lifetimes. Load the data, commit, release the connection. Do not hold a session open while you serialize JSON, format templates, or call external APIs. Acquire late. Release early. The event loop's concurrency makes this discipline essential, not optional.

• Always use async with for sessions. Manual close() calls are easy to forget, and leaked sessions in async code cause connection pool exhaustion faster than in sync code because the event loop processes more requests per unit of time.
• Do not share sessions across coroutines. One session per request. No exceptions.
• Set pool_pre_ping=True on your engine. This detects stale connections before queries fail on them — especially important when connecting through connection poolers that may have idle-timeout policies.

What selectinload needs from your database

If selectinload is the workhorse of async SQLAlchemy — and it is — then it is worth understanding what it asks of PostgreSQL. The strategy is only as fast as the index that serves it.

Every selectinload call generates a query like SELECT * FROM child_table WHERE foreign_key IN (1, 2, 3, ..., N). This is a clean, efficient query pattern. But it has one requirement: the foreign key column must be indexed.

# What selectinload sends to PostgreSQL:
# SELECT * FROM authors WHERE id IN (1, 2, 3, ..., 847)

# Without an index on authors.id — well, it is a primary key,
# so that one is fine. But consider this model:

class OrderItem(Base):
    __tablename__ = "order_items"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"))
    quantity: Mapped[int]

# selectinload(Order.items) generates:
# SELECT * FROM order_items WHERE order_id IN (1, 2, 3, ..., 200)
#
# If order_items.order_id is not indexed, that is a sequential
# scan on every selectinload call. With 10 million order items,
# you are looking at 2-5 seconds per page load.
#
# Gold Lapel sees this pattern in live traffic, recognizes that
# order_id appears in a WHERE ... IN clause repeatedly, and
# creates the index automatically:
# CREATE INDEX CONCURRENTLY ON order_items (order_id)
#
# The next selectinload: 2ms instead of 2,000ms.

Foreign keys in PostgreSQL do not automatically create indexes. The constraint exists — PostgreSQL enforces referential integrity — but without a B-tree index on the FK column, every selectinload query performs a sequential scan. With small tables, you will not notice. With millions of rows, you will notice immediately.

This surprises many developers who come from MySQL, where creating a foreign key automatically creates an index. PostgreSQL does not. The foreign key constraint and the index are independent structures. You must create the index yourself.

Finding your missing indexes

Before you deploy your async loading strategies, I would recommend running this diagnostic query against your database:

-- Check which foreign key columns lack indexes.
-- Run this in psql and address every row that appears.

SELECT
    tc.table_name,
    kcu.column_name,
    tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes
    WHERE tablename = tc.table_name
      AND indexdef LIKE '%' || kcu.column_name || '%'
  )
ORDER BY tc.table_name;

-- Every row in this result is a foreign key without an index.
-- Every selectinload hitting one of these columns is doing
-- a sequential scan. In a mature application with 30+ models,
-- you will typically find 5-15 missing indexes.

In a mature application with 30 or more models, you will typically find between 5 and 15 missing foreign key indexes. Every one of those is a selectinload penalty waiting to manifest. Fix them all. It costs nothing — a CREATE INDEX CONCURRENTLY runs without locking the table — and the performance difference on collection loading can be two or three orders of magnitude.

Automatic index creation

This is where Gold Lapel earns its keep. It sits between your application and PostgreSQL, observing query traffic in real time. When it sees SELECT * FROM order_items WHERE order_id IN (...) arriving repeatedly — the unmistakable signature of selectinload — it checks whether order_id is indexed. If not, it creates the index concurrently, without downtime, without a migration file, without anyone filing a ticket.

The result: your selectinload queries go from sequential scans to index lookups. The 2-second page load becomes 4 milliseconds. The async session that was "slow" was never slow — it was unindexed.

This pattern repeats across every relationship in a mature application. Ten models with three selectinload calls each means thirty queries that depend on FK indexes existing. Miss one, and that endpoint is an order of magnitude slower than the others. Gold Lapel finds every missing one automatically, because it sees the actual queries, not the model definitions.

I should note — and honesty compels me to — that you do not need Gold Lapel for this. The diagnostic query above, run periodically or integrated into your CI pipeline, accomplishes the same thing manually. Gold Lapel's advantage is that it catches the indexes you do not know you need yet: the ones created by new code, new features, new eager loading options added by a developer who did not think to check for the supporting index. The database tells Gold Lapel what it needs. Gold Lapel provides it.

Migrating from sync to async: a practical sequence

If you are reading this article because you are in the middle of migrating a sync SQLAlchemy codebase to async, allow me to suggest a sequence that minimizes the pain.

1. Add raiseload to your sync codebase first. Before touching async at all, set lazy="raise" on your relationships and fix every query that breaks. This forces you to add explicit selectinload and joinedload options while you can still test in familiar territory. Every lazy load you eliminate in sync is one fewer MissingGreenlet in async.
2. Set expire_on_commit=False on your sync session factory. Verify that your application still works. Most applications do not depend on post-commit expiry behavior, but the few that do will break in confusing ways — better to discover that now.
3. Switch to the async engine and session. With raiseload already enforcing explicit loading and expire_on_commit=False already set, the migration to async is primarily mechanical: Session becomes AsyncSession, session.query becomes await session.execute(select(...)), and with becomes async with.
4. Audit your asyncio.gather calls. Any place you run concurrent database operations, ensure each operation uses its own session.
5. Index your foreign keys. Run the diagnostic query. Create the missing indexes. This is the step that turns "async is slow" into "async is fast."

The critical insight: steps 1 and 2 can be done entirely in sync. They are improvements to your sync codebase that happen to be prerequisites for async. If you skip them and jump straight to AsyncSession, you will spend your time fixing MissingGreenlet errors one by one, in an unfamiliar async context, under the cognitive load of learning async patterns simultaneously. That is an unnecessary hardship.

An honest accounting: where async SQLAlchemy falls short

I have spent considerable time in this article explaining how to use async SQLAlchemy well. I should be equally forthcoming about where it does not excel, because pretending these limitations do not exist would be a disservice to you and an embarrassment to me.

The ORM overhead is real. Async SQLAlchemy adds a greenlet bridge on top of an already-abstracted ORM. For simple CRUD operations, the overhead of object hydration, identity map maintenance, and change tracking can exceed the time spent on actual database IO. If your async SQLAlchemy is slower than sync, the eager loading strategy is rarely the cause — the ORM machinery itself is. For latency-critical paths, consider dropping to the raw asyncpg driver for those specific queries.

Eager loading is not a silver bullet for the N+1. You can still create N+1 patterns in async SQLAlchemy — you just create them differently. A loop that calls await session.execute(select(Author).where(Author.id == book.author_id)) for each book is an N+1 that no loading strategy can fix. raiseload catches implicit N+1 through lazy loading; it cannot catch explicit N+1 through manual queries.

The cognitive overhead is genuine. Sync SQLAlchemy's lazy loading is, from a developer experience standpoint, extraordinarily convenient. You access an attribute and the data appears. The fact that this fires a SQL query is a problem for performance, but it is a feature for productivity. Async demands that you think about data loading at query time, every time. For rapid prototyping and early-stage development, this overhead can slow you down meaningfully. If your application does not need async concurrency, sync SQLAlchemy with careful attention to N+1 patterns is a perfectly valid choice. The Waiter does not insist on async where sync suffices.

A complete async loading checklist

If you take nothing else from this article, take these ten items. They will prevent every MissingGreenlet error and most selectinload performance problems before they start.

1. Set expire_on_commit=False on every async session factory. This prevents MissingGreenlet on scalar columns after commit.
2. Use raiseload as the default on all relationship definitions. Override per-query with explicit loading strategies. This is the single most impactful change you can make.
3. Use selectinload for collections (one-to-many, many-to-many). Two clean queries, no row duplication.
4. Use joinedload for single objects (many-to-one, one-to-one). One query, one join, no overhead.
5. Use subqueryload when parent result sets exceed 10,000 rows — the IN-list from selectinload becomes the bottleneck at that scale.
6. Index every foreign key column that appears in selectinload queries. Run the diagnostic query. Or let Gold Lapel do it.
7. Use recursion_depth (not join_depth) for self-referential relationships. Prefer recursive CTEs for deep trees.
8. Use write_only for relationships with thousands of records that you never load fully.
9. Never share an AsyncSession across concurrent coroutines. One session per request. One request per session.
10. Migrate to async incrementally: add raiseload in sync first, fix every query, then switch to AsyncSession.

Async SQLAlchemy is not harder than sync — it is more explicit. Every relationship access that was invisible in sync becomes a visible decision in async. That is not a burden. It is a gift. The N+1 patterns that sync codebases accumulate silently for years are impossible to introduce accidentally when raiseload is your default. The MissingGreenlet error, for all its initial frustration, is doing you a service: it is showing you, at the earliest possible moment, exactly where your data access patterns need to be stated explicitly.

Once stated, those patterns can be optimized. And once optimized, your ORM is no longer the bottleneck — it is the bottleneck's documentation. Every .options() clause is a record of intent, legible to any developer who reads the code six months from now. That legibility, in my experience, is worth more than any number of convenient lazy loads.

The household runs more smoothly when the instructions are written down. Your async data layer is no different.