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