Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite concurrency and why you should care about it

jellyfin.org

Post reply on HN
Print view View original

71–80 of 189 posts

Re: SQLite concurrency and why you should care about it

#71

Post by catlifeonmars »

zimpenfish wrote: post #63
catlifeonmars wrote: post #58

Earlier quoted context omitted.

> When a commit completes for a transaction, that transaction has been durably written. No mystery. That's true whether you decide to restrict writes to a single thread in your application or not. Usually this is true but there are edge cases for certain journaled file systems. IIRC sqlite.org has a discussion on this.

> there are edge cases for certain journaled file systems. IIRC sqlite.org has a discussion on this. Can't currently find it but I guess it comes under the "if the OS or hardware lies to SQLite, what can it do?" banner?

That might have been it. Overall the whole “How to corrupt your database article” was quite a good read:

https://sqlite.org/howtocorrupt.html

Re: SQLite concurrency and why you should care about it

#72

Post by yread »

I'm a bit confused. The point of this article is that the author used .NET Interceptors and TagWith to somehow tag his EF Core operations so that they make their own busy_timeout (which EF Core devs think is not necessary https://github.com/dotnet/efcore/issues/28135 ) or do a horrible global lock? No data is presented on how it improved things if it did. Nor is it described which operations were tagged with what. The only interesting thing about it are the interceptors but that's somehow not discussed in HN's comments at all.

Re: SQLite concurrency and why you should care about it

#74

Post by jitl »

pstuart wrote: post #66
mkoubaa wrote: post #16

Earlier quoted context omitted.

Seems like it's asking to be forked

The real fork is DuckDB in a way, it has SQLite compatibility and so much more . The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster. https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html As to the…

DuckDB is similar as an in process SQL database, but lacking btree-style ordered indexes makes it a poor performer in key lookups and order-by / range scans if your table is any size larger than trivial.

It’s the classic OLAP (DuckDB) vs OLTP (SQLite) trade off between the two. DuckDB is very good at many things but most applications that need a traditional SQL DB will probably not perform well if you swap it over to DuckDB.

Re: SQLite concurrency and why you should care about it

#75

Post by Kinrany »

pstuart wrote: post #66
mkoubaa wrote: post #16

Earlier quoted context omitted.

Seems like it's asking to be forked

The real fork is DuckDB in a way, it has SQLite compatibility and so much more . The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster. https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html As to the…

> Read only databases are a lot easier to deal with regarding locking.

"A lot easier" sounds like an understatement. What's there to lock when the data is read only?

Re: SQLite concurrency and why you should care about it

#76

Post by Kinrany »

jitl wrote: post #74
pstuart wrote: post #66

Earlier quoted context omitted.

The real fork is DuckDB in a way, it has SQLite compatibility and so much more . The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster. https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html As to the…

DuckDB is similar as an in process SQL database, but lacking btree-style ordered indexes makes it a poor performer in key lookups and order-by / range scans if your table is any size larger than trivial. It’s the classic OLAP (DuckDB) vs OLTP (SQLite) trade off between the two. DuckDB is very good at many things but most applications that need a traditional SQL DB will probably not perform well if you swap it over to…

That's surprising, surely OLAP use cases also need key lookups?

Re: SQLite concurrency and why you should care about it

#78

Post by thayne »

throwaway894345 wrote: post #59
thayne wrote: post #53

Earlier quoted context omitted.

Using postgres would make it significantly more complicated for Jellyfin users to install and set up Jellyfin. And then users would need to worry about migrating the databases when PostgreSQL has a major version upgrade. An embedded database like sqlite is a much better fit for something like Jellyfin.

As a Jellyfin user, this hasn’t been my experience. I needed to do a fair bit of work to make sure Jellyfin could access its database no matter which node it was scheduled onto and that no more than one instance ever accessed the database at the same time. Jellyfin by far required more work to setup maintainably than any of the other applications I run, and it is also easily the least reliable application. This isn’t…

Is running multiple nodes a typical way to run Jellyfin through? I would expect that most Jellyfin users only run a single instance at a time.

Re: SQLite concurrency and why you should care about it

#79

Post by geysersam »

jitl wrote: post #74
pstuart wrote: post #66

Earlier quoted context omitted.

The real fork is DuckDB in a way, it has SQLite compatibility and so much more . The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster. https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html As to the…

DuckDB is similar as an in process SQL database, but lacking btree-style ordered indexes makes it a poor performer in key lookups and order-by / range scans if your table is any size larger than trivial. It’s the classic OLAP (DuckDB) vs OLTP (SQLite) trade off between the two. DuckDB is very good at many things but most applications that need a traditional SQL DB will probably not perform well if you swap it over to…

Duckdb has optional adaptive radix tree indexing (https://duckdb.org/docs/stable/sql/indexes.html)

Re: SQLite concurrency and why you should care about it

#80

Post by Sammi »

thayne wrote: post #60

There seem to be some misunderstandings in this: > If your application fully manages this file, the assumption must be made that your application is the sole owner of this file, and nobody else will tinker with it while you are writing data to it. Kind of, but sqlite does locking for you, so you don't have to do anything to ensure your process is the only one writing to the db file. > [The WAL] allows multiple parall…

Yeah... I adore Sqlite and upvote anything about it, but I couldn't upvote this article because it was just so poorly informed. It gets the very basics on sqlite concurrency wrong.
Post reply on HN