Why the 20‑Year‑Old N+1 Query Problem Doesn’t Apply to SQLite

Why N+1 is an anti‑pattern and why the “why” matters more than the “what”

The N+1 query problem occurs when an application first fetches a list of identifiers (1 query) and then issues a separate query for each item in the list (N queries). A typical example is retrieving 50 timeline entries and then, for each entry, fetching its tags, permissions, and parent node. In client‑server databases such as MySQL or PostgreSQL each SQL statement traverses the full TCP/IP stack, incurring a message round‑trip that costs at least a millisecond. Two hundred round‑trips therefore add roughly 200 ms of latency, exceeding the typical user‑perceived latency threshold of 100 ms.

25 ms and the underlying code‑architecture cost

SQLite’s own website is powered by the Fossil version‑control system. Fossil generates each dynamic page (timeline, tickets, wiki) by executing about 200 SQL statements. The raw SQL log for a real page rendered on 2016‑09‑16 is published unedited, showing the full sequence of statements without post‑hoc optimisation. The main query that pulls the latest 50 timeline entries is:

INSERT OR IGNORE INTO timeline SELECT
  blob.rid AS blobRid,
  uuid AS uuid,
  datetime(event.mtime,toLocal()) AS timestamp,
  coalesce(ecomment, comment) AS comment,
  coalesce(euser, user) AS user,
  blob.rid IN leaf AS leaf,
  bgcolor AS bgColor,
  event.type AS eventType,
  (SELECT group_concat(substr(tagname,5), ', ') FROM tag, tagxref
     WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid
       AND tagxref.rid=blob.rid AND tagxref.tagtype>0) AS tags,
  tagid AS tagid,
  brief AS brief,
  event.mtime AS mtime
FROM event CROSS JOIN blob
WHERE blob.rid=event.objid
  AND NOT EXISTS (SELECT 1 FROM tagxref WHERE tagid=5 AND tagtype>0 AND rid=blob.rid)
ORDER BY event.mtime DESC LIMIT 50;

After this large query Fossil issues a small per‑item query for each of the 50 entries (e.g., fetching tags, parent links, permission flags). The total number of statements exceeds 200, yet the page generation time is reported as less than 25 ms. Most of that time is spent in HTTP handling, template rendering, and HTML output; the actual SQLite engine work accounts for only a few milliseconds.

Code‑maintenance cost

The timeline page mixes three content types (commits, tickets, wiki pages), each requiring different data and rendering logic. Consolidating all data into a single massive query would produce a gigantic JOIN that intertwines unrelated columns, making future changes risky. The N+1 approach keeps each content type’s data‑access code isolated within its own rendering module, preserving separation of concerns and reducing the risk of accidental breakage.

Misapplied best‑practice assumptions

ORMs (Hibernate, Entity Framework, Django) introduced batch‑fetching to mitigate N+1 on client‑server databases, but when the database runs in‑process as SQLite those abstractions become unnecessary complexity. SQLite’s website handles roughly 500 k page views per day with about 200 queries per page and experiences no concurrency bottlenecks, demonstrating that the “N+1 is always bad” rule does not hold in this context.

Understanding query cost

The SQLite team’s unedited log is the most convincing evidence that, on an embedded engine, the cost of 200 queries is effectively zero. Optimising by merging queries would not improve latency; it would only add code complexity. When network latency dominates, query consolidation is valuable; when the engine runs in the same process, the cost difference between a function call and a network round‑trip spans six orders of magnitude, making the former negligible.

Takeaway

The N+1 anti‑pattern is a context‑dependent engineering judgment. It is deadly on MySQL, but on SQLite it is harmless. The key lesson is to understand the actual cost of the operation before applying blanket “best‑practice” rules.

Reference: https://www.sqlite.org/whentouse.html