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

111–120 of 165 posts

Re: Distributed SQLite: Paradigm shift or hype?

#111

Post by zackify »

danappelxx wrote: post #12
zackify wrote: post #8

I think this skips one mega benefit for apps. I’ve been using liteFS in production for a couple months. Your web app is able to resolve db queries instantly. You don’t need loading states if you’re using complex charts and other frontend JS that waits for data. All the data is resolved so fast and you can just return all your data like more traditional apps, and the load times are insane. If you’re multi region you c…

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

I “use the platform” so clicking a new tab shows the browser loading state. If you’re on a slow network, that’s the way I go about it.

Re: Distributed SQLite: Paradigm shift or hype?

#112

Post by cp9 »

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?

at least for awhile this is how bluesky/atproto worked. afaik they only ran into issues when the number of users on each server overwhelmed how many files would fit comfortably in a single directory (which is obviously a large number)

https://news.ycombinator.com/item?id=38171322

Re: Distributed SQLite: Paradigm shift or hype?

#113

Post by simonw »

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…

"Obviously you can kinda emulate it by adding the columns you want to cover to the key"

Right, that seems like a good solution to me.

Re: Distributed SQLite: Paradigm shift or hype?

#114

Post by uhoh-itsmaciek »

samwillis wrote: post #74

The paradigm shift that's going to come with distributed SQLite isn't to the edge, it's going to be to users devices. I believe the DX of building Local-first apps is going to hit the criticality point in the next year or so and its popularity is going to explode. With dynamic partial replication you can synchronise a subset of your database to a SQLite db on your users device, eliminating the network from the ui int…

What do you think about non-SQL CRDT frameworks like Automerge and Y.js and how they fit into this local-first future?

Re: Distributed SQLite: Paradigm shift or hype?

#115

Post by elithrar »

> What a nightmare! Now your application code spills into your database and our initial goal of simplifying application development is nothing but a long-forgotten dream. All of that for what? To save a few milliseconds to display a web page.

I think "a few milliseconds" vastly understates this: if you want to run your application closer to users, even just across the US, each query is (at least) 70ms just to get over the network and back again.

"Application code spills into your database" was a bad thing when you wrote one language (say, Java, or PHP) and another language (PSQL/TSQL/etc) for your "stored procedures", but that's not what most modern databases are advocating for.

Instead, and not unlike something like React Server Components (RSC), you can choose whether to run code close to the user or closer to the DB (for transactions) in the same language as your application, because it's still part of your application code. This is the model that Durable Objects[1], our coordinated storage service, uses.

Disclaimer: I work on D1 & Durable Objects at Cloudflare, so I'm likely to be called biased here, but it's not like we haven't a) thought about this deeply and b) actually use D1 and Durable Objects to build distributed systems at Cloudflare.

[1]: https://blog.cloudflare.com/durable-objects-easy-fast-correc...

Re: Distributed SQLite: Paradigm shift or hype?

#116

Post by throwaway2037 »

znpy wrote: post #81
throwaway2037 wrote: post #66

> While SQLite is a really amazing database, most teams will benefit from avoiding it and going the PostgreSQL way instead. > Bazillions of engineering hours have been spent to make Postgres the best backend database and choosing SQLite will inevitably force you to reinvent what Postgres already had for many years, in a fragile and buggy way. Could the same not also be said for MS SQL Server, Oracle, Sybase, MySQL, o…

> Rewrite that: "Bazillions of engineering hours have been spent to make XYZ the best backend database..." What you're saying (and what the author is saying) however is clashing with the reality of so many developers using sqlite and being happy with it. I'd suggest to rewrite it another way: > Bazillion of developers think they'll need a full-fledged database for their new project while sqlite will cover most of the…

This is a good reply. I want to clarify why I excluded SQLite in that list: It is not a database server (all of the others are); it is an embedded database. I say that with zero disrespect. I am 110% a SQLite fanboi. Honestly, I have no experience with PosgreSQL, but I have heard a lot of good things about it. The community looks amazing.

Re: Distributed SQLite: Paradigm shift or hype?

#117

Post by throwaway2037 »

klysm wrote: post #78
throwaway2037 wrote: post #66

> While SQLite is a really amazing database, most teams will benefit from avoiding it and going the PostgreSQL way instead. > Bazillions of engineering hours have been spent to make Postgres the best backend database and choosing SQLite will inevitably force you to reinvent what Postgres already had for many years, in a fragile and buggy way. Could the same not also be said for MS SQL Server, Oracle, Sybase, MySQL, o…

Without giving evidence, I do agree that Postgres is a better database that serves the general case better. I use both in production for different purposes, but postgresql makes a lot more sense then SQLite as our primary database

Fair comment. First hand experience is important. HN is great for people sharing their technical experiences. I would not say that is "without giving evidence"!

Re: Distributed SQLite: Paradigm shift or hype?

#118

Post by jkestner »

candiddevmike wrote: post #108
samwillis wrote: post #74

The paradigm shift that's going to come with distributed SQLite isn't to the edge, it's going to be to users devices. I believe the DX of building Local-first apps is going to hit the criticality point in the next year or so and its popularity is going to explode. With dynamic partial replication you can synchronise a subset of your database to a SQLite db on your users device, eliminating the network from the ui int…

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' data and resiliency is part of our value—but we're still holding some of it for consumer convenience.

Re: Distributed SQLite: Paradigm shift or hype?

#119

Post by masklinn »

simonw wrote: post #113
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…

"Obviously you can kinda emulate it by adding the columns you want to cover to the key" Right, that seems like a good solution to me.

It’s a workaround, but it bloats the interior pages of the index with the covering data, which increases the size of the index and makes lookup less efficient (as they have to traverse more interior pages, and since there are more pages those are less likely to remain in cache).

Re: Distributed SQLite: Paradigm shift or hype?

#120

Post by winwang »

samwillis wrote: post #74

The paradigm shift that's going to come with distributed SQLite isn't to the edge, it's going to be to users devices. I believe the DX of building Local-first apps is going to hit the criticality point in the next year or so and its popularity is going to explode. With dynamic partial replication you can synchronise a subset of your database to a SQLite db on your users device, eliminating the network from the ui int…

Do you think SQLite performance on user devices changes anything for UX/DX? i.e. if SQLite were 10x faster than today on user devices in the future.
Post reply on HN