How to Precompute Aggregates for a Fast, Scalable API
For data pipeline engineers · Based on Deming Live-Build Full Stack Iteration Method
// TL;DR
The Deming Live-Build Method gives data pipeline engineers a clean pattern for serving fast APIs backed by heavy batch computation. Instead of computing expensive aggregates on every request, you calculate them once during the nightly batch job and store the result as a column, so the API just reads the cached value. Updates are incremental — you increment counters only for rows active in the current run rather than recomputing all history. Use it when your pipeline pulls from a third-party source, stores processed stats in PostgreSQL, and must serve a low-latency dashboard.
Why should aggregates live in the batch job, not the API?
Computing expensive aggregates like appearance counts or position statistics on every API call slows every request and collapses under load. The Deming Live-Build Method's precompute, serve fast principle moves that work into the nightly batch: run the heavy calculation once, store the result as a column, and let the API do a simple SELECT at request time. This is a legitimate, well-established pattern — precompute during the pipeline, serve instantly at request time.
How do you make aggregate updates scale forever?
Use incremental update over full recompute. A full-table recompute recalculates the aggregate from all historical data on every run, which is O(N × history) and degrades as your dataset grows. Instead, increment counters only for rows active in the current run. First fetch the latest run with `SELECT MAX(run_id) FROM leader_snaps`, then run:
```sql
UPDATE leaders
SET appearances = appearances + 1
WHERE address IN (
SELECT DISTINCT address FROM leader_snaps WHERE run_id = [max_run_id]
);
```
This is O(N new rows) and scales cleanly no matter how much history accumulates.
How do you keep the pipeline safe from broken connection states?
Wrap every write sequence — insert then update, or save then increment — in a single transaction with rollback on exception. The pattern is: try → save each entity → if the count flag is True, run the increment → commit → log success. Except Exception: rollback → raise. Finally: close cursor. A failed query that's never rolled back leaves the connection broken and causes every subsequent request to fail with 'current transaction aborted, commands ignored until end of transaction block.' Rollback discipline is what keeps a nightly batch failure from cascading.
How do you reuse one pipeline across multiple data paths?
Add a count_appearances flag to your build pipeline function. When True, the incremental aggregate update runs after saving the snapshot; when False, it's skipped. This lets you reuse the same save logic for non-leaderboard data paths without triggering the increment — one pipeline function, multiple contexts, no duplicated logic.
Why compute your own metrics instead of trusting the source API?
Third-party data can be misleading. A one-trade 100% win rate or large unrealized losses hidden in open positions can make poor performers look elite. Tracking how many times an entity appears in your own nightly sweeps — the appearances metric — gives you a proprietary signal you can't get from the external API, and it's exactly the kind of aggregate that belongs in the batch job.
How do you verify the aggregate populated correctly?
After the pipeline completes, query a known record directly: `SELECT appearances FROM leaders WHERE address = '[known_address]'`. Confirm the count matches the number of runs that entity should have appeared in. Do this before exposing the field in the API, because a silent aggregate bug in the batch is far harder to catch once it's serving a dashboard.
Next step: Audit your pipeline for any aggregate computed at request time, move it into the nightly batch as a stored column, and convert any full recompute into an incremental run_id-scoped update.
// FREQUENTLY ASKED QUESTIONS
When should I precompute an aggregate versus computing it live?
Precompute any expensive aggregate that doesn't need to be real-time — appearance counts, position statistics, rollups. Run it during the nightly batch and store it as a column so the API just does a simple SELECT. Compute live only for values that must reflect the exact current moment and are cheap to calculate.
How do I convert a full recompute into an incremental update?
Fetch the latest run with SELECT MAX(run_id), then increment the counter only for rows active in that run: UPDATE ... SET counter = counter + 1 WHERE id IN (SELECT DISTINCT id FROM snaps WHERE run_id = [max]). This changes the cost from O(N × history) to O(N new rows), so it scales cleanly as history grows.
What happens if my batch job fails mid-write?
If the write sequence isn't wrapped in a transaction with rollback, a partial failure leaves the connection in a broken state and every subsequent request fails with transaction-aborted errors. Wrap save + increment in one transaction, roll back on any exception, and close the cursor in a finally block so a failure rolls back cleanly instead of cascading.