public inbox for [email protected]
help / color / mirror / Atom feedRe: Partitioning and unique key
5+ messages / 2 participants
[nested] [flat]
* Re: Partitioning and unique key
@ 2024-09-02 13:43 Laurenz Albe <[email protected]>
2024-09-02 16:09 ` Re: Partitioning and unique key veem v <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-09-02 13:43 UTC (permalink / raw)
To: veem v <[email protected]>; pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partitioning and unique key
2024-09-02 13:43 Re: Partitioning and unique key Laurenz Albe <[email protected]>
@ 2024-09-02 16:09 ` veem v <[email protected]>
2024-09-02 19:44 ` Re: Partitioning and unique key Laurenz Albe <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: veem v @ 2024-09-02 16:09 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general <[email protected]>
On Mon, 2 Sept 2024 at 19:13, Laurenz Albe <[email protected]> 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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partitioning and unique key
2024-09-02 13:43 Re: Partitioning and unique key Laurenz Albe <[email protected]>
2024-09-02 16:09 ` Re: Partitioning and unique key veem v <[email protected]>
@ 2024-09-02 19:44 ` Laurenz Albe <[email protected]>
2024-09-03 05:09 ` Re: Partitioning and unique key veem v <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-09-02 19:44 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: pgsql-general <[email protected]>
On Mon, 2024-09-02 at 21:39 +0530, veem v wrote:
> On Mon, 2 Sept 2024 at 19:13, Laurenz Albe <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partitioning and unique key
2024-09-02 13:43 Re: Partitioning and unique key Laurenz Albe <[email protected]>
2024-09-02 16:09 ` Re: Partitioning and unique key veem v <[email protected]>
2024-09-02 19:44 ` Re: Partitioning and unique key Laurenz Albe <[email protected]>
@ 2024-09-03 05:09 ` veem v <[email protected]>
2024-09-03 05:53 ` Re: Partitioning and unique key Laurenz Albe <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: veem v @ 2024-09-03 05:09 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general <[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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partitioning and unique key
2024-09-02 13:43 Re: Partitioning and unique key Laurenz Albe <[email protected]>
2024-09-02 16:09 ` Re: Partitioning and unique key veem v <[email protected]>
2024-09-02 19:44 ` Re: Partitioning and unique key Laurenz Albe <[email protected]>
2024-09-03 05:09 ` Re: Partitioning and unique key veem v <[email protected]>
@ 2024-09-03 05:53 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2024-09-03 05:53 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: pgsql-general <[email protected]>
On Tue, 2024-09-03 at 10:39 +0530, veem v wrote:
> 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)?
I'd say it is because of the foreign key.
If you have a foreign key that points to a partitioned table, and you detach
a partition, PostgreSQL has to verify that that won't violate the constraint,
so it has to scan the tables, which will take time if the partitions are large.
> We were thinking it's because we have FK defined on tablelevel , so we were
> planning to make the FK on partition level.
Good move.
> 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.
No, a primary key on the partitioned table won't be a problem for performance.
My concern was that if what you really would like is "id" to be unique, how does
a primary key on (id, some_timestamp) benefit you?
> 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.
No, you can define a primary key on the partition. That is, if you have no
primary key on the partitioned table. A primary key on the partitioned table
is a primary key on each partition, and a table can only have a single primary
key, so adding another primary key on the partition would cause an error.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-09-03 05:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-02 13:43 Re: Partitioning and unique key Laurenz Albe <[email protected]>
2024-09-02 16:09 ` veem v <[email protected]>
2024-09-02 19:44 ` Laurenz Albe <[email protected]>
2024-09-03 05:09 ` veem v <[email protected]>
2024-09-03 05:53 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox