I try to find the best scenario to update or insert 1 million contacts in my postgres database.

My actual scenario is:

  • read contacts 1000 per 1000 and try to insert multiple rows with one request
  • if my request fails, I go line per line to insert or update my 1000 contacts

It takes 7 minutes for 50K lines.

What is the best scenario to perform my update or insert?

  • 1
    This question is very similar to this one. – Twinkles Dec 9 '13 at 14:47
  • I used this question for my insert, my problem is when I have to update line per line – Guillaume Vincent Dec 9 '13 at 14:50
  • 1
    This related answer on SO should be of help. – Erwin Brandstetter Dec 10 '13 at 16:44
  • Is your problem ease of processing for correctness? Or performance? – Colin 't Hart Feb 7 '14 at 17:15
  • 1
    My problem was a performance problem, for now I looking for a good upsert method. Right now, I'm making an update and an insert for 1000 lines per lines. My CPU for my postgres method is around 75% CPU. – Guillaume Vincent Feb 7 '14 at 17:29

I would do it like this:

Load all data into an empty staging table and use SQL to identify and correct problematic records before moving the data into your database.

| improve this answer | |
  • This assumes the OP's problem is how to process for correctness. If the OP's problem is performance then this doesn't help at all. – Colin 't Hart Feb 7 '14 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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