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