Postgres Pro
Downloads
Home   >   mailing lists

Re: Very long deletion time on a 200 GB database - Mailing list pgsql-performance

From Greg Spiegelberg
Subject Re: Very long deletion time on a 200 GB database
Date
Msg-id CAEtnbpWh-ko=4mznWLdPVT7pifaGMeoy0wDB1=mBcrUx35Dkdw@mail.gmail.com
Whole thread Raw
In response to Re: Very long deletion time on a 200 GB database  (Andy Colson <andy@squeakycode.net>)
Responses Re: Very long deletion time on a 200 GB database  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Tree view
On Thu, Feb 23, 2012 at 11:11 AM, Andy Colson <andy@squeakycode.net> wrote:
On 2/23/2012 12:05 PM, Shaun Thomas wrote:
On 02/23/2012 11:56 AM, Greg Spiegelberg wrote:

I know there are perils in using ctid but with the LOCK it should be
safe. This transaction took perhaps 30 minutes and removed 100k rows
and once the table was VACUUM'd afterward it freed up close to 20 GB
on the file system.

It took *30 minutes* to delete 100k rows? And 100k rows were using 20GB?
Is that off by an order of magnitude?

Using the ctid is a cute trick, though. :)


And I'm not sure the LOCK is necessary, while googling for "delete from table limit 10" I ran across this thread:

http://archives.postgresql.org/pgsql-hackers/2010-11/msg02028.php

They use it without locks.


I used LOCK simply because if a VACUUM FULL x; slipped in between the SELECT and the DELETE the ctid's could conceivably change. 

-Greg

pgsql-performance by date:

Previous
From: Andy Colson
Date:
Subject: Re: Very long deletion time on a 200 GB database
Next
From: Tom Lane
Date:
Subject: Re: Very long deletion time on a 200 GB database