Partitioning for Time-Series Data

In this lesson, you will learn how to design time-bucketed sharding schemas to handle high-velocity ingestion while enabling efficient, automated data retention policies.

The Time-Bucket Strategy

In traditional sharding, we might use a user_id to distribute data. However, in time-series workloads—like sensor logs or financial ticks—the most common access pattern is "get the latest data" or "delete data older than 90 days."

Instead of a single massive table, we use Time-Bucketing. We partition data into physical shards based on time intervals (e.g., daily_logs_2023_10_27). This transforms a massive, ever-growing dataset into a collection of immutable, manageable chunks.

Optimization for Ingestion and Retention

Heavy ingestion creates hotspots (as discussed in Lesson 10). If all writes target the "current" time bucket, that specific shard experiences all the load. To mitigate this, we combine time-bucketing with Hash-Based Sharding for the current window.

-- Conceptual schema for a time-bucketed shard
CREATE TABLE sensor_data_2023_10_27 (
    shard_id INT, -- Hash of device_id to spread writes within the bucket
    timestamp TIMESTAMP,
    value FLOAT,
    PRIMARY KEY (shard_id, timestamp)
);

Retention-Based Deletion

The beauty of time-bucketing lies in Drop Partitioning. When data exceeds your retention policy (e.g., 30 days), you don't need to issue expensive DELETE FROM table WHERE timestamp < ... commands, which would generate massive undo logs and fragment your indexes. Instead, you simply DROP TABLE or DETACH PARTITION on the old bucket. This is an $O(1)$ metadata operation that instantly recovers disk space.

Designing the Schema

1. Choose a Bucket Granularity: Match your bucket size to your retention policy and query window. If you query by day, use daily buckets.
2. Handle the "Active" Shard: Ensure your routing proxy directs incoming writes to the current bucket while allowing the proxy to perform a Scatter-Gather (Lesson 13) across multiple buckets if a query spans a time range.
3. Immutability: Once a time bucket is "closed" (the time window has passed), treat it as read-only. This simplifies replication and allows for heavy compression, as the data will never be updated.

Key Takeaway: By aligning your sharding architecture with time-based intervals, you transform costly deletion tasks into metadata operations while naturally grouping hot, active data away from cold, historical data.