<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Label this issue](https://contributors.gitlab.com/manage-issue?action=label&projectId=278964&issueIid=607420) </details> <!--IssueSummary end--> ## Parallel Test Balancing (PTB) — Storage Schema Schema for **Parallel Test Balancing**: a duration-aware queue that distributes a job's `parallel:` test files across nodes so every node finishes at roughly the same time, and replays the same split on retry. Builds on the existing `parallel:` keyword (`CI_NODE_INDEX` / `CI_NODE_TOTAL`). ### Key decisions - **PostgreSQL only.** A claim and its durable assignment must commit atomically; `FOR UPDATE ... SKIP LOCKED` is purpose-built for concurrent work-queue claims. Redis/brokers/ClickHouse rejected (reconciliation windows, wrong retry semantics, or no transactional claim). Storage bounded by daily partition drops. - **Job groups.** Parallelization is per test type (unit / integration / system), each a separate CI job with its own `parallelize` value and independent `node_index` space. Group name comes from the (arbitrary) CI job name → interned, not an `enum`. All operations scoped by `(pipeline_id, job_group_id)`. A file usually belongs to one group but may appear in several. - **State in `node_index`** (no `status` column): `NULL` = pending, non-null = claimed by that node. No separate "done" state — rows are read-only history once claimed. - **Interned paths + group names** keep the fact-table row at ~72 B (`bigint` ids). ### Tables Two small intern tables + one partitioned fact table. ```sql CREATE TABLE test_balancing_files ( id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, path text NOT NULL UNIQUE, last_seen_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE test_balancing_job_groups ( id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name text NOT NULL UNIQUE, -- from CI job name; arbitrary string last_seen_at timestamptz NOT NULL DEFAULT now() ); -- Live SKIP LOCKED queue AND durable retry/history source. -- One row per (pipeline, group, file). Partitioned daily on pipeline_created_at. CREATE TABLE test_balancing_assignments ( pipeline_id bigint NOT NULL, -- 8B pipeline_created_at timestamptz NOT NULL, -- 8B (partition key; pipeline's start time) duration double precision, -- 8B, snapshotted at seed, slowest-first file_id bigint NOT NULL REFERENCES test_balancing_files(id), -- 8B job_group_id bigint NOT NULL REFERENCES test_balancing_job_groups(id), -- 8B node_index int -- 4B, NULL = pending, non-null = claimed ) PARTITION BY RANGE (pipeline_created_at); ``` - Both intern tables are append-only get-or-create; **ids never recycled, never truncated** so historical references stay valid. `last_seen_at` supports optional lazy GC. - `test_balancing_assignments` uses **GitLab's built-in time-based partitioning helpers** (daily partitions + retention) — no `pg_partman`. Columns ordered big→small; row is ~72 B. `node_index` stays `int` — a `smallint` would save nothing (absorbed by alignment padding). ### Indexes (per-partition, scoped by `job_group_id`) | Index | Serves | |---|---| | `(pipeline_id, job_group_id, duration DESC) WHERE node_index IS NULL` (partial) | Claim: pending rows, slowest-first | | `(pipeline_id, job_group_id, node_index)` (no `INCLUDE`) | Retry: locate a node's file set | | `test_balancing_files (path)` unique / `test_balancing_job_groups (name)` unique | Seed get-or-create | - The **partial claim index** is the key optimization: claimed rows leave it immediately, so it stays ~1 GB (not ~35 GB) and claim cost stays flat as history grows. - **No `INCLUDE`** on the retry index: retry (rare) does a plain index scan + heap fetch for ~430 rows, saving ~10 GB/30d on the largest index. - Keep index names short — Postgres truncates at 63 chars and partitioning adds per-partition suffixes. ### Operations All operations pass `pipeline_created_at` (the pipeline's start time from CI) as the partition key, pruning to one partition by equality. Using the *pipeline's* start time — not the row's insert time — is what makes retry able to locate the partition without knowing the original run's row timestamps. - **Seed** (once per job group): get-or-create group + paths, bulk-insert one pending row per file (`node_index = NULL`), stamping each row with `$pipeline_created_at`. - **Claim** (first run): atomic `SKIP LOCKED` batch scoped to `(pipeline_id, pipeline_created_at, job_group_id)`, slowest-first, claiming files until a cumulative **duration budget** is met (tapered server-side: `budget = clamp(sum(pending.duration)/(nodes*c), floor, target)`; suggested `target≈600s`, `floor≈120s`, `c≈3`). Sets `node_index`. - **Retry** (`(pipeline_id, pipeline_created_at, job_group_name, node_index)`): reads the node's claimed rows via the retry index (single-partition, index-only) and replays them; never touches the queue. Also covers crashed jobs (assignment is the durable record). ### Scale & retention - ~1,000 pipelines/day → ~22M rows/day, ~0.65B rows / 30 days (scales ~linearly; ~3× at 3,000/day). - Storage ~**71 GB / 30 days** (heap ~47 GB, retry index ~23 GB, partial claim index ~1 GB). Compute (bursty ~1.25M claims/day) is the dominant cost, not storage. - Retention: `test_balancing_assignments` 30 days via partition drops (instant, no `DELETE`, via GitLab partitioning helpers); intern tables permanent with optional lazy GC (90-day floor). ### Out of scope (deferred) Analytics sink (ClickHouse); Redis claim accelerator; maintained per-`(pipeline, group)` pending-duration counter (fallback if claim CPU is hot); rolling/EWMA duration model; BRIN / cold-partition compression.