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

P.S.: I am on postgres 11.3

Il giorno ven 12 lug 2019 alle ore 16:32 Nicola Contu <nicola.contu@gmail.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.