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