Message-ID: <441F3187.90506@modgraph-usa.com>
Date: Mon, 20 Mar 2006 14:49:43 -0800
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: update == delete + insert?
References: <7F10D26ECFA1FB458B89C5B4B0D72C2B088286@sesrv12.wirelesscar.com>
	<441EC652.1010807@modgraph-usa.com>
In-Reply-To: <441EC652.1010807@modgraph-usa.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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