I'm saving about 3GB per day of financial orderbook data to a database (currently Mysql). In the coming weeks, I'm going to save trade and quote (bids/asks) data too.

Data is structured, but I'm doing many searches on it, for example get all orderbooks by stock symbol (which is very slow somehow). Or get all bids/asks by symbol. Currently, a query might take 5 seconds or so (select X where ticker like Y), and the size is only 20GB. In the future, at 1TB, I'm a bit concerned regarding speed per query.

What are some recommendations for cost efficient but also performant solutions for this task? I'm currently running a Mysql database on Digitalocean. Appreciate any help, also any tips regarding what to look into (indexing, sql vs nosql, performance tips, which specific databases would be better suited).

  • if wont be leveraging the "relational" capabilities of a rdbms, consider a nosql solution. May also consider R data frames instead of a nosql soltuion. No silver bullets. Answers from the community might improve if more detail were provided about how the collected data will be used. – Cary Reams Nov 13 '18 at 12:08

Any relational database will work. 1TB is huge, but feasible.

Don't use ticker LIKE Y unless you need to use wildcards. Instead use `ticker = 'Y'. Are you saying that the resultset is 20GB? Then the real problem is network time. It takes a lot of time to shovel that much data.

And have an index starting with ticker. It may be better to have the PRIMARY KEY start with ticker. If you would like more advice, I need to see more information.

A single row, properly indexed, will take only milliseconds to fetch.

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