Postgresql 11 Partitioning detail tables based on column in master table in foreign-key relationship

Question

The improvements to declarative range-based partitioning in version 11 seem like they can really work for my use case, but I'm not completely sure how foreign keys work with partitions.

I have tables Files -< Segments -< Entries in which there are hundreds of Segments per File and hundreds of Entries per segment, so Entries is on the order of 10,000 times the size of Files. Files have a CreationDate field and customers will define a retention period so they'll drop old Entries. All of this clearly points to date-based partitions so it's quicker to query the most recent entries first, and easy to drop old ones.

The first issue I encounter is that when I try to create the Files table, it sounds like I have to include createdDate as part of the primary key in order to have it in the RANGE partition:

CREATE TABLE Files2
  (
    FileId BIGSERIAL PRIMARY KEY,
    createdDate timestamp with time zone,
    filepath character varying COLLATE pg_catalog."default" NOT NULL
  ) PARTITION BY RANGE (createdDate)
  WITH (
    OIDS = FALSE
  )                                
  TABLESPACE pg_default;
ERROR:  insufficient columns in PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "files2" lacks column "createddate" which is part of the partition key.

If I drop the "PRIMARY KEY" from the definition of FileId, I don't get the error, but does that affect the efficiency of child lookups?

I also don't know how to declare the partitions for the child table. PARTITION BY RANGE (Files.createdDate) doesn't work.

Since it's only in version 11 that this use case would even be possible, I haven't found much information about it and would appreciate any pointers! Thanks!

Answer 1

I believe it is a new feature of the version 11. There is a document from which you can get the following information:

"A primary key constraint must include a partition key column. Attempting to create a primary key constraint that does not contain partitioned columns results in an error"

https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL11NewFeaturesGAen20181022-1.pdf