I don't know where this idea of having to swap a whole table in SQLite came from, but it simply isn't true. Over the last 13 years I have upgraded production HashBackup databases at customer sites a total of 35 times without rewriting and swapping out tables by using the ALTER statement, just like other databases:
https://www.sqlite.org/lang_altertable.html
For the most recent upgrade, I upgraded to strict tables, which I could also have done without a rebuild/swap. I chose to do a rebuild/swap this one time because I wanted to reorder some columns. Why? Because columns stored with default or null values don't have row space allocated if the column is at the end of the row.
I'm embarrassed to admit that I didn't realize RENAME COLUMN was actually added in 3.25, almost four years ago.
DROP COLUMN was only just added last year in 3.35.
I'm surprised a database schema lasted 9/12 years without ever renaming or dropping a column.
This changes things! But even now, ALTER TABLE is not transactional. So especially with many concurrent readers there can definitely be situations where you'd still want to rewrite.
sqlite> CREATE TABLE foo(a,b,c);
sqlite> INSERT INTO foo VALUES (1,2,3);
sqlite> BEGIN;
sqlite> ALTER TABLE foo DROP COLUMN b;
sqlite> SELECT * FROM foo;
1|3
sqlite> ROLLBACK;
sqlite> SELECT * FROM foo;
1|2|3
I did have a couple of columns that were no longer needed and would have dropped them, but instead I just set them to null and ignored them. Nulls only take 1 byte of space in a row. I dropped them when DROP COLUMN was added.
`FULL OUTER JOIN` is the secret to diff'ing table sources. `MERGE` is just a diff operation + insert/update/delete statements to make the target table more like the source one (or even completely like the source one).
`FULL OUTER JOIN` is essential to implementing `MERGE`. Granted, one could implement `MERGE` without implementing `FULL OUTER JOIN` as a public feature, but that seems silly.
Sadly, the SQLite3 dev team specifically says they will not implement `FULL OUTER JOIN`[0].
Implementing `MERGE`-like updates without `FULL OUTER JOIN` is possible (using two `LEFT OUTER JOIN`s), but it's an O(N log N) operation instead of O(N).
The lack of `FULL OUTER JOIN` is a serious flaw in SQLite3. IMO.
[0] https://www.sqlite.org/omitted.htmlFinally!
Thank you so much for this Mr. Hipp!
EDIT: Don't forget to edit the `omitted.html` page when you ship it!
Prisma Migrate can automatically generate these steps, removing most of the pain. I'm sure other migration tools can do this as well.
Sounds like there will be no synchronous replication and instead there will be a background process to "constantly keep [read-only clones] up-to-date". This means that a stale read from an older read replica can occur even after a write transaction has successfully committed on the "primary" used for writes.
So, while the consistency is not "thrown away", it's no longer a strong consistency? Anyway, Kyle from Jepsen will figure it out soon, I guess :)
Disclaimer: I work at Cloudflare :)
Does SQLite support some kind of monotonic transaction id that can be used as a cache coherency key? Say a client writes a new record to the database which returns `{"result": "ok", "transaction_id": 123}`, then to ensure that subsequent read requests are coherent they provide a header that checks that the read replica has transaction_id >= 123 and either waits for replication before serving or fails the request. (Perhaps a good use for the embedded worker?)
I know of a very important system at AWS that did this with MySQL :D
Edit: But that would mean that durable objects can't be replicated asynchronously? That would mean a big latency hit. Then what's the difference to a central DB in one datacenter?
It's more of a "quickstart" than a peek under the hood.
[0]: https://en.wikipedia.org/wiki/Online_analytical_processing
Duckdb on HN: https://news.ycombinator.com/item?id=23287278
all the hip service providers seem to be all over it which would indicate pretty good modernity to me at least.
sqlite is a great embedded database and thanks to use by browsers and on mobile the most used database in the world by orders of magnitude.
But it also comes with lots of limitations.
* there is no type safety, unless you run with the new strict mode, which comes with some significant drawbacks (eg limited to the handful of primitive types)
* very narrow set of column types and overall functionality in general
* the big one for me: limited migration support, requiring quite a lot of ceremony for common tasks (eg rewriting a whole table and swapping it out)
These approaches (like fly.io s) with read replication also (apparently?) seem to throw away read after write consistency. Which might be fine for certain use cases and even desirable for resilience, but can impact application design quite a lot.
With sqlite you have do to a lot more in your own code because the database gives you fewer tools. Which is usually fine because most usage is "single writer, single or a few local readers". Moving that to a distributed setting with multiple deployed versions of code is not without difficulty.
This seems to be mitigated/solved here though by the ability to run worker code "next to the database".
I'm somewhat surprised they went this route. It probably makes sense given the constraints of Cloudflares architecture and the complexity of running a more advanced globally distributed database.
On the upside: hopefully this usage in domains that are somewhat unusual can lead to funding for more upstream sqlite features.