Harness published a post-mortem on refactoring the SQL table at the core of its Test Intelligence Service—the backend for test selection, flakiness detection, and trend tracking in CI pipelines. Engineer Moshe Tsur documented what broke at scale, the team's fix, and the engineering principles they locked in before production rollout.
The original schema was flat and denormalized: one row per test result, account, project, pipeline, and build identifiers stored as plain text strings on every row. It worked at thousands of rows. At millions, it failed three ways.
First, 93% of row storage was duplicated scope strings. A pipeline running 10,000 tests produced 10,000 identical copies of account and project identifiers. Second, those string columns couldn't be indexed as integer foreign keys on the fly, so every filter did full string comparisons across millions of rows—roughly 10x slower than integer lookups. Third, the write path was O(N) inside the HTTP request: 50,000 test results meant 50,000 INSERT statements before the API returned. Under concurrent load, services ran out of memory and timed out.
| Failure Mode | Root Cause | Measured Impact |
|---|---|---|
| Storage duplication | Account, project & pipeline identifiers stored as plain-text strings on every row | 93% of row storage wasted — 10,000 tests → 10,000 identical scope string copies |
| Slow query filters | String columns could not be indexed as integer foreign keys; every filter did full string comparisons | ~10× slower than integer-key lookups at millions of rows |
| O(N) write path | 50,000 test results triggered 50,000 INSERT statements inside a single HTTP request | Services ran out of memory and timed out under concurrent load |
The fix was structural. Harness split the table into normalized relations, replacing repeated string identifiers with integer references. Hierarchical scope became foreign keys. Test output blobs—stdout and stderr—were separated from lightweight metadata so summary queries stopped loading unnecessary columns. Pre-computed aggregates replaced full-table scans for build-level pass/fail counts.
Three principles governed the rewrite. The API must do a fixed number of operations regardless of payload size: store input, return immediately, process asynchronously. All work proportional to data size runs in background worker pools via queue. Every component gets an explicit memory cap; process in fixed-size chunks and release between iterations.
| Principle | Rule | Mechanism |
|---|---|---|
| Fixed operations per API call | Work done by the handler must not grow with payload size | Store input → return immediately → process asynchronously |
| Proportional work in background only | All work proportional to data size runs outside the request path | Queue-based background worker pools |
| Explicit memory caps | Every component has a defined memory ceiling | Process in fixed-size chunks; release memory between iterations |
Harness stress-tested before rollout on burst scenarios the original schema never faced: 1,000 pipelines completing simultaneously, single reports carrying 100,000 test results. That discipline—break things in test labs, not production—is how they identified the original ceilings.
Denormalized schemas optimized for write simplicity accumulate hidden read costs. The 93% duplication and 10x query slowdown are actual numbers Harness hit at production scale, not theoretical curves. The string-versus-integer penalty is easy to dismiss at small tables; at millions of rows it's the dominant term in latency. An O(N) insert path stays invisible until a large report triggers OOM. Schema decisions made early tend to survive long past the point where they remain neutral.
If your write handler's latency scales with payload size, you've locked in a ceiling.