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?)?

  • A DBMS -- relational or NoSQL -- is always going to use a lot more disk space to store data vs a relatively compact format such as CSV. – Colin 't Hart Mar 5 at 18:08
  • First thoughts are use a timeseries db (Timescaledb?) or a distributed column store (citusdb?). You could convert the data to Parquet format (parquet.apache.org/documentation/latest) which a number of FOSS tools can read. 6TB is going to take resouces & time however you handle it. – Michael Green Mar 6 at 11:19
  • I read about TimescaleDB but it seemed to me that it is not suitable for my problem because, if I understood well, it was designed to optimize for problems with very big sequences on time. In my case, I have only 800 time points, so the problem is more related to the fact that I have a lot of these sequences because of the high number of combinations of categories as well as the space dimension. But I am going to take a look on Citusdb and read more about Parqet format, that I don't know too much about. Thank you for the suggestion! – Raffaela Cunha Mar 6 at 17:44
  • And thank you for the information about disk space. I imagined that relational database would use more than csv files, but I thought that 3 times more was too much. – Raffaela Cunha Mar 6 at 17:48