Message-ID: <4F47C6C4.6030102@pinpointresearch.com>
Date: Fri, 24 Feb 2012 09:20:04 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.26) Gecko/20120131 Lightning/1.0b2 Thunderbird/3.1.18
MIME-Version: 1.0
To: "Reuven M. Lerner" <reuven@lerner.co.il>
CC: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
References: <4F45FB55.1020600@lerner.co.il>
In-Reply-To: <4F45FB55.1020600@lerner.co.il>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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