Re: ToDo List Item - System Table Index Clustering

On Jan 18, 2011, at 6:35 AM, Alvaro Herrera wrote:
>>
>
> Wow, this is really old stuff. I don't know if this is really of any
> benefit, given that these catalogs are loaded into syscaches anyway.

The benefit is educational primarily. I was looking for a todo list item
that would expose me to the system tables. Learning the data model
of a new system is always step 1 for me. So that one was perfect as
it would have me study and consider each one to determine if there
was any benefit from clustering on its initial load into cache.

> Furthermore, if you cluster at initdb time, they will soon lose the
> ordering, given that updates move tuples around and inserts put them
> anywhere. So you'd need the catalogs to be re-clustered once in a
> while, and I don't see how you'd do that (except by asking the user to
> do it, which doesn't sound so great).

I did discover that last night. I'm used to databases that keep up their
clustering. One that falls apart over time is distinctly strange. And the
way you guys do your re-clustering logic is overkill if just a few rows
are out of place. On the upside, a call to mass re-clustering goes
and updates all the clustered indexes in the system and that includes
these tables. Will have to study auto-vacuum as well to consider that.

> (unless you just want to play with
> Bison for educational purposes, of course, which is always a good thing
> to do).

Pretty much, yeah.

- Simone Aiken