Now that's some lucky timing!
Literally in 12 hours I'm supposed to work on how to sync machines without network access, that write to SQLite databases.
Especially the combination of Syncthing and `nncp-xfer` is super useful. NNCP usually has strict routing which can be a hassle to guarantee (machine D only gets packets from machine A if they went through machine B and C first).
Using Syncthing as a central storage for NNCP packets circumvents the strict routing requirement.
I don't think it does with a patchset but maybe could with a changeset:
> When processing an UPDATE change, the following conflicts may be detected:
> The target database may contain a row with the specified PRIMARY KEY values, but the current values of the fields that will be modified by the change may not match the original values stored within the changeset. This type of conflict is not detected when using a patchset.
Also, if you have A and B, both initially the same, modify both with an UPDATE on the same existing row, and send and apply the patchiest to each other, I think they will overate each other and have a different state...
Edit:
I wander if there is a way to add a last_mod column to the tables and so only a more recent UPDATE would be executed from a patch set? It would only work at the row level but for most cases that should be ok. You could always create other tables for more granular control.
It looks like you can provide a conflict hander too that can replace the update statement, that could be useful for merging conflicting changes.
Please don’t ever use time stamps for synchronization. You will have a bad time.
Clock skew is an insidious problem: things may look like they’re working, but they’re not. Given all of the clock bugs out there it’s sometimes not even safe to rely on the clock on a single machine. Across multiple machines it’s hopeless.
Please note that this doesn’t apply if you have a fancy time source like Google uses in their fancy cloud DB (whose name escapes me at the moment).
While I agree that 99% of the time you are right. There are edge cases where a time stamp is best.
I’m looking for a system using SQLite that gives you the same eventual consistency guarantees as CouchDB/PouchDB on consumer devices. You need to be able to have nodes disconnect from each other and the internet for long periods of time before syncing. Hours or days not seconds or milliseconds.
If two nodes overwrite the same row while disconnected you need a deterministic way to decide which is the winner. This could be though a hash of the values in the row (lowest value wins) or using a timestamp. Pros and cons for each and it depends on use case. So for example if you are building a notebook application you almost certainly want the most recent update to win. The transaction rate would be low and devices with an internet connection will be “relatively” accurate on time.
Obviously this could be open to abuse, someone changing their clock to force their edit to win. But again pros and cons. The use case I’m looking for is within a trusted group where this wouldn’t be a problem.
This is nice if you can afford to have atomic clocks in your data centers and whole infrastructure teams working on keeping your clocks in sync (read: if you are Google) but otherwise you're probably gonna have a bad time.
I think those clocks are coming down in price but the bigger challenge is the logistics - for whatever reason shared data centers don’t run in a GPS signal from the outside that all their clients can share.
Well within the price range of smaller data centers, ~$1500 to build the open source reference card published by the open compute project: Open Time Server
Anyone have resources on how to actually use this? I see they provide C code, but I'd greatly prefer some kind of simplified interface or introduction to what is happening (kind of like how litestream has a getting started https://litestream.io/getting-started/ )
Fossil is a version control system — it's what SQLite uses and it includes a static site/documentation generator, which is (AIUI) what generates sqlite.org. See https://sqlite.org/whynotgit.html and https://fossil-scm.org.
The use case is an upgrade from postgres 10 to 11. The last time we upgraded (to 13), some queries turned out not to work well, so we were forced to go back to the old server. In the meantime stuff was written to the new server which we then manually put into the old db before switching over to the old. Result: lots of downtime.
Is there a way to create a changeset like this in postgres?
As much as I love the idea, Raft is not a silver bullet and can break in strange and inconspicuous ways that may be difficult to fix. It can also lead to subtle inconsistencies in the data depending on what data you are putting in it. Overall it may be good in some situations, but I doubt it will dislodge Postgres in any meaningful way.
Thanks for chiming in, Philip! I’m a big fan of your work.
I was referring to the issues around determinism in queries. These are easy enough to catch if you’re aware of them, but it pushes more onus onto the application layer / devs (not that it’s necessarily a bad thing, just a trade-off).
Wouldn't this be true of any form of replication, including whatever Postgres uses? Or is the proof of correctness for Raft weaker than some other class of consensus?
Edit: from rqlite's FAQ
> Raft is a Consistency-Partition (CP) protocol. This means that if a rqlite cluster is partitioned, only the side of the cluster that contains a majority of the nodes will be available.
Disconcertingly, this sounds like they assume that only one side of a partition can contain a majority of nodes. This isn't true if the partition is partial, eg A<-->B, B<-->C, A<-/->C.
rqlite (and Raft) doesn't assume this. All it states is that for the cluster to make progress i.e. apply a change in a consistent manner, at least a quorum of nodes must be online and in contact with the Leader (which is one of the online nodes). Every node in a rqlite cluster knows what size the cluster is and therefore requires that (N/2)+1 nodes acknowledge the change before that change is committed. The Leader node performs this coordination.
The scenario you outlined above is obviously possible. But in that event the cluster is down -- the Leader (let's say it's node A) cannot contact a quorum of nodes. No changes can be made to it.
rqlite is nice but it's got limitations that made me look elsewhere. https://dqlite.io/ does it in C and allows for transactions and can be used for the Go sql.DB object.
There are, nonetheless, lots of situations where I would have reached for pg automatically a few years ago where I now stop and think about which of the two is going to fit my use case better.
The comment you're replying to is saying rather more than that, and I don't endorse it, but the shift in the possibility space is still both fascinating and impressive.
(also I love sqlite's tendency to use pg as a syntax template, it seems like an example of two projects with significantly different goals playing to each others' strengths in a beautifully positive sum game fashion)
I don't know if SQLite + Raft is going to be the next Postgres, but it's certainly an interesting solution for a variety of use cases where you don't need your data _partitioned_ on multiple machines (but still want it to be replicated for availability and fault tolerance).
Adding Raft and replication will make your database more available but strictly slower and not any more concurrent. If SQLite is not good enough for you now it probably wouldn't be any good for you then.
SQLite is not a high throughput database. Trying to work around this with cool tech is not likely to make it better than a boring solution, like using a high throughput database in the first place.
My SQLite usages are able to saturate my NVMe storage devices in many test cases. Not sure what exact criteria you are using for "high throughput" here. Perhaps you are not familiar with the additional WAL journal mode or synchronization flags?
SQLite3 is not a high-write-concurrency database, but not a high-throughput DB is less clear, and it's not at all clear that it can't be hacked on to go faster.
If you take a sharding approach to concurrency, you might just be happy with no write concurrency: one process/thread per-CPU / storage slice, and fix everything else upstairs. It's not a crazy approach, and it might be the approach many would take if they could start from scratch. In fact, it's the approach Spanner takes, for example.
SQLite never ceases to amaze me.