title: High speed time series query postgresql hardware considerations
body: <p>Just have some general questions on hardware choices for polling lots of low-medium density sensor data time series as fast as possible.</p>
  <p><strong>System Overview</strong></p>
  <p>The data consists of multiple time series of approx 50-100K data points from various sensors at different locations at different times. ~15 columns of data in the main table, but some columns have long array values in them. I'm interfacing with C#/python to send queries to a local database and then work on the returned data.</p>
  <p><strong>The Problem and Some Thoughts</strong></p>
  <p>As it stands, the logging server is on average hardware (budget 4tb hdds, basic quadcore, 8gb ram) and depending on the amount of data requested in the query, it takes forever (re: &quot;annoying amounts of waiting&quot;) to return an entire time series. I have thousands of independent time series and I want to compare multiple ones against each other. Returning a single time series takes at least 30 for 50K rows with a * can take 30+ seconds, but as low as 100ms for only a few columns. A lot of different queries are used so I don't have the luxury of caching for repeated queries.</p>
  <p>So what I was thinking of is, instead of work directly from the online server, make an offline copy of the database onto more dedicated hardware that can be used for faster analysis. I don't need all of the data at any one time (i.e. I can pick a a location and time range, copy that offline, and work on that)</p>
  <p>The table design is very basic. The most often queried table data has primary key:</p>
  <pre><code>PRIMARY KEY (location_id, time_logged, sensor_id)
  </code></pre>
  <p>With simple query like</p>
  <pre><code>SELECT * FROM table 
  WHERE location_id = 2154321 AND sensor_id = 254;
  </code></pre>
  <p>This is most common, and will be used along with some additional WHERE conditions.</p>
  <p>There are thousands of location_id and potentially dozens of sensor_id for each location_id</p>
  <p><strong>The Questions</strong></p>
  <p>(with regard to the above table/pk/query setup)</p>
  <ol>
  <li>How much does more RAM help with faster queries. i.e. 64GB vs 8GB</li>
  <li>How much does faster cpu / more cores help with faster queries. What kind of CPU (quad/6/12+) would provide the best speedup. Is there diminishing returns?</li>
  <li>How would one set up hard disk drives to help with faster queries (RAID cluster with SSD or mechanical).</li>
  <li>Would getting a couple or even 4+ cheap mechanical hard disk drives provide a significant speedup in RAID?</li>
  <li>I've read about columnar store and how it can be useful for time series (<a href="https://www.citusdata.com/blog/76-postgresql-columnar-store-for-analytics" rel="nofollow noreferrer">https://www.citusdata.com/blog/76-postgresql-columnar-store-for-analytics</a>). Can anyone shed any insight on this and is it worth setting up?</li>
  <li>Will increasing the planner statistics value help significantly?</li>
  <li>Any general recommendations / first steps to get the best kind of query speedup? Another dedicated PC with multi-core and lots of ram? A NAS? Dedicated PC with multi-drive RAID</li>
  </ol>
  <p>I'm relatively new to working with databases so don't really know what to expect in terms of performance so any pointers would be helpful.</p>
  <p>thanks!</p>