I have made a simulation experiment to obtain time series measurements at different points along a power line. These measurements were done for different types of power lines in different test conditions. So, for each combination of the 8 categories among test conditions and power line types, which gives a total of 60 thousand possible combinations of categories values, I have 6 measurements fields, and, for each of them, a csv file was generated. Each csv file contains data related to a type of measurement for a range of time values (rows, ~ 800 time points) and a range of distance values (columns, ~ 100 columns). Unfortunately, I can not change the format this software outputs the data. The csv files occupies a total of almost 6TB (more than 8 million files). I need to make this data easy to be analysed from different perspectives (e.g. choosing values for 7 of the available categories and compare the time series in a given space point among different possible values of the 8th category), preferentially using python to make queries and analyze data.
I am trying to migrate the csv files to a PostgreSQL database but it is not a requirement, it is just a solution I am working on. So, I modeled the data to have a big table named "measurements" where each row has 8 categories columns (where the entries are integers as foreign keys for each category table), 2 columns for time and space points (float) and 6 columns with the measurements (float). However I checked the disk usage of my database for some insertions and I saw that comparing with csv files, it is occupying almost 3 times of disk space. It is being also very time consuming to transform data from csv files to insert to SQL tables (I am currently using python pandas dataframes as an intermediary format). Any suggestions on how to model the data so that the disk usage is lower? Is there a better way to model the data to optimize queries as the mentioned above? Is there another type of database that may be more suitable for this problem (maybe NoSQL?)?