I'm preparing for a talk about streaming databases and would like to know if RisingWave supports setting a retention time for tables or materialized views to automatically delete older rows. This is to avoid the need for an extra database or data warehouse, which could be costly or lead to too much data replication. Is there a feature like this in RisingWave, or is there another way to manage memory usage?
Ralph Matthias Debusmann
Asked on Jun 22, 2023
Yes, RisingWave previously allowed users to define a TTL (time-to-live) for tables or materialized views using the retention_seconds option in the CREATE TABLE/MATERIALIZED VIEW statement. However, this could lead to data inconsistency and has been replaced with a different approach using temporal filters in SQL queries. For example, you can create a materialized view that only keeps data from the last week:
CREATE MATERIALIZED VIEW sales_mv AS
SELECT *
FROM sales
WHERE sale_date > NOW() - INTERVAL '1 week';This way, the retention logic is part of the computation rather than a hard time limit on the materialized view. Additionally, RisingWave stores data on S3, and it only fetches data into local memory when needed. If data is old and not queried, it won't occupy memory. For data ingestion, RisingWave offers create table for storing ingested data and create source for not storing data but allowing materialized views to be built on top of the source. Plain views (create view) are logical constructs that do not store any data but can be used to simplify SQL queries.