Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

Philipp Marek wrote:
> 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 close them and try again.

> 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?

I see no reason at all for CLUSTER not to "return space to the
filesystem", unless it is copying all the tuples over including dead
ones (which can only be explained if you have open transactions).

I also see no reason for vacuum_freeze_min_age=0 to interfere with btree
cleaning.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support