From: Robert Schnabel <schnabelr@missouri.edu>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: david@lang.hm <david@lang.hm>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: How to best use 32 15k.7 300GB drives?
Date: Fri, 28 Jan 2011 11:09:53 -0600
Message-ID: <4D42F861.9000000@missouri.edu> (raw)
In-Reply-To: <AANLkTi=3j3PW=Lkrwu2XiWdS4jCy0TcLLO4L2h3=9U0G@mail.gmail.com>
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: schnabelr@missouri.edu, scott.marlowe@gmail.com, sfrost@snowman.net, david@lang.hm
Subject: Re: How to best use 32 15k.7 300GB drives?
In-Reply-To: <4D42F861.9000000@missouri.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox