title: Efficiently merging large data.tables
body: <p>I have two fairly large <code>data.table</code> objects that I want to merge.</p>
  
  <ul>
  <li><code>dt1</code> has 500.000.000 observations on 5 columns. </li>
  <li><code>dt2</code> has 300.000 observations on 2 columns.</li>
  </ul>
  
  <p>Both objects have the same <code>key</code> called <code>id</code>.</p>
  
  <p>I want to <code>left_join</code> information from <code>dt2</code> into <code>dt1</code>. </p>
  
  <p>For example:</p>
  
  <pre><code>dt1  &lt;- data.table(id = c(1, 2, 3, 4),
                 x1 = c(12, 13, 14, 15),
                 x2 = c(5, 6, 7, 8),
                 x3 = c(33, 44, 55, 66),
                 x4 = c(123, 123, 123, 123))
  
  dt2 &lt;- data.table(id = c(1, 2, 3, 4),
                x5 = c(555, 666, 777, 888))
  setkey(dt1, id)
  setkey(dt2, id)
  
  dt2[dt1, on="id"] 
  
  &gt; dt2[dt1, on="id"]
     id  x5 x1 x2 x3  x4
  1:  1 555 12  5 33 123
  2:  2 666 13  6 44 123
  3:  3 777 14  7 55 123
  4:  4 888 15  8 66 123
  </code></pre>
  
  <p>However, when merging my original data R can't allocate memory anymore. Yet, the output of the merge fits in the RAM. </p>
  
  <p><strong><em>What is the most efficient (speed vs. memory limitations) way of getting this large merge done?</em></strong></p>
  
  <p>Should we split-apply-combine?</p>
  
  <p>Should we use a DB library to get this done?</p>
  
  <p>How would you do this efficiently?</p>