pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedArchiving old data when foreign keys involved
2+ messages / 2 participants
[nested] [flat]
* Archiving old data when foreign keys involved
@ 2023-02-03 16:11 Albin Ary <albinary10@gmail.com>
2023-02-03 16:36 ` Re: Archiving old data when foreign keys involved Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Albin Ary @ 2023-02-03 16:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Archiving old data when foreign keys involved
2023-02-03 16:11 Archiving old data when foreign keys involved Albin Ary <albinary10@gmail.com>
@ 2023-02-03 16:36 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Ron @ 2023-02-03 16:36 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-02-03 16:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-03 16:11 Archiving old data when foreign keys involved Albin Ary <albinary10@gmail.com>
2023-02-03 16:36 ` Ron <ronljohnsonjr@gmail.com>
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