Partitioning and unique key

From: veem v <veema0000(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Partitioning and unique key
Date: 2024-08-31 20:02:07
Message-ID: CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2024-08-31 22:28:54 Re: Partitioning and unique key
Previous Message Peter J. Holzer 2024-08-31 18:02:13 Re: Upgrade Ubuntu 22 -> 24 may break PostgreSQL