I'm creating a flutter app, I need a database but don't need online storage, local only. SQLite really is the only option for this.
Firebird is sometimes an option.
How (and why) to run SQLite in production
61–70 of 85 posts
Re: How (and why) to run SQLite in production
#62Earlier 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.
Tested it repeatedly on Hetzner CPX11 (2 vCPU, 2GB RAM) instance, got one lock around row 49000, then it resumed until the end.
Re: How (and why) to run SQLite in production
#63I 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…
I use Litestream for my production web app and stream to Backblaze B2. It's S3 compatible and literally free!
Re: How (and why) to run SQLite in production
#64> 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…
It's also not just "a file on disk". I know this because I re-read the docs about locking last night: https://www.sqlite.org/lockingv3.html
Between this and WAL, which-ever one you pick, you have the following caveats:
- You aren't supposed to use a SQLite DB through a hard or soft link
- By default, in rollback journal mode, it will create a temporary `-journal` file during every write
- If you're doing atomic transactions on multiple DBs, it also creates a super-journal file
- You definitely can't `cp` a database file if it's in use, the copy may be corrupt
- It relies on POSIX locks to cooperate with other SQLite threads / processes, the docs advise that locks don't work right in many NFS implementations
- In WAL mode it needs a `-wal` and a `-shm` file, and I believe the use of shared memory makes it extra impossible to run it over NFS
SQLite is not simple, it's like a million lines of code. It is as simple as a DB can be while still implementing SQL and ACID without any network protocol.
I am thinking of writing a toy DB for fun and frankly I am not sure if I want to start with SQLite's locking and pager or just make an on-demand server architecture like `sccache` uses. Then the only lock I'd have to worry about is an exclusive lock on the whole DB file.
Re: How (and why) to run SQLite in production
#65Since the page didn't seem to link to it, here's a really great article about using SQLite in production for moderately-sized web apps: https://blog.wesleyac.com/posts/consider-sqlite
That article, along with the ability to have streaming backups with Litestream and not wanting to pay for a separate DB server, inspired me to use SQLite in my SaaS four years ago. I've been enjoying the operational simplicity a lot although there is not a lot of community documentation currently about tuning SQLite for web app loads. My app does about 120m hits a month (mostly cached and not hitting the DB) on a $14/month single-processor DigitalOcean droplet.
Re: How (and why) to run SQLite in production
#66I 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.
this comments seems to say, that you could corrupt your copy, if you copy it while it is in use https://news.ycombinator.com/item?id=39838753
Re: How (and why) to run SQLite in production
#67Postgres. 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
> 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
Anecdote: the sqlite forum runs on a single sqlite db and has well over 1500 users. Similarly, the sqlite source site is heavily visited by thousands of folks and actively used in write mode by its developers. It sibling project, the Fossil SCM, also runs entirely from a single sqlite db.
sqlite db forum stats: https://sqlite.org/forum/reports>
Re: How (and why) to run SQLite in production
#68Postgres. 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
But n can be so big that it you never run into it. My company has a read-heavy SQLite-backed service that serves roughly 150,000 daily users with p99.9 at about 5 milliseconds. We did some rough projection and determined we could reach the total addressable market of our product in the US without even approaching having a problem.
Re: How (and why) to run SQLite in production
#69Earlier quoted context omitted.
You just need to set busy_timeout > 0, and you'll basically never have database locked situations. It's just unfortunate, that it's not activated per default: https://sqlite.org/forum/info/7e456bf5544ab128
yeah, but that will slow down your app and may help in a short run, but you basically just shift the problem and make it even worse if your workload gets even higher.
Then use a different busy handler
Re: How (and why) to run SQLite in production
#70Earlier quoted context omitted.
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.
this comments seems to say, that you could corrupt your copy, if you copy it while it is in use https://news.ycombinator.com/item?id=39838753
The ".backup" mechanism is safe - it's directly copying the file with "cp" that doesn't work.
Here's how to use .backup:
sqlite3 data.db ".backup 'backup.db'"
sqlite3 data.db 'VACUUM INTO "backup.db";'