Data Storage · Study deck
Time-Series Query Optimization
Query tuning starts with the sentence a user is trying to answer.
Data Dora is your guide for this deck.
After studying this chapter
Learning objectives
You will be able to:
- Classify IoT read requests into latest-value, window, rollup, rate, gap, and incident-replay query families.
- Write bounded SQL query patterns that expose time, entity, metric, quality, and dimension predicates.
- Use query-plan evidence to distinguish pruning, index use, filtering, sorting, and raw table scans.
- Decide when a dashboard should read raw data, a continuous aggregate, a materialized view, a recording rule, or a cache.
Major section
In 60 Seconds
The most common IoT query families are latest value, bounded window scan, dashboard rollup, rate or delta, gap and quality check, and incident replay.
- Each family has a different access path.
- A dashboard trend should not repeatedly scan raw history when a tested rollup exists.
- An incident replay should not read a coarse aggregate that hides spikes.
Major section
Query Families
The path makes the time boundary, entity filters, storage tier, execution plan, and quality checks visible before a fast response is accepted as the correct operational answer.
- Those constraints select raw data, a latest-value view, or a rollup before the engine plans the scan.
- That chain keeps a fast answer from being mistaken for the right answer.
Major section
Rollups and Dashboard Queries
Repeated dashboards should usually read the coarsest data tier that can answer the question.
- A daily executive trend and a five-minute operations panel should not share the same raw scan.
- The dashboard consumes that bounded tier for routine speed, while an investigation can follow the lineage back to raw readings.
Major section
Rollup Refresh and Invalidation Rules
A rollup is not only a smaller table.
- TimescaleDB continuous aggregates, InfluxDB downsampling tasks, and Prometheus recording rules all make that trade: compute a cheaper series or bucketed view on a schedule, then route repeated dashboards to that precomputed tier.
- The cost shift is easy to review.
- The hidden contract is refresh scope.
Major section
Rollup Refresh and Invalidation Rules (continued)
A device-level one-hour rollup for the same window has 7,200,000 rows, a 60x reduction.
- If most dashboards show site averages for 50 sites, the site-hour rollup is only 36,000 rows.
- A continuous aggregate policy might refresh buckets from three days ago up to five minutes ago, leaving the newest five minutes to raw real-time reads.
- Late readings from yesterday are corrected during the next refresh.
Major section
Rates, Deltas, and Counters
Counters need different treatment from gauges.
- A temperature gauge can go up or down naturally.
- A message counter usually increases until the service restarts or the counter resets.
- Rate queries must account for that difference.
- Prometheus uses range selectors such as [5m] and functions such as rate() for counter changes.
Major section
Common Pitfalls
Rollups are not only performance tools.
- A query that filters one device for five minutes may be easy.
- The real dashboard may filter a site, group by zone, compare device classes, read 30 days, and refresh repeatedly.
- A cache with no timestamp can make bad data look current.
Major section
Common Pitfalls (continued)
Prefer raw range predicates such as observed_at >=:start_time AND observed_at <:end_time, then bucket or format timestamps after the range has selected candidate rows.
- If they omit sample counts, min/max values, gap flags, freshness, or source-window metadata, they can make dashboards faster while making investigations weaker.
- A latest-value cache is useful only when it exposes when the cached value was observed, when it was refreshed, and how stale values are displayed.
- Prometheus-style metrics are excellent for infrastructure and service health: queue depth, request duration, scrape health, dropped messages, and ingest lag.
- Some expressions prevent the database from using the time axis efficiently.
Major section
Summary
The strongest queries start from the user question, bind the time range, filter by meaningful dimensions, use the right data tier, and preserve result quality.
- For IoT systems, query speed and trust are linked.
- A dashboard that scans too much raw history is slow.
- A dashboard that hides sample counts, min/max values, stale data, and gaps is misleading.
Deck summary
Key takeaways
The most common IoT query families are latest value, bounded window scan, dashboard rollup, rate or delta, gap and quality check, and incident replay.
- The path makes the time boundary, entity filters, storage tier, execution plan, and quality checks visible before a fast response is accepted as the correct operational answer.
- Repeated dashboards should usually read the coarsest data tier that can answer the question.
- A rollup is not only a smaller table.
- A device-level one-hour rollup for the same window has 7,200,000 rows, a 60x reduction.
Retrieval practice
Recall check 1 of 4
Data Dora says: answer from memory, then check your reasoning.
Q1A dashboard asks for an hourly temperature trend for the last 30 days. Which first review question best protects performance and result quality?
Show answer
Answer: C A repeated 30-day dashboard should normally prove its time range, rollup tier, and quality fields instead of scanning raw samples blindly.
Retrieval practice
Recall check 2 of 4
Data Dora says: answer from memory, then check your reasoning.
Q2A dashboard aggregates a month of one-second data on every load and is far too slow. Which fix preserves both speed and reviewability?
Show answer
Answer: A Precomputed rollups make repeated reads cheaper, but the refresh and invalidation rules keep late data, corrections, and raw replay honest.
Retrieval practice
Recall check 3 of 4
Data Dora says: answer from memory, then check your reasoning.
Q3Place each query-review artifact where it lives so you can tell a fast answer from a correct, bounded answer.
Show answer
Answer: A Place the bounded question, execution route, and result checks correctly so you can approve the answer rather than merely the runtime.
Q4Complete the query-review record with fields that prove the query is bounded, uses the intended tier, and preserves quality evidence.
Show answer
Answer: A A reviewable query record documents predicates, data tier, plan evidence, result quality, and ownership.
Retrieval practice
Recall check 4 of 4
Data Dora says: answer from memory, then check your reasoning.
Q5Which query review packet is strongest for a repeated IoT dashboard?
Show answer
Answer: A A repeated dashboard needs both performance evidence and result-quality evidence.
Print reference
Answers 1 of 2
Answer key.
- C · A repeated 30-day dashboard should normally prove its time range, rollup tier, and quality fields instead of scanning raw samples blindly.
- A · Precomputed rollups make repeated reads cheaper, but the refresh and invalidation rules keep late data, corrections, and raw replay honest.
- A · Place the bounded question, execution route, and result checks correctly so you can approve the answer rather than merely the runtime.
- A · A reviewable query record documents predicates, data tier, plan evidence, result quality, and ownership.
Print reference
Answers 2 of 2
Answer key.
- A · A repeated dashboard needs both performance evidence and result-quality evidence.