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
globalindexes 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
indexsupport.
Generally, there's not all that much consensus in the community that this would be a good feature to have. Why do
peoplewant to use partitioning? Many people do it so that they can quickly remove data that's no longer required with
asimple DETACH operation. This is metadata only and is generally very fast. Another set of people partition as their
tablesare 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-onlyoperation as we must somehow invalidate or remove tuples that belong to the detached partition. The group
ofpeople who partitioned to get away from very large tables now have a very large index. Maybe the only group to get
offlightly 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
overall partitions then maybe you're better off just using a non-partitioned table to store this data.
David
