Sqlite in Production

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

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

It is only enabled for msvc.

btw: next version of Oban will support sqlite.. GitHub - oban-bg/oban: 💎 Robust job processing in Elixir, backed by modern PostgreSQL, SQLite3, and MySQL · GitHub

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 Jason on X: "In one of my first documentation efforts with https://t.co/vulccQMvY9 I wrote up a quick guide on using SQLite3, Phoenix/Ecto and even a bonus tip of one way you can transfer data to SQLite from other databases. https://t.co/lVbKZiVC7W Hat tip to @mcrumm_ as well!" / X

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.

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.

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

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

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.

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

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

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.