How (and why) to run SQLite in production • Hacker News Board

Re: How (and why) to run SQLite in production

#31

Post by yread » Wed, Mar 27, 2024, 7:52 AM UTC

skybrian wrote: post #14

I 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.

Re: How (and why) to run SQLite in production

#32

Post by ojosilva » Wed, Mar 27, 2024, 8:45 AM UTC

> 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 as an intricate file format for which your app will use an API (ie sqlite.c) to access.

Re: How (and why) to run SQLite in production

#33

Post by mythz » Wed, Mar 27, 2024, 8:52 AM UTC

We're also defaulting to using SQLite first for our new Apps with Simplicity, Speed, Cost and flexibility being the motivating factors.

Not needing a managed database also allows us to move away from AWS onto much better value Hetzner servers, much love to Litestream [1] which makes replication to R2/S3 effortless. It's a silly 30-40x cheaper hosting SQLite Web Apps on Hetzner compared to the "recommended" managed DB configurations on AWS/Azure.

[1] https://docs.servicestack.net/ormlite/litestream

Re: How (and why) to run SQLite in production

#34

Post by klabb3 » Wed, Mar 27, 2024, 8:55 AM UTC

ojosilva wrote: post #32

> 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…

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.

Re: How (and why) to run SQLite in production

#35

Post by klabb3 » Wed, Mar 27, 2024, 9:05 AM UTC

abhiyerra wrote: post #3

We 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.

Re: How (and why) to run SQLite in production

#36

Post by vidarh » Wed, Mar 27, 2024, 9:12 AM UTC

mattmanser wrote: post #30

keyle wrote: post #28

Earlier quoted context omitted.

Before you even need to consider postgres, you can batch your writes to sqlite! I am no sqlite fanboy, although I might be, but I found the industry seems to run to postgres for just about anything. I prefer simplicity first.

How it that simplicity? To batch updates makes the code far more complex. To install any full strength DB is trivial. I don't get the 'simplicity'?

That depends entirely on what you're doing. If your workload is heavily transactional, then sure, that might add complexity.

The simplicity is not having a separate process that can fail, and that requires fail over, and monitoring.

Re: How (and why) to run SQLite in production

#37

Post by gwd » Wed, Mar 27, 2024, 9:15 AM UTC

skybrian wrote: post #14

I 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…

For my project I've been using Litestream to Cloudflare's R2, which is compatible with Amazon S3, and has a very generous free tier.

Re: How (and why) to run SQLite in production

#38

Post by adius » Wed, Mar 27, 2024, 9:21 AM UTC

abbbi wrote: post #11

duped wrote: post #10

Earlier quoted context omitted.

What's wrong with PRAGMA journal_mode=WAL? It enables concurrent writes, at the expense of disk. Which to my understanding is the same as any other database backend with write ahead logging to enable concurrent writers. Anecdotally, I've seen the WAL file grow way too large even after all writes have finished and should shrink, but that's manageable.

WAL does not help with "database locked" situations. At some point you will see them even with WAL enabled, and your application frontend code has to deal with the timeout and retry or whatever.

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

Re: How (and why) to run SQLite in production

#39

Post by jddj » Wed, Mar 27, 2024, 9:22 AM UTC

skybrian wrote: post #14

I 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…

Wasabi should give you a lot more storage for almost the same price

Re: How (and why) to run SQLite in production

#40

Post by jddj » Wed, Mar 27, 2024, 9:29 AM UTC

Seems 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.