SQLite is an embedded database: no socket to open, you directly access to it via file system.
If you do not plan to use BigData with high number of writers, you will have an hard time beating SQLite on modern hardware, on average use cases.
I have written a super simple search engine [1] using python asyncio and SQLite is not the bottleneck so far.
If you are hitting the SQLite limit, I have an happy news: PostgreSQL upgrade will be enough for a lot of use cases [2]: you can use it to play with a schemaless mongo-like database, a simple queue system [3] or a search engine with stemming. After a while you can decide if you need a specialized component (i.e. Kafka, Elastic Search, etc) for one of your services.
[1]: https://github.com/daitangio/find
[2]: https://gioorgi.com/2025/postgres-all/
The nice thing about this pattern is that you can create foreign data wrappers for your customer SQLite databases and query them as if they were in postgres, cross customer aggregations are slow but individual customer analytics are quite fast, and this gives you near infinite scalability.
(I am just asking: are you sure WAL is on?)
I'm seeing these numbers on my current scratch benchmark:
- Events append to a 10M+ record table (~4+ GiB database).
- Reads are fetched from a separate computed table, which is trigger-updated from the append-only table.
- WAL-mode ON, Auto-vacuum ON
{:dbtype "sqlite",
:auto_vacuum "INCREMENTAL",
:connectionTestQuery "PRAGMA journal_mode;",
:preferredTestQuery "PRAGMA journal_mode;",
:dataSourceProperties
{:journal_mode "WAL",
:limit_worker_threads 4,
:page_size 4096,
:busy_timeout 5000,
:enable_load_extension true,
:foreign_keys "ON",
:journal_size_limit 0,
:cache_size 15625,
:maximumPoolSize 1,
:synchronous "NORMAL"}},
- With a separate writer process (sequential), and concurrently, two reader processes, I'm seeing 400+ append transactions/second (into the append-only table, no batching), and a total of 41,000 reads per second, doing `select *` on the trigger-updated table.
My schema is (deliberately) poor --- most of it is TEXT.
(edit: add clarifying text)
It seems like it mostly comes down to how likely it is that the site will grow large enough to need a networked database. And people probably wildly overestimate this. HackerNews, for example, runs on a single computer.
That's before you even get into sharding sqlite.
[1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
LLM also has this tendency of premature optimization where they start to write very complex classes for users who only want to extract some information just to resolve a quick problem.
They call it the n+1 problem. 200 queries is the theoretically correct approach, but due to high network latency of networked DMBSes you have to hack around it. But if the overhead is low, like when using SQLite, then you would not introduce hacks in the first place.
The parent is saying that if you correctly design your application, but then move to system that requires hacks to deal with its real-world shortcomings, that you won't be prepared. Although I think that's a major overstatement. If you have correctly designed the rest of your application too, introducing the necessary hacks into a couple of isolated places is really not a big deal at all.
Part of the "object-relational mapping" problem has always been that SQL is superior to conventional programming languages in many ways.
SQL was originally designed to run on the same machine as the user, so it was never envisioned as a problem. It wasn't until Oracle decided to slap networking protocols on top of an SQL engine did it become one. Unfortunately, they should have exposed a language more conducive to the limitations of the network, performing the mapping in the same place as the database. But, such is the life of commercial computing.
Oracle has that now, it was just several decades too late, and by that time everyone else had copied their bad ideas.
Network adds latency and while it might be fine to run 500 queries with the database being on the same machine, adding 1-5ms per query makes it feel not okay.
Or going from ~1ms over a local wired network to ~10ms over a wireless network.
Had a customer performance complaint that boiled down to that, something that should take minutes took hours. Could not reproduce it internally.
After a lot of back abd forth I asked if the user machine was wired. Nope, wireless laptop. Got them to plug in like their colleagues and it was fast again.
This isn’t an sqlite-specific point, although sqlite often runs faster on a single machine because local sockets have some overhead.
> For stored procedures that contain several statements that don't return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
Always send "pragma foreign_keys=on" first thing after opening the db.
Some of the types sloppiness can be worked around by declaring tables to be STRICT. You can also add CHECK constraints that a column value is consistent with the underlying representation of the type -- for instance, if you're storing ip addresses in a column of type BLOB, you can add a CHECK that the blob is either 4 or 16 bytes.
Still doesn't have a huge variety of types though.
I understand maintaining backwards compatibility, but the non-strict behavior is just so insane I have a hard time imagine it doesn’t bite most developers who use SQLite at some point.
Maybe this has been solved though? Anybody here running a serious backend-heavy app with SQLite in production and can share? How do you remotely edit data, do analytics queries etc on production data?
Maybe the page could have been shorter, but not my much.
And instead were spent blocking on the disk for all of the extra queries that were made? Or is it trying to say that the concatenation a handful of strings takes 22 ms. Considering how much games can render with a 16 ms budget I don't see where that time is going rendering html.
Update: Actually it looks like I was wrong about TH1: https://fossil-scm.org/home/doc/tip/www/th1.md
The timeline appears to be constructed by C code instead: https://www.fossil-scm.org/home/file?name=src/timeline.c&ci=...
Update 2: Here's the timeline code from September 2016: https://www.fossil-scm.org/home/file?name=src/timeline.c&ci=...
Back then it had some kind of special syntax for outputting HTML:
sqlite3_snprintf(sizeof(zNm),zNm,"b%d",i);
zBr = P(zNm);
if( zBr && zBr[0] ){
@ <p style='border:1px solid;background-color:%s(hash_color(zBr));'>
@ %h(zBr) - %s(hash_color(zBr)) -
@ Omnes nos quasi oves erravimus unusquisque in viam
@ suam declinavit.</p>
cnt++;
}
}
Or am i mistaken in thinking that communicating to mysql on localhost is comparable latency to sqlite?
Of course, SQLite and client/server database servers have different use cases, so it is kind of an apples and oranges comparison.
SQLite is embedded in your program's address space. You call its functions directly like any other function. Depending on your language, there is probably some FFI overhead but it's a lot less than than an external localhost connection
Which should be obvious. But I could see some reading this blog post and jumping to the wrong conclusion.
In the bad old days you had to wait for a lever to move and for the disk to rotate at least once!
I know it’s not and never suggested it was.
I was making the point that writes contain more overhead than reads (which should be obvious) so people should bear that in mind when reading this blog post.
Edit: is it “bear” or “bare”? I’m never sure with that phrase haha
I can think of many hacks to do this, but is there a best practice for this kind of stuff? I’m curious how people do this.
I use it with pocketbase and it is a delightful and very productive setup.
This guide [2] is for an older version of pocketbase and litestream, but i can update it if would be helpful/interesting for anyone.
[1] https://github.com/benbjohnson/litestream/
[2] https://notes.danielgk.com/Pocketbase/Pocketbase+on+Fly.io
So the sqlite developers use their on versioning system which uses sqlite for storage. Funny.
As for reliability - it's a fault-tolerant, highly available system. Reliability is the reason it exists. :-) If you're asking about quality and test coverage, you might like to check out these resources:
- https://rqlite.io/docs/design/
I had to build some back-office tools and used Ruby on Rails with SQLITE and didn't bother with doing "efficient" joins or anything. Just index the foreign keys, do N+1s everywhere - you'll be fine. The app is incredibly easy to maintain and add features because of this and the db is super easy to backup - literally just scp the sqlite db file somewhere else. Couldn't be happier with this setup.
If there's a chance someone is writing to the database during the copy, you should "sqlite3 database.sqlite .backup" (or ".dump") first; Or, alternatively, on a new enough sqlite3, you have a builtin sqlite3_rsync that is like rsync except it interacts with the sqlite3 updates to guarantee a good copy at the other end.
We just flip into an app-side maintenance mode before we run the backup so we know there’s no writes, scp the file and then flip it back. We only do nightlies so it’s not a problem. The shell script is super simple and we’ve only needed to do nightly backups so far so we run it in a cron at midnight when no one is working. Ezpz. Literally took us an hour to implement and been chugging along without issues for nearly 2 years now without fail.
If we ever need more than that I’d probably just setup litestream replication.
I.e. sometimes one query is cheaper. It is not network anymore.
Also you can run your "big" DB like postgres on the same machine too. No law against that.
Most SQLite queries however, are not analytic queries. They're more like record retrievals.
So hitting a SQLite table with 200 "queries" is similar hitting a webserver with 200 "GET" commands.
In terms of ergonomics, SQLite feels more like a application file-format with a SQL interface. (though it is an embedded relational database)
Unless you have toy amounts data... or doing batch operations which is not typical (and can be problematic for other transactions due to locking, etc...)