Re: Deleting millions of rows

On Mon, Feb 2, 2009 at 3:01 PM, Brian Cox <brian.cox@ca.com> wrote:
>> How much memory do you have in your machine? What is work_mem set to?
>
> 32G; work_mem=64M

Hmm.  Well then I'm not sure why you're running out of memory, that
seems like a bug. Taking a long time, I understand.  Crashing, not so
much.

>> Did you try VACUUM FULL instead of just plain VACUUM to recover
>> performance?  You might also need to REINDEX.
>> Or you could TRUNCATE the table.
>
> I didn't try FULL or REINDEX. In this case, TRUNCATE is the best option as I
> was just trying to reset the state of the table for another test. But this
> brings up another question: will autovacuum do the right thing to preserve
> performance on this table when many rows are deleted?

I don't think so.  I think you need to VACUUM FULL and REINDEX when
you do a big DELETE.  But if you TRUNCATE then you should be OK - no
further cleanup required in that case.

...Robert