Declarative Range Partitioning Postgres 11
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
RE: Declarative Range Partitioning Postgres 11
Thanks Michael. From: Michael Lewis Sent: October-07-19 7:18 PM To: Shatamjeev Dewan Cc: pgsql-general 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.
RE: Declarative Range Partitioning Postgres 11
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 Sent: October-07-19 7:18 PM To: Shatamjeev Dewan Cc: pgsql-general 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.
RE: Declarative Range Partitioning Postgres 11
Thanks a lot Michael for invaluable advise . Appreciate your great help and support. From: Michael Lewis Sent: October-08-19 1:33 PM To: Shatamjeev Dewan Cc: pgsql-general Subject: Re: Declarative Range Partitioning Postgres 11 On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan mailto:sde...@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.
RE: Declarative Range Partitioning Postgres 11
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 Sent: October-08-19 1:33 PM To: Shatamjeev Dewan Cc: pgsql-general Subject: Re: Declarative Range Partitioning Postgres 11 On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan mailto:sde...@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.
RE: Declarative Range Partitioning Postgres 11
Thanks a ton Michael
From: Michael Lewis
Sent: November-01-19 3:20 PM
To: Shatamjeev Dewan
Cc: pgsql-general
Subject: Re: Declarative Range Partitioning Postgres 11
On Fri, Nov 1, 2019 at 9:22 AM Shatamjeev Dewan
mailto:sde...@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.
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.
ERROR: there is no unique constraint matching given keys for referenced table "audit_p"
Hi Michael, I am trying to create a foreign key constraint on a table : audit_param in postgres 12 which references partitioned table audit_p. is there anyway to get rid of this error. ERROR: there is no unique constraint matching given keys for referenced table "audit_p" ^ sd_tems_partition_test=# CREATE TABLE audit_logging.audit_p ( sd_tems_partition_test(# id BIGINT NOT NULL, sd_tems_partition_test(# event_id BIGINT NOT NULL, sd_tems_partition_test(# caused_by_user_id BIGINT NOT NULL, PARTITION BY RANGE(create_dtt); sd_tems_partition_test(# -- additional user information (to be defined by the application) sd_tems_partition_test(# adtl_user_info BIGINT, sd_tems_partition_test(# create_dtt TIMESTAMP DEFAULT now() NOT NULL, sd_tems_partition_test(# CONSTRAINT audit_PK1 PRIMARY KEY (id,create_dtt)) sd_tems_partition_test-# PARTITION BY RANGE(create_dtt); CREATE TABLE sd_tems_partition_test=# \d audit_logging.audit_p Partitioned table "audit_logging.audit_p" Column |Type | Collation | Nullable | Default ---+-+---+--+- id| bigint | | not null | event_id | bigint | | not null | caused_by_user_id | bigint | | not null | adtl_user_info| bigint | | | create_dtt| timestamp without time zone | | not null | now() Partition key: RANGE (create_dtt) Indexes: "audit_pk1" PRIMARY KEY, btree (id, create_dtt) Number of partitions: 0 sd_tems_partition_test=# CREATE TABLE audit_logging.audit_param ( sd_tems_partition_test(# audit_id BIGINT NOT NULL, sd_tems_partition_test(# param_position SMALLINT NOT NULL, sd_tems_partition_test(# value CHARACTER VARYING(4096) NOT NULL, sd_tems_partition_test(# CONSTRAINT audit_param_PK PRIMARY KEY (audit_id) sd_tems_partition_test(# ); CREATE TABLE sd_tems_partition_test=# \d audit_logging.audit_param; Table "audit_logging.audit_param" Column | Type | Collation | Nullable | Default +-+---+--+- audit_id | bigint | | not null | param_position | smallint| | not null | value | character varying(4096) | | not null | Indexes: "audit_param_pk" PRIMARY KEY, btree (audit_id) sd_tems_partition_test=# sd_tems_partition_test=# Alter table audit_logging.audit_param add constraint audit_param_audit_fk2 FOREIGN KEY (audit_id) REFERENCES audit_logging.audit_p(id); ERROR: there is no unique constraint matching given keys for referenced table "audit_p" sd_tems_partition_test=# Thanks, Shatamjeev
RE: ERROR: there is no unique constraint matching given keys for referenced table "audit_p"
Thanks a lot Michael. Get well soon. From: Michael Lewis Sent: November-18-19 12:57 PM To: Shatamjeev Dewan Cc: pgsql-general Subject: Re: ERROR: there is no unique constraint matching given keys for referenced table "audit_p" On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan mailto:sde...@nbsps.com>> wrote: I am trying to create a foreign key constraint on a table : audit_param in postgres 12 which references partitioned table audit_p. is there anyway to get rid of this error. ERROR: there is no unique constraint matching given keys for referenced table "audit_p" As far as I understand, what you want is not possible. You cannot partition on a timestamp and then foreign key to an object_id natively. You can get around this with triggers- https://www.depesz.com/2018/10/31/foreign-key-to-partitioned-table-part-2/ - but that would not be recommended. Admittedly, I have a pretty nasty head cold so it may be that someone chimes in with much better insight on your design. By the way, there are strong recommendations for using timestamp WITH TIMEZONE in nearly all cases. I just wanted to mention since timestamp without timezone is rarely the best choice.
