0

I have a timeseries pandas dataframe which dynamically increases the columns every minute as well as adds a new row:

Initial:

timestamp                100     200     300
2020-11-01 12:00:00       4       3       5

Next minute:

timestamp                100     200     300   500
2020-11-01 12:00:00       4       3       5     0
2020-11-01 12:01:00      14       3       5     4

The dataframe has these updated values and so on every minute.

so ideally, I want to design a database solution that supports such a dynamic column structure. The number of columns could grow to over 20-30k+ and since it's one minute timeseries, it will have 500k+ rows per year.

I've read that relational db's have a limit on the number of columns so that might not work here, but also, since I am setting the data for new columns and assigning a default value(0) to previous timestamps, I lose out on the DEFAULT param that's there on MySQL.

Eventually, I will be querying data for 1 day, 1 month to get the data for the columns and their values.

Please suggest a suitable database solution for this type of dynamic row and column data.

Improve this question
3
  • 1
    You may want to explain your data model not your implementation of it. A table represents a real-life entity of one kind or another, and I'm hard-pressed to imagine an entity that gets a new attribute (represented by a column) every minute. – mustaccio Dec 1 '20 at 1:55
  • To second @mustaccio it's difficult to come up with a correct answer (I have a feeling JD's is close, but not quite) but why you would add a new column with time is puzzling and makes me think the answer you accepted won't be the best solution. – bbaird Dec 1 '20 at 16:52
  • it's more of a 3 scale data, where time and price combination have a value. and it's updated every minute based off the previous value – Pavneet Singh Dec 1 '20 at 18:18
0

Usually a dynamic data problem like this can be solved by storing the dynamic portion of the schema in its own table, transposed as rows.

For example you can have an Intervals table where one column is called Interval and another column called Values. Interval would store 100, 200, 300 etc for each instance of values for that interval.

You could either store the Timestamp as a column in this table too, or my recommendation would be to normalize Timestamp into its own table with a TimestampId that is a foreign key field in your Intervals table.

Implementing your schema this way then allows you to not worry about how many dynamic Intervals are created since this it's a row based generic solution.

Improve this answer
5
  • The challenge here is that, each timestamp data has dynamic values for each columns, so if i have 10k rows and 9k columns, I would have about 90 million rows that would keep expanding. how about storing timestamp in one column, and the other row data as a json in another column, then when querying, I could query and get all the jsons and convert it to a dataframe? – Pavneet Singh Dec 1 '20 at 1:33
  • You can do a JSON column but in terms of performance, it's going to be the same amount of data no matter which way you splice it. That being said, 90 million rows isn't a lot of data to store in a table (I've managed 10s of billions of rows in 1 table in Microsoft SQL Server). Depending on how much data you need to recall back (SELECT) at any given time and the hardware you're running on will affect how quickly you'll be able to pull your data out of the table. But using proper indexing generally can keep you sufficient even in a table with billions of rows. – J.D. Dec 1 '20 at 1:58
  • Also for a point of reference, when I managed a table with 10s of billions of records, I was able to pull any single record back out of it within milliseconds on a server with only 16 GB of RAM, 8 core processor, and SSD. I was able to pull 1 million data points from the same table on the same hardware in about 10 seconds. This is because of proper indexing which stores logical references to the data points in a B-Tree which is a pretty performant data structure. (Not sure what your data needs will be later on for recalling the data, or the hardware you're running on.) – J.D. Dec 1 '20 at 2:01
  • 1
    Alright, I understand what you mean. Per my calculations for a year of 1 minute data, it would be 500k rows, with about 40k columns so that's about 20 trillion rows in that table. I'm guessing any system with a 64+ gig ram would work well. Thanks for your suggesstions – Pavneet Singh Dec 1 '20 at 2:05
  • No problem. You'll definitely need to do some testing. Not sure how you plan to use the data, but if you can reduce your historical data by rolling it up into your final calculated results and store that results data instead for a rolling timeframe of data, you can reduce your overall number of records by a lot. – J.D. Dec 1 '20 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.