> The enterprise mindset dictates that you need an out-of-process database server. But the truth is, a local SQLite file communicating over the C-interface or memory is orders of magnitude faster than making a TCP network hop to a remote Postgres server.
I don't want to diss SQLite because it is awesome and more than adequate for many/most web apps but you can connect to Postgres (or any DB really) on localhost over a Unix domain socket and avoid nearly all of the overhead.
It's not much harder to use than SQLite, you get all of the Postgres features, it's easier to run reports or whatever on the live db from a different box, and much easier if it comes time to setup a read replica, HA, or run the DB on a different box from the app.
I don't think running Postgres on the same box as your app is the same class of optimistic over provisioning as setting up a kubernetes cluster.
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.
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.
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...
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.
Interesting 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.
> 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 love them both too but that might not be the best metric unless you’re planning to run lots of little read queries. If you’re doing CRUD, simulating that workflow may favor Postgres given the transactional read/write work that needs to take place across multiple concurrent connections.
Not sure that’s always right either though. For example Mapbox used to use an SQLite database as the disk cache for map tile info. You cannot possibly store that amount of data in memory, so it’s a great use case.
This is mostly about thread communication. With SQLite you can guarantee no context switching. Postgres running on the same box gets you close but not all the way. It's still in a different process.
You appear to have benchmarked how quickly Python can access memory vs how quickly Python can open and close a socket. I'm not sure the DBs were required in any way.
The original claim being discussed is about the overhead of an in-process database vs. a database server in a separate process, not about whether SQLite or PostgreSQL have a faster database engine.
I have used SQLite with extensions in extreme throughput scenarios. We’re talking running through it millions of documents per second in order to do disambiguation.
I won’t say this wouldn’t have been possible with a remote server, but it would have been a significant technical challenge.
Instead we packed up the database on S3, and each instance got a fresh copy and hammered away at the task. SQLite is the time tested alternative for when you need performance, not features
> It's not much harder to use than SQLite, you get all of the Postgres features, it's easier to run reports or whatever on the live db from a different box, and much easier if it comes time to setup a read replica, HA, or run the DB on a different box from the app.
Isn't this idea to spend a bit more effort and overhead to get YAGNI features exactly what TFA argues against?
> It's not much harder to use than SQLite, you get all of the Postgres features [..]
More features is a net negative if you don't need those features. Ideally you want your DB to support exactly what you need and nothing more. Not typically realistic but the closer you can get the better.
A feature you don't think you need today, might be one you actually need tomorrow. It would be short-sighted to choose some tech based only on what you need today. If the extra features don't cost you anything, I can't see that as a "net negative".
I'm not anticipating everything ahead of time, I'm choosing mature tech that does what I need and then some. There's no misstep there. The real problem I've seen with choosing software tech is "new, shiny".
That's fine, so long as your aware of the costs of that decision.
That choice is what tends towards a"minimum prod deployment" consisting of something like a pair of app servers behind a load balance with a pair of redundant databases behind them and usually some sort of object store as well. Assuming you engineer you app and db schemas sanely, you can reliably get four nine out of a setup like that. On AWS that looks like an aELB with two EC2 instances and a multiAZ RDS with a few S3 buckets. At on demand rates with .medium ec2 and rds instances that runs about $150/month - or maybe as low as $100 with reserved instances. You could probably deploy that for $60 or so on something like Linode or Digital ocean.
That's usually fine when you're spending someone else's money, and they're fully-but-incorrectly expecting their idea to need Netflix or Facebook scale within six months.
|||||If it's _my_ money, something "less scalable" that runs closer to $20/month that will easily support enough traffic to prove product market fit and generate enough sales/profit to suppot both otself and a team to rebuikd it when/if it ever needs it is a really sensibly approach.
For most things I personally use AWS Lambda and S3, with some SimpleDB. If I needed something more in the DB department, I'd probably use DynamoDB, but S3 actually works well for most of the data I need to store. I pay about $0.50/mo and most of that is S3 cost for media files. I don't have a lot of users, but even if I did I'd still be paying way less than $20/mo. And when I do have a lot of users, I don't have to worry about scaling anything (and that will happen soon). I think the first million Lambda invocations are free, and if/when I start paying for Lambda invocations, the AWS costs will definitely be covered easily by user subscriptions.
I also use pretty much the same stack at work, where we currently have about 150,000 users. The cost is about $15/day, mostly in S3 costs, we have a lot more data at my day job. We also do use a few hundred EC2 instances in short bursts, and the VPC has stupid high costs, and other costs add up, but it's probably about 1% of what the rest of the teams cost that run on EC2 and Postgres and other AWS tech.
You can't simply copy/paste a Postgres database though...also you'd be surprised how fast SQLite can be...I've used SQLite for projects where I just couldn't get the performance elsewhere. For example, I had a names database with over 100 million rows in it for converting names to diminutives (e.g. David to Dave) and the inverse...after I precomputed a metric ton of indices it went like a rocket. Sure the file was quite big but oh boy was it quick.
Thats just swapping another enterprise focused concern into the mix. Your database connection latency is absolutely not a concerning part of your system.
I mean, you’re not wrong about the facts, but it’s also pretty trivial to migrate the data from SQLite into a separate Postgres server later, if it turns out you do need those features after all. But most of the time, you don’t.
So you are migrating from Sqlite to Postgres because you need it. What is the state of your product when you need to do this migration? Is your product non trivial? Are you now dependent on particular performance characteristics of Sqlite? Do you now need to keep your service running 24/7? Accounting for all of that takes way more than 5 minutes. The only way to beat that is if you still have a toy product and you can just export the database and import it and pray that it all works as a migration strategy.
IIRC TCP/IP through localhost actually benchmarked faster than Unix sockets because it was optimized harder. Might've been fixed now. Unix sockets gives you the advantage of authentication based on the user ID of who's connecting.
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.
> If you need a little breathing room, just use a swapfile.
You should always use a swap file/partition, even if you don't want any swapping. That's because there are always cold pages and if you have no swap space that memory cannot be used for apps or buffers, it's just wasted.
I always thought I had to add a swap file to avoid crashing with OOM. I wasn't aware of the cold pages overhead.
Sometimes that crashing is what I want: a dedicated server running one (micro)service in a system that'll restart new servers on such crashes (e.g. Kubernetes-alike). I'd rather have it crash immediately rather than chugging along in degraded state.
But on a shared setup like OP shows, or the old LAMP-on-a-vps, i'd prefer the system to start swapping and have a chance to recover. IME it quite often does. Will take a few minutes (of near downtime) but will avoid data corruption or crash-loops much easier.
Basically, letting Linux handle recovery vs letting a monitoring system handle recovery
F# but not Scala? In TFA F# is only lacking in community and ecosystem. Scala is comparable in popularity to Go or Rust and has access to the entire Java ecosystem and JVM runtime. That should give it the five stars the author is looking for but its not considered.
I think a lot of devs are missing out by not considering Scala 3. I use it with the Zio effect system which brings composable concurrency and transactional memory amongst other features. I don't think there is anything comparable in any of the languages listed in TFA.
Not good ones, and Scala devs are keenly aware they have been going in the wrong direction compared to Go/Rust, in part because of articles like this.
RedMonk shows Scala is comparable to Go and Rust [0] You can see in this chart which plots the number of projects on Github and tags on StackOverflow (ha ha.)
The upper right most cluster has the most popular languages (C++, Java, Python, JS, PHP, TypeScript) then the next cluster has Scala with Rust, Go, Kotlin, R, Swift, etc... That cluster is clearly separate from the next less popular one which has Haskell, Lua, Ocaml, Groovy, Erlang, Fortran, etc... and then you can see the long tail is a big cluster covering the entire lower left half of the chat with a clear gap between it and the upper right half.
I don't think it is a "very, very wrong" statement.
Grateful Dead has analog reel-to-reel recordings going back to the 60's but most of those have been digitized already or are in the Deads vault.
There are also large collections of recordings on Betamax cassettes made with Sony PCM-F1 digital front-ends which were used before DAT become available. These are digitized versions of old analog recordings and original digital recordings from the 80's. They need transferring and sample rate conversion (they are 44.056kHz) and in some cases pre-emphasis removal.
There is also a lot of digital material on DAT cassettes including analog transfers and digital recordings from the 90s. There are also some CD-Rs where original sources can't be found.
A lot of the cleanup is just figuring out what comes from what show and substituting sources where there are gaps to make complete versions for listening. The archival nature of the endeavor usually limits the amount of "clean up" that is done.
> urgently need to innovate to grow their revenue streams
No, people are saying that Firefox needs to diversify their revenue streams because almost all of their revenue comes from their main competitor who (likely) only keeps Firefox alive to keep regulators from forcing them to divest their browser. The situation has gotten more dire since the regulators got fired last year.
You're basically restating the very argument I'm citing, but phrasing it like you're expressing a disagreement. Diversifying revenue and growing revenue are distinct but overlapping, and both charges are made against Mozilla. This represents one side of the quantum accusation, the other being that even their search revenue is excessive and unnecessary, they don't need to spend that much anyway. According to this perspective, the 1.2 billion they have on hand should be enough to finance, development in perpetuity.
Which side of the quantum accusation will be invoked in any given comment thread? Flip a coin and find out.
reply