On Tue, 2024-09-03 at 10:39 +0530, veem v wrote: > As you rightly said "they will make it more difficult to detach a partition." , > we are really seeing a longer time when detaching parent table partitions. > It runs forever sometimes. So do you mean it's because we have primary key > defined table level or it's because we have FK defined in table level > (for multiple child tables which are also partitioned)? I'd say it is because of the foreign key. If you have a foreign key that points to a partitioned table, and you detach a partition, PostgreSQL has to verify that that won't violate the constraint, so it has to scan the tables, which will take time if the partitions are large. > We were thinking it's because we have FK defined on tablelevel , so we were > planning to make the FK on partition level. Good move. > But as you just pointed now , even keeping the PK on table level will also > make the detach partition slow? I understand, for detaching partitions , > it may be scanning while child because of the FK defined on the table level. > but i am unable to understand how the table level PK impacts the detach > partition from parent here. No, a primary key on the partitioned table won't be a problem for performance. My concern was that if what you really would like is "id" to be unique, how does a primary key on (id, some_timestamp) benefit you? > My understanding is PK can only be created on table level but not on the > partition level. On the partition level we only can have a "unique index" > defined. Correct me if my understanding is wrong. No, you can define a primary key on the partition. That is, if you have no primary key on the partitioned table. A primary key on the partitioned table is a primary key on each partition, and a table can only have a single primary key, so adding another primary key on the partition would cause an error. Yours, Laurenz Albe
