Thread: Archiving old data when foreign keys involved
Hello,
I am writing to ask for your advice regarding the deletion of old data when foreign keys are involved.
I have this database which has quite a complex schema (generated by ORM), very normalized with a lot of foreign key relationships for some tables.
So the situation is that some certain tables reference columns from other tables and also have columns which are being referenced from certain other tables.
Because some tables are getting big and this is affecting the overall performance (mainly insert performance) I was thinking of creating some denormalized tables in which I will keep the necessary relevant data and delete older records afterwards. This is ok for the app from a business point of view, but technically I am not sure how it could be tackled.
Is there any tool which might help with this kind of situation?
Not sure if it makes a difference anyway this is Postgresql 14.5.
Many thanks!
On 2/3/23 10:11, Albin Ary wrote:
I found an information_schema query on stackexchange which got me 95% of the way to the correct FK dependency chain. Using that, some trial and error, I developed an order by which to delete from tables.
Sadly and distressingly, I can't find it anymore. But it does mean that what you want can and has been done before.
Hello,I am writing to ask for your advice regarding the deletion of old data when foreign keys are involved.I have this database which has quite a complex schema (generated by ORM), very normalized with a lot of foreign key relationships for some tables.So the situation is that some certain tables reference columns from other tables and also have columns which are being referenced from certain other tables.Because some tables are getting big and this is affecting the overall performance (mainly insert performance) I was thinking of creating some denormalized tables in which I will keep the necessary relevant data and delete older records afterwards. This is ok for the app from a business point of view, but technically I am not sure how it could be tackled.Is there any tool which might help with this kind of situation?
I found an information_schema query on stackexchange which got me 95% of the way to the correct FK dependency chain. Using that, some trial and error, I developed an order by which to delete from tables.
Sadly and distressingly, I can't find it anymore. But it does mean that what you want can and has been done before.
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.