| From: | Greg Spiegelberg <gspiegelberg(at)gmail(dot)com> |
|---|---|
| To: | Andy Colson <andy(at)squeakycode(dot)net> |
| Cc: | sthomas(at)peak6(dot)com, "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Marcin Mańk <marcin(dot)mank(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Very long deletion time on a 200 GB database |
| Date: | 2012-02-23 18:13:39 |
| Message-ID: | CAEtnbpWh-ko=4mznWLdPVT7pifaGMeoy0wDB1=mBcrUx35Dkdw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Thu, Feb 23, 2012 at 11:11 AM, Andy Colson <andy(at)squeakycode(dot)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<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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2012-02-23 18:30:32 | Re: Very long deletion time on a 200 GB database |
| Previous Message | Andy Colson | 2012-02-23 18:11:12 | Re: Very long deletion time on a 200 GB database |