Re: Matview size - space increased on concurrently refresh

It does not. That's the issue.
It always increases of 120mb and it reached 12gb instead of just 180mb.

Il dom 14 lug 2019, 21:34 Kaixi Luo <kaixiluo(at)gmail(dot)com> ha scritto:

>
> 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.
>
>