Re: Very long deletion time on a 200 GB database

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.

-Andy