Re:Performance/Avoid SQLite in Your Next Firefox Feature (2014)
by Groxx on Sunday June 14, 2020 @04:33PM
(#23521079)
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.
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.