Pre-computed results: when to cache aggregates

An aggregate is a number computed from many rows: the count of open orders, the total value of stock at a site, the average delivery time this month. The database has to read every contributing row to produce it, and the result is a single value that fits in a few bytes. That asymmetry, expensive to compute and cheap to store, is the whole argument for caching aggregates. The question is when the argument holds.

The cost shape of an aggregate

A lookup of one row by its identifier costs about the same whether the table has a thousand rows or a hundred million; the index (the database's own lookup table pointing straight to the row) does the work. An aggregate scales with the number of rows it covers. A count over a table of fifty million rows means the database reads every one of them, or at best every entry in an index, and it takes as long every time it runs.

Dashboards, list pages that show a page of twenty out of fifty thousand, and reports all run aggregates, and they run them repeatedly. The database recomputes the same total for each viewer and each refresh. Storing the result once and reading it from memory turns that full read into a single lookup.

When to keep an aggregate in memory

The test is the ratio of reads to changes, weighed against how much the reader cares about the last few seconds.

• The aggregate is read far more often than the rows underneath it change. A total row count on a catalog table that changes a few times an hour is read on every list page; keep it.
• The computation is heavy. A sum across millions of rows with a filter and a grouping is worth caching even if it is only read every minute.
• The reader tolerates a value that is a moment old. Almost every dashboard, list page and report does.

redfly exposes GetTotalRowCount alongside Get and GetRows, and it is a good example of the first case. The count is kept in Redis (a memory store that answers in about a millisecond) and served from there, so a paged list does not run a count query per page.

When to compute on demand

• The aggregate depends on the caller. A total filtered by an arbitrary date range, a free-text search or a per-user permission set has too many distinct answers to store; each combination would be its own entry, and most would be read once.
• The rows underneath change more often than the aggregate is read. A running total that moves on every write and is read once an hour is cheaper to compute when asked.
• The reader needs the value as of this transaction. A balance check before a debit, or a stock check at the moment of sale, belongs in the database transaction (the set of changes applied together or not at all), not in a cache.

Cache the answers that many people ask for; compute the answers only one person will ever ask.

How a short lag changes the trade-off

The traditional way to cache an aggregate is a timer: recompute every five minutes and serve the stored value in between. That forces a choice between a stale number and a busy database, and the number is oldest exactly when the reader looks.

A sync service watching the database changes that. On SQL Server it reads change tracking on a short interval; on PostgreSQL and MongoDB it streams changes through logical replication and change streams. Changed rows reach Redis shortly after, and the pre-computed results that depend on them are refreshed because the data changed, not because a clock ran out. The stored total is a short lag behind the database, with no timer to tune and no recompute on a schedule.

That moves the line. Aggregates that changed too often to cache on a five-minute timer become reasonable to cache when the lag is a moment rather than minutes. The cases left for on-demand computation are the per-caller ones and the transactional ones, and those were never good cache candidates anyway.

Pitfalls

The first is caching an aggregate whose inputs are not all in the synced tables. If a total depends on a table the sync service is not watching, it will drift and nobody will be told. Every input table has to be in the sync.

The second is treating a cached count as a promise the database enforces. A page that says fifty items remain is a page, not a promise; the order insert is where the database enforces the truth.

The third is the fallback path. If Redis is unreachable, reads fall back to the database automatically, and the aggregate that was a millisecond lookup becomes a full read of the table again. The database has to be able to survive that, which means the cache should reduce its load, not be the sole reason it is still standing.

The fourth is the shape of the aggregate. A pre-computed result that groups by every dimension a reader might want is a table in itself. Keep the stored aggregates to the ones actually rendered, and let the rare combinations run on demand.

Where redfly fits

redfly keeps the rows and the row counts the application reads in Redis, kept in step with the customer's own SQL Server, MongoDB or PostgreSQL database by a sync service matched to the engine, and serves them through one API with fallback to the database built in.