Sqlite in Production

Chat / Discussions
1

(Moved to separate topic from: Lower the Initial Barrier by using ETS and not PostgreSQL for Phoenix Examples )

FWIW I’ve seen sqlite and related projects (e.g. litestream) getting rave reactions on HN the past 1.5 year or so (even today there’s such discussion), and what I find intriguing in many of the comments I read was people using it in production. So much discussion in the tech community comes from startups and Big Tech who need hyperscale, and vendors playing into this. Many average web apps don’t need all that. I am interested myself in small technology, and sqlite is a great fit there. I think more sqlite focus would not only be attractive wrt onboarding, but for actual use as well.

10 Likes
2

Thats true :slight_smile: but gives you enough to get started.

3

Yes, the litestream stuff is exciting and if they finally remove the single-writer limitation with hc-tree, it would be huge. I do have a question though: how do you deal with backups in such cases? Because there is an argument for simpler deployments with sqlite3 but that inevitably pushes some concerns to you, no?

2 Likes
4

Yes. I could not give you a good answer now. Maybe a comment on HN, now the discussion is still active?

5

FYI Moved these sqlite specific bits out to their own thread, I think the suitability of Sqlite in production is a super interesting topic right now, but I think is a bit tangential to the original thread.

4 Likes
6

Apparently Litestream can replicate to S3/Google/etc: Install - Litestream

I also found this for a lightweight cron-based approach: Cron-based backup - Litestream

3 Likes
7

https://www.sqlite.org/backup.html
https://www.sqlite.org/lang_vacuum.html#vacuuminto

There are also some sqlite options without litestream. From the point of having a separate file of the db it’s mostly a matter of putting that file somewhere safe.

3 Likes
8

The only thing that scares me is the fact that you need to lock the file, it would be nice to be able to backup partially without having to lock.

9

SQLite has its own backup functionality as linked by @LostKobrakai – it’s not just copying the files though – and a lot of energy has been expended to prove that it works stably.

Additionally, it also has the concepts of patch-sets and change-sets (section 2.1) – somewhat to the confusion of us in the Elixir space due to the naming clash – that allow you to accumulate changes since a last known snapshot, in a dedicated file.

SQLite has a strong backup apparatus even without litestream. The latter seeks to make backups over the network more or less transparent. The rest is well-covered by sqlite itself.

8 Likes
10

That’s already covered. You issue a backup command and SQLite itself makes new file(s) while you’re using the same session. It’s fully atomic.

People get needlessly scared there, their team is very aware of the potential issues and took care of them a while ago.

7 Likes
11

It is not recommend to backup sqlite by just copying the file. You want to use the backup and restore commands.

From command line backup can be done like this, super simple.

$ sqlite /path/to/db.sqlite ".backup /path/to/file.bkp"

For most of my pet projects it is absolutely fine to use sqlite, it is plenty fast and super reliable. I cant say postgres ever caused me any big troubles, but the simplicity of sqlite is just great when you can get away with it :slight_smile:

3 Likes
12

FYI cross-ref’ing the blog post topic by @silbermm describing use of sqlite + livestream: Distributed SQLite with Elixir | Silbernagel.Dev

1 Like
13

For my (small hobby) projects I’ve also decided to use sqlite.

The main advantage for me is the ease of development and deployment. I don’t need to worry about running postgres locally and about managing this in deployments. It’s simple enough a mental modal to understand and be confident that it will work and keep working.

I’ve used postgres (and mariadb) in the past, but I interact so little with them it’s always a little scary. My go-to setup currently is running Litestream as a “side-car” container via compose. This way I have near instant replication to a backup target (1s delay). I also run a cron-job every night to create a regular backup just in case.

With postgres this kind of (near) instant replication/backup that is so easy with Litestream would be more complicated and more fragile (or I’m unaware about a Litestream for postgres solution!).

I think for anything not requiring specific postgres features and not expecting to grow to massive scale I’d be confident in using sqlite. The future for sqlite looks bright with advancements in both 3rd party tooling (like LiteFS which is improving at a fast rate to become production ready) and the earlier discussed sqlite advancements like the HC-tree branch.

4 Likes
14

I am heavily looking into Litestream to store audits. There’s a single audit writer already that takes audits from multiple nodes and sends them to files on disk, but I’d love to use the Litestream instead. These audits get associated with PostgreSQL records, by IDs, and the queries to retrieve them are fairly simple. For the full text search that will get fed into some sort of indexer anyway, but that’s for the future.

The single-writer limitation is not a huge issue in this case, and for the multi-tenant applications it may be very tempting to use separete Litestream instances instead of one huge database too.

Very exciting to see some movement in the database space :slight_smile:

2 Likes
15

There is a full text search index thingy in sqlite too, but maybe something more serious is needed :slight_smile:

https://www.sqlite.org/fts5.html

2 Likes
16

I’ve been using sqlite in production for read-only datasets for 1.5 years now. It has performed much better than postgresql in this use-case and FTS5 has actually been faster on full-text search.

I’ve been planning a complete migration for all workloads from postgresql when litefs is a bit more mature, but it has been close to a drop in replacement after some minor changes.

For those looking to migrate from postgres to sqlite:

# Dump the db and rename some tables from "public"
pg_dump --data-only --inserts db_dev -h localhost -U user > backupName.sql
sed -i 's/public\.//' backupName.sql

# sqlite doesn't support boolean true/false, so you'll have to manually change in an editor
0,.s/true,/1,/
0,.s/false,/0,/

Regarding litefs, https://litefs-liveview.fly.dev/ is multi-region application using an early version and a custom library to forward write requests. ~sheertj/elixir_litefs - sourcehut git is a elixir specific version of fly_rpc and fly_postgres_elixir which is used to forward db requests to the primary on fly. However, I believe litefs may include write forwarding soon, which could make this library mostly redundant.

Regarding write performance, to be honest, I would expect most applications to be fine. I did a quick write benchmark, but unfortunately don’t remember if it was 1k, 10k, or 100k writes per second. My application is very read heavy with between 200k-500k pageviews/events per month (not so easy to measure without instrumenting everything), but only 50k write queries / month. That works out to about 0.02 queries per second which much, much, much, much less than what sqlite can handle. HCTree is not required. :slight_smile:

When litefs finishes solving streaming backup, db migration, and write forwarding, there will be very little reason to be using postgresql in production for a large number of applications.

Edit: hctree: Thread Test
Stock sqlite does between 4k and 40k writes on a single thread. HCTree looks like it is roughly double the performance.

9 Likes
17

Fly’s brand new beta docs for sqlite also has instruction for postgres->sqlite migration using the sequel ruby gem… SQLite3 · Fly Docs

they also promised future posts on litefs+litestream https://twitter.com/peregrine/status/1615777789806014467

3 Likes
18

btw: next version of Oban will support sqlite… GitHub - sorentwo/oban: 💎 Robust job processing in Elixir, backed by modern PostgreSQL

4 Likes
19

I am trying to use ecto with mvsqlite which is a remix of sqlite that allows clustering.

Here’s my work in progress GitHub - fire/elixir_mvsqlite: A mvsqlite driver for Elixir, I also enabled sqlite strict mode in ecto.

It is only enabled for msvc.

4 Likes