Re: Proposal: Global Index

From: "Nasby, Jim" <nasbyj(at)amazon(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>, Robert Haas <robertmhaas(at)gmail(dot)com>, "Ibrar Ahmed" <ibrar(dot)ahmad(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Amit Langote <amitlangote09(at)gmail(dot)com>, Hamid Akhtar <hamid(dot)akhtar(at)gmail(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, heikki(dot)linnakangas <heikki(dot)linnakangas(at)iki(dot)fi>
Subject: Re: Proposal: Global Index
Date: 2020-01-07 22:55:13
Message-ID: 2E111BE5-E51B-4B27-8423-9A72A57EF477@amazon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


> On Oct 30, 2019, at 12:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> But ... why bother with partitioning then? To me, the main reasons
> why you might want a partitioned table are
>
> * ability to cheaply add and remove partitions, primarily so that
> you can cheaply do things like "delete the oldest month's data".
>
> * ability to scale past our limits on the physical size of one table
> --- both the hard BlockNumber-based limit, and the performance
> constraints of e.g. vacuuming a very large table.

A third case is data locality. In that case global indexes would be useful for queries that do not correlate will with hot data.

> Both of those go out the window with a global index. So you might
> as well just have one table and forget all the overhead.

Partition pruning could still be valuable even with global indexes, provided that we teach vacuum how to clean up tuples in an index that point at a partition that has been deleted.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2020-01-07 22:56:34 Re: [QUESTION/PROPOSAL] loose quadtree in spgist
Previous Message Tom Lane 2020-01-07 22:28:57 Re: pgsql: Add basic TAP tests for psql's tab-completion logic.