selectinload vs joinedload for eager loading

WHAT THIS TESTS Whether you can identify the N+1 query problem behind slow ORM endpoints and pick the correct eager-loading strategy with an understanding of the trade-off.

A GOOD ANSWER COVERS First analyze: enable SQL echo or query logging to see whether the slowness is one big query or many small ones, which reveals lazy loading firing once per parent row, the classic N+1. Use EXPLAIN ANALYZE on the database side to inspect the plan and indexes. To fix, replace lazy access with explicit eager loading options on the query. joinedload fetches related objects in a single SQL statement using a JOIN; it is efficient for many-to-one and one-to-one relationships and avoids a second round trip. However, for one-to-many or many-to-many, a JOIN multiplies the parent row once per child, producing a wide, duplicated result set that SQLAlchemy must deduplicate in memory, which can be slow and memory-heavy. selectinload instead emits the primary query, collects the parent primary keys, and runs a second query that loads children with a WHERE IN clause; there is no row explosion and it scales well for collections, at the price of one extra query round trip. As a rule of thumb: joinedload for to-one, selectinload for to-many.

COMMON WRONG ANSWERS Failing to recognize the N+1 pattern. Always reaching for joinedload, including on large one-to-many collections, causing row explosion. Adding more indexes without fixing the query strategy. Disabling lazy loading without choosing a deliberate eager strategy.

LIKELY FOLLOW-UPS How does row explosion inflate memory and dedupe cost? When is subqueryload used? How do indexes interact with these choices?

ONE CONCRETE EXAMPLE Loading 100 users each with many posts via lazy loading fires 1 plus 100 queries. joinedload(User.posts) collapses it to one query but returns one row per post, duplicating user columns and forcing dedupe. selectinload(User.posts) runs two queries, one for users and one SELECT ... WHERE user_id IN (...), with no duplicated rows, which is faster and lighter for this one-to-many case.