Re: Massive delete from a live production DB

Am 12.05.2011 16:38, schrieb Phoenix Kiula:
> On Thu, May 12, 2011 at 10:33 PM, Eric Ndengang
> <eric(dot)ndengang_foyet(at)affinitas(dot)de> wrote:
>> Am 12.05.2011 16:23, schrieb Phoenix Kiula:
>>> Hi
>>>
>>> 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.
>>>
>>> Instead, we might as well do a dump/restore. Faster, cleaner.
>>>
>>> This is all well and good, but what about a situation where the
>>> database is in production and cannot be brought down for this
>>> operation or even a cluster?
>>>
>>> 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...
>>>
>>> Thanks! Version is 9.0.4.
>>>
>> Hey,
>> try to use pg_reorg --> http://reorg.projects.postgresql.org
>> but the table must get a primary key.
>> regards
>>
>
>
> Thanks Eric.
>
> I do have a primary key.
>
> I am on version 9.0.4. Will pg_reorg work with this version too? The
> example on that website mentions 8.3.
>
> Also, it it a fast process that does not consume too much resource?
> This DB is behind a very high traffic website, so I cannot have a
> CLUSTER alternative like pg_reog making my DB very slow concurrently.
>
> How does one install the patch easily on CentOS (Linux) 64 bit?
>
> Thanks!
Hi,

/* I am on version 9.0.4. Will pg_reorg work with this version too? The
example on that website mentions 8.3. */

I used to use pg_reorg on version 8.4.8 and regarding the documentation
it will also work with the 9.0 version.

/* How does one install the patch easily on CentOS (Linux) 64 bit? */

You can easily install it as a contrib . Just read the installation
guide or the man Page.

/*

Also, it it a fast process that does not consume too much resource?
This DB is behind a very high traffic website, so I cannot have a
CLUSTER alternative like pg_reog making my DB very slow concurrently.*/

Yes, it's a fast process that is neither time nor resource consumming. The reorgainization of a table with about 60 million could take less than 8 minutes without higher cpu cost.

cheers

--
Eric Ndengang
Datenbankadministrator

Affinitas GmbH | Kohlfurter Straße 41/43 | 10999 Berlin | Germany
email: eric(dot)ndengang_foyet(at)affinitas(dot)de | tel: +49.(0)30. 991 949 5 0 | www.edarling.de

Geschäftsführer: Lukas Brosseder, David Khalil, Kai Rieke, Christian Vollmann
Eingetragen beim Amtsgericht Berlin, HRB 115958

Real People: www.edarling.de/echte-paare
Real Love: www.youtube.de/edarling
Real Science: www.edarling.org