Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 0EE221337B5C for ; Fri, 28 Jan 2011 13:45:54 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 11167-04 for ; Fri, 28 Jan 2011 17:45:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-4.exch018.msoutlookonline.net (exhub018-4.exch018.msoutlookonline.net [64.78.17.19]) by mail.postgresql.org (Postfix) with ESMTP id A198C1337B51 for ; Fri, 28 Jan 2011 13:45:46 -0400 (AST) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-4.exch018.msoutlookonline.net ([64.78.17.19]) with mapi; Fri, 28 Jan 2011 09:45:46 -0800 From: Scott Carey To: Scott Marlowe , Robert Schnabel CC: Stephen Frost , "david@lang.hm" , pgsql-performance Date: Fri, 28 Jan 2011 09:47:55 -0800 Subject: Re: How to best use 32 15k.7 300GB drives? Thread-Topic: [PERFORM] How to best use 32 15k.7 300GB drives? Thread-Index: Acu/EzFKYjnnMkPHRoC8BLX0x4n1xQ== Message-ID: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: user-agent: Microsoft-MacOutlook/14.2.0.101115 acceptlanguage: en-US Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201101/321 X-Sequence-Number: 42108 On 1/28/11 9:00 AM, "Scott Marlowe" wrote: >On Fri, Jan 28, 2011 at 9:39 AM, Robert Schnabel >wrote: >> I can't do outside the database. So yes, once the upload is done I run >> queries that update every row for certain columns, not every column. >>After >> I'm done with a table I run a VACUUM ANALYZE. I'm really not worried >>about >> what my table looks like on disk. I actually take other steps also to >>avoid >> what you're talking about. > >It will still get bloated. If you update one column in one row in pg, >you now have two copies of that row in the database. If you date 1 >column in 1M rows, you now have 2M rows in the database (1M "dead" >rows, 1M "live" rows). vacuum analyze will not get rid of them, but >will free them up to be used in future updates / inserts. Vacuum full >or cluster will free up the space, but will lock the table while it >does so. > >There's nothing wrong with whole table updates as part of an import >process, you just have to know to "clean up" after you're done, and >regular vacuum can't fix this issue, only vacuum full or reindex or >cluster. Also note that HOT will come into play if you have FILLFACTOR set appropriately, so you won't get two copies of the row. This is true if the column being updated is small enough and not indexed. It wastes some space, but a lot less than the factor of two. > >--=20 >Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-performance