Synthetic Industry

Troubleshooting guide · updated 2026-10-11

An endpoint gets slower as the list grows: count its queries, then choose how to load relations

How to recognise a query-per-row pattern in Django, Rails or SQLAlchemy, the loading options each offers, and how a query-count test keeps it fixed.

The pattern: one query for the list, one more for every row

An ORM lets code walk from one object to the next without writing SQL, which is the convenience and the trap. Load fifty orders, then print each order's customer name, and by default the library fetches the customer separately the first time you touch it. That is one query for the list plus fifty for the customers, the pattern usually called N+1. Each query may be fast. The request is slow because of the number of round trips, and the number grows with the list.

You will rarely see it in code review, because the loop looks harmless. You see it in a query log as the same statement repeating with a different id, in a debug toolbar as a high count, or in a response time that rises with the number of rows. The first step is therefore to count: how many queries does one request make for a small list and for a large one?

  • Use the framework's debug tooling or the database log on a copy with fake data.
  • Measure with two list sizes; a count that scales with the list confirms the pattern.

What each framework offers

Django has two tools. select_related follows foreign keys and one-to-one relations by joining in the same query, so later access costs nothing. prefetch_related runs a separate lookup for each relationship and joins the results in Python, which is how it handles many-to-many and reverse relations. Rails offers includes, which chooses between separate queries and a join depending on whether you filter on the association, preload for separate queries and eager_load for a join. SQLAlchemy defaults to lazy loading and offers selectinload, which batches parent keys into one extra query, and joinedload, which adds a join to the original statement.

Their documentation gives rough guidance. SQLAlchemy says selectinload is generally the best choice for collections and joinedload the most general for many-to-one. Each approach trades round trips for something else: a join widens the result and can repeat parent data, while a batched lookup loads every related row in memory.

  • Do not apply eager loading everywhere; load only the relations the endpoint uses.
  • Trim columns where the response needs few of them.

How the fix quietly undoes itself

Django's documentation warns about the commonest regression: prefetch_related primes the related manager's all(), and calling filter() on that manager sends a new query for each object, throwing away the prefetch. The documented alternative is a Prefetch object with its own queryset and a to_attr. Rails and SQLAlchemy offer guard rails: strict_loading in Rails raises on a lazy load, and raiseload in SQLAlchemy turns an accidental lazy load into an error.

The most durable guard is a test that counts the queries. In Django, assertNumQueries asserts an exact number: it fails on fewer queries as well as on more, so it cannot express "no more than". For a ceiling, capture the queries with CaptureQueriesContext around the request and assert that len(ctx) is at most the ceiling; use assertNumQueries when you want any change in the count to be looked at. In Rails, subscribe to the sql.active_record event around the request and count what arrives; in SQLAlchemy, a before_cursor_execute listener on the engine can do the same. If someone later adds a field that triggers a lookup per row, the test fails in review instead of in production.

  • Write a ceiling with a little headroom when ordinary changes may add a query, and an exact count when you want every change in the count reviewed.
  • Run the test against the original code to confirm it would have caught the problem.
  • Count the queries of the whole request: a login or a database session adds its own.

How the one-endpoint outcome is accepted

The paid job covers one endpoint in a Django, Rails or SQLAlchemy application. It is accepted when the query count stays at or below the agreed ceiling for both a small and a large synthetic list, the response is identical to the original, including order, and a new test fails on the old code and passes on the new. You review and merge the pull request; we do not touch production data.

It does not cover a single slow statement, which is a plan and index question, nor several endpoints at once, which is a project. If your pages are slow and you are not sure which pattern applies, count the queries first: the number decides which guide and which job fits.

  • High count with a repeating statement: this guide.
  • Low count with one slow statement: read its plan instead.

Sources and limits

  • Django 5.2: QuerySet API, select_related and prefetch_related Checked 2026-10-11.
    • select_related follows foreign-key and one-to-one relations with a join, and prefetch_related does a separate lookup per relationship and joins in Python.
    • Calling filter() on a prefetched related manager sends a new query and wastes the prefetch; Prefetch with a custom queryset and to_attr is the documented alternative.
  • Django 5.2: Testing tools Checked 2026-10-11.
    • assertNumQueries(num, ...) asserts that num database queries are executed, for a function call or inside a with block.
  • Django 5.2 source: django/test/testcases.py Checked 2026-10-11.
    • assertNumQueries compares the number of captured queries with the number given using assertEqual, so it fails on fewer queries as well as on more.
  • Django 5.2 source: django/test/utils.py Checked 2026-10-11.
    • CaptureQueriesContext(connection) is a context manager that records the queries run on a connection while it is active; len() of it is the number captured.
  • Rails 8.1 guide: Active Record Query Interface Checked 2026-10-11.
    • The guide describes the N+1 queries problem and fixes with includes, preload and eager_load, and strict_loading to raise on lazy loads that remain.
  • Rails 8.1 guide: Active Support Instrumentation Checked 2026-10-11.
    • The sql.active_record event is called every time Active Record uses an SQL query, with the statement in its :sql payload, and ActiveSupport::Notifications.subscribe attaches a block to it.
  • SQLAlchemy 2.0: Relationship loading techniques Checked 2026-10-11.
    • Lazy loading is the default; selectinload is generally best for collections and joinedload is the most general option for many-to-one; raiseload turns accidental lazy loads into errors.
  • SQLAlchemy 2.0: Core events, before_cursor_execute Checked 2026-10-11.
    • The before_cursor_execute event is called with the SQL statement before each cursor execution and is a good choice for logging, which a counter can use.