Re: Postgresql optimisation

On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER <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.

>
> For now, we only planned a VACUUM ANALYSE eacha night.
>
if it is 8.3+, don't , as autovacuum takes care of that.

>
> But the database complained about checkpoint_segments (currently = 3)
>
depending on traffic, that's pretty low. You should increment it, beyond 12
if possible.

>
> What should be changed first to improve speed ?
> * memory ?
> *???
> Thanks a lot for any advice (I know there are plenty of archived
> discussions on this subject but it's always difficult to know what very
> important, and what's general as opposed to specific solutions)
>

again, if it is 8.3+ (and everyone here would advice you to run at least
that version), try using pg_tune script to get best performance settings.

--
GJ