Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

On Donnerstag, 14. Mai 2009, Alvaro Herrera wrote:
> Philipp Marek wrote:
> > On Mittwoch, 13. Mai 2009, Alvaro Herrera wrote:
> > > 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.
I'll try this tonight.

> > 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.
Well, I now looked into pg_stat_user_tables and found that since we're trying
to use vacuum_freeze_min_age CLUSTER doesn't seem to work anymore:

select relname, n_live_tup, n_dead_tup
from pg_stat_user_tables
where relname like 'log_lines__2009%' order by relname;

relname | n_live_tup | n_dead_tup
---------------------+------------+------------
log_lines__20090418 | 12469112 | 24
log_lines__20090419 | 12782920 | 12
log_lines__20090420 | 13548366 | 27
log_lines__20090421 | 14212689 | 12
log_lines__20090422 | 13266117 | 30
log_lines__20090423 | 16463312 | 549
log_lines__20090424 | 15435935 | 449
log_lines__20090425 | 11521196 | 457
log_lines__20090426 | 11015089 | 184
log_lines__20090427 | 11886995 | 106
log_lines__20090428 | 13261038 | 255
log_lines__20090429 | 12731062 | 351
log_lines__20090430 | 12897104 | 355
log_lines__20090501 | 12560355 | 378740
log_lines__20090502 | 12334676 | 13
log_lines__20090503 | 11931585 | 352089
log_lines__20090504 | 13013210 | 67727
log_lines__20090505 | 13617898 | 487454
log_lines__20090506 | 14875983 | 194299
log_lines__20090507 | 13435968 | 222537
log_lines__20090508 | 13970324 | 459262
log_lines__20090509 | 12321769 | 448003
log_lines__20090510 | 12358591 | 390394
log_lines__20090511 | 12109246 | 457838
log_lines__20090512 | 11979171 | 438211
log_lines__20090513 | 12747908 | 423004
log_lines__20090514 | 7685059 | 281043

Let's see what tomorrow brings ;-)

Regards,

Phil