Earlier quoted context omitted.
The idea is that you're not doing that.
Unless your database is in the browser, you are always going to be at mercy of network latencies talking to the backend.
You can with sqlite in webassembly
Distributed SQLite: Paradigm shift or hype?
61–70 of 165 posts
Re: Distributed SQLite: Paradigm shift or hype?
#62Most apps never need to scale. Also, worried about scaling? Just use an ORM that allows you to switch from SQLite to Postgres. It’s as simple as that.
Unless your using database specific features. One of the biggest advantages for Postgres is how incredible the ecosystem is. It doesn't work for everything, but I have an OEM, multiple kinds of text search (vector, inverted indexes, trigrams), recursive and graph-like queries (though that's admittedly less of an issue if n+1 isn't a problem), row-level acls, locks, etc. It's really nice to have all of that power avai…
Not really a problem if you go from sqlite to postgres. Which sqlite feature is missing from
Postgres?
Re: Distributed SQLite: Paradigm shift or hype?
#63Something 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.
Re: Distributed SQLite: Paradigm shift or hype?
#64I wonder if SQLite will ever be modified to allow concurrent writes. Right now it locks the whole DB on write [1]. But since SQLite usage is rapidly expanding from its embedded db roots, one has to wonder if the devs have considered creating some sort of SQLite daemon, or if the underlying architecture would have to be changed so drastically it wouldn't be worth it. 1. https://www.sqlite.org/faq.html#q5
In most SQL implementation, modification of row is table level locking anyway, so you can put tables in different sqlite db files and achieve same "concurrent" writes.
Re: Distributed SQLite: Paradigm shift or hype?
#65If 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.
Re: Distributed SQLite: Paradigm shift or hype?
#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.
Rewrite that: "Bazillions of engineering hours have been spent to make XYZ the best backend database..."
Re: Distributed SQLite: Paradigm shift or hype?
#67LiteFS 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 same way as you'd do it manually with SQLite - table gets _replaced_ with an identical table with the data correctly ordered and the data is shoved into the new table.
Re: Distributed SQLite: Paradigm shift or hype?
#68Is there a distributed version of SQLite that keeps its embedded library feature? For example, it could use EBS and S3 for shared storage, allowing for distributed read and write access, and possibly even multiple concurrent reads and writes.
Should this be available, numerous lightweight web applications could operate without having to set up a separate PostgreSQL or MySQL database.
Re: Distributed SQLite: Paradigm shift or hype?
#69We have so many distributed X applications nowadays that all try to solve the same problem, either in the same or different ways. I think we first have to come up with a simple, distributed, open-source storage solution. In the cloud, we have things like AWS S3, which is a very reliable distributed storage, but for self-hosting, we have:
Ceph, with which I have much experience, is a very solid and quite bulletproof storage solution that offers S3 protocol and FS. However, maintaining it in the long run is really challenging. You better become a Ceph expert.
SeaweedFS struggles with managing large data groups. It's inspired by an outdated Facebook study (Haystack) and is intended for storing and sharing large images. However, I think it's only average—it has poor documentation, underwhelming performance, and a confusing set of components to install. Its design allows each server process to use one big file for storage, bypassing slow file metadata operations. It offers various access points through gateways.
MinIO has evolved a lot recently, making it hard to evaluate. MinIO relies on many small databases. Currently, it's phasing out some features, like the gateway, and mainly consists of two parts: a command line interface (CLI) and a server. While MinIO's setup is complex, SeaweedFS's setup is much simpler. MinIO also seems to be moving from an open-source model towards a more commercial one, but I have not closely followed this transition.
All of these solutions are not simple enough to be the base for a distributed database application. What we really need would be something like an Ext4 successor, let's call it Ext5, with native distributed storage capabilities in the most dead-simple way. ZFS is another good candidate. ZFS has already solved the problem of how to distribute storage across multiple hard drives within one server very well, but it still lacks a good solution on how to distribute storage across different hard drives on different servers connected via a network.
Yes, I know there is the CAP theorem, so it is really a hard challenge to solve, but I think we can do better in terms of self-hosted solutions.
Re: Distributed SQLite: Paradigm shift or hype?
#70I 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…
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?