5 ms·

All this recent hype around sqlite... sqlite is a great embedded database and thanks to use by browsers and on mobile the most used database in the world by or

All this recent hype around sqlite... 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.

vlovich123 4y ago
D1 does not throw away consistency. It’s built on top of Durable Objects which is globally strongly consistent.
- mwcampbell 4y ago
  Interesting that D1 is built on top of Durable Objects. Does this mean that it would be practical for a single worker to access multiple D1 databases, so it could use, for example, a separate database for each tenant in a B2B SaaS application? Edit: And could each database be in a different primary region?
  - a-robinson 4y ago
    Yes, exactly!
- kwizzt 4y ago
  I’m not familiar with Durable Objects. When D1 does replication to read replicas, if it’s not doing synchronous replication, then it’s not strongly consistent, is that correct?
- hn_ei_ser_23 4y ago
  That is interesting. I wish CF would give us some more information as I've assumed that there must be a lack of strong consistency which would be a major drawback. 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?
- the_duke 4y ago
  I wish the post had provided some more technical details. It's more of a "quickstart" than a peek under the hood.
  - unraveller 4y ago
    I'd like to see some up front D1 & R2 benchmarks (read/write/iops). I can't judge invocation cost value until I can judge my use case performance. Here's hoping its -gt NVMe raid 10 under the hood of D1 as some big SQLite reads suffer under slow storage.
- smarx007 4y ago
  "D1 will create read-only clones of your data, close to where your users are, and constantly keep them up-to-date with changes." 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 :)
  - geelen 4y ago
    Yeah, so you can always opt-in to strong consistency by transferring execution to the primary (see the "Embedded Compute" section of the blog). Then it's pretty much exactly the same as a DO.
- jpcapdevila 4y ago
  Are you guys using litestream or a similar approach? E.g storing WAL frames in a durable object.
- greg-m 4y ago
  Just clarifying - D1 without read replicas is strongly consistent. If you add read replicas, those can have replication lag and will not be strongly consistent. Disclaimer: I work at Cloudflare :)
  - infogulch 4y ago
    Thanks for the clarification, that is what I would expect. 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?)
    - vlovich123 4y ago
      Yup sorry about that. I missed the entire "read replica" bit when reading that blog post.
    - discodave 4y ago
      Since it's a relational DB, and supports transactions, you can have a journal table right? I know of a very important system at AWS that did this with MySQL :D
      - infogulch 4y ago
        Yes you could do it manually, but it would be nice if the solution didn't require carefully managing update queries so the journal addition isn't missed and increasing write amplification to manually update a journal table when that information probably already exists somewhere in the WAL implementation.
vaughan 4y ago
Has anyone tried to write a new modern SQLite?
- chrisshroba 4y ago
  DuckDB comes to mind, but I can't speak to its differences from SQLite. https://duckdb.org/ https://duckdb.org/
  - anyfactor 4y ago
    I haven't tried duckdb but I have been googling about it. I think I saw a discussion where it was mentioned that duckdb isn't a replacement for SQLite. It is an OLAP database [0] which makes its ingestion time slower than SQLite, I think. So it is meant for analytics but not as fullfledge replacement for SQLite. [0]: https://en.wikipedia.org/wiki/Online_analytical_processing https://en.wikipedia.org/wiki/Online_analytical_processing Duckdb on HN: https://news.ycombinator.com/item?id=23287278 https://news.ycombinator.com/item?id=23287278
    - 1egg0myegg0 4y ago
      Close! DuckDB has very fast bulk insert speeds, but slower individual row insertion/update speeds. (Disclaimer: I write docs for DuckDB)
      - deleted 4y ago
        [deleted]
  - gigatexal 4y ago
    DuckDB is Olap SQLite. The vector engine is dope. But most of the innovation is in the OLAP stuff.
- sophacles 4y ago
  Why? Yes sqlite doesn't have all the features postgres has. Postgres doesn't have all the features the sqlite has either. What's wrong with having different tools with different sets tradeoffs. It's a different shape of Lego and that's fine - some things call for a 1/3height 2x2 and others call for a full height 1x8.
jambutters 4y ago
What types are missing from strict that you need?
- deleted 4y ago
  [deleted]
prirun 4y ago
* the big one for me: very limited migration support, requiring quite a lot of ceremony for common tasks (eg rewriting a whole table and swapping it out) 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 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.
- the_duke 4y ago
  For a long time sqlite did not have DROP COLUMN and RENAME COLUMN support, which are both pretty essential. 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.
  - teraflop 4y ago
    I'm not sure what you mean by "not transactional". SQLite implements transaction support at the "page" level, and builds all other database operations on top of it, which means anything that touches the bytes of the database file is transaction-safe. You can verify this for yourself: 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 It's of course still subject to SQLite's normal restrictions on locking, which means a long-running ALTER statement will block concurrent writers (and probably also concurrent readers if you're not running in WAL mode).
  - 4y ago
sorenbs 4y ago
Migrations have gotten better recently, but there are still cases where you need to follow the 12 steps very carefully: https://www.sqlite.org/lang_altertable.html#otheralter https://www.sqlite.org/lang_altertable.html#otheralter Prisma Migrate can automatically generate these steps, removing most of the pain. I'm sure other migration tools can do this as well.
- llimllib 4y ago
  simonw's sqlite-utils can help here too: https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
