PostgreSQL 8.x defaults

[Craig Boston]

Palle,

Thanks for listening to my thoughts and for your insight on why things
are set up they way they are -- I knew there must be a good reason :)

On Mon, Apr 09, 2007 at 11:46:50AM +0200, Palle Girgensohn wrote:
> It does run vaccumdb -aqz per default, where -z is for analyze:
> 
> $ grep daily_pgsql_vacuum_args files/502.pgsql
> daily_pgsql_vacuum_args="-z"
>         su -l pgsql -c "vacuumdb -a -q ${daily_pgsql_vacuum_args}"

Ah, I missed the daily_pgsql_vacuum_args variable being set at the start
of the file!

> About the two "strategies" you present; autovacuum will probably need  
> some tweaking for most applications, since it will not perform  
> vacuums unless a certain percentage of the tuples are changed. Hence,  
> usually I use a combination of autovacuum and a nightly vacuumdb -za.  

Hmm, yes, thinking about it that way using both makes sense.  In theory,
will running autovacuum lessen the impact of running the scheduled
VACUUM [ANALYZE] (because there is less work to do)?

> For smaller installations, the vanilla setup could of course be  
> either of your suggestions, or my just suggested combo, it is  
> probably a matter of taste. I'd prefer you #1 or the combo... I'll  
> think about it a bit more, and will fix the port. :)

Now that I know there's a good reason for running both it seems to me
that the combo is a sane default.  The only objection I can think of is
that ports typically default to being "off" -- i.e. you must explicitly
enable rc.d scripts in rc.conf, explicitly copy/symlink web apps into
your server's root directory, etc.

Thus, the periodic vacuum job that runs by default simply because the
port is installed, even if you don't enable PostgreSQL itself to run,
feels somewhat wrong.  The impact is minimal -- an extra message in the
daily output complaining about being unable to connect -- so it's not
something that is critical.

I'm not sure of a good solution however.  Do you think it would be
possible / reasonable for the periodic job to check if the user has set
postgresql_enable and do nothing if it is not enabled?

Craig