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

From Nicola Contu
Subject Re: Matview size - space increased on concurrently refresh
Date
Msg-id CAMTZZh3vqa94vU2g1fmzV2X3JDtNwr9G4GSPACHK2JgqbveWNg@mail.gmail.com
Whole thread Raw
In response to Re: Matview size - space increased on concurrently refresh  (Kaixi Luo <kaixiluo@gmail.com>)
Responses Re: Matview size - space increased on concurrently refresh
List pgsql-general
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@gmail.com> ha scritto:

On Fri, Jul 12, 2019 at 4:34 PM Nicola Contu <nicola.contu@gmail.com> wrote:
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.
 

pgsql-general by date:

Previous
From: Kaixi Luo
Date:
Subject: Re: Matview size - space increased on concurrently refresh
Next
From: Tom Lane
Date:
Subject: Re: Matview size - space increased on concurrently refresh