Обсуждение: DELETE, INSERT vs SELECT, UPDATE || INSERT

Hi, all.

 While working on algorithm of my project I came to question. Let it
be table like this (user+cookie pair is the primary key).

INT user
INT cookie
INT count

 Periodically (with period 10 minutes) this PostgreSQL table
updated with my information.
 The main problem that some of pairs (user, cookie) may be already
exists in PostgreSQL and must be updated, some not exists and must be
inserted.

 My first way was to DELETE row with (user, cookie) pair which I'm
going to update then INSERT new. This guarantees that there will not
be an error when (user, cookie) pair already exists in table. And
currently it works by this way.
 But I think that it lead to highly fragmentation of table and it need
to be VACUUMED and ANALYZED far more frequently...

 Second idea was to try to SELECT (user, cookie) pair and then UPDATE
it if it exists or INSERT if not. I has thought that if UPDATE will
rewrite same place in file with new count it may lead to more compact
table (file not grow and information about actual rows in file will
not changed). And, if actual file blocks containing (user, cookie)
pair will not be moved to new place in file, table need to be ANALYZED
less frequently.
 But if UPDATE will actually insert new row in file, marking as 'free
to use' previous block in file which was contain previous version of
row, then again, table need to be VACUUMED and ANALYZED far more
frequently...
 And this second idea will be completely waste of time and code.
Because write on C code which "DELETE and INSERT" is more portably
than "SELECT than UPDATE if there are rows, or INSERT if there are
not".


 So, can anyone explain me is the actual mechanism of UPDATE can save
resources and tables from been highly fragmented? Or it gives same
results and problems and "DELETE then INSERT" is the best way?
--
engineer

Anton Maksimenkov <engineer@hlebprom.ru> writes:
>  Second idea was to try to SELECT (user, cookie) pair and then UPDATE
> it if it exists or INSERT if not. I has thought that if UPDATE will
> rewrite same place in file with new count it may lead to more compact
> table (file not grow and information about actual rows in file will
> not changed).

You're wasting your time, because Postgres doesn't work that way.
UPDATE is really indistinguishable from DELETE+INSERT, and there will
always be a dead row afterwards, because under MVCC rules both versions
of the row have to be left in the table for some time after your
transaction commits.  See
http://www.postgresql.org/docs/8.1/static/mvcc.html

            regards, tom lane