title: How do I do large non-blocking updates in PostgreSQL?
body: <p>I want to do a large update on a table in PostgreSQL, but I don't need the transactional integrity to be maintained across the entire operation, because I know that the column I'm changing is not going to be written to or read during the update.  I want to know if there is an easy way <em>in the psql console</em> to make these types of operations faster. </p>
  
  <p>For example, let's say I have a table called "orders" with 35 million rows, and I want to do this: </p>
  
  <pre><code>UPDATE orders SET status = null;
  </code></pre>
  
  <p>To avoid being diverted to an offtopic discussion, let's assume that all the values of status for the 35 million columns are currently set to the same (non-null) value, thus rendering an index useless.</p>
  
  <p>The problem with this statement is that it takes a very long time to go into effect (solely because of the locking), and all changed rows are locked until the entire update is complete.    This update might take 5 hours, whereas something like </p>
  
  <pre><code>UPDATE orders SET status = null WHERE (order_id &gt; 0 and order_id &lt; 1000000);
  </code></pre>
  
  <p>might take 1 minute.   Over 35 million rows, doing the above and breaking it into chunks of 35 would only take 35 minutes and save me 4 hours and 25 minutes.</p>
  
  <p>I could break it down even further with a script (using pseudocode here):</p>
  
  <pre><code>for (i = 0 to 3500) {
    db_operation ("UPDATE orders SET status = null
                   WHERE (order_id &gt;" + (i*1000)"
               + " AND order_id &lt;" + ((i+1)*1000) " +  ")");
  }
  </code></pre>
  
  <p>This operation might complete in only a few minutes, rather than 35.  </p>
  
  <p>So that comes down to what I'm really asking.  I don't want to write a freaking script to break down operations every single time I want to do a big one-time update like this.   Is there a way to accomplish what I want entirely within SQL?</p>