PostgreSQL partitioned table data model

Question

I have created partition by range based on row_created_tmst for PostgreSQL table. Since partition keys needs to be part of the primary key in PostgreSQL, row_created_tmst is included in the primary key of the table.

With this change, I am facing an issue with foreign key in the child tables.

Parent Table:source_record

source_record_id bigint NOT NULL,
row_created_tmst timestamp with time zone NOT NULL,
list of other attributes..
CONSTRAINT source_record_pk PRIMARY KEY (source_record_id, row_created_tmst)

Child Table: role_player (one source_record have 1 or more role player)
role_player_id bigint NOT NULL,
row_created_tmst timestamp with timezone NOT NULL,
source_record_id bigint NOT NULL, -- Foreign Key
sr_row_created_tmst bigint NOT NULL, -- Foreign Key
list of other attributes.. 
CONSTRAINT role_player_pk PRIMARY KEY (role_player_id, row_created_tmst)
CONSTRAINT role_player_ak FOREIGN KEY (source_record_id, sr_row_created_tmst)

Because of partitioning based on row_created_tmst column, all child tables of source_record is having 2 row_created_tmst columns (sr_row_created_tmst (FK), row_created_tmst)

Is there a way to aviod having both parent and child table row_created_tmst column in child tables.

Answer 1

No, there is no way to avoid that. Moreover, if you define a foreign key that references a partitioned table, you can no longer detach partitions.

The best thing you can do (in my opinion) is not to defile a primary key on the partitioned table, but to define a primary key on source_record_id on each partition. Sacrifice referential integrity and don't define a foreign key constraint at all. You have to live with reduced data integrity guarantees if you want to benefit from partitioning.