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
