Best database and table design for billions of rows of data I am writing an application that needs to store and analyze  large amounts of electrical and temperature data.

Basically I need to store large amounts of hourly electricity usage measurements for the past several years and for many years to come for tens of thousands of locations and then analyze the data in a not very complex manner.

The information that I need to store (for now) is Location ID, Timestamp (Date and Time), Temperature and Electricity Usage.

About the amount of the data that needs to be stored, this is an approximation, but something along those lines:
20 000+ locations, 720 records per month (hourly measurements, approximately 720 hours per month), 120 months (for 10 years back) and many years into the future. Simple calculations yield the following results:  


  20 000 locations x 720 records x 120 months (10 years back) = 1 728 000 000 records.


These are the past records, new records will be imported monthly, so that's approximately 20 000 x 720 = 14 400 000 new records per month.

The total locations will steadily grow as well.

On all of that data, the following operations will need to be executed:


Retrieve the data for a certain date AND time period: all records for a certain Location ID between the dates 01.01.2013 and 01.01.2017 and between 07:00 and 13:00.
Simple mathematical operations for a certain date AND time range, e.g. MIN, MAX and AVG temperature and electricity usage for a certain Location ID for 5 years between 07:00 and 13:00.


The data will be written monthly, but will be read by hundreds of users (at least) constantly, so the read speed is of significantly more importance.

I have no experience with NoSQL databases but from what I've gathered, they are the best solution to use here.
I've read on the most popular NoSQL databases, but since they are quite different and also allow for very different table architecture, I have not been able to decide what is the best database to use.

My main choices were Cassandra and MongoDB, but I since I have very limited knowledge and no real experience when it comes to large data and NoSQL I am not very certain. I also read that PostreSQL also handles such amounts of data well.

My questions are the following:


Should I use a NoSQL database for such large amounts of data. If not can I stick to MySQL?
What database should I use?
Should I keep the date and time in separate, indexed (if possible) columns to retrieve and process the data quickly for certain time and date periods, or can this be done by keeping the timestamp in a single column?
Is a time series data modeling approach appropriate here, and if not could you give me pointers for a good table design?


Thank you.
This is exactly what I do every day, except instead of using the hourly data, I use the 5 minute data. I download about 200 million records everyday, so the amount you talk about here is not a problem. The 5 minute data is about 2 TB in size and I have weather data going back 50 years at an hourly level by location. So let me answer you questions based on my experience:


Don't use NoSQL for this. The data is highly structured and fits a
relational database perfectly.
I personally use SQL Server 2016 and I have no problems applying computations across that volume of data. It was originally on a
PostgreSQL instance when I started my job and it couldn't handle the
volume of data as it was on a small AWS instance.
I would highly recommend extracting the hour portion of the date and storing it separate from the date itself. Believe me, learn from my mistakes!
I store the majority of data list-wise (DATE,TIME,DATAPOINT_ID,VALUE) but that is not how people will want to interpret the data. Be prepared for some horrendous queries against the data and vast amounts of pivoting. Don't be afraid to create a de-normalized table for result sets that are just too large to compute on the fly.


General tip: I store most of the data between two databases, the first is straight-up time series data and is normalized. My second database is very de-normalized and contains pre-aggregated data. As fast as my system is, I am not blind to the fact that users don't even want to wait 30 seconds for a report to load – even if I personally think 30 seconds to crunch 2 TB of data is extremely fast.

To elaborate on why I recommend storing the hour separate from the date, here are a few reasons why I do it that way:


The way that the electrical data is presented is by Hour Ending – therefore, 01:00 is actually the average of the electrical power for the previous hour and 00:00 is Hour Ending 24. (This is important because you actually have to search for two dates to include the 24 hour value – the day you are looking for plus the first mark of the following day.) However, the weather data is actually presented in a forward manner (actual and forecast for the next hour). In my experience with this data, consumers wish to analyze the effect that the weather has on the power price/demand. If you were to use a straight-up date comparison, you would actually be comparing the average price for the previous hour versus the average temperature for the following hour, even though the time stamps are the same. Storing the hour separate from the date allows you to apply transformations to the time with less performance impact than you would see applying a calculation to a DATETIME column. 
Performance. I would say at least 90% of the reports that I generate are graphs, normally plotting the price against the hour either for a single date or for a range of dates. Having to split out the time from the date can bog down the speed of the query used to generate the report depending on the date range that you want to see. It is not uncommon for consumers to want to see a single date, Year-on-Year for the past 30 years (in fact for weather this is required to generate the 30 year normals) – this can be slow. Of course you can optimize your query and add indexes, and trust me I have some insane indexes that I would rather not have but it makes the system run fast. 
Productivity. I hate having to write the same piece of code more than once. I used to store the date and time in the same column, until I had to write the same query over and over again to extract the time portion. After a while I just got sick of having to do this and extracted it to its own column. The less code you have to write the less chance there is of an error in it. Also, having to write less code means that you can get your reports out faster, nobody wants to be waiting all day for reports.
End users. Not all end users are power users (i.e. know how to write SQL). Having the data already stored in a format that they can bring into Excel (or other similar tool) with minimal effort will make you a hero in the office. If the users cannot access or manipulate the data easily, they will not use your system. Believe me, I designed the perfect system a couple of years ago and nobody used it because of this reason. Database design is not just about adhering to a predefined set of rules/guidelines, it is about making the system usable. 


As I said above, this is all based on my personal experience, and let me tell you, it has been a hard few years and a lot of redesigns to get to where I am now. Don't do what I did, learn from my mistakes and make sure you involve the end users of your system (or developers, report authors etc...) when making decisions about your database.