Multi-writer SQLite over P2P works by shipping SQL text, not pages

The interesting part is what the sync layer actually carries. Not pages, not binary diffs of a file: a list of statements, appended to an Autobee writer's oplog, with the result of executing them applied to an Autobee view ^[8]. Ordering is Autobee's job, since it rebases writers automatically ^[7]. SQLite's job shrinks back to executing one stream of writes at a time, which is the only situation it was ever built for ^[8].

That inversion is what makes the custom VFS load-bearing rather than decorative ^[3]. The VFS persists the executed result into a shared view, and that view can itself be synced when conditions allow ^[6], which buys the fast-forward path: a peer well behind does not have to replay every statement, it can take the materialised database instead ^[9]. So database state does move across the wire. It just moves as a cache of the log, not as the thing peers agree on. Temporary files stay local, in RocksDB ^[10].

The author's reasoning for rejecting the obvious routes is worth reading literally. Syncing the results of writes is cheap, but once you allow several writers, conflicting writes corrupt the database very quickly ^[4]. CRDTs or binary patching get expensive fast and still leave corruption possible in edge cases, because SQLite makes no effort to support any of this ^[5]. The log design is described as making corruption extremely unlikely ^[8], which is a different guarantee than impossible, and it is the honest way to put it.

The bill arrives on disk. RocksDB does not release deleted data until compact() is called, and both Autobee and the temporary storage sit on RocksDB, so the footprint grows with every operation ^[11]. SQLite meanwhile writes in pages and shuffles data around to keep the file compact, which generates many writes even for small changes ^[12]. (The post gives the default page size as 4096KiB, which reads like a slip for bytes.) The two habits do not compose: writing 1MiB of data can require up to 200MiB of storage depending on how it was written ^[13], a factor of 200 ^[15]. Most of that is reclaimable through ParaQL's own compact(), and the number surfaced under benchmarking rather than ordinary use ^[14].

The gap in the design is the same one that gives it its strength. Because the replicated artefact is SQL text, correctness now depends on statements meaning the same thing on every peer that replays them. The author flags PRAGMA as something not to expect to work correctly ^[17], and says nothing about statements whose outcome depends on local state. Everything else in the feature list points the other way, toward ordinary use: any SQLite database can be loaded, replication runs over any stream-like transport, write access and permitted writes are controllable per peer, and the result exports back to standard SQLite ^[16], with encryption on disk and in transport plus deflate compression traded against performance ^[18]. It also came out of a concrete want, which is full-text and vector search inside P2P projects where the usual answer is a local SQLite index over synced data ^[1][2]. The author calls the whole thing a mad-science experiment, and says it has yet to meet a real-world scenario ^[19].