go with design 1, update does = delete + insert. ---------- Original Message ----------- From: "Craig A. James" <cjames@xxxxxxxxxxxxxxxx> To: pgsql-performance@xxxxxxxxxxxxxx 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 -------
