Re: Not able to purge partition

On Sat, 23 Mar 2024 at 23:08, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Sat, 2024-03-23 at 22:41 +0530, veem v wrote:
> > 1)As we see having foreign key defined is making the detach partition run
> > for minutes(in our case 5-10minutes for 60 million rows partition), so
> > how to make the parent table partition detach and drop work fast in
> such
> > a scenario while maintaining the foreign key intact?
>
> I told you: don't do it.
> Instead, use foreign keys between the partitions.
>

I am struggling to understand how to maintain those partitions then? As
because we were planning to use pg_partman for creating and dropping
partitions automatically without much hassle. So do you mean to say do the
partition maintenance(create/drop) by creating our own jobs and not to use
the pg_partman extension for this.

Say for example in our case the parent table has 3-4 child table and all
are partitioned on same keys/columns, so how we can identify the child
partitions and then create all foreign keys to the respective parent table
partitions and attach those partitions to parent table and also make this
process automated? Appreciate any guidance on this.

Actually, using pg_partman was taking care of everything starting from
creating partitions with different names and creating respective indexes,
constraints also with different names for each partitions without us being
worrying anything about those.