Re: Index only scan paving the way for "auto" clustered tables?

On Tue, Oct 11, 2011 at 3:02 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Robert Haas <robertmhaas@gmail.com> wrote:
>>> [implement "clustered index" as a covering index with all columns
>>> which are present in the heap]
>> I guess we could do that, but I'm not convinced there would be
>> much benefit.
>
> The "traditional" way to implement a clustered index is to have the
> leaf level of the index contain the tuples rather than pointers to
> the tuples.  If we're going to do clustered tables, we might want to
> jump all the way to that, rather than a half-way solution which
> stores everything twice.

Not a bad thought.

Actually, I've been thinking for a while now that we might need a
pluggable heapam, similar to the pluggable indexams we already have.
Our current heap format has served us pretty well, but there are any
number of things that we can't really do without changing it.  Of
course, if we came up with a new format that was better in every case,
across the board, then perhaps we'd be willing to just replace the
current format outright -- though even then, that would break
pg_upgrade, which would be painful, to put it mildly.  And it seems to
me that there could easily be format changes that would make sense for
particular cases, but not across the board, like:

- index-organized tables (heap is a btree, and secondary indexes
reference the PK rather than the TID; this is how MySQL does it, and
Oracle offers it as an option)
- WORM tables (no updates or deletes, and no inserts after creating
transaction commits, allowing a much smaller tuple header)
- non-transactional tables (tuples visible as soon as they're written,
again allowing for smaller tuple header; useful for internal stuff and
perhaps for insert-only log tables)

Alternatively, we could try to graft the concept of a self-clustering
table on top of the existing heap implementation.  But I'm having
trouble seeing how that would work.  The TODO describes it as
something like "maintain CLUSTER ordering", but that's a gross
oversimplification, because we have no structure that would allow us
to sensibly do any such thing...  the current heap implementation is
just that: a pile of stuff.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company