On Thu, May 12, 2011 at 17:23, Phoenix Kiula <phoenix.ki...@gmail.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
