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).

https://github.com/mapbox/mapbox-gl-native/pull/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[bot]]

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[bot]]

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