Matview size - space increased on concurrently refresh

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: Matview size - space increased on concurrently refresh
Date: 2019-07-12 14:32:56
Message-ID: CAMTZZh1Muw_cg6fQHKihOK4i7mQarcmksE-+NPP0eEBaSPjywA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nicola Contu 2019-07-12 14:34:36 Re: Matview size - space increased on concurrently refresh
Previous Message Adrian Klaver 2019-07-12 14:30:16 Re: Issue: Creating Symlink for data directory of postgresql in CentOS7