Re: Proposal: Global Index

On Mon, Jan 11, 2021 at 07:40:18PM +0800, 曾文旌 wrote:
> >> In addition you mentioned: "It is still unclear if these use-cases justify the architectural changes needed to enable global indexes."
> >> Please also describe the problems you see, I will confirm each specific issue one by one.
> >
> > One example is date partitioning. People frequently need to store
> > only the most recent data. For instance doing a monthly partitioning
> > and dropping the oldest partition every month once you hit the wanted
> > retention is very efficient for that use case, as it should be almost
> > instant (provided that you can acquire the necessary locks
> > immediately). But if you have a global index, you basically lose the
> > advantage of partitioning as it'll require heavy changes on that
> > index.
> If the global index removes all the major benefits of partitioned tables, then it is not worth having it.
>
> This is indeed a typical scenario for a partitioned table.
> there are two basic operations
> 1) Monthly DETACH old child table
> 2) Monthly ATTACH new child table
>
> For 1) The DETACH old child table can be finished immediately, global index can be kept valid after DETACH is completed, and the cleanup of garbage data in global index can be deferred to VACUUM.
> This is similar to the global index optimization done by Oracle12c.
> For 2) ATTACH new empty child table can also be completed immediately.
> If this is the case, many of the advantages of partitioned tables will be retained, while the advantages of global indexes will be gained.

Yes, we can keep the index rows for the deleted partition and clean them
up later, but what is the advantage of partitioning then? Just heap
deletion quickly? Is that enough of a value?

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee