Обсуждение: Partitioning, Identity and Uniqueness (given pg 16 changes)
Hi,
I have a table that is capturing what is, basically, time series data. We use identity column as primary key.
I'm considering the potential to partition it by the "source" of the time series (100s .. 1000s of sources) as often (but not always) use of this table is to get the time series for one source.
I note that in Postgresql 16 identity column handling in partitioned tables has been aligned to the view that the partitioned table as a whole is a single relation (and so a unique identity across partitions). This makes sense.
Now, I want to partition but not by that identity column. But I have to include the identity in the partitioning key (if I have it as the pk) so that the uniqueness can be checked. But - using a (shared across all partition tables) identity sequence should be enough to ensure uniqueness without this.
Currently I need to define my partitioned table as something like
#create table tpart (id int generated always as identity not null, src int, data varchar) partition by hash(src);
#create table t_p001 partition of tpart for values with (modulus 32, remainder 0) primary key id;
#create table t_p002 partition of tpart for values with (modulus 32, remainder 1) primary key id;
#...
#create table t_p031 partition of tpart for values with (modulus 32, remainder 31) primary key id;
Note there is no pk on the partitioned table, but the (in practice unique across all partitions) id identity column is pk of each / every partition.
The src column is not indexed at all in this example (it could be of course, but one of the things we are trying to get out of this is high insert performance for concurrent inserts from multiple venues).
Two things:
1) Is my attempt to improve performance of insert AND (as the number of sources goes up, as it has) querying which is invariably by id but it is easy to include "and src = x" in the majority of usage (the other common case is to query across all sources, asking for a set is unusual) a reasonable approach?
2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid?
Thanks
Darryl.
On Sunday, February 18, 2024, Darryl Green <darryl.green@gmail.com> wrote:
I note that in Postgresql 16 identity column handling in partitioned tables has been aligned to the view that the partitioned table as a whole is a single relation (and so a unique identity across partitions). This makes sense.
Where did you find this documented? It makes sense as that is indeed how a partitioned table should behave but given the constraint definitely wasn’t a foregone conclusion not needing documentation.
Now, I want to partition but not by that identity column. But I have to include the identity in the partitioning key (if I have it as the pk) so that the uniqueness can be checked. But - using a (shared across all partition tables) identity sequence should be enough to ensure uniqueness without this.
A sequences is a value generator - its state can be modified so that numbers it previously issued are issued again. Thus a sequence implies nothing about whether the thing using it is unique. You need a constraint to do that and as you note unique constraints can only enforce a single partition.
In short, you can choose to rely on the expected usage/behavior of a sequence defined this way and forgo the enforced PK constraint.
David J.
On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote: > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioneddefinition of the table) once to document and enforce that the partitions simply inherit the id pk. This wouldseem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be definedas pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid? Unfortunately, it's not as easy as you think. It's not a matter of dropping the check that requires all PRIMARY KEY columns are present in the PARTITION BY clause. For this to work a *single* index (i.e. non-partitioned index) would have to index all partitions. Otherwise, how would Postgres ensure that the value being inserted doesn't exist in some other partition? The problem with a single index is that it kinda defeats the purpose of partitioning, i.e., "my table is large and I want to split it up". Operations such as DETACH PARTITION would have to become more than just a metadata operation when you consider having to trawl through the index and remove all records belonging to a single partition. It may be possible to still have it work by doing a speculative record in the index for the target table then go and check all of the other indexes before marking the speculative entry as valid. I think it would be very tricky to make it work well, however. Imagine how expensive ATTACH PARTITION would be! There are probably other race conditions I've not thought about too. Likely, we'd get more complaints about this being a terrible feature than we do due to the fact that it's unsupported. David
On 19.02.24 04:32, Darryl Green wrote: > I note that in Postgresql 16 identity column handling in partitioned > tables has been aligned to the view that the partitioned table as a > whole is a single relation (and so a unique identity across partitions). > This makes sense. The change that I think you are referring to is in the not-yet-released version 17.
1) Is my attempt to improve performance of insert AND (as the number of sources goes up, as it has) querying which is invariably by id but it is easy to include "and src = x" in the majority of usage (the other common case is to query across all sources, asking for a set is unusual) a reasonable approach?2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid?
One of the potential solutions could be to include src into your partitioning key as follows.
create table tpart (id int generated always as identity not null, src int, data varchar, primary key (id, src)) partition by hash(src) ;
create table tpart_p001 partition of ttest_part for values with (modulus 4, remainder 0);
...
create table tpart_p032 partition of ttest_part for values with (modulus 32, remainder 31);
App side changes required:
- Ensure all queries come with src as a filter, otherwise it could backfire performance due to partition pruning overhead.
Here is how it could help:
- "generated always as identity" for id would ensure unique id and src can be used for hash partitioning.
- insert performance may improve if you are seeing resource contention related wait events during insert.
- it would greatly help with the access pattern querying time series for single src
Thanks,
Ketan
On Sun, Feb 18, 2024 at 8:23 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote:
> 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid?
Unfortunately, it's not as easy as you think. It's not a matter of
dropping the check that requires all PRIMARY KEY columns are present
in the PARTITION BY clause. For this to work a *single* index (i.e.
non-partitioned index) would have to index all partitions. Otherwise,
how would Postgres ensure that the value being inserted doesn't exist
in some other partition?
The problem with a single index is that it kinda defeats the purpose
of partitioning, i.e., "my table is large and I want to split it up".
Operations such as DETACH PARTITION would have to become more than
just a metadata operation when you consider having to trawl through
the index and remove all records belonging to a single partition.
It may be possible to still have it work by doing a speculative record
in the index for the target table then go and check all of the other
indexes before marking the speculative entry as valid. I think it
would be very tricky to make it work well, however. Imagine how
expensive ATTACH PARTITION would be! There are probably other race
conditions I've not thought about too. Likely, we'd get more
complaints about this being a terrible feature than we do due to the
fact that it's unsupported.
David
On Mon, 19 Feb 2024 at 14:23, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote:
> > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid?
>
> Unfortunately, it's not as easy as you think. It's not a matter of
> dropping the check that requires all PRIMARY KEY columns are present
> in the PARTITION BY clause. For this to work a *single* index (i.e.
> non-partitioned index) would have to index all partitions.
Now that would be bad. And I do understand that is exactly why in the general case the existing rule is correct. You snipped my specific usage/question which was about the uniqueness of a column that was specified as "id int generated always as identity not null" and relying on all partitions using the same sequence to populate to guarantee that there are never(*) duplicates in other partitions.
> Otherwise,
> how would Postgres ensure that the value being inserted doesn't exist
> in some other partition?
It is, in practice, ensured by the fact that it is ensured that all partitions are using the same IDENTITY source of (unique) key values.
Yes I realise that even with this it is possible to force a value OVERRIDING SYSTEM VALUE and of course by changing constraints over time. I can imagine this could make some changes expensive.
>
> The problem with a single index is that it kinda defeats the purpose
> of partitioning, i.e., "my table is large and I want to split it up".
Exactly.
But, given that people who know more than I decided to make IDENTITY work as a "shared" property of partitions (and invested effort to do it) surely they didn't mean for it to not be used. It can be used in circumstances where partitioning by the identity makes sense. It can't be used (without running afoul of this issue) in the case I described. So either I'm "doing it wrong" - suggestions welcome, or the "work around" I described of putting the primary key on each partition explicitly is "the right way to do it". I assume you are implicitly saying - yes it is?
> Operations such as DETACH PARTITION would have to become more than
> just a metadata operation when you consider having to trawl through
> the index and remove all records belonging to a single partition.
Yes. If there was a single index created, this is one way it would be bad.
Which is what the existing "pk or unique column must be in the partitioning key" rule is about. It allows relaxing the check and hence the index to per partition as we know because we are partitioning by that column, that we only need to check that one partition (index).
My question was whether it would be in any way practical to make the "specification" that there is a unique or pk constraint on a column of a partitioned table be treated as an instruction to "trust" the uniqueness guarantee that is implicit in a IDENTITY column that is using a shared SEQUENCE (as of the change discussed here) across partitions - so you don't know which partition a given sequence value will be in (if any) but you do know it won't be in multiple without, actually, using ANY indexes. Hence you don't "need" the completely impractical "shared across partitions" index to enforce uniqueness (well - enforce until someone does something creative/silly using OVERRIDING SYSTEM VALUE). One could with yet more special casing make any such OVERRIDING SYSTEM VALUE usage enforce the constraint - but at some considerable expense - this is where you would have to check every partition (index) to confirm uniqueness.
I guess if you take my argument far enough you arrive at a position of never creating an implicit index on any column specified as "id int generated always as identity unique not null" or similar .. but incurring the cost of the scan to prove unique in the rare case above - regardless of partitioning. That wasn't the proposal. Partitioning implies some sort of tradeoff - this specific one seemed perhaps common enough to support.
>
> It may be possible to still have it work by doing a speculative record
> in the index for the target table then go and check all of the other
> indexes before marking the speculative entry as valid.
It is always valid - except in the OVERRIDING SYSTEM VALUE case - no?
> I think it
> would be very tricky to make it work well, however. Imagine how
> expensive ATTACH PARTITION would be!
True.
> There are probably other race
> conditions I've not thought about too. Likely, we'd get more
> complaints about this being a terrible feature than we do due to the
> fact that it's unsupported.
I'm not sure it is a "terrible feature" in the exact case of unique IDENTITY columns (which was the only case I expected it to be reasonable for).
I'm also far from an expert.
>
> David
On Mon, 19 Feb 2024 at 14:23, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote:
> > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid?
>
> Unfortunately, it's not as easy as you think. It's not a matter of
> dropping the check that requires all PRIMARY KEY columns are present
> in the PARTITION BY clause. For this to work a *single* index (i.e.
> non-partitioned index) would have to index all partitions. Otherwise,
> how would Postgres ensure that the value being inserted doesn't exist
> in some other partition?
>
> The problem with a single index is that it kinda defeats the purpose
> of partitioning, i.e., "my table is large and I want to split it up".
> Operations such as DETACH PARTITION would have to become more than
> just a metadata operation when you consider having to trawl through
> the index and remove all records belonging to a single partition.
>
> It may be possible to still have it work by doing a speculative record
> in the index for the target table then go and check all of the other
> indexes before marking the speculative entry as valid. I think it
> would be very tricky to make it work well, however. Imagine how
> expensive ATTACH PARTITION would be! There are probably other race
> conditions I've not thought about too. Likely, we'd get more
> complaints about this being a terrible feature than we do due to the
> fact that it's unsupported.
>
> David
>
> On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote:
> > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid?
>
> Unfortunately, it's not as easy as you think. It's not a matter of
> dropping the check that requires all PRIMARY KEY columns are present
> in the PARTITION BY clause. For this to work a *single* index (i.e.
> non-partitioned index) would have to index all partitions.
Now that would be bad. And I do understand that is exactly why in the general case the existing rule is correct. You snipped my specific usage/question which was about the uniqueness of a column that was specified as "id int generated always as identity not null" and relying on all partitions using the same sequence to populate to guarantee that there are never(*) duplicates in other partitions.
> Otherwise,
> how would Postgres ensure that the value being inserted doesn't exist
> in some other partition?
It is, in practice, ensured by the fact that it is ensured that all partitions are using the same IDENTITY source of (unique) key values.
Yes I realise that even with this it is possible to force a value OVERRIDING SYSTEM VALUE and of course by changing constraints over time. I can imagine this could make some changes expensive.
>
> The problem with a single index is that it kinda defeats the purpose
> of partitioning, i.e., "my table is large and I want to split it up".
Exactly.
But, given that people who know more than I decided to make IDENTITY work as a "shared" property of partitions (and invested effort to do it) surely they didn't mean for it to not be used. It can be used in circumstances where partitioning by the identity makes sense. It can't be used (without running afoul of this issue) in the case I described. So either I'm "doing it wrong" - suggestions welcome, or the "work around" I described of putting the primary key on each partition explicitly is "the right way to do it". I assume you are implicitly saying - yes it is?
> Operations such as DETACH PARTITION would have to become more than
> just a metadata operation when you consider having to trawl through
> the index and remove all records belonging to a single partition.
Yes. If there was a single index created, this is one way it would be bad.
Which is what the existing "pk or unique column must be in the partitioning key" rule is about. It allows relaxing the check and hence the index to per partition as we know because we are partitioning by that column, that we only need to check that one partition (index).
My question was whether it would be in any way practical to make the "specification" that there is a unique or pk constraint on a column of a partitioned table be treated as an instruction to "trust" the uniqueness guarantee that is implicit in a IDENTITY column that is using a shared SEQUENCE (as of the change discussed here) across partitions - so you don't know which partition a given sequence value will be in (if any) but you do know it won't be in multiple without, actually, using ANY indexes. Hence you don't "need" the completely impractical "shared across partitions" index to enforce uniqueness (well - enforce until someone does something creative/silly using OVERRIDING SYSTEM VALUE). One could with yet more special casing make any such OVERRIDING SYSTEM VALUE usage enforce the constraint - but at some considerable expense - this is where you would have to check every partition (index) to confirm uniqueness.
I guess if you take my argument far enough you arrive at a position of never creating an implicit index on any column specified as "id int generated always as identity unique not null" or similar .. but incurring the cost of the scan to prove unique in the rare case above - regardless of partitioning. That wasn't the proposal. Partitioning implies some sort of tradeoff - this specific one seemed perhaps common enough to support.
>
> It may be possible to still have it work by doing a speculative record
> in the index for the target table then go and check all of the other
> indexes before marking the speculative entry as valid.
It is always valid - except in the OVERRIDING SYSTEM VALUE case - no?
> I think it
> would be very tricky to make it work well, however. Imagine how
> expensive ATTACH PARTITION would be!
True.
> There are probably other race
> conditions I've not thought about too. Likely, we'd get more
> complaints about this being a terrible feature than we do due to the
> fact that it's unsupported.
I'm not sure it is a "terrible feature" in the exact case of unique IDENTITY columns (which was the only case I expected it to be reasonable for).
I'm also far from an expert.
>
> David
On Mon, 19 Feb 2024 at 14:23, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote:
> > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid?
>
> Unfortunately, it's not as easy as you think. It's not a matter of
> dropping the check that requires all PRIMARY KEY columns are present
> in the PARTITION BY clause. For this to work a *single* index (i.e.
> non-partitioned index) would have to index all partitions. Otherwise,
> how would Postgres ensure that the value being inserted doesn't exist
> in some other partition?
>
> The problem with a single index is that it kinda defeats the purpose
> of partitioning, i.e., "my table is large and I want to split it up".
> Operations such as DETACH PARTITION would have to become more than
> just a metadata operation when you consider having to trawl through
> the index and remove all records belonging to a single partition.
>
> It may be possible to still have it work by doing a speculative record
> in the index for the target table then go and check all of the other
> indexes before marking the speculative entry as valid. I think it
> would be very tricky to make it work well, however. Imagine how
> expensive ATTACH PARTITION would be! There are probably other race
> conditions I've not thought about too. Likely, we'd get more
> complaints about this being a terrible feature than we do due to the
> fact that it's unsupported.
>
> David
On Mon, 19 Feb 2024 at 17:31, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 19.02.24 04:32, Darryl Green wrote:
> > I note that in Postgresql 16 identity column handling in partitioned
> > tables has been aligned to the view that the partitioned table as a
> > whole is a single relation (and so a unique identity across partitions).
> > This makes sense.
>
> The change that I think you are referring to is in the not-yet-released
> version 17.
>
> On 19.02.24 04:32, Darryl Green wrote:
> > I note that in Postgresql 16 identity column handling in partitioned
> > tables has been aligned to the view that the partitioned table as a
> > whole is a single relation (and so a unique identity across partitions).
> > This makes sense.
>
> The change that I think you are referring to is in the not-yet-released
> version 17.
Oh! Oops!
On Monday, February 19, 2024, Darryl Green <darryl.green@gmail.com> wrote:
> It may be possible to still have it work by doing a speculative record
> in the index for the target table then go and check all of the other
> indexes before marking the speculative entry as valid.
It is always valid - except in the OVERRIDING SYSTEM VALUE case - no?
False.
ALTER TABLE … ALTER id RESTART 1;
David J.
On Monday, February 19, 2024, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, February 19, 2024, Darryl Green <darryl.green@gmail.com> wrote:
> It may be possible to still have it work by doing a speculative record
> in the index for the target table then go and check all of the other
> indexes before marking the speculative entry as valid.
It is always valid - except in the OVERRIDING SYSTEM VALUE case - no?False.ALTER TABLE … ALTER id RESTART 1;
Even without a reset as soon as you override system value at least one future non-override insert is going to fail when the sequence catches up to whatever that non-overridden value was.
David J.
On Mon, 19 Feb 2024 at 22:07, Darryl Green <darryl.green@gmail.com> wrote: > > On Mon, 19 Feb 2024 at 14:23, David Rowley <dgrowleyml@gmail.com> wrote: > > > > On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote: > > > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioneddefinition of the table) once to document and enforce that the partitions simply inherit the id pk. This wouldseem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be definedas pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid? > > > > Unfortunately, it's not as easy as you think. It's not a matter of > > dropping the check that requires all PRIMARY KEY columns are present > > in the PARTITION BY clause. For this to work a *single* index (i.e. > > non-partitioned index) would have to index all partitions. > > Now that would be bad. And I do understand that is exactly why in the general case the existing rule is correct. You snippedmy specific usage/question which was about the uniqueness of a column that was specified as "id int generated alwaysas identity not null" and relying on all partitions using the same sequence to populate to guarantee that there arenever(*) duplicates in other partitions. Using such proofs that an index can only ever contain unique values is just not worth talking about. It's just nowhere near project standard. We'd just be forever answering questions on this list from people with unique violation problems. There are just too many reasons to list for ways this could go wrong. David
On Sun, Feb 18, 2024 at 10:32 PM Darryl Green <darryl.green@gmail.com> wrote:
I am struggling to see the overall benefit of partitioning here. How many total rows and inserts per second, anyway?
1) Is my attempt to improve performance of insert AND (as the number of sources goes up, as it has) querying which is invariably by id but it is easy to include "and src = x" in the majority of usage (the other common case is to query across all sources, asking for a set is unusual) a reasonable approach?
I am struggling to see the overall benefit of partitioning here. How many total rows and inserts per second, anyway?
If the app already knows the src (as evidenced by adding in "and src = x"), and you query by id, why would you partition by src?
It may help us all to take a step back and describe the problem here with specific timing numbers (for a slow INSERT rate maybe?) rather than trying to force one particular solution to work.
Cheers,
Greg
I have a table that is capturing what is, basically, time series data
Cheers,
Greg