Re: Massive delete from a live production DB

On Thu, May 12, 2011 at 17:23, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> Been reading some old threads (pre 9.x version) and it seems that the
> consensus is to avoid doing massive deletes from a table as it'll
> create so much unrecoverable space/gaps that vacuum full would be
> needed. Etc.

Just running DELETE with normal autovacuum won't *shrink* the physical
table, but the freed-up space will be made available for future
inserts/updates. No problem there. Fragmentation of newly inserted
records is still a potential issue.

It's true that pre-8.4 PostgreSQL versions you could run into "dead
space" that couldn't be re-used, if you had badly tuned FSM. I presume
this is why VACUUM FULL was recommended -- but this advice no longer
applies to 8.4 or 9.0.

> Instead, we might as well do a dump/restore. Faster, cleaner.
>
> Any ideas on what I could do without losing all the live updates? I
> need to get rid of about 11% of a 150 million rows of database, with
> each row being nearly 1 to 5 KB in size...

For deleting 11%, a dump and restore of 150 million records and
hundreds of gigabytes doesn't seem worth it. If it was closer to 50%,
then I'd consider it.

Regards,
Marti