Re: Index/Function organized table layout

On Wed, 2003-10-01 at 08:37, Tom Lane wrote:
> I think you'd need to do some basic architectural work first. Right now
> we have a clean API for index access methods, but there is no comparable
> abstraction layer for heaps (tables). It'd probably be necessary to
> create such a layer in order to allow different heap organizations to be
> supported. Another point of confusion is that heaps and indexes are
> rigidly distinct. Perhaps some heaps could be considered to be indexes
> as well, in order to support your idea of b-tree-organized tables.
> Doing that would undoubtedly break a few places though.

Ahhh, okay. So it would require an architectural mod.

Still, it would probably be a worthwhile project (for me at least).
There are a number of enterprise features that rely on a similar
architectural premise that would also be good to have eventually. We
are at the point at this company where we need some of these features in
our databases for scalability and management purposes, but we aren't all
that eager to migrate to Oracle (the alternative). From my standpoint,
it would be substantially cheaper to actively work on adding the
features needed to Postgres than to move to Oracle to get the same
features.

I clearly need to take a good look at the relevant source.

> > Both of these things really are attempts to address the same basic problem,
> > which is optimizing the number of buffers a given query uses by making the
> > tables layout reflect typical queries.
>
> Hm, are you sure that smarter buffer management wouldn't serve the
> purpose?

No, the problem isn't really buffer management; the buffer usage
behaviors are largely valid for the situation. The problem is that the
number of relevant tuples per buffer is low for a given query, so we
aren't getting much tuple bang for the buffer buck. I suppose one could
look at it as trying to improve the intra-buffer hit rate for a given
query.

Cheers,

-James Rogers
jamesr(at)best(dot)com