This article explores how time travel queries work in RisingWave, how to enable them, and provides real-world use cases.
📌 Official Documentation: Time Travel Queries in RisingWave
What Are Time Travel Queries?
A Time Travel Query allows you to retrieve a table’s state at a specific point in time. Instead of only accessing the latest data, you can “rewind the clock” and inspect previous records.
In RisingWave, time travel queries use the FOR SYSTEM_TIME AS OF clause:
SELECT * FROM table_name FOR SYSTEM_TIME AS OF timestamp_expression;
Where timestamp_expression can be:
A Unix timestamp (e.g.,
1721024455)A datetime string (e.g.,
'2025-03-12T14:30:00-08:00')A relative time expression (e.g.,
NOW() - INTERVAL '10' SECOND)
How to Use Time Travel Queries in RisingWave
📌 Full Setup Guide: RisingWave Time Travel Queries
Optional: Step 1 - Set the Retention Period
By default, RisingWave retains historical states for 10 minutes. To modify the retention period, adjust the time_travel_retention_ms system parameter, which specifies the duration for which RisingWave keeps past table states.
Set the retention period in milliseconds using:
ALTER SYSTEM SET time_travel_retention_ms = 3600000; -- 1 hours
This keeps historical data for 1 hours.
Step 2 - Query Historical Data
Once enabled, you can use time travel queries to inspect past states.
For example, to view the orders table as it was 10 seconds ago:
SELECT * FROM orders FOR SYSTEM_TIME AS OF NOW() - INTERVAL '10' SECOND;
Real-World Use Cases for Time Travel Queries
Time travel queries are particularly valuable in event-driven analytics, debugging, and compliance monitoring. Let’s explore how they can be applied in real-world scenarios.
1. Debugging Trade Execution Errors in Capital Markets
Scenario:
A high-frequency trading system notices an unexpected trade execution. The team wants to investigate the state of the order book at the exact moment the trade occurred.
Solution:
Using a time travel query, they can replay the order book as it was at 2:30 PM:
SELECT * FROM order_book FOR SYSTEM_TIME AS OF '2024-03-12T14:30:00-08:00';
This allows traders to verify if market conditions justified the trade execution.
2. Investigating Banking Transaction Discrepancies
Scenario:
A customer claims they had $5,000 in their account, but after a withdrawal, their balance seems incorrect.
Solution:
The bank can retrieve the account balance before the transaction:
SELECT * FROM accounts FOR SYSTEM_TIME AS OF NOW() - INTERVAL '1 MINUTE' WHERE user_id = 123;
This helps resolve disputes by providing an exact snapshot of the account state.
3. Analyzing E-Commerce Flash Sales Performance
Scenario:
An e-commerce site runs a flash sale. The marketing team wants to analyze how fast stock levels dropped.
Solution:
By querying inventory levels at different timestamps, they can track depletion trends:
SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T08:00:00' WHERE product_id = 'ABC123';
SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T09:00:00' WHERE product_id = 'ABC123';
SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T10:00:00' WHERE product_id = 'ABC123';
This allows the team to optimize pricing and restocking strategies for future sales.
4. Backtesting Trading Strategies with Historical Market Data
Scenario:
A hedge fund wants to simulate trades using past market conditions.
Solution:
Instead of relying on static CSVs, they can query historical market data in real time:
SELECT * FROM stock_prices FOR SYSTEM_TIME AS OF '2024-02-01T09:30:00-05:00';
By running multiple queries at different points in time, they can test and refine trading models.
Managing Time Travel Data Efficiently
Since time travel queries store past table states, they consume additional storage. RisingWave implements automatic storage cleanup to manage resource usage.
Key Cleanup Mechanisms:
Meta Store Cleanup:
Happens asynchronously in the background.
Removes stale metadata from meta store.
Object Store Cleanup:
Happens asynchronously in the background.
Removes stale objects from object storage.
💡 Tip:
If queries fail due to missing historical data, the requested timestamp may be outside the retention window. You can increase retention using:
ALTER SYSTEM SET time_travel_retention_ms = 86400000; -- 24 hours
However, be mindful of storage costs when extending retention periods.
Best Practices for Using Time Travel Queries
Increase time travel retention only when needed to avoid unnecessary storage overhead.
Set a retention period that balances historical access vs. storage costs.
Use absolute timestamps for reproducibility and relative timestamps for live debugging.
Monitor storage usage. Data beyond the retention period is deleted automatically. Actively monitor meta store and object store to ensure the storage usage remains within expected limits.
Conclusion
Time travel queries in RisingWave bridge the gap between real-time and historical analytics, allowing users to access past table states effortlessly. Whether for debugging, compliance, or market analysis, this feature is a powerful tool in streaming applications.
By configuring time_travel_retention_ms, utilizing FOR SYSTEM_TIME AS OF, and balancing storage efficiency, users can unlock deeper insights into their streaming data.
📌 For full details, visit: Time Travel Queries in RisingWave
🚀 Ready to try it out? Start by configuring time travel queries and see how past data can help improve decision-making and debugging workflows in your applications!
