4 ms·

What I'd like to see is a system where a single-writer SQLite database is replicated to object storage so that you can spin up really cheap read replicas. Is an

What I'd like to see is a system where a single-writer SQLite database is replicated to object storage so that you can spin up really cheap read replicas. Is anyone working on something like that? Such a system would also require a side channel propagating WAL updates (over Kafka or similar) to replicas, so that the running replicas can update themselves incrementally and stay fresh without loading anything from object storage.

anentropic 1y ago
Litestream is basically that, though the OP article has "The next major feature we’re building out is a Litestream VFS for read replicas" as a What's Next todo at the bottom. https://litestream.io/guides/s3/ https://litestream.io/guides/s3/ I think this is also roughly what Turso is, although it's becoming a SQLite-compatible db rather than vanilla https://docs.turso.tech/features/embedded-replicas/introduction https://docs.turso.tech/features/embedded-replicas/introduct... https://docs.turso.tech/cloud/durability https://docs.turso.tech/cloud/durability
- atombender 1y ago
  I don't Litestream does that yet? It appears to be for backing up to S3, and you manually "restore" the image to a file. You can't point an SQLite client at the S3 bucket, and there's no provision for getting low latency updates. But it sounds like they're working on this. Turso looks cool and is a project I will keep an eye on, but it's replica mode seems to be designed for apps rather than mechanism to rapidly scale out read replicas in a server cluster. Also, the web site warns that it's not ready for production use.
simonw 1y ago
LiteFS can do that, but you need to run a custom FUSE filesystem for it - hence why Litestream remained more popular. https://fly.io/docs/litefs/how-it-works/#capturing-sqlite-transactions https://fly.io/docs/litefs/how-it-works/#capturing-sqlite-tr... Litestream is working on that now - the code is already in https://github.com/benbjohnson/litestream/blob/v0.5.0/vfs.go https://github.com/benbjohnson/litestream/blob/v0.5.0/vfs.go but it's not yet a working, documented feature.
- atombender 1y ago
  LiteFS is also described as "beta" quality on GitHub and therefore not appropriate for production use.
ncruces 1y ago
I mean, that's literally their "What’s next?" from the OP: https://fly.io/blog/litestream-v050-is-here/#whats-next https://fly.io/blog/litestream-v050-is-here/#whats-next They already have a prototype, and... it's pretty rough on the edges. I'm porting it to my Go SQLite driver and already ran into a bunch of issues. But it seems at least feasible to get it into a working shape. https://github.com/benbjohnson/litestream/issues/772 https://github.com/benbjohnson/litestream/issues/772 https://github.com/ncruces/go-sqlite3/compare/main...litestream https://github.com/ncruces/go-sqlite3/compare/main...litestr...
- koeng 1y ago
  Interesting! I really like your wasm compiled SQLite more than the pure Go one, so it is what I use most of the time. What exactly are you trying to port?
  - ncruces 1y ago
    A VFS that allows you to directly open a Litestream replication target (e.g. S3) as a read-only database and run queries against it without ever having to download the entire database (e.g. to an ephemeral instance that doesn't even have the disk space for it).
    - koeng 1y ago
      Interesting. Any plans for a cache? Or would that just be a litestream thing
      - ncruces 1y ago
        I… honestly don't know. I saw the announcement some time ago (the revamped post) and started following their repo. They did mention caching to hide latency. When I saw the v0.5.0 tag, I dived into just porting it. It's just over a couple hundred lines, and I have more experience with SQLite VFSes than most, so why not. But it's still pretty shaky.
      - ncruces 1y ago
Nelkins 1y ago
I'm curious about the same, but also am wondering if there can be an automatic election of a new primary through the use of conditional writes (or as Fly.io say, CASAAS: Compare-and-Swap as a Service).
emadda 1y ago
Cloudflare D1 has this, although you are limited to using JS workers to read/write it. https://developers.cloudflare.com/d1/best-practices/read-replication/ https://developers.cloudflare.com/d1/best-practices/read-rep...

anentropic 1y ago

Litestream is basically that, though the OP article has "The next major feature we’re building out is a Litestream VFS for read replicas" as a What's Next todo at the bottom. https://litestream.io/guides/s3/ https://litestream.io/guides/s3/ I think this is also roughly what Turso is, although it's becoming a SQLite-compatible db rather than vanilla https://docs.turso.tech/features/embedded-replicas/introduction https://docs.turso.tech/features/embedded-replicas/introduct... https://docs.turso.tech/cloud/durability https://docs.turso.tech/cloud/durability

atombender 1y ago
I don't Litestream does that yet? It appears to be for backing up to S3, and you manually "restore" the image to a file. You can't point an SQLite client at the S3 bucket, and there's no provision for getting low latency updates. But it sounds like they're working on this. Turso looks cool and is a project I will keep an eye on, but it's replica mode seems to be designed for apps rather than mechanism to rapidly scale out read replicas in a server cluster. Also, the web site warns that it's not ready for production use.

simonw 1y ago

LiteFS can do that, but you need to run a custom FUSE filesystem for it - hence why Litestream remained more popular. https://fly.io/docs/litefs/how-it-works/#capturing-sqlite-transactions https://fly.io/docs/litefs/how-it-works/#capturing-sqlite-tr... Litestream is working on that now - the code is already in https://github.com/benbjohnson/litestream/blob/v0.5.0/vfs.go https://github.com/benbjohnson/litestream/blob/v0.5.0/vfs.go but it's not yet a working, documented feature.

atombender 1y ago
LiteFS is also described as "beta" quality on GitHub and therefore not appropriate for production use.

ncruces 1y ago

I mean, that's literally their "What’s next?" from the OP: https://fly.io/blog/litestream-v050-is-here/#whats-next https://fly.io/blog/litestream-v050-is-here/#whats-next They already have a prototype, and... it's pretty rough on the edges. I'm porting it to my Go SQLite driver and already ran into a bunch of issues. But it seems at least feasible to get it into a working shape. https://github.com/benbjohnson/litestream/issues/772 https://github.com/benbjohnson/litestream/issues/772 https://github.com/ncruces/go-sqlite3/compare/main...litestream https://github.com/ncruces/go-sqlite3/compare/main...litestr...

koeng 1y ago
Interesting! I really like your wasm compiled SQLite more than the pure Go one, so it is what I use most of the time. What exactly are you trying to port?
- ncruces 1y ago
  A VFS that allows you to directly open a Litestream replication target (e.g. S3) as a read-only database and run queries against it without ever having to download the entire database (e.g. to an ephemeral instance that doesn't even have the disk space for it).
  - koeng 1y ago
    Interesting. Any plans for a cache? Or would that just be a litestream thing
    - ncruces 1y ago
      I… honestly don't know. I saw the announcement some time ago (the revamped post) and started following their repo. They did mention caching to hide latency. When I saw the v0.5.0 tag, I dived into just porting it. It's just over a couple hundred lines, and I have more experience with SQLite VFSes than most, so why not. But it's still pretty shaky.
    - ncruces 1y ago

Nelkins 1y ago

I'm curious about the same, but also am wondering if there can be an automatic election of a new primary through the use of conditional writes (or as Fly.io say, CASAAS: Compare-and-Swap as a Service).

emadda 1y ago

Cloudflare D1 has this, although you are limited to using JS workers to read/write it. https://developers.cloudflare.com/d1/best-practices/read-replication/ https://developers.cloudflare.com/d1/best-practices/read-rep...