Thanks to the new partitions features in pg12 (referencing partition table is possible) I was trying to migrate some of my tables into a partitions structure.
Lets assume I have the following non partitions structure :
Product(id int PK,vendor int references Vendor(id),price int)
ProductPic(int picId PK,product int references product(id) )
Vendor(id int PK,name text)
.... more tables that has references to the Product(id).
I understand that the PK on the Product table must include also the partition column in order to ensure the uniqueness across all the partitions. However, on the other hand I'll need to add the partition column (Vendor) to all the tables that has a reference to the Product(id) + update that column with the relevant data. This type of maintenance requires a lot of time because I have a lot of references to the Product table. Is there any other option in PG12 to allow references to partition table ?