Re: Partitioning and unique key - Mailing list pgsql-general

On Mon, 2024-09-02 at 21:39 +0530, veem v wrote:
> On Mon, 2 Sept 2024 at 19:13, Laurenz Albe <laurenz.albe@cybertec.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.
>
> 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.

You can keep the primary key defined on both columns if it is good enough for you.
But it will give you lower guarantees of uniqueness: with that primary key, there could
be two rows with a different timestamp, but the same "txn_id", and these two rows could
be in the same partition...

Also, if you need a foreign key pointing *to* the partitioned table, you cannot do without
a primary key.  But I recommend that you do *not* define such foreign keys: they will make
it more difficult to detach a partition.

If you partition two tables in the same way, you can use foreign keys between the partitions
instead of foreign keys between the partitioned tables.  Such foreign keys won't be a problem.

> 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.

Yes, that is true.  A disadvantage of not having a unique constraint on the partitioned table.

> 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?

Don't use partman.  Or if you do, create the primary key yourself, after partman has created
the partition.
I wouldn't let the limitations of a tool govern my design choices.

Yours,
Laurenz Albe