Most active commenters
  • apitman(3)

←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
353 points HunOL | 14 comments | 01 Nov 25 12:59 UTC | HN request time: 0.736s | source | bottom
Show context
mangecoeur ◴[01 Nov 25 15:14 UTC] No.45782293[source]▶
Sqlite is a great bit of technology but sometimes I read articles like this and think, maybe they should have used postgres. I you don’t specifically need the “one file portability” aspect of sqlite, or its not embedded (in which case you shouldn’t have concurrency issues), Postgres is easy to get running and solves these problems.
replies(11): >>45782439 #>>45782829 #>>45782906 #>>45782930 #>>45782932 #>>45783524 #>>45784757 #>>45784918 #>>45787275 #>>45788143 #>>45788886 #
1. abound ◴[01 Nov 25 16:12 UTC] No.45782829[source]▶
Jellyfin is a self-hostable media server. If they "used Postgres", that means anyone who runs it needs Postgres. I think SQLite is the better choice for this kind of application, if one is going to choose a single database instead of some pluggable layer
replies(4): >>45783314 #>>45785817 #>>45786226 #>>45788638 #
2. morshu9001 ◴[01 Nov 25 17:06 UTC] No.45783314[source]▶
Exactly, there are use cases where SQLite makes sense but you also want to make it faster. I really don't get why there isn't a more portable Postgres.
replies(1): >>45785432 #
3. zie ◴[01 Nov 25 21:13 UTC] No.45785432[source]▶
There is, you can even run PG under wasm if you are desperate. :)

SQLite is probably the better option here and in most places where you want portability though.

4. tombert ◴[01 Nov 25 22:01 UTC] No.45785817[source]▶
I share my Jellyfin with about a dozen people, and it's not weird to have several people streaming at the same time. I have a two gigabit connection so bandwidth isn't generally an issue, but I've had issues when three people all streaming a VC-1 encoded video to H264 in software.

This is something that I think I could fairly easily ameliorate if I could simply load-balance the application server by user, but historically (with Emby), I've not been able to do that due to SQLite locking not allowing me to run multiple instances pointing to the same config instance.

There's almost certainly ways to do this correctly with SQLite but if they allowed for using almost literally any other database this would be a total non-issue.

ETA:

For clarification if anyone is reading this, all this media LEGALLY OBTAINED with PERMISSION FROM THE COPYRIGHT HOLDER(S).

replies(3): >>45786203 #>>45789067 #>>45790482 #
5. reddalo ◴[01 Nov 25 22:54 UTC] No.45786203[source]▶
Yeah, I'm sure those twelve people love watching your vacation clips all the time ;)
6. reddalo ◴[01 Nov 25 22:57 UTC] No.45786226[source]▶
They're actually planning on migrating to Postgres in a future release:

>[...] it also opens up new possibilities - not officially yet, but soon - for running Jellyfin backed by "real" database systems like PostgreSQL, providing new options for redundancy, load-balancing, and easier maintenance and administration. The future looks very bright!

https://jellyfin.org/posts/jellyfin-release-10.11.0/

replies(1): >>45789094 #
7. npodbielski ◴[02 Nov 25 08:00 UTC] No.45788638[source]▶
What is the problem to bundle postgress db engine in the docker server? If you want to install it from package, they can have postgress dB as an option with the warning somewhere that it is 'recomended'. I am sure that if you are able to slefhost stuff you are able to install postgress too.
replies(2): >>45789054 #>>45789852 #
8. apitman ◴[02 Nov 25 09:40 UTC] No.45789054[source]▶
Jellyfin is one of the very few selfhosted apps that can be run as a simple GUI app on Windows. As an advocate for making selfhosting accessible to less technical people, I'm glad they're using sqlite and also that they don't require docker.
9. apitman ◴[02 Nov 25 09:43 UTC] No.45789067[source]▶
Why not encode to H264 or another codec more widely supported by clients? Storage is cheap.
10. apitman ◴[02 Nov 25 09:49 UTC] No.45789094[source]▶
I hope they keep sqlite as a first class citizen.
11. xorcist ◴[02 Nov 25 12:34 UTC] No.45789852[source]▶
A database is never hard to install, but it can be tricky to operate.

You have to at least have at least a slight idea about the specifics, from different types of vacuum to how it behaves in low memory conditions. The idea that docker has something to do this is a misdirection at best.

And if you think sqlite has many knobs and special modes, wait until you hear about Postgres.

replies(1): >>45791471 #
12. MayeulC ◴[02 Nov 25 14:11 UTC] No.45790482[source]▶
> I've had issues when three people all streaming a VC-1 encoded video to H264 in software.

I don't quite get the "in software" part. I assume you mean that the video needs to be transcoded to h.264 on your server for their client to play it.

The way I mostly solved this is to ask people to install and use the native app (jellyfin-media-player or Android app) whenever possible, as it is compatible with more codecs.

You can also configure HW acceleration for transcoding, a decent GPU should have no trouble encoding a few h.264 streams in real time.

And lastly, you can play with distributed versions of ffmpeg, since Jellyfin calls ffmpeg. There are multiple options, such as https://hub.docker.com/r/bitwrk/jellyfin-rffmpeg (I never used it myself, though).

replies(1): >>45793797 #
13. npodbielski ◴[02 Nov 25 16:31 UTC] No.45791471{3}[source]▶
> And if you think sqlite has many knobs and special modes, wait until you hear about Postgres.

And why do you think I think that?

14. tombert ◴[02 Nov 25 21:59 UTC] No.45793797{3}[source]▶
I mean "in software" in that it's not hardware assisted. I have gotten VAAPI working but it's a bit flaky with some videos for some reason, so I disabled it and just do vanilla ffmpeg.

I'll look into the distributed ffmpeg.