Re: Index AM change proposals, redux

Simon Riggs wrote:
> GIT significantly reduces the size of clustered indexes, greatly
> improving the number of index pointers that can be held in memory for
> very large indexes. That translates directly into a reduction in I/O for
> large databases on typical hardware, for primary operations, file
> backups and recovery (and this, log replication). Test results validated
> that and showed increased performance, over and above that experienced
> with HOT, when tested together.
>
> Now there may be problems with the GIT code as it stands, but we should
> acknowledge that the general technique has been proven to improve
> performance on a recent PostgreSQL codebase. This is an unsurprising
> result, since SQLServer, Sybase, DB2, Oracle and Teradata (at least) all
> use indexes of this category to improve real-world performance. The idea
> is definitely not a benchmark-only feature.
>
> Many users would be very interested if we could significantly reduce the
> size of the main index on their largest tables.

Yes, basically GIT allows index compression for clustered tables, and
stated that way it is clear it would be a big feature if we had it for
8.4.

> I would at least like to see clustered indexes acknowledged as a TODO
> item, so we keep the door open for a future implementation based around
> the basic concept of GIT.

Yes, it is already a TODO:

* Consider compressing indexes by storing key values duplicated in
several rows as a single index entry

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

+ If your life is a hard drive, Christ can be your backup. +