simolus3
commented
1 month ago Thanks for filing these issues! I've already started on this one in ff99144909f23a9fd3068517b11795f31101a26a, but in the current state it would be opt-in.
It would be ideal if drift could transparently (i.e without additional consumer configuration) distribute reads across isolates so that they could run concurrently and not block one another
A problem with doing this transparently is that we need to reconfigure the database. By default, sqlite3 uses a journaling mechanism only allowing a single concurrent operation on the database. An alternative, write-ahead logging, supports one writer and many readers to access the database concurrently. So to support a pool of readers, we need users to set pragma journal_mode = WAL; early when using the database. Drift has always given users full control over pragmas (ok, except for user_version for migrations) without enforcing them. So enabling WAL mode by default (which would be required for this feature to work reliably) may be too risky.
Another thing is that the current implementation gives each isolate its own independent sqlite3_db connection. To benefit from shared caches, we should also investigate whether we can use a multithreaded connection shared across isolate, or whether sqlite3's internal locks ruin performance in that case.
Based on recent conversations with @simolus3, I learned that currently Drift does not distribute read executions across background isolates and instead both writes and reads execute on a single isolate.
This can negatively impact apps that load a lot of data (or have many queries) during user-sensitive moments (such as app launch).
In my case, my app loads most of the users data from the drift database on launch, as this data is required to show a "dashboard" experience. However, one table is much larger than the others, and its query causes other queries to be queued behind it. In development mode these queries generally take ~700ms even though they may be a 5 column table with, say, 5-10 records in them. Sometimes these queries can hang even longer, taking beyond 3 seconds to complete (generally during application-wide state changes that cause many queries to execute).
Describe the solution you'd like:
It would be ideal if drift could transparently (i.e without additional consumer configuration) distribute reads across isolates so that they could run concurrently and not block one another (obviously there is some ceiling on the amount of concurrency we can harness, so this will be finite). One thing to consider is the device running the application, as having too many isolates may result in performance degradation. Drift could have a sane default that spins up N number of isolates where N is dependent on the number of cores on the device (I've never read this information in a dart program though I suspect it's possible) with the ability for the consumer to override this calculation?
Please let me know if there is any other information you'd need for this request. Thank you!