RFC: Analysis-oriented database schema

Aperta
  • Qualcosa non va?
#1,947 3 commenti 1 reazione 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 2 giorni

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
5/5
Tempo stimato
Più di una settimana
Idoneità per principianti
25/100
Tipo di issue
Refactoring
Chiarezza
Da chiarire
Stato di attività
Tranquilla
Stack tecnologico
python
Ambito
backend, databases

Direzione di ricerca

Iniziare dall’issue correlata #1383 e dallo schema attuale di build/test della proposta, quindi esaminare come vengono usate le API legacy e le tabelle denormalizzate come tree_listing, hardware_status e i rollup. L’issue non indica file o test e lascia aperti il livello di granularità della definizione, le estrazioni JSON e il comportamento dei rollup; per considerarla completata servirebbero un design concordato, benchmark validati e un piano di migrazione incrementale.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Database

Related: #1383

Problem

The dashboard must support historical and comparative analysis, not only current-state browsing. Today, large builds and tests tables mix stable definition data with per-run execution data. Important fields also live in JSON blobs, which limits indexing and aggregation.

Proposal (direction, not final design)

Move toward a star-schema-style layout: narrow fact tables (runs) keyed to shared dimensions (commits, definitions). Early POC work suggests this can shrink hot rows and speed long-range queries.

Surface Current schema avg Proposed schema avg Faster
tree_listing_60d 2.839s 0.362s proposed, ~7.8×
tree_by_checkout_5 0.058s 0.033s proposed, ~1.8×
notification_history 13.018s 4.900s proposed, ~2.7×
hardware_tree_heads 0.192s 0.142s proposed, ~1.35×
hardware_listing 6.508s 2.254s proposed, ~2.9×
hardware_details_summary 0.026s 0.031s current, marginal
hardware_details_full 0.013s 0.017s current, marginal

Proposed schema = star-schema POC read path (build_runs / test_runs fact tables), not the current monolithic builds / tests tables.

  • It is important to point this is just a small experiment, and more analysis could bring further improvement for slower queries (some of those queries today, are pre cached to avoid slow compute).

We expect to migrate incrementally (expand → backfill → validate → cut over), not in one breaking change.

1. Split definitions from runs
  • Build/test definitions — relatively static fields (e.g. config grain, test path).
  • Build/test runs — execution facts: status, timestamps, lab/platform, mostly foreign keys.

tests in particular can grow very large; separating definition from run should keep fact tables smaller and cheaper to scan.

Open: exact definition grain. Community input welcome before we lock migrations.

2. Materialize structured fields

Promote frequently queried data out of JSON (environment_misc, large misc blobs) into typed columns or explicit relations where it measurably helps queries—especially hardware and platform listing.

Open: which extractions are worth the ingestion and storage cost; we will gate optional normalizations on query benchmarks, not assumptions.

3. Commit as a first-class dimension

Introduce a commits dimension (tree + repository + branch + hash) and link checkouts to it, so revision-scoped analysis does not depend on denormalized checkout text fields.

4. Daily rollups on runs

Pre-aggregate build and test runs by day (and existing rollup tables where they already help) to accelerate wide time-range dashboards.

Trade-off: rollups add ingestion work. If cost grows, we can reconcile periodically instead of on every write—feedback on acceptable latency vs query speed is useful.

Risks

  • Ingestion: more tables and enrichment (e.g. commit metadata) may increase per-submission cost. We will monitor ingest throughput and keep git/network work outside DB transactions.
  • Migration: legacy APIs and denormalized tables (tree_listing, hardware_status, rollups) must keep working until new paths are validated.

Feedback requested

Please comment on:

  1. Definition vs run split.
  2. Definition keys — checkout-scoped vs commit-scoped build grouping; test path + environment handling.
  3. JSON materialization — which fields we need filterable or aggregatable?
  4. Rollups — which time grains and dimensions matter for workflows? Is daily aggregation enough?

Alternative layouts are welcome. The goal is a schema that serves real analysis questions.

Lingua principale
Python
Stelle
10
Fork
32
Merge medio
3g 19h
PR unite (30g)
33

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di kernelci/dashboard

Tutte le issue di kernelci/dashboard

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.