MIME-Version: 1.0
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>
In-Reply-To: <7beda414a58970f917acf7959810cd9eeb94af89.camel@cybertec.at>
From: veem v <veema0000@gmail.com>
Date: Tue, 3 Sep 2024 10:39:42 +0530
Message-ID: <CAB+=1TV9cc6401SB3L2enAhT2TvHxsHjbwit6egobWzOKaASRg@mail.gmail.com>
Subject: Re: Partitioning and unique key
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000075b97b0621301186"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TV9cc6401SB3L2enAhT2TvHxsHjbwit6egobWzOKaASRg%40mail.gmail.com>
Precedence: bulk

--00000000000075b97b0621301186
Content-Type: text/plain; charset="UTF-8"

On Tue, 3 Sept 2024 at 01:14, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

>
> You can keep the primary key defined on both columns if it is good enough
> for you.
> But it will give you lower guarantees of uniqueness: with that primary
> key, there could
> be two rows with a different timestamp, but the same "txn_id", and these
> two rows could
> be in the same partition...
>
> Also, if you need a foreign key pointing *to* the partitioned table, you
> cannot do without
> a primary key.  But I recommend that you do *not* define such foreign
> keys: they will make
> it more difficult to detach a partition.
>
> If you partition two tables in the same way, you can use foreign keys
> between the partitions
> instead of foreign keys between the partitioned tables.  Such foreign keys
> won't be a problem.
>
>
Thank You so much.
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)?

 We were thinking it's because we have FK defined on tablelevel , so we
were planning to make the FK on partition level. 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.

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.

--00000000000075b97b0621301186
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Tue, 3 Sept 2024 at 01:14, Laurenz Alb=
e &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at<=
/a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><=
br>
You can keep the primary key defined on both columns if it is good enough f=
or you.<br>
But it will give you lower guarantees of uniqueness: with that primary key,=
 there could<br>
be two rows with a different timestamp, but the same &quot;txn_id&quot;, an=
d these two rows could<br>
be in the same partition...<br>
<br>
Also, if you need a foreign key pointing *to* the partitioned table, you ca=
nnot do without<br>
a primary key.=C2=A0 But I recommend that you do *not* define such foreign =
keys: they will make<br>
it more difficult to detach a partition.<br>
<br>
If you partition two tables in the same way, you can use foreign keys betwe=
en the partitions<br>
instead of foreign keys between the partitioned tables.=C2=A0 Such foreign =
keys won&#39;t be a problem.<br><br></blockquote><div><br></div><div>Thank =
You so much.</div><div>As you rightly said <i>&quot;they will make it more =
difficult to detach a partition.&quot; , </i>we are really seeing a longer =
time when detaching parent table partitions.It runs forever sometimes. So d=
o you mean it&#39;s because we have primary key defined table level or it&#=
39;s because we have FK defined in table level (for multiple child tables w=
hich are also partitioned)?<br></div><div><br></div><div>=C2=A0We were thin=
king it&#39;s because we have FK defined on tablelevel=C2=A0, so we were pl=
anning to make the FK on partition level. But as you just pointed now , eve=
n keeping the PK on table level will also make the detach partition slow? I=
 understand, for detaching partitions , it may be scanning while child beca=
use 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.</div><di=
v><br></div><div>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 hav=
e a &quot;unique index&quot; defined. Correct me if my understanding is wro=
ng.</div></div></div>

--00000000000075b97b0621301186--