Building Software
Engineering Fundamentals for the Agent Era
N+1 queries: one query for a list and then one more for every item, usually hidden inside an ORM's lazy loading.
Uniqueness checked only in application code, so two simultaneous requests both pass the check and create duplicates.
A whole table fetched into memory to be filtered or sorted in code instead of in the query.
The same business rule written in both a query and application code, drifting apart over time.
The boundary between application code and storage: where business rules live, what queries get generated, and how the two stay consistent.
An agent built a team dashboard that loads teams through an ORM, then lazily loads each member's recent tasks inside a loop, and enforces unique team names by looking the name up before inserting. Count the queries for a page showing 30 teams of 8 people, and find the race that still creates duplicate names.
Watch
Kleppmann explains what isolation levels actually guarantee and which race conditions (lost updates, write skew) slip through them. A check-then-insert uniqueness test fails in exactly this way.
Shows how an ORM quietly adds queries to a request, how to find them in the query log, how to remove N+1 patterns, and how to guard against their return with query-count tests.
A Hibernate expert shows the SQL an ORM generates for lazy associations, fetch strategies and batching, and when to fetch only the data a view needs.
Each chapter takes a plausible-looking pattern (keyless entries, rules kept only in application code, fetching everything and filtering in code) and shows the cost it hides and the constraint or query that fixes it.
The transactions chapter is the clearest treatment of isolation anomalies and of which invariants need a constraint or serializable isolation rather than an application-level check.
The source for the patterns underneath every ORM (Active Record, Data Mapper, Unit of Work, Lazy Load, Repository), which explains where N+1 queries come from and how a data access layer hides the schema.
Manual
Documents check, not-null, unique, primary-key, foreign-key and exclusion constraints, the database-side rules that hold under concurrent writes where an application-side check does not.
Manual
Official guidance on profiling queries, doing work in the database instead of in Python, and fixing N+1 access with select_related and prefetch_related. The same ideas carry over to any ORM.