Add instance-wide daily totals for billable usage

Vijay Hawoldarrequested to merge
18552-billable-usage-daily-totals into master

What does this MR do and why?

Adds an instance-wide daily totals table for air-gapped billable usage, and an hourly worker that keeps it current. This is the pre-aggregation agreed in database review on !256937 (merged) (note_3908065381), since the export currently re-sums every per-namespace row on each download.

  • billable_usage_daily_totals: one row per day per usage type for the whole instance.
  • Utilization::BillableUsage::RefreshDailyTotalsService recomputes each changed day whole and overwrites it. Reruns are idempotent, and late events are picked up.
  • Utilization::BillableUsage::RefreshDailyTotalsCronWorker runs it hourly, gated on Gitlab::BillingEvents::Client#local_persistence_enabled?.
  • An index on billable_usage_daily_namespace_aggregates.updated_at is used to find changed days.

The cursor is derived from the totals table itself (MAX(source_updated_at)), so there is no separate state. It re-reads an hour's margin.

Builds on !258349 (merged) (merged). The export switches to the totals in !256937 (merged).

Database

Two migrations, both on tables that are empty everywhere (written only behind flags on offline-licensed instances). gitlab_main_cell_local, table_size: small.

CREATE TABLE billable_usage_daily_totals (
    id bigint NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    source_updated_at timestamp with time zone NOT NULL,
    events_count bigint DEFAULT 0 NOT NULL,
    usage_date date NOT NULL,
    schema_version smallint DEFAULT 1 NOT NULL,
    quantity numeric(20,4) NOT NULL,
    event_type text NOT NULL,
    unit_of_measure text NOT NULL,
    feature_qualified_name text NOT NULL,
    operation_type text,
    ...
);
CREATE UNIQUE INDEX index_billable_usage_daily_totals_on_unique_tuple ON billable_usage_daily_totals
    USING btree (usage_date, event_type, feature_qualified_name, operation_type) NULLS NOT DISTINCT;
CREATE INDEX index_billable_usage_daily_ns_aggs_on_updated_at ON ONLY billable_usage_daily_namespace_aggregates
    USING btree (updated_at);

quantity is numeric(20,4) because a sum across namespaces can exceed numeric(14,4). The totals are kept indefinitely. The updated_at index ships here with the query that uses it. The table is about 730 rows a year and empty on GitLab.com.

Queries

Plans are from Database Lab, on a seed of 3,650,000 per-namespace rows (one year x 5,000 root namespaces x 2 event types, with today's 10,000 rows written in the last hour) and a year of totals (730 rows).

Query Runs Plan
1. Read the watermark Once per run https://postgres.ai/console/gitlab/gitlab-production-main/sessions/60680/commands/165896
2. Find changed days, hourly Once per run https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/60680/commands/165887
2. Find changed days, first backfill Once, when the totals table is empty https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/60680/commands/165890
3. Recompute a day Once per changed day https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/60680/commands/165889
4. Write a day's totals Once per changed day https://postgres.ai/console/gitlab/gitlab-production-main/sessions/60680/commands/165897

1. Read the watermark (DailyTotal.watermark)

SELECT MAX("billable_usage_daily_totals"."source_updated_at") FROM "billable_usage_daily_totals"

2. Find changed days (DailyNamespaceAggregate.changed_usage_dates)

The hourly run reads each partition through its updated_at index. The first backfill has no watermark, so it drops the WHERE and reads every row; the next hourly run retries if it fails.

SELECT usage_date FROM billable_usage_daily_namespace_aggregates
WHERE updated_at > '2026-10-02 08:32:01.432191'
GROUP BY usage_date ORDER BY MAX(updated_at) ASC, usage_date ASC LIMIT 1500

3. Recompute a day (DailyNamespaceAggregate.totals_for)

At a worst case of about 270,000 rows for one day (36 flow types), this takes 230 to 350 ms per changed day, from a local PostgreSQL 17 database rather than Database Lab: plan.

SELECT usage_date, event_type, feature_qualified_name, operation_type,
       MIN(unit_of_measure), MAX(schema_version), SUM(quantity), SUM(events_count), MAX(updated_at)
FROM billable_usage_daily_namespace_aggregates
WHERE usage_date = '2026-08-30'
GROUP BY usage_date, event_type, feature_qualified_name, operation_type

4. Write a day's totals (DailyTotal.upsert_all)

The plan uses a simplified SET clause, without the CASE on updated_at.

INSERT INTO "billable_usage_daily_totals" ("usage_date","event_type","feature_qualified_name","operation_type","unit_of_measure","schema_version","quantity","events_count","source_updated_at","created_at","updated_at")
VALUES ('2026-09-10', 'secrets_read', 'secrets_read', NULL, 'request', 1, 6.0, 2, '2026-09-30 06:05:38.535510', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT ("usage_date","event_type","feature_qualified_name","operation_type")
DO UPDATE SET updated_at=(CASE WHEN (<every column below IS NOT DISTINCT FROM excluded>) THEN "billable_usage_daily_totals".updated_at ELSE CURRENT_TIMESTAMP END),
  "unit_of_measure"=excluded."unit_of_measure","schema_version"=excluded."schema_version","quantity"=excluded."quantity",
  "events_count"=excluded."events_count","source_updated_at"=excluded."source_updated_at"
RETURNING "id"

Migration output

From the test database; the quote_column_name, transaction_open? and per-partition index lines are trimmed (43 partitions, each an add_index ... algorithm: :concurrently of about 1 ms).

main: == 20260929060000 CreateBillableUsageDailyTotals: migrating ===================
main: -- create_table(:billable_usage_daily_totals)
main:    -> 0.0450s
main: == 20260929060000 CreateBillableUsageDailyTotals: migrated (0.0626s) ==========
main: == 20260929060100 AddUpdatedAtIndexToBillableUsageDailyNamespaceAggregates: migrating
main: -- add_index(:billable_usage_daily_namespace_aggregates, :updated_at, {name: :index_billable_usage_daily_ns_aggs_on_updated_at})
main:    -> 0.0023s
main: == 20260929060100 AddUpdatedAtIndexToBillableUsageDailyNamespaceAggregates: migrated (0.1867s)
main: == 20260929060100 AddUpdatedAtIndexToBillableUsageDailyNamespaceAggregates: reverting
main: -- remove_index(:billable_usage_daily_namespace_aggregates, {name: :index_billable_usage_daily_ns_aggs_on_updated_at})
main:    -> 0.0058s
main: == 20260929060100 AddUpdatedAtIndexToBillableUsageDailyNamespaceAggregates: reverted (0.0870s)
main: == 20260929060000 CreateBillableUsageDailyTotals: reverting ===================
main: -- drop_table(:billable_usage_daily_totals)
main:    -> 0.0341s
main: == 20260929060000 CreateBillableUsageDailyTotals: reverted (0.0492s) ==========

Rolling both back and dumping gives back !258349 (merged)'s db/structure.sql byte for byte.

References

How to set up and validate locally

bundle exec rspec ee/spec/models/utilization/billable_usage/daily_total_spec.rb \
  ee/spec/services/utilization/billable_usage/refresh_daily_totals_service_spec.rb \
  ee/spec/workers/utilization/billable_usage/refresh_daily_totals_cron_worker_spec.rb

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Edited by Vijay Hawoldar

Merge request reports