WAL Mode in LiteFS

How fast a network are we talking about? SQLite can max out my NVMe drives, despite its internal serialization constraint. Thousands of connections at milliseconds is similar to a few connections at microseconds. Latency is everything.

with a script

It depends on what you're trying to do. If you're trying to query revenue then that's usually stored in a third-party system like Stripe. If you're tracking metrics then those can be aggregated into a time series database like Prometheus and viewed through Grafana.

It's definitely a trade-off. Isolation is pretty compelling for a lot of mid-market and enterprise customers but it does come at a cost of making it a pain to query across customers. But then again, that's kinda the point. :)

You can attach multiple dbs and join across them and it works very well. The default limit is ten and I think the max is a hundred so there's definitely a limit. But if you really needed to do more I can think of some workarounds.

Pretty simple with the built-in command

ATTACH customer145.sqlite as customer145; ATTACH customer2.sqlite as customer2;

Then query the attached tables.

> What are some reasons you reckon that the current setup won't scale beyond 10GB?

It's more of an arbitrary threshold right now. A lot of testing that we do right now is chaos testing where we frequently kill nodes to ensure that the cluster recovers correctly and we try to test a range of database sizes within that threshold. Larger databases should work fine but you also run into SQLite limitations of single writer. Also, the majority of databases we see in the wild are less than 10GB.

> Leaving aside stability related bugs, what design decisions previously made were the caused painful bugs / roadblocks?

So far the design decisions have held up pretty well. Most of the PRs were either stability related or WAL related. That being said, the design is pretty simple. We convert transactions into files and then ship those files to other nodes and replay them.

We recently added LZ4 compression (which will be in the next release). There was a design issue there with how we were streaming data that we had to fix up. We relied on the internal data format of our transaction files to delineate them but that would mean we'd need to uncompress them to read that. We had to alter our streaming protocol a bit to do chunk encoding.

I think our design decisions will be tested more once we expand to doing pure serverless & WASM implementations. I'm curious how things will hold up then.

> Consequently, what things majorly surprised you in a way that perhaps has altered your approach / outlook towards this project or engineering in general?

One thing that's surprised me is that we originally wrote LiteFS to be used with Consul so it could dynamically change its primary node. We kinda threw in our "static" leasing implementation for one of our internal use cases. But it turns out that for a lot of ancillary cache use cases, the static leasing works great! Losing write availability for a couple seconds during a deploy isn't necessarily a big deal for all applications.

You'll probably hit file descriptor limits so you'll need to up those. There's also some memory overhead per connection. I'd recommend limiting the number of concurrent connections on a server if you're running on a resource constrained box. In practice, you probably won't have all thousand databases querying at the same time and reopening SQLite connections is pretty fast.

> Does it support snapshots?

We'll be adding an "export" feature soon to do snapshots. We'll also be adding support for streaming backups similar to what Litestream does (although it'll be cluster aware).

> From what I could work out, when you start a non primary instance it catches up from the primary? So if your replica is a fresh instance it replicates the entire db from the primary?

Yes, it'll grab a snapshot from the primary if it's a fresh instance or if it's been too long since it was connected to the primary (and the next transaction files are not available).

> I was wondering if it’s possible to snapshot somehow, so if I have a fresh server that comes online it grabs the last known snapshot from somewhere like s3 then the delta changeset from the primary is much smaller (less load?)

You could but I'm not sure there's a large benefit over simply copying from the primary. I suppose if you have a very large database and there's only a small number of changes then pulling from S3 first could be beneficial.

The transaction files are also compactable so we'll be able to reduce the total amount transferred once we integrate that into LiteFS.

We took the tradeoff in our product due to the strong performance upside.

Our reasoning goes something like - whatever happened approximately at the edge of the power-loss abyss is considered part of the explosion. We have never held out hope that we'd be able to get information to disk up until the last microsecond.

Letting WAL roll us back a few transactions is not a huge deal for our customers. Even if this breaks logical consistency with regard to external systems. We have many degrees of "redo" and the back office is always going to be a thing in our line of business.

We file this under the "edge cases not economically worth targeting" bucket and take our 2 ~free orders of magnitude.

> unexpectedly shutdown

Right, a power cut or kernel panic. It's certainly something to be aware of, but there are probably much more likely ways to lose some data (programming errors, etc).

The WAL is append-only so you don't risk data corruption (which is great).

I'm not sure this is true with all filesystems. I think there are some filesystems in which a crash during append can end up with the file enlarged, but the data not written (IIRC I saw something like this with XFS when I was working on a kernel module that kept crashing the kernel).