Re: Archiving old data when foreign keys involved

On 2/3/23 10:11, Albin Ary wrote:
> 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.