Keeping 3 different CAGGs is possible but requires unnecessary CPU/space and requires the client to decide which CAGG to use which I prefer to avoid (in theory, we can also create a view with a CASE statement).
My problem is that keeping the “high res” data makes the database work harder - unless I miss something, even if I use stats_agg() it will still need to go over all the data (e.g. for SUM) so I don’t see the real value from performance side?
I’m a little confused about your question here? You’re right that high-res data will make the database work harder (this is why we made caggs, to allow that work to be done once).
Keeping multiple CAGGS will also use more disk space than not having them - the trade-off here is a small amount of storage and CPU to prevent costly queries being run many times.