litefs VS fuse

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.

For this first implementation I used Go. After a reviewing some solutions I decided to use https://github.com/bazil/fuse. It seemed to be the easiest way to prototype.

Often, the SQLite database would fit in RAM, and reads would be served straight from the page cache, with no overhead.

Disclaimer: I wrote the FUSE framework LiteFS uses, https://bazil.org/fuse -- and I also have some pending performance-related work to finish, there...

I'd love to upgrade but I rely on mounting my Google Drive via /dev/fuse and rclone. There was a post yesterday saying that is broken in 13.1-RELEASE and linking to a FreeBSD bug which links to a rclone bug which links to a bazil bug which seems to have no traction. Someone mentioned a commandline utility that can interact with gdrive but this seems like a pretty bad replacement. IIUC the FreeBSD devs' theory is that a new async system call path exposed a bug in rclone and the blame is there. Anyway, I'm still on 13.0 for now, unfortunately.

This was also reported to rclone where someone pointed out that the problem is with fuse lib on FreeBSD and as such is a FreeBSD fuse lib porting problem.

> 'failed' state and the process itself leaving the accounting tables.

Once again, that cannot be done until the parent process consumes the exit status. That's what the zombie is there for. Zombies don't take up much space.

> Stuck mounts have a half solution (lazy unmounts) but even _that_ interface really also needs a timeout value after which operations on the target should be assumed to fail rather than return correctly.

These days most NFS etc mounts are "soft mounts", that is operations will eventually time out.

Lazy unmount doesn't really apply here, it makes the mountpoint disappear from the global namespace, but all existing open files remain untouched, and the mount lives as long as anything is still using it; it just removes the "entry point" to the mount.

On today's Linux, it's up to each filesystem to provide abort/timeout mechanism. For timeouts, this is the right design, as demonstrated by macOS complications with FUSE. I do wish there was a common way to make things abort.

There was a patch in circulation a long time ago, that could seamlessly switch all open FDs of any given mountpoint into a whole different filesystem named badfs. badfs would just return an error on any operation. As far as I know, that patch never got merged, probably because nobody ever got it working 100%.

That kind of a DoS would require a local attacker, and then the victim to access a mountpoint owned by the attacker. Using FUSE, you could get a lot of processes hanging like that, for sure. I guess you could trap a mail delivery agent, if you still had a system where mail was delivered to users' home directories.

However, forcibly aborting any FUSE mount is a single `echo 1 >/sys/fs/fuse/connection/NNNN/abort`, the only challenge is finding the right ID. (See https://github.com/bazil/fuse/blob/fb710f7dfd05053a3bc9516dd...)