From: Dima Tkach <dmitry@openratings.com>
Subject: Re: Effective usage without unique key -- suggestion
Date: Thu, 19 Sep 2002 21:15:20 -0400
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 40
Message-ID: <3D8A76A8.5040106@openratings.com>
References: <Pine.LNX.4.44.0208071353500.2273-100000@Bhuvan.bksys.co.in>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123
To: pgsql-sql@postgresql.org

Bhuvan A wrote:
> Hi,
> 
> I am using postgresql 7.2.1 on redhat linux 7.3
> 
> I have a table in very high production database which is circulating
> several thousands of records per day ie.. count does not exceed several
> thousands.  Here for some technical reason i dont have unique key on this
> table, but it should contain unique records. I know that without using
> unique index it can be achieved in any of the following methods.
> 
> Method 1
> --------
>   * check for the record.
>   * if exists update, else insert
> 
> Method 2
> --------
>   * delete the record (trigger on before insert)
>   * insert the record
> 
> So Can you please suggest the best among the above 2 methods?
> 
> BTW, internals of the above 2 methods would be different. For example in
> method 2, frequent deletion of records calls for vacuuming the database.  
> FYI, i does VACUUM ANALYZE every day. So Can you please suggest the best
> among the above 2 methods which well suits me and to use postgres more
> effectively?
> 

Not really - internally update does pretty much the same thing as 
delete+insert - so, either way, you won't be able to get away from 
vacuum'ing it...

I *think* the second method should be (a little) more effective...

I hope, it helps...

Dima