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.

timeseriesqueries

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.

A time-series query should expose its question, predicates, data tier, plan evidence, and result-quality checks.

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

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.
The rollup is therefore a reviewed derivative, not a replacement that silently erases evidence.

Rollup queries are trustworthy when they preserve quality fields and point back to raw data for incident replay.

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.

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.
Counters need different treatment from gauges.

Retrieval practice

Recall check 1 of 5

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?

ACan the query return every raw sample since the system started?

BCan the chart hide missing samples by connecting all points?

CDoes the query omit filters so every future dashboard can reuse it?

DDoes the query use a bounded time range and an hourly rollup?

Show answer

Answer: D 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 5

Data Dora says: answer from memory, then check your reasoning.

Q2A dashboard query asks for recent readings, but its plan scans old partitions before filtering rows. What should the reviewer investigate first?

AWhether the time predicate matches the partitioning column and timestamp type.

BWhether the dashboard applies its recent-time filter after fetching results.

CWhether the dashboard result cache is serving an older query plan that scans historical partitions.

DWhether the final sort is configured to exclude older partitions.

Show answer

Answer: A A recent window should prune old partitions; a mismatched time predicate can prevent that.

Retrieval practice

Recall check 3 of 5

Data Dora says: answer from memory, then check your reasoning.

Q3A dashboard aggregates a month of one-second data on every load and is far too slow. Which fix preserves both speed and reviewability?

ARemove the time predicate so the planner can inspect the whole table.

BRead one-second raw samples on every refresh and rely on browser caching.

CStore only averages and discard sample counts, min/max values, and freshness.

DPrecompute a rollup and define refresh/invalidation rules for late data.

Show answer

Answer: D Precomputed rollups make repeated reads cheaper, but the refresh and invalidation rules keep late data, corrections, and raw replay honest.

Retrieval practice

Recall check 4 of 5

Data Dora says: answer from memory, then check your reasoning.

Q4Complete the query-review record with fields that prove the query is bounded, uses the intended tier, and preserves quality evidence.

Apredicates: observed_at_start, observed_at_end, site_id, metric_name, quality_status

Bpredicates: none_for_reuse

Cpredicates: page_color, icon_size, chart_theme

Dpredicates: only_metric_name_without_time

Show answer

Answer: A A reviewable query record documents predicates, data tier, plan evidence, result quality, and ownership.

Q5Which query review packet is strongest for a repeated IoT dashboard?

AThe actual query text, bounded predicates, and chosen data tier.

BA screenshot that appears to load quickly on a developer laptop.

CA public benchmark from a database vendor.

DA query that omits time filters so it can be reused for every page.

Show answer

Answer: A A repeated dashboard needs both performance evidence and result-quality evidence.

Retrieval practice

Recall check 5 of 5

Data Dora says: answer from memory, then check your reasoning.

Q6A dashboard shows a smooth hourly average after a sensor gap. Which rollup fields help reviewers see the gap?

AThe hourly average with a visual gap-interpolation rule.

BCount, extrema, gap flag, freshness and source window beside average.

CLatest device identity and reporting status for the hour.

DChart line style that distinguishes measured and estimated hours while leaving sample counts and source-window freshness out of the rollup record.

Show answer

Answer: B Coverage and extrema show whether the average hides missing or unusual samples.

Print reference

Answers 1 of 2

Answer key.

D · A repeated 30-day dashboard should normally prove its time range, rollup tier, and quality fields instead of scanning raw samples blindly.
A · A recent window should prune old partitions; a mismatched time predicate can prevent that.
D · Precomputed rollups make repeated reads cheaper, but the refresh and invalidation rules keep late data, corrections, and raw replay honest.
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.
B · Coverage and extrema show whether the average hides missing or unusual samples.