Re: Matview size - space increased on concurrently refresh - Mailing list pgsql-general

[ please do not top-post in your replies, it makes the conversation hard
  to follow ]

Nicola Contu <nicola.contu@gmail.com> writes:
> Il dom 14 lug 2019, 21:34 Kaixi Luo <kaixiluo@gmail.com> 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