Analytical Query Performance and Optimization Questions

Cache invalidation for a mostly-hourly, sometimes-manually-refreshed dashboard needs three distinct mechanisms working together: a default time-to-live (TTL) that expires stale results automatically, an explicit manual-refresh path that bypasses the TTL on request, and a way to prevent many simultaneous manual requests from all triggering the same expensive recomputation at once.

Balancing TTL and manual refresh

Set the TTL to the hourly cadence (results expire and recompute automatically on schedule), and expose a manual "Refresh" action that force-invalidates the specific cached entry a user is looking at, triggering an immediate recompute for that one entry rather than waiting for the next scheduled hour. The manual path should write the same cache key the scheduled refresh would use, so the next scheduled refresh simply finds the cache already warm and does not duplicate work.

Avoiding a thundering herd at hour boundaries

If every cached entry's TTL expires at exactly the top of the hour, every dashboard viewer's next request in that same minute triggers its own recomputation simultaneously, which is the thundering-herd failure. Two effective techniques: stagger TTL expiry with a small random jitter per cache key (so not every entry expires in the same instant), and use a "stale-while-revalidate" pattern, where an expired entry is still served immediately while a single background refresh recomputes it, so concurrent requests during the recompute window get the (slightly stale) old value instead of all blocking on, or all independently triggering, the same expensive query.

Preventing duplicate concurrent recomputation

When a cache entry expires and multiple requests arrive before the recompute finishes, only the first request should actually trigger the underlying query; subsequent requests during that window should either wait on the same in-flight computation or serve the stale value, never independently re-run the same expensive query. This is typically implemented with a short-lived lock or "computation in progress" marker keyed to the same cache key.

Trade-offs and pitfalls

