Re: Partitioning and unique key

On 8/31/24 13:02, veem v wrote:
> 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 thatthe 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?

The model is at odds with itself and untenable. If the tables hold
multiple rows for a given transaction_id then you cannot have a
PK/Unique constraint on that column. Seems there is a decided lack of
any planning. The only way I can see this happening is consolidating all
the duplicate transaction_id rows into a single row for each
transaction_id. That then leads to the question of how to do that and
retain the 'correct' information from the selection of rows for each
transaction_id.

>
> Note-its 15.4 postgres database.
>
>
> Regards
>
> Veem
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com