Test Metrics in Postgres: No Vendor Needed

Most teams treat test results like a checkbox: green is good, red is bad, ship or block. The interesting signal lives in everything between those two states — runtime variance, retry counts, the same five tests appearing in every postmortem. If your current setup is "upload JUnit XML to Allure and move on," you're discarding the longitudinal data that makes pattern recognition possible.

The specific problem: vendor-hosted test analytics platforms (ReportPortal, BuildPulse, Trunk Flaky Tests) are useful, but they put your historical test corpus behind a SaaS wall, often with retention limits and no ability to join against your own deployment or incident data. Postgres — which your team almost certainly already runs — is a perfectly capable test analytics store if you model it correctly.

By the end of this article you'll have a working schema, a parser that ingests JUnit XML, and the SQL queries that power a Grafana dashboard covering flake rate, P95 duration, and failure-rate trends — all without a new vendor contract.

JUnit XML as a First-Class Data Source

JUnit XML is the lingua franca of test results. Pytest, Playwright, Selenium, k6, and virtually every other framework can emit it. The schema is shallow: a <testsuite> wraps <testcase> elements, each carrying name, classname, time, and optional <failure> or <skipped> children. That's enough to derive pass/fail, duration, and error message — but only for a single run. The analytical value comes when you stack hundreds of runs and query across them.

In a modern test architecture, JUnit XML sits at the boundary between CI execution and observability. GitHub Actions, Jenkins, CircleCI, and Buildkite all produce or consume it natively. Treating it as an event stream — each test execution is a row — lets you apply standard time-series and aggregation patterns. As noted in the broader discussion of connecting test signals to logs and traces, the test result is most useful when it's joinable with the rest of your system's telemetry, not siloed in a report renderer.

Schema, Ingestion, and the Queries That Matter

Start with a schema that captures the run context alongside the result. A test_runs table holds pipeline-level metadata; test_cases holds one row per test execution.

CREATE TABLE test_runs (
  id          BIGSERIAL PRIMARY KEY,
  pipeline    TEXT NOT NULL,          -- e.g. 'api-service / main'
  branch      TEXT,
  commit_sha  TEXT,
  triggered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE test_cases (
  id          BIGSERIAL PRIMARY KEY,
  run_id      BIGINT REFERENCES test_runs(id) ON DELETE CASCADE,
  suite       TEXT NOT NULL,
  classname   TEXT,
  test_name   TEXT NOT NULL,
  status      TEXT NOT NULL CHECK (status IN ('passed','failed','skipped','error')),
  duration_s  NUMERIC(10,4),
  message     TEXT,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ON test_cases (test_name, recorded_at);
CREATE INDEX ON test_cases (run_id, status);

The Python ingestion script below uses xml.etree.ElementTree (stdlib, no deps) and psycopg2. Wire it as a post-step in your GitHub Actions workflow after the test runner exits.

import os, xml.etree.ElementTree as ET, psycopg2

def ingest(xml_path: str, run_id: int, conn):
    tree = ET.parse(xml_path)
    rows = []
    for suite in tree.iter("testsuite"):
        suite_name = suite.attrib.get("name", "")
        for tc in suite.iter("testcase"):
            status = "passed"
            msg = None
            if tc.find("failure") is not None:
                status, msg = "failed", tc.find("failure").text
            elif tc.find("error") is not None:
                status, msg = "error", tc.find("error").text
            elif tc.find("skipped") is not None:
                status = "skipped"
            rows.append((
                run_id, suite_name,
                tc.attrib.get("classname"),
                tc.attrib.get("name"),
                status,
                float(tc.attrib.get("time", 0) or 0),
                msg[:2000] if msg else None
            ))
    with conn.cursor() as cur:
        cur.executemany(
            """INSERT INTO test_cases
               (run_id,suite,classname,test_name,status,duration_s,message)
               VALUES (%s,%s,%s,%s,%s,%s,%s)""", rows)
    conn.commit()

Once you have a few weeks of data, the queries that actually drive decisions are flake rate and P95 duration per test. Flake rate is the fraction of runs where a test both passed and failed within the same rolling window — a pass/fail ratio alone won't surface this.

-- Flake rate: tests that both passed and failed in the last 14 days
SELECT
  test_name,
  COUNT(*) FILTER (WHERE status = 'failed') AS failures,
  COUNT(*) FILTER (WHERE status = 'passed') AS passes,
  ROUND(
    COUNT(*) FILTER (WHERE status = 'failed')::numeric
    / NULLIF(COUNT(*), 0) * 100, 1
  ) AS failure_pct
FROM test_cases
WHERE recorded_at > now() - interval '14 days'
GROUP BY test_name
HAVING
  COUNT(*) FILTER (WHERE status = 'failed') > 0
  AND COUNT(*) FILTER (WHERE status = 'passed') > 0
ORDER BY failure_pct DESC
LIMIT 20;

-- P95 duration per test, last 7 days
SELECT
  test_name,
  ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_s)::numeric, 3) AS p95_s,
  ROUND(AVG(duration_s)::numeric, 3) AS avg_s
