public inbox for [email protected]  

help / color / mirror / Atom feed

From: veem v <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Partitioning and unique key
Date: Tue, 3 Sep 2024 10:39:42 +0530
Message-ID: <CAB+=1TV9cc6401SB3L2enAhT2TvHxsHjbwit6egobWzOKaASRg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
	<[email protected]>
	<CAB+=1TVGKBNTbEJC8=hx4DACO=T4uDiaZGKhwirpU62fUQ=UEQ@mail.gmail.com>
	<[email protected]>

On Tue, 3 Sept 2024 at 01:14, Laurenz Albe <[email protected]> wrote:

>
> 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.
>
>
Thank You so much.
As you rightly said *"they will make it more difficult to detach a
partition." , *we are really seeing a longer time when detaching parent
table partitions.It runs forever sometimes. So do you mean it's because we
have primary key defined table level or it's because we have FK defined in
table level (for multiple child tables which are also partitioned)?

 We were thinking it's because we have FK defined on tablelevel , so we
were planning to make the FK on partition level. But as you just pointed
now , even keeping the PK on table level will also make the detach
partition slow? I understand, for detaching partitions , it may be scanning
while child because of the FK defined on the table level. but i am unable
to understand how the table level PK impacts the detach partition from
parent here.

My understanding is PK can only be created on table level but not on the
partition level. On the partition level we only can have a "unique index"
defined. Correct me if my understanding is wrong.

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Partitioning and unique key
  In-Reply-To: <CAB+=1TV9cc6401SB3L2enAhT2TvHxsHjbwit6egobWzOKaASRg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox