| 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 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin pgsql-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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andy Colson | 2010-03-27 13:35:42 | Re: Database size growing over time and leads to performance impact |
| Previous Message | Gnanakumar | 2010-03-27 13:00:09 | Database size growing over time and leads to performance impact |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andy Colson | 2010-03-27 13:35:42 | Re: Database size growing over time and leads to performance impact |
| Previous Message | Gnanakumar | 2010-03-27 13:00:09 | Database size growing over time and leads to performance impact |