Re: Why pgAdmin III guru suggests VACUUM in 8.1

On Wednesday 30 November 2005 12:12, Andrus wrote:
> >> No. autovacuum is turned ON by default in 8.1 XP
> >
> > Hrm, interesting that it's different than on Unix.
>
> Why major functionality is configured differently in different platforms ?
> This increases the cost of initial tuning when mixed platforms are used.
>

Yeah, that's odd. It's really more of a packaging decision though.

> > Initial tuning != maintenance. Many of PostgreSQL's default settings are
> > extremely conservative and will benefit from being increased on almost
> > any hardware. There's extensive discussion of this to be found in the
> > pgsql-performance archives, but take a look at shared_buffers and
> > work_memory at a minimum.
>
> I need to create installation for dumb users to ship DBMS with my
> application.
> So manual initial tuning is not possible.
>

Some tuning is... for instance during installation you should run vacuum
analyze after loading your data in.

> How to force Postgres to use reliable settings by default?
>
> How to use
>
> shared_buffers=auto
> work_memory =auto
>

It will dynamically allocate shared_buffers to a point, but you'll need to
figure out a way to auto tune these features yourself. It isn't an impossible
task in a controlled environment (use the annotated pg conf to find an
algorithm and then build a new conf file and copy it over before start up) so
you should have a chance, but it is complicated to do in an uncontrolled
environment, which is why it doesn't work that way as of yet.

BTW, if you have a good idea of the size of your data set, you might be able
to hardcode things. Ie. a desktop app is probably not going to need multi
gb / 100 user settings. HTH

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL