refresh materialized view concurrently alternatives

From: Zsolt Ero <zsolt(dot)ero(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: refresh materialized view concurrently alternatives
Date: 2019-07-02 22:09:45
Message-ID: CAKw-smAdALa7YyG33a+aGrPG2pDQio3zxx+BdG6UWBNoZhYyYQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm using refresh materialized view concurrently at the moment. I have
a few problems with it:
1. It requires adding a unique index, even if it's never actually
used. This can just create wasted space and bad cache utilization.

2. It locks the table so that two refresh commands cannot be run at
the same time.

3. It's slower than without concurrently.

My idea is the following approach:

DROP MATERIALIZED VIEW IF EXISTS tmp.my_mat_view;

CREATE MATERIALIZED VIEW tmp.my_mat_view AS
SELECT ...

BEGIN;
DROP MATERIALIZED VIEW IF EXISTS my_mat_view;
ALTER MATERIALIZED VIEW tmp.my_mat_view SET SCHEMA public;
COMMIT;

Would this approach work? From my testing this approach doesn't result
in any kind of locking, and it's very fast and also it doesn't require
the unique index condition.

Are there any problems with this? In what situations would refresh mat
view or refresh mat view concurrently has advantages over this?

Probably it's important to note how my DB works, inserts are pretty
much 100% controlled. They happen once per hour, after which all views
are refreshed.

Zsolt

Browse pgsql-general by date

  From Date Subject
Next Message raf 2019-07-02 23:04:27 Re:
Previous Message Day, David 2019-07-02 17:54:13 pglogical extension. - 2 node master <-> master logical replication ?