Postgres Pro
Downloads
Home   >   mailing lists

RE: Declarative Range Partitioning Postgres 11 - Mailing list pgsql-general

From Shatamjeev Dewan
Subject RE: Declarative Range Partitioning Postgres 11
Date
Msg-id F2F23E564EB92A4C80228EB86713BA130BEF63@TO1RODC.nbspaymentsolutions.com
Whole thread Raw
In response to Re: Declarative Range Partitioning Postgres 11  (Michael Lewis <mlewis@entrata.com>)
Responses Re: Declarative Range Partitioning Postgres 11  (Michael Lewis <mlewis@entrata.com>)
List pgsql-general
Tree view

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@entrata.com>
Sent: October-08-19 1:33 PM
To: Shatamjeev Dewan <sdewan@nbsps.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Declarative Range Partitioning Postgres 11

 

On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan <sdewan@nbsps.com> 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.

pgsql-general by date:

Previous
From: M Tarkeshwar Rao
Date:
Subject: Getting following error in using cursor to fetch the records from alarge table in c language(current transaction is aborted, commands ignoreduntil end of transaction block)
Next
From: Francisco Olarte
Date:
Subject: Re: Getting following error in using cursor to fetch the records froma large table in c language(current transaction is aborted, commands ignoreduntil end of transaction block)