Skip site navigation (1) Skip section navigation (2)

Re: column updates

From: Ben <bench(at)silentmedia(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: column updates
Date: 2004-10-29 23:03:45
Message-ID: Pine.LNX.4.44.0410291551210.3308-100000@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-general
Thanks. Good to hear that the easiest solution for me is also the fastest.

On Fri, 29 Oct 2004, Martijn van Oosterhout wrote:

> The number of dead tuples is the number of UPDATE statements you make.
> You if you just do a straight UPDATE x set f1=x, f2=y, ... that will be
> as optimal as you can get.
> 
> The only exception may be toasted fields, which would only apply if you
> had fields with more than 2K of data or so...
> 
> Hope this helps,
> 
> On Fri, Oct 29, 2004 at 12:24:35PM -0700, Ben wrote:
> > I'm designing a system where I'll be making frequent updates to rows, but
> > some columns will change far less frequently than others. All columns will 
> > be read with equal frequency, though probably by means of a materialized 
> > view. Updates will happen via a stored proc.
> > 
> > Which makes the most sense?
> > 
> > 1. Blindly overwrite the value for all columns, even columns that haven't
> > changed.
> > 
> > 2. Compare the current value and update each column at a time, leaving 
> > unchanged columns alone.
> > 
> > 3. Break out my tables more, so that infrequently updated columns are in 
> > different tables. 
> > 
> > 
> > I'm thinking that 1 and 2 are similar and less complex than 3, but might 
> > result in more dead tuples. 2 and 3 will have to look up values before 
> > updates can happen. I don't know if 2 has any advantage over 1. 1 is 
> > certainly the simpliest.
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> > 
> >                http://www.postgresql.org/docs/faqs/FAQ.html
> 
> -- 
> Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
> 



In response to

pgsql-general by date

Next:From: Paul TillotsonDate: 2004-10-29 23:43:07
Subject: Re: SELECT a value from various tables depending on a column
Previous:From: Tom LaneDate: 2004-10-29 22:27:46
Subject: Re: XMIN/xid vs UNION

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group