Re: DB become enormous with continuos insert and update

On 10/26/10 2:01 PM, Hfe80 wrote:
> Some one know how to solve the problem of db that became huge from 700 Mbyte
> to 16 Gbyte in 10 days and grow...
> Vacuum doesn't solve the problem.
> Only vacuum full is too invasive, the only way to reduce the DB  is dump and
> restore, but we cannot stop service...
>
> I know that a in a similar db where there is a lot of update the problem was
> solved but I don't know how...
> Some one Know this problem?

if you have a lot of updates of small non-indexed fields, use a
fillfactor of something like 60 or 70% so that the HOT thing can do its
job (assuming 8.3+).    do be sure to have autovacuum running and
cranked up sufficiently that its keeping up with the workload.

never do VACUUM FULL.   Rather, use CLUSTER to rebuild heavily used
tables in order of the most frequently used key (typically the PK),
however this requires a global table lock for the duration, so should
only be used when your application is relatively inactive.