[Feature]: Support adding columns to existing continuous aggregates

[noctarius]

What problem does the new feature solve?

Continuous aggregates are one of the major features of TimescaleDB. A common, and proposed, use case is to expire raw data at some point after the continuous aggregate time bucket is materialized. That means, recalculation of old continuous aggregate time buckets (hence chunks) isn't possible.

Over time, additional columns may be added to the underlying raw data hypertable, though. Especially in wide table layouts. Due to the immutability of the continuous aggregate definition, adding those additional columns isn't supported, and an additional continuous aggregate needs to be created which complicates querying and management.

It'd be awesome to support adding columns (or maybe even deleting columns).

What does the feature do?

This feature will add support for something like (the non-existent) ALTER MATERIALIZED VIEW ADD COLUMN .... Since the syntax isn't supported in PostgreSQL, some alternative way would be required, such as a function to make it work.

Implementation challenges

No response

[adriangb]

Even if we can't add a column the easy way, is there any recommendation on doing a manual workaround? I'm thinking:

1. Make the new cagg using WITH NO DATA. Carefully do a manual refresh in steps to make sure you don't overload and lock up the db recalculating multiple days of data. Then enable a policy and realtime aggregation.
2. Stop refreshing the old cagg by removing the policy.
3. Create a view that unions both caggs because your new one doesn't have the older data (if your underlaying hypertable has a retention policy)
4. After the new cagg has enough historical data delete the old cagg and the view.