Forum: Building VoltDB Applications

Post: MV getting deleted when base table is truncated

MV getting deleted when base table is truncated
SharonDashet
Oct 26, 2015
Hi

We have a POC in which we have a base table populated via Kafka loader with 20k events per second.
This base table is partitioned and populating one or more Materialed Views, each has a partitioned stored procedure to handle a certain report/use case.
The MV should serve 2 purposes:
**Compressing the raw data and allowing quicker reads, as you show in your demos
**Allowing the data to stay longer than the base table. For example the base table should contain raw data for 5 minutes while the MV should conatin data for one hour to allow reporting of past 1 hour.

The problem:
When deleting values from the base table the aggregated values on the MV are deleted as well!
How can this be prevented?
If not, what is your recommended method for holding aggregated data for longer than the base table?
jpiekos
Oct 26, 2015
Hi Sharon,

Yes, materialized views are updated dynamically when the underlying table data changes, so deleting rows updates the aggregates in the MV.

We're working on a new view-type that can retain aggregates when the source data leaves the database, look for it in an upcoming release. Until then, perhaps capturing any aggregates and putting them in a different table (cache the result set, for example) might work?

John