Rollup Table

How rollup tables work

Rollup tables transform detailed time-series data into coarser-grained summaries through windowed aggregation. For example, tick-by-tick trading data might be rolled up into 1-minute, 5-minute, and 1-hour intervals, each storing relevant aggregates like VWAP, volume, and price ranges.

Benefits and tradeoffs

Benefits

• Dramatically improved query performance for common time-based aggregations
• Reduced storage requirements for historical data
• Lower computational overhead for analytical workloads
• Better support for real-time analytics dashboards

Tradeoffs

• Additional storage space required for maintaining rollup tables
• Increased system complexity for managing multiple aggregation levels
• Potential staleness depending on rollup update frequency
• Loss of granularity compared to raw data

Implementation example

Here's an example of creating and querying a rollup table for trading data:

WITH
daily_rollup AS (
    SELECT 
        timestamp,
        symbol,
        max(price) as high,
        min(price) as low,
        first(price) as open,
        last(price) as close,
        sum(amount) as volume
    FROM trades 
    SAMPLE BY 1d
)
SELECT * FROM daily_rollup
WHERE symbol = 'AAPL'
ORDER BY timestamp DESC;

Best practices

1. Interval Selection: Choose rollup intervals based on common query patterns and business requirements

2. Storage Strategy: Implement storage tiering to manage different rollup granularities efficiently

3. Update Frequency: Balance data freshness requirements with system resources when scheduling rollup updates

4. Retention Policies: Define clear data retention policies for different rollup intervals

5. Monitoring: Track query patterns to validate rollup interval choices and adjust as needed

The effective use of rollup tables is particularly important in high-frequency trading systems and industrial monitoring applications where rapid access to historical summaries is crucial for decision-making.