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 -------