Re: Index size

On Wed, Mar 02, 2005 at 11:30:58PM +0200, Ioannis Theoharis wrote:
> On Wed, 2 Mar 2005, Martijn van Oosterhout wrote:
> > What makes you think that? Clustering is nice, but postgresql needs to
> > get the right answer and that the table in clustered is not something
> > postgresql can rely on.
>
> If postgresql doesn't rely on it, it' s postgresql's technical decision
> (and i don't know the reason) and not a default decision between rdbms's.
>
> But if you know exactly the reason, it would be a great help for me to
> know it.

Easy, if you CLUSTER a table, it's CLUSTERed then. But it doesn't stay
that way. As soon as you insert a new row, or update an old one, it
gets added to the end (the only place with space) and now it's not
clustered anymore. It's almost clustered and from a caching point of
view it's fine. But postgresql can't assume at any point a table will
stay clustered, an insert could happen in the middle of your
processing.

Logically you can't magically add space in the middle of a file, you
have to move everything else up. If you know an efficient way to keep a
table clustered while handling arbitrary inserts and updates, I'd be
curious to know...
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.