Slow API: Fast Queries, Slow Endpoint

This is the second article in Slow API, a series of practical investigations into API performance. In Part 1: Investigating a Slow Query, we improved an orders query with an index and cursor pagination. Now we follow what happens when the application turns those orders into a response.

The orders page needs one more field: the customer's name. The model already has a customer relationship, so we add order.customer.name to the response. There is no new query in the handler, and the original orders query still uses its index.

Yet the response gets slower as the page grows. To find out why, we will start with the application code, inspect the SQL it actually emits, and change how it loads related objects.

We’ll follow a SQLAlchemy example from model access to the SQL it emits, then compare two fixes using local PostgreSQL measurements.

Start with the models

An order belongs to one customer. Both belong to a tenant. These are the relevant mappings, using SQLAlchemy's typed model declarations:

from datetime import datetime

from sqlalchemy import BigInteger, DateTime, ForeignKeyConstraint, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "lab_customers"

    tenant_id: Mapped[int] = mapped_column(primary_key=True)
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


class Order(Base):
    __tablename__ = "lab_orders"
    __table_args__ = (
        ForeignKeyConstraint(
            ["tenant_id", "customer_id"],
            ["lab_customers.tenant_id", "lab_customers.id"],
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    tenant_id: Mapped[int]
    customer_id: Mapped[int]
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    total_cents: Mapped[int]
    customer: Mapped[Customer] = relationship(lazy="select")

The composite foreign key makes the customer relationship match on both tenant and customer ID. Customer 1 in tenant 42 is different from customer 1 in tenant 7. The lab_ table prefix identifies the example’s tables.

The setting to watch is lazy="select". We have declared how Order relates to Customer, but have not asked the orders query to fetch customer objects with it. SQLAlchemy can load them when we access the relationship. See its lazy-loading behavior.

The handler looks like one query

Here is the response-building path. The caller provides an open Session for the request:

def serialize_order(order):
    return {
        "id": order.id,
        "created_at": order.created_at.isoformat(),
        "total_cents": order.total_cents,
        "customer": {"id": order.customer.id, "name": order.customer.name},
    }


def list_orders(session, tenant_id, limit=50):
    orders = session.scalars(
        select(Order)
        .where(Order.tenant_id == tenant_id)
        .order_by(Order.created_at.desc(), Order.id.desc())
        .limit(limit)
    ).all()
    return [serialize_order(order) for order in orders]

The code explicitly selects orders once. The extra work is hidden in serialize_order(): accessing order.customer can execute SQL even though it looks like an ordinary attribute read.

The session must still be open when we serialize. Closing it first would not solve N+1; attempting to load an unavailable relationship would fail instead.

Look at what reaches PostgreSQL

The fixture has 20,000 orders, two tenants, and 200 customers per tenant. The orders index from Part 1's design is already present. The first 100 orders per tenant reference distinct customers.

We recorded executed SELECT statements through SQLAlchemy's before_cursor_execute event. We counted through response construction to include queries triggered by attribute access as well as the initial model load.

The first diagnostic output is:

After loading Order models: 1 SELECT
After building 50 response objects: 51 SELECTs

The first statement fetches the orders. Below, the generated SQL is normalized for readability: aliases and driver casts are shortened, and named placeholders stand for bound parameters.

SELECT id, tenant_id, customer_id, created_at, total_cents
FROM lab_orders
WHERE tenant_id = :tenant_id
ORDER BY created_at DESC, id DESC
LIMIT :limit;

Then the serializer causes this statement to run 50 times, with different customer IDs:

SELECT tenant_id, id, name
FROM lab_customers
WHERE tenant_id = :tenant_id
  AND id = :customer_id;

That is N+1: one query loads N orders, then N more queries load their customers. Each lookup uses the customer's primary key, but each still requires a separate database call. A slow-query threshold can miss the pattern because it judges statements individually.

The lab observed 11 queries for 10 orders, 51 for 50, and 101 for 100. The apparent single-query handler grows a new database round trip for each returned order.

There is a useful wrinkle: the session can reuse a customer already loaded for another order. Our 300-order check references only 200 distinct customers and makes 201 queries, not 301. We use a fresh Session for every simulated request, so a previous run cannot accidentally preload the relationships and hide the problem.

Ask for the customers before serialization

The response needs every order's customer. Make that requirement explicit in the query with selectinload():

from sqlalchemy.orm import selectinload

def list_orders_batched(session, tenant_id, limit=50):
    orders = session.scalars(
        select(Order)
        .options(selectinload(Order.customer))
        .where(Order.tenant_id == tenant_id)
        .order_by(Order.created_at.desc(), Order.id.desc())
        .limit(limit)
    ).all()
    return [serialize_order(order) for order in orders]

The serializer stays unchanged. The difference appears underneath it: SQLAlchemy first fetches the order page, then loads the referenced customers together.

The second statement now has this shape:

SELECT tenant_id, id, name
FROM lab_customers
WHERE (tenant_id, id) IN (
    (:tenant_1, :customer_1),
    (:tenant_2, :customer_2)
    -- Remaining customer keys from this page
);

The actual trace has 50 key pairs for the 50-order page. Both parts of the customer identity are present. SQLAlchemy attaches each loaded customer to the corresponding orders, so order.customer.name no longer needs a new query during serialization.

In this lab, that produces two queries for a nonempty page, and one for an empty page. Larger key sets can be split into batches; two is the observed budget for these page sizes, not a promise for every use of selectinload().

Try loading the relationship with a JOIN

For a small customer object, we can also ask SQLAlchemy to load the relationship in the orders statement:

from sqlalchemy.orm import joinedload

def list_orders_joined(session, tenant_id, limit=50):
    orders = session.scalars(
        select(Order)
        .options(joinedload(Order.customer))
        .where(Order.tenant_id == tenant_id)
        .order_by(Order.created_at.desc(), Order.id.desc())
        .limit(limit)
    ).all()
    return [serialize_order(order) for order in orders]

The generated SQL becomes a single statement with this shape:

SELECT o.id, o.tenant_id, o.customer_id, o.created_at, o.total_cents,
       c.tenant_id, c.id, c.name
FROM lab_orders AS o
LEFT OUTER JOIN lab_customers AS c
  ON c.tenant_id = o.tenant_id AND c.id = o.customer_id
WHERE o.tenant_id = :tenant_id
ORDER BY o.created_at DESC, o.id DESC
LIMIT :limit;

That is one query in our run. The ORM reconstructs Order and Customer objects from the rows, and the same serializer returns the same response.

This relationship points to one customer per order, so the join does not multiply orders. It can repeat customer columns when several orders share a customer. Batch loading can avoid that repetition; the trade-off matters more when related objects are large.

SQLAlchemy documents both approaches in its relationship-loading strategies. Here, we choose the strategy for this particular response rather than changing every query through a global model default.

Verify the result, then compare the cost

We checked that lazy loading, selectinload(), and joinedload() produce identical JSON, including order IDs, customer names, amounts, timestamps, and ordering. We also checked the other tenant, repeated customers, and empty results.

For each page size, we ran five warmups and 20 measured iterations per variant, rotating their execution order. Each iteration used a fresh Session over the same established database connection.

These were the medians for a 50-order response on PostgreSQL 17.10 in Docker, Python 3.13.0, SQLAlchemy 2.0.55, and Psycopg 3.3.6 on macOS:

Timing includes database calls, ORM object loading, response construction, JSON serialization, and the session lifecycle. It excludes fixture setup, connection establishment, HTTP handling, and pool wait time. There is no artificial delay or concurrent traffic. The measured path includes the ORM work, not just execution inside PostgreSQL.

The timings are local observations, not production latency predictions. The repeatable structural change is that customer loading no longer makes one call per order. Check endpoint latency under representative traffic before treating either fix as a production win.

The fixture is unchanged during the run. Multiple reads in production can observe different snapshots at PostgreSQL's default Read Committed isolation. If the response requires one consistent snapshot, choose transaction semantics deliberately; merely placing both reads in a transaction is not enough. See transaction isolation.

Be careful when the relationship becomes a collection

Now the response also needs order items. Add the collection to Order and map its items:

from sqlalchemy import ForeignKey

# Add this attribute inside the Order class:
# items: Mapped[list["OrderItem"]] = relationship(order_by="OrderItem.line_number")

class OrderItem(Base):
    __tablename__ = "lab_order_items"

    order_id: Mapped[int] = mapped_column(ForeignKey("lab_orders.id"), primary_key=True)
    line_number: Mapped[int] = mapped_column(primary_key=True)

Every order in this example has three items.

Suppose we manually join the items and tell SQLAlchemy to populate the collection from those rows:

from sqlalchemy.orm import contains_eager

stmt = (
    select(Order)
    .join(Order.items)
    .options(contains_eager(Order.items))
    .where(Order.tenant_id == 42)
    .order_by(Order.created_at.desc(), Order.id.desc(), OrderItem.line_number)
    .limit(50)
)
orders = session.scalars(stmt).unique().all()

The SQL limit now applies to joined rows. Our lab returned 17 orders and 50 items, with only two of the final order's three items. Calling .unique() removes duplicate parent objects; it cannot recover rows excluded by LIMIT.

This is a problem with that manual query. SQLAlchemy's collection joinedload() handles the page boundary differently:

stmt = (
    select(Order)
    .options(joinedload(Order.items))
    .where(Order.tenant_id == 42)
    .order_by(Order.created_at.desc(), Order.id.desc())
    .limit(50)
)
orders = session.scalars(stmt).unique().all()

The captured SQL wraps the limited orders query in a subquery before joining the items. Here is its shape, with nonessential selected columns omitted:

SELECT page.id, items.order_id, items.line_number
FROM (
    SELECT id, created_at
    FROM lab_orders
    WHERE tenant_id = :tenant_id
    ORDER BY created_at DESC, id DESC
    LIMIT :limit
) AS page
LEFT OUTER JOIN lab_order_items AS items ON items.order_id = page.id
ORDER BY page.created_at DESC, page.id DESC, items.line_number;

Our checks returned 50 orders with all 150 items. Using selectinload(Order.items) also returned the full page, through two queries. The joined eager-loading documentation covers collection deduplication and preserving limits through a subquery.

Both fixes still load all items for the selected orders. If a single order can have thousands of items, define a separate pagination or preview policy for that collection. A limit on orders does not bound their items.

Catch the next accidental load

N+1 can return when someone adds a relationship to the response but forgets to load it upfront. Adding raiseload("*") makes access to an unloaded relationship raise an error, so tests can catch the missing load. Explicitly loaded relationships, such as the customer below, remain available:

from sqlalchemy.orm import raiseload

stmt = (
    select(Order)
    .options(selectinload(Order.customer), raiseload("*"))
    .where(Order.tenant_id == 42)
    .order_by(Order.created_at.desc(), Order.id.desc())
    .limit(50)
)
orders = session.scalars(stmt).all()
payload = [serialize_order(order) for order in orders]

Also test the full response-building path with several orders and a fresh session: this example should return the expected data using two queries. A one-order fixture or previously loaded relationships can hide N+1.

The slow work began at an attribute access, far from the visible select(Order). Inspecting the emitted SQL connected those two places. Choosing a loading strategy for the response removed the repeated calls while preserving the models and the returned data.

The Slow API series

1. Investigating a Slow Query — Use EXPLAIN ANALYZE, improve an index, and replace deep OFFSET pagination.
2. Fast Queries, Slow Endpoint (this article) — Find N+1 queries and compare SQLAlchemy relationship-loading strategies.
3. Waiting for a Database Connection — Measure Go connection pool waits, shorten transactions, and understand where PgBouncer helps.
4. Waiting for a Database Lock — Find the blocking transaction and shorten lock waits without breaking purchase correctness.