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

81–90 of 165 posts

Re: Distributed SQLite: Paradigm shift or hype?

#81

Post by znpy »

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

Re: Distributed SQLite: Paradigm shift or hype?

#82

Post by datadeft »

I think this is a false dichotomy to try to frame there is a Sqlite vs Postgres situation going on. Sqlite is amazing super fast single writer database without network service so it can be used locally as a library mostly while Postgres is a full fledged RDBMS. Both have use-cases that the given service is a better option and there are many other software products out there to be used for similar purposes.

Notable SQLite use cases: https://www.sqlite.org/famous.html

Postgres does not have a similar page: https://www.postgresql.org/about/press/faq/

Re: Distributed SQLite: Paradigm shift or hype?

#83

Post by masklinn »

dkjaudyeqooe wrote: post #72
TheCapeGreek wrote: post #67

Earlier quoted context omitted.

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

Why would you want to recorder columns? SQLite reads in a whole record at a time to access any column.

Because as in structs padding slack can lead to a surprising amount of overhead.

Re: Distributed SQLite: Paradigm shift or hype?

#84

Post by moritzruth »

tutfbhuf wrote: post #69

We 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 s…

There is also Garage: https://garagehq.deuxfleurs.fr/

Re: Distributed SQLite: Paradigm shift or hype?

#85

Post by masklinn »

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…

> Sure there are some edge cases that don't work the same, but most apps won't hit those.

That really depends on your modelling style. If you like things like types, SQL-side processing (eg using functions), or covering indexes, then you’ll hit issues every five minutes in sqlite.

SQLite really wants the logic (including consistency logic) in the application, just compare the list of aggregate functions in postgres versus sqlite, or consider that you have to enable FKs on a per-connection basis.

Which I guess is why ORMs help a lot: they are generally based on application-side logic and LCD database.

Re: Distributed SQLite: Paradigm shift or hype?

#86

Post by gwd »

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?

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

I'm doing this in a project I'm developing for language learning, except that you have both shared databases for content, and individual databases for view logs, preferences, and so on. What I actually do is open a :memory: database, ATTACH all the appropriate databases. Transactions work just fine, but because the shared database is basically read-only, then there's no write contention because each user is just writing to their own database. Overall it makes queries easier, because you don't even need to include the user (or the language). (Of course, the flip side is that getting stats on all the users and languages is more difficult.)

Currently it's just single server, but it should be possible to read-replicate the content, and actually move the write replica of the study database to a local server. It should also make it straightforward to let people download their own information: just hand them the actual SQLite file.

If I ever grow large enough that I need multiple servers in different geos, I'll write up my experience and post it here.

Re: Distributed SQLite: Paradigm shift or hype?

#87

Post by meindnoch »

datadrivenangel wrote: post #14
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?

The SQLite database is located on the application server, so there is no network between the DB and the app.

And your users are sitting in front the application server?

Re: Distributed SQLite: Paradigm shift or hype?

#88

Post by lxpz »

nurettin wrote: post #59

The only way I know for doing concurrent writes in sqlite is to open a transaction, accumulate a bunch of writes and then commit them. Otherwise it is dog slow. And it has to be a single process, or you get data corruption. Has this changed somehow?

This is due to SQLite calling fsync a lot by default, to be on the safe side. You can use pragma journal_mode = "wal" and pragma synchronous = "off" and it should be much faster, without risking corruption if your server powers down unexpectedly (at least in theory, you should still make regular backups, which can be done from within a running sqlite instance using the backup command)

Re: Distributed SQLite: Paradigm shift or hype?

#89

Post by klabb3 »

tutfbhuf wrote: post #69

We 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 s…

> In the cloud, we have things like AWS S3, which is a very reliable distributed storage

Yes, but S3 is basically a standardized protocol at this point. There are many both open and commercial alternatives, like Cloudflare R2 (no egress). So depending on the reason for self-hosting (such as preventing lock-in), S3 might be the least important thing to actually move away from. It’s way more difficult to migrate away from eg a proprietary db, sometimes by design.

Re: Distributed SQLite: Paradigm shift or hype?

#90

Post by dns_snek »

cnity wrote: post #77
victorbjorklund wrote: post #62

Earlier quoted context omitted.

Not really a problem if you go from sqlite to postgres. Which sqlite feature is missing from Postgres?

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.
Post reply on HN