Re: table clustering brings joy

Junaili Lie <junaili(at)gmail(dot)com> writes:

> Quick questions:
> For big tables with frequent insert, no update, and frequent read
> (using indexes), will clustering help?
> what should be done on such table other than regular analyze?
> comments are appreciated.

If you never have any deletes or updates then you don't really need to vacuum
the table regularly. (You still need to vacuum it before transaction id
wraparound but that's a pretty long time.)

So clustering won't help you by removing dead tuples and compacting the table.

But it can still help by ordering the records in the same order as your index.
The more the record order is correlated with the index the more effective the
index is and the larger the result set that can use that index productively.

That will only help if you're often retrieving moderately large result sets by
one particular index. If you normally only retrieve one record at a time or
from lots of different indexes then it probably won't really make much
difference.

New records won't be inserted in order though so periodically you'll want to
recluster the table to maintain the order.

--
greg