title: What is the most efficient way to store 2-D timeseries in a database (sqlite3)
body: <p>I am performing large scale wind simulations to produce hourly wind patterns over a city. The results is a time series of 2-dimensional contours. Currently I am storing the results in <code>SQLite3</code> database tables with the following structure</p>
  
  <pre><code>Table: CFD
  id, timestamp,           velocity, cell_id
  1 , 2010-01-01 08:00:00, 3.345,    1 
  2 , 2010-01-01 08:00:00, 2.355,    2 
  3 , 2010-01-01 08:00:00, 2.111,    3 
  4 , 2010-01-01 08:00:00, 6.432,    4 
  .., ..................., .....,    .
  1000 , 2010-01-01 09:00:00, 3.345,    1 
  1001 , 2010-01-01 10:00:00, 2.355,    2 
  1002 , 2010-01-01 11:00:00, 2.111,    3 
  1003 , 2010-01-01 12:00:00, 6.432,    4 
  .., ..................., .....,    .
  </code></pre>
  
  <p>Actual create statement:</p>
  
  <pre><code>CREATE TABLE cfd(id INTEGER PRIMARY KEY, time DATETIME, u, cell_id integer)
  CREATE INDEX idx_cell_id_cfd on cfd(cell_id)
  CREATE INDEX idx_time_cfd on cfd(time)
  </code></pre>
  
  <p>(There are three of these tables, each for a different result variable)</p>
  
  <p>where <code>cell_id</code> is a reference to the cell in the domain representing a location in the city. See this picture to have an idea of what it looks like at a specific timestep.<a href="https://i.stack.imgur.com/BO5ap.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BO5ap.jpg" alt="contour at timestep"></a></p>
  
  <p>The typical query performs some kind of aggregation on the time dimension and group by on <code>cell_id</code>. For example, if I want to know the average local wind speed in each cell during a specific time interval, I would execute</p>
  
  <pre><code>select sum(time in ('2010-01-01 08:00:00','2010-01-01 13:00:00','2010-01-01 14:00:00', ...................., ,'2010-12-30 18:00:00','2010-12-30 19:00:00','2010-12-30 20:00:00','2010-12-30 21:00:00') and u &gt; 5.0) from cfd group by cell_id
  </code></pre>
  
  <p>The number of timestamps can vary from 100 to 8,000.</p>
  
  <p>This is fine for small databases, but it gets much slower for larger ones. For example, my last database was 60GB, 3 tables and each table had 222,000,000 rows.</p>
  
  <p>Is there a better way to store the data? For example:</p>
  
  <ul>
  <li>would it make sense to create a different table for each day?</li>
  <li>would be better to use a separate table for the timesteps and then use a join?</li>
  <li>is there a better way of indexing?</li>
  </ul>
  
  <p>I have already adopted all the recommendations in this <a href="https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite">question</a> to maximise the performance.</p>