Rollup Table | QuestDB<br>New: QuestDB For AI Agents<br>New: QuestDB For AI Agents<br>Learn more<br>SUMMARY
A rollup table is a pre-aggregated data structure that stores summarized time-series data at predefined intervals. It optimizes query performance by maintaining pre-computed aggregations of high-granularity data, reducing the processing overhead for common analytical queries.
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.
Next generation time-series database<br>QuestDB is an open-source time-series database optimized for market and heavy industry data. Built from scratch in Java and C++, it offers high-throughput ingestion and fast SQL queries with time-series extensions.<br>Try live demoRead documentation
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
Next generation time-series database<br>QuestDB is an open-source time-series database optimized for market and heavy industry data. Built from scratch in Java and C++, it offers high-throughput ingestion and fast SQL queries with time-series extensions.<br>Try live demoRead documentation
Implementation example
Here's an example of creating and querying a rollup table for trading data:
WITH<br>daily_rollup AS (<br>SELECT<br>timestamp,<br>symbol,<br>max(price) as high,<br>min(price) as low,<br>first(price) as open,<br>last(price) as close,<br>sum(amount) as volume<br>FROM trades<br>SAMPLE BY 1d<br>SELECT * FROM daily_rollup<br>WHERE symbol = 'AAPL'<br>ORDER BY timestamp DESC;
Best practices
Interval Selection : Choose rollup intervals based on common query patterns and business requirements
Storage Strategy : Implement storage tiering to manage different rollup granularities efficiently
Update Frequency : Balance data freshness requirements with system resources when scheduling rollup updates
Retention Policies : Define clear data retention policies for different rollup intervals
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.