Re: A thought on Index Organized Tables

Heikki,
I had a quick look at the discussion on visibility map design. The
main differences as i see it are
a) IOT has both table and index in one structure. So no duplication of data
b) With visibility maps, we have three structures a) Table b) Index c)
Visibility map. So the disk footprint of the same data will be higher in
postgres ( 2x + size of the visibility map).
c) More than that, inserts and updates will incur two extra random i/os
every time. - one for updating the table and one for updating the visibility
map.

Are we going to ignore these differences?

Thanks,
Gokul.

On Mon, Feb 22, 2010 at 12:21 PM, Heikki Linnakangas <
heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:

> Gokulakannan Somasundaram wrote:
> > Hi,
> > As you all know, Index Organized tables are a way by which we can
> > automatically cluster the data based on the primary key. While i was
> > thinking about an implementation for postgres, it looks like an
> impossible
> > with the current ideologies. In an IOT, if a record gets updated, we need
> to
> > mark the old row as deleted immediately, as we do with any other table.
> But
> > since Postgres supports user defined data types and if they happen to be
> a
> > broken data type, then we have an unstable IOT.(as there is no guarantee,
> we
> > might hit the same record)
> > This was the reason for which, the proposal on creating indexes with
> > snapshot was rejected.
> > 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 worked on that last summer, but unfortunately didn't
> have the time to finish anything.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>