We’ve been testing LiteFS for while before we go live and we are a bit concerned about some issues we have found, which might be caused by some not so well-written queries, but we would like make sure we really understand LiteFS and its tradeoffs.
I saw from the FAQ that LiteFS is not suitable for heavy write application, and it mentions 100 transactions per second. Does it count any database write as a transaction or is this 100 SQL Transactions? Also, does LiteFS perform just like SQLite when it comes to reading?
We have a node application that uses prisma ORM and we need to perform some transactions and they work fine with SQLite, but with LiteFS, we are having problems with timeouts even though our transactions are not so heavy. The solution we’ve found so far is basically to avoid SQL transactions.
All database writes should go through SQL with SQLite. I’m not familiar how Prisma interacts with SQLite per se but it should convert its commands down to SQL.
Largely, yes. LiteFS can be slower if you are querying across your entire database and your database does not fit into memory. However, for most queries it should perform similarly to SQLite on average.
LiteFS can take longer to process a transaction so it could be that your SQLite transaction was just under the timeout threshold and LiteFS is causing it to go over. Looks like Prisma defaults to a 5s timeout. If you increase it to 10s do you still see the same issue?
Also, are you running your SQLite database with the default rollback journal or did you enable the WAL via PRAGMA journal_mode = wal?
We considered to increase the timeout to 10s, but as we stopped using a transaction to perform the change, it simply started to take less than 5s. So, we’re avoiding to use transactions.
We’re using the default rollback journal. We’re quite new to SQLite/LiteFS in production.
