Content-Type: multipart/alternative;
 boundary="------------JipaWwwy5i4WLUTtearrCzj5"
Message-ID: <d9d3d5f0-507a-bc9f-0b6e-46aa75947dc0@gmail.com>
Date: Fri, 3 Feb 2023 10:36:43 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Archiving old data when foreign keys involved
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAGCWzULJ4nGDVNv+xmmTfpnrth9TRH=qDyJ26pee80c4DuROzA@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: 
 <CAGCWzULJ4nGDVNv+xmmTfpnrth9TRH=qDyJ26pee80c4DuROzA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d9d3d5f0-507a-bc9f-0b6e-46aa75947dc0%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------JipaWwwy5i4WLUTtearrCzj5
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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.
--------------JipaWwwy5i4WLUTtearrCzj5
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 2/3/23 10:11, Albin Ary wrote:<br>
    <blockquote type="cite"
cite="mid:CAGCWzULJ4nGDVNv+xmmTfpnrth9TRH=qDyJ26pee80c4DuROzA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hello, 
        <div>I am writing to ask for your advice regarding the deletion
          of old data when foreign keys are involved. </div>
        <div><br>
        </div>
        <div>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. </div>
        <div><br>
        </div>
        <div>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. </div>
        <div><br>
        </div>
        <div>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. </div>
        <div><br>
        </div>
        <div>Is there any tool which might help with this kind of
          situation?</div>
      </div>
    </blockquote>
    <br>
    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.<br>
    <br>
    Sadly and distressingly, I can't find it anymore.  But it <i>does</i>
    mean that what you want <i>can and has been</i> done before.<br>
    <br>
    <div class="moz-signature">-- <br>
      Born in Arizona, moved to Babylonia.</div>
  </body>
</html>

--------------JipaWwwy5i4WLUTtearrCzj5--