MIME-Version: 1.0
From: veem v <veema0000@gmail.com>
Date: Sun, 1 Sep 2024 01:32:07 +0530
Message-ID: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
Subject: Partitioning and unique key
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006d11b10621002f1a"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TViAodXqYbgB%2BxiXL%3D6AShPacH5G9PB2hbcd%2BgHR_Nb9w%40mail.gmail.com>
Precedence: bulk

--0000000000006d11b10621002f1a
Content-Type: text/plain; charset="UTF-8"

Hello,

We have our transaction tables daily range partitioned based on
transaction_timestamp column which is timestamptz data type and these are
having composite primary key on (transaction_id, transaction_timestamp).
And we were using an "insert on conflict" for loading data to our system ,
which means if another record comes to the system with the same
transaction_id and transaction_timestamp, it will get updated. This way we
already have 60 days worth of data stored in our system with approx. 70
million transactions per day.

But we just got to know from business that the data should be unique by
only transaction_id but not transaction_timestamp. Any incoming data with
the same transaction_id(even different transaction_timestamp) should get
updated but not inserted.

Also these daily partitions are going to hold 400million rows in future and
will be queried on the transaction_timestamp filter so we can't really
avoid the partitioning option here considering future growth.

But 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
*trans*action_id only?

Note-its 15.4 postgres database.


Regards

Veem

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

<div dir=3D"auto"><p dir=3D"ltr"><span style=3D"font-size:1em">Hello,=C2=A0=
</span></p><p dir=3D"ltr"><span style=3D"font-size:1em">We</span> have our =
transaction tables daily range partitioned based on transaction_timestamp c=
olumn which is timestamptz data type and these are having composite primary=
 key on (transaction_id, transaction_timestamp). And we were using an &quot=
;insert on conflict&quot; for loading data to our system , which means if a=
nother record comes to the system with the same transaction_id and transact=
ion_timestamp, it will get updated. This way we already have 60 days worth =
of data stored in our system with approx. 70 million transactions per day. =
</p>
<p dir=3D"ltr"><span style=3D"font-size:1em"><span style=3D"font-size:1em">=
But we just got to know from business that</span></span><span style=3D"font=
-size:1em"> the data should be unique by only transaction_id but not transa=
ction_timestamp. Any incoming data with the same transaction_id(even differ=
ent transaction_timestamp) should get updated but not inserted.</span></p><=
p dir=3D"ltr">Also these daily partitions are going to hold 400million rows=
 in future and will be queried on the transaction_timestamp filter so we ca=
n&#39;t really avoid the partitioning option here considering future growth=
. </p>
<p dir=3D"ltr"><span style=3D"font-size:1em">But due to postgres limitation=
s we are unable to have this unique constraint or primary key only on the t=
ransaction_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 possib=
le way to satisfy both partitioning on transaction_timestamp column and uni=
que key or pk just on </span><u><span style=3D"font-size:1em">trans</span><=
/u><span style=3D"font-size:1em">action_id only?=C2=A0</span></p><p dir=3D"=
ltr"><span style=3D"font-size:1em">Note-its 15.4 postgres database.=C2=A0</=
span></p><p dir=3D"ltr"><span style=3D"font-size:1em"><br></span></p><p dir=
=3D"ltr"><span style=3D"font-size:1em">Regards</span></p><p dir=3D"ltr"><sp=
an style=3D"font-size:1em">Veem</span></p></div>

--0000000000006d11b10621002f1a--