From: | Nicola Contu <nicola(dot)contu(at)gmail(dot)com> |
---|---|
To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Cc: | Alessandro Aste <Alessandro(dot)aste(at)gtt(dot)net> |
Subject: | Re: Matview size - space increased on concurrently refresh |
Date: | 2019-07-12 14:34:36 |
Message-ID: | CAMTZZh0vo62VczNBSF5dZ7MzWA0fKicn4MuU40=4MGSLiPcbRw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-07-12 14:43:13 | Re: disable and enable trigger all when a foreign keys |
Previous Message | Nicola Contu | 2019-07-12 14:32:56 | Matview size - space increased on concurrently refresh |