Re: Partitioning and unique key

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