title: Does sqlite3 compress data?
body: <p>I've got an 7.4Gb csv file. After converting it to a sqlite database with a <a href="https://gist.github.com/2838964" rel="noreferrer">python script</a> the output DB is 4.7Gb, around 60% of the original size.</p>
  
  <p>The csv has around 150,000,000 rows. It has header:</p>
  
  <pre><code>tkey,ipaddr,healthtime,numconnections,policystatus,activityflag
  </code></pre>
  
  <p>And each row looks something like </p>
  
  <pre><code>261846,172.10.28.15,2012-02-03 16:15:00,22,1,1
  </code></pre>
  
  <p>The script uses healthtime to split the data into tables 192 tables</p>
  
  <p>When I first saw these numbers, I assumed I had made an error someplace. How big of a reduction in file size should I expect from the added efficiency of only writing the healthtime 192 times instead of 150,000,000 times?</p>
  
  <p><strong>EDIT:</strong> As soon as I posted this I realized the answer. I'm removing about 40% of the string, hence the 40% reduction in size. </p>
  
  <p><strong>Edit 2</strong> Let's calculate the difference in size between the plain text:</p>
  
  <pre><code>"261846,172.10.28.15,2012-02-03 16:15:00,22,1,1" 
  </code></pre>
  
  <p>And the data base entry:</p>
  
  <pre><code>db(261846,'172.10.28.15',22,1,1)
  </code></pre>
  
  <p>First of all, we drop from 46 to 26 characters in plain text representation. </p>
  
  <p>The remaining characters are:</p>
  
  <pre><code>"261846,172.10.28.15,22,1,1"
  </code></pre>
  
  <p>or 26 bytes. If each integer needs to be stored in 32 bit (4 bytes), then we have:</p>
  
  <p>12 bytes (ipaddr) + 4 bytes * 4 (integer fields) = 28 bytes.</p>
  
  <p>So it looks like converting to integers makes the storage slightly less efficient, and all of my gains come form reducing the number of characters stored in each row.</p>