pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDELETE, INSERT vs SELECT, UPDATE || INSERT
2+ messages / 2 participants
[nested] [flat]
* DELETE, INSERT vs SELECT, UPDATE || INSERT
@ 2005-12-23 09:02 Anton Maksimenkov <engineer@hlebprom.ru>
0 siblings, 1 reply; 2+ messages in thread
From: Anton Maksimenkov @ 2005-12-23 09:02 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: DELETE, INSERT vs SELECT, UPDATE || INSERT
@ 2005-12-23 15:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Anton Maksimenkov <engineer@hlebprom.ru>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-12-23 15:30 UTC (permalink / raw)
To: Anton Maksimenkov <engineer@hlebprom.ru>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-23 15:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-23 09:02 DELETE, INSERT vs SELECT, UPDATE || INSERT Anton Maksimenkov <engineer@hlebprom.ru>
2005-12-23 15:30 ` Tom Lane <tgl@sss.pgh.pa.us>
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