Correct architecture for storing market data

Hi all,

I’m setting up a large storage of market data point. Probably around 4k stocks and i was wondering if i have to create programmatically a bucket for each stock or use one bucket only e put everything together. If I store every one minute a data point for 5k stock the bucket will grow exponentially during the years to come. So I’m a bit concern about this, if i have a bucket for each security instead it can never outgrow, and it looks more manageble. Every bucket/table will have 40 columns. Any suggestion about which one of the two architecture should i use? Thanks!

Hi @Scav ,
I hope you are doing well. So this really boils down to how retention and how you plan to use the data:

  1. If you can plan to keep historical data on some stocks longer than others then separating stocks into separate buckets makes sense.
  2. Separating buckets gives you more granularity in terms of security. I.e. with this token, I can only pull information about stocks in buckets 1, 4, 8.
  3. In terms of the data use this is more interesting. If each stock shares the same fields as each other then separating stocks by a tag is more performant for querying than using separate buckets. As essentially you would have to pull from each bucket and perform some form of union or join to do something interesting with them. I also believe Flux queries would be far simpler if all data was stored in one bucket.

So, in conclusion, I believe there are pros and cons to both architectures. It really boils down to your end goal. The same amount of data is being stored within the database no matter which approach you take. Its more of the question of how you plan to use, manage and maintain that data. You could potentially look at a hybrid approach where each bucket stores stocks on a specific vertical of business i.e. (manufacturing, finance, marketing, etc.)

Hi @Jay_Clifford

Ok understood, i think probably i will do four/five buckets deviding them by asset class. Thanks for the clarfication! best s