pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: marc@oscar.eng.cv.net
Cc: pgsql-sql@postgresql.org
Subject: Re: how do I update or insert efficently in postgres
Date: Tue, 13 Nov 2001 11:42:54 -0800
Message-ID: <web-505220@davinci.ethosmedia.com> (raw)
In-Reply-To: <slrn9v2ouq.30qa.marc@oscar.eng.cv.net>

Maqrc,

> if row exists update some fields 
> else insert all fields
> 
> I have come across this befor and have used select to drive the
> choice, if I could get the row update else insert.  The db I worked
> on
> had a few thousand rows so it was fast enough.  This table will have
> around 1 million rows to start out with and I was wondering if there
> was any way to do this better.  I am touching each row twice and
> would
> like to get that down to once if possable.  If that is not possable
> would it be better to move the whole thing inside of 1 explicit
> transaction?  Any other ideas I have missed?  

Not really.  If you have to check for existance, that's going to be a
seperate query from the UPDATE/INSERT no matter how you cut it.  MySQL's
REPLACE probably just hides this double-check from you.  Using an
integer surrogate key which is indexed and VACUUMed regularly is about
all the performance increase you can get. Except maybe moving the table
and the WAL_FILES both to seperate disks.

If you have a significant budget for your project, there could be ways
around this ... you'd just have to hire yourself a PostgreSQL hacker.
They could theoretically write modifications to PostgreSQL that would do
the following:

Scan the index for a pointer to the existing record.
If a pointer is found, use it to do the update without a second index
scan.
If the pointer is not found, do an insert without a unique index check.

However, the above could be costly to implement and put you out of line
for further PostgreSQL upgrades.  So throwing hardware at the problem is
probably a better idea.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



view thread (5+ messages)  latest in thread

Message-ID: <web-505220@davinci.ethosmedia.com>
Permalink:  ../web-505220@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-505220@davinci.ethosmedia.com

 · 

reply

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-sql@postgresql.org
  Cc: josh@agliodbs.com, marc@oscar.eng.cv.net
  Subject: Re: how do I update or insert efficently in postgres
  In-Reply-To: <web-505220@davinci.ethosmedia.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