While different than the approach offered by Litestream, I am fairly excited by the direction of Cloudflare D1, making SQLite available at the edge without having to manage anything. Still in alpha but worth looking at if you're looking for cheap cloud option.
I'm all-in on server-side SQLite (2022)
21–30 of 167 posts
Re: I'm all-in on server-side SQLite (2022)
#22> We’re beginning to hit theoretical limits. In a vacuum, light travels about 186 miles in 1 millisecond. That’s the distance from Philadelphia to New York City and back. Add in layers of network switches, firewalls, and application protocols and the latency increases further.
> The per-query latency overhead for a Postgres query within a single AWS region can be up to a millisecond. That’s not Postgres being slow—it’s you hitting the limits of how fast data can travel.
No. An AWS region has a radius of less than a few dozen kilometers, more likely around 5km. Lightspeed doesn't factor into it at those small distances. That millisecond is indeed Postgres being "slow" in these terms. (Most of it is the networking stack, as noted.)
This basic error makes me question the validity of the document. I stopped reading here.
I agree that "networks are slow" but this sort of false justification is not the way to sell it. Is this an attempt to make the author seem like he knows what he is doing because he knows the speed of light?
Re: I'm all-in on server-side SQLite (2022)
#23I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that requires it. Or if you need a graph database there are better choices than Postgres.
Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that is important.
Most important difference to me being SQLite I understand lacks flexibility in modifying table structures.
Re: I'm all-in on server-side SQLite (2022)
#24I recently wrote a production system that uses SQLite as the main backend. SQLite is in memory in this case and its entire state gets rebuilt from Kafka on start. The DB receives about 2 updates a second, wrapped with rest api aiohttp and odata filters. It has been able to handle close to 9k requests/second ands it’s a primary system in a financial institution. So yes SQLite is fully capable prod db.
Re: I'm all-in on server-side SQLite (2022)
#25> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement over an intra-region Postgres query. Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing…
But why take on the operational overhead of a separate DB server (not to mention 200 more microseconds), plus the EC2 surcharge? I would rather run app+SQLite + dumb object storage, than app + MySQL + MySQL incremental backup and restore.
What separate DB server? I was talking about installing the RDBMS on localhost, right inside the server where your application runs. No other EC2 instance, no extra charges. Preferably connect to it over a Unix domain socket instead of TCP. That's the only way to compare SQLite performance with an RDBMS in an apples-to-apples way.
The point about operational overhead makes sense, though, and IMO it's the only point in this unnecessarily long article that's actually worth considering. I do have a couple of other apps running on SQLite, so I appreciate the simplicity.
Re: I'm all-in on server-side SQLite (2022)
#26Been feeling a little miffed about this recently. Litestream is excellent but if you have multiple writers your db gets corrupted. Quite easy to do with rolling deploys. LifeFS was announced and is intended to help this. Now seems like ( https://fly.io/docs/litefs/getting-started-fly/ ) it requires an HTTP proxy so that the application can guess about sqlite write/read usage by reading the HTTP request method. This s…
> if you have multiple writers
Our strategy is to not attempt replication at the level of SQLite. We use a single binary for our SaaS product which shares 1 SQLiteConnection instance for the lifetime of the whole ordeal. Remember - every single SQLite connection instance is a file system abstraction, not some in-memory/networking clever optimized thing that Postgres or SQL Server is managing on your behalf. Every time you open a new connection to SQLite you are doing some pretty heavy-duty OS calls, relative to just reusing a prior connection. SQLite itself is typically built with serialization on by default, which deals with multiple threads on one connection. In my experience, this is the most stable & performant arrangement (with WAL, et. al. also enabled).
Our backup solution is to snapshot the entire VM (or block storage device) that SQLite is running on. Replication is not a concern because our restore strategy is to just bring back a snapshot if required. Our customers are ultimately responsible for this and typically handle it with a few clicks through AWS, Azure or a quick email to their private cloud provider. RPO and RTO is entirely in their court and all parties prefer it this way - them being highly-regulated banks and us being a small startup operating at the edge of the abyss.
To this day, we have not once had to support recovery of a SQLite database from snapshot due to corruption or other weirdness. We've been at it for half a decade now.
Re: I'm all-in on server-side SQLite (2022)
#27I hope fly is able to make it. I’m rooting for them - however - I’m starting to wonder if the SQLite push isn’t more “this is fun and interesting to build” and less “customers want this”. Don’t get me wrong - this is neat - but I’d never suggest anyone to actually use this outside of a fun experiment. The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or…
I think you're skipping the replicated read only use case, which is our use case, and it's super handy there. but i understand this is a restricted scenario where little could really go wrong, and it could be done other ways.
Re: I'm all-in on server-side SQLite (2022)
#28I hope fly is able to make it. I’m rooting for them - however - I’m starting to wonder if the SQLite push isn’t more “this is fun and interesting to build” and less “customers want this”. Don’t get me wrong - this is neat - but I’d never suggest anyone to actually use this outside of a fun experiment. The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or…
Lots of smaller businesses could do fine with this if they don't have a write-heavy workload. Like an ecomm shop, for instance.
Re: I'm all-in on server-side SQLite (2022)
#29I’m bullish on SQLite, and this is mostly a great article, but this kind of stuff is flat-out misleading:
> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds.
As if postgres and others don’t have a way to run application logic at the database. I like the SQLite way of doing it — you pretty much freely choose your own host language — anything with a decent SQLite client will work. While in postgres, for example, you’ll probably end up with pgplsql (there are others, but there are constraints). So this isn’t about latency, as the whole section of the article suggests.
There’s actually a relative weakness in SQLite here, since it doesn’t include a built-in protocol to support running application logic separate from the database. That’s also architecturally useful, and so you may have to find/build a solution for this.
Just adding replicas isn’t a general solution either, because each replica has an inherent cost: changes have to somehow get to every replica.
E.g., systems can grow to have a lot of database clients. In traditional setups you begin to struggle with the number of connections. You might think with SQLite, “hey, no connections, to problems!” but now, instead of 1000 connections you’ve got 1000 replicas. That’s something you’re going to have to deal with… that’s 1000x write load, 1000x write bandwidth.
Perhaps fly.io has a solution for this, but I suspect it’s going to cost you.
Re: I'm all-in on server-side SQLite (2022)
#30> We’re beginning to hit theoretical limits. In a vacuum, light travels about 186 miles in 1 millisecond. That’s the distance from Philadelphia to New York City and back. Add in layers of network switches, firewalls, and application protocols and the latency increases further. > The per-query latency overhead for a Postgres query within a single AWS region can be up to a millisecond. That’s not Postgres being slow—it…
If he really understood what he's talking about, he would at least say "half the speed of lights" because that's the max speed a fiber cable will ever go.