SQLite in Production? Not So Fast for Complex Queries - yyhh.org

These points are fair. The overarching theme is a pushback against automatically choosing complex, client-server databases like PostgreSQL when SQLite is often more than sufficient, simpler to manage, and faster for the majority of use cases. I agree with that framing. The debate has settled into a well-understood set of tradeoffs: |For "SQLite for everything"|Known limitations| |--|--| |Zero-latency reads as an embedded library|Write concurrency limited to a single writer| |No separate server to set up or maintain|Not designed for distributed or clustered systems| |Reliable, self-contained, battle-tested (most deployed DB in the world)|No built-in user management; relies on filesystem permissions| |Fast enough for most human-driven web workloads|Schema migration can be more complex in large projects| These are the terms of the current discussion. But there is an important, often overlooked dimension missing from this framing. **SQLite struggles with complex queries**. More specifically, SQLite is not well-suited to handle the kind of multi-join queries that arise naturally in any serious production system. This goes beyond the usual talking points about deployment concerns (write concurrency, distribution, and so on). It points to a system-level limitation: the query optimizer itself. That limitation matters even for read-heavy, single-node deployments, which is exactly the use case where SQLite is supposed to shine. … SQLite needed a 60-second timeout per query, and 9 queries failed to complete within that limit. The actual total time for SQLite would be substantially higher if these were included. For example, query 10c, when allowed to run to completion, took 446.5 seconds. … **Extreme slowdowns**. Even among queries that completed, SQLite was often dramatically slower. Query 9d took 37.8 seconds on SQLite versus 1.6 seconds on Datalevin (24x). Query 19d took 20.8 seconds versus 5.7 seconds. Query families 9, 10, 12, 18, 19, 22, and 30 all show SQLite performing significantly worse, often by 10-50x. … 1. **Limited join order search**. SQLite uses exhaustive search for join ordering only up to a limited number of tables. Beyond that threshold, it falls back to heuristics that produce poor plans for complex queries. 2. **Weak statistics model**. SQLite's cardinality estimation is simpler than PostgreSQL's, which itself has well-documented weaknesses [1]. With fewer statistics to guide optimization, SQLite makes worse choices about which tables to join first and which access methods to use. 3. **No cost-based plan selection for complex cases**. For queries with many tables, SQLite's planner cannot explore enough of the plan space to find good join orderings. The result is plans that process orders of magnitude more intermediate rows than necessary. These limitations are architectural; they are not bugs likely to be fixed in a near-term release. They reflect design tradeoffs inherent in SQLite's goal of being a lightweight, embedded database. ## What this means for "SQLite in production" SQLite is excellent for what it was designed to be: an embedded database for applications with simple query patterns. It excels as a local data store, a file format, and a cache. For read-heavy workloads with straightforward queries touching a few tables, it works extremely well. But the production systems described above, e.g. CRM, EHR, e-commerce, authorization, analytics, are precisely where SQLite's query optimizer becomes a bottleneck. These are not hypothetical workloads, but the day-to-day reality of systems that serve businesses and users. The "SQLite in production" advocates often benchmark simple cases: key-value lookups, single-table scans, basic CRUD operations. On those workloads, SQLite does extremely well. But production systems grow. Schemas become more normalized as data integrity requirements increase. Questions become more compositional as business logic matures. And at that point, the query optimizer becomes the bottleneck, not the network round trip to a database server.