deleted 4y ago
[deleted]

vlovich123 4y ago

D1 does not throw away consistency. It’s built on top of Durable Objects which is globally strongly consistent.

mwcampbell 4y ago
Interesting that D1 is built on top of Durable Objects. Does this mean that it would be practical for a single worker to access multiple D1 databases, so it could use, for example, a separate database for each tenant in a B2B SaaS application? Edit: And could each database be in a different primary region?
- a-robinson 4y ago
  Yes, exactly!
kwizzt 4y ago
I’m not familiar with Durable Objects. When D1 does replication to read replicas, if it’s not doing synchronous replication, then it’s not strongly consistent, is that correct?
hn_ei_ser_23 4y ago
That is interesting. I wish CF would give us some more information as I've assumed that there must be a lack of strong consistency which would be a major drawback. 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?
the_duke 4y ago
I wish the post had provided some more technical details. It's more of a "quickstart" than a peek under the hood.
- unraveller 4y ago
  I'd like to see some up front D1 & R2 benchmarks (read/write/iops). I can't judge invocation cost value until I can judge my use case performance. Here's hoping its -gt NVMe raid 10 under the hood of D1 as some big SQLite reads suffer under slow storage.
smarx007 4y ago
"D1 will create read-only clones of your data, close to where your users are, and constantly keep them up-to-date with changes." 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 :)
- geelen 4y ago
  Yeah, so you can always opt-in to strong consistency by transferring execution to the primary (see the "Embedded Compute" section of the blog). Then it's pretty much exactly the same as a DO.
jpcapdevila 4y ago
Are you guys using litestream or a similar approach? E.g storing WAL frames in a durable object.
greg-m 4y ago
Just clarifying - D1 without read replicas is strongly consistent. If you add read replicas, those can have replication lag and will not be strongly consistent. Disclaimer: I work at Cloudflare :)
- infogulch 4y ago
  Thanks for the clarification, that is what I would expect. 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?)
  - vlovich123 4y ago
    Yup sorry about that. I missed the entire "read replica" bit when reading that blog post.
  - discodave 4y ago
    Since it's a relational DB, and supports transactions, you can have a journal table right? I know of a very important system at AWS that did this with MySQL :D
    - infogulch 4y ago
      Yes you could do it manually, but it would be nice if the solution didn't require carefully managing update queries so the journal addition isn't missed and increasing write amplification to manually update a journal table when that information probably already exists somewhere in the WAL implementation.

vaughan 4y ago

Has anyone tried to write a new modern SQLite?

chrisshroba 4y ago
DuckDB comes to mind, but I can't speak to its differences from SQLite. https://duckdb.org/ https://duckdb.org/
- anyfactor 4y ago
  I haven't tried duckdb but I have been googling about it. I think I saw a discussion where it was mentioned that duckdb isn't a replacement for SQLite. It is an OLAP database [0] which makes its ingestion time slower than SQLite, I think. So it is meant for analytics but not as fullfledge replacement for SQLite. [0]: https://en.wikipedia.org/wiki/Online_analytical_processing https://en.wikipedia.org/wiki/Online_analytical_processing Duckdb on HN: https://news.ycombinator.com/item?id=23287278 https://news.ycombinator.com/item?id=23287278
  - 1egg0myegg0 4y ago
    Close! DuckDB has very fast bulk insert speeds, but slower individual row insertion/update speeds. (Disclaimer: I write docs for DuckDB)
    - deleted 4y ago
      [deleted]
- gigatexal 4y ago
  DuckDB is Olap SQLite. The vector engine is dope. But most of the innovation is in the OLAP stuff.
sophacles 4y ago
Why? Yes sqlite doesn't have all the features postgres has. Postgres doesn't have all the features the sqlite has either. What's wrong with having different tools with different sets tradeoffs. It's a different shape of Lego and that's fine - some things call for a 1/3height 2x2 and others call for a full height 1x8.

jambutters 4y ago

What types are missing from strict that you need?

deleted 4y ago
[deleted]

prirun 4y ago

* the big one for me: very limited migration support, requiring quite a lot of ceremony for common tasks (eg rewriting a whole table and swapping it out) 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 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.

the_duke 4y ago
For a long time sqlite did not have DROP COLUMN and RENAME COLUMN support, which are both pretty essential. 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.
- teraflop 4y ago
  I'm not sure what you mean by "not transactional". SQLite implements transaction support at the "page" level, and builds all other database operations on top of it, which means anything that touches the bytes of the database file is transaction-safe. You can verify this for yourself: 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 It's of course still subject to SQLite's normal restrictions on locking, which means a long-running ALTER statement will block concurrent writers (and probably also concurrent readers if you're not running in WAL mode).
- 4y ago

sorenbs 4y ago

Migrations have gotten better recently, but there are still cases where you need to follow the 12 steps very carefully: https://www.sqlite.org/lang_altertable.html#otheralter https://www.sqlite.org/lang_altertable.html#otheralter Prisma Migrate can automatically generate these steps, removing most of the pain. I'm sure other migration tools can do this as well.

llimllib 4y ago
simonw's sqlite-utils can help here too: https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

deleted 4y ago

[deleted]