Partition and rename the per-namespace billable usage table

Vijay Hawoldarrequested to merge
18552-partition-namespace-aggregates into master

What does this MR do and why?

Partitions and renames the per-namespace billable usage table ahead of the export rework agreed in database review on !256937 (merged).

The air-gapped usage export sums every per-namespace row in the licence term, so its cost grows with root namespaces x feature names x days. The rework moves the export onto an instance-wide daily totals table, maintained by a worker (next MR). This table keeps the per-namespace detail, which is retained for future namespace-level association, so it gets monthly partitions and a retention window, as the database review suggested while it is still empty.

  • billable_usage_daily_aggregates is dropped and recreated as billable_usage_daily_namespace_aggregates, partitioned monthly by usage_date with three years of retention. It is written only behind the local_billing_persistence and billing_event_tracking flags on offline-licensed instances, so it holds no data anywhere.
  • The new name says the scope, next to the billable_usage_daily_totals table that follows.
  • The unique key puts root_namespace_id last: (usage_date, event_type, feature_qualified_name, operation_type, root_namespace_id). Its leading four columns are then the export key, so summing one day across namespaces, which the totals worker in !258358 (merged) does, can read one partition's index in key order. The planner may still prefer the primary key with a sort; the per-day query and its plan are in !258358 (merged).
  • event_aggregate_uuid and its unique index are not carried over. The export derives its own event_id (!256937 (merged)), the totals table has none, and the uuid was a hash of the unique tuple, so nothing read it and its index enforced nothing the tuple index does not.
  • The model becomes Utilization::BillableUsage::DailyNamespaceAggregate.

Related to https://gitlab.com/gitlab-org/customers-gitlab-com/-/work_items/18552.

Database

Drops an empty table and creates a partitioned one; no data migration. gitlab_main_cell_local, table_size: small. The drop precedent is 20260710120000_repartition_p_duo_workflows_checkpoint_blobs_by_workflow_created_at; the monthly date-keyed partitioning follows ai_events_counts.

CREATE TABLE billable_usage_daily_namespace_aggregates (
    ...
) PARTITION BY RANGE (usage_date);

ALTER TABLE ONLY billable_usage_daily_namespace_aggregates
    ADD CONSTRAINT billable_usage_daily_namespace_aggregates_pkey PRIMARY KEY (id, usage_date);

CREATE UNIQUE INDEX index_billable_usage_daily_ns_aggs_on_unique_tuple
    ON ONLY billable_usage_daily_namespace_aggregates
    USING btree (usage_date, event_type, feature_qualified_name, operation_type, root_namespace_id) NULLS NOT DISTINCT;

Partitions come from the partition manager (retain_for: 3.years, monthly strategy). After migrating, sync_partitions created 43 monthly partitions, billable_usage_daily_namespace_aggregates_202310 through _202704 (three years back, six months ahead).

Retention is declared in db/docs/data_retention/billable_usage_daily_namespace_aggregates.yml (drop_partition, 1095 days). The per-namespace detail is kept so usage can later be associated with namespaces on the instance. Air-gapped subscriptions mostly run on multi-year terms, and three years follows that data while creating fewer empty past partitions up front; it can be lengthened later if the namespace-level work needs more history. The export reads the totals table, which is kept indefinitely, so the export does not depend on this window.

The write path's upsert

RecordAggregateService changes only its conflict target, which follows the new unique key, and it no longer writes a uuid. The upsert against the partitioned parent, captured from the service:

