[HN Gopher] SQLite concurrency and why you should care about it
___________________________________________________________________
SQLite concurrency and why you should care about it
Author : HunOL
Score : 353 points
Date : 2025-11-01 12:59 UTC (1 days ago)
(HTM) web link (jellyfin.org)
(TXT) w3m dump (jellyfin.org)
| asa400 wrote:
| In SQLite, transactions by default start in "deferred" mode. This
| means they do not take a write lock until they attempt to perform
| a write.
|
| You get SQLITE_BUSY when transaction #1 starts in read mode,
| transaction #2 starts in write mode, and then transaction #1
| attempts to upgrade from read to write mode while transaction #2
| still holds the write lock.
|
| The fix is to set a busy_timeout and to begin any transaction
| that does a write (any write, even if it is not the first
| operation in the transaction) in "immediate" mode rather than
| "deferred" mode.
|
| https://zeroclarkthirty.com/2024-10-19-sqlite-database-is-lo...
| tlaverdure wrote:
| Yes, these are both important points. I didn't see any mention
| of SQLITE_BUSY in the blog post and wonder if that was never
| configured. Something that people miss quite often.
| BobbyTables2 wrote:
| Thats the best explanation I've seen of this issue.
|
| However, it screams of a broken implementation.
|
| Imagine if Linux PAM logins randomly failed if someone else was
| concurrently changing their password or vice versa.
|
| In no other application would random failures due to
| concurrency be tolerated.
|
| SQLite is broken by design; the world shouldn't give them a
| free pass.
| asa400 wrote:
| SQLite is a truly remarkable piece of software that is a
| victim both of its own success and its unwavering commitment
| to backward compatibility. It has its quirks. There are
| definitely things we can learn from it.
| mickeyp wrote:
| Indeed. Everyone who uses sqlite _will_ get burnt by this one
| day and spend a lot of time chasing down errant write-upgraded
| transactions that cling on for a little bit longer than
| intended.
| BinaryIgor wrote:
| SQLite has its quirks, but in this particular case all you
| need is set PRAGMA busy_timeout=<a few seconds> and the
| problem is solved; and if you google it, it's widely known
| issue with described (this) solution.
|
| It's just weird that it's set to 0 by default rather than
| something resonable like 3000 or 5000 ms.
| summarity wrote:
| I've always tried to avoid situations that could lead to
| SQLITE_BUSY. SQLITE_BUSY is an architecture smell. For standard
| SQLite in WAL, I usually structure an app with a read
| "connection" pool, and a single-entry write connection pool.
| Making the application aware of who _actually_ holds the write
| lock gives you the ability to proactively design access
| patterns, not try to react in the moment, and to get
| observability into lock contention, etc.
| mickeyp wrote:
| I mean, you're not wrong, and that is one way to solve it,
| but the whole point of a sensibly-designed WAL -- never mind
| database engine -- is that you do not need to commit to some
| sort of actor model to get your db to serialise writes.
| sethev wrote:
| These are performance optimizations. SQLite does serialize
| writes. Avoiding concurrent writes to begin with just
| avoids some overhead on locking.
| mickeyp wrote:
| "performance optimisation" --- yeees, well, if you don't
| care about data integrity between your reads and writes.
| Who knows when those writes you scheduled really get
| written. And what of rollbacks due to constraint
| violations? There's we co-locate transactions with code:
| they are intertwined. But yes, a queue-writer is fine for
| a wide range of tasks, but _not_ everything.
|
| It's that we need to contort our software to make sqlite
| not suck at writes that is the problem.
| sethev wrote:
| This is just FUD. The reason SQLite does locking to begin
| with is to avoid data corruption. Almost every statement
| this blog post makes about concurrency in SQLite is
| wrong, so it's little surprise that their application
| doesn't do what they expect.
|
| >Who knows when those writes you scheduled really get
| written
|
| 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.
| mickeyp wrote:
| You are talking about low level stuff like syncing to the
| filesystem; that data is journalled and ensuring
| atomicity is maintained and I am in actual fact not.
|
| Dislocating DML from the code that triggers it creates
| many problems around ensuring proper data integrity and
| it divorces consistent reads of uncommitted data that you
| may want to tightly control before committing. By punting
| it to a dedicated writer you're removing the ability to
| ensure serialised modification of your data and the
| ability to cleanly react to integrity errors that may
| arise. If you don't need that? Go ahead. But it's not
| fud. We build relational acid compliant databases this
| way for a reason
| sethev wrote:
| Oh, I think you're picturing executing your transaction
| logic and then sending writes off to a background queue.
| I agree, that's not a general strategy - it only works
| for certain cases.
|
| I just meant that if you can structure your application
| to run write transactions in a single thread (the whole
| transaction and it's associated logic, not just deferring
| writing the end result to a separate thread) then you
| minimize contention at the SQLite level.
| catlifeonmars wrote:
| > 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.
| zimpenfish wrote:
| > 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?
| catlifeonmars wrote:
| That might have been it. Overall the whole "How to
| corrupt your database article" was quite a good read:
|
| https://sqlite.org/howtocorrupt.html
| jitl wrote:
| > Who knows when those writes you scheduled really get
| written
|
| I await the write to complete before my next read in my
| application logic, same as any other bit of code that
| interacts with a database or does other IO. Just because
| another thread handles interacting with the writer
| connection, doesn't mean my logic thread just walks away
| pretending the write finished successfully in 0ms.
| ncruces wrote:
| SQLite, for the most part, uses polling locks. That means
| it checks if a lock is available to be taken, and if it's
| not, it sleeps for a bit, then checks again, until this
| times out.
|
| This becomes increasingly inefficient as contention
| increases, as you can easily get into a situation where
| _everyone_ is sleeping, waiting for others, for a few
| milliseconds.
|
| Ensuring all, or most, writes are serialized, improves
| this.
| simonw wrote:
| Even with that pattern (which I use too) you still need to
| ensure those write operations always start a transaction at
| the beginning in order to avoid SQLITE_BUSY.
| summarity wrote:
| Yes, indeed. In my apps, which are mostly Nim, my pool
| manager ensures this always happens - along with a host of
| other optimizations. I often start with barebones SQLite
| and then later switch to LiteSync (distributed SQLite with
| multi-master replication), so I keep the lock management at
| the app level to adapt to whatever backend I'm using.
| probst wrote:
| I am really curious about LiteSync. Any chance you could
| share a bit on your experiences with it (recognising it's
| somewhat off-topic...). Do you run with multiple
| primaries? What sort of use cases do you reach to it for?
| Conflict resolution seems a bit simplistic at first
| glance (from the perspective of someone very into CRDTs),
| have you experienced any issues as a result of that?
| andersmurphy wrote:
| Yes! This is the way.
|
| Honestly, its the key to getting the most out of sqlite. It
| also allows for transaction batching and various other forms
| if batching that can massively improve write throughput.
| simonw wrote:
| Yeah I read the OP and my first instinct was that this is
| SQLITE_BUSY. I've been collecting posts about that here:
| https://simonwillison.net/tags/sqlite-busy/
| gwking wrote:
| One tidbit that I don't see mentioned here yet is that ATTACH
| requires a lock. I just went looking for the documentation
| about this and couldn't find it, especially for WAL mode
| (https://www.sqlite.org/lockingv3.html mentions the super-
| journal, but the WAL docs do not mention ATTACH at all).
|
| I have a python web app that creates a DB connection per
| request (not ideal I know) and immediately attaches 3
| auxiliary DBs. This is a low traffic site but we have a
| serious reliability problem when load increases: the ATTACH
| calls occasionally fail with "database is locked". I don't
| know if this is because the ATTACH fails immediately without
| respecting the normal 5 second database timeout or what. To
| be honest I haven't implemented connection pooling yet
| because I want to understand what exactly causes this
| problem.
| sgbeal wrote:
| > I have a python web app that creates a DB connection per
| request (not ideal I know)
|
| FWIW, "one per request per connection is bad" (for SQLite)
| is FUD, plain and simple. SQLite's own forum software
| creates one connection per request (it creates a whole
| forked process per request, for that matter) and we do not
| have any problems whatsoever with that approach.
|
| Connection pools (with SQLite) are a solution looking for a
| problem, not a solution to a real problem.
| apitman wrote:
| The necessity of this sort of tribal knowledge kills a lot of
| the simplicity of sqlite for me. Honestly it seems to have a
| lot of footguns. I've tried to understand proper concurrent
| use of sqlite with Golang about 5 times and never come away
| feeling like I actually get it.
| nasretdinov wrote:
| With Go it's quite straightforward actually: use WAL mode +
| two connection pools, one for reads and the other, with
| MaxConnections set to 1, for writes. This way you should
| never encounter any concurrency issues, and Go will
| serialise writes for you too
| johannes1234321 wrote:
| I have no experience in Go, but won't that be a full
| database lock and prevent transactions with more than one
| operation?
| BinaryIgor wrote:
| Exactly - it's also strange that they didn't find a simple
| solution of setting PRAGMA busy_timeout=N; if you
| google/search _SQLITE_BUSY: database is locked_ there are
| plenty of solid results describing the problem and solution
| kijin wrote:
| Wouldn't that "fix" make the problem worse on the whole, by
| making transactions hold onto write locks longer than
| necessary? (Not trying to disagree, just curious about
| potential downsides.)
| asa400 wrote:
| It's a reasonable question!
|
| In WAL mode, writers and readers don't interfere with each
| other, so you can still do pure read queries in parallel.
|
| Only one writer is allowed at a time no matter what, so
| writers queue up and you have to take the write lock at some
| point anyway.
|
| In general, it's hard to say without benchmarking your own
| application. This will get rid of SQLITE_BUSY errors firing
| immediately in the situation of read/write/upgrade-read-to-
| write scenario I described, however. You'd be retrying the
| transactions that fail from SQLITE_BUSY anyway, so that
| retrying is what you'd need to benchmark against.
|
| It's a subtle problem, but I'd rather queue up writes than
| have to write the code that retries failed transactions that
| shouldn't really be failing.
| chasil wrote:
| In an Oracle database, there is only one process that is
| allowed to write to tablespace datafiles, the DBWR (or its
| slaves). Running transactions can write to ram buffers and the
| redo logs only.
|
| A similar design for SQLite would design for only one writer,
| with all other processes passing their SQL to it.
| liuliu wrote:
| Note that busy_timeout is not applicable to SQLite in this case
| (the SQLITE_BUSY issued immediately, no wait in this case).
|
| Also this is because WAL mode (and I believe only for WAL mode,
| since there is really no concurrent reads in the other mode).
|
| The reason is because pages in WAL mode appended to a single
| log file. Hence, if you read something inside a BEGIN
| transaction, later wants to mutate something else, there could
| be another page already appended and potentially interfere with
| the strict serializable guarantee for WAL mode. Hence, SQLite
| has to fail at the point of lock upgrade.
|
| Immediate mode solves this problem because at BEGIN time (or
| more correctly, at the time of first read in that transaction),
| a write lock is acquired hence no page can be appended between
| read -> write, unlike in the deferred mode.
| BinaryIgor wrote:
| Also worth mentioning - it happens more often when you set
| journal_mode=WAL, which is not a default.
|
| The default is DELETE mode, where the rollback journal is
| deleted at the conclusion of each transaction. What's more - in
| this mode (not-WAL), readers can coexist, but they do block the
| writer (which is always one) and the writer block readers -
| concurrency is highly limited.
|
| In WAL mode - which pretty much always you should set - there's
| also at most one writer, but writer can coexist with readers.
| mickeyp wrote:
| 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.
| tejinderss wrote:
| Do you know any good default PRAGMAs that one should enable?
| mickeyp wrote:
| These are my PRAGMAs and not _your_ PRAGMAs. Be very careful
| about blindly copying something that may or may not match
| your needs. PRAGMA foreign_keys=ON
| PRAGMA recursive_triggers=ON PRAGMA journal_mode=WAL
| PRAGMA busy_timeout=30000 PRAGMA synchronous=NORMAL
| PRAGMA cache_size=10000 PRAGMA temp_store=MEMORY
| PRAGMA wal_autocheckpoint=1000 PRAGMA optimize <- run
| on tx start
|
| Note that I do not use auto_vacuum for DELETEs are uncommon
| in my workflows and I am fine with the trade-off and if I do
| need it I can always PRAGMA it.
|
| defer_foreign_keys is useful if you understand the pros and
| cons of enabling it.
| adzm wrote:
| Really, no mmap?
| metrix wrote:
| I'm curious what your suggest mmap pragma would be.
| adzm wrote:
| PRAGMA mmap_size=268435456;
|
| for example? I'm surprised by the downvotes. Using mmap
| significantly reduced my average read query time;
| durations about 70% the length!
| mikeocool wrote:
| Using strict tables is also a good thing to do, if you
| value your sanity.
| porridgeraisin wrote:
| You should pragna optimize before TX end, not at tx start.
|
| Except for long lived connections where you do it
| periodically.
|
| https://www.sqlite.org/lang_analyze.html#periodically_run_p
| r...
| masklinn wrote:
| Also foreign_keys has to be set per connection but
| journal_mode is sticky (it changes the database itself).
| porridgeraisin wrote:
| Yes, if journal_mode was not sticky, a new process
| opening the db would not know to look for the wal and shm
| files and read the unflushed latest data from there. On
| the other hand, foreign key enforcement has nothing to do
| with the file itself, it's a transaction level thing.
|
| In any case, there is no harm in setting sticky pragmas
| every connection.
| leetrout wrote:
| Explanation of sqlite performance PRAGMAs
|
| https://kerkour.com/sqlite-for-servers
| e2le wrote:
| Although not what you asked for, the SQLite authors maintain
| a list of recommended compilation options that should be used
| where applicable.
|
| https://sqlite.org/compile.html#recommended_compile_time_opt.
| ..
| mkoubaa wrote:
| Seems like it's asking to be forked
| justin66 wrote:
| It has been forked at least once:
|
| https://docs.turso.tech/libsql
| fulafel wrote:
| How are the defaults there?
| justin66 wrote:
| The default is, don't use it.
| kbolino wrote:
| SQLite is fairly fork-resistant due to much of its test suite
| being proprietary: https://www.sqlite.org/testing.html
| pstuart wrote:
| 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 problem that prompted the article, there's another
| way of addressing the problem that is kind of a kludge but is
| guaranteed to work in scenarios like theirs: Have each thread
| in the parallel scan write to it's own temporary database and
| then bulk import them once the scan is done.
|
| It's easy to get hung up on having "a database" but sharding
| to different files by use is trivial to do.
|
| Another thing to bear in mind with a _lot_ of SQLite use
| cases is that the data is effectively read only save for
| occasional updates. Read only databases are a lot easier to
| deal with regarding locking.
| jitl wrote:
| 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.
| Kinrany wrote:
| That's surprising, surely OLAP use cases also need key
| lookups?
| geysersam wrote:
| Duckdb has optional adaptive radix tree indexing
| (https://duckdb.org/docs/stable/sql/indexes.html)
| jitl wrote:
| Oops, I stand corrected!
|
| What I remember about our evaluation of DuckDB in 2024
| concluded that (1) the major limitations were lack of
| range-scan and index-lookup performance (maybe w/ joins?
| or update where?), and (2) the DuckDB Node.js module
| segfaulted too much. Perhaps the engineers somehow missed
| the ART index it could also be the restriction that data
| fit in memory to create an index on it (our test dataset
| was about 50gb)
| Kinrany wrote:
| > 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?
| stefanos82 wrote:
| 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
| andersmurphy wrote:
| Thing is if you design your app to have a single writer you can
| probably get higher throughput than multiple writers in
| concurrency mode.
| dv35z wrote:
| Curious if anyone has strategies on how to perform parallel
| writes to an SQLite database using Python's `multiprocessing`
| Pool.
|
| I am using it to loop through a database of 11,000 words, hit an
| HTTP API for each (ChatGPT) and generate example sentences for
| the word. I would love to be able to asynchronously launch these
| API calls and have them come back and update the database row
| when ready, but not sure how to handle the database getting hit
| by all these writes from (as I understand it) multiple instances
| of the same Python program/function.
| mickeyp wrote:
| Edit: disregard. I read it as he'd done it and had contention
| problems.
|
| You can't. You have a single writer - it's one of the many
| reasons sqlite is terrible for serious work.
|
| You'll need a multiprocessing Queue and a writer that picks off
| sentences one by one and commits it.
| hruk wrote:
| This is just untrue - the naive implementation (make the API
| call, write a single row to the db) will work fine, as
| transactions are quite fast on modern hardware.
|
| What do you consider "serious" work? We've served a SaaS
| product from SQLite (roughly 300-500 queries per second at
| peak) for several years without much pain. Plus, it's not
| like PG and MySQL are pain-free, either - they all have their
| quirks.
| mickeyp wrote:
| Edit: disregard. I read it as he'd done it and had
| contention problems.
|
| I mean it's not if he's got lock contention from BUSY
| signals, now is it, as he implies. Much of his issues will
| stem from transactions blocking each other; maybe they are
| long-lived, maybe they are not. And those 3-500 queries ---
| are they writes or reads? Because reads is not a problem.
| hruk wrote:
| Roughly 80/20 read to write. On the instance's gp3 EBS
| volume (which is pretty slow), we've pushed ~700 write
| transactions per second without much problem.
| mickeyp wrote:
| For small oltp workloads the locking is not going to be a
| problem. But stuff that holds the write lock for some
| measurable fraction of a second even will gum things up
| real fast. Transactions that need it for many seconds?
| You'll quickly be dead in the water.
| zie wrote:
| Technically SQLite can only have 1 writer at any given moment,
| but it can appear like it works across multiple writers and let
| it serialize the calls for you.
|
| By default SQLite will not do what you want out of the box. You
| have to turn on some feature flags(PRAGMA) to get it to behave
| for you. You need WAL mode, etc read:
|
| * https://kerkour.com/sqlite-for-servers *
| https://zeroclarkthirty.com/2024-10-19-sqlite-database-is-lo...
|
| My larger question is why multiprocessing? this looks like an
| IO heavy workload, not CPU bound, so python asyncio or python
| threads would probably do you better.
|
| multiprocessing is when your resource hog is CPU(probably 1
| python process per CPU), not IO bound.
| dv35z wrote:
| I will check into `asyncio` and Python threads. I used
| multiprocessing as my first project into asynchronous
| programming. The previous use-case was using Python +
| multiprocessing to run MacOS `say` (using Python subprocess)
| - so I could invoke it 10-20 times simultaneously on my
| computer, rather than waiting for each to complete. I
| experimented a bit with how many concurrent processes to run
| (using `time` to clock how long the runs were).
| sethev wrote:
| Have you tried it?
|
| What you're describing sounds like it would work fine to me.
| The blog post is misleading imho - it implies that SQLite
| doesn't handle concurrency at all. In reality, you can perform
| a bunch of writes in parallel and SQLite will handle running
| them one after the other internally. This works across
| applications and processes, you just need to use SQLite to
| interact with the database. The blog post is also misleading
| when it implies that the application has to manage access to
| the database file in some way.
|
| Yes, it's correct that only one of those writes will execute at
| a time but it's not like you have to account for that in your
| code, especially in a batch-style process like you're
| describing. In your Python code, you'll just update a row and
| it will look like that happens concurrently with other updates.
|
| I'll bet that your call to ChatGPT will take far longer than
| updating the row, even accounting for time when the write is
| waiting for its turn in SQLite.
|
| Use WAL-mode for the best performance (and to reduce
| SQLITE_BUSY errors).
| dv35z wrote:
| I haven't tried it yet - async processing (and even using
| SQLite) is new to me, so I'm trying to figure out solution
| patterns which work for the now, and also I can continue to
| invest my knowledge in to solve future problems.
|
| I will look into WAL mode. I am enjoying using SQLite (and
| aware that its not the solution for everything), and have
| several upcoming tasks which I'm planning to use async stuff
| - and yes, trying to find the balance between how to handle
| those async tasks (Networky HTTP calls being different than
| running `ffmpeg` locally).
| crazygringo wrote:
| It should just work.
|
| If one thread is writing another thread tries to write, the
| first thread will have the file write lock, and the second
| thread will wait to write until that lock is released.
|
| I've written code using the pattern you describe and it's
| totally fine.
| porridgeraisin wrote:
| > 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?
| yellow_lead wrote:
| C# devs*
| porridgeraisin wrote:
| Didn't mean to belittle any 'X' developer. By "what web devs
| expect", I meant the settings that are usually used for
| databases in web apps.
| ignoramous wrote:
| 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/
| mangecoeur wrote:
| 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.
| eduction wrote:
| 100%. I specifically clicked for the "why you should care" and
| was disappointed I could not find it.
|
| I certainly don't mind if someone is pushing the limits of what
| SQLite is designed for but personally I'd just rather invest
| the (rather small) overhead of setting up a db server if I need
| a lot of concurrency.
| abound wrote:
| 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
| morshu9001 wrote:
| 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.
| zie wrote:
| 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.
| tombert wrote:
| 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).
| reddalo wrote:
| Yeah, I'm sure those twelve people love watching your
| vacation clips all the time ;)
| apitman wrote:
| Why not encode to H264 or another codec more widely
| supported by clients? Storage is cheap.
| MayeulC wrote:
| > 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).
| tombert wrote:
| 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.
| reddalo wrote:
| 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/
| apitman wrote:
| I hope they keep sqlite as a first class citizen.
| npodbielski wrote:
| 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.
| apitman wrote:
| 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.
| xorcist wrote:
| 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_.
| npodbielski wrote:
| > 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?
| bambax wrote:
| Jellyfin is a media server app that gets installed on a great
| variety of platforms and while it would certainly be possible
| to add a postgres server to the install, the choice of sqlite
| is more than justified here IMHO.
| throwaway894345 wrote:
| As a user of Jellyfin, I'm very sad that it doesn't just use
| Postgres. I basically have to run an NFS system just for
| Jellyfin so that its data can be available to it no matter
| which node it gets scheduled on and also that there are never
| multiple instances running at the same time, even during
| deployments (e.g., I need to take care that deployments
| completely stop the first Jellyfin instance before starting the
| subsequent instance). There are so many unnecessary single
| points of failure, and Postgres would make a pretty big one go
| away (never mind addressing the parallelism problems that
| plague the developers).
|
| Jellyfin is by far the least reliable application I run, but it
| also seems to be best in class.
| KingMob wrote:
| I gave up on Jellyfin after media library updates kept
| hanging on certain video files, and switched to the original
| Emby it was forked from (iiuc).
|
| Emby has a scarily-ancient install process, but it's been
| working just fine with less hassle.
| ants_everywhere wrote:
| I have the same experience. SQLite has been a source of most
| Jellyfin problems, and Jellyfin has more problems than the
| rest of the ~ 150 containers I run regularly.
|
| A stateless design where a stateless jellyfin server talks to
| a postgres database would be simpler and more robust.
| throwaway894345 wrote:
| Yeah, honestly I'm kind of thinking about a media server
| architecture that has a stateless media server that vends
| links to pre-transcoded media in object storage (which
| video players would source from), since pretty much
| anything can handle mp4/h264/acc video. Maybe in the future
| I could add on some on-the-fly transcoding (which would
| happen on a dedicated cluster, reading and writing to
| object storage), but that seems like a pretty big
| undertaking.
| apitman wrote:
| You're from the current generation of selfhosters, which
| culturally is very similar to kit car builders. The next
| generation of selfhosters/indiehosters just want a car to get
| from point A to point B. Sqlite is better for those people.
| throwaway894345 wrote:
| That's a bit of a strange argument considering all the
| hoops one needs to jump through to make Jellyfin work on
| account of Sqlite. I just want to run the software I use on
| the computers I have.
| apitman wrote:
| You're having issues because you're trying to shoehorn it
| into your desired architecture. Most people just want to
| run an app on their Windows laptop and start streaming
| their videos.
| throwaway894345 wrote:
| Maybe that's what most users want, but that's not what
| the software was designed to target, judging from all of
| the documentation and marketing. But yes, clearly the
| software wasn't designed to run in a distributed fashion,
| and that's kind of the point of my criticism--they had to
| go out of their way to couple their application in a way
| that precludes distributed execution. Well designed
| server software is trivial to distribute, and even if you
| never run it in a distributed configuration it makes it
| easy to do the basic parallelism described in this
| article.
| thayne wrote:
| 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.
| throwaway894345 wrote:
| 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
| all down to SQLite, but it's all down to a similar set of
| assumptions (exactly one application instance interacting
| with state over a filesystem interface).
| thayne wrote:
| 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.
| throwaway894345 wrote:
| Yes, but you have to go out of your way when writing
| software to make it so the software _can only_ run on one
| node at a time. Or rather, well-architected software
| should require minimal, isolated edits to run in a
| distributed configuration (for example, replacing SQLite
| with a distributed SQLite).
| thayne wrote:
| That's just not true. Distributed software is much more
| complicated and difficult than non-distributed software.
| Distributed systems have many failure modes that you
| don't have to worry about in non-distributed systems.
|
| Now maybe you could have an abstraction layer over your
| storage layer that supports multiple data stores,
| including a distributed one. But that comes with
| tradeoffs, like being limited to the least common
| denominator of features of the data stores, and having to
| implement the abstraction layer for multiple data stores.
| throwaway894345 wrote:
| I'm a distributed systems architect. I design, build, and
| operate distributed systems.
|
| > Distributed systems have many failure modes that you
| don't have to worry about in non-distributed systems.
|
| Yes, but as previously mentioned, those failure modes are
| handled by abiding a few simple principles. It's also
| worth noting that multiprocess or multithreaded software
| have many of the same failure modes, including the one
| discussed in this post. Architecting systems as though
| they are distributed largely takes care of those failure
| modes as well, making even single-node software like
| Jellyfin more robust.
|
| > Now maybe you could have an abstraction layer over your
| storage layer that supports multiple data stores,
| including a distributed one. But that comes with
| tradeoffs, like being limited to the least common
| denominator of features of the data stores, and having to
| implement the abstraction layer for multiple data stores.
|
| Generally I just target storage interfaces that can be
| easily distributed--things like Postgres (or maybe
| dqlite?) for SQL databases or an object storage API
| instead of a filesystem API. If you build a system like
| it could be distributed one day, you'll end up with a
| simpler, more modular system even if you never scale to
| more than one node (maybe you just want to take advantage
| of parallelism on your single node, as was the case in
| this blog post).
| thayne wrote:
| > just target storage interfaces that can be easily
| distributed--things like Postgres
|
| But as I mentioned above, that makes the system more
| complicated for people who don't need it to be
| distributed.
|
| Setting up separate db software, configuring the
| connection, handling separate updates, etc. is a lot more
| work for most users than Jellyfin just using a local
| embedded sqlite database. And it would probably make the
| application code more complicated as well.
| throwaway894345 wrote:
| > But as I mentioned above, that makes the system more
| complicated for people who don't need it to be
| distributed. Setting up separate db software, configuring
| the connection, handling separate updates, etc. is a lot
| more work for most users than Jellyfin just using a local
| embedded sqlite database.
|
| You can package a Postgres database with your app just
| like SQLite. Users should not have to know that they are
| using Postgres much less configuring connections,
| handling updates, etc.
|
| > And it would probably make the application code more
| complicated as well.
|
| Not at all, this is an article about the hoops the
| application has to jump through to make SQLite behave
| well with parallel access. Postgres is designed for
| parallel access by default. It's strictly simpler from
| the perspective of the application.
| stormbeard wrote:
| Jellyfin isn't meant to be some highly available
| distributed system, so of course this happens when you try
| to operate it like one. The typical user is not someone
| trying to run it via K8s.
| throwaway894345 wrote:
| Yeah, I agree, though making software that can run in a
| distributed configuration is a matter of following a few
| basic principles, and would be far less work than what
| the developers have spent chasing down trying to make
| SQLite work for their application.
|
| The effort required to put an application on Kubernetes
| is a pretty good indicator of software quality. In other
| words, I can have a pretty good idea about how difficult
| a software is to maintain in a single-instance
| configuration by trying to port it to Kubernetes.
| FrinkleFrankle wrote:
| Care to share your setup?
| heavyset_go wrote:
| Jellyfin isn't a Netflix replacement, it's a desktop
| application that's a web app by necessity. Treat it like a
| desktop app and you won't have these issues.
| throwaway894345 wrote:
| They have clients for nearly every device; it's clearly
| intended to be a streaming media server.
| heavyset_go wrote:
| It's a local media library manager in the same vein as
| media servers that came before it that were intended to
| run on desktops and serve up content to consoles and
| whatever on your LAN back when that was the thing to do.
|
| My point is to treat it like software from that lineage
| and you won't have a problem, trying to treat it like
| something it's not, like a distributed web app, will lead
| to issues.
| throwaway894345 wrote:
| It feels like we're saying similar things. We both agree
| that its architecture makes it difficult to run with high
| availability, although I'll point out that the issues
| documented in the article apply to single nodes and even
| on a single node it has pretty specific hardware
| requirements. I think we just disagree about whether "you
| have to hold it very carefully and it works just fine" is
| a good thing or not.
| amaccuish wrote:
| Their whole recent rewrite of the DB code (to Entity Framework)
| is to allow the user choice of DB in future.
| petters wrote:
| 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
| nick_ wrote:
| Agreed. How can a media file sharing app possibly saturate
| Sqlite's write limit? I would use an app-level global lock on
| all writes to Sqlite.
| npodbielski wrote:
| Probably during scanning libraries? They read hundreds of
| files and for each of them look for metadata in the
| internet like discogs and similar. So sure if implemented
| as async in c# you could run into this issue.
| nick_ wrote:
| Are you hinting at the lack of an `AsyncLock` in .NET?
| o11c wrote:
| 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.
| hamandcheese wrote:
| Maybe in theory. In practice, most people who need Postgres
| for their test suite will boot an instance in a docker
| container in CI, and maybe just assume a system version is
| available for local dev.
| zeroq wrote:
| 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?".
| heavyset_go wrote:
| 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.
| andersmurphy wrote:
| 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).
| ricardobeat wrote:
| 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.
| kccqzy wrote:
| 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?
| npodbielski wrote:
| If something is stupid but it works then it is not stupid. If
| this will help them find a solution then sure why not. Though
| I am wondering if this would not be easier to just use
| postgress and focus on features instead.
| apitman wrote:
| Cunningham's Law
| Daniel_sk wrote:
| 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.
| Leherenn wrote:
| 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.
| asa400 wrote:
| Are you running vacuums at all? auto_vacuum enabled at all?
|
| https://sqlite.org/lang_vacuum.html
| porridgeraisin wrote:
| In memory DBs don't have anything to vacuum.
|
| However... what you (and OP) are looking for might be pragma
| shrink_memory [1].
|
| [1] https://sqlite.org/pragma.html#pragma_shrink_memory
| asa400 wrote:
| Ah, you're correct. I read too fast and missed that it was
| in-memory databases specifically!
| kachapopopow wrote:
| 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.
| pstuart wrote:
| If you're deleting all rows you can also just drop the table
| and recreate it.
| ddtaylor wrote:
| 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.
| thayne wrote:
| 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.
| Sammi wrote:
| 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.
| yread wrote:
| 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.
| fitsumbelay wrote:
| Very helpful and a model for how technical posts should be
| written: clarity, concision, anchor links that summarize the top
| lines. It was a pleasure to read.
| tombert wrote:
| Does this mean I can finally load-balance with multiple Jellyfin
| instances?
|
| A million years ago, back when I still used Emby, I was annoyed
| that I couldn't use it across multiple in Docker Swarm due to
| locking of SQLite. It really annoyed me, enough to where I
| started (but never completed) a driver to change the DB to
| postgres [1]. I ended up moving everything over to a single
| server, which is mostly fine unless I have multiple people
| transcoding at the same time.
|
| If this is actually fixed then I might have an excuse to
| rearchitect my home server setup again.
|
| [1] https://github.com/Tombert/embypostgres
| Yodel0914 wrote:
| Jellyfin have just gone through a massive refactor and pulled
| all their data access code into EFCore. This opens the path for
| supporting different RBDMSs which think is next on their list.
| EionRobb wrote:
| 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.
| Multicomp wrote:
| Would the SQLite vacuum function help with that?
| mceachen wrote:
| You can VACUUM INTO, ~~but standard vacuum won't rewrite the
| whole db~~ (vacuum rewrites the whole db)
|
| https://sqlite.org/lang_vacuum.html
|
| (Edit: if multiple processes are concurrently reading and
| writing, and one process vacuums, verify that the right
| things happen: specifically, that concurrent writes from
| other processes during a vacuum don't get erased by the other
| processes' vacuum. You may need an external advisory lock to
| avoid data loss).
| return_to_monke wrote:
| > You can VACUUM INTO, but standard vacuum won't rewrite
| the whole db.
|
| This is not true. From the link you posted:
|
| > The VACUUM command works by copying the contents of the
| database into a temporary database file and then
| overwriting the original with the contents of the temporary
| file.
| mceachen wrote:
| Ugh, you're totally right.
|
| I always get optimize and vacuum mixed up.
|
| https://sqlite.org/pragma.html#pragma_optimize
| teddyh wrote:
| > _You can VACUUM INTO, ~~but standard vacuum won't rewrite
| the whole db~~ (vacuum rewrites the whole db)_
|
| HN does not support whatever markup you are trying to use.
| You have to use Unicode:
|
| "You can VACUUM INTO, but standard vacuum won't rewrite the
| whole db (vacuum rewrites the whole db)"
| didip wrote:
| This is fascinating. What would be the solution for this? You
| can't ask users to defrag.
| DANmode wrote:
| Perform the file operation, after zipping the existing db as
| a backup, and leaving the original where it sits.
|
| Success, performance increase.
|
| Failure, no change.
| axitanull wrote:
| Forgive my lack of knowledge, but how is simply zipping the
| original file would "defrag" the file?
|
| Shouldn't the file be moved into different disk fragment
| first, for that to happen?
| DANmode wrote:
| That's "the file operation" :]
|
| Again: zip is a backup
| izacus wrote:
| That's much more likely flash degradation than actual
| fragmentation. Did you use cheap tablets with eMMC storage?
| georgemcbay wrote:
| > That's much more likely flash degradation than actual
| fragmentation. Did you use cheap tablets with eMMC storage?
|
| My understanding of the parent reply's situation is that this
| was happening on the tablets of their users, so it kinda
| doesn't matter that it can be avoided by not using cheap
| tablets.
|
| Most apps aren't in a position to tell their users that they
| are on their own when they run into what feels like an
| unreasonable app slowdown because they didn't buy a good
| enough device to run it on, especially when they've
| previously experienced it running just fine.
|
| If _all_ their apps feel like crap on that tablet, sure, that
| might fly... but if its only your app (or only a small set of
| apps that use SQLite in the same way the OP 's company did)
| that feels like crap after a while, that's effectively a you
| problem (to solve) even if its not really a you problem.
|
| In any case, its an interesting data point and could be very
| useful information to others who run into similar issues.
| izacus wrote:
| I don't quite understand what you're arguing here.
|
| I'm merely saying that the root cause was misidentified -
| the performance degradation didn't happen due to
| fragmentation, but because the flash storage was degraded
| to the point where the write performance dropped
| significantly. This happens faster for eMMC vs. SSD-style
| storage.
|
| Copying the DB file moved the data to different storage
| blocks which is why it (temporarily again) improved
| performance.
| EionRobb wrote:
| We had that thought too. I'll have to try dig out what the
| tablets were to find out exactly what type - this would have
| been 3 or 4 years ago now. We sort of ruled that out because:
|
| The other workaround to get a speed boost was the user to
| uninstall and reinstall the app (and then wait for all the
| data to download again) but that didn't fly because the users
| would delete before they'd synced off all their data and then
| data would go missing.
|
| This was all despite having VACUUM running whenever the app
| started.
|
| Whether it was bad flash or no, we still had to try resolve
| it as the client wouldn't buy new hardware until we could
| prove that we had the knowledge to make the problem go away
| first :/
| EionRobb wrote:
| Ah yup eMMC
| https://www.gsmarena.com/samsung_galaxy_tab_active2-8897.php
| izacus wrote:
| Yeah, then copying the files most likely moved them to
| different part of storage and it improved performance
| again.
|
| Sadly that's a common plague for cheaper Android hardware -
| after enough writes the flash performance drops off a cliff
| making those devices essentially unusable :/
|
| (More expensive hardware - including Apples - tends to have
| UFS type storage which lasts longer.)
| simscitizen wrote:
| Copying the file likely forces the creation of a new one with
| no or lower filesystem fragmentation (e.g. a 1MB file probably
| gets assigned to 1MB of consecutive FS blocks). Then those FS
| blocks likely get assigned to flash dies in a way that makes
| sense (i.e. the FS blocks are evenly distributed across flash
| dies). This can improve I/O perf by some constant factor. See
| https://www.usenix.org/system/files/fast24-jun.pdf for instance
| for more explanation.
|
| I would say that the much more common degradation is caused by
| write amplification due to a nearly full flash drive (or a
| flash drive that appears nearly full to the FTL because the
| system doesn't implement some TRIM-like mechanism to tell the
| FTL about free blocks). This generally leads to systemwide
| slowdown though rather than slowdown accessing just one
| particular file.
|
| This was especially prevalent on some older Android devices
| which didn't bother to implement TRIM or an equivalent feature
| (which even affected the Google devices, like the Nexus 7).
| ErroneousBosh wrote:
| > One of the biggest contributors I've had in the past for
| SQLite blocking was disk fragmentation.
|
| Is that even still a thing? I thought modern filesystems like
| ext4 were supposed to be largely immune to that.
| tredre3 wrote:
| Ext4 isn't used on Android, and it isn't immune to
| fragmentation.
|
| The way ext4 reduces fragmentation is with some basic
| heuristics: mainly, it spreads files across the full disk
| instead of finding the next free spot. So they have room to
| grow without fragmenting. When the space gets low, it
| fragments just as badly as older file systems unfortunately.
| slashdave wrote:
| I am a little confused, but maybe I am missing some context?
| Wouldn't using a proper database be a lot easier than all of this
| transaction hacking? I mean, is Postgres that hard to use?
| rpcope1 wrote:
| 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.
___________________________________________________________________
(page generated 2025-11-02 23:01 UTC)