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

141–150 of 165 posts

Re: Distributed SQLite: Paradigm shift or hype?

#141

Post by cztomsik »

masklinn wrote: post #126
samatman wrote: post #123

Earlier quoted context omitted.

SQLite has what they "call a covering index", see point 9 here: https://www.sqlite.org/optoverview.html My impression is that this mechanism is less general than what one finds in full-fat client-server SQLite databases.

Ya if my reading is correct this is the poor man's covering index: if all the requested data is in the index key the query will not hit the table, so you can add additional fields at the end of the key to get index-only scans (at a cost, also some flexibility cost e.g. doesn't work with unique indexes). I guess it's less of an issue in sqlite than in databases with richer datatypes in the sense that all datatypes are…

can you elaborate? I was living under impression that what sqlite has, is exactly what covering index is...

Re: Distributed SQLite: Paradigm shift or hype?

#142

Post by cztomsik »

boxed wrote: post #70
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…

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?

the cool thing with sqlite is that you can compile your whole app into a single binary, so you don't need docker for example (or it's trivial to dockerize it afterwards if you really insist)

Re: Distributed SQLite: Paradigm shift or hype?

#143

Post by simonw »

cztomsik wrote: post #141
masklinn wrote: post #126

Earlier quoted context omitted.

Ya if my reading is correct this is the poor man's covering index: if all the requested data is in the index key the query will not hit the table, so you can add additional fields at the end of the key to get index-only scans (at a cost, also some flexibility cost e.g. doesn't work with unique indexes). I guess it's less of an issue in sqlite than in databases with richer datatypes in the sense that all datatypes are…

can you elaborate? I was living under impression that what sqlite has, is exactly what covering index is...

In PostgreSQL a covering index can be configured which includes extra information from columns that aren't part of the searchable index itself. It's documented quite well here: https://www.postgresql.org/docs/current/indexes-index-only-s...

    CREATE INDEX tab_x_y
    ON tab(x) INCLUDE (y);

Re: Distributed SQLite: Paradigm shift or hype?

#144

Post by SJC_Hacker »

MobiusHorizons wrote: post #131
SJC_Hacker wrote: post #125

Earlier quoted context omitted.

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…

That's much better, thanks. In case I ever need to do years 10000 :-)

Still, having that all over a query looks ugly. SQL is can be unreadable enough as it is without all the joins/table renaming.

I just want something more readable like EXTRACT(year from date), like you can in Postgres et al.

Would also be nice if there was a native timestamp like there is in, pretty much every other database.

I'm sensitive to "feature creep" but this doesn't seem like too big of an ask.

Re: Distributed SQLite: Paradigm shift or hype?

#145

Post by masklinn »

cztomsik wrote: post #141
masklinn wrote: post #126

Earlier quoted context omitted.

Ya if my reading is correct this is the poor man's covering index: if all the requested data is in the index key the query will not hit the table, so you can add additional fields at the end of the key to get index-only scans (at a cost, also some flexibility cost e.g. doesn't work with unique indexes). I guess it's less of an issue in sqlite than in databases with richer datatypes in the sense that all datatypes are…

can you elaborate? I was living under impression that what sqlite has, is exactly what covering index is...

With a "proper" covering index (an INCLUDE clause in SQL Server or Postgres for example) you add data to the index value. This means it can be retrieved just by looking into the index but

- it's not constrained (e.g. to be orderable)

- it does not affect the behaviour of the index, so you can have covering data in a UNIQUE index, or in a PK constraint (although for the latter one might argue a clustered index is superior)

- it only takes space in leaf nodes, not interior nodes, so you can have better occupancy of interior node pages, less pages to traverse during lookup, and they have better cache residency

- and finally the intent is clearer, when you put everything in the key it does not tell the reader what's what and why it there, and thus makes it harder to evaluate changes

Re: Distributed SQLite: Paradigm shift or hype?

#146

Post by giovannibonetti »

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

You should probably use a specialized DB for analytics (a.k.a. OLAP DB) anyway. As long as you have an automated way of replicating data from SQLite to your OLAP DB, everything should be fine.

Re: Distributed SQLite: Paradigm shift or hype?

#147

Post by giovannibonetti »

boxed wrote: post #70
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…

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?

Postgres is great, but managing a fleet of them (one in each web server) and ensuring they are all working fine would bring a lot of operational complexity.

SQLite, on the other hand, skips all of that with its simple in-process model.

Re: Distributed SQLite: Paradigm shift or hype?

#148

Post by [deleted] »

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.

[deleted]

Re: Distributed SQLite: Paradigm shift or hype?

#149

Post by giovannibonetti »

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…

Tigris Data [1] seems like a promising open-source solution [2] in this space.

[1] https://www.tigrisdata.com/ [2] https://github.com/tigrisdata-archive/tigris

Re: Distributed SQLite: Paradigm shift or hype?

#150

Post by pfraze »

cp9 wrote: post #112
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

that's still how it works, we just shard our users across multiple hosts
Post reply on HN