INSERT INTO "billable_usage_daily_namespace_aggregates" ("usage_date","event_type","feature_qualified_name","operation_type","root_namespace_id","unit_of_measure","quantity","events_count","created_at","updated_at")
VALUES ('2026-09-10', 'secrets_read', 'secrets_read', NULL, 42, 'request', 1.0, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT ("usage_date","event_type","feature_qualified_name","operation_type","root_namespace_id")
DO UPDATE SET quantity = billable_usage_daily_namespace_aggregates.quantity + EXCLUDED.quantity,
  events_count = billable_usage_daily_namespace_aggregates.events_count + EXCLUDED.events_count,
  updated_at = EXCLUDED.updated_at
RETURNING "id","usage_date"

That is the counter form; a snapshot sets quantity = EXCLUDED.quantity instead. Plans from the test database, inside a rolled-back transaction. The unique tuple index is the conflict arbiter in both cases; Postgres routes the row to the usage_date partition at execution, which EXPLAIN does not show as a separate node.

Conflicting row (update):

Insert on billable_usage_daily_namespace_aggregates (actual time=0.031..0.032 rows=1 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: index_billable_usage_daily_ns_aggs_on_unique_tuple
  Tuples Inserted: 0
  Conflicting Tuples: 1
  Buffers: shared hit=15
  ->  Result (actual time=0.001..0.001 rows=1 loops=1)
        Buffers: shared hit=1

New row (insert):

Insert on billable_usage_daily_namespace_aggregates (actual time=0.029..0.029 rows=1 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: index_billable_usage_daily_ns_aggs_on_unique_tuple
  Tuples Inserted: 1
  Conflicting Tuples: 0
  Buffers: shared hit=17
  ->  Result (actual time=0.001..0.002 rows=1 loops=1)
        Buffers: shared hit=1

Development seed

ee/db/fixtures/development/99_billable_usage_daily_namespace_aggregates.rb seeds a few rows across two months through RecordAggregateService, so they are written like real rows, and only when the table is empty. It is here because the renamed table counts as new, and run-dev-fixtures-ee requires new tables to be seeded.

Data deletion

The migration drops billable_usage_daily_aggregates and recreates it, partitioned and renamed, so Danger flags it as data deletion.

  1. Records affected: expected to be none. The table is written only when both local_billing_persistence and billing_event_tracking are enabled, both development flags with default_enabled: false, and only on an offline cloud licence; the write path is excluded on GitLab.com (Gitlab::BillingEvents::Client#local_persistence_enabled?). An instance can only hold rows if an administrator enabled both development flags.
  2. Recovery. With no rows there is nothing to recover. On an instance that did enable the flags, the dropped rows could only be restored from a database backup taken before the migration.
  3. User impact: none. Nothing reads the table yet (the export is unreleased), and the write path continues against the new table after the migration.

Expected size and access

  • Growth. One row per root namespace per usage type per day with activity, on offline-licensed instances only; empty on GitLab.com. Retention caps it at three years.
  • Reads and writes. One upsert per billable event (unchanged). Reads will come from the totals worker in !258358 (merged), which finds recently changed days by updated_at and adds the index on that column with that query.
  • Availability risk. None expected: small, capped by partition drops, off hot paths.

Migration output

From the test database; the quote_column_name lines are trimmed.

db:migrate:

main: == 20260928093000 ReplaceBillableUsageDailyAggregatesWithPartitionedTable: migrating
main: -- drop_table(:billable_usage_daily_aggregates, {if_exists: true})
main:    -> 0.0388s
main: -- create_table(:billable_usage_daily_namespace_aggregates, {primary_key: [:id, :usage_date], options: "PARTITION BY RANGE (usage_date)", if_not_exists: true})
main:    -> 0.0066s
main: == 20260928093000 ReplaceBillableUsageDailyAggregatesWithPartitionedTable: migrated (0.0506s)

db:rollback:

main: == 20260928093000 ReplaceBillableUsageDailyAggregatesWithPartitionedTable: reverting
main: -- drop_table(:billable_usage_daily_namespace_aggregates, {if_exists: true})
main:    -> 0.0355s
main: -- create_table(:billable_usage_daily_aggregates, {if_not_exists: true})
main:    -> 0.0068s
main: == 20260928093000 ReplaceBillableUsageDailyAggregatesWithPartitionedTable: reverted (0.0473s)

Rolling back and dumping gives back master's db/structure.sql apart from the position of ai_events_counts. That table's block moves in any fresh dump since master removed Ai::EventsCount in 0324b5f9, so db:check-migrations fails on it for every migration MR and is skipped here with pipeline:skip-check-migrations, as on !257679. None of this MR's tables are in that diff. Rolling back on an instance that had written rows would lose them, but none exist while the flags are off.

References

How to set up and validate locally

bin/rails db:migrate
bin/rails runner 'Gitlab::Database::Partitioning.sync_partitions([Utilization::BillableUsage::DailyNamespaceAggregate])'
bundle exec rspec ee/spec/models/utilization/billable_usage/daily_namespace_aggregate_spec.rb \
  ee/spec/services/utilization/billable_usage/record_aggregate_service_spec.rb \
  ee/spec/lib/ee/gitlab/billing_events/client_spec.rb
bundle exec rspec -r spec_helper spec/db/schema_spec.rb spec/db/docs_spec.rb spec/db/retention_policy_definitions_spec.rb

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Edited by Vijay Hawoldar

Merge request reports