[syastrov]

I did an experiment of switching an app which uses Postgres to use SQLite. I ran into issues where I had a long running transaction in one process doing writes and another process doing reads and writes. As soon as the second process tried to do any writes, I would often get the queries running into locks and aborting. Otherwise, it worked great for reads and sped up the app a lot as there were lots of N+1 queries issues.

This is also after playing with settings about WAL mode.

It would not be a viable solution to split these tables into separate databases to avoid the concurrency issue. Also, most of the issues involve writes to the same tables.

Is there honestly any way to get that working well with current SQLite or do we need to wait for hctree or begin concurrent to mature?

I would be concerned that to get this working in its current state would require a large refactoring of the app which would require a lot of effort and would introduce limitations that make coding more difficult.