Stale-while-revalidate trades a small, bounded amount of extra staleness (users see last hour's number for the few seconds the refresh takes) for a large reduction in redundant load, which is almost always the right trade for a dashboard where a few seconds of extra staleness is invisible to users. The main pitfall is forgetting the manual-refresh path needs the same duplicate-suppression logic as the scheduled one; without it, ten users clicking Refresh on the same dashboard within the same second reintroduces exactly the thundering-herd problem the scheduled path was designed to avoid.

Given events(id, user_id, amount, event_time) and a materialized daily_user_totals(date, user_id, total_amount), the incremental update needs to fold only the dates present in staging_events into the existing totals, adding new activity to matching (date, user_id) rows and inserting fresh rows for combinations that do not exist yet, all without holding a lock on the whole target table for longer than necessary.

Approach

1. Aggregate staging_events down to one row per (date, user_id) first, so the merge step only ever has to reconcile one summarized delta row per key, not many raw event rows.
2. Upsert that delta into daily_user_totals: insert a new row where the key does not exist, and add the delta's amount onto the existing total where it does.
3. For deletes (a correction that removes a previously-counted event), apply the negative of that row's amount as a delta in the same way, rather than trying to recompute the whole day from scratch.

Code (executed and verified)

-- fixture: existing totals plus a new staging batch
CREATE TABLE daily_user_totals (date DATE, user_id INT, total_amount DECIMAL(12,2), PRIMARY KEY(date, user_id));
INSERT INTO daily_user_totals VALUES (DATE '2024-01-01', 1, 100.00), (DATE '2024-01-01', 2, 50.00);

CREATE TABLE staging_events (id INT, user_id INT, amount DECIMAL(12,2), event_time TIMESTAMP);
INSERT INTO staging_events VALUES
  (100, 1, 25.00, '2024-01-01 10:00:00'),
  (101, 3, 40.00, '2024-01-01 11:00:00'),
  (102, 1, 5.00,  '2024-01-01 12:00:00');

-- 1. summarize new/changed activity to one row per (date, user_id)
CREATE TABLE staging_daily AS
SELECT CAST(event_time AS DATE) AS date, user_id, sum(amount) AS delta_amount
FROM staging_events
GROUP BY 1, 2;

-- 2. upsert the delta into the materialized target
INSERT INTO daily_user_totals (date, user_id, total_amount)
SELECT date, user_id, delta_amount FROM staging_daily
ON CONFLICT (date, user_id)
DO UPDATE SET total_amount = daily_user_totals.total_amount + EXCLUDED.total_amount;

Executed against a target table pre-seeded with (2024-01-01, user 1, 100.00) and (2024-01-01, user 2, 50.00), and a staging batch containing two new events for user 1 (25.00 and 5.00) and one new event for user 3 (40.00): the result was user 1 = 130.00 (100 + 25 + 5, correctly accumulated), user 2 = 50.00 (untouched, as expected since no new activity existed for them), and user 3 = 40.00 (a new row inserted). All three outcomes matched the expected arithmetic exactly.

Handling deletes and idempotency

For a genuine delete of a previously-processed event, apply it as a negative delta through the same staging_daily aggregation path (summing a negative amount for a deleted row) rather than a separate code path, so the merge logic only has one shape to reason about. Concretely, using the fixture above: if event id 100 (user 1, amount = 25.00) later turns out to be a duplicate that needs to be removed, a later staging batch carries a compensating row, (user_id=1, amount=-25.00, event_time='2024-01-01 13:00:00'), rather than deleting anything from staging_events. That row flows through the same step-1 GROUP BY and sum(amount), contributing -25.00 to staging_daily for (2024-01-01, user 1), and the step-2 upsert applies it exactly like any other delta: total_amount = daily_user_totals.total_amount + EXCLUDED.total_amount becomes 130.00 + (-25.00) = 105.00, which is exactly the total user 1 would have had if the erroneous 25.00 event had never been staged (the 100.00 seed plus the remaining 5.00 event). The negative amount is not produced by a special delete code path; it is simply the deleted row's own original amount, sign-flipped, staged like any other event. For idempotency (the same staging batch being replayed, for example after a job retry), the delta must be computed from events not yet applied, not from all events in the staging table unconditionally; track a processed-flag or a watermark on staging_events and only aggregate unprocessed rows, or the same delta will be double-applied on a retry.

Complexity and edge cases

The aggregation step is a single pass over the staging batch, and the upsert is bounded by the number of distinct (date, user_id) pairs touched, not the full target table, so cost scales with the size of the incoming batch rather than the table's total history. Edge cases worth naming explicitly: a staging_events batch that spans more than one date needs the GROUP BY to include date (as written above) so events do not get folded into the wrong day; and a batch containing both an insert and a same-key correction in the same run must net out correctly before the upsert, which the sum(amount) in step 1 already handles since it aggregates all of a key's staged rows together before touching the target.

Trade-offs and pitfalls

A blind INSERT ... ON CONFLICT without first deduplicating and aggregating the staging batch (skipping step 1) would apply each raw event row as its own tiny update, which is both slower (many small writes instead of one aggregated write per key) and more exposed to a partial-batch retry double-applying individual rows rather than one clean delta per key.

Orchestrating dependent rollups (a base fact feeding an hourly aggregate, which feeds daily, which feeds monthly) means treating the whole chain as a dependency graph, not a set of independently scheduled jobs, and routing each incoming dashboard query to the coarsest rollup that can still answer it correctly.

Dependency-aware refresh

Model each rollup's refresh as depending on the freshness of the level below it: hourly cannot legitimately advance past what the base fact has ingested, daily cannot advance past what hourly has completed, and so on up the chain. A scheduler that tracks each level's watermark (a marker, typically a timestamp or a monotonically increasing id, recording how much of the upstream data that level has already incorporated) can then trigger the next level's refresh automatically once its dependency's watermark advances, rather than running every level on a fixed clock and hoping the timing lines up.

Query routing

Route an incoming query to the coarsest rollup whose grain matches (or can be trivially summed to match) the requested date range and dimensions: a query for "total revenue this month" should hit the monthly rollup directly rather than summing 720 hourly rows or reading the base fact, while a query for "revenue in the last 3 hours" has to fall back to the hourly (or base) level since no coarser rollup has fine enough grain. This routing logic needs an explicit mapping from query shape to the cheapest rollup that can serve it, checked before the query runs.

Minimizing recomputation

When an upstream correction happens (a late-arriving or corrected base-fact row), only the specific hourly buckets it falls into need to be recomputed, and only the daily and monthly rollups that include those specific hours need to be redone, not the entire history. Track which downstream rollup buckets each base-fact time range feeds, so a correction's blast radius is bounded to exactly the buckets it touches.

Failure recovery

If an hourly refresh fails partway, daily and monthly should not advance on the assumption it succeeded; the dependency-watermark tracking above naturally prevents this since daily's own refresh will see that hourly's watermark did not move and will correctly wait rather than working from stale or missing hourly rows.

Trade-offs and pitfalls

The query-routing logic is the piece most likely to go stale silently: as new dashboard query patterns emerge that do not map cleanly onto an existing rollup grain, they will quietly fall back to the most expensive path (the base fact) unless someone notices and adds a routing rule or a new rollup grain. Periodically audit which grain each dashboard's queries are actually being served from, since the whole design's value depends on that routing staying correct as usage evolves.

Cutting a query's scanned bytes from 5TB to 200GB is a 25x reduction, and at $5 per TB scanned, running 100 times a day, that turns a $2,500-per-day query into a $100-per-day query, a savings of $2,400 per day, or roughly $876,000 per year at that same run rate.

The arithmetic

• Before: 5TB x $5/TB x 100 runs/day = $2,500/day
• After: 0.2TB x $5/TB x 100 runs/day = $100/day
• Daily savings: $2,500 - $100 = $2,400/day
• Annualized: $2,400 x 365 = $876,000/year

Assumptions this rests on

• The $5/TB rate and the 100-runs/day frequency are both held constant going forward; if the query's run frequency grows (more users, more automation triggering it) the savings scale up further, and if the organization negotiates a different pricing tier or moves to a flat-rate/reserved-capacity billing model instead of pure pay-per-byte, the whole calculation no longer applies in the same form.
• The 200GB post-optimization figure is itself stable, not a one-time favorable measurement on an unusually small run; verify it against a few different runs (different filter values, different days) before committing to it as the ongoing baseline, since a partition-pruning fix's savings can vary with which partitions a given run's filter actually touches.
• 365 days a year is used as a simple annualization, not accounting for the query possibly running less often on weekends or holidays, which would make the true annual figure somewhat lower than this straight-line estimate.

What could make the real savings differ

If the optimization that reduced bytes scanned also changed the query's results in some subtle way (a stricter filter that silently excludes rows it should include), the "savings" partly reflect doing less work rather than doing the same work more efficiently, which is not a real win and needs to be ruled out by comparing outputs, not just costs, before and after. Separately, if the warehouse has a minimum billing granularity or a base cost per query independent of bytes scanned, the actual per-run savings could be somewhat smaller than the pure bytes-scanned arithmetic suggests, since that fixed cost does not shrink with the optimization.

A materialized view stores the computed result of a query physically, so reading it later is just reading stored rows instead of recomputing the underlying query; a warehouse or BI-tool result cache stores the result of one specific, previously-run query verbatim and reuses it only if an identical (or equivalent) query is issued again; a report snapshot is a one-off, manually or scheduled copy of a report's output at a point in time, generally not tied to an automatic refresh mechanism at all.

Choosing for a high-traffic executive dashboard

For an hourly-aggregate dashboard queried across many different filter combinations by many users, a materialized view (or the equivalent pre-aggregated table) is usually the right primary mechanism: it is filter-agnostic (any query against the aggregated grain benefits, not just an exact repeat of a prior query), and its refresh cadence can be controlled explicitly (hourly, matching the freshness requirement) independent of when users happen to query it.

Trade-offs

On latency specifically: a materialized view and a report snapshot both give every user a fast, query-free read on every request, while a result cache only delivers that speed on a repeat hit and falls back to the full underlying query's latency the moment a filter changes. That is the main reason a result cache cannot replace the materialized view for a dashboard with many different filter combinations: it is a good complement (it costs nothing extra and helps the common case of two users loading the identical view back to back) but is not a substitute, since the moment a user changes a filter it is a cache miss and the full underlying query runs again, latency and all.

Pitfalls

The most common mistake is materializing at too fine or too coarse a grain: too fine, and you have paid the storage and refresh cost without meaningfully reducing query time over the raw table; too coarse, and a user's specific filter combination cannot be answered from the materialized rows at all and falls back to scanning the base tables anyway, silently losing the benefit for exactly the queries that most need it.