From: Andrew Dunstan <andrew@dunslane.net>
To: sthomas@peak6.com
Cc: Reuven M. Lerner <reuven@lerner.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
Date: Mon, 27 Feb 2012 09:53:59 -0500
Message-ID: <4F4B9907.60008@dunslane.net> (raw)
In-Reply-To: <4F4B9721.6050808@peak6.com>
References: <4F45FB55.1020600@lerner.co.il>
<4F47C6C4.6030102@pinpointresearch.com>
<4F4B39EF.2080004@lerner.co.il>
<4F4B9721.6050808@peak6.com>
On 02/27/2012 09:45 AM, Shaun Thomas wrote:
> On 02/27/2012 02:08 AM, Reuven M. Lerner wrote:
>
>> In the end, it was agreed that we could execute the deletes over
>> time, deleting items in the background, or in parallel with the
>> application's work. After all, if the disk is filling up at the rate
>> of 2 GB/day, then so long as we delete 4 GB/day (which is pretty easy
>> to do), we should be fine.
>
> Please tell me you understand deleting rows from a PostgreSQL database
> doesn't work like this. :) The MVCC storage system means you'll
> basically just be marking all those deleted rows as reusable, so your
> database will stop growing, but you'll eventually want to purge all
> the accumulated dead rows.
>
> One way to see how many there are is to use the pgstattuple contrib
> module. You can just call it on the table name in question:
>
> SELECT * FROM pgstattuple('my_table');
>
> You may find that after your deletes are done, you'll have a free_pct
> of 80+%. In order to get rid of all that, you'll need to either run
> CLUSTER on your table(s) or use the select->truncate->insert method
> anyway.
>
If he has autovacuum on he could well be just fine with his proposed
strategy. Or he could have tables partitioned by time and do the delete
by just dropping partitions. There are numerous way he could get this to
work.
cheers
andrew
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@dunslane.net, sthomas@peak6.com, reuven@lerner.co.il
Subject: Re: Very long deletion time on a 200 GB database
In-Reply-To: <4F4B9907.60008@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox