Skip site navigation (1) Skip section navigation (2)

Re: Database size growing over time and leads to performance impact

From: "Pierre C" <lists(at)peufeu(dot)com>
To: gnanam(at)zoniac(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: Database size growing over time and leads to performance impact
Date: 2010-03-27 13:35:15
Message-ID: op.u98ao1lxeorkce@immo.peufeu.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
> 1.     VACUUM FULL ANALYZE once in a week during low-usage time and

VACUUM FULL compacts tables, but tends to bloat indexes. Running it weekly  
is NOT RECOMMENDED.

A correctly configured autovacuum (or manual vacuum in some circumstances)  
should maintain your DB healthy and you shouldn't need VACUUM FULL.

If you realize you got a bloat problem, for instance due to a  
misconfigured vacuum, use CLUSTER, which re-generates table AND index  
data, and besides, having your table clustered on an index of your choice  
can boost performance quite a lot in some circumstances.

8.2 is so old I don't remember if autovacuum is even included. Please try  
upgrading to the latest version...

Since your database probably fits in RAM, CLUSTER will be pretty fast.
You can schedule it weekly, if you need clustering. If you don't,  
autovacuum will suffice.
Hint : add a "SELECT count(*) FROM yourtable;" before "CLUSTER yourtable;"  
so that the table is pulled in the OS disk cache, it'll make CLUSTER  
faster.


In response to

Responses

pgsql-performance by date

Next:From: Andy ColsonDate: 2010-03-27 13:35:42
Subject: Re: Database size growing over time and leads to performance impact
Previous:From: GnanakumarDate: 2010-03-27 13:00:09
Subject: Database size growing over time and leads to performance impact

pgsql-admin by date

Next:From: Andy ColsonDate: 2010-03-27 13:35:42
Subject: Re: Database size growing over time and leads to performance impact
Previous:From: GnanakumarDate: 2010-03-27 13:00:09
Subject: Database size growing over time and leads to performance impact

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group