title: Quickest way to insert several hundred batches of 500k - 3 million records to PostgreSQL database
body: <p>I have a PostgreSQL server hosted on a VM on our intranet. Elsewhere within the network (these machines do not have access to the same filesystem) I have ~850 R scripts running (simultaneously) that are each generating anywhere between 500k and 3 million rows of data that I want to push to a (currently empty) table in the database. I am trying to determine what is the optimal way to do so. I came up with these solutions so far:</p>
  
  <ol>
  <li><p>Use the R odbc and DBI::dbWriteTable packages/functions to push the data to the remote server. As a test, I tried taking this route for 3 (out of ~850) batches simultaneously and that took ~25 minutes.</p></li>
  <li><p>Export the data as a text file, use SSH to copy text file to the server hosting the database, and use <code>COPY</code> to import the data</p></li>
  </ol>
  
  <p>Are there better ways to do this? What is the best way to do this?</p>
  
  <p>The table that I am trying to push to is indexed and has foreign keys, as well as unique constraints. I've read somewhere that removing these prior to adding the data, and adding them back in afterwards could significantly speed things up. Would this make a significant difference?</p>