[ please do not top-post in your replies, it makes the conversation hard to follow ] Nicola Contu <nicola.contu@xxxxxxxxx> writes: > Il dom 14 lug 2019, 21:34 Kaixi Luo <kaixiluo@xxxxxxxxx> ha scritto: >> This is normal and something to be expected. When refreshing the >> materialized view, the new data is written to a disk and then the two >> tables are diffed. After the refresh finishes, your view size should go >> back to normal. > It does not. That's the issue. > It always increases of 120mb and it reached 12gb instead of just 180mb. A concurrent matview refresh will necessarily leave behind two copies of any rows it changes, just like any other row-update operation in Postgres. Once there are no concurrent transactions that can "see" the old row copies, they should be reclaimable by vacuum. Since you're not seeing autovacuum reclaim the space automatically, I hypothesize that you've got autovacuum turned off or dialed down to unrealistically non-aggressive settings. Or possibly you have old open transactions that are preventing reclaiming dead rows (because they can still possibly "see" those rows). Either of those explanations should imply that you're getting similar bloat in every other table and matview, though. You might want to look into pg_stat_all_tables to see what it says about the last_autovacuum time etc. for that matview. Another source of insight is to do a manual "vacuum verbose" on the matview and see what that says about removable and nonremovable rows. regards, tom lane
