Effective usage without unique key -- suggestion

From: Bhuvan A <bhuvansql(at)linuxfreemail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Effective usage without unique key -- suggestion
Date: 2002-08-07 08:27:56
Message-ID: Pine.LNX.4.44.0208071353500.2273-100000@Bhuvan.bksys.co.in
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Suggestion or a link is suffice.
TIA.

regards,
bhuvaneswaran

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2002-08-07 08:30:25 Re: Limit A Table To 1 Row
Previous Message Trey Ackerman 2002-08-07 05:03:55 looking to speed up SELECT ... LEFT JOIN query