From: "Michel SALAIS" <msalais@msym.fr>
To: "'David Rowley'" <dgrowleyml@gmail.com>,
	"'Nagaraj Raj'" <nagaraj.sf@yahoo.com>
Cc: "'Christophe Pettus'" <xof@thebuild.com>,
	<pgsql-performance@postgresql.org>
References: <20210708221703.GA1301@telsasoft.com>
 <202107082228.obnak7c7mx4n@alvherre.pgsql>
 <080901d7744e$ac0f3d70$042db850$@runbox.com>
 <613808845.2913413.1625801566230@mail.yahoo.com>
 <5B9DB883-EED5-4378-ABEC-03FCC11560B2@thebuild.com>
 <859150623.3882063.1626050211237@mail.yahoo.com>
 <CAApHDvoTbN8BwGMkPY0=YsTpsJBLDv_t21iQvYqhx=gDRW3Rzw@mail.gmail.com>
In-Reply-To: 
 <CAApHDvoTbN8BwGMkPY0=YsTpsJBLDv_t21iQvYqhx=gDRW3Rzw@mail.gmail.com>
Subject: RE: Partition column should be part of PK
Date: Mon, 12 Jul 2021 11:56:40 +0200
Message-ID: <00a501d77704$371708f0$a5451ad0$@msym.fr>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: 
 AQEl2keVU7R58lp6jrYpCcwCAGqjQAI0b0d+AfEmqX8CTnmBjAH9JnBEAfJ4TRkCVrD4qqw8fC7Q
Content-Language: fr
Archived-At: 
 <https://www.postgresql.org/message-id/00a501d77704%24371708f0%24a5451ad0%24%40msym.fr>
Precedence: bulk

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>=20
Envoy=C3=A9 : lundi 12 juillet 2021 02:57
=C3=80 : 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