Hacker News Reader

Comment by infogulch | Hacker News Reader

> To improve latency, we're aiming at a scale-out model that works similarly to Fly Postgres. That's to say: writes get forwarded to the primary and all read requests get served from their local copies.

How can you ensure that a client that just performed a forwarded write will be able to read that back on their local replica on subsequent reads?

View on HN

That's a fun one.

A couple years ago someone posted a solution to that here. I'm not sure if it works for SQLite, but it worked for Postgres. The basics of it were that each replica was aware of the latest transaction ID it had seen. On a normal read you'd deal with the usual set of eventually consistent issues. But on a read-after-write, you would select a replica that was ahead of the write transaction.

Ultimately that's a very odd flavor of sharding. What I don't recall is how they propagated that shard information efficiently, since with sharding and consistent hashing the whole idea is that the lookup algorithm is deterministic, and therefore can be run anywhere and get the same result. WAL lag information is dynamic, so... Raft?

Another solution that I've used successfully in the past for web apps is to set a 10s cookie every time a user performs a write, and then route their read requests to the lead server until that cookie expires. That way they're sure to see the impact of the write they just made.

If write traffic is low enough I could see that working, but 10s is an awful long time. Anything less than a 1000:1 read:write ratio would start running into capacity problems wouldn't it?

That's never been a problem on any system I've worked on. If you assume a web application with logged in users who are occasionally updating their data, the percentage of users who will have performed a write within the last 10s is always going to be absolutely tiny.

The absolute rate is the problem. You can't shed that load to other machines so the percentage of users or percentage of traffic doesn't matter. This is basically a variant of Little's Law, where server count has a ceiling of 1.

100 edits a minute from distinct sessions is 1000 sessions pinned at any moment. If they read anything in that interval it comes from the primary. The only question is what's the frequency and interval of reads after a write.

Yeah a transaction ID solution would work pretty well. Every request would return the highest transaction ID seen by the replica; writes forwarded to the primary would also return the transaction ID after that write; any request with a transaction ID higher than the replica would be forwarded to the primary.

I think that may have been the case. Try the local replica, if it is too old, hit the primary, rather than shopping around for the next best replica.

LiteFS provides a transaction ID that applications can use to determine replication lag. If the replica is behind the TXID, it can either wait or it can forward to the primary to ensure consistency.

Is that transaction ID made available to SQLite clients? As a custom SQL function or a value in a readable table somewhere?

It’s available as a file handle. If you have a database file named “db” then you can read “db-pos” to read the replication position. The position is a tuple of the TXID and the database checksum.

Can the app read it concurrently while it is updated by LiteFS?

Yes, the app can read it whenever. There's a brief lock obtained on the current database position internally to read it out. The position only gets updated at the end of the transaction.

Looks like LiteFS invalidates the cached file contents on every update of position. This means there is never stale data to be read.

(The client needs to make sure to consume that in a single pread/read syscall, or it could observe a sheared state.)

Disclaimer: I wrote the FUSE framework LiteFS uses, https://bazil.org/fuse

https://github.com/superfly/litefs/blob/52e269d4b04070690ce2... https://github.com/superfly/litefs/blob/52e269d4b04070690ce2... https://github.com/superfly/litefs/blob/a5cf33d1a3a91873d4ad...

Very clear. Thanks for the great explanation.

They don't have ACID guarantees with this setup.

ACID is typically used to describe single database instances and from that perspective, LiteFS doesn't change the ACID semantics that SQLite has. For distributed consistency models[1], it gets more complicated as LiteFS currently just supports async replication. We'll introduce stronger guarantees in the future although we currently provide some information such as transaction IDs so you can manage replication lag if you need stronger consistency.

[1]: https://jepsen.io/consistency

Reply on news.ycombinator.com