Store large amounts of sensor data in SQL, optimize for query performance
<p>I need to store sensor data from various locations (different factories with different rooms with each different sensors). Data is being downloaded in regular intervals from a device on site in the factories that collects the data transmitted from all sensors.</p>

<p>The sensor data looks like this:</p>

<pre><code>collecting_device_id, sensor_id, type, value, unit, timestamp
</code></pre>

<p><code>Type</code> could be temperature, <code>unit</code> could be degrees_celsius. <code>collecting_device_id</code> will identify the factory.</p>

<p>There are quite a lot of different things (==types) being measured.</p>

<p>I will collect around 500 million to 750 million rows and then perform analyses on them.</p>

<p><strong>Here's the question</strong> for storing the data in a SQL database (let's say MySQL InnoDB on AWS RDS, large machine if necessary):</p>

<p>When considering query performance for future queries, is it better to store this data in one huge table just like it comes from the sensors? Or to distribute it across tables (tables for factories, temperatures, humidities, …, everything normalized)? Or to have a wide table with different fields for the data points?</p>

<p>Yes, I know, it's hard to say "better" without knowing the queries. Here's more info and a few things I have thought about:</p>

<ul>
<li>There's no constant data stream as data is uploaded in chunks every 2 days (a lot of writes when uploading, the rest of the time no writes at all), so I would guess that index maintenance won't be a huge issue.</li>
<li>I will try to reduce the amount of data being inserted upfront (data that can easily be replicated later on, data that does not add additional information, …)</li>
<li>Queries that should be performed are not defined yet (I know, designing the query makes a big difference in terms of performance). It's exploratory work (so we don't know ahead what will be asked and cannot easily pre-compute values), so one time you want to compare data points of one type in a time range to data points of another type, the other time you might want to compare rooms in factories, calculate correlations, find duplicates, etc.</li>
<li>If I would have multiple tables and normalize everything the queries would need a lot of joins (which probably makes everything quite slow)</li>
<li>Queries mostly need to be performed on the whole ~ 500 million rows database, rarely on separately downloaded subsets</li>
<li>There will be very few users (&lt;10), most of them will execute these "complex" queries.</li>
<li>Is a SQL database a good choice at all? Would there be a big difference in terms of performance for this use case to use a NoSQL system?</li>
<li>In this setup with this amount of data, will I have queries that never "come back"? (considering the query is not too stupid :-))</li>
</ul>