title: How does COPY work and why is it so much faster than INSERT?
body: <p>Today I spent my day improving the performance of my Python script which pushes data into my Postgres database. I was previously inserting records as such:</p>
  
  <pre><code>query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)";
  for d in data:
      cursor.execute(query, d)
  </code></pre>
  
  <p>I then re-wrote my script so that it creates an in-memory file than is the used for Postgres' <code>COPY</code> command, which lets me copy data from a file to my table:</p>
  
  <pre><code>f = StringIO(my_tsv_string)
  cursor.copy_expert("COPY my_table FROM STDIN WITH CSV DELIMITER AS E'\t' ENCODING 'utf-8' QUOTE E'\b' NULL ''", f)
  </code></pre>
  
  <p>The <code>COPY</code> method was <strong>staggeringly faster</strong>.</p>
  
  <pre><code>METHOD      | TIME (secs)   | # RECORDS
  =======================================
  COPY_FROM   | 92.998    | 48339
  INSERT      | 1011.931  | 48377
  </code></pre>
  
  <p>But I cannot find any information as to why? How does it work differently than a multiline <code>INSERT</code> such that it makes it so much faster?</p>
  
  <p>See <a href="https://gist.github.com/jsheedy/efa9a69926a754bebf0e9078fd085df6" rel="noreferrer">this benchmark</a> as well:</p>
  
  <pre><code># original
  0.008857011795043945: query_builder_insert
  0.0029380321502685547: copy_from_insert
  
  #  10 records
  0.00867605209350586: query_builder_insert
  0.003248929977416992: copy_from_insert
  
  # 10k records
  0.041108131408691406: query_builder_insert
  0.010066032409667969: copy_from_insert
  
  # 1M records
  3.464181900024414: query_builder_insert
  0.47070908546447754: copy_from_insert
  
  # 10M records
  38.96936798095703: query_builder_insert
  5.955034017562866: copy_from_insert
  </code></pre>