Re: Proposal: Global Index

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.