MIME-Version: 1.0
References: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
 <a2473f06464fde533ed499b53e2d87b79567fd21.camel@cybertec.at>
In-Reply-To: <a2473f06464fde533ed499b53e2d87b79567fd21.camel@cybertec.at>
From: veem v <veema0000@gmail.com>
Date: Mon, 2 Sep 2024 21:39:06 +0530
Message-ID: <CAB+=1TVGKBNTbEJC8=hx4DACO=T4uDiaZGKhwirpU62fUQ=UEQ@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="000000000000b6480206212529bc"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TVGKBNTbEJC8%3Dhx4DACO%3DT4uDiaZGKhwirpU62fUQ%3DUEQ%40mail.gmail.com>
Precedence: bulk

--000000000000b6480206212529bc
Content-Type: text/plain; charset="UTF-8"

On Mon, 2 Sept 2024 at 19:13, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

> On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:
> > due to postgres limitations we are unable to have this unique constraint
> or primary key
> > only on the transaction_id column, we have to include
> transaction_timestamp with it as
> > a composite key. So I want to understand from experts if there is any
> possible way to
> > satisfy both partitioning on transaction_timestamp column and unique key
> or pk just on
> > transaction_id only?
>
> No, you cannot have both.
>
> Usually the solution is to *not* create a primary key on the partitioned
> table
> and instead create a primary key on each partition.
>
> That won't guarantee global uniqueness (and there is no way to do that),
> but it
> goes a long way by ensuring that the column is unique within each
> partition.
>
> Yours,
> Laurenz Albe
>

Thank you so much.

So it means in our case the existing PK on table level on column (txn_id
and txn_timestamp), we should drop that and create a unique index on each
partition level and also the same way the foreign key also maps to the
parent table partitions. And in that case , can we follow this as best
practices to not have the primary keys defined at the tabe level at all,
but on the partition level only, or there exist any down side to it too?
Please suggest.

Also then what I see is, it will make the data load query fail which uses
"insert on conflict" to insert data into the table and that requires the
primary key on both the columns to have on table level. Also the partition
maintenance job which uses partman extension uses the template table which
in turn uses table level properties for creating new partitions and they
will not have these unique indexes created for the new partitions as
because the unique index property is not on the table level but partition
level. Can you share your thoughts on these?

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, 2 Sept 2024 at 19:13, Laurenz Alb=
e &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at<=
/a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex">On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:<=
br>
&gt; due to postgres limitations we are unable to have this unique constrai=
nt or primary key<br>
&gt; only on the transaction_id column, we have to include transaction_time=
stamp with it as<br>
&gt; a composite key. So I want to understand from experts if there is any =
possible way to<br>
&gt; satisfy both partitioning on transaction_timestamp column and unique k=
ey or pk just on<br>
&gt; transaction_id only?=C2=A0<br>
<br>
No, you cannot have both.<br>
<br>
Usually the solution is to *not* create a primary key on the partitioned ta=
ble<br>
and instead create a primary key on each partition.<br>
<br>
That won&#39;t guarantee global uniqueness (and there is no way to do that)=
, but it<br>
goes a long way by ensuring that the column is unique within each partition=
.<br>
<br>
Yours,<br>
Laurenz Albe<br></blockquote><div><br></div><div>Thank you so much.</div><d=
iv><br></div><div>So it means in our case the existing PK on table level on=
 column (txn_id and txn_timestamp), we should drop that and create a unique=
 index on each partition level and also the same way the foreign key also m=
aps=C2=A0to the parent table partitions. And in that case , can we follow t=
his as best practices to not have the=C2=A0primary=C2=A0keys defined at the=
 tabe level at all, but on the partition level only, or there exist any=C2=
=A0down side to it too? Please suggest.</div><div><br></div><div>Also then =
what I see is, it will make the data load query fail which uses &quot;inser=
t on conflict&quot; to insert data into the table and that requires the pri=
mary key on both the columns to have on table level. Also the partition mai=
ntenance job which uses partman extension uses the template table which in =
turn uses table level properties for creating new partitions and they will =
not have these unique indexes created for the new partitions as because the=
 unique index property is not on the table level but partition level. Can y=
ou share your=C2=A0thoughts=C2=A0on these?=C2=A0=C2=A0</div><div><br></div>=
</div></div>

--000000000000b6480206212529bc--