Postgres. Only postgres.
Why? Because once your app gets more than _n_ users you will run into scaling problems and then you end up with huge tech debt
How (and why) to run SQLite in production
51–60 of 85 posts
Re: How (and why) to run SQLite in production
#52We recently moved some of our cron jobs that needed a database backend to GitHub Actions + Cloudflare D1 which is SQLite in the Cloud and couldn’t be happier.
TIL D1 is SQLite. Why don’t they market it more prominently that way? If their sql is interoperable with SQLite it’s a huge selling point, both for reducing vendor lock-in and compatibility with existing frameworks and such.
TIL for me too. Looks like it's in beta still so maybe not fully marketable yet.
Re: How (and why) to run SQLite in production
#53> First is that SQLite is simple. The database is a literal file on disk. The engine is a single executable. No, it's not a single executable. There's no database executable as far as your app goes. There's no engine. The "engine" is a library, meant to be embedded into other code and may be concurrent and have many "executables" if you implement it that way or have different apps access the database. Think of SQLite…
There are ODBC drivers for SQLite. It can make sense when you want to offer SQLite as an option alongside other, more traditional DBMS. If your queries and data are relatively simple, it will probably work just fine.
ODBC drivers for SQLite are just a thin abstraction over the C library.
Re: How (and why) to run SQLite in production
#54Seems as good a place as any to put this question: has anyone had any issues with Litestream? I've got a more basic backup solution running currently and don't want to put another moving piece in the way of a production service unless it's extremely solid, but I do like the idea.
I've been using it for a couple different production apps, and it has been great. No problems for me.
Re: How (and why) to run SQLite in production
#55Might want to proofread it. There are a few paragraphs that are repeating and out of place: > Why? So, let’s explore that together. Who here is running or has run an application in production with SQLite? Who has experimented with SQLite for an app, but not shipped it to production? There are a couple hands up, but not many. So, let’s turn this question around.
Yep, that was an errant duplication. I did proofread, but how is it that one thing always seems to sneak through anyway? Thanks for the catch. Fixing now.
Re: How (and why) to run SQLite in production
#56It's not immediately clear. How does this work over the network for multiple app servers (or does it)? Is the DB hosted on something like nfs, or are writes synced to all app servers or something else?
This presentation is focused on the use-case of vertically scaling a single server and driving everything through that app server, which is running SQLite embedded within your application process.
This is the sweet-spot for SQLite applications, but there have been explorations and advances to running SQLite across a network of app servers. LiteFS (https://fly.io/docs/litefs/), the sibling to Litestream for backups (https://litestream.io), is aimed at precisely this use-case. Similarly, Turso (https://turso.tech) is a new-ish managed database company for running SQLite in a more traditional client-server distribution.
Re: How (and why) to run SQLite in production
#57Earlier quoted context omitted.
Yes. If you write your server in eg Go or Rust, you can have a “single executable deployment” so to say. But the main limitation is no horizontal scalability. So if you want web scale, you have to use something faster, like /dev/null.
Webscale always reminds me of this video [1] - MongoDB is webscale. [1] - https://www.youtube.com/watch?v=b2F-DItXtZs edit: and now I believe you are directly referencing this but may be useful for others who don't get the reference
> and now I believe you are directly referencing this
Yep haha.
> but may be useful for others who don't get the reference
Probably also yes. Important lore for techno-archeologists.
Re: How (and why) to run SQLite in production
#58Earlier quoted context omitted.
In my experience most SQLite writes take less than 1ms. Do your writes really need to be concurrent if they run that fast? Hard to get upset about waiting for the current write to complete before you get your turn when we are talking delays measured in thousandths of a second. If you have more than 1000 writes per second then maybe this is something to worry about. The solution there is probably to run a slightly mor…
back in the days where we hit this issue (mostly on windows systems) i used to create a little stress tool, you would be surprised how fast you reach the database-locked state. ive just put it here: https://github.com/abbbi/sqlitestress maybe its useful for some people to simulate their workloads.
And once its locked do you have to hold all operations for X ms?
Re: How (and why) to run SQLite in production
#59I went through this presentation looking for "how do you do backups" and it glosses over it. But the author blogged about that separately [1]. It seems he uses Litestream with DigitalOcean Spaces for this. Looks like they start at $5 per month for 250 GB [2]. Would that be the best way for a hobbyist to get started? [1] https://fractaledmind.github.io/2023/09/09/enhancing-rails-s... [2] https://www.digitalocean.com/p…
Maybe I'm crazy but I just have a cron job that does .backup during a time with little activity. My db is only 40GB or so and stored on an NVMe so it finishes quickly.
Nowadays you're considered crazy if you don't pay rent-seekers for every simple thing.
Re: How (and why) to run SQLite in production
#60Earlier quoted context omitted.
There are ODBC drivers for SQLite. It can make sense when you want to offer SQLite as an option alongside other, more traditional DBMS. If your queries and data are relatively simple, it will probably work just fine.
ODBC drivers for SQLite are just a thin abstraction over the C library.
[deleted]