Issues like large-WAL and the need for periodic blocking maintenance (e.g. vacuum, to free space and un-interleave data) are excellent reasons to aggressively t

Issues like large-WAL and the need for periodic blocking maintenance (e.g. vacuum, to free space and un-interleave data) are excellent reasons to aggressively try to keep your insert/trigger/etc logic as simple as possible. In all databases. When it's simple enough, you can safely abstract over the DB and do stuff like: 1) maintain an in-memory operation-queue, so you can copy the DB when idle, vacuum as long as necessary on the copy, and then switch to the vacuumed copy after replaying the queue. (SQLite allows only a single writer, so statement-replay is safe, unlike concurrent-writer databases in some cases since you can't recreate the DB's row-visibility logic) 2) in-memory caches can alleviate a lot of load, but are hideously difficult or impossible to maintain if you allow complex or DB-only insertion logic (e.g. 'on conflict' rules). Simple table+rowid based caches can be utterly trivial and correct if you have nothing complex. Or if you really want, a full-blown lambda architecture is possible, and sometimes not even very difficult. 3) simple logic almost always completes more quickly in the DB, and long-running transactions are the bane of your DB's existence, causing large WALs, worse contention, etc. Aim to keep small atomic operations safe, e.g.: don't require a transaction to modify 100k records correctly, allow each one to be atomic. Then you can batch in any way you please, optimizing for throughput or non-blocking-ness as desired. Complex queries are far safer since you can just ignore your abstraction layer. But complex modifications are worth avoiding whenever possible.