Handling Time-Series Data

In this lesson, you will learn how to improve query performance and data retention for time-stamped sensor logs by applying partitioning and bucketing techniques in embedded databases like SQLite.

The Challenge of Time-Series Bloat

When dealing with sensors that report data every few seconds, your database table can grow rapidly. Querying a single massive table by timestamp becomes increasingly expensive as indices grow and disk I/O demands rise. In embedded environments, we lack the compute power to scan millions of rows just to calculate a daily average.

Partitioning by Time

Instead of one monolithic table, we can partition data by time segments. You might create separate tables for each month, such as sensor_data_2023_10 and sensor_data_2023_11. This allows you to:

1. Drop old data efficiently: To delete a month of data, you simply run DROP TABLE sensor_data_2023_09 instead of executing a DELETE statement that generates massive transaction logs.
2. Reduce index size: Smaller tables keep indices small enough to fit within your memory-limited cache, significantly speeding up range scans.

Bucketing for Aggregation

When granular second-by-second data isn't needed, use "bucketing." This means calculating an aggregate (like an average) for a set time interval and storing it in a separate summary table.

For example, if you collect temperature every 10 seconds, your raw data table stays small because you periodically roll it up:

-- Move raw 10-second data into a 1-hour "bucket"
INSERT INTO hourly_summaries (bucket_time, avg_temp)
SELECT 
    strftime('%Y-%m-%d %H:00:00', timestamp), 
    AVG(temperature)
FROM sensor_raw
WHERE timestamp < datetime('now', '-1 day')
GROUP BY strftime('%Y-%m-%d %H:00:00', timestamp);

-- Remove the raw data once it's summarized
DELETE FROM sensor_raw WHERE timestamp < datetime('now', '-1 day');

By keeping raw data only for the immediate past and moving older data into smaller, indexed buckets, you maintain high performance without exhausting the limited storage and memory resources of your device.

Key Takeaway: Efficiently manage time-series data by using temporal partitioning to prune old data via table drops and bucketing to consolidate historical information into high-density aggregates.