3 ms·

> Would We Choose SQLite Again? Yes. For a single-server deployment with moderate write volume, SQLite eliminates an entire category of infrastructure complexit

> Would We Choose SQLite Again? Yes. For a single-server deployment with moderate write volume, SQLite eliminates an entire category of infrastructure complexity. No connection pool tuning. No database server upgrades. No replication lag. These are weird reasons. You can just install Postgres or MySQL locally too. Connection pool tuning certainly isn't anything you have to worry about for a moderate write volume. You don't ever need to upgrade the database if you don't want to, since you're not publicly exposing it. There's obviously no replication lag if you're not replicating, which you wouldn't be with a single server. The reason you don't usually choose SQLite for the web is future-proofing. If you're totally sure you'll always stay single-server forever, then sure, go for it. But if there's even a tiny chance you'll ever need to expand to multiple web servers, then you'll wish you'd chosen a client-server database from the start. And again, you can run Postgres/MySQL locally, on even the tiniest cheapest VPS, basically just as easily as using SQLite.

xnorswap 6mo ago
Yeah, it's weird "they" don't consider any middle ground between SQLite and replicated postgres cluster. Locally running database servers are massively underrated as a working technology for smaller sites. You can even easily replicate it to another server for resiliency while keeping the local performance.
talkingtab 6mo ago
This. Spinning up Postgresql is easy once you know how. Just as SQLITE3 is easy once you know how. But I can find no benefit from not just learning postgres the first time around.
- kaibee 6mo ago
  They're using AI Agents to do it in either case and using docker. There was no reason to choose SQLite.
