Make session credits ingestion drain its backlog safely

Andrew Jungrequested to merge
ajung/607369-session-credits-scan-per-status into master

What does this MR do and why?

Cron worker Ai::DuoWorkflows::FetchSessionCreditsCronWorker scans Duo Agent Platform sessions in a billable status and fans out Ai::DuoWorkflows::FetchNamespaceSessionCreditsWorker jobs, at most 100 ids each, that call CustomersDot's sessionCreditsUsed and upsert duo_workflow_session_enrichments in ClickHouse. This MR is one commit on master, EE only, GitLab side only, and addresses three defects in that pipeline.

  1. The scan timed out. status IN (5 values) on the leading column of idx_workflows_status_updated_at_id forced Postgres to sort every row in the window before the limit. The cursor was frozen at 2026-09-05, so the window was 16 days, and the production scan took 47.7 seconds against the 15 second statement timeout every tick, and dispatched nothing. Fixed in !256920 (merged), which merged and is now in production: one branch per billable status joined with from_union, so Postgres plans a Merge Append over bounded index scans. This MR keeps that structure and adds the per-batch limit and the in-tick continuation described next.

  2. The backlog walk was too slow. 1,000 rows a tick against roughly 71,000 new billable rows a day meant the 1.14M-row backlog would take about six weeks. Fix: a tick now walks up to MAX_BATCHES_PER_RUN (10) batches of MAX_ROWS_PER_RUN (1,000) rows, advancing the ClickHouse cursor after each batch and continuing from the exact (updated_at, id) of the last consumed row, served by the same index. Rows outside the rollout gate cost only the scan, so a group pilot moves the cursor up to 10,000 rows a tick and clears the backlog in about a day. Dispatch is budgeted at MAX_CALLS_PER_RUN (1,000) child jobs a tick, the agreed CustomersDot call ceiling; jobs the concurrency limiter still holds count against that budget (ConcurrencyLimitService.queue_size), so a slow CustomersDot shrinks dispatch rather than growing the deferred queue. The back-off latch is re-read before every batch. At global rollout the budget is spent within a batch or two, matching current behavior.

  3. CustomersDot's cost per call was set by our window, not the batch. CustomersDot can only prune by ingestion time in practice (Timestamp is the second sort-key column, behind a nanosecond-precision first column); subscription is a per-row filter, workflow id a per-row JSON parse, and an inner subquery repeats the scan. GitLab sent start_date as the batch's oldest created_at and end_date as today, so one old session meant about 97 platform-days read twice per call, the mechanism behind incident INC-13873 and today's 20 second timeouts. Fix: the ingest service derives the window from the batch itself, Workflow.credit_window_bounds returning MIN(created_at) and MAX(updated_at); end_date becomes the newest updated_at plus one day (WINDOW_END_SLACK), capped at today and never before start_date. The cron sorts each namespace's ids by created_at, never slices sessions created more than MAX_CREATED_AT_GAP (7 days) apart into one job, and enqueues each batch with one bulk_perform_async call. A batch whose sessions were all deleted no longer calls CustomersDot. Cost per call is independent of id count, so a full 100-id batch is roughly ten times cheaper per session than a small one.

This MR also raises the session credits request's read_timeout to 25 seconds (SESSION_CREDITS_READ_TIMEOUT), above CustomersDot's 20 second max_execution_time. A slow query then reaches GitLab as CustomersDot's own 500 (server_error, logged) instead of a socket timeout; only this call gets the option. The HTTP gem's header read timeout stays at its 20 second constant, so a response whose status line arrives fragmented after 20 seconds can still surface as a read timeout; both paths feed the same back-off accounting.

Done-line metadata on the cron: batches, scanned_rows, eligible_rows, dispatched_jobs, child_backlog, stop_reason (exhausted, budget, batches, backlog, or backoff), cursor_lag_seconds (seconds of window still unread at the end of the tick: 0 once the tick reached the settle horizon; a value that grows tick over tick means the walk is not keeping up).

Unchanged: child worker arguments, concurrency_limit -> { 5 }, back-off latch semantics, kill switch handling, MAX_ROWS_PER_RUN = 1000 (the runbook check still reads true), the cold-start seed on cursor 0, the whole-second cursor and its cross-tick re-read of the high-water second (now >=, so rows stamped exactly on that second are re-read too), and the 90-day floor. The console cursor rewind is still a valid backfill lever, now draining at up to 10,000 rows a tick.

Why not Gitlab::Pagination::Keyset::InOperatorOptimization: five bounded index scans merged by Postgres in one UNION ALL query is simpler than a recursive CTE with array and finder plumbing, and each branch's limit equals the outer limit, so the merge is exact because (updated_at, id) is unique.

