Re: Matview size - space increased on concurrently refresh

On Fri, Jul 12, 2019 at 4:34 PM Nicola Contu <nicola(dot)contu(at)gmail(dot)com> wrote:

> P.S.: I am on postgres 11.3
>
> Il giorno ven 12 lug 2019 alle ore 16:32 Nicola Contu <
> nicola(dot)contu(at)gmail(dot)com> ha scritto:
>
>> Hello,
>> we noticed with a simple matview we have that refreshing it using the
>> concurrently item the space always increases of about 120MB .
>> This only happens if I am reading from that matview and at the same time
>> I am am refreshing it.
>>
>> cmdv3=# SELECT
>> pg_size_pretty(pg_relation_size('public.matview_nm_connections'::regclass));
>> pg_size_pretty
>> ----------------
>> 133 MB
>> (1 row)
>>
>> cmdv3=# refresh materialized view matview_nm_connections;
>> REFRESH MATERIALIZED VIEW
>> cmdv3=# SELECT
>> pg_size_pretty(pg_relation_size('public.matview_nm_connections'::regclass));
>> pg_size_pretty
>> ----------------
>> 133 MB
>> (1 row)
>>
>> cmdv3=# \! date
>> Fri Jul 12 13:52:51 GMT 2019
>>
>> cmdv3=# refresh materialized view matview_nm_connections;
>> REFRESH MATERIALIZED VIEW
>> cmdv3=# SELECT
>> pg_size_pretty(pg_relation_size('public.matview_nm_connections'::regclass));
>> pg_size_pretty
>> ----------------
>> 133 MB
>> (1 row)
>>
>>
>> Let's try concurrently.....
>>
>> cmdv3=# refresh materialized view CONCURRENTLY matview_nm_connections;
>> REFRESH MATERIALIZED VIEW
>> cmdv3=# SELECT
>> pg_size_pretty(pg_relation_size('public.matview_nm_connections'::regclass));
>> pg_size_pretty
>> ----------------
>> 261 MB
>> (1 row)
>>
>>
>> So the matview is not really used and it does not have anything strange
>> but that matview growth to 12GB as we refresh it once an hour.
>> It had the free percent at 97%.
>> I understand with concurrenlty it needs to take copy of the data while
>> reading, but this seems to be too much on the space side.
>>
>> Is this a bug? Or is there anyone can help us understanding this?
>>
>> Thanks a lot,
>> Nicola
>>
>
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.