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

91–100 of 165 posts

Re: Distributed SQLite: Paradigm shift or hype?

#91

Post by azeirah »

LVB wrote: post #53
danappelxx wrote: post #12

Earlier quoted context omitted.

I think you always need loading states to account for slow network, or am I missing something?

>to account for slow network Or a slow anything else, e.g., SQLite queries. This thread has focused on the network aspects, and they do stand out since there can be such a large gap between a network call vs a local SSD read. But we're still talking about a database, which could be huge (presumably it's the main, single DB for the whole app). And there is still all of the actual SQLite work that needs to happen to ex…

I think this strongly depends on the application and use-case. I've worked at two businesses so far, and they target small and medium-sized companies in a tenant-style manner.

All data for one customer that is important enough to load easily fits within less than 5MB. That is of course not counting logs and such, but it's all "important" user-specific data. It's not -that- dissimilar from a small to medium-sized redux store in complexity. Lots of toggles, forms, raw text and some relations.

Of course this architecture doesn't scale to the enterprise level, or to other certain heavily data-driven applications (like imagine running the entirety of your sentry database in-browser?), but that's what architecture is -for-! Pick one that synergizes well with your use-case!

Re: Distributed SQLite: Paradigm shift or hype?

#92

Post by gwd »

config_yml wrote: post #63
internetter wrote: post #52

Something I've been thinking about is partitioning my SQLite. Instead of storing all user's data in one mega table, what if I made a SQLite database for each user? Provided users never talk to each other, I think this might work?

You can slice it and dice it any way you want, really. The constraint is often what data needs to be written within a transaction. You'll have to figure our a way to reliably apply a consistent schema to all these database files somehow and keep track of them.

One of the things I appreciate about SQLite is being able to keep all the schema initialization and upgrades in the application itself, which are then checked into git and can be tested like mad with throw-away copies of the data.

Here's a package in golang I wrote to help with that process:

https://pkg.go.dev/gitlab.com/martyros/sqlutil@v0.0.0-202312...

Re: Distributed SQLite: Paradigm shift or hype?

#93

Post by geenat »

infogulch wrote: post #65

If you're willing to accept eventual consistency (a big ask, but acceptable in some scenarios) then there are options like marmot [1] that replicate cdc over nats. [1]: https://github.com/maxpert/marmot

The major downside to marmot right now is schema changes don't propagate.

Otherwise, keeping a very close eye on it.

Re: Distributed SQLite: Paradigm shift or hype?

#94

Post by rapnie »

rapnie wrote: post #57
simonw wrote: post #27

Earlier quoted context omitted.

The key here is to make a single API call to the backend which then runs 100+ SQL queries at once and combines the results into a single JSON response - that way you're only paying the network cost once. See https://www.sqlite.org/np1queryprob.html I've implemented GraphQL on top of SQLite and found it to be an amazingly good match, because the biggest weakness of GraphQL is that it makes it easy to accidentally trig…

> I've implemented GraphQL on top of SQLite and found it to be an amazingly good match Could you give a pointer to the repository, or is this part of Datasette?

Nevermind, found it. Just don't know the license.

https://github.com/simonw/datasette-graphql

Re: Distributed SQLite: Paradigm shift or hype?

#95

Post by cnity »

dns_snek wrote: post #90
cnity wrote: post #77

Earlier quoted context omitted.

This is saying: "just don't try to solve hard data storage problems". Not all applications are CRUD.

You're missing the point. Most software doesn't need to scale or solve hard data storage problems, and if it ends up having to, you can always upgrade to Postgres with minimal effort. That makes SQLite an attractive option if you won't immediately benefit from Postgres' rich features.

We're just going to be speaking past each other, I think. GP clearly stated they lean heavily on Postgres technologies (by calling out specifics like pgvector). Stating that the applications that don't need those technologies, indeed don't need them, is tautological!

Re: Distributed SQLite: Paradigm shift or hype?

#96

Post by cjblomqvist »

TheCapeGreek wrote: post #67
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…

>most SQL will carry over between SQLite and Postgres and MySQL, especially if you add ORMs in the mix I think this goes underappreciated, or rather the opposite is overstated. Sure there are some edge cases that don't work the same, but most apps won't hit those. My _biggest_ gripe with SQLite so far is the lack of column reordering like other DBs. And my simplistic understanding is that the others do it exactly the…

I think the bigger issue for many is that tooling, infra(provider), in-house knowledge/skill/experience as well as optimizations may differ quite a lot.

Of course, this will differ a lot between projects.

Re: Distributed SQLite: Paradigm shift or hype?

#97

Post by boxed »

yurishimo wrote: post #80
boxed wrote: post #70

Earlier quoted context omitted.

You can run postgres on the same host as the web server too. Isn't that going to get you most of that same benefit in speed?

It is. I do this on plenty of hobby Laravel apps.

Me too, but I guess there could be even higher gains with SQLite when it's in-process...

Re: Distributed SQLite: Paradigm shift or hype?

#98

Post by adius »

simonw wrote: post #27
danappelxx wrote: post #21

Earlier quoted context omitted.

Yup, exactly. Phones change wifi networks, routers drop packets, load balancers get overloaded. Hard to fully eliminate tail latencies.

The key here is to make a single API call to the backend which then runs 100+ SQL queries at once and combines the results into a single JSON response - that way you're only paying the network cost once. See https://www.sqlite.org/np1queryprob.html I've implemented GraphQL on top of SQLite and found it to be an amazingly good match, because the biggest weakness of GraphQL is that it makes it easy to accidentally trig…

FYI: We're offering a SQLite hosting service which automatically creates a full-fledged GraphQL endpoint for your database: https://www.airsequel.com

Re: Distributed SQLite: Paradigm shift or hype?

#99

Post by _heimdall »

rozenmd wrote: post #51
_heimdall wrote: post #44

Earlier quoted context omitted.

D1 is still pretty limited in my experience. No read replicas really kills any of the meaningful benefit of the architecture, and being built off of a (mostly) SQLite-compliant API makes me nervous as it isn't really SQLite at all. Last year's major Cloudflare outage really was the final straw for me with regards to D1. I don't mean that as a knock at Cloudflare at all, the situation sounded horrible and I appreciate…

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 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.

Re: Distributed SQLite: Paradigm shift or hype?

#100

Post by simonw »

TheCapeGreek wrote: post #67
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…

>most SQL will carry over between SQLite and Postgres and MySQL, especially if you add ORMs in the mix I think this goes underappreciated, or rather the opposite is overstated. Sure there are some edge cases that don't work the same, but most apps won't hit those. My _biggest_ gripe with SQLite so far is the lack of column reordering like other DBs. And my simplistic understanding is that the others do it exactly the…

If you want a more convenient way to do column reordering (and other advanced alter table operations) in SQLite my sqlite-utils CLI tool can do this:

    sqlite-utils transform data.db mytable \
      -o id -o title -o description
That will change the order of the columns in the specified table such that id, title and description come first.

The same command can handle many other operations such as changing column types, renaming columns or assigning a new primary key.

https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

Post reply on HN