From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Archiving old data when foreign keys involved
Date: Fri, 3 Feb 2023 10:36:43 -0600
Message-ID: <d9d3d5f0-507a-bc9f-0b6e-46aa75947dc0@gmail.com> (raw)
In-Reply-To: <CAGCWzULJ4nGDVNv+xmmTfpnrth9TRH=qDyJ26pee80c4DuROzA@mail.gmail.com>
References: <CAGCWzULJ4nGDVNv+xmmTfpnrth9TRH=qDyJ26pee80c4DuROzA@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Archiving old data when foreign keys involved
In-Reply-To: <d9d3d5f0-507a-bc9f-0b6e-46aa75947dc0@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox