pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartition column should be part of PK
13+ messages / 8 participants
[nested] [flat]
* Partition column should be part of PK
@ 2021-06-25 03:10 Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 2 replies; 13+ messages in thread
From: Nagaraj Raj @ 2021-06-25 03:10 UTC (permalink / raw)
To: pgsql-performance
we have some partitioned tables with inherence and planning to migrate them to the declaration.
Table DDL:
CREATE TABLE c_account_p
(
billing_account_guid character varying(40) NOT NULL,
ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
load_dttm timestamp(6) without time zone NOT NULL,
ban integer NOT NULL,
CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
) PARTITION by RANGE(load_dttm);
When I try the create table, it's throwing below error:
ERROR: insufficient columns in the PRIMARY KEY constraint definition
DETAIL: PRIMARY KEY constraint on table "l_billing_account_p" lacks column "load_dttm" which is part of the partition key.
SQL state: 0A000
Is it mandatory/necessary that the partition column should be a primary key? cause if I include load_dttm as PK then it's working fine.db<>fiddle
If the partition column should be supposed to be a PK, it's challenging to create a partition by range with the date column, cause the load_dttm column chances to have duplicate if data loaded COPY or INSERT.INSERT INTO c_account_p SELECT * from c_account_p_bkp ON CONFLICT (billing_account_guid,ban,load_dttm) DO UPDATE SET 'some stuff..'
If I receive billing_account_guid, ban combination with different load_dttm then it will end up with duplicate keys.
Could some please help me to understand this scenario?
Thanks.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-06-25 04:22 Justin Pryzby <pryzby@telsasoft.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 0 replies; 13+ messages in thread
From: Justin Pryzby @ 2021-06-25 04:22 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance
Declarative partitioning was new in v10
In v11, it was allowed to create an index on a partitioned table, including
unique indexes.
However it's not a "global" index - instead, it's an "inherited" index.
For a unique index, uniqueness is enforced within each individual index.
And so global uniqueness is only guaranteed if the partition key is included in
the index.
On Fri, Jun 25, 2021 at 03:10:07AM +0000, Nagaraj Raj wrote:
> CREATE TABLE c_account_p (
> billing_account_guid character varying(40) NOT NULL,
> ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
> load_dttm timestamp(6) without time zone NOT NULL,
> ban integer NOT NULL,
> CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
> ) PARTITION by RANGE(load_dttm);
> When I try the create table, it's throwing below error:
> ERROR: insufficient columns in the PRIMARY KEY constraint definition
> DETAIL: PRIMARY KEY constraint on table "l_billing_account_p" lacks column "load_dttm" which is part of the partition key.
>
> If the partition column should be supposed to be a PK, it's challenging to create a partition by range with the date column, cause the load_dttm column chances to have duplicate if data loaded COPY or INSERT.INSERT INTO c_account_p SELECT * from c_account_p_bkp ON CONFLICT (billing_account_guid,ban,load_dttm) DO UPDATE SET 'some stuff..'
>
> If I receive billing_account_guid, ban combination with different load_dttm then it will end up with duplicate keys.
It sounds like you want a unique index on (billing_account_guid, ban) to
support INSERT ON CONFLICT. If DO UPDATE SET will never move tuples to a new
partittion, then you could do the INSERT ON CONFLICT on the partition rather
than its parent.
But it cannot be a unique, "partitioned" index, without including load_dttm.
--
Justin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-08 22:17 Justin Pryzby <pryzby@telsasoft.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 1 reply; 13+ messages in thread
From: Justin Pryzby @ 2021-07-08 22:17 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance
If I'm not wrong, this is the same thing you asked 2 week ago.
If so, why not continue the conversation on the same thread, and why not
reference the old thread ?
I went to the effort to find the old conversation.
https://www.postgresql.org/message-id/20210625042228.GJ29179@telsasoft.com
If declaratively partitioned tables and partitioned indexes don't do what you
wanted, then you should consider not using them for this.
On Fri, Jun 25, 2021 at 03:10:07AM +0000, Nagaraj Raj wrote:
> we have some partitioned tables with inherence and planning to migrate them to the declaration.
> Table DDL:
> CREATE TABLE c_account_p
> (
> billing_account_guid character varying(40) NOT NULL,
> ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
> load_dttm timestamp(6) without time zone NOT NULL,
> ban integer NOT NULL,
> CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
> ) PARTITION by RANGE(load_dttm);
> When I try the create table, it's throwing below error:
> ERROR: insufficient columns in the PRIMARY KEY constraint definition
> DETAIL: PRIMARY KEY constraint on table "l_billing_account_p" lacks column "load_dttm" which is part of the partition key.
> SQL state: 0A000
> Is it mandatory/necessary that the partition column should be a primary key? cause if I include load_dttm as PK then it's working fine.db<>fiddle
> If the partition column should be supposed to be a PK, it's challenging to create a partition by range with the date column, cause the load_dttm column chances to have duplicate if data loaded COPY or INSERT.INSERT INTO c_account_p SELECT * from c_account_p_bkp ON CONFLICT (billing_account_guid,ban,load_dttm) DO UPDATE SET 'some stuff..'
> If I receive billing_account_guid, ban combination with different load_dttm then it will end up with duplicate keys.
> Could some please help me to understand this scenario?
> Thanks.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-08 22:28 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2021-07-08 22:28 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Nagaraj Raj <nagaraj.sf@yahoo.com>; pgsql-performance
On 2021-Jul-08, Justin Pryzby wrote:
> If I'm not wrong, this is the same thing you asked 2 week ago.
>
> If so, why not continue the conversation on the same thread, and why not
> reference the old thread ?
>
> I went to the effort to find the old conversation.
> https://www.postgresql.org/message-id/20210625042228.GJ29179@telsasoft.com
Actually, it looks like you're replying to the same email you replied to
two weeks ago.
--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Partition column should be part of PK
@ 2021-07-08 23:12 Mike Sofen <msofen@runbox.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 13+ messages in thread
From: Mike Sofen @ 2021-07-08 23:12 UTC (permalink / raw)
To: 'Alvaro Herrera' <alvherre@alvh.no-ip.org>; 'Justin Pryzby' <pryzby@telsasoft.com>; +Cc: 'Nagaraj Raj' <nagaraj.sf@yahoo.com>; pgsql-performance
I believe this thread qualifies for the funniest thread of 2021 (so far). And yes, this is a top post. :-)
Mike Sofen
-----Original Message-----
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Sent: Thursday, July 08, 2021 3:29 PM
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Nagaraj Raj <nagaraj.sf@yahoo.com>; pgsql-performance@postgresql.org
Subject: Re: Partition column should be part of PK
On 2021-Jul-08, Justin Pryzby wrote:
> If I'm not wrong, this is the same thing you asked 2 week ago.
>
> If so, why not continue the conversation on the same thread, and why
> not reference the old thread ?
>
> I went to the effort to find the old conversation.
> https://www.postgresql.org/message-id/20210625042228.GJ29179@telsasoft
> .com
Actually, it looks like you're replying to the same email you replied to two weeks ago.
--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-09 03:32 Nagaraj Raj <nagaraj.sf@yahoo.com>
parent: Mike Sofen <msofen@runbox.com>
0 siblings, 2 replies; 13+ messages in thread
From: Nagaraj Raj @ 2021-07-09 03:32 UTC (permalink / raw)
To: Mike Sofen <msofen@runbox.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
My apologies for making confusion with new thread. Yes its same issue related to earlier post.
I was trying to figure out how to ensure unique values for columns (billing_account_guid, ban). If i add partition key to constraint , it wont be possible what im looking for.
My use case as below
INSERT INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) DO UPDATE SET something…
Or
INSERT INTO t1 SELECT * from t2 ON CONFLICT constraint (pk or uk)(billing_account_guid,ban) DO UPDATE SET something…
Thanks
Sent from Yahoo Mail for iPhone
On Thursday, July 8, 2021, 7:12 PM, Mike Sofen <msofen@runbox.com> wrote:
I believe this thread qualifies for the funniest thread of 2021 (so far). And yes, this is a top post. :-)
Mike Sofen
-----Original Message-----
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Sent: Thursday, July 08, 2021 3:29 PM
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Nagaraj Raj <nagaraj.sf@yahoo.com>; pgsql-performance@postgresql.org
Subject: Re: Partition column should be part of PK
On 2021-Jul-08, Justin Pryzby wrote:
> If I'm not wrong, this is the same thing you asked 2 week ago.
>
> If so, why not continue the conversation on the same thread, and why
> not reference the old thread ?
>
> I went to the effort to find the old conversation.
> https://www.postgresql.org/message-id/20210625042228.GJ29179@telsasoft
> .com
Actually, it looks like you're replying to the same email you replied to two weeks ago.
--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-09 03:52 Christophe Pettus <xof@thebuild.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 1 reply; 13+ messages in thread
From: Christophe Pettus @ 2021-07-09 03:52 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance
> On Jul 8, 2021, at 20:32, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
>
> My apologies for making confusion with new thread. Yes its same issue related to earlier post.
>
> I was trying to figure out how to ensure unique values for columns (billing_account_guid, ban). If i add partition key to constraint , it wont be possible what im looking for.
>
> My use case as below
>
> INSERT INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) DO UPDATE SET something…
>
> Or
>
> INSERT INTO t1 SELECT * from t2 ON CONFLICT constraint (pk or uk)(billing_account_guid,ban) DO UPDATE SET something…
Right now, PostgreSQL does not support unique indexes on partitioned tables (that operate across all partitions) unless the partition key is included in the index definition. If it didn't have that requirement, it would have to independently (and in a concurrency-supported way) scan every partition individually to see if there is a duplicate key violation in any of the partitions, and the machinery to do that does not exist right now.
If the goal is to make sure there is only one (billing_account_guid, ban, date) combination across the entire partition set, you can create an index unique index on the partitioned set as (billing_account_guid, ban, date), and INSERT ... ON CONFLICT DO NOTHING works properly then.
If the goal is to make sure there is only one (billing_account_uid, ban) in any partition regardless of date, you'll need to do something more sophisticated to make sure that two sessions don't insert an (billing_account_uid, ban) value into two different partitions. This isn't a great fit for table partitioning, and you might want to reconsider if partitioning the table is the right answer here. If you *must* have table partitioning, a possible algorithm is:
-- Start a transaction
-- Hash the (billing_account_uid, ban) key into a 64 bit value.
-- Use that 64 bit value as a key to a call to pg_advisory_xact_lock() [1] to, in essence, create a signal to any other transaction attempting to insert that pair that it is being modified.
-- SELECT on that pair to make sure one does not already exist.
-- If one does not, do the INSERT.
-- Commit, which releases the advisory lock.
This doesn't provide quite the same level of uniqueness that a cross-partition index would, but if this is the only code path that does the INSERT, it should keep duplicate from showing up in different partitions.
[1] https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-12 00:36 Nagaraj Raj <nagaraj.sf@yahoo.com>
parent: Christophe Pettus <xof@thebuild.com>
0 siblings, 2 replies; 13+ messages in thread
From: Nagaraj Raj @ 2021-07-12 00:36 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-performance
personally, I feel this design is very bad compared to other DB servers.
> If the goal is to make sure there is only one (billing_account_uid, ban) in any partition regardless of date, you'll need to do something more > sophisticated to make sure that two sessions don't insert an (billing_account_uid, ban) value into two different partitions. This isn't a great fit > for table partitioning, and you might want to reconsider if partitioning the table is the right answer here. If you *must* have table partitioning, a > possible algorithm is:
yes, this is my use case.
can I use some trigger on the partition table before inserting the call that function this one handle conflict?
CREATE or replace FUNCTION insert_trigger() RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOFAS $BODY$DECLARE conn_name text; c_table TEXT; t_schema text; c_table1 text; m_table1 text; BEGIN c_table1 := TG_TABLE_NAME; t_schema := TG_TABLE_SCHEMA; m_table1 := t_schema||'.'||TG_TABLE_NAME; SELECT conname FROM pg_constraint WHERE conrelid = TG_TABLE_NAME ::regclass::oid and contype = 'u' into conn_name; execute 'insert into '|| m_table1 || ' values ' || new.* || ' on conflict on constraint ' || conn_name || ' do nothing -- or somthing'; RETURN null; end; $BODY$;
CREATE TRIGGER insert BEFORE INSERT ON t4 FOR EACH ROW WHEN (pg_trigger_depth() < 1) EXECUTE FUNCTION insert_trigger(); CREATE TRIGGER insert BEFORE INSERT ON t3 FOR EACH ROW WHEN (pg_trigger_depth() < 1) EXECUTE FUNCTION insert_trigger(); .. so on ..
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=bcfdfc26685ffb498bf82e6d50da95e3
Please suggest.
Thanks,Rj
On Thursday, July 8, 2021, 08:52:35 PM PDT, Christophe Pettus <xof@thebuild.com> wrote:
> On Jul 8, 2021, at 20:32, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
>
> My apologies for making confusion with new thread. Yes its same issue related to earlier post.
>
> I was trying to figure out how to ensure unique values for columns (billing_account_guid, ban). If i add partition key to constraint , it wont be possible what im looking for.
>
> My use case as below
>
> INSERT INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) DO UPDATE SET something…
>
> Or
>
> INSERT INTO t1 SELECT * from t2 ON CONFLICT constraint (pk or uk)(billing_account_guid,ban) DO UPDATE SET something…
Right now, PostgreSQL does not support unique indexes on partitioned tables (that operate across all partitions) unless the partition key is included in the index definition. If it didn't have that requirement, it would have to independently (and in a concurrency-supported way) scan every partition individually to see if there is a duplicate key violation in any of the partitions, and the machinery to do that does not exist right now.
If the goal is to make sure there is only one (billing_account_guid, ban, date) combination across the entire partition set, you can create an index unique index on the partitioned set as (billing_account_guid, ban, date), and INSERT ... ON CONFLICT DO NOTHING works properly then.
If the goal is to make sure there is only one (billing_account_uid, ban) in any partition regardless of date, you'll need to do something more sophisticated to make sure that two sessions don't insert an (billing_account_uid, ban) value into two different partitions. This isn't a great fit for table partitioning, and you might want to reconsider if partitioning the table is the right answer here. If you *must* have table partitioning, a possible algorithm is:
-- Start a transaction
-- Hash the (billing_account_uid, ban) key into a 64 bit value.
-- Use that 64 bit value as a key to a call to pg_advisory_xact_lock() [1] to, in essence, create a signal to any other transaction attempting to insert that pair that it is being modified.
-- SELECT on that pair to make sure one does not already exist.
-- If one does not, do the INSERT.
-- Commit, which releases the advisory lock.
This doesn't provide quite the same level of uniqueness that a cross-partition index would, but if this is the only code path that does the INSERT, it should keep duplicate from showing up in different partitions.
[1] https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-12 00:38 Justin Pryzby <pryzby@telsasoft.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 0 replies; 13+ messages in thread
From: Justin Pryzby @ 2021-07-12 00:38 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Mike Sofen <msofen@runbox.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-performance
On Fri, Jul 09, 2021 at 03:32:46AM +0000, Nagaraj Raj wrote:
> My apologies for making confusion with new thread. Yes its same issue related to earlier post.
> I was trying to figure out how to ensure unique values for columns (billing_account_guid, ban). If i add partition key to constraint , it wont be possible what im looking for.
> My use case as below
> INSERT INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) DO UPDATE SET something…
>
> Or
> INSERT INTO t1 SELECT * from t2 ON CONFLICT constraint (pk or uk)(billing_account_guid,ban) DO UPDATE SET something…
I'm not sure, but did you see the 2nd half of what I wrote in June ?
lightly edited:
> It sounds like you want a unique index on (billing_account_guid, ban) to
> support INSERT ON CONFLICT. If DO UPDATE SET will never move tuples to a new
> partition, then you could do INSERT ON CONFLICT to a partition rather
> than its parent.
>
> But it cannot be a unique, "partitioned" index, without including load_dttm.
You could try something that doesn't use a parent/partitioned index (as I was
suggesting). Otherwise I think you'd have to partition by something else
involving the unique columns, or not use declarative partitioning, or not use
insert on conflict.
Justin
PS, I'm sorry for my own confusion, but happy if people found it amusing.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-12 00:47 Christophe Pettus <xof@thebuild.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 0 replies; 13+ messages in thread
From: Christophe Pettus @ 2021-07-12 00:47 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance
> On Jul 11, 2021, at 17:36, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
>
> personally, I feel this design is very bad compared to other DB servers.
Patches accepted. The issue is that in order to have a partition-set-wide unique index, the system would have to lock the unique index entries in *all* partitions, not just the target one. This logic does not currently exist, and it's not trivial to implement efficiently.
> can I use some trigger on the partition table before inserting the call that function this one handle conflict?
That doesn't handle the core problem, which is ensuring that two different sessions do not insert the same (billing_account_uid, ban) into two different partitions. That requires some kind of higher-level lock. The example you give isn't required; PostgreSQL will perfectly happily accept a unique constraint on (billing_account_uid, ban) on each partition, and handle attempts to insert a duplicate row correctly (either by returning an error or processing an ON CONFLICT) clause. What that does not prevent is a duplicate (billing_account_uid, ban) in two different partitions.
There's another issue here, which is this design implies that once a particular (billing_account_uid, ban) row is created in the partitioned table, it is never deleted. This means older partitions are never dropped, which means the number of partitions in the table will row unbounded. This is not going to scale well as the number of partitions starts getting very large.
You might consider, instead, hash-partitioning on one of billing_account_uid or ban, or reconsider if partitioning is the right solution here.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-12 00:57 David Rowley <dgrowleyml@gmail.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 2 replies; 13+ messages in thread
From: David Rowley @ 2021-07-12 00:57 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Christophe Pettus <xof@thebuild.com>; pgsql-performance
On Mon, 12 Jul 2021 at 12:37, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> personally, I feel this design is very bad compared to other DB servers.
I'm not sure exactly what you're referring to here as you didn't quote
it, but my guess is you mean our lack of global index support.
Generally, there's not all that much consensus in the community that
this would be a good feature to have. Why do people want to use
partitioning? Many people do it so that they can quickly remove data
that's no longer required with a simple DETACH operation. This is
metadata only and is generally very fast. Another set of people
partition as their tables are very large and they become much easier
to manage when broken down into parts. There's also a group of people
who do it for the improved data locality. Unfortunately, if we had a
global index feature then that requires building a single index over
all partitions. DETACH is no longer a metadata-only operation as we
must somehow invalidate or remove tuples that belong to the detached
partition. The group of people who partitioned to get away from very
large tables now have a very large index. Maybe the only group to get
off lightly here are the data locality group. They'll still have the
same data locality on the heap.
So in short, many of the benefits of partitioning disappear when you
have a global index.
So, why did you partition your data in the first place? If you feel
like you wouldn't mind having a large global index over all partitions
then maybe you're better off just using a non-partitioned table to
store this data.
David
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Partition column should be part of PK
@ 2021-07-12 06:00 Thomas Kellerer <shammat@gmx.net>
parent: David Rowley <dgrowleyml@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Thomas Kellerer @ 2021-07-12 06:00 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
David Rowley schrieb am 12.07.2021 um 02:57:
> Generally, there's not all that much consensus in the community that
> this would be a good feature to have. Why do people want to use
> partitioning? Many people do it so that they can quickly remove data
> that's no longer required with a simple DETACH operation. This is
> metadata only and is generally very fast. Another set of people
> partition as their tables are very large and they become much easier
> to manage when broken down into parts. There's also a group of people
> who do it for the improved data locality. Unfortunately, if we had a
> global index feature then that requires building a single index over
> all partitions. DETACH is no longer a metadata-only operation as we
> must somehow invalidate or remove tuples that belong to the detached
> partition. The group of people who partitioned to get away from very
> large tables now have a very large index. Maybe the only group to get
> off lightly here are the data locality group. They'll still have the
> same data locality on the heap.
>
> So in short, many of the benefits of partitioning disappear when you
> have a global index.
The situations where this is useful are large tables where partitioning
would turn Seq Scans of the whole table into Seq Scans of a partition,
or where it would allow for partition wise joins and still have
foreign keys referencing the partitioned table.
I agree they do have downsides. I only know Oracle as one of those systems
where this is possible, and in general global indexes are somewhat
avoided but there are still situations where they are useful.
E.g. if you want to have foreign keys referencing your partitioned
table and including the partition key in the primary key makes no
sense.
Even though they have disadvantages, I think it would be nice to
have the option to create them.
I know that in the Oracle world, they are used seldomly (precisely
because of the disadvantages you mentioned) but they do have a place.
Thomas
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Partition column should be part of PK
@ 2021-07-12 09:56 Michel SALAIS <msalais@msym.fr>
parent: David Rowley <dgrowleyml@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Michel SALAIS @ 2021-07-12 09:56 UTC (permalink / raw)
To: 'David Rowley' <dgrowleyml@gmail.com>; 'Nagaraj Raj' <nagaraj.sf@yahoo.com>; +Cc: 'Christophe Pettus' <xof@thebuild.com>; pgsql-performance
Hi all,
I think that global indexes could be useful sometimes. That is why Oracle implements them.
Just to mention two benefits that could be required by a lot of people:
- Global uniqueness which shouldn't be in conflict with partitioning
- Performance! Well, when index is on a column which is not the partitioning key. A global index would be better for performance...
Nevertheless, this doesn't go without any price and you have described this very well. That is why Oracle invalidates global indexes when some partitioning maintenance operations are achieved. These indexes have to be rebuilt. But, anyway, such operations could be done "concurrently" or "online"...
Michel SALAIS
-----Message d'origine-----
De : David Rowley <dgrowleyml@gmail.com>
Envoyé : lundi 12 juillet 2021 02:57
À : Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc : Christophe Pettus <xof@thebuild.com>; pgsql-performance@postgresql.org
Objet : Re: Partition column should be part of PK
On Mon, 12 Jul 2021 at 12:37, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> personally, I feel this design is very bad compared to other DB servers.
I'm not sure exactly what you're referring to here as you didn't quote it, but my guess is you mean our lack of global index support.
Generally, there's not all that much consensus in the community that this would be a good feature to have. Why do people want to use partitioning? Many people do it so that they can quickly remove data that's no longer required with a simple DETACH operation. This is metadata only and is generally very fast. Another set of people partition as their tables are very large and they become much easier to manage when broken down into parts. There's also a group of people
who do it for the improved data locality. Unfortunately, if we had a
global index feature then that requires building a single index over all partitions. DETACH is no longer a metadata-only operation as we must somehow invalidate or remove tuples that belong to the detached partition. The group of people who partitioned to get away from very large tables now have a very large index. Maybe the only group to get off lightly here are the data locality group. They'll still have the same data locality on the heap.
So in short, many of the benefits of partitioning disappear when you have a global index.
So, why did you partition your data in the first place? If you feel like you wouldn't mind having a large global index over all partitions then maybe you're better off just using a non-partitioned table to store this data.
David
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2021-07-12 09:56 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-25 03:10 Partition column should be part of PK Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-06-25 04:22 ` Justin Pryzby <pryzby@telsasoft.com>
2021-07-08 22:17 ` Justin Pryzby <pryzby@telsasoft.com>
2021-07-08 22:28 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-07-08 23:12 ` Mike Sofen <msofen@runbox.com>
2021-07-09 03:32 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-07-09 03:52 ` Christophe Pettus <xof@thebuild.com>
2021-07-12 00:36 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-07-12 00:47 ` Christophe Pettus <xof@thebuild.com>
2021-07-12 00:57 ` David Rowley <dgrowleyml@gmail.com>
2021-07-12 06:00 ` Thomas Kellerer <shammat@gmx.net>
2021-07-12 09:56 ` Michel SALAIS <msalais@msym.fr>
2021-07-12 00:38 ` Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox