Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

On Mittwoch, 13. Mai 2009, Alvaro Herrera wrote:
> > we're using postgresql 8.3 for some logging framework.
> >
> > There are several tables for each day (which are inherited from a common
> > base), which
> > - are filled during the day,
> > - after midnight the indizes are changed to FILLFACTOR=100, and
> > - the tables get CLUSTERed by the most important index.
> > - Some time much later the tables that aren't needed anymore are DROPped.
> >
> > So far, so fine.
>
> Do say, do you have any long-running transactions, or "idle"
> transactions?  Maybe someone opened a terminal somewhere and left it
> open for days?  Have a look at pg_stat_activity.
Yes, I have two terminal windows for different users/schemas in the same DB
open - but they're set to auto-commit, and have no tables open or locked.


Please, let me repeat myself:
> So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree
> indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return
> space to the filesystem.

Might the open connections make a difference?


Regards,

Phil