Suggest me a Database to store Tick data

techietrader
Hi Guys,
So many people ask the same question time and again that I felt odd at first to start the discussion on it, but thought it will help others as well who will be in the same dilemma as me with so many options available
So here are the options first-
SQL (Relational Database)
- SQL Server
- PostGresSql
- MySql

NoSql
- Redis
- MongoDb
- Cassandra

FlatFiles
- CSV
- TeaFiles

TimeSeries Database
- KDB+
- InfluxDb

This is what i m looking for-
1. Storing live streaming data to a database
2. Running a script that converts Tick data to OHLC and save the OHLC in same Database
3. Running one more script or coding in same script above to create one more type of Chart Pattern (Renko) and saving it in same database
4. Running one last script that will fetch data from same Database and trade using Zerodha trading API

Note- I will be using Python programming . So whichever database you suggest should have a python library
Most Important- Database should be free and opensource

Latency- Doesnot matter much as it will be a Medium Frequency model ( 5 min- 1 hour)
Scalability- Database should be able to handle tickdata for atleast 200 Equity Scrip or more


All suggestion on topic with reasonable explanations are Welcome.
Any one who already is in the role and/or associated with any prop trading firm and wants to suggest something off topic is also Welcome.

Thanks in advance
  • Kailash
    Since your requirement is not latency critical and you're only covering a couple hundred instruments, Postgres or MariaDB (MySQL) should do you good. We store billions of rows in these databases.

    If you want a true time series database, then checkout https://www.timescale.com (built atop Postgres)
  • pinkpanther
    i use KDB+ mostly for IOT(internet of things) data-very much like financial data . For streaming use case its the best in market. However it has a high learning curve. Very Fortran like language -q.But its robust and very stable in production scenarios.
  • sidverm
    @pinkpanther I have used KDB+ a lot while at an Investment bank. Now as a stand-alone HFT trader, I can't afford it. Do you work for a firm or has KX Systems slashed its prices? I am aware of 32 bit free version, but I don't think that would suffice for my needs.
  • ramatius
    MySQL with in-memory DB for live data (while prev days data can reside on disk) is a great combination for algo trading.
  • Sudheer
    @Kailash

    Pls Suggest Best Way to Store Tick Data for All F&O Stocks and Convert Tick Data to OHLC Etc and Throw Out Updated Info for All F&O Stocks..

    Note: 1. I Want Tick Data in Full Mode
    2. I Would be Running Analysis on Tick Data and Post Which Should Throw Updated Info for all F&O Stocks

    So Requesting To Kindly Suggest Better Way of Handling Such Large Tick data.. {Max Tick Data I Want to Store is for Only one day.. If Possible One Week Data Would be Great}

    Kindly Suggest
  • Kailash
    @Sudheer http://www.timescale.com (built on top of PostgreSQL) is a really good time series database.
  • phantomdrake
    @Sudheer @sujith Anybody compared InfluxData(InfluxDb) with TimeScale or Artic for storing the Tick Data. Currently using InfluxData but want to know the opinion of others.
  • autotrading
    @techietrader , @Kailash , @pinkpanther , @sidverm , @ramatius , @Sudheer

    Hello everyone. Thanks for this great discussion.
    Has anyone here practically tried https://www.timescale.com/ as suggested by kailash above ?

    Specially for storing and analyzing NSE Tick Data. Can someone please comment, if TimeScale database is well capable for such work or not ?

    I am a private trader and I plan to use it for personal analysis. So will the community edition "FREE" will suit my needs, or I will need to look for the paid version for any proper tick data analysis ? - https://www.timescale.com/products

    Any comments or suggestions are most welcome.
    Thanks a lot

    PS: @techietrader since you are the original thread starter, please do update us about your final decision. Which particular database did you finally choose for this work and how did that perform ? Thanks a lot.
Sign In or Register to comment.