public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: veem v <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Partitioning and unique key
Date: Tue, 03 Sep 2024 07:53:29 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB+=1TV9cc6401SB3L2enAhT2TvHxsHjbwit6egobWzOKaASRg@mail.gmail.com>
References: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
	<[email protected]>
	<CAB+=1TVGKBNTbEJC8=hx4DACO=T4uDiaZGKhwirpU62fUQ=UEQ@mail.gmail.com>
	<[email protected]>
	<CAB+=1TV9cc6401SB3L2enAhT2TvHxsHjbwit6egobWzOKaASRg@mail.gmail.com>

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

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Partitioning and unique key
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox