We have an scenario where we have two tables
One COLUMNSTORE table (on disk) that has all our data from the past 6 months.
One table (on memory) that has data only from the last 4 weeks (same data as the other table, with fewer columns).
We have this setup, as we require some questions to be answered faster (therefore the memory table)
What is the best approach to replicate this data, and keep only the last 30 days?
Thought about pipelines, but didn’t have an option to “drop” observations older than 30 days (each day).
Currently we are using a Python script running each day that:
- Drops everything inside the memory table
- Loads data from the last 30 days
- Repeat each day (but of course takes time/bandwidth)
Would like to know if MemSQL has a better approach for this.