title: Database Implementation Help : Time-Series data
body: <p>This is the re-submission of my <a href="https://stackoverflow.com/questions/10588933/ordered-timeseries-data-stock-market-data-using-structure-variants">previous question</a>:</p>
  
  <p>I have a collection of ordered time-series data(stock minute price information). My current database structure using PostgreSQL is below:</p>
  
  <p><code>symbol_table</code> - where I keep the list of the symbols with the <code>symbol_id</code> as a primary key(serial).
  <code>time_table, date_table</code> - time/date values are stored there. time_id/date_id are primary keys(serial/serial).</p>
  
  <p>My main <code>minute_table</code> contains the minute pricing information where
  <code>date_id|time_id|symbol_id</code> are primary keys(also foreign keys from the corresponding tables)</p>
  
  <p>Using this main <code>minute_table</code> I'm performing different statistical analyses and keep the results in a separate tables, like <code>one_minute_std</code> - where one minute standard deviation measures are kept. </p>
  
  <p>Every night I'm updating the tables with the current price information from the last day's closing prices.</p>
  
  <p>With the current implementation my tables contain all the symbols with around 50m records each. 
  Primary keys are indexed. </p>
  
  <p>If I want to query for <code>all the symbols where closing price > x and one_minute_std >2 and one_minute_std &lt; 4 for the specific date</code> it takes about 3-4 minutes for the search.</p>
  
  <p>To speed up the process I was thinking of separating each symbol to its own table but not 100% sure if this is a 'proper' way of doing it.</p>
  
  <p>Could you advise me on how I can speed up the query process?</p>