Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 33AF1633B39 for ; Thu, 7 Jan 2010 13:56:29 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 94877-05 for ; Thu, 7 Jan 2010 17:56:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from zql.com (3a.1f.de.static.xlhost.com [206.222.31.58]) by mail.postgresql.org (Postfix) with ESMTP id 85F7D6335D0 for ; Thu, 7 Jan 2010 13:56:18 -0400 (AST) Received: from localhost ([127.0.0.1]) by zql.com with smtp (Exim 4.68) (envelope-from ) id 1NSwam-0008HU-Kv; Thu, 07 Jan 2010 12:56:16 -0500 From: "Greg Sabino Mullane" To: pgsql-performance@postgresql.org Subject: Re: Massive table (500M rows) update nightmare X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Request-PGP: http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 In-Reply-To: <4B45DE4C.4050606@beecom.ch> Date: Thu, 7 Jan 2010 17:56:16 -0000 X-Mailer: JoyMail 2.4.0 Message-ID: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.446 tagged_above=-10 required=5 tests=AWL=0.153, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/110 X-Sequence-Number: 36990 -----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > If it is possible to lock this audit table exclusively (may be during > off peak hours) I would look into > - create new_audit_table as select col1, col2, col3 ... col9, > 'new_col_value' from old_audit_table; > - create all indexes > - drop old_audit_table > - rename new_audit_table to old_audit_table This is a good approach, but you don't necessarily have to exclusively lock the table. Only allowing reads would be enough, or you could install a trigger to keep track of which rows were updated. Then the process becomes: 1. Create trigger on the old table to store changed pks 2. Create new_audit_table as select col1, col2, col3 ... col9, 'new_col_value' from old_audit_table; 3. Create all indexes on the new table 4. Stop your app from writing to the old table 5. COPY over the rows that have changed 6. Rename the old table to something else (for safety) 7. Rename the new table to the real name 8. Drop the old table when all is good - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201001071253 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAktGIC0ACgkQvJuQZxSWSshEAQCfRT3PsQyWCOBXGW1XRAB814df pJUAoMuAJoOKho39opoHq/d1J9NprGlH =htaE -----END PGP SIGNATURE-----