FROM test_cases
WHERE status = 'passed' AND recorded_at > now() - interval '7 days'
GROUP BY test_name
ORDER BY p95_s DESC
LIMIT 20;

Point Grafana at Postgres using the built-in PostgreSQL data source (Grafana 10+). Paste the flake-rate query into a Table panel and the P95 query into a Bar Gauge. One team running ~4,000 tests per CI run found that wiring these two panels into their existing Grafana instance dropped triage time from roughly 22 minutes per failure to under 4 — the slow, intermittently-failing tests were immediately visible without digging through Allure HTML reports. For teams thinking about what to surface at the leadership level, a quality scorecard built on these same Postgres queries gives executives a durable view without a separate BI tool.

Where Self-Hosted Test Stores Break Down

Ingesting only the final XML, not retries. Most CI frameworks retry flaky tests and emit a single aggregated XML where the test shows as "passed." You lose the retry signal entirely. Fix this by capturing intermediate XML artifacts (Pytest's --junit-xml per-retry, or Buildkite's retry metadata) and writing a retry_count column. Without it, your flake rate query is blind to the most expensive flakes — the ones that waste minutes before eventually passing.

No partition or retention strategy. At 5,000 test cases per run and 50 runs per day, you're at 2.5M rows per week. Without a PARTITION BY RANGE (recorded_at) on test_cases and a cron job dropping old partitions, query performance degrades within months and nobody notices until the Grafana dashboard starts timing out. Set a retention policy before you need one — 90 days of detailed rows plus a monthly rollup table is a workable default for most teams.

Myths About JUnit Dashboards and What They Measure

"Pass/fail rate is the metric." It's a lagging indicator of problems you already know about. A suite running at 98% pass rate can still have three tests that fail on every third run, two tests that take 90 seconds each, and a skip rate that's been quietly climbing for six weeks. CI observability requires metrics beyond the binary outcome — duration percentiles, skip trends, and failure clustering by suite or owner are what drive actual fixes. Pass/fail is a status light, not a diagnostic.

"A vendor tool handles this better than Postgres." Vendor tools handle the UI faster, but they can't join your test results against your deployment table, your on-call rotation, or your feature-flag state. When you want to answer "did flake rate spike after the last Kubernetes node upgrade?" you need the data in your own warehouse. Postgres is not the right choice if you have no DBA and no existing Postgres instance — in that case, BigQuery or ClickHouse with a managed tier makes more sense. But if you already run Postgres for your application, the marginal cost of a test analytics schema is near zero, and the analytical flexibility is high.

The schema above fits in an afternoon. The ingestion script is under 40 lines. The two SQL queries will surface more actionable signal than most teams get from their current setup. Start by backfilling two weeks of JUnit XML from your CI artifact store — even that window is enough to rank your top-ten flakes. If you want to layer in AI-assisted triage on top of this data, the test-insight copilot pattern is a natural next step once the raw store is stable.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.