Just read this blog post on how Rails 8 features immediate mode for SQLite and makes it a viable choice for most production workloads (assuming no deployment redundancy).
Is immediate mode available on Ecto’s SQLite 3 adapter too? I don’t see a mention of it on the docs. Is there a way to make it the default?
1 Like
Iirc are options on Repo.transaction to make it add the necessary keyword.
Here’s a relevant discussion in the ecto_sqlite3 repo:
opened 05:10PM - 02 Sep 24 UTC
closed 04:16PM - 13 Sep 24 UTC
Ruby on Rails is making many changes to improve SQLite support. Apparently, one … of the most noticeable is using `BEGIN IMMEDIATE TRANSACTION`. Quoting from [this article][1] from the author of such improvements for RoR (it is a highly recommended read for anyone using SQLite):
> By default, SQLite uses a deferred transaction mode. This means that SQLite will not acquire the lock until a write operation is made inside the transaction.
> In a context where you only have one connection or you have a large amount of transactions that only do read operations, this is great for performance, because it means that SQLite doesn’t have to acquire a lock on the database for every transaction, only for transactions that actually write to the database.
The he goes about how 99% of the time starting a transaction in Ruby on Rails means that there will be a database write. The conclusion is that immediate transactions should be the default for most cases.
This made me wonder a few things:
1. Is it possible to use immediate transactions with Ecto?
2. If not, would it be possible to add support for them? I could help if this feature seems interesting to have.
3. Would it be interesting to replicate the RoR behaviour in Ecto as well and make immediate transactions the default? I've been thinking about it and the fact that most transactions result in a database write seems true in my experience.
[1]: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/
Short answer: just add a function to your Repo module to do immediate transactions
1 Like
