Re: Postgresql optimisation

2009/10/28 Denis BUCHER <dbucherml(at)hsolutions(dot)ch>

> Grzegorz Jaśkiewicz a écrit :
> >
> >
> > On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER <dbucherml(at)hsolutions(dot)ch
> > <mailto:dbucherml(at)hsolutions(dot)ch>> wrote:
> >
> > Dear all,
> >
> > I need to optimize a database used by approx 10 people, I don't need
> to
> > have the perfect config, simply to avoid stupid bottle necks and
> follow
> > the best practices...
> >
> > The database is used from a web interface the whole work day with
> > "normal" requests (nothing very special).
> >
> > And each morning huge tables are DELETED and all data is INSERTed new
> > from a script. (Well, "huge" is very relative, it's only 400'000
> > records)
> >
> > use truncate, to clear the tables.
>
> Oh yes, instead of DELETE FROM table; ? Ok thanks for the tip
>
> > For now, we only planned a VACUUM ANALYSE eacha night.
> >
> > if it is 8.3+, don't , as autovacuum takes care of that.
>
> 8.1.17
>
> > But the database complained about checkpoint_segments (currently = 3)
> >
> > depending on traffic, that's pretty low. You should increment it, beyond
> > 12 if possible.
>
> Ok no problem in increasing this value, to, let's say... 50 ?
>

yes. This simply means, that in case of any failure (power outage, etc) -
data log could be slightly older, but if you have busy DB on the other hand
- low number here, means a lot of checkpoints written - which slows down
performance. So it is a trade-off.
8.1 is pretty old. Go for 8.3 if you want something old enough (as in,
stable-and-old-but-not-too-old). Or 8.4 if you are interested in newest
features.

--
GJ