update == delete + insert? - Mailing list pgsql-performance

From Craig A. James
Subject update == delete + insert?
Date
Msg-id 441F3187.90506@modgraph-usa.com
Whole thread Raw
In response to Re: Migration study, step 1: bulk write performance optimization  ("Craig A. James" <cjames@modgraph-usa.com>)
Responses Re: update == delete + insert?
Re: update == delete + insert?
Re: update == delete + insert?
List pgsql-performance
I've seen it said here several times that "update == delete + insert".  On the other hand, I've noticed that "alter
table[add|drop] column ..." is remarkably fast, even for very large tables, which leads me to wonder whether each
column'scontents are in a file specifically for that column. 

My question: Suppose I have a very "wide" set of data, say 100 columns, and one of those columns will be updated often,
butthe others are fairly static.  I have two choices: 

Design 1:
   create table a (
     id integer,
     frequently_updated  integer);

   create table b(
     id integer,
     infrequently_updated_1 integer,
     infrequently_updated_2 integer,
     infrequently_updated_3 integer,
     ... etc.
     infrequently_updated_99 integer);

Design 2:
   create table c(
     id integer,
     frequently_updated  integer,
     infrequently_updated_1 integer,
     infrequently_updated_2 integer,
     infrequently_updated_3 integer,
     ... etc.
     infrequently_updated_99 integer);

If "update == delete + insert" is strictly true, then "Design 2" would be poor since 99 columns would be moved around
witheach update.  But if columns are actually stored in separate files, the Designs 1 and 2 would be essentially
equivalentwhen it comes to vacuuming. 

Thanks,
Craig

pgsql-performance by date:

Previous
From: Vivek Khera
Date:
Subject: Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec RAID 2200S
Next
From: "Jim Buttafuoco"
Date:
Subject: Re: update == delete + insert?