Re: Proposal: Global Index

> 2021年1月8日 16:26，Julien Rouhaud <rjuju123(at)gmail(dot)com> 写道：
>
> On Fri, Jan 8, 2021 at 4:02 PM 曾文旌 <wenjing(dot)zwj(at)alibaba-inc(dot)com> wrote:
>>
>>> 2021年1月7日 22:16，Bruce Momjian <bruce(at)momjian(dot)us> 写道：
>>>
>>> On Thu, Jan 7, 2021 at 05:44:01PM +0800, 曾文旌 wrote:
>>>> I've been following this topic for a long time. It's been a year since the last response.
>>>> It was clear that our customers wanted this feature as well, and a large number of them mentioned it.
>>>>
>>>> So, I wish the whole feature to mature as soon as possible.
>>>> I summarized the scheme mentioned in the email and completed the POC patch(base on PG_13).
>>>
>>> I think you need to address the items mentioned in this blog, and the
>>> email link it mentions:
>>>
>>> https://momjian.us/main/blogs/pgblog/2020.html#July_1_2020
>>
>> Thank you for your reply.
>> I read your blog and it helped me a lot.
>>
>> The blog mentions a specific problem: "A large global index might also reintroduce problems that prompted the creation of partitioning in the first place. "
>> I don't quite understand, could you give some specific information?
>>
>> 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.