11 Time-Series Query Optimization
Predicate Design, Query Plans, Latest Values, Window Scans, Rollups, Counters, Gaps, and Dashboard Evidence
11.1 Start With the Question on the Screen
Query tuning starts with the sentence a user is trying to answer. “Show the latest room temperature for this floor” is a different storage problem from “replay the last outage at raw resolution.” Once the question names the time range, entity, metric, and quality rule, the query plan can be reviewed instead of guessed.
11.2 In 60 Seconds
Time-series query optimization is not a bag of product tricks. It is a review habit: name the question, bound the time range, filter by stable dimensions, choose the right data tier, prove the query plan, and preserve enough quality evidence for the result to be trusted.
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. A counter rate should not be treated like a gauge. The right query is the one that answers the product question with visible evidence.
Learning Objectives
After this chapter, you should 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.
- Preserve sample counts, min/max values, freshness timestamps, and gap flags so rollups remain reviewable.
- Build a query-review record that can be checked during release and incident investigations.
11.3 Query Optimization Starts With the Question
A time-series database can only optimize the query you actually send. Before touching indexes or dashboards, write the request in plain language:
For each device in building-1 / floor-3-east,
show the latest valid room_temperature value
observed in the last 2 hours,
and mark any stale device separately.That sentence already contains the shape of the access path:
“Show all temperature data” is not a dashboard query. It is an export or investigation request that needs paging, archive controls, and a clear time range. Every normal dashboard, alert, and report query should have a bounded time window or a documented reason for using a precomputed latest-state table.
11.4 Query Families
Current state
Find the newest valid reading per device, room, gateway, or metric and mark stale entities.
Bounded scan
Read raw samples for one entity, site, or metric over a specific investigation interval.
Dashboard trend
Read bucketed summaries with count, min, max, mean, and gap flags for repeat dashboards.
Counter change
Calculate increase, rate, or delta for counters without treating reset-prone counters as gauges.
Gaps and stale data
Find missing intervals, invalid samples, late arrivals, and devices whose current state is old.
Incident investigation
Recover the raw or highest-resolution interval around an event, then compare it with rollups.
11.5 Predicates That Make Queries Reviewable
The strongest query predicates come from the data contract. They usually include:
observed_at: the event time used for analysis and dashboard windows.received_at: the platform time used to inspect ingest lag and late arrivals.tenant_id,site_id,zone_id, orasset_id: stable dimensions that users actually filter by.device_id: the entity whose state, trend, or replay is being inspected.metric_name: the measured signal, such as temperature, vibration, battery voltage, or queue depth.quality_status: whether a reading is valid, late, estimated, rejected, or part of an incident window.
-- Reviewable bounded query pattern.
SELECT
observed_at,
device_id,
metric_name,
value,
unit,
quality_status
FROM readings
WHERE observed_at >= :start_time
AND observed_at < :end_time
AND site_id = :site_id
AND metric_name = :metric_name
AND quality_status = 'valid'
ORDER BY observed_at ASC, device_id ASC
LIMIT 5000;11.6 Plan Evidence
For SQL-backed time-series systems, plan evidence is usually the fastest way to catch a false optimization. PostgreSQL EXPLAIN exposes the plan tree, scan nodes, estimates, filters, sort operations, and index use. In a time-series store built on PostgreSQL, the same habit helps reviewers confirm that time and dimension predicates are being used as intended.
EXPLAIN (ANALYZE, BUFFERS)
SELECT
time_bucket('15 minutes', observed_at) AS bucket_start,
device_id,
avg(value) AS avg_value,
min(value) AS min_value,
max(value) AS max_value,
count(*) AS sample_count
FROM readings
WHERE observed_at >= TIMESTAMPTZ '2026-05-23 00:00:00+00'
AND observed_at < TIMESTAMPTZ '2026-05-24 00:00:00+00'
AND site_id = 'building-1'
AND metric_name = 'room_temperature'
AND quality_status = 'valid'
GROUP BY bucket_start, device_id
ORDER BY bucket_start, device_id;Reviewers should record the plan evidence in a compact table:
A query that looks fine on a tiny development table may fail after realistic devices, dimensions, late arrivals, and retention tiers are present. Capture plans on a dataset that resembles the production access path, preferably while normal ingest is running.
11.7 Latest-Value Queries
Latest-value queries power current-state dashboards, status lists, maps, and device detail panels. They are often deceptively expensive because the UI asks for “one row per device” while the database may need to search a large recent window.
-- Latest valid temperature per device in one zone.
SELECT DISTINCT ON (device_id)
device_id,
observed_at,
value,
unit,
quality_status
FROM readings
WHERE observed_at >= now() - interval '2 hours'
AND site_id = 'building-1'
AND zone_id = 'floor-3-east'
AND metric_name = 'room_temperature'
AND quality_status = 'valid'
ORDER BY device_id, observed_at DESC;Define stale
A current-state query should say how old a value may be before it is displayed as stale or missing.
Entity then time
Latest-per-entity queries often benefit from an access path aligned to entity filters and descending time.
Latest-state table
When the same latest view is hit constantly, maintain a tested latest-state table or cache with freshness metadata.
Silent old values
Do not show the last known value as current unless the UI also exposes its timestamp and stale status.
11.8 Bounded Window Queries
Window queries are used for device detail charts, diagnostics, calibration checks, and incident triage. They should have a start time, end time, entity or dimension filters, and a result-size budget.
-- Raw samples for one device around an investigation window.
SELECT
observed_at,
received_at,
value,
unit,
quality_status
FROM readings
WHERE observed_at >= TIMESTAMPTZ '2026-05-23 10:00:00+00'
AND observed_at < TIMESTAMPTZ '2026-05-23 10:30:00+00'
AND device_id = 'sensor-a17'
AND metric_name = 'motor_vibration_rms'
ORDER BY observed_at ASC;For APIs, prefer seek pagination over growing offsets. The cursor should continue from the last (observed_at, device_id, sequence_id) tuple rather than asking the database to skip an increasing number of rows.
-- Seek-style next page.
SELECT observed_at, device_id, sequence_id, value, quality_status
FROM readings
WHERE observed_at >= :start_time
AND observed_at < :end_time
AND site_id = :site_id
AND (observed_at, device_id, sequence_id) > (:last_time, :last_device, :last_sequence)
ORDER BY observed_at, device_id, sequence_id
LIMIT 1000;11.9 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.
-- Hourly dashboard trend with quality evidence.
SELECT
bucket_start,
zone_id,
avg_value,
min_value,
max_value,
sample_count,
gap_count,
refreshed_at
FROM room_temperature_hourly
WHERE bucket_start >= now() - interval '30 days'
AND bucket_start < now()
AND site_id = 'building-1'
ORDER BY bucket_start ASC, zone_id ASC;TimescaleDB users often use time_bucket for bucketed aggregation and continuous aggregates for repeated summaries. The durable lesson is broader than one product: a rollup must document bucket size, source query, refresh policy, late-arrival behavior, and quality fields.
11.10 Rollup Refresh and Invalidation Rules
A rollup is not only a smaller table. It is a promise that a dashboard can read fewer rows without losing the evidence needed to explain the result. 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. A fleet with 10,000 devices sampled once per minute creates 432,000,000 raw rows in 30 days for one metric. 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. That is small enough for repeated dashboard refreshes, provided the query is deliberately routed to the rollup tier and the UI labels the granularity honestly.
The hidden contract is refresh scope. 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. Late readings from last month may require an explicit backfill job. Prometheus recording rules and InfluxDB tasks have the same ownership problem: they must name the source range, destination series or bucket, late-arrival behavior, and rerun policy.
11.11 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.
rate(iot_gateway_messages_total{site="plant-a",gateway="gw-17"}[5m])Prometheus uses range selectors such as [5m] and functions such as rate() for counter changes. In SQL systems, the same design issue appears when calculating deltas between adjacent counter samples.
WITH ordered AS (
SELECT
observed_at,
gateway_id,
messages_total,
lag(messages_total) OVER (
PARTITION BY gateway_id
ORDER BY observed_at
) AS previous_total,
lag(observed_at) OVER (
PARTITION BY gateway_id
ORDER BY observed_at
) AS previous_at
FROM gateway_counters
WHERE observed_at >= now() - interval '30 minutes'
AND site_id = 'plant-a'
)
SELECT
observed_at,
gateway_id,
CASE
WHEN previous_total IS NULL THEN NULL
WHEN messages_total < previous_total THEN NULL
ELSE (messages_total - previous_total)
/ EXTRACT(EPOCH FROM (observed_at - previous_at))
END AS messages_per_second
FROM ordered
ORDER BY observed_at, gateway_id;Record whether a signal is a gauge, counter, event count, cumulative energy value, or derived rate. Optimizing the query is not enough if the math treats a reset-prone counter as a regular measurement.
11.12 Gaps, Staleness, and Quality Checks
An optimized dashboard that hides missing data is not a good dashboard. Gap queries should be part of the release evidence for any telemetry path that supports monitoring, reports, or incident review.
-- Find expected 5-minute buckets with no valid sample for one device.
WITH expected AS (
SELECT generate_series(
TIMESTAMPTZ '2026-05-23 10:00:00+00',
TIMESTAMPTZ '2026-05-23 11:00:00+00',
INTERVAL '5 minutes'
) AS bucket_start
),
actual AS (
SELECT
time_bucket('5 minutes', observed_at) AS bucket_start,
count(*) AS sample_count
FROM readings
WHERE observed_at >= TIMESTAMPTZ '2026-05-23 10:00:00+00'
AND observed_at < TIMESTAMPTZ '2026-05-23 11:05:00+00'
AND device_id = 'sensor-a17'
AND metric_name = 'room_temperature'
AND quality_status = 'valid'
GROUP BY bucket_start
)
SELECT
expected.bucket_start,
coalesce(actual.sample_count, 0) AS sample_count
FROM expected
LEFT JOIN actual USING (bucket_start)
WHERE coalesce(actual.sample_count, 0) = 0
ORDER BY expected.bucket_start;No sample
The device did not report, the gateway dropped data, or the query filtered the sample out.
Arrived after rollup
Late readings may require refreshing affected buckets and marking the aggregate as revised.
Old latest value
The latest value exists but is older than the freshness target for the UI or alert.
Filled value
Interpolation or last-value carry-forward should be visible to users and excluded from some analyses.
11.13 Incident Replay Queries
Incident replay is the exception path that proves the telemetry system can still explain what happened. It should retrieve the highest-resolution data available around an event, include quality metadata, and compare the raw view with any dashboard rollup that users saw.
incident_window:
event_id: overheat-2026-05-23-plant-a-line-2
raw_start: 2026-05-23T09:58:00Z
raw_end: 2026-05-23T10:12:00Z
entities: motor-17, controller-17, gateway-4
required_fields: observed_at, received_at, metric_name, value, unit, quality_status
compare_with: five_minute_dashboard_rollupThe replay query is allowed to be more expensive than a dashboard query because it serves a different purpose. It still needs bounds, ownership, and a result-size expectation.
11.14 Label the Query Review
11.15 Code Challenge: Query Review Record
11.16 Query Symptom Triage
11.17 Common Pitfalls
11.17.1 Optimizing a Demo Query Instead of the Product Query
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. Review the actual query mix.
11.17.2 Using Functions on the Time Column in the Predicate
Some expressions prevent the database from using the time axis efficiently. 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.
11.17.3 Treating Rollups as Just Smaller Tables
Rollups are not only performance tools. They are evidence summaries. If they omit sample counts, min/max values, gap flags, freshness, or source-window metadata, they can make dashboards faster while making investigations weaker.
11.17.4 Letting a Cache Hide Freshness
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. A cache with no timestamp can make bad data look current.
11.17.5 Mixing Monitoring Metrics and Business Telemetry
Prometheus-style metrics are excellent for infrastructure and service health: queue depth, request duration, scrape health, dropped messages, and ingest lag. Long-lived customer telemetry, regulated history, and replayable raw readings usually need a telemetry store or archive path as well.
11.18 Release Checklist
Query family
Latest value, bounded window, rollup, rate, gap, or incident replay is named.
Bounded access path
Time range, entity, metric, dimension, and quality predicates are explicit and tested.
Right data level
The query uses raw data, a latest-state table, a rollup, a monitoring rule, or a cache intentionally.
Evidence captured
Plans, scanned chunks or partitions, index use, rows returned, and runtime budget are recorded.
No hidden gaps
Sample counts, min/max values, freshness timestamps, quality status, and gap flags are visible.
Review packet
The dashboard owner, data owner, rollback limit, query budget, and next review date are documented.
11.19 Self-Assessment
11.20 Summary
Time-series query optimization is the discipline of making the access path visible. 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. The review habit in this chapter keeps both problems visible before they reach learners, operators, or customers.
11.21 Concept Relationships
- Time-Series Database Fundamentals explains chunks, timestamp contracts, rollups, compression, and lifecycle mechanics.
- Time-Series Database Platforms compares platform roles and the query evidence each platform should provide.
- Data Retention and Downsampling expands the rollup, archive, restore, and deletion decisions that affect query tiers.
- Time-Series Practice and Labs turns query review into hands-on workload, schema, retention, and release drills.
- Anomaly Detection builds on bounded window, rate, gap, and quality queries for model and rule evidence.
11.22 What’s Next
| If you need to… | Read next |
|---|---|
| Design retention, rollups, archive, and deletion | Data Retention and Downsampling |
| Practice query plans and release evidence | Time-Series Practice and Labs |
| Revisit storage mechanics behind query paths | Time-Series Database Fundamentals |
| Choose a platform role for the query workload | Time-Series Database Platforms |
| Detect unusual telemetry behavior | Anomaly Detection |
11.23 Official References
- PostgreSQL EXPLAIN - query-plan inspection, scan nodes, estimates, filters, and index use.
- Tiger Data time_bucket - bucketed time aggregation for TimescaleDB workloads.
- Tiger Data continuous aggregates - incremental aggregate refresh and real-time aggregate behavior.
- Prometheus querying basics - instant vectors, range vectors, label matchers, and query load guidance.
- Prometheus rate function - counter-rate calculation over range vectors and reset handling.
11.24 Key Takeaway
Good time-series queries control time ranges, grouping, aggregation, and downsampling explicitly. Unbounded or poorly grouped queries become slow, expensive, and misleading at IoT scale.