Message-ID: <7beda414a58970f917acf7959810cd9eeb94af89.camel@cybertec.at>
Subject: Re: Partitioning and unique key
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Date: Mon, 02 Sep 2024 21:44:37 +0200
In-Reply-To: <CAB+=1TVGKBNTbEJC8=hx4DACO=T4uDiaZGKhwirpU62fUQ=UEQ@mail.gmail.com>
References: 
	<CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
	 <a2473f06464fde533ed499b53e2d87b79567fd21.camel@cybertec.at>
	 <CAB+=1TVGKBNTbEJC8=hx4DACO=T4uDiaZGKhwirpU62fUQ=UEQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/7beda414a58970f917acf7959810cd9eeb94af89.camel%40cybertec.at>
Precedence: bulk

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> wro=
te:
> > On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:
> > > due to postgres limitations we are unable to have this unique constra=
int or primary key
> > > only on the transaction_id column, we have to include transaction_tim=
estamp 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?=C2=A0
> >=20
> > No, you cannot have both.
> >=20
> > Usually the solution is to *not* create a primary key on the partitione=
d table
> > and instead create a primary key on each partition.
> >=20
> > 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 parti=
tion.
>=20
> 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=C2=A0to the parent table partitions. And in tha=
t case , can we follow
> this as best practices to not have the=C2=A0primary=C2=A0keys defined at =
the tabe level at all, but
> on the partition level only, or there exist any=C2=A0down side to it too?=
 Please suggest.

You can keep the primary key defined on both columns if it is good enough f=
or 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 tw=
o rows could
be in the same partition...

Also, if you need a foreign key pointing *to* the partitioned table, you ca=
nnot 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 betwe=
en 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 t=
he 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=C2=A0thoughts=C2=
=A0on these?

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

Yours,
Laurenz Albe