Re: Proposal: Global Index for PostgreSQL

On Wed, Jun 18, 2025 at 4:38 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Sat, Jun 14, 2025 at 2:32 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >

Thanks for your opinion, Sawada-San.

> Does it need to keep holding dead TIDs for each partition until it
> completes vacuuming all partitions that are covered by the global
> index? If so, it would end up holding a huge amount of memory in cases
> where there are many partitions. How does maintanence_work_mem (or
> autovacuum_work_mem) work in this context?

So it will keep holding the deadtids until we vacuum all the
partitions or we run out of the 'maintanence_work_mem', consider this
similar to the case where you do not have concept of global index and
for supporting the unique constraint on not partitioned key column
user has to keep one giant table without partitioning it, then every
time we run out of the  'maintanence_work_mem' we need to vacuum the
indexes, same theory applies for the global index.

 Also, what if the
> autovacuum worker who is processing the partitioned table with global
> indexes gets cancelled? I guess that we would need to scan the
> partitions again in order to collect dead TIDs to vacuum the global
> index but it would be very expensive.

You're right, but that's comparable to the cost of managing a single,
unpartitioned giant table. I believe the primary value of global
indexes lies in enabling users to partition tables that otherwise
couldn't be. So, while certain maintenance tasks might incur similar
costs to a single large table, you'll gain significant advantages
during many other DML operations due to partitioning that wouldn't be
possible without global indexes.

--
Regards,
Dilip Kumar
Google