Feedback-ID: i76984098:Fastmail
Message-ID: <7fa81130-0b33-4e53-bb32-39d84f06c680@aklaver.com>
Date: Sat, 31 Aug 2024 15:28:54 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Partitioning and unique key
To: veem v <veema0000@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/7fa81130-0b33-4e53-bb32-39d84f06c680%40aklaver.com>
Precedence: bulk

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.klaver@aklaver.com