Message-ID: <1b018e6f3df43c4f6c8f3c2b2280ba411c38581a.camel@cybertec.at>
Subject: Re: Partitioning and unique key
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Date: Tue, 03 Sep 2024 07:53:29 +0200
In-Reply-To: <CAB+=1TV9cc6401SB3L2enAhT2TvHxsHjbwit6egobWzOKaASRg@mail.gmail.com>
References: 
	<CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
	 <a2473f06464fde533ed499b53e2d87b79567fd21.camel@cybertec.at>
	 <CAB+=1TVGKBNTbEJC8=hx4DACO=T4uDiaZGKhwirpU62fUQ=UEQ@mail.gmail.com>
	 <7beda414a58970f917acf7959810cd9eeb94af89.camel@cybertec.at>
	 <CAB+=1TV9cc6401SB3L2enAhT2TvHxsHjbwit6egobWzOKaASRg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/1b018e6f3df43c4f6c8f3c2b2280ba411c38581a.camel%40cybertec.at>
Precedence: bulk

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 partiti=
on." ,
> 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 ke=
y
> 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 detac=
h
a partition, PostgreSQL has to verify that that won't violate the constrain=
t,
so it has to scan the tables, which will take time if the partitions are la=
rge.

> We were thinking it's because we have FK defined on tablelevel=C2=A0, 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 als=
o
> make the detach partition slow? I understand, for detaching partitions ,
> it may be scanning while child because of the FK defined on the table lev=
el.
> 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 performan=
ce.

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=C2=A0only can have a "unique i=
ndex"
> 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 tab=
le
is a primary key on each partition, and a table can only have a single prim=
ary
key, so adding another primary key on the partition would cause an error.

Yours,
Laurenz Albe