Message-ID: <4D42F861.9000000@missouri.edu>
Date: Fri, 28 Jan 2011 11:09:53 -0600
From: Robert Schnabel <schnabelr@missouri.edu>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US;
	rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: Stephen Frost <sfrost@snowman.net>, "david@lang.hm" <david@lang.hm>,
	pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: How to best use 32 15k.7 300GB drives?
References: <4D41F92F.9030006@missouri.edu>
	<alpine.DEB.2.00.1101271512390.17579@asgard.lang.hm>
	<4D420D61.3000109@missouri.edu>	<20110128131410.GC30352@tamriel.snowman.net>
	<4D42F127.9050307@missouri.edu>
	<AANLkTi=3j3PW=Lkrwu2XiWdS4jCy0TcLLO4L2h3=9U0G@mail.gmail.com>
In-Reply-To: <AANLkTi=3j3PW=Lkrwu2XiWdS4jCy0TcLLO4L2h3=9U0G@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


On 1/28/2011 11:00 AM, Scott Marlowe wrote:
> On Fri, Jan 28, 2011 at 9:39 AM, Robert Schnabel<schnabelr@missouri.edu>  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.

Those are exactly what I was referring to with my "other steps".  I just 
don't always do them as soon as I'm done updating because sometimes I 
want to query the table right away to find out something.  Yep, I found 
out the hard way that regular VACUUM didn't help.