title: Performance: Using indexing and partitioning (PostgreSQL)
body: <p>I have a fairly simple database model. My table "main" looks like this:</p>
  
  <pre><code>| id (PK) | device_id (int) | msg_type (int) | rawdata (text) | timestamp (date+time) |
  </code></pre>
  
  <p>Therefore each received message is stored within this table, including the message type, timestamp, the device which sent it and the rawdata. </p>
  
  <p>In addition for each possible msg_type (in total approx. 30) I have a separate table storing the parsed raw data. Example for the table "main_type1":</p>
  
  <pre><code>| id (PK) | main_id (FK) | device_id (int) | attribute_1 | attribute_2 | attribute_n |
  </code></pre>
  
  <p>(Structure differs for each msg_type and the messages are not equally distributed meaning some tables are hugh some tables are small).</p>
  
  <p>Please note that the device_id is always included within the rawdata, so each table has this column.</p>
  
  <p><strong>Now to my problem:</strong></p>
  
  <p>I used to have queries such as:</p>
  
  <pre><code>select attribute_1, attribute_2 from main_type1 inner join main on main_type1.main_id = main.id where timestamp &gt; X and timestamp &lt; Y and main.device_id = Z
  </code></pre>
  
  <p>At the beginning everything was sufficient and also fast. But now my database has more than 400.000.000 entries in "main". Queries are taking up to 15 minutes now. </p>
  
  <p><strong>Indexing</strong></p>
  
  <p>I tried to use indexing such as:</p>
  
  <pre><code>CREATE INDEX device_id_index ON main (device_id);
  </code></pre>
  
  <p>Well, now I can retreive data much faster from the main table, but it does not help with joins. My biggest problem here is that I  stored the timestamp information only in the main table. So I have to join all the time... is this a general failure of my database model? I tried to avoid storing timestamps twice.</p>
  
  <p><strong>Partitioning</strong></p>
  
  <p>Would one solution be to create a new table with rawdata for each device_id by using partitioning? I would then (of course automatically) create appropriate partitions such as:</p>
  
  <pre><code>main_device_id_343223
  main_device_id_4563
  main_device_id_92338
  main_device_id_4142315
  </code></pre>
  
  <p>Would this give me speed advantages related to the joins? What other options do I have? For the sake of completeness: I am using PostgreSQL</p>