DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default;
	d=consistentstate.com;
	h=Received:From:Organization:To:Subject:Date:User-Agent:References:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-Id:X-Identified-User;
	b=QF/C5dNKvbhX0udt5fuwM1zxiVcIo241dpNjYi1xpCn+oYKxB0Z1D51vjTQAMycjfIWcA1bIfirqhqVZD2j8aZpxIIKUx8WwiR6DH7hF5kn8Us+9NlByj3Rab3ZiPDTZ;
From: Kevin Kempter <kevink@consistentstate.com>
Organization: Consistent State
To: pgsql-performance@postgresql.org
Subject: Re: Massive table (500M rows) update nightmare
Date: Thu, 7 Jan 2010 10:46:24 -0700
User-Agent: KMail/1.12.4 (Linux/2.6.31.9-174.fc12.x86_64; KDE/4.3.4; x86_64; ;
	)
References: <4B45BE14020000250002DEE1@gw.wicourts.gov>
	<4B45BE37020000250002DEE4@gw.wicourts.gov>
	<4B45BE37020000250002DEE4@gw.wicourts.gov>
In-Reply-To: <4B45BE37020000250002DEE4@gw.wicourts.gov>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201001071046.24305.kevink@consistentstate.com>

On Thursday 07 January 2010 09:57:59 Kevin Grittner wrote:
> Ludwik Dylag <ldylag@gmail.com> wrote:
> > I would suggest:
> > 1. turn off autovacuum
> > 1a. ewentually tune db for better performace for this kind of
> >     operation (cant not help here)
> > 2. restart database
> > 3. drop all indexes
> > 4. update
> > 5. vacuum full table
> > 6. create indexes
> > 7. turn on autovacuum
> 
> I've only ever attempted something like that with a few tens of
> millions of rows.  I gave up on waiting for the VACUUM FULL step
> after a few days.
> 
> I some scheduled down time is acceptable (with "some" kind of hard
> to estimate accurately) the best bet would be to add the column with
> the USING clause to fill in the value.  (I think that would cause a
> table rewrite; if not, then add something to the ALTER TABLE which
> would.)  My impression is that the OP would rather stretch out the
> implementation than to suffer down time, which can certainly be a
> valid call.
> 
> If that is the goal, then the real question is whether there's a way
> to tune the incremental updates to speed that phase.  Carlo, what
> version of PostgreSQL is this?  Can you show us the results of an
> EXPLAIN ANALYZE for the run of one iteration of the UPDATE?
> Information on the OS, hardware, PostgreSQL build configuration, and
> the contents of postgresql.conf (excluding all comments) could help
> us spot possible techniques to speed this up.
> 
> -Kevin
> 


If you can come up with an effective method of tracking updates/deletes/inserts 
such as a trigger that writes the PK to a separate table upon any inserts, 
updates or deletes to the table you could do something like this:


1) create new table (no indexes) with the structure you want the table to have 
at the end of the process (i.e. the post-altered state) [new_tab]

2) create the insert,update,delete triggers mentioned above on the existing 
table [curr_tab] and write all the PK id's that change into a 3rd table 
[changed_keys]

3) kick off a process that simply does a select from curr_tab into new_tab and 
populates/back-fills the new column as part of the query

4) let it run as long as it takes

5) once it's complete do this:
   
   Create the all the indexes on the new_tab
   
   BEGIN;
   
   LOCK TABLE curr_tab;

   DELETE from new_tab 
       where pk_id in (select distinct pk_id from changed_keys);

   INSERT into new_tab
       select * from curr_tab
       where curr_tab.pk_id in (select distinct pk_id from changed_keys);

   ALTER TABLE curr_tab RENAME to old_tab;

   ALTER TABLE new_tab RENAME to curr_tab;

   COMMIT;


Also you might want to consider partitioning this table in the process...


Once you're confident you no longer need the old table [old_tab] you can drop 
it