Moving use counters out of telemetry.main for performance.

[Daniel Thorn]

Use counters are now available via telemetry.main_use_counters

If you are looking at telemetry.main for any other data, expect queries to perform at least two to three times faster when the view is migrated to read from the new table without use counters, moz-fx-data-shared-prod.telemetry_stable.main_v5, as soon as it has been backfilled with data from main_v4, at which time I will send another update. If you want an early look, expect the first day of full data in main_v5 to be 2023-09-29 until I begin backfilling next week.

[Alessio Placitelli]

Given that the historical data was broken, is there any performance gain from just deleting it?

[Daniel Thorn]

Context: per Bug 1845779, use counter data was broken prior to 2023-08-16 and Fx118.

For that reason, telemetry.main_use_counter will probably have data prior to 2023-08-16 removed, rather than being backfilled with historical data from main_v4. Given that the table currently only contains data back to 2023-06-20, I don't expect this to significantly impact performance.

Also, rather than filter data prior to Fx118 out of the table, I believe there is a separate effort to deprecate and/or delete the table entirely, in favor of a new glean ping, which I do not have further context on.

[Daniel Thorn]

The backfill has completed, and telemetry.main is now updated to point at the new main_v5 table without use counters.

Tables telemetry.saved_session and telemetry.first_shutdown, which share a schema with telemetry.main, have also been backfilled and updated to move use counters to separate tables.

per this decision brief, main was backfilled to 25 months, while the other two tables were backfilled to the full extent of their history.

[Daniel Thorn]

tl;dr Effective today, telemetry_stable.main_v4 is now a restricted access archive

main_v5 was backfilled to 37 months because the decision brief was updated. Data partitions older than 37 months in telemetry_stable.main_v5 are moved on a daily basis to telemetry_stable.main_v5_archive which also a restricted access archive.

No ad hoc or stmo queries were found to have been using main_v4 since telemetry.main was updated to point to main_v5 on Oct 23rd, and all scheduled ETL has been updated to use main or main_v5.

For questions and additional details please see https://mozilla-hub.atlassian.net/browse/DENG-476 and https://mozilla-hub.atlassian.net/browse/DSRE-1456 or contact me directly