7 ms·
Sqlite smokes postgres on the same machine even with domain sockets [1]. This is before you get into using multiple sqlite database. What features postgres off
by andersmurphy 6mo ago
Sqlite smokes postgres on the same machine even with domain sockets [1]. This is before you get into using multiple sqlite database.
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-billion-rows-the-unreasonable-effectiveness-of-sqlite.html https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
- [2] https://sqlite.org/appfunc.html https://sqlite.org/appfunc.html
- [3] https://litestream.io/ 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.
- locknitpicker 6mo ago> Sqlite smokes postgres on the same machine even with domain sockets [1]. 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.
- rpdillon 6mo agoI wonder what percentage of services run on the Internet exceed a few hundred transactions per second.
- egwor 6mo agoI think the better question to ask is what services peak at a few hundred transactions per second?
- icedchai 6mo agoI’ve seen multimillion dollar “enterprise” projects get no where close to that. Of course, they all run on scalable, cloud native infrastructure costing at least a few grand a month.
- not_kurt_godel 6mo ago> a few grand a month. A negligible cost for a successful tech business that also works when your requirements exceed the capabilities of a single VPS.
- icedchai 6mo agoI agree. But these are projects that barely get any requests.
- andersmurphy 6mo ago> SQLite on the same machine is akin to calling fwrite. 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. - [1] https://sqlite.org/fasterthanfs.html https://sqlite.org/fasterthanfs.html - [2] https://checkboxes.andersmurphy.com https://checkboxes.andersmurphy.com
- wookmaster 6mo agoHow do you manage HA?
- rovr138 6mo agoNo offense, you wait. Like everyone's been doing for years in the internet and still do - When AWS/GCP goes down, how do most handle HA? - When a database server goes down, how do most handle HA? - When Cloudflare goes down, how do most handle HA? The down time here is the server crashed, routing failed or some other issue with the host. You wait. One may run pingdom or something to alert you.
- locknitpicker 6mo ago> When AWS/GCP goes down, how do most handle HA? This is a disingenuous scenario. SQLite doesn't buy you uptime if you deploy your app to AWS/GCP, and you can just as easily deploy a proper RDBMS such as postgres to a small provider/self-host. Do you actually have any concrete scenario that supports your belief?
- tl 6mo agohttps://antonz.org/sqlite-is-not-a-toy-database/ https://antonz.org/sqlite-is-not-a-toy-database/ — 240K inserts per second on a single machine in 2021. The problem you describe is real, but the TPS ceiling is wrong by three orders of magnitude on modern hardware.
- pdhborges 6mo agoDo you know why it is a toy? Because in a real prod environment after inserting 240k rows per second for a while you have to deal with the fact that schema evolution is required. Good luck migrating those huge tables with Sqlite ALTER table implementation
- devmor 6mo agoTry doing that on a “real” DB with hundreds of millions of rows too. Anything more than adding a column is a massive risk, especially once you’ve started sharding.
- pdhborges 6mo agoYes it might be risky. But most schema evolution changes can be done with no or minimal downtime even if you have to do then in multiple steps. When is a simple ALTER going to be totally unacetable if youare using Sqlite?
- shimman 6mo agoThis doesn't seem like a toy but you know... realizing different systems will have different constraints. Not everyone needs monopolistic tech to do their work. There's probably less than 10,000 companies on earth that truly need to write 240k rows/second. For everyone else, we can focus on better things.
- pdhborges 6mo ago> realizing different systems will have different constraints. I realize that. There are a few comments already that present use cases where I can totally see using Sqlite as a good option. > Not everyone needs monopolistic tech to do their work We are talking about localhost Postgres vs SQLite here. Both are open source.
- darkwater 6mo agoI mean, your "This is fine for" is almost literally the whole point of TFA, that you can go a long way, MRR-wise, with a simpler architecture.
- maccard 6mo agoThing is though - either of those options is still multiple orders of magnitude faster than running on a remote host. Either will work, either will scale way farther than you reasonably expect it to.
- noahbp 6mo agoFYI, the color gradient on your website is an easy tell that it was vibe coded: https://prg.sh/ramblings/Why-Your-AI-Keeps-Building-the-Same-Purple-Gradient-Website https://prg.sh/ramblings/Why-Your-AI-Keeps-Building-the-Same...
- andersmurphy 6mo agoA blog that's 11 years old and uses a minimalist CSS framework https://picocss.com https://picocss.com ? It's a static blog that renders markdown... there's literally nothing to code, let alone vibe code.
- noahbp 6mo agoThat’s my mistake then. That particular gradient is the visual equivalent of reading a paragraph with em-dashes and “It’s not just X, it’s Y”. This is quite the coincidence. Forgive me for assuming your website was built without attention to detail and care.
- 59nadir 6mo agoIt's funny, we're now trained to see these things where they can't possibly ever have been (like in this case with the 11 year old blog). It's as if we all collectively forgot that whatever the LLMs are doing comes from somewhere, so it's obviously going to be found out in the wild.
- andriy_koval 6mo ago> Sqlite smokes postgres on the same machine even with domain sockets [1] for inserts only into singe table with no indexes. Also, I didn't get why sqlite was allowed to do batching and pgsql was not.
- andersmurphy 6mo ago> for inserts only into singe table with Actually, there are no inserts in this example each transaction in 2 updates with a logical transaction that can be rolled back (savepoint). So in raw terms you are talking 200k updates per second and 600k reads per second (as there's a 75%/25% read/write mix in that example). Also worth keeping in mind updates are slower than inserts. > no indexes. The tables have an index on the primary key with a billion rows. More indexes would add write amplification which would affect both databases negatively (likely PG more). > Also, I didn't get why sqlite was allowed to do batching and pgsql was not. Interactive transactions [1] are very hard to batch over a network. To get the same effect you'd have to limit PG to a single connection (deafeating the point of MVCC). - [1] An interactive transaction is a transaction where you intermingle database queries and application logic (running on the application).
- andriy_koval 6mo agoThank you for clarification, I was wrong in my prev comment. > - [1] An interactive transaction is a transaction where you intermingle database queries and application logic (running on the application). could you give specific example why do you think SQlite can do batching and PG not?
- hedora 6mo agoNot the person you are responding to, but sqlite is single threaded (even in multi process, you get one write transaction at a time). So, if you have a network server that does BEGIN TRANSACTION (process 1000 requests) COMMIT (send 1000 acks to clients), with sqlite, your rollback rate from conflicts will be zero. For PG with multiple clients, it’ll tend to 100% rollbacks if the transactions can conflict at all. You could configure PG to only allow one network connection at a time, and get a similar effect, but then you’re paying for MVCC, and a bunch of other stuff that you don’t need.
- eduction 6mo ago> What features postgres offers over sqlite in the context of running on a single machine with a monolithic app The same thing SQL itself buys you: flexibility for unforeseen use cases and growth. Your SQLite benchmark is based in having just one write connection for SQLite but all eight writable connections for Postgres. Even in the context of a single app, not everyone wants to be tied down that way, particularly when thinking how it might evolve. If we know our app would not need to evolve we could really maximize performance and use a bespoke database instead of an rdbms. It seems a little aggressive for you to jump on a comment about how it’s reasonable to run Postgres sometimes with “SQLite smokes it in performance.” That’s true, when you can accept its serious constraints. As a wise man once said, “Postgres is great and there's nothing wrong with using it!”
- tikotus 6mo agoI've slowly evolved from just writing to and looking up json files to using SQLite, since I had to do a bit more advanced querying. I'm glad I did. But the defaults did surprise me! I'm using it with php, and I noticed some inserts were failing. Turns out there's no tolerance for concurrent writes, and there's no global config that can be changed. Rertry/timeout has to be configured per connection. I'm still not sure if I'm missing something, since this felt like a really nasty surprise, since it's basically unusable by default! Or is this php's PDO's fault?
- formerly_proven 6mo agoThis is the fault/price of backwards compatibility. Most users of SQLite should just fire off a few pragmas on each connection: PRAGMA journal_mode = WAL PRAGMA foreign_keys = ON # Something non-null PRAGMA busy_timeout = 1000 # This is fine for most applications, but see the manual PRAGMA synchronous = NORMAL # If you use it as a file format PRAGMA trusted_schema = OFF You might need additional options, depending on the binding. E.g. Python applications should not use the defaults of the sqlite3 module, which are simply wrong (with no alternative except out-of-stdlib bindings pre-3.12): https://docs.python.org/3/library/sqlite3.html#transaction-control https://docs.python.org/3/library/sqlite3.html#transaction-c... Also use strict tables. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- SomeUserName432 6mo ago> PRAGMA journal_mode = WAL Pretty sure this is a persistent setting. Don't need to set it per-connection.
- panny 6mo agoInteresting comparison. Have you done one for sqlite vs H2? Since you're using clojure, it seems a natural fit. According to what I've read, H2 is faster than sqlite, but it would be interesting to see some up to date numbers on it.
- andersmurphy 6mo agoThat's a good point. I'll give that a go and do a write up at some point. The choice to use SQLite for me isn't actually about speed (LMDB is way faster). I just get tired of people saying switch to Postgres for speed/scale. Postgres is great for many things but speed/scale is not its strength. The main reason I use SQLite is the affordances and conveniences of an embedded database. Being able to easily have many databases. When databases are "cheap" it opens up loads of options. Of the embedded/file OLTP databases SQLite also has the largest toolbox: litestream, R*Tree indexes, JSONB, FTS, etc.
- panny 6mo agoI haven't tried sqlite because the lack of data types is off putting to me. I want to like derby because they have gone to the trouble of making their database JPMS modular. But derby doesn't have UUID types and I want UUID types. Especially with Java 26 adding UUIDv7. I end up on H2 as a result.
- andersmurphy 6mo agoFor what it's worth blob types and application functions make it pretty straight forward to implement your own datatypes. I often have one for EDN and one for bigdecimal.