litefs VS litestream

Firstly, LiteJob's reliance on SQLite inherently restricts its horizontal scaling capabilities. Unlike other databases, SQLite is designed for single-machine use, making it challenging to distribute workload across multiple servers. This can certainly be done using novel technologies like LiteFS, but it is far from intuitive.

Im curious to know if others have tried out Turso or LiteFS or any of the newer edge db providers that are popping up in 'real world' applications and what your experiences have been?

Author here. I think we could have set better expectations with our Postgres docs. It wasn't meant to be a managed service but rather some tooling to help streamline setting up a database and replicas. I'm sorry about the troubles you've had and that it's come off as us being disingenuous. We blog about things that we're working on and find interesting. It's not meant say that we've figured everything out but rather this is what we've tried.

As for this post, it's not managed SQLite but rather an open source project called LiteFS [1]. You can run it anywhere that runs Linux. We use it in few places in our infrastructure and found that sharing the underlying database for internal tooling was really helpful for that use case.

[1]: https://github.com/superfly/litefs

#. SQLite WAL mode

From https://www.sqlite.org/isolation.html https://news.ycombinator.com/item?id=32247085 :

> [sqlite] WAL mode permits simultaneous readers and writers. It can do this because changes do not overwrite the original database file, but rather go into the separate write-ahead log file. That means that readers can continue to read the old, original, unaltered content from the original database file at the same time that the writer is appending to the write-ahead log

#. superfly/litefs: aFUSE-based file system for replicating SQLite https://github.com/superfly/litefs

#. sqldiff: https://www.sqlite.org/sqldiff.html https://news.ycombinator.com/item?id=31265005

#. dolthub/dolt: https://github.com/dolthub/dolt

> Dolt can be set up as a replica of your existing MySQL or MariaDB database using standard MySQL binlog replication. Every write becomes a Dolt commit. This is a great way to get the version control benefits of Dolt and keep an existing MySQL or MariaDB database.

#. pganalyze/libpg_query: https://github.com/pganalyze/libpg_query :

> C library for accessing the PostgreSQL parser outside of the server environment

#. Ibis + Substrait [ + DuckDB ]

> ibis strives to provide a consistent interface for interacting with a multitude of different analytical execution engines, most of which (but not all) speak some dialect of SQL.

> Today, Ibis accomplishes this with a lot of help from `sqlalchemy` and `sqlglot` to handle differences in dialect, or we interact directly with available Python bindings (for instance with the pandas, datafusion, and polars backends).

> [...] `Substrait` is a new cross-language serialization format for communicating (among other things) query plans. It's still in its early days, but there is already nascent support for Substrait in Apache Arrow, DuckDB, and Velox.

#. benbjohnson/postlite: https://github.com/benbjohnson/postlite

> postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier.

> The proxy works by translating Postgres frontend wire messages into SQLite transactions and converting results back into Postgres response wire messages. Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables. The proxy also performs minor rewriting on these system queries to convert them to usable SQLite syntax.

> Note: This software is in alpha. Please report bugs. Postlite doesn't alter your database unless you issue INSERT, UPDATE, DELETE commands so it's probably safe. If anything, the Postlite process may die but it shouldn't affect your database.

#. > "Hosting SQLite Databases on GitHub Pages" (2021) re: sql.js-httpvfs, DuckDB https://news.ycombinator.com/item?id=28021766

#. awesome-db-tools https://github.com/mgramin/awesome-db-tools

LiteFS works sorta like that. It provides read replicas on all your application servers so you can use it just like vanilla SQLite for queries.

Write transactions have to occur on the primary node but that's mostly because of latency. SQLite operates in serializable isolation so it only allows one transaction at a time. If you wanted to have all nodes write then you'd need to acquire a lock on one node and then update it and then release the lock. We actually allow this on LiteFS using something called "write forwarding" but it's pretty slow so I wouldn't suggest it for regular use.

We're adding an optional a query API over HTTP [1] soon as well. It's inspired by Turso's approach. That'll let you issue one or more queries in a batch over HTTP and they'll be run in a single transaction.

[1]: https://github.com/superfly/litefs/issues/326

Basically, LiteFS: https://github.com/superfly/litefs

And then some load balancer cleverness that reroutes writes to a specific VM: https://fly.io/blog/globally-distributed-postgres/

But my guess is they'll have an alternate implementation or something like LiteFS in Deno Deploy that will make this substantially more interesting when running in the Cloud.

I have not, but I keep meaning to collate everything I've learned into a set of useful defaults just to remind myself what settings I should be enabling and why.

Regarding Litestream, I learned pretty much all I know from their documentation: https://litestream.io/

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.

This post intends to help you setup replication for SQLite using Litestream.

I've been messing around with litestream. It is so cool. And, I either found a bug in the -timestamp switch or don't understand it correctly.

What I want to do is time travel into my sqlite database. I'm trying to do some forensics on why my web service returned the wrong data during a production event. Unfortunately, after the event, someone deleted records from the database and I'm unsure what the data looked like and am having trouble recreating the production issue.

Litestream has this great switch: -timestamp. If you use it (AFAICT) you can time travel into your database and go back to the database state at that moment. However, it does not seem to work as I expect it to:

https://github.com/benbjohnson/litestream/issues/564

I have the entirety of the sqlite database from the production event as well. Is there a way I could cycle through the WAL files and restore the database to the point in time before the records I need were deleted?

Will someone take sqlite and compile it into the browser using WASM so I can drag a sqlite database and WAL files into it and then using a timeline slider see all the states of the database over time? :)

We're using SQLite in production very heavily with millions of databases and fairly high operations throughput.

But we did run into some scariness around trying to use Litestream that put me off it for the time being. Litestream is really cool but it is also very much a cool hack and the risk of database corruption issues feels very real.

The scariness I ran into was related to this issue https://github.com/benbjohnson/litestream/issues/510

Litestream is a library that allows you to easily create backups. You can probably just do analytic queries on the backup data and reduce load on your server.

https://litestream.io/

One possible strategy is to have one directory/file per customer which is one SQLite file. But then as the user logs in, you have to look up first what database they should be connected to.

OR somehow derive it from the user ID/username. Keeping all the customer databases in a single directory/disk and then constantly "lite streaming" to S3.

Because each user is isolated, they'll be writing to their own database. But migrations would be a pain. They will have to be rolled out to each database separately.

One upside is, you can give users the ability to take their data with them, any time. It is just a single file.

[0]. https://litestream.io/

Upstream Kuma uses a local SQLite database to store account data, configuration for services to monitor, notification settings, and more. To make sure that our data is available across redeploys, we will bundle Uptime Kuma with Litestream, a project that implements streaming replication for SQLite databases to a remote object storage provider. Effectively, this allows us to treat the local SQLite database as if it were securely stored in a remote database.