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

Distributed SQLite: Paradigm shift or hype?

kerkour.com

Post reply on HN
Print view View original

121–130 of 165 posts

Re: Distributed SQLite: Paradigm shift or hype?

#121

Post by skybrian »

_heimdall wrote: post #99
rozenmd wrote: post #51

Earlier quoted context omitted.

D1 (the ability to query your database at the edge) didn't get knocked offline during that outage though?

We did actually run into D1 issues during the outage, though I don't remember exact details on what was down. Our issues may have been API related with reads still functional. My concerns, and this is very much my own concerns with no context of what has or is happening internally st Cloudflare, is that the outage exposed some serious issues that will take time to fix safely. The fact that D1 still doesn't support re…

There’s no way of knowing from the outside, but nothing in the April 1 announcement for D1 suggests deprioritization to me. They just announced that they’re doing read replication, describing how it will work in detail. Why preannounce it if they’re not working on it?

My guess is that they wanted it for 1.0 but the release slipped. It happens.

Re: Distributed SQLite: Paradigm shift or hype?

#122

Post by elithrar »

_heimdall wrote: post #99
rozenmd wrote: post #51

Earlier quoted context omitted.

D1 (the ability to query your database at the edge) didn't get knocked offline during that outage though?

We did actually run into D1 issues during the outage, though I don't remember exact details on what was down. Our issues may have been API related with reads still functional. My concerns, and this is very much my own concerns with no context of what has or is happening internally st Cloudflare, is that the outage exposed some serious issues that will take time to fix safely. The fact that D1 still doesn't support re…

> The fact that D1 still doesn't support replication is an indication to me that it has been deprioritized, likely with other newer and less used products, while the infrastructure updates are dealt with.

D1 is definitely not deprioritized. We're heads down on replication, and it's important for us to get it right. Takes time!

Re: Distributed SQLite: Paradigm shift or hype?

#123

Post by samatman »

masklinn wrote: post #109
simonw wrote: post #101

Earlier quoted context omitted.

I'm pretty sure SQLite has covering indexes. And the relatively new strict mode should enforce at least basic types (though if you want to enforce your own rules for things like dates you're still on your own).

> I'm pretty sure SQLite has covering indexes. I checked to be sure I had not missed it, and didn’t find anything. You have expressions and conditions, but no covering. Obviously you can kinda emulate it by adding the columns you want to cover to the key, but… > though if you want to enforce your own rules for things like dates you're still on your own That’s what I was talking about, having richer types, and the abi…

SQLite has what they "call a covering index", see point 9 here: https://www.sqlite.org/optoverview.html

My impression is that this mechanism is less general than what one finds in full-fat client-server SQLite databases.

Re: Distributed SQLite: Paradigm shift or hype?

#124

Post by emmanueloga_ »

simonw wrote: post #105
emmanueloga_ wrote: post #40

Also I think sqlite would not be a good fit if any sort of slow workers / background jobs are required.

Why not? (I'm planning a background job system based around SQLite at the moment)

Because typically all writes need to happen from a single process, so if you want to run multiple processes writing to the same DB, you need to synchronize them somehow.

If you are running a workers loop together with your http serving loop, running on the same process is awkward: you would need to stop serving your webapp each time you want to deploy new workers. Also you would need to wait until all workers are done before you could redeploy the app. If one of the workers does something unexpected, it could take down your webapp together with any other workers running, etc.

If you used multiple processes you would need to perform sync through some IPC or something like redis to perform writes sequentially, but using a DB that already ships as a daemon would fit the problem better.

Re: Distributed SQLite: Paradigm shift or hype?

#125

Post by SJC_Hacker »

benbjohnson wrote: post #43

LiteFS author here. I don't disagree with any points in the article but perhaps a reframing could help. I previously wrote a tool called Litestream that would do disaster recovery for a single-node SQLite server and I still think it's a great default option for people starting new projects. Unless you're doing very database-specific things, most SQL will carry over between SQLite and Postgres and MySQL, especially if…

SQLites handling of dates is pretty kludgy.

It stores them as strings, so to do something like extract just the year from a date, you have to do 'CAST(substr(game_date,0,5) AS INTEGER).'

Hackish and error prone.

Re: Distributed SQLite: Paradigm shift or hype?

#126

Post by masklinn »

samatman wrote: post #123
masklinn wrote: post #109

Earlier quoted context omitted.

> I'm pretty sure SQLite has covering indexes. I checked to be sure I had not missed it, and didn’t find anything. You have expressions and conditions, but no covering. Obviously you can kinda emulate it by adding the columns you want to cover to the key, but… > though if you want to enforce your own rules for things like dates you're still on your own That’s what I was talking about, having richer types, and the abi…

SQLite has what they "call a covering index", see point 9 here: https://www.sqlite.org/optoverview.html My impression is that this mechanism is less general than what one finds in full-fat client-server SQLite databases.

Ya if my reading is correct this is the poor man's covering index: if all the requested data is in the index key the query will not hit the table, so you can add additional fields at the end of the key to get index-only scans (at a cost, also some flexibility cost e.g. doesn't work with unique indexes).

I guess it's less of an issue in sqlite than in databases with richer datatypes in the sense that all datatypes are ordered and thus indexable.

Re: Distributed SQLite: Paradigm shift or hype?

#127

Post by samatman »

simonw wrote: post #104
tptacek wrote: post #54

Earlier quoted context omitted.

You can attach to databases dynamically in queries and join across them. I probably wouldn't (in an ordinary data model) do per-user, but I would consider it for different functional areas.

There's a limit on how many databases you can attach to the same connection (SQLITE_LIMIT_ATTACHED), it defaults to 10.

Worth noting that this limit can be raised up to 125 (as I'm sure Simon is aware).

I would say that if one needs to query across more SQLite files than that, it's definitely time for a different data policy.

Re: Distributed SQLite: Paradigm shift or hype?

#129

Post by mixmastamyk »

jkestner wrote: post #118
candiddevmike wrote: post #108

Earlier quoted context omitted.

You've been able to build these apps for years now, hell PouchDB was released in 2012. I don't think anything significant is going to push for more of these apps--the DX isn't really that great compared to remote write--there are more abstractions, less caching, way more corner cases, and I call BS on your conflict free utopia.

I'm using Pouch/Couch on a web app, and the sync is really good DX - I got all the functionality I wanted, including easy undo, in a day or so. So yeah, what more DX would actually make people adopt local-first? I don't think it's a dev problem, but a business one. No consumer is demanding local-first, and no company wants to give up that profitable data. We're doing it because we're purposely not interested in users…

I think you’re on the right track, though local-first doesn’t necessarily mean you give up on centralized data. That’s where the sync ultimately ends up, right?
Post reply on HN