895151 - Review health report database schema

Status: RESOLVED WONTFIX

(Firefox Health Report Graveyard :: Data Collection, defect)

Description

[Marco Bonardo [:mak]]

There are various things that should be verified in the schema:

1. it has a ton of Indices. indices are expensive, both for database size and insert operations, one should add an index only for hot queries. It has 21 indices. Some tables in my profile have a few (<10) entries, for these sizes linear scan may be faster than an index.
2. it has a lot of auto-indices due to unique constraints.
3. it creates views and then queries them with a SELECT WHERE. Views cannot use indices, so those are slower than a direct UNION ALL query.
4. it looks a bit too much normalized. This may be good or bad, depends on the queries.

Not a priority but I think a small analysis may be useful, I could probably make a first pass and see if there's some low hanging fruits there.

Comment 1

[Gregory Szorc [:gps]]

I'm up for making FHR's storage suck less. Keep in mind that we'll eventually need to support version 3 of the payload format and this will necessitate schema changes. I'd prefer to minimize the number of backwards-incompatible schema migrations. So, I'd prefer simple changes that don't impact schema (e.g. removing indices).

Comment 2

[Richard Newman [:rnewman]]

For reference, the Android schema (which implements v3):

https://github.com/mozilla-services/android-sync/blob/develop/src/main/java/org/mozilla/gecko/background/healthreport/HealthReportDatabaseStorage.java

Comment 3

[Georg Fritzsche [:gfritzsche]]

FHR on desktop is about to get killed, we are transitioning to Unified Telemetry [0].

0: https://wiki.mozilla.org/Unified_Telemetry