title: Bulk/batch update/upsert in PostgreSQL
body: <p>I'm writing a Django-ORM enchancement that attempts to cache models and postpone model saving until the end of the transaction. It's all almost done, however I came across an unexpected difficulty in SQL syntax.</p>
  
  <p>I'm not much of a DBA, but from what I understand, databases don't really work efficiently for many small queries. Few bigger queries are much better. For example it's better to use large batch inserts (say 100 rows at once) instead of 100 one-liners.</p>
  
  <p>Now, from what I can see, SQL doesn't really supply any statement to perform a batch update on a table. The term seems to be <em>confusing</em> so, I'll explain what I mean by that. I have an array of arbitrary data, each entry describing a single row in a table. I'd like to update certain rows in the table, each using data from its corresponding entry in the array. The idea is very similar to a batch insert. </p>
  
  <p>For example: My table could have two columns <code>"id"</code> and <code>"some_col"</code>. Now the array describing the data for a batch update consists of three entries <code>(1, 'first updated')</code>, <code>(2, 'second updated')</code>, and <code>(3, 'third updated')</code>. Before the update the table contains rows: <code>(1, 'first')</code>, <code>(2, 'second')</code>, <code>(3, 'third')</code>.</p>
  
  <p>I came accross this post:</p>
  
  <p><a href="https://stackoverflow.com/questions/1006969/why-are-batch-inserts-updates-faster-how-do-batch-updates-work/1007006#1007006">Why are batch inserts/updates faster? How do batch updates work?</a></p>
  
  <p>which seems to do what I want, however I can't really figure out the syntax at the end.</p>
  
  <p>I could also delete all the rows that require updating and reinsert them using a batch insert, however I find it hard to believe that this would actually perform any better.</p>
  
  <p>I work with PostgreSQL 8.4, so some stored procedures are also possible here. However as I plan to open source the project eventually, any more portable ideas or ways to do the same thing on a different RDBMS are most welcome.</p>
  
  <p><strong>Follow up question:</strong> How to do a batch "insert-or-update"/"upsert" statement?</p>
  
  <p><strong>Test results</strong></p>
  
  <p>I've performed 100x times 10 insert operations spread over 4 different tables (so 1000 inserts in total). I tested on Django 1.3 with a PostgreSQL 8.4 backend.</p>
  
  <p>These are the results:</p>
  
  <ul>
  <li>All operations done through Django ORM - each pass <strong>~2.45 seconds</strong>,</li>
  <li>The same operations, but done without Django ORM - each pass <strong>~1.48 seconds</strong>,</li>
  <li>Only insert operations, without querying the database for sequence values <strong>~0.72 seconds</strong>,</li>
  <li>Only insert operations, executed in blocks of 10 (100 blocks in total) <strong>~0.19 seconds</strong>,</li>
  <li>Only insert operations, one big execution block <strong>~0.13 seconds</strong>.</li>
  <li>Only insert operations, about 250 statements per block, <strong>~0.12 seconds</strong>.</li>
  </ul>
  
  <p>Conclusion: execute as many operations as possible in a single connection.execute(). Django itself introduces a substantial overhead.</p>
  
  <p>Disclaimer: I didn't introduce any indices apart from default primary key indices, so insert operations could possibly run faster because of that.</p>