I find PostgreSQL's materialized view functionality very useful, but one
problem is that when the view gets large, there is no way to refresh
part of it. I know that table partitioning is coming in Postgres 10,
but I haven't heard anything about ideas for partitioning / sharding of
materialized views, so I'm trying to work out a strategy for doing it
manually until that's supported in the future.
Because there's no table inheritance for materialized views, the only
way I can think of to do it is to create materialized views manually for
each partition (e.g. each month) and then use a UNION ALL view to act as
the "parent" table. It looks something like this:
CREATE MATERIALIZED VIEW prices_2016_04 AS (
SELECT sale_date, price FROM tbl
WHERE sale_date >= '2016/04/01' AND sale_date < '2016/05/01'
);
CREATE MATERIALIZED VIEW test_2016_05 AS (
SELECT sale_date, price FROM tbl
WHERE sale_date >= '2016/05/01' AND sale_date < '2016/06/01'
);
CREATE MATERIALIZED VIEW test_2016_06 AS (
SELECT sale_date, price FROM tbl
WHERE sale_date >= '2016/06/01' AND sale_date < '2016/07/01'
);
CREATE VIEW test_2016 AS (
SELECT * FROM prices_2016_04
UNION ALL
SELECT * FROM test_2016_05
UNION ALL
SELECT * FROM test_2016_06
);
The problem comes when these views get large. Unlike when the
underlying objects are tables, there is no way for me to create CHECK
constraints on these materialized views, which means when I do something
like:
SELECT * FROM test_2016 WHERE sale_date = '2016/04/15'
The query planner is forced to look at all of the underlying
materialized views, even though the date in the query will only ever
match one of them.
As a workaround, I can have my application restrict the query to a
single month's materialized view, but it would be ideal if we could
create CHECK constraints on materialized views, or even better, if the
query planner could know about the underlying query that created the
materialized view to infer that certain rows will never match based on
the conditions that created the view.
Are either of these things that could be supported in the future? If
not, is there a better way to get the behavior I want, where I can
partition materialized views based on a date range?
Thanks.
-Tony
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
