pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Merlin Moncure <mmoncure@gmail.com>
To: Mark Makarowsky <bedrockconstruction@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update table performance
Date: Thu, 9 Aug 2007 18:04:09 +0530
Message-ID: <b42b73150708090534n3c5dfba6qbabf6dc1b2e0011e@mail.gmail.com> (raw)
In-Reply-To: <670773.87500.qm@web32209.mail.mud.yahoo.com>
References: <20070807214227.GR25704@nasby.net>
	<670773.87500.qm@web32209.mail.mud.yahoo.com>

On 8/8/07, Mark Makarowsky <bedrockconstruction@yahoo.com> wrote:
> Can you provide more detail on what you mean by your
> two suggestions below:
>
> 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.
>
> 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.
>
> I guess I was just surprised by the speed it takes to
> update the field in Postgres since on an almost
> identical table in FoxPro (400,000 records less), it
> updates the table with the same exact update table
> statement in about 4 minutes.

FoxPro is a single process DBF based system with some sql access.
When you update th records, it updates them in place since all the
records are fixed size and padded.  Be careful with this
comparison...while certain operations like the above may feel faster,
the locking in foxpro is extremely crude compared to PostgreSQL.
There are many other things about dbf systems in general which are
pretty lousy from performance perspective.

That said, 'update' is the slowest operation for postgresql relative
to other databases that are not MVCC.  This is balanced by extremely
efficient locking and good performance under multi user loads.
PostgreSQL likes to be used a certain way...you will find that when
used properly it is extremely fast.

keep an eye for the HOT feature which will hopefully make 8.3 that
will highly reduce the penalty for (small) updates in many cases.

merlin



view thread (19+ messages)  latest in thread

Message-ID: <b42b73150708090534n3c5dfba6qbabf6dc1b2e0011e@mail.gmail.com>
Permalink:  ../b42b73150708090534n3c5dfba6qbabf6dc1b2e0011e@mail.gmail.com/
Also on:    postgresql.org/message-id/b42b73150708090534n3c5dfba6qbabf6dc1b2e0011e@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mmoncure@gmail.com, bedrockconstruction@yahoo.com
  Subject: Re: Update table performance
  In-Reply-To: <b42b73150708090534n3c5dfba6qbabf6dc1b2e0011e@mail.gmail.com>
  References: <20070807214227.GR25704@nasby.net> <670773.87500.qm@web32209.mail.mud.yahoo.com> <b42b73150708090534n3c5dfba6qbabf6dc1b2e0011e@mail.gmail.com>

* 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