kaibee 6mo ago
Yeah a PG Docker container is basically magic. I too went down a rabbit-hole of trying to setup a write-heavy SQLite thing because my job is still using CentOS6 on their AWS cluster (don't ask). Once I finally got enough political capital to get my own EC2 box I could put a PG docker container on, so much nonsense I was doing just evaporated.
NewEntryHN 6mo ago
It's a spectrum. Installing Postgres locally is not 100% future-proofing since you'll still need to migrate your local Postgres to a central Postres. Using Sqlite is not 0% future-proofing since it's still using the SQL standard. If the only argument for a piece of tech in comparison to another one is "future-proofing", that's pretty much acknowledging the other one is simpler to setup and maintain.
- crazygringo 6mo ago
  > It's a spectrum. For web servers specifically, no, SQLite is not generally part of that spectrum. That makes as much sense as saying that in a kitchen, you want a spectrum of knives from Swiss Army Knives to chef's knives. No -- Swiss Army Knives are not part of the spectrum. For web servers, you do have a wide spectrum of database options from single servers to clusters to multi-region clusters, along with many other choices. But SQLite is not generally part of that spectrum, because it's not client-server. > since you'll still need to migrate your local Postgres to a central Postres No you don't. You leave your DB in-place and turn off the web server part. Or even if you do want to migrate to something beefier when needed, it's basically as easy as copying over a directory. It's nothing compared to migrating from SQLite to Postgres. > since it's still using the SQL standard. No, every variant of SQL is different. You'll generally need to review every single query to check what needs rewriting. Features in one database work differently from in another. Most of the basic concepts are the same, and the basic syntax is the same, but the intermediate and advanced concepts can have both different features and different syntax. Not to mention sometimes wildly different performance that needs to be re-analyzed. > that's pretty much acknowledging the other one is simpler to setup and maintain. No it's not. What logic led you there...? They're basically equally simple to set up and maintain, but one also scales while the other doesn't. That's the point. The main advantage of SQLite has nothing to do with setup and maintenance, but rather the fact that it is file-based and can be integrated into the binary of other applications, which makes it amazing for locally embedded databases used by user-installed applications. But these aren't advantages when you're running a server. And it becomes a problem when you need to scale to multiple webservers.
  - pullshark91 6mo ago
runako 6mo ago
Have run PG, MySQL, and SQLite locally for production sites. Backups are much more straightforward for SQLite. They are running Kamal, which means "just install Postgres" would also likely mean running PG in a container, which has its own idiosyncrasies. SQLite is not a terrible choice here.
- crazygringo 6mo ago
  > Backups are much more straightforward for SQLite. Not sure how? All of them can be backed up with a single command. But if you want live backups (replication) as opposed to daily or hourly, SQLite is the only one that doesn't support that.
  - MitziMoto 6mo ago
    Litestream exists?
    - crazygringo 6mo ago
      That's a third-party tool. It's not part of SQLite. And it's a pretty hacky usage of the WAL. If it works for you, great, but if I need replication, I'm going to want a database that supports it natively.
- jbrogers63 6mo ago
  "On our t3.small (2GB RAM), the web container uses ~780MB. Spawning an exec container for a quick database query adds another ~500MB. Two concurrent exec calls during a deploy? That's 2.5GB on a 2GB machine. OOM killer takes the web process down." I suspect this is the reason for SQLite and not PG or some other DB. From my read of the article, it sounds like they're running lean (single host for web, db, etc). PG et. al. add overhead that they likely can't (or more likely won't want to) afford on that box. Maybe I'm wrong?
MattRogish 6mo ago
Yeah, the cost - both operationally and coding-wise - of running pgsql in some cloud dwarfs the cost of lost orders. "We'll just deploy less often" is tribal knowledge that will absolutely be forgotten at some point and maybe there'll be more than two lost orders. Just setup postgresql.

xnorswap 6mo ago

Yeah, it's weird "they" don't consider any middle ground between SQLite and replicated postgres cluster. Locally running database servers are massively underrated as a working technology for smaller sites. You can even easily replicate it to another server for resiliency while keeping the local performance.

talkingtab 6mo ago

This. Spinning up Postgresql is easy once you know how. Just as SQLITE3 is easy once you know how. But I can find no benefit from not just learning postgres the first time around.

kaibee 6mo ago
They're using AI Agents to do it in either case and using docker. There was no reason to choose SQLite.

kaibee 6mo ago

Yeah a PG Docker container is basically magic. I too went down a rabbit-hole of trying to setup a write-heavy SQLite thing because my job is still using CentOS6 on their AWS cluster (don't ask). Once I finally got enough political capital to get my own EC2 box I could put a PG docker container on, so much nonsense I was doing just evaporated.

NewEntryHN 6mo ago

It's a spectrum. Installing Postgres locally is not 100% future-proofing since you'll still need to migrate your local Postgres to a central Postres. Using Sqlite is not 0% future-proofing since it's still using the SQL standard. If the only argument for a piece of tech in comparison to another one is "future-proofing", that's pretty much acknowledging the other one is simpler to setup and maintain.

crazygringo 6mo ago
> It's a spectrum. For web servers specifically, no, SQLite is not generally part of that spectrum. That makes as much sense as saying that in a kitchen, you want a spectrum of knives from Swiss Army Knives to chef's knives. No -- Swiss Army Knives are not part of the spectrum. For web servers, you do have a wide spectrum of database options from single servers to clusters to multi-region clusters, along with many other choices. But SQLite is not generally part of that spectrum, because it's not client-server. > since you'll still need to migrate your local Postgres to a central Postres No you don't. You leave your DB in-place and turn off the web server part. Or even if you do want to migrate to something beefier when needed, it's basically as easy as copying over a directory. It's nothing compared to migrating from SQLite to Postgres. > since it's still using the SQL standard. No, every variant of SQL is different. You'll generally need to review every single query to check what needs rewriting. Features in one database work differently from in another. Most of the basic concepts are the same, and the basic syntax is the same, but the intermediate and advanced concepts can have both different features and different syntax. Not to mention sometimes wildly different performance that needs to be re-analyzed. > that's pretty much acknowledging the other one is simpler to setup and maintain. No it's not. What logic led you there...? They're basically equally simple to set up and maintain, but one also scales while the other doesn't. That's the point. The main advantage of SQLite has nothing to do with setup and maintenance, but rather the fact that it is file-based and can be integrated into the binary of other applications, which makes it amazing for locally embedded databases used by user-installed applications. But these aren't advantages when you're running a server. And it becomes a problem when you need to scale to multiple webservers.
- pullshark91 6mo ago

runako 6mo ago

Have run PG, MySQL, and SQLite locally for production sites. Backups are much more straightforward for SQLite. They are running Kamal, which means "just install Postgres" would also likely mean running PG in a container, which has its own idiosyncrasies. SQLite is not a terrible choice here.

crazygringo 6mo ago
> Backups are much more straightforward for SQLite. Not sure how? All of them can be backed up with a single command. But if you want live backups (replication) as opposed to daily or hourly, SQLite is the only one that doesn't support that.
- MitziMoto 6mo ago
  Litestream exists?
  - crazygringo 6mo ago
    That's a third-party tool. It's not part of SQLite. And it's a pretty hacky usage of the WAL. If it works for you, great, but if I need replication, I'm going to want a database that supports it natively.
jbrogers63 6mo ago
"On our t3.small (2GB RAM), the web container uses ~780MB. Spawning an exec container for a quick database query adds another ~500MB. Two concurrent exec calls during a deploy? That's 2.5GB on a 2GB machine. OOM killer takes the web process down." I suspect this is the reason for SQLite and not PG or some other DB. From my read of the article, it sounds like they're running lean (single host for web, db, etc). PG et. al. add overhead that they likely can't (or more likely won't want to) afford on that box. Maybe I'm wrong?

MattRogish 6mo ago

Yeah, the cost - both operationally and coding-wise - of running pgsql in some cloud dwarfs the cost of lost orders. "We'll just deploy less often" is tribal knowledge that will absolutely be forgotten at some point and maybe there'll be more than two lost orders. Just setup postgresql.