Aggregate GridDB time-series data into hourly intervals using SQL

by

Summary

During a recent load testing phase for our IoT telemetry pipeline, we identified a critical performance and logic regression. A query designed to aggregate energy consumption into hourly buckets was instead returning unaggregated high-cardinality rows. Because the query grouped by the raw TIMESTAMP rather than a truncated time bucket, the database was performing a “group by” on unique millisecond-level values, effectively defeating the purpose of the aggregation and causing an OOM (Out of Memory) error on the application layer when processing the massive result set.

Root Cause

The failure stems from a misunderstanding of how GROUP BY interacts with high-precision temporal data.

  • Granularity Mismatch: In time-series databases, a TIMESTAMP column contains specific points in time (e.g., 10:05:00, 10:25:00).
  • Identity Grouping: When you GROUP BY ts, the database treats every unique second or millisecond as a distinct group.
  • Lack of Truncation: The query failed to apply a temporal transformation to strip the minutes and seconds, leaving the “bucket” too small to catch multiple data points.

Why This Happens in Real Systems

In production environments, this is a common trap when migrating from batch processing to real-time stream aggregation.

  • Cardinality Explosion: As more sensors are added, the number of unique timestamps grows linearly. Grouping by raw timestamps turns an aggregation task into a near-identity operation.
  • Schema Evolution: Developers often write queries against small sample sets where timestamps happen to align, only to have the query fail when faced with real-world jitter (where sensors report at irregular intervals like 10:01:02 and 10:01:05).
  • Database Specificity: Unlike standard SQL, time-series optimized engines like GridDB require explicit function calls to handle temporal windows efficiently.

Real-World Impact

  • Memory Exhaustion: Instead of receiving 24 rows (one per hour), the application receives 86,400 rows (one per second), leading to Heap Exhaustion.
  • Increased Latency: The database engine spends excessive CPU cycles building hash tables for millions of unique timestamp keys instead of a few hundred hourly buckets.
  • Data Inaccuracy: Business intelligence dashboards display “jagged” data that looks like noise rather than the smooth trends required for energy forecasting.

Example or Code

To fix this in GridDB, you must use the TRUNC or equivalent temporal function to normalize the timestamp into an hourly bucket before grouping.

SELECT 
    meter_id, 
    TRUNC(ts, 'HOUR') AS hour_bucket, 
    AVG(consumption) AS avg_consumption
FROM 
    energy_usage
GROUP BY 
    meter_id, 
    TRUNC(ts, 'HOUR');

How Senior Engineers Fix It

Senior engineers approach this by focusing on data density and computational complexity:

  • Bucket Normalization: Always apply a truncation function (like DATE_TRUNC or TRUNC) to the temporal dimension to create discrete buckets.
  • Predicate Pushdown: Ensure that filtering (e.g., WHERE ts > ...) happens before the grouping to minimize the working set.
  • Aggregation Pre-calculation: In high-scale systems, we avoid calculating hourly averages on the fly. We implement Materialized Views or Rollup Tables that pre-aggregate data at ingestion time.
  • Testing with Jitter: We write unit tests using “dirty” data (timestamps that are not perfectly aligned to the hour) to ensure the grouping logic is robust.

Why Juniors Miss It

  • The “Exact Match” Fallacy: Juniors often assume that because a column is a “time” column, the database inherently understands the “concept” of an hour.
  • Small Dataset Bias: They often test queries against 10-20 rows of manual SQL inserts where data is “clean,” failing to realize that real-world sensor drift breaks naive grouping.
  • Focus on Syntax over Set Theory: They focus on making the query “run” (syntax) rather than analyzing the cardinality of the result set (logic).

Leave a Comment