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

131–140 of 165 posts

Re: Distributed SQLite: Paradigm shift or hype?

#131

Post by MobiusHorizons »

SJC_Hacker wrote: post #125
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.

It is a fairly low level abstraction, but one that does not require a verbose api. There is nothing error prone or hackish about what you have written, it will work for all inputs, it is just low level. You are just used to having other people write this code for you and give you a library. With newer versions of SQLite you could also write

CAST(strftime(“%Y”, game_date)) as INTEGER

Which is somewhat higher level and less easily mistyped

Re: Distributed SQLite: Paradigm shift or hype?

#132

Post by simonw »

emmanueloga_ wrote: post #124
simonw wrote: post #105

Earlier quoted context omitted.

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

Thanks, that's useful.

My design should be OK - I'm planning on having the workers retrieve jobs and send back their results via an HTTP API to a single process that wraps the SQLite database (Datasette with a custom plugin).

Re: Distributed SQLite: Paradigm shift or hype?

#133

Post by mixmastamyk »

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?

Sqlite handles many tables, per tenant is more reasonable.

Re: Distributed SQLite: Paradigm shift or hype?

#134

Post by prirun »

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.

One reason to reorder columns with SQLite is that if a column is usually null or has the default value, SQLite will not store the column at all if it is at the end of the row. It only saves a couple of bytes per column, but it is a reason to get these columns at the end.

Re: Distributed SQLite: Paradigm shift or hype?

#135

Post by SJC_Hacker »

throwaway2037 wrote: post #116
znpy wrote: post #81

Earlier quoted context omitted.

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

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

Agree. The confusion probably comes from the fact, the SQLite programming language interfaces still have the "connection" abstraction.

Also, it would be great if there was a simple way to simply "load this entire database into memory". Its not too difficult to manually copy tables, but its much slower than it could be. Even a smallish ~250 MB database was taking like 30 seconds to copy row-by-row.

Re: Distributed SQLite: Paradigm shift or hype?

#136

Post by SJC_Hacker »

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?

If you want to query across users, which you probably want for analytics, that is going to be a massive PITA.

Re: Distributed SQLite: Paradigm shift or hype?

#137

Post by masklinn »

prirun wrote: post #134
dkjaudyeqooe wrote: post #72

Earlier quoted context omitted.

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

One reason to reorder columns with SQLite is that if a column is usually null or has the default value, SQLite will not store the column at all if it is at the end of the row. It only saves a couple of bytes per column, but it is a reason to get these columns at the end.

AFAIK position has nothing to do with nulls, a null is a 0 byte in the header and has no payload in the row: https://www.sqlite.org/fileformat.html#record_format

Re: Distributed SQLite: Paradigm shift or hype?

#138

Post by SJC_Hacker »

victorbjorklund wrote: post #62
mind-blight wrote: post #58

Earlier quoted context omitted.

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?

All SQLite queries are not just going to straight up work in Postgres.

e.g handling of dates is a big one. In SQLite they are just strings(kludgy IMO), where as Postgres has the timestamp data type.

Re: Distributed SQLite: Paradigm shift or hype?

#139

Post by dkjaudyeqooe »

masklinn wrote: post #83
dkjaudyeqooe wrote: post #72

Earlier quoted context omitted.

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.

That's not the case:

"SQLite does not pad or align columns within a row. Everything is tightly packed together using minimal space."

https://sqlite.org/forum/info/06ad7f81fea46401

Re: Distributed SQLite: Paradigm shift or hype?

#140

Post by cztomsik »

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…

column reordering is simple to fix with this migration script https://david.rothlis.net/declarative-schema-migration-for-s...

if you are using Zig (and like to live on the bleeding edge), you can also just use my library which includes similar script and also a simple query builder https://github.com/cztomsik/fridge?tab=readme-ov-file#migrat...

Post reply on HN