RE: Declarative Range Partitioning Postgres 11

[Shatamjeev Dewan <sdewan@xxxxxxxxx>]

Hi Michael,

 

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

 

Thanks,

Shatamjeev

 

From: Michael Lewis <mlewis@xxxxxxxxxxx>
Sent: October-08-19 1:33 PM
To: Shatamjeev Dewan <sdewan@xxxxxxxxx>
Cc: pgsql-general <pgsql-general@xxxxxxxxxxxxxx>
Subject: Re: Declarative Range Partitioning Postgres 11

 

On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan <sdewan@xxxxxxxxx> wrote:

Hi Michael,

 

In this case , I always need to include partition key(date)  in primary key ( if I have a primary key defined on non partition key column e.g id (in my case), to make it a composite primary key (id, date). This would allow duplicate id with different date,which is not desirable .

 

If you are generating the ID with a sequence, there isn't any real world likelihood of conflict, but I do understand your concern in terms of enforcing data integrity. Other than creating a custom stored procedure that functions as a primary key constraint, I don't know of any way around that.

 

Let's take a step back... why do you think you need to partition at all? And why partition by the date/timestamp/timestamptz field? Also, from what I have seen, PG12 is when partitioning really gets performant in terms of more than 10 to 100 partitions, and you can then create FKeys to the partitioned table (not possible in PG11). Also, if your frequent access of the table is by date/timestamptz field, then you might consider a BRIN index if you have high correlation between physical storage and values in that field. That can mitigate the need for partitioning.

 

Our organization will be waiting until next quarter to upgrade to PG12 and then partitioning a few of our largest tables. That is to say, I don't have experience with partitioning in production yet so others may chime in with better advice.