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.