From: "Jim Buttafuoco" <jim@contactbda.com>
To: "Craig A. James" <cjames@modgraph-usa.com>,
	pgsql-performance@postgresql.org
Reply-To: jim@contactbda.com
Subject: Re: update == delete + insert?
Date: Mon, 20 Mar 2006 17:56:34 -0500
Message-Id: <20060320225614.M93465@contactbda.com>
In-Reply-To: <441F3187.90506@modgraph-usa.com>
References: <7F10D26ECFA1FB458B89C5B4B0D72C2B088286@sesrv12.wirelesscar.com>
	<441EC652.1010807@modgraph-usa.com>
	<441F3187.90506@modgraph-usa.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1

go with design 1, update does = delete + insert.


---------- Original Message -----------
From: "Craig A. James" <cjames@modgraph-usa.com>
To: pgsql-performance@postgresql.org
Sent: Mon, 20 Mar 2006 14:49:43 -0800
Subject: [PERFORM] update == delete + insert?

> 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's contents 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, but the 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 with each update.  But if columns are actually stored in separate files, the Designs 1 and 2 would be 
> essentially equivalent when it comes to vacuuming.
> 
> Thanks,
> Craig
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
------- End of Original Message -------