On Sun, Mar 24, 2024 at 12:38 AM veem v <veema0000@xxxxxxxxx> wrote:
On Sat, 23 Mar 2024 at 23:08, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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.
This appears to be a major issue , if it's taking minutes for dropping the parent table partitions and not allowing read operation during that time on the child table by taking locks on them. We have many databases in Oracle with such referential key constraints existing on partitioned tables and we were planning to move those to postgres. I think in Oracle, they were by default created partition to partition without need to check the whole table or all the child table partitions while dropping the parent partitions .
