Hi,
I experience unresponsiveness from psql when a continuous aggregate refresh starts running. Especially true to long term aggregates (like weekly, monthly). The connections stay in idle and won’t get served. As we have high amount of data, this takes long time.
I don’t exactly know the underlying storage (Im working on a VM on a private cloud), but it is quite crafty, able to do 400-600MB/s writes with high transfer rates. Even when the IO is much lower than that, the db server is still unresponsive until I kill the refresh.
Any ideas or should I consider this normal and forget about long term aggregates?
This refresh does not cause a table lock, right?
Thanks,
Semirke