Investigate performance-enhancing database schema changes

[friedbunny]

The offline/ambient cache SQLite database currently uses journal_mode = DELETE and synchronous = FULL pragmas. These modes are safe and have proven reasonably reliable, but they are slow.

We briefly moved to WAL + NORMAL (#5796) and saw increased speed, but that ended up being short-lived as it provoked bad behavior on Android (#6193) and was reverted out of prudence (#6320).

#6319 presented a way to avoid the Android file permission issue by switching to in-memory temporary stores. This would purportedly bring its own speed improvements, but also a variety of unknowns — so we tabled it.

To a glorious future

After more research and testing, I’d like to bring back these changes:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;

So what do we need to feel confident about another schema change?

• Profile memory usage
  • iOS
  • Android
• Write and expand tests
• Understand the causes of temp file usage under our usage patterns, and the implications of keeping this temp storage in memory
• Understand what SQLite state is global and shared with application-level database connections.
• Hands-on testing on all platforms
  • Android
  • iOS
  • macOS
  • Node
  • Qt
• Add a benchmark test using gbenchmark (./benchmark/...`)
• Your checkbox here

/cc @mapbox/mobile @kkaefer @tmpsantos @brunoabinader @mikemorris

[jfirebaugh]

👍 I added a couple bullet points above.

[tmpsantos]

Added some more...

[stale]

This issue has been automatically detected as stale because it has not had recent activity and will be archived. Thank you for your contributions.

[stale]

This issue has been automatically detected as stale because it has not had recent activity and will be archived. Thank you for your contributions.