Re: Partitioning and unique key

On Mon, 2 Sept 2024 at 19:13, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:
> > 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
> > transaction_id only?
>
> No, you cannot have both.
>
> Usually the solution is to *not* create a primary key on the partitioned
> table
> and instead create a primary key on each partition.
>
> That won't guarantee global uniqueness (and there is no way to do that),
> but it
> goes a long way by ensuring that the column is unique within each
> partition.
>
> Yours,
> Laurenz Albe
>

Thank you so much.

So it means in our case the existing PK on table level on column (txn_id
and txn_timestamp), we should drop that and create a unique index on each
partition level and also the same way the foreign key also maps to the
parent table partitions. And in that case , can we follow this as best
practices to not have the primary keys defined at the tabe level at all,
but on the partition level only, or there exist any down side to it too?
Please suggest.

Also then what I see is, it will make the data load query fail which uses
"insert on conflict" to insert data into the table and that requires the
primary key on both the columns to have on table level. Also the partition
maintenance job which uses partman extension uses the template table which
in turn uses table level properties for creating new partitions and they
will not have these unique indexes created for the new partitions as
because the unique index property is not on the table level but partition
level. Can you share your thoughts on these?