Re:How we made data aggregation on PostgreSQL better and faster
by trollied on Tuesday June 21, 2022 @12:29PM
(#31825844)
I was expecting this to be about INSERT performance/overhead (both IO and CPU), which is the metric that matters most when dealing with the overhead of materialized views.
[ Parent
| on HN ]
Re:How we made data aggregation on PostgreSQL better and faster
by ryanbooz on Tuesday June 21, 2022 @02:55PM
(#31827654)
(NB - post author)
In a sense, it is. Continuous aggregates only have to materialize the most recent bucket of time, not the entire materialized view as you have to in PostgreSQL. That's honestly hard to demonstrate and quantify in a blog post like this because it's something that you notice over time. If you have to refresh the PG materialized view every hour (to get the recent hour of data) and it takes 2 minutes - a year from now it's probably going to take 3-4 minutes (maybe more)... and a lot of valuable CPU/IO to boot.
With continuous aggregates, TimescaleDB is only materializing the last hour - and updating anything that's changed in previous buckets within the refresh window.