From: Craig A. James <cjames@modgraph-usa.com>
To: pgsql-performance@postgresql.org
Subject: update == delete + insert?
Date: Mon, 20 Mar 2006 14:49:43 -0800
Message-ID: <441F3187.90506@modgraph-usa.com> (raw)
In-Reply-To: <441EC652.1010807@modgraph-usa.com>
References: <7F10D26ECFA1FB458B89C5B4B0D72C2B088286@sesrv12.wirelesscar.com>
<441EC652.1010807@modgraph-usa.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cjames@modgraph-usa.com
Subject: Re: update == delete + insert?
In-Reply-To: <441F3187.90506@modgraph-usa.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox