[GENERAL] range partitioning of materialized views

Поиск
Список
Период
Сортировка
От Tony Cebzanov
Тема [GENERAL] range partitioning of materialized views
Дата
Msg-id 0974f793-e5aa-71a9-aec1-01b7bed7162b@gmail.com
обсуждение исходный текст
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Best way to alter a foreign constraint
Следующее
От: lin
Дата:
Сообщение: [GENERAL] the master has two sync slave, which slave can promote to masterand the other slave as the new master's slave?