RE: Declarative Range Partitioning Postgres 11

Поиск
Список
Период
Сортировка
От Shatamjeev Dewan
Тема RE: Declarative Range Partitioning Postgres 11
Дата
Msg-id F2F23E564EB92A4C80228EB86713BA130BEFA8@TO1RODC.nbspaymentsolutions.com
обсуждение исходный текст
Ответ на Re: Declarative Range Partitioning Postgres 11  (Michael Lewis <mlewis@entrata.com>)
Список pgsql-general
Дерево обсуждения

Thanks a ton Michael

 

From: Michael Lewis <mlewis@entrata.com>
Sent: November-01-19 3:20 PM
To: Shatamjeev Dewan <sdewan@nbsps.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Declarative Range Partitioning Postgres 11

 

On Fri, Nov 1, 2019 at 9:22 AM Shatamjeev Dewan <sdewan@nbsps.com> wrote:

Hi Michael,

 

I want to create a partition by year and subpartition by month in postgres 11 timestamp column. Please advise syntax.

 

 

 

The documentation is rather clear with examples like-

 

CREATE TABLE measurement_y2006m02 PARTITION OF measurement

    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

 

Note- Don't partition on function results like date_part because performance will likely suffer greatly. Also note that the top end is always exclusive so the above give a continuous range for those two months.

 

I would hesitate to partition by more than year alone before upgrading to PG v12. The speed improvements for more than 10-100 partitions (max recommended for PG11) is huge in 12.

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