Declarative Range Partitioning Postgres 11

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Declarative Range Partitioning Postgres 11

Shatamjeev Dewan

Hi,

 

I am trying to create a table in postgres 11 with timestamp column as  a partition key using  PARTITION BY RANGE (create_dtt). The table definition has also an id column which is a primary key.

 

ERROR:  insufficient columns in PRIMARY KEY constraint definition

DETAIL:  PRIMARY KEY constraint on table "audit_p" lacks column "create_dtt" which is part of the partition key.

 

I don’t want partition key column   : create_dtt  to part of composite primary key.  Is there any way I can create range partition on date column without including as part of primary key .

 

Please advise.

 

Thanks,

Shatamjeev

 

Reply | Threaded
Open this post in threaded view
|

Re: Declarative Range Partitioning Postgres 11

Michael Lewis
No, what you want is not possible and probably won't ever be I would expect. Scanning every partition to validate the primary key isn't scalable.
Reply | Threaded
Open this post in threaded view
|

Re: Declarative Range Partitioning Postgres 11

Ron-2
On 10/7/19 6:17 PM, Michael Lewis wrote:
> No, what you want is not possible and probably won't ever be I would expect.

Sure it is.  Maybe not the (weird) way that Postgres does partitioning, but
the legacy RDBMS that I still occasionally maintain has for at least 25
years had partition key independent of any indexes.

> Scanning every partition to validate the primary key isn't scalable.

That's only because of the way Pg implements partitioning.

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

RE: Declarative Range Partitioning Postgres 11

Shatamjeev Dewan
In reply to this post by Michael Lewis

Thanks Michael.

 

From: Michael Lewis <[hidden email]>
Sent: October-07-19 7:18 PM
To: Shatamjeev Dewan <[hidden email]>
Cc: pgsql-general <[hidden email]>
Subject: Re: Declarative Range Partitioning Postgres 11

 

No, what you want is not possible and probably won't ever be I would expect. Scanning every partition to validate the primary key isn't scalable.

Reply | Threaded
Open this post in threaded view
|

RE: Declarative Range Partitioning Postgres 11

Shatamjeev Dewan
In reply to this post by Michael Lewis

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 .

 

Thanks,

Shatamjeev

 

From: Michael Lewis <[hidden email]>
Sent: October-07-19 7:18 PM
To: Shatamjeev Dewan <[hidden email]>
Cc: pgsql-general <[hidden email]>
Subject: Re: Declarative Range Partitioning Postgres 11

 

No, what you want is not possible and probably won't ever be I would expect. Scanning every partition to validate the primary key isn't scalable.

Reply | Threaded
Open this post in threaded view
|

Re: Declarative Range Partitioning Postgres 11

Michael Lewis
In reply to this post by Ron-2
On Mon, Oct 7, 2019 at 5:56 PM Ron <[hidden email]> wrote:
On 10/7/19 6:17 PM, Michael Lewis wrote:
> No, what you want is not possible and probably won't ever be I would expect.

Sure it is.  Maybe not the (weird) way that Postgres does partitioning, but
the legacy RDBMS that I still occasionally maintain has for at least 25
years had partition key independent of any indexes.

> Scanning every partition to validate the primary key isn't scalable.

That's only because of the way Pg implements partitioning.

I can dig that, but since this is a Postgres list and everything I have heard indicates it is not a limitation that is likely to be removed in Postgres, it seems like we are having two different discussions.
Reply | Threaded
Open this post in threaded view
|

Re: Declarative Range Partitioning Postgres 11

Michael Lewis
In reply to this post by Shatamjeev Dewan
On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

RE: Declarative Range Partitioning Postgres 11

Shatamjeev Dewan

Thanks a lot Michael for invaluable advise . Appreciate your great help and support.

 

From: Michael Lewis <[hidden email]>
Sent: October-08-19 1:33 PM
To: Shatamjeev Dewan <[hidden email]>
Cc: pgsql-general <[hidden email]>
Subject: Re: Declarative Range Partitioning Postgres 11

 

On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Declarative Range Partitioning Postgres 11

Ron-2
In reply to this post by Michael Lewis
On 10/8/19 12:33 PM, Michael Lewis wrote:
On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan <[hidden email]> 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?

Because archiving old is (well, should be) easier that way.

--
Angular momentum makes the world go 'round.