public inbox for [email protected]  

help / color / mirror / Atom feed

From: veem v <[email protected]>
To: pgsql-general <[email protected]>
Subject: Partitioning and unique key
Date: Sun, 1 Sep 2024 01:32:07 +0530
Message-ID: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Partitioning and unique key
  In-Reply-To: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox