Re: Very long deletion time on a 200 GB database

On 02/23/2012 12:39 AM, Reuven M. Lerner wrote:
> Hi, everyone. ...
> ...at one installation, we're quickly running out of disk space. The
> database is already taking up about 200 GB of space, and is growing by
> 1 GB or so a day.

I've been following the discussion of approaches and tuning for bulk
deletes and suddenly wondered if you have checked a couple other basics.

Do you know the source of the increases in DB size? Is it due strictly
to inserted data or are there lots of updates as well?

Is autovacuum running properly?

Could you, due to bulk deletes and other issues, be suffering from
table- or index-bloat? Heavily bloated tables/indexes will exacerbate
both your disk-usage and performance problems.

If possible you might try clustering your tables and see what happens to
disk usage and bulk-delete performance. Clusters are generally
reasonably fast - way faster than VACUUM FULL, though they could still
take a while on your very large tables.

As a bonus, cluster gives you shiny, new non-bloated indexes. They do
require an exclusive lock and they do require sufficient disk-space to
build the new, albeit smaller, table/indexes so it may not be an option
if you are short on disk-space. You may be able to start by clustering
your smaller tables and move toward the larger ones as you free
disk-space. Be sure to run ANALYZE on any table that you have CLUSTERed.

You might find it useful to make CLUSTER part of your regular maintenance.

Cheers,
Steve