SQLite

Performance-optimizations:

  • PRAGMA journal_mode=WAL; faster, but requires vfs-shared-memory-primitives. =DELETE is normal. =OFF even faster, but risks corruption on OS-crash.
  • PRAGMA synchronous=normal; less sync than =full. =off disable fsync but risks corruptions if Operating System crashes during write.
  • PRAGMA page_size= set page-size, – power of 2 <= 65536, defaults to 4K. Do PRAGMA VACUUM in journal_mode=DELETE to apply to existing DB.
  • PRAGMA analysis_limit= reduce optimize time/depth, – somewhere between a few hundreds to a few thousands.
  • PRAGMA optimize; good to run, – for instance before closing db.