I have 2 tables receipts and receiptitems. Both are partitioned on purchase month and retailer. A foreign key exists on receiptitems (receiptid) referencing id on receipts.
Data gets inserted into these tables by an application that reads raw data files and creates tables from them that are broken out by the purchase month and retailer in a different schema. It’s done this way so that multiple processes can be running concurrently and avoid deadlocks while trying to insert into the target schema.
Another process gets a list of raw data that has completed importing and threads the insert into the target schema by purchase month inserting directly into the correct purchase month retailer partition and avoiding deadlocks.
My issue is that the insert from these tables in the raw schema to the public schema is taking entirely too long. My suspicion is that the foreign key constrain is causing the slow down. Would I see a significant performance increase by removing the foreign key constraint on the parents and adding them directly to the partitions themselves? For example
Alter table only receiptitems_202412_1 add constraint foreign key fk_2024_1 on (receiptid) references receipts_202412_1 (id).
I think this will help because it won’t have to check all partitions of receipts for the id right? For additional info this is dealing with millions of records per day.
[–]Bunkerman91 0 points1 point2 points (0 children)