13 Lab: Time-Series Practice
Workload Drills, Query Evidence, Retention Dry Runs, Edge Buffers, Timestamp Checks, and Review Artifacts
13.1 Start With the Evidence Packet
A useful lab should leave behind something a release reviewer could trust: workload assumptions, sample data, query evidence, retention proof, and rollback notes. If the exercise only produces a working demo, it misses the real lesson. Practice the artifacts that make a storage change explainable.
13.2 In 60 Seconds
Practice work should prove the same things a production review must prove: the workload contract, ingest behavior, schema fit, query path, retention policy, edge-buffer behavior, timestamp handling, and rollback limits. A lab that only inserts a few sample rows does not teach the real time-series decisions.
This chapter gives you evidence-first drills. You will size a workload, inspect schema choices, review query plans, run a retention dry run, design an edge buffer, and build a release packet. The point is not to memorize one database command. The point is to practice the review habits that keep telemetry systems understandable when data volume, late arrivals, and retention rules change.
Learning Objectives
After this chapter, you should be able to:
- Turn an IoT sensor profile into ingest, storage, retention, and query evidence.
- Review whether a schema supports the actual dashboard and investigation questions.
- Use query plans and representative queries to decide whether raw scans, rollups, or indexes are needed.
- Design a retention dry run that proves what would be deleted before any data is removed.
- Explain how edge buffers and downsampling protect cloud ingest without hiding incident evidence.
- Assemble a release packet for a time-series storage change.
13.3 Practice Like a Release Review
Each lab in this chapter follows the same loop: define the contract, run the smallest useful test, capture evidence, then decide what should change. Do not skip the evidence step. The value of the lab is the artifact it leaves behind.
13.4 Lab 1: Workload Contract Drill
Start with a compact but realistic sensor profile. The numbers are not a benchmark. They are a worksheet that forces assumptions into the open.
13.4.1 Worksheet
For each signal, answer:
- What is the primary time column: observed time, received time, or ingest time?
- Which dimensions are stable enough to filter by: site, floor, room, device class, firmware channel, or quality state?
- Which values are measurements: temperature, counter value, voltage, queue depth, or error count?
- What is the freshness target for the dashboard?
- What evidence is needed before raw data can be deleted?
Save a one-page workload contract that includes sample records, timestamp source, unit policy, dimension list, expected query families, late-arrival handling, and retention owner.
13.5 Lab 2: Schema Review Drill
Practice with two records that represent the same physical reading. The first record is convenient but weak. The second record is easier to review, query, and migrate.
Run it: Once you can see why the second record reviews better, open the optimization game below to price those schema choices. Add or drop dimensions, choose which fields become indexes or tags, and pick a rollup strategy, then watch the storage footprint and query speed move together. Use it to check that the audit fields and named dimensions you added stay affordable, and record the storage-versus-query trade-off your schema commits to.
{
"time": "2026-05-23T10:00:00",
"sensor": "a17",
"value": 22.4
}{
"observed_at": "2026-05-23T10:00:00Z",
"received_at": "2026-05-23T10:00:02Z",
"device_id": "a17",
"site_id": "building-1",
"zone_id": "floor-3-east",
"metric_name": "room_temperature",
"value": 22.4,
"unit": "celsius",
"quality_status": "valid",
"schema_version": 2
}Ambiguous time
time does not say whether it is observed, received, or ingested time, and it does not prove UTC handling.
Unclear metric
value has no metric name, unit, quality state, or schema version. Later rollups cannot be trusted.
Named dimensions
Stable dimensions such as site, zone, and device class can support filters without turning every payload field into an index.
Audit fields
Received time, quality status, and schema version make late data, validation failures, and migrations reviewable.
13.5.1 Drill Questions
- Which fields should be indexed, tagged, or labeled for dashboard filters?
- Which fields should remain ordinary values or payload fields?
- Which fields are required to replay an incident window later?
- What would break if the unit changed from Celsius to Fahrenheit without a schema version?
13.6 Lab 3: Query Evidence Drill
A query practice session should collect the query text and the evidence that it used the intended access path. For SQL systems, that usually means EXPLAIN or EXPLAIN ANALYZE. For metrics systems, it may mean rule evaluation, label-cardinality review, and dashboard query inspection.
Run it: Before you trust that a query is optimized, reproduce its plan in the query analyzer below. Set the query shape, then toggle the index, rollup, and cache choices and watch the plan cost, scanned rows, and access path change. Use it to confirm the plan prunes by time and dimension instead of scanning all history, and copy the predicate fit, plan shape, and rollup-use evidence into the drill table below.
-- Latest room temperature values for one zone.
SELECT DISTINCT ON (device_id)
device_id,
observed_at,
value,
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'
ORDER BY device_id, observed_at DESC;-- Hourly trend. TimescaleDB users may use time_bucket for this pattern.
SELECT
time_bucket('1 hour', observed_at) AS bucket_start,
zone_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 >= now() - interval '7 days'
AND metric_name = 'room_temperature'
AND quality_status = 'valid'
GROUP BY bucket_start, zone_id
ORDER BY bucket_start DESC, zone_id;Do not declare a query optimized because it is fast on a tiny table. Use enough rows, enough dimensions, and enough time range to exercise the access path. A practice query should also run while writes are happening if the production dashboard will do that.
13.7 Lab 4: Retention Dry Run
Retention practice should not start by deleting data. It should start with a dry run that reports what would be removed, what aggregate would replace it, and whether restore still works.
13.8 Lab 5: Edge Buffer Drill
Edge buffering is useful when the device or gateway must survive network gaps, protect bandwidth, or preserve high-resolution incident windows. The practice goal is not to write a perfect embedded database. It is to prove the edge behavior with bounded memory and clear upload rules.
Run it: Before you commit the buffer capacities below, size them in the storage calculator below. Enter the sample rate, bytes per sample, retention window, and compression, and read the raw ingest, buffer, and archive volumes it produces. Use it to check that the raw ring, rollup, and incident buffers fit the gateway’s memory and that an outage’s worth of points still drains, then carry those numbers into the buffer worksheet.
raw_buffer:
capacity: 600 readings
sample_interval: 1 second
purpose: last 10 minutes at full resolution
rollup_buffer:
capacity: 1440 readings
sample_interval: 60 seconds
purpose: one day of minute summaries
incident_buffer:
capacity: event window
trigger: threshold breach or explicit operator request
purpose: preserve high-resolution evidence around unusual behaviorShort local replay
Keep enough full-resolution readings to debug recent behavior and retransmit after a short outage.
Normal upload
Upload minute summaries with sample count, min, max, mean, unit, and quality flags.
Exception path
Preserve raw windows around events before downsampling so rare spikes are not averaged away.
Replay test
Disconnect the network in the lab, reconnect, replay buffered data, and compare cloud results with local records.
13.8.1 Edge Buffer Formula
Use this worksheet:
raw_buffer_seconds = raw_capacity / samples_per_second
outage_coverage = raw_buffer_seconds - worst_case_upload_delay_seconds
rollup_points_per_day = 86400 / rollup_interval_seconds
incident_storage = sample_rate_hz x event_window_seconds x bytes_per_sampleCapture the buffer capacities, sampling rules, upload schedule, retry policy, incident trigger, duplicate handling, and proof that replay preserves ordering.
13.9 Outage Replay Evidence
Store-and-forward only counts as practice evidence when the replay path is bounded, idempotent, and measured. A gateway should buffer points locally while offline, forward them in capped batches when the link returns, and apply backpressure so replay does not overload the cloud sink.
Use numbers that would fit on a gateway. A controller with 32 one-second metrics creates 115,200 raw points during a one-hour outage. At 40 bytes per compact local record before storage overhead, that is about 4.6 MB; at 200 bytes per JSON record, it is about 23 MB. The lab decision changes depending on that representation. A ten-minute raw ring buffer for the same controller is only 19,200 points, but it may be exactly what an incident replay needs.
Replay also needs an identity rule. A point keyed by series and observed timestamp should overwrite the same slot when re-sent, so an at-least-once retry after a flaky connection does not double-count. In line-protocol stores such as InfluxDB, an identical measurement, tag set, field, and timestamp is a last-write-wins update rather than a new logical sample; SQL schemas can get the same behavior with a stable uniqueness key and upsert policy.
If the cloud sink can safely accept 10,000 replayed points per second and the gateway has 600,000 queued points after an outage, the fastest drain time is 60 seconds before live traffic is included. If live traffic continues at 2,000 points per second, the replay budget is only 8,000 points per second and the drain time rises to 75 seconds. Put that calculation in the release packet with the maximum retry batch and the full-buffer policy.
13.10 Code Challenge: Edge Buffer Admission
13.11 Lab 6: Timestamp and Late-Data Drill
Timestamp practice should separate when the device observed a reading from when the platform received and stored it. The drill is to admit records with clear flags rather than silently rewriting time.
reject_reason = observed_at_in_futurequality_status = late_validdedupe_key and duplicate_policytimestamp_source = received_at_fallbackFor oscillator drift worksheets, the stable formula is:
drift_ms_per_second = oscillator_ppm / 1000
maximum_seconds_between_sync = available_drift_budget_ms / drift_ms_per_secondThe result is a design input, not a guarantee. Network timing error, connectivity gaps, device sleep behavior, and local clock correction policy still need to be tested on the actual device class.
13.12 Late-Data Backfill Drill
Late data is the part of ingestion that most often separates a syntax demo from a production-like practice run. Buffered edges and lossy links mean points can arrive after their time window has already been rolled up. The lab should set an allowed-lateness watermark for the fast path and a separate backfill path for anything older.
A small test is enough. Generate one hour of one-second temperature readings for 20 devices, which produces 72,000 expected raw points. Hold back 300 readings from the 10:00 to 11:00 bucket and deliver them 20 minutes late. If the allowed lateness is 10 minutes, those 300 readings must follow the backfill path, and the hourly aggregate or TimescaleDB continuous aggregate for that bucket must refresh its count, average, minimum, maximum, and gap flag. The test passes only if the raw table, aggregate, and dashboard agree after refresh.
The same evidence should distinguish observed time from received or ingest time. Rewriting the late readings to “now” makes ingestion easy but destroys incident reconstruction. Keeping both observed_at and received_at lets reviewers answer two different questions: what happened in the physical system, and how far behind the platform was when it received the data.
13.13 Label the Practice Evidence
13.14 Release Packet Checklist
The final artifact for this chapter is a release packet. It should be short enough to review and complete enough to repeat.
Workload evidence
Sample records, timestamp sources, unit policy, schema version, quality states, and data owner.
Read-path evidence
Representative query text, plans, scanned rows or chunks, dashboard screenshots, and freshness targets.
Retention evidence
Dry-run output, rollup completeness, archive manifest, restore sample, deletion owner, and exception process.
Buffer evidence
Buffer size, outage coverage, replay order, duplicate handling, incident window preservation, and upload proof.
Operational evidence
Rejected writes, late arrivals, clock drift, full disk, slow query, missed rollup, restore failure, and rollback test.
Decision evidence
Accepted trade-offs, approval owner, migration path, rollback limit, and next review date.
13.15 Common Pitfalls
13.15.1 Practicing on Toy Data Only
Ten rows can validate syntax, but they cannot validate chunk pruning, index usefulness, rollup freshness, late data, or retention behavior. Use small enough data to understand and large enough data to expose the access path.
13.15.2 Treating Calculators as Evidence
Capacity worksheets are useful for estimates, but they are not evidence until they are compared with actual sample records, storage output, query plans, and retention dry-run results.
13.15.3 Deleting Before Restoring
The retention policy is not ready until a restore sample has been performed and compared with the original query result. An archive that has never been restored is only a hope.
13.15.4 Hiding Quality in Averages
A mean without sample count, min, max, quality status, and gap markers can hide missing readings and spikes. Rollups should preserve enough context to support investigations.
13.15.5 Mixing Monitoring and Business History
Gateway CPU, queue depth, and rejected writes are monitoring evidence. Device readings and customer analytics usually need a separate telemetry store or archive path.
13.16 Self-Assessment
13.17 Summary
Time-series practice should mirror production review. The useful lab output is not only a table, query, or program. It is the evidence that proves the workload contract, schema, query path, lifecycle path, edge behavior, timestamp handling, and release ownership.
The strongest practice habits are simple: write down the contract, use representative data, capture query evidence, dry-run retention, test restore, preserve incident windows, and package the decision with owners and rollback limits. Those habits prevent quality drift when the system grows.
13.18 Concept Relationships
- Time-Series Database Fundamentals explains the storage mechanics that these labs exercise.
- Time-Series Database Platforms compares platform roles before you run platform-specific practice.
- Time-Series Query Optimization goes deeper into query plans, rollups, and dashboard access paths.
- Data Retention and Downsampling expands the lifecycle and deletion checks used in the dry-run lab.
- Data Storage Worked Examples provides additional role-based storage review scenarios.
13.19 What’s Next
| If you need to… | Read next |
|---|---|
| Tune dashboard and investigation queries | Time-Series Query Optimization |
| Design retention, rollups, archive, and deletion | Data Retention and Downsampling |
| Process readings before they enter storage | Stream Processing |
| Detect unusual telemetry patterns | Anomaly Detection |
| Review more storage design scenarios | Data Storage Worked Examples |
13.20 Official References
- PostgreSQL EXPLAIN - query plan inspection and plan interpretation.
- Tiger Data time_bucket - time-bucket aggregation for TimescaleDB workloads.
- Tiger Data continuous aggregates - incremental rollups and refresh behavior.
- Prometheus querying basics - metric selectors, labels, and query fundamentals.
- InfluxDB schema design recommendations - tags, fields, timestamps, and schema guidance.
13.21 Key Takeaway
Time-series practice should test modeling and queries together. A schema that is easy to write but impossible to aggregate by device, location, or time window will fail in operations.