SQLite on Servers

Gathering some info on SQLite usage on servers.

My use-case is single-instance servers and non/rarely-communicating nodes. Once you introduce frequent internode network calls - you may as well just go full Postgres instead of re-implementing it. Single-node systems are nonetheless very appealing and here I'll be collecting some relevant info for such use-cases.

CDC/replication

Litestream

• https://litestream.io/
• https://github.com/benbjohnson/litestream
• https://fly.io/blog/all-in-on-sqlite-litestream/
• https://news.ycombinator.com/item?id=26440397

https://news.ycombinator.com/item?id=26445688

Hey, lead of Debezium here, a change data capture tool for a number of databases (not SQLite, though). Out of curiousity, how are you implementing change ingestion, is there some interface/API in SQLite which lets you do this? Or are you manually parsing its log files?

https://news.ycombinator.com/item?id=26447995

Hi Gunnar, good to meet you. Litestream works by reading off the SQLite WAL file which acts as a circular buffer. It takes over the checkpointing process to control when the buffer rolls over so it doesn't miss any frames. Those frames get copied over and each buffer is recreated as a sequential set of WAL files that can be replayed to reconstruct the state of the database at a given point-in-time.

It sounds like Litestream differs from Debezium in that it provides physical replication rather than logical row changes. However, I've been toying with the idea of determining row-level changes from the WAL frames by using ptrmap pages to traverse up the b-tree and determine the owner table. There's a bunch of stuff on the roadmap before that like live read replication though.

There's some additional info on the site about how Litestream works1 and I'm planning on making a video similar to this Raft visualization2 I did a while back.

https://news.ycombinator.com/item?id=26447045:

How does Litstream actually works? Do you read from WAL and then sync with S3?

https://news.ycombinator.com/item?id=26447917

Yes, it reads off the WAL then compresses the frames using LZ4 and then uploads to S3. The SQLite WAL acts as a circular buffer so Litestream takes over the checkpointing process to control when it rolls over so it can recreate that buffer as separate files. There's additional information on the web site: https://litestream.io/how-it-works/

1. "reads off the WAL"
2. "compresses the frames using LZ4"
3. "uploads to S3"

LiteFS

• https://fly.io/blog/introducing-litefs/

rqlite

• https://rqlite.io/
• https://github.com/rqlite/rqlite

Turso

• https://turso.tech/
• https://github.com/tursodatabase
• https://turso.tech/blog/a-deep-look-into-our-new-massive-multitenant-architecture

libSQL

SQLite fork.

• https://turso.tech/libsql
• https://github.com/tursodatabase/libsql

limbo

SQLite re-write in Rust. WIP.

• https://github.com/tursodatabase/limbo
• https://turso.tech/blog/introducing-limbo-a-complete-rewrite-of-sqlite-in-rust
• https://penberg.org/papers/penberg-edgesys24.pdf
• https://penberg.org/papers/penberg-conext-sw-23.pdf
• testing:
  • Deterministic Simulation Testing (DST)
    • at OS level with help of https://antithesis.com/
    • general DST info:
      • https://apple.github.io/foundationdb/testing.html
      • https://docs.tigerbeetle.com/about/vopr/
      • https://blog.resonatehq.io/deterministic-simulation-testing
      • https://notes.eatonphil.com/2024-08-20-deterministic-simulation-testing.html
        • https://news.ycombinator.com/item?id=41298899
      • https://quanttype.net/posts/2024-11-10-weeknote-13-deterministic-simulation-testing.html
      • https://antithesis.com/blog/deterministic_hypervisor/
      • https://medium.risingwave.com/deterministic-simulation-a-new-era-of-distributed-system-testing-part-1-of-2-69f151593a1