There is a growing movement to use SQLite for everything. Kent C. Dodds argues for defaulting to SQLite in web development due to its zero-latency reads and minimal operational burden. Wesley Aptekar-Cassels makes a strong case that SQLite works for web apps with large user bases, provided they don't need tens of thousands of writes per second. Discussions on Hacker News and elsewhere cite companies like Apple, Adobe, and Dropbox using SQLite in production. Even the official SQLite documentation encourages its use for most websites with fewer than 100K hits per day.
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.
I have benchmark evidence showing this clearly. This post focuses on join-heavy analytical queries, not on the many workloads where SQLite is already the right choice. But first, let me explain why this matters more than people think.
Multi-join queries are not exotic
A common reaction to discussing multi-join queries is: "I don't write queries with 10 joins." This usually means one of three things: the schema is denormalized, the logic has been moved into application code, or the product is simple. None of these mean the problem goes away.
In any system with many entity types, rich relationships, history or versioning, permissions, and compositional business rules, multi-join queries inevitably appear. They emerge whenever data is normalized and questions are compositional. Here are concrete examples from real production systems.
Enterprise SaaS (CRM / ERP / HR). A query like "show me all open enterprise deals" in a Salesforce-like system touches accounts, contacts, products, pricebooks, territories, users, permissions, and activity logs. Real queries in these systems routinely involve 10-20 joins. Every dimension of the business (customers, ownership, products, pricing, regions, access control, activity statistics) is often normalized into its own table.
Healthcare (EHR). "Patients with condition X, treated by doctors in department Y, prescribed drug Z in the last 6 months, and whose insurance covers that drug" spans patients, visits, diagnoses, providers, departments, prescriptions, drugs, insurance plans, coverage rules, and claims. Exceeding 15 joins is common.
E-commerce and Marketplaces. "Orders in the last 30 days that include products from vendor V, shipped late, refunded, with customers in region R" touches orders, order items, products, vendors, shipments, delivery events, refunds, customers, addresses, regions, and payment methods. Again, 10+ joins.
Authorization and Permission systems. "Which documents can user U see?" requires traversing users, groups, roles, role assignments, resource policies, ACLs, inheritance rules, and organizational hierarchies. This alone can be 12+ joins, sometimes recursive.
Analytics and BI. Star schemas look simple on paper, but real dashboard queries add slowly changing dimensions, hierarchy tables, permission joins, and attribution models. A "simple" dashboard query often hits 6-10 dimension tables plus access control.
Knowledge graphs and semantic systems. "Papers authored by people affiliated with institutions collaborating with company X on topic Y" requires joining papers, authors, affiliations, institutions, collaborations, and topics. Very common in search and recommendation systems.
Event sourcing and temporal queries. Reconstructing the state of an account at a point in time with approval chains requires joining entity tables, event tables, approval tables, history tables, and version joins. Temporal dimensions multiply join counts quickly.
AI / ML feature pipelines. Feature stores generate massive joins. Assembling a feature vector often requires joining user profiles, sessions, events, devices, locations, and historical aggregates. This is why feature stores are expensive.
The pattern is consistent across domains:
| Domain | Typical join count |
|---|---|
| SaaS CRM / ERP | 8-20 |
| Healthcare | 10-25 |
| Authorization | 6-15 |
| BI dashboards | 6-12 |
| Knowledge graphs | 10-30 |
| Feature pipelines | 8-20 |
Complex joins are not accidental. They emerge from normalized data, explicit relationships, compositional business rules, layered authorization, and historical records. Again, if you don't see many joins in your system, it usually means the schema is denormalized, the logic is in the application layer, or the product hasn't reached sufficient complexity yet. This does not mean the system is better. It often means complexity has been pushed into the application layer, which can add engineering cost without adding real value.
The evidence: JOB benchmark
The Join Order Benchmark (JOB) is a standard benchmark designed specifically to stress database query optimizers on complex multi-join queries [1]. Based on the Internet Movie Database (IMDb), a real-world, highly normalized dataset with over 36 million rows in its largest table, it contains 113 analytical queries with 3 to 16 joins each, averaging 8 joins per query. Unlike synthetic benchmarks like TPC, JOB uses real data with realistic data distributions, making it a much harder test of query optimization.
I ran this benchmark comparing three databases: SQLite (via JDBC), PostgreSQL 18, and Datalevin (an open-source database I build). All were tested in default configurations with no tuning, on a MacBook Pro M3 Pro with 36GB RAM. This is not a tuning shootout, but a look at out-of-the-box optimizer behavior. Details of the benchmark methodology can be found here.
Overall wall clock time
| Database | Total time (113 queries) |
|---|---|
| Datalevin | 93 seconds |
| PostgreSQL | 171 seconds |
| SQLite | 295 seconds (excluding 9 timeouts) |
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.
Execution time statistics (milliseconds)
| Database | Mean | Median | Min | Max |
|---|---|---|---|---|
| Datalevin | 773 | 232 | 0.2 | 8,345 |
| PostgreSQL | 1,507 | 227 | 3.5 | 36,075 |
| SQLite | 2,837 | 644 | 8.1 | 37,808 |
The median tells the story: SQLite's median is nearly 3x worse than the other two.
Per-query speedup: Datalevin vs. SQLite
The chart at the top of this post shows the speedup ratio (SQLite time / Datalevin time) for each of the queries on a logarithmic scale (excluding 9 timeouts). Points above the 1x line (10^0) mean Datalevin is faster; points below mean SQLite is faster. The horizontal lines mark 1x, 10x, and 100x speedups.
Several patterns stand out:
- The vast majority of points are above the 1x line, often by 10x or more.
- For the hardest queries, Datalevin achieves 100x+ speedups. These are precisely the complex multi-join queries where SQLite's optimizer breaks down.
- SQLite is rarely faster, and when it is, the margin is small.
- The 9 timed-out queries (not shown) would push the ratio even higher.
Where SQLite breaks down
Timeouts. Queries 8c, 8d, 10c, 15c, 15d, 23a, 23b, 23c, and 28c all timed out at the 60-second limit during the benchmark runs. These represent queries with higher join counts where SQLite's optimizer failed to find an efficient plan.
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.
Why SQLite falls behind
SQLite's query optimizer has fundamental limitations for complex joins:
-
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.
-
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.
-
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.
Before choosing SQLite for a production system, ask: will our queries stay simple forever? If the answer is no, and it usually is, the savings in deployment simplicity may not be worth the cost in query performance as the system grows.
An alternative approach
In a previous post, I described how Datalevin, a triplestore using Datalog, handles these complex queries effectively. Its query optimizer uses counting and sampling on its triple indices to produce accurate cardinality estimates, resulting in better execution plans. Unlike row stores, where cardinality estimation is notoriously difficult due to bundled storage, a triplestore can count and sample individual data atoms directly.
This approach yields plans that are not only better than SQLite's, but consistently better than PostgreSQL's across the full range of JOB queries. Despite Datalevin being written in Clojure on the JVM rather than optimized C code, it still managed to halve the total query time in the JOB benchmark. The quality of the optimizer's decisions matters more than the raw execution speed of the engine.
For systems that need both deployment simplicity (Datalevin works as an embedded database too) and the ability to handle complex queries as they inevitably arise, a triplestore with a cost-based optimizer offers a practical alternative to either SQLite or a full client-server RDBMS. It is not a silver bullet, but it can deliver SQLite-like operational simplicity without giving up complex-query performance.
If you have different results or have tuned SQLite to handle these queries well, I would love to compare notes. The goal here is not to dunk on SQLite, but to surface a missing dimension in a discussion that often defaults to deployment tradeoffs alone.
References
[1] Leis, V., et al. "How good are query optimizers, really?" VLDB Endowment. 2015.