Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building an application that will fetch messages with JSON every once in a while and store these in a postgres db. Now I want to prevent duplicates, so I calculate the sha-512 of the message and set it as the primary key. Since I want to prevent duplicates I can do two things:

  1. Fetch all hashes from the database and see when inserting new messages if the hash already exists (checking in code)
  2. Use an insert ignore (in postgres an update probably, since postgres doesn't support insert ignore) and let the database handle these exceptions

Considering I will probably be inserting a few messages at a time, I was wondering if method two will look up the primary key and see if exists for every insert as opposed to just storing a list in memory with method 1. What would be the most efficient?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

PostgreSQL doesn't have INSERT ... IGNORE so that isn't an option. You can use upsert-like operations (search for "postgresql upsert) but they certainly have a performance cost. Inserting and ignoring the resulting error on duplicate key is possible, but it tends to result in pretty spammy logs.

Personally, I'd do what's effectively do a bulk upsert, where I'd do something like this:

  • CREATE TEMPORARY TABLE staging_table(...)
  • COPY TO staging_table(...) FROM ... (or do bulk inserts)
  • LOCK TABLE real_table IN EXCLUSIVE MODE
  • INSERT INTO real_table(...) SELECT col1,col2,col3,... FROM staging_table WHERE NOT EXISTS (SELECT 1 FROM real_table WHERE real_table.primarykey = staging_table.primarykey)
  • COMMIT

You can do an UPDATE before the INSERT if you want, updating existing records with new values.

The LOCK TABLE is necessary to prevent concurrent updates failing with errors. You can still SELECT from the table during the update.

If you EXPLAIN the plan for your INSERT you should see that it's been transformed into a JOIN when you're inserting larger numbers of records.

share|improve this answer
1  
Missed a spot: there can be duplicates in the staging table itself, which would not be detected this way and lead to a unique key violation as well. Use GROUP BY or DISTINCT with the SELECT ... FROM staging_table. –  Erwin Brandstetter Apr 1 '13 at 13:24
    
@ErwinBrandstetter Very good point. Can't really handle that without knowing the data (do you just DISTINCT ON and pick the 1st? Pick the one that's chronologically last? etc) but that case certainly must be handled. –  Craig Ringer Apr 1 '13 at 13:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.