Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Zero-latency SQLite storage in every Durable Object

simonwillison.net

Post reply on HN
Print view View original

91–100 of 108 posts

Re: Zero-latency SQLite storage in every Durable Object

#91

Post by kentonv »

emadda wrote: post #85
matharmin wrote: post #65

Earlier quoted context omitted.

Just wondering, do you have a specific use case for read transactions implemented on the database level here? In SQLite in general read transactions are useful since you can access the same database from multiple processes at a time. Here, only a single process can access the database. So you can get the same effect as read transactions either by doing all reads in one synchronous function, or implement your own proc…

E.g. if you have many websocket connections and they each have a snapshot at a point in time (that spans over many different await function calls/ws messages). SQLite can have many readers and a single writer with WAL, so a many read transactions can exist whilst the writers move the db state forward.

We (Cloudflare) have considered adding an API to create multiple "database connections", especially to be able to stream a response from a long-running cursor while representing a consistent snapshot of the data.

It's a bit tricky since if you hold open that old connection, the WAL could grow without bound and cannot be checkpointed back into the main database. What do we do when the WAL gets unreasonably large (e.g. bigger than the database)? Cancel old cursors so we can finally checkpoint? Will that be annoying for app developers to deal with, e.g. causing errors when traffic is high?

SQLite itself calls an open database a "connection" even though there's no actual network involved.

Re: Zero-latency SQLite storage in every Durable Object

#92

Post by avinassh »

I'd love to know how they have hooked VFS with WAL to monitor changes. The SQLite's WAL layer deals with page numbers where as VFS deals with file and byte offsets. I am curious to understand how they mapped it, how they get new writes to the WAL and read from the WAL.

Re: Zero-latency SQLite storage in every Durable Object

#93

Post by bluehatbrit »

This is probably a really stupid question, but how would one handle schema migrations with this kind of setup? My understanding is it's aimed at having a database per-tenant (or even more broken down than that). Is there a sane way of handling schema migrations, or is the expectation that these databases are more short-lived and so you support multiple versions of the db (DO) until it's deleted?

In my head, this would be a fun way to build a bookmark service with a DO per user. But as soon as you want to add a new field to an existing table, you meet a pretty tricky problem of getting that change to each individual DO. Perhaps that example is too long lived though, and this is designed for more ephemeral usage.

If anyone has any experience with this, I'd be really interested to know what you're doing.

Re: Zero-latency SQLite storage in every Durable Object

#94

Post by simonw »

CyberDildonics wrote: post #90

What is the difference between a "durable object" and a file?

You can read the full article for an answer to that: https://blog.cloudflare.com/sqlite-in-durable-objects/

Short version: it's replicated to five data centers on every transaction, and backed up as a stream to object storage as well.

Re: Zero-latency SQLite storage in every Durable Object

#95

Post by simonw »

bluehatbrit wrote: post #93

This is probably a really stupid question, but how would one handle schema migrations with this kind of setup? My understanding is it's aimed at having a database per-tenant (or even more broken down than that). Is there a sane way of handling schema migrations, or is the expectation that these databases are more short-lived and so you support multiple versions of the db (DO) until it's deleted? In my head, this woul…

You'd need to roll your own migrations.

I have a version of that for SQLite written in Python, but I'm not sure if you could run that in Durable Objects - maybe via WASM and PyOdide? Otherwise you'd have to port it to JavaScript.

https://github.com/simonw/sqlite-migrate

Re: Zero-latency SQLite storage in every Durable Object

#96

Post by bluehatbrit »

simonw wrote: post #95
bluehatbrit wrote: post #93

This is probably a really stupid question, but how would one handle schema migrations with this kind of setup? My understanding is it's aimed at having a database per-tenant (or even more broken down than that). Is there a sane way of handling schema migrations, or is the expectation that these databases are more short-lived and so you support multiple versions of the db (DO) until it's deleted? In my head, this woul…

You'd need to roll your own migrations. I have a version of that for SQLite written in Python, but I'm not sure if you could run that in Durable Objects - maybe via WASM and PyOdide? Otherwise you'd have to port it to JavaScript. https://github.com/simonw/sqlite-migrate

Appreciate the response (and the blog post itself)! I probably worded my question poorly, but I'm more wondering about executing schema migrations against a large number of DO's as part of a deployment (such as 1 per customer).

I suppose the answer is "it's easier to have 1 central database/DO", but it feels like this approach to data storage really shines when you can have a DO per tenant.

Re: Zero-latency SQLite storage in every Durable Object

#98

Post by simonw »

bluehatbrit wrote: post #96
simonw wrote: post #95

Earlier quoted context omitted.

You'd need to roll your own migrations. I have a version of that for SQLite written in Python, but I'm not sure if you could run that in Durable Objects - maybe via WASM and PyOdide? Otherwise you'd have to port it to JavaScript. https://github.com/simonw/sqlite-migrate

Appreciate the response (and the blog post itself)! I probably worded my question poorly, but I'm more wondering about executing schema migrations against a large number of DO's as part of a deployment (such as 1 per customer). I suppose the answer is "it's easier to have 1 central database/DO", but it feels like this approach to data storage really shines when you can have a DO per tenant.

A pattern where you check for and then execute any necessary migrations on initialization of a Durable Object would actually work pretty well I think - presumably you can update the code for these things without erasing the existing database?

Re: Zero-latency SQLite storage in every Durable Object

#99

Post by simonw »

kikimora wrote: post #97

This design does not handle hot partitions well and they are ubiquitous to so many domains.

Your partition would have to be VERY hot for SQLite not to be able to handle it - anything up to several thousand writes per second would likely work fine.

Since this is all running on Cloudflare you could scale reads with a 1 second cache TTL somewhere, which would drop your incoming read queries to around one per second no matter how much read traffic you had.

Re: Zero-latency SQLite storage in every Durable Object

#100

Post by bluehatbrit »

simonw wrote: post #98
bluehatbrit wrote: post #96

Earlier quoted context omitted.

Appreciate the response (and the blog post itself)! I probably worded my question poorly, but I'm more wondering about executing schema migrations against a large number of DO's as part of a deployment (such as 1 per customer). I suppose the answer is "it's easier to have 1 central database/DO", but it feels like this approach to data storage really shines when you can have a DO per tenant.

A pattern where you check for and then execute any necessary migrations on initialization of a Durable Object would actually work pretty well I think - presumably you can update the code for these things without erasing the existing database?

Ah yes, that would work pretty well! You'd have to be able to guarantee any migrations can run within the timeouts, but at a per-tenant level that should be very doable for most cases. Not sure why I didn't think of that approach - great idea.

I might have to try this out now.

Post reply on HN