From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | veem v <veema0000(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Partitioning and unique key |
Date: | 2024-08-31 22:28:54 |
Message-ID: | 7fa81130-0b33-4e53-bb32-39d84f06c680@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Clift | 2024-09-01 02:32:43 | Re: Upgrade Ubuntu 22 -> 24 may break PostgreSQL |
Previous Message | veem v | 2024-08-31 20:02:07 | Partitioning and unique key |