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].
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.
I'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?
This 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-c...
Thing 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.
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).
Not 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.
Sqlite supports nested transactions with SAVEPOINT so each client can have their own logical transaction that can be rolled back. The outer transaction just batches the fsync effectively. So an individual client failing a transaction doesn't cause the batch to fail. But, a crash would cause the batch to fail. Because, it's a single writer, there's no rollback/retries from contention/MVCC.
You could try to imitate this in postgresql but the problem is the outer transaction does not eliminate the network hops for each inner/client transaction so you don't gain anything doing it and you still have the contention problem which will cause rollbacks/retries. You could reduce your number of connections to one to eliminate contention. But, then you are just playing sqlite's game.
An interactive transaction works like this in pseudo code.
beginTx
// query to get some data (network hop)
result = exec(query1)
// application code that needs to run in the application
safeResult = transformAndValidate(result)
// query to write the data (network hop)
exec(query2, safeResult)
endTx
How would you batch this in postgres and get any value? You can nest them all in a single transaction. But, because they are interactive transactions that doesn't reduce your number of network hops.
The only thing you can batch in postgres to avoid network hops is bulk inserts/updates.
But, the minute you have interactive transactions you cannot batch and gain anything when there is a network.
Your best bet is to not have an interactive transaction and port all of that application code to a stored procedure.
> How would you batch this in postgres and get any value? You can nest them all in a single transaction. But, because they are interactive transactions that doesn't reduce your number of network hops.
you can write it as stored procedure in your favorite language, or use domain socket where communication happens using through shared memory buffs without network involved.
In your post, I think big performance hit for postgres potentially comes from focus on update only statement, in SQlite updates likely happen in place, while postgress creates separate record on disk for each updated record, or maybe some other internal stuff going on.
Your benchmark is very simplistic, it is hard to tell what would be behavior of SQlite if you switch to inserts for example, or many writers which compete for the same record, or transaction would be longer. Industry built various benchmarks for this, tpc for example.
Also, if you want readers understand your posts better, you can consider using less exotic language in the future. Its hard to read what is and how is batched there.
> 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!”
> 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.
> 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.
May be an "out" there question, but any tech book suggestions you'd recommend that can teach an average dev on how to build highly performant software with minimal systems?
I feel like the advice from people with your experience is worth way way way way more than what you'd hear from big tech. Like what you said yourself, big tech tends to recommend extremely complicated systems that only seem worth maintaining if you have a trillion dollar monopoly behind it.
Not specific books per say. Though I'd advise starting with some constraints. As that really helps you focus.
Your reading/learning material can spin out of those constraints.
So for me my recent constraints were:
1. Multiplayer/collaborative web apps built by small teams.
2. Single box.
3. I like writing lisp.
So single box pushes me towards a faster language, and something that's easy to deploy. Go would be the natural choice here, but I want a lisp so Clojure is probably the best option here (helps that I already know it). JVM is fast enough and has a pretty good deployment story. Multiplayer web apps, pushed me to explore distributed state vs streaming with centralised state. This became a whole journey which ended with Datastar [1]. Thing is immediate mode streaming HTML needs your database queries to be fast and that's how I ended up on SQLite (I was already a fan, and had used it in production before), but the constraints of streaming HTML forced me to revisit it in anger.
Your constraints could be completely different. They could be:
1. Fast to market.
2. Minimise risk.
3. Mobile + Web
4. Try something new.
Fast to market might mean you go with something like Rails/Django. Minimise risk might mean you go with Rails because you have a load of experience with it. Mobile + web means you read up on Hotwire. Try something new might mean you push more logic into stored procedures and SQL queries so you can get the most out of Postgres and make your Rails app faster. So you read The Art of Postgresql [2] (great book). Or maybe you try hosting rails on a VPS and set up/manage your own postgres instance.
A few companies back mine were:
1. JVM but with a more ruby/rails like development experience.
2. Mobile but not separate iOS/Android projects.
3. Avoid the pain of app store releases.
4. You can't innovate everywhere.
That meant Clojure. React native. Minimal clients with as much driven from the backend as possible. Sticking to postgres and Heroku because it's what we knew and worked well enough.
Backups, litestream gives you streaming replication to the second.
Deployment, caddy holds open incoming connections whilst your app drains the current request queue and restarts. This is all sub second and imperceptible. You can do fancier things than this with two version of the app running on the same box if that's your thing. In my case I can also hot patch the running app as it's the JVM.
Server hard drive failing etc you have a few options:
1. Spin up a new server/VPS and litestream the backup (the application automatically does this on start).
2. If your data is truly colossal have a warm backup VPS with a snapshot of the data so litestream has to stream less data.
Pretty easy to have 3 to 4 9s of availability this way (which is more than github, anthropic etc).
My understanding is litestream can lose data if a crash occurs before the backup replication to object storage. This makes it an unfair comparison to a Postgres in RDS for example?
Last I checked RDS uploads transaction logs for DB instances to Amazon S3 every five minutes. Litestream by default does it every second (you can go sub second with litestream if you want).
> Backups, litestream gives you streaming replication to the second.
You seem terribly confused. Backups don't buy you high availability. At best, they buy you disaster recovery. If your node goes down in flames, your users don't continue to get service because you have an external HD with last week's db snapshots.
If anything backups are the key to high availability.
Streaming replication lets you spin up new nodes quickly with sub second dataloss in the event of anything happening to your server. It makes having a warm standby/failover trivial (if your dataset is large enough to warrant it).
If your backups are a week old snapshots, you have bigger problems to worry about than 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?
> SQLite doesn't buy you uptime if you deploy your app to AWS/GCP
This is...not true of many hyperscaler outages? Frequently, outages will leave individual VMs running but affect only higher-order services typically used in more complex architectures. Folks running an SQLite on a EC2 often will not be affected.
And obviously, don't use us-east-1. This One Simple Trick can improve your HA story.
All I'm saying is that people mention HA, when there isn't a need for it or when most people are fine with some downtime.
For example,
> When AWS/GCP goes down, how do most handle HA?
When they go down, what do most do? Honestly, people still go about their day and are okay. Look how many systems do go down. What ends up happening? An article goes out that X cloud took out large parts of the internet.. and that's it.
Even when there's ways of doing it, they just go down and we accept it. I never said this doesn't go down or can't go down, it's just that it's okay and totally fine if it does.
Nonsense. You can't outrun physics. The latency across the Atlantic is already ~100ms, and from the US to Asia Pacific can be ~300ms. If you are interested in performance and you need to shave off ~200ms in latency, you deploy an instance closer to your users. It makes absolutely no sense to frame the rationale around performance if your systems architecture imposes a massive performance penalty in networking just to shave a couple of ms in roundtrips to a data store. Absurd.
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.
Do 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
This 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.
Try 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.
Yes 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?
I’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.
It'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.
I mean you can easily do 100K TPS on a M1 with sqlite and a dynamic language. With sub 100ms latency.
People don't do it because it's not fashionable (the cool kids are all on AWS with hundreds of containers, hosting thousands micro services, because that's web scale).
Well, transactions in this context are business transactions, which may involve 1 or N remote calls. Imagine checks against no fly lists, fraud detection, flight delay and so on. Speed of light is also another concern. So it’s not as simple as doing 35k TPS on a local SQL database.
True, but a lot of those checks will be against a local snapshots of the data.
> But yes, you don’t always need cool technologies.
That's kinda the irony mainframes are incredibly cool piece's of tech, just not fashionable. They have insane consistency guarantee at the instruction level. Hot swapping features etc. Features you'd struggle to replicate with the dumpster fire that is modern microservice based cloud computing.
A JIT is a double edged sword, it _can_ make your code faster, i remember in the early days of smartphone gaming, developers often had to manually "warm up" the JIT to prevent stutters during gameplay
Similar story with the GC, it's nice to have, until it causes you problems (wich it will), so you end up having to avoid using it and instead rely on manual techniques
JIT and GC aren't the panacea people make them out to be
True using a JIT without understanding it is not a panacea. Same as a GC. Same as malloc and free (you're often much better off with arena allocators).
Most JITs let you tune when and how they inline. It's also worth knowing how they works and what they can/can't inline.
You linked to monojit. Luajit is a whole other beast. I'd argue it's superior to anything in JS/JAVA/C# land (and I say that as someone with a reasonable understanding of the JVMs C2 JIT).
As an aside with low latency GCs like the JVM's ZGC trading manual memory management for no memory related security vulnerabilities is pretty appealing.
- [1] https://litestream.io
reply