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

101–110 of 165 posts

Re: Distributed SQLite: Paradigm shift or hype?

#101

Post by simonw »

masklinn wrote: post #85
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…

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

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

Re: Distributed SQLite: Paradigm shift or hype?

#102

Post by simonw »

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.

I reorder columns all the time for neater readability of "select *" queries.

Re: Distributed SQLite: Paradigm shift or hype?

#103

Post by simonw »

rapnie wrote: post #94
rapnie wrote: post #57

Earlier quoted context omitted.

> I've implemented GraphQL on top of SQLite and found it to be an amazingly good match Could you give a pointer to the repository, or is this part of Datasette?

Nevermind, found it. Just don't know the license. https://github.com/simonw/datasette-graphql

It's Apache 2 (there is a badge in the README) but I realize now I forgot to drop a copy of the license into the repo - fixing that now.

Re: Distributed SQLite: Paradigm shift or hype?

#104

Post by simonw »

tptacek wrote: post #54
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?

You can attach to databases dynamically in queries and join across them. I probably wouldn't (in an ordinary data model) do per-user, but I would consider it for different functional areas.

There's a limit on how many databases you can attach to the same connection (SQLITE_LIMIT_ATTACHED), it defaults to 10.

Re: Distributed SQLite: Paradigm shift or hype?

#106

Post by simonw »

russellbeattie wrote: post #31

I 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

There's an official branch that implements BEGIN CONCURRENT: https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

Re: Distributed SQLite: Paradigm shift or hype?

#107

Post by simonw »

Animats wrote: post #48

SQLite is getting a lot heavier. This is becoming more like MySQL or Postgres.

The most recent SQLite amalgamation zip file is 2732008 bytes.

Version 3.26.0 from 2018 is 2286469 bytes.

That's a 20% increase in 6 years, but it's still just 2.6MB of compressed C.

Re: Distributed SQLite: Paradigm shift or hype?

#108

Post by candiddevmike »

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.

Re: Distributed SQLite: Paradigm shift or hype?

#109

Post by masklinn »

simonw wrote: post #101
masklinn wrote: post #85

Earlier quoted context omitted.

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

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 ability to create more (especially domains).

Strict tables provides table stakes of actually enforcing the all-of-5-types sqlite has built-in. Afaik a strict mode is something that’s still being discussed if it ever becomes reality.

Re: Distributed SQLite: Paradigm shift or hype?

#110

Post by Sytten »

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?

Yes it has been years since the WAL mode fixed this. And WAL2 is even better now.
Post reply on HN