Re: Update table performance

From: Decibel! <decibel(at)decibel(dot)org>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Richard Huxton <dev(at)archonet(dot)com>, Mark Makarowsky <bedrockconstruction(at)yahoo(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Update table performance
Date: 2007-08-07 21:42:28
Message-ID: 20070807214227.GR25704@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Tue, Aug 07, 2007 at 02:36:18PM -0500, Scott Marlowe wrote:
> On 8/7/07, Decibel! <decibel(at)decibel(dot)org> wrote:
> > On Tue, Aug 07, 2007 at 02:33:19PM +0100, Richard Huxton wrote:
> > > Mark Makarowsky wrote:
> > > >I have a table with 4,889,820 records in it. The
> > > >table also has 47 fields. I'm having problems with
> > > >update performance. Just as a test, I issued the
> > > >following update:
> > > >
> > > >update valley set test='this is a test'
> > > >
> > > >This took 905641 ms. Isn't that kind of slow?
> > >
> > > The limiting factor here will be how fast you can write to your disk.
> >
> > Well, very possibly how fast you can read, too. Using your assumption of
> > 1k per row, 5M rows means 5G of data, which might well not fit in
> > memory. And if the entire table's been updated just once before, even
> > with vacuuming you're now at 10G of data.
>
> Where one might have to update just one column of a wide table often,
> it's often a good idea to move that column into its own dependent
> table.

Yeah, I've used "vertical partitioning" very successfully in the past,
though I've never done it for just a single field. I'll typically leave
the few most common fields in the "main" table and pull everything else
into a second table.

> Or just don't update one column of every row in table...

Yeah, that too. :) Though sometimes you can't avoid it.

I should mention that if you can handle splitting the update into
multiple transactions, that will help a lot since it means you won't be
doubling the size of the table.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Makarowsky 2007-08-07 23:13:00 Re: Update table performance
Previous Message Scott Marlowe 2007-08-07 19:36:18 Re: Update table performance