Re: A thought on Index Organized Tables

On Tue, 2010-02-23 at 17:08 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Mon, 2010-02-22 at 08:51 +0200, Heikki Linnakangas wrote:
> >> Gokulakannan Somasundaram wrote:
> >
> >>> May i get a little clarification on this issue? Will we be supporting
> >>> the IOT feature in postgres in future?
> >> What seems like the best path to achieve the kind of performance
> >> benefits that IOTs offer is allowing index-only-scans using the
> >> visibility map.
> >
> > I don't agree with that. Could you explain why you think that would be
> > the case? It would be a shame to have everybody think you can solve a
> > problem if it turned out not to be the case.
>
> I'm thinking of a scan based on the index key. With an
> index-organised-table, you can skip the heap access because the heap and
> the index are the same structure. An index-only-scan likewise allows you
> to skip the heap access.
>
> I grant you that an index-organised-table can have other benefits, like
> reduced disk space usage (which is good cache efficiency), or less
> random I/O required for updates.
>
> The question was if PostgreSQL will be supporting index-organised-tables
> in the future. The answer is "not in the foreseeable future". No-one has
> come up with a plausible plan for how to do it, and no-one working on it
> at the moment.

I think Gokul was asking because he wanted to work on it, but wanted to
check community approval first.

> I don't want to discourage thinking about pie-in-the-sky features.

Planning, is what I would call that. Calling them "pie in the sky" is
just a negative label, as much as if someone else called them "obvious
next steps" is a positive label.

> There's many tricks like column-oriented storage, compression,
> index-organised-tables etc. that would be nice to have. Whether any
> particular feature is worthwhile in the end, the devil is in the details.

I agree that the way to improve things is to focus on a particular
architectural technique and then a design for doing that. Going straight
to the design and naming it doesn't help at all.

That was why I named an earlier project "Frequent Update Optimisation"
rather than any of the names that referred to a design.

The devil is in the details, I agree. The important part is analysis
though, not coding. Which is why I was asking why you were working on
index-only scans, though do not doubt your ability to make them work.

And also why I would say to Gokul: the right approach isn't to ask "will
we be supporting IOTs" and then go and build them. The right approach is
to work out what you want to improve and give a clear justification of
why, come up with a proposal to do that with analysis of how the
proposal will improve the situation and then think about coding.

--
Simon Riggs www.2ndQuadrant.com