Birbla

Join the discussion

Write your take first — we'll ask for email only when you're ready to publish.

Sign in
  • Hacker News
  • Do these guys really not understand that WAL is still single writer multi reader? You could do concurrent (but not parallel) write DML in both the normal and WAL journaling models. WAL alleviates read transactions being blocked by writers but you still have to lock it down to a single writer. It would be nice if SQLite3 had full blown MVCC, but it still works if you understand it.
    by rpcope1
  • A bit off topic, but there seems to be quite a few SQLite experts here.

    We're having troubles with memory usage when using SQLite in-memory DBs with "a lot" of inserts and deletes. Like maybe inserting up to a 100k rows in 5 minutes, deleting them all after 5 minutes, and doing this for days on end. We see memory usage slowly creeping up over hours/days when doing that.

    Any settings that would help with that? It's particularly bad on macOS, we've had instances where we reached 1GB of memory usage according to Activity Monitor after a week or so.

    by Leherenn
  • sounds like normal behavior of adjusting buffers to better fit the usecase, not sure if it applies to sqlite or if sqlite even implements dynamic buffers.
    by kachapopopow
  • If you're deleting all rows you can also just drop the table and recreate it.
    by pstuart
  • Are you running vacuums at all? auto_vacuum enabled at all?

    https://sqlite.org/lang_vacuum.html

    by asa400
  • > So an application that wants to use SQLite as its database needs to be the only one accessing it.

    No. It uses OS level locks. fcntl(). You can access it from how many ever processes. The only rule is, single writer (at a time).

    > When another part of the application wants to read data, it reads from the actual database, then scans the WAL for modifications and applies them on the fly.

    Also wrong. WAL does not contain modifications, it contains the full pages. A reader checks the WAL, and if it finds the page it won't even read the DB. It's a bit like a cache in this sense, that's why shared cache mode was discouraged in favour of WAL (in addition to its other benefits). Multiple versions of a page can exist in the WAL (from different transactions), but each reader sees a consistent snapshot which is the newest version of each page up to its snapshot point.

    > For some reason on some systems that run Jellyfin when a transaction takes place the SQLite engine reports the database is locked and instead of waiting for the transaction to be resolved the engine refuses to wait and just crashes

    You can set a timeout for this - busy_timeout.

    > Reproducible

    There's nothing unreliable here. It will fail every single time. If it doesn't, then the write finished too fast for the read to notice and return SQLite busy. Not sure what they are seeing.

    > The solution

    So they've reimplemented SQLites serialisation, as well as SQLites busy_timeout in C#?

    > "engine", "crash"

    Sqlite is not an engine. It's literally functions you link into your app. It also doesn't crash, it returns sqlite_busy. Maybe EF throws an exception on top of that.

    I have to say, this article betrays a lack of fundamental DB knowledge and only knowing ORMs. Understand the DB and then use the ORM on top of it. Or atleast, don't flame the DB (context: blame-y tone of article) if you haven't bothered to understand it. Speaking of ORMs ...

    > EF Core

    You're telling me that burj khalifa of abstractions doesn't have room to tune SQLite to what web devs expect?

    by porridgeraisin
  • C# devs*
    by yellow_lead
  • 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.
    by mangecoeur
  • Sqlite has so many small benefits for tiny projects it can't be easily replaced.

    It's like saying "oh, you want to visit Austrian country side next month and you're asking for advice for best tent? How about you build a cabin instead?".

    by zeroq
  • Sqlite is fine you need to read the extensive documentation though to get the most out of it. It also has terrible defaults.

    I think the author od this article missed sqlite_busy.

    Once you do have it set up correctly, are handling a single writer at the application level and have litestream set up your off to the races assuming your app can scale on a single box (it most likely can).

    by andersmurphy
  • I run Jellyfin in a multi-arch cluster because I hate myself, and this would force me to think about where Jellyfin/Postgres is deployed because Postgres databases aren't portable.

    I already had to do that for my authoritative PG deployment, and my media manager shouldn't require a full RDBMS.

    Using SQLite for Jellyfin has made running it wherever really, really easy, same thing with doing backups and lazy black box debugging.

    by heavyset_go
  • Even with postgres, you don't have to use the system instance; there's nothing stopping you from running the server as a child process.

    You probably need to support this for your testsuite anyway.

    by o11c
  • Their whole recent rewrite of the DB code (to Entity Framework) is to allow the user choice of DB in future.
    by amaccuish
  • 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.
    by thayne
  • Jellyfin is mostly for a single household, right? Sqlite should be much more than sufficient for Jellyfin (if used correctly). Unfortunately, reading this article you get the impression that they are not using it optimally
    by petters
  • 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
    by abound

  •   So, I decided on three locking strategies:

  No-Lock
  Optimistic locking
  Pessimistic locking

  As a default, the no-lock behavior does exactly what the name implies. Nothing. This is the default because my research shows that for 99% all of this is not an issue and every interaction at this level will slow down the whole application.
    Aren't the mutexes in the more modern implementations (like Cosmo [0]) & runtimes (like Go [1]) already optimized so applications can use mutexes fearlessly?

    [0] https://justine.lol/mutex/

    [1] https://victoriametrics.com/blog/go-sync-mutex/

    by ignoramous
  • I have encountered this problem on Jellyfin before. It works like a dream, but there are some very strange circumstances that can cause the database to become locked and then just not work until I restart the docker container. If I check the logs it just says stuff about the database being locked. It happens quite rarely and seems to be when we fidget in the menus on the smart TV like starting to watch a show to realize it's the wrong episode as you click the button, then spam the back button, etc.
    by ddtaylor
  • Articles like this leave me with an uneasy feeling that the “solutions” are just blind workarounds - more debugging/research should be able to expose exactly what the problem is, now that would be something worth sharing.
    by ricardobeat
  • I am pretty sure in this case even Claude or ChatGPT would give them the correct answer quickly or at least it would point them to the right direction (the busy-timeout pragma) with 5 minutes of work.
    by Daniel_sk
  • Articles like this give me the feeling that the author did a little bit of research and shared a suboptimal solution, and was hoping that experts on HN would present better solutions. Wasn't there a saying about how the best way to get correct answers is to post not just the question but the wrong answers to it?
    by kccqzy
  • When hctree [1] becomes stable in SQLite, it will be the only database I will be using lol!

    I presume the `hc` part in project's code name should be High Concurrency.

    [1] https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html

    by stefanos82
  • Thing is if you design your app to have a single writer you can probably get higher throughput than multiple writers in concurrency mode.
    by andersmurphy
  • SQLite is a cracking database -- I love it -- that is let down by its awful defaults in service of 'backwards compatibility.'

    You need a brace of PRAGMAs to get it to behave reasonably sanely if you do anything serious with it.

    by mickeyp
  • Seems like it's asking to be forked
    by mkoubaa
  • Do you know any good default PRAGMAs that one should enable?
    by tejinderss
  • 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 parallel writes to take place and get enqueued into the WAL.

    The WAL doesn't allow multiple parallel writes. It just allows reads to be concurrent with a single write transaction.

    by thayne
  • 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.
    by Sammi
  • One of the biggest contributors I've had in the past for SQLite blocking was disk fragmentation.

    We had some old Android tablets using our app 8 hours a day for 3-4 years. They'd complain if locking errors and slowness but every time they'd copy their data to send to us, we couldn't replicate, even on the same hardware. It wasn't until we bought one user a new device and got them to send us the old one that we could check it out. We thought maybe the ssd had worn out over the few years of continual use but installing a dev copy of our app was super fast. In the end what did work was to "defrag" the db file by copying it to a new location, deleting the original, then moving it back to the same name. Boom, no more "unable to open database" errors, no more slow downs.

    I tried this on Jellyfin dbs a few months ago after running it for years and then suddenly running into performance issues, it made a big difference there too.

    by EionRobb
  • This is fascinating. What would be the solution for this? You can’t ask users to defrag.
    by didip