range partitioning of materialized views

[Tony Cebzanov <tonycpsu@xxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general