What features postgres offers over sqlite in the context of running on a single machine with a monolithic app? Application functions [2] means you can extend it however you need with the same language you use to build your application. It also has a much better backup and replication story thanks to litestream [3].
- [1] https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
- [2] https://sqlite.org/appfunc.html
- [3] https://litestream.io/
The main problem with sqlite is the defaults are not great and you should really use it with separate read and write connections where the application manages the write queue rather than letting sqlite handle it.
SQLite on the same machine is akin to calling fwrite. That's fine. This is also a system constraint as it forces a one-database-per-instance design, with no data shared across nodes. This is fine if you're putting together a site for your neighborhood's mom and pop shop, but once you need to handle a request baseline beyond a few hundreds TPS and you need to serve traffic beyond your local region then you have no alternative other than to have more than one instance of your service running in parallel. You can continue to shoehorn your one-database-per-service pattern onto the design, but you're now compelled to find "clever" strategies to sync state across nodes.
Those who know better to not do "clever" simply slap a Postgres node and call it a day.
Actually 35% faster than fwrite [1].
> This is also a system constraint as it forces a one-database-per-instance design
You can scale incredibly far on a single node and have much better up time than github or anthropic. At this rate maybe even AWS/cloudflare.
> you need to serve traffic beyond your local region
Postgres still has a single node that can write. So most of the time you end up region sharding anyway. Sharding SQLite is straight forward.
> This is fine if you're putting together a site for your neighborhood's mom and pop shop, but once you need to handle a request baseline beyond a few hundreds TPS
It's actually pretty good for running a real time multiplayer app with a billion datapoints on a 5$ VPS [2]. There's nothing clever going on here, all the state is on the server and the backend is fast.
> but you're now compelled to find "clever" strategies to sync state across nodes.
That's the neat part you don't. Because, for most things that are not uplink limited (being a CDN, Netflix, Dropbox) a single node is all you need.
Running 100,000 `SELECT 1` queries:
PostgreSQL (localhost): 2.77 seconds
SQLite (in-memory): 0.07 seconds
Exactly. Back in the real world,anyone who is faced with that sort of usecase will simply add memory cache and not bother with the persistence layer.
Also:
> PostgreSQL (localhost): (. .) SQLite (in-memory):
This is a rather silly example. What do you expect to happen to your data when your node restarts?
Your example makes as much sense as comparing Valkey with Postgres and proceed to proclaim that the performance difference is not insignificant.
Running 100,000 `SELECT 1` queries:
PostgreSQL (localhost): 2.71 seconds
SQLite (in-memory): 0.07 seconds
SQLite (tempfile): 0.07 seconds
I hope you understand that your claim boils down to stating that SQLite is faster at doing nothing at all, which is a silly case to make.
Isn't this idea to spend a bit more effort and overhead to get YAGNI features exactly what TFA argues against?
What I like about sqlite is that it's simply one file
My experience with sqlite for server-based apps has been that as your app grows, you almost always eventually need something bigger than sqlite and need to migrate anyway. For a server-based app, where minimizing deployment complexity isn't an extremely important concern, and with mixed reads and writes, it's rarely a bad idea to use Postgres or MariaDB from the start. Yes there are niche scenarios where sqlite on the server might be better, but they're niche.