Correctness argument for the CustomersDot window: a session's billing events carry a timestamp at or before the transition that made it billable, plus seconds of skew; day granularity plus one day of slack covers ordering and the midnight boundary. Later billing implies a later transition, which re-enters the scan with a later window end. CustomersDot adds its own 7-day ingestion bound, and the ClickHouse write is an upsert. Accepted limit, same class as the existing 90-day floor: an event more than a day after the last transition, with no further transition, under-counts until a cursor rewind. During a backlog walk this replaces the old behaviour of ending at the fetch day, and events CustomersDot ingests more than 8 days after the transition fall outside the window.

References

Load and rollout

  • Pilot (gitlab-org group only): expect roughly 10 to 50 CustomersDot calls a tick, mostly dense 100-id batches (a long-lived session gets its own job, so a batch can carry a few more), hard bound 1,000, 5 in flight, windows of a few days. The cron now walks the backlog since September 5 at master's 1,000 rows a tick, a rate this MR raises to up to 10,000 once it deploys.
  • Global rollout: at most 1,000 calls a tick, unchanged, 5 in flight, deferred queue bounded by the dispatch budget.
  • Sequencing: the cron always advances its cursor past every row it scans, whether or not it dispatches; its scan timed out every tick until !256920 (merged) reached production at 09:31 UTC on 2026-09-23, and duo_workflow_session_credits_ingestion went on for the gitlab-org group on gprd at 10:49 UTC that same day, so gitlab-org rows scanned in between were skipped. Until this MR deploys, gitlab-org calls to CustomersDot use master's window, which runs up to today, with its back-off latch, kill switch handling, and 5-in-flight concurrency limit; deploying it narrows the window with no flag change. https://gitlab.com/gitlab-org/gitlab/-/issues/630263 plans a cursor rewind (start date still open, up to 90 days back) that can recover the skipped rows and the history before September 5, draining at up to 10,000 rows a tick if it runs after this MR deploys.
  • Staging and pre have had the flag on globally since 2026-09-11. Their crons started dispatching again when !256920 (merged) deployed; this MR's deploy walks what is left under the same budget, a canary against the CustomersDot staging environment.
  • Rows already written keep working; the ClickHouse write is an upsert.

How to set up and validate locally

  1. With ClickHouse analytics enabled, set the cursor three days back and run one tick. The done metadata shows the walk:

    ClickHouse::SyncCursor.update_cursor_for(:duo_workflow_session_credits, 3.days.ago.to_i)
worker = Ai::DuoWorkflows::FetchSessionCreditsCronWorker.new
worker.perform
worker.logging_extras

    Expect keys for batches, scanned_rows, eligible_rows, dispatched_jobs, child_backlog, stop_reason, and cursor_lag_seconds. With the flag off for every group, dispatched_jobs is 0 while scanned_rows and batches advance. A second perform call scans a 15 minute window.

  2. Window derivation for a batch:

    ids = Ai::DuoWorkflows::Workflow.for_status(:finished).order(:created_at).limit(5).pluck(:id)
Ai::DuoWorkflows::Workflow.credit_window_bounds(ids)

    Expect [earliest created_at, latest updated_at]. On master the ingest service used only the first value and today as the end.

End-to-end check (local GDK against a local CustomersDot)

Ran the real chain on this commit: cron tick with child jobs executed inline, local CustomersDot serving sessionCreditsUsed from a 100,017-row usage_billing_enriched fixture, ClickHouse writes, under SaaS simulation for root group 118.

  • Rows as fixtured (updated_at before their September billing): one batch, 13 rows scanned, 4 jobs, exhausted, lag 0. The fixture workflows' call carried startDate 2026-07-31, endDate 2026-08-11, read_timeout 25; written credits were 12.5 and 3.0, the August events only, which is what the window is meant to do.
  • Realistic (updated_at after the last billing event, as in production): call window 2026-07-31 to 2026-09-18; written credits 42.0 and 10.75, identical to the v1 baseline that used today as the end date. A second tick reported exhausted with lag 0 and re-dispatched the same rows once, the documented cross-tick re-read of the cursor second (upsert-safe; in production newer rows move the cursor within a tick).
  • Other namespaces' batches carried windows of one to six days.

Database review

Read-only change, no schema change. The per-status UNION ALL scan came from !256920 (merged) and was reviewed there, production plan https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/58273/commands/162944. This MR's first batch of a tick is that same query with >= instead of > on the cursor and created_at added to the select, served by the same index condition. This MR adds the in-tick continuation predicate to each branch, shrinks each branch's limit to the remaining budget, and adds one query that derives the CustomersDot window from the batch.

Query 1 (new, continuation inside a tick): the same five branches, each continuing from the last consumed row instead of the cursor.

SELECT "duo_workflows_workflows".* FROM ((SELECT "duo_workflows_workflows"."id", "duo_workflows_workflows"."namespace_id", "duo_workflows_workflows"."project_id", "duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."created_at"
   FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."status" = 3 AND "duo_workflows_workflows"."updated_at" <= '2026-09-21 18:00:00'
   AND (("duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."id") > ('2026-09-05 12:00:00.123456', 1))
   ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
UNION ALL
(SELECT "duo_workflows_workflows"."id", "duo_workflows_workflows"."namespace_id", "duo_workflows_workflows"."project_id", "duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."created_at"
   FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."status" = 5 AND "duo_workflows_workflows"."updated_at" <= '2026-09-21 18:00:00'
   AND (("duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."id") > ('2026-09-05 12:00:00.123456', 1))
   ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
UNION ALL
(SELECT "duo_workflows_workflows"."id", "duo_workflows_workflows"."namespace_id", "duo_workflows_workflows"."project_id", "duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."created_at"
   FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."status" = 6 AND "duo_workflows_workflows"."updated_at" <= '2026-09-21 18:00:00'
   AND (("duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."id") > ('2026-09-05 12:00:00.123456', 1))
   ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
UNION ALL
(SELECT "duo_workflows_workflows"."id", "duo_workflows_workflows"."namespace_id", "duo_workflows_workflows"."project_id", "duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."created_at"
   FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."status" = 7 AND "duo_workflows_workflows"."updated_at" <= '2026-09-21 18:00:00'
   AND (("duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."id") > ('2026-09-05 12:00:00.123456', 1))
   ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
UNION ALL
(SELECT "duo_workflows_workflows"."id", "duo_workflows_workflows"."namespace_id", "duo_workflows_workflows"."project_id", "duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."created_at"
   FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."status" = 8 AND "duo_workflows_workflows"."updated_at" <= '2026-09-21 18:00:00'
   AND (("duo_workflows_workflows"."updated_at", "duo_workflows_workflows"."id") > ('2026-09-05 12:00:00.123456', 1))
   ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
) duo_workflows_workflows
ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/58451/commands/163231

Production plan: Limit over Merge Append over five Limit over Index Scan using idx_workflows_status_updated_at_id, the row comparison inside each index condition, no sort. Took 699.6 ms and 1,043 buffers (522 hit, 521 read), almost all of it (687 ms) cold I/O read time. The five branches returned 142, 15, 689, 1 and 157 rows (1,004 total) to produce the 1,000 output rows, so Merge Append stops pulling from each branch once it has enough, and none of the five reads its full 1,000-row limit.

Query 2 (new, batch window bounds, replaces a plain MIN(created_at)):

SELECT MIN("duo_workflows_workflows"."created_at"), MAX("duo_workflows_workflows"."updated_at")
FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."id" = ANY((
  SELECT array_agg(id) FROM (
    SELECT id FROM "duo_workflows_workflows"
    WHERE "status" = 3 AND "updated_at" > '2026-09-05 00:00:00'
    ORDER BY "updated_at", "id" LIMIT 100
  ) ids
)::bigint[]);

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/58451/commands/163232

Production plan: Aggregate over Index Scan using duo_workflows_workflows_pkey for the 100 ids, at 128 ms and 139 buffers (33 hit, 106 read), almost all of it (127 ms) cold I/O read time. The ids came from an InitPlan (Index Only Scan on idx_workflows_status_updated_at_id, 0 heap fetches) used only to feed 100 real ids into the plan; the shipped code passes the ids directly.

The two root-namespace plucks are unchanged from master and stay at or under 1,000 ids per call. A row has exactly one of namespace_id or project_id, enforced by a check constraint.

Not in this MR

Follow-ups

  • Update the CustomersDot runbook: backfill semantics, new throughput numbers, the seed log row, stop_reason values.
  • Attach go/no-go thresholds to corrective action https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/work_items/29726 using the numbers above.
  • Let the team that owns CustomersDot know the windows are narrower now; the call ceiling is unchanged. The 25 second read timeout assumes their SESSION_CREDITS_QUERY_TIMEOUT_SECONDS stays at 20; above about 23 the failure surfaces as a socket timeout again.
  • Pre-existing, not changed here: a batch whose root namespace has no subscription resolves as success with 0 rows and lifts a kill-switch latch, so under a kill switch the next tick can re-dispatch a budget of cheap rejected calls. Worth a follow-up.
  • Pre-existing, not changed here: a GraphQL-level error from CustomersDot (auth, validation) does not feed the back-off latch, and the child keeps Sidekiq's default 25 retries, so a systematic error of that kind keeps jobs retrying for days. Those requests are rejected before any ClickHouse query and stay under the 5-in-flight cap, so the cost is noise rather than load. Options: count the retry set in the budget, or lower the child's retries.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Edited by Andrew Jung

Merge request reports