From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Shatamjeev Dewan <sdewan(at)nbsps(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Declarative Range Partitioning Postgres 11 |
Date: | 2019-11-01 19:19:58 |
Message-ID: | CAHOFxGqZtHphsNbAWJYWpBEyO0oSGuro0zCKWjir+kWbnFv+Aw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Nov 1, 2019 at 9:22 AM Shatamjeev Dewan <sdewan(at)nbsps(dot)com> wrote:
> Hi Michael,
>
>
>
> I want to create a partition by year and subpartition by month in postgres
> 11 timestamp column. Please advise syntax.
>
https://www.postgresql.org/docs/11/ddl-partitioning.html
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Shatamjeev Dewan | 2019-11-01 19:26:00 | RE: Declarative Range Partitioning Postgres 11 |
Previous Message | Jason Ralph | 2019-11-01 18:58:40 | Are my autovacuum settings too aggressive for this table? |