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.
  • gautamsatpathy
    Realize this is a old thread.

    I am new to Kite Connect and am building my own system in Java. Just at the point of picking a Time Series DB for the Tick data. I looked at InfluxDB but realized that their Windows 10 support is still in Beta. And since I am working on a Windows 10 laptop at this time, I didnt want to invest the time and energy required for InfluxDB. Rather I am looking to get started with a immediate solution and then migrate the ticker data system to my linux box to operate independently.

    Now looking at QuestDB, a pure Java system which seems to provide the features needed.

    Any pointers? Any experience that you can share?

    Data capture, even for a large number of tokens, should not be a problem. What I am looking for is data aggregation on the fly to build OHLC data sets for higher time frames from the tick data without having to fetch the data from DB to application layer for aggregation.

    Another feature I am curious about is running indicator computation code directly in the DB. Anyone has any experience with that? And if you are running the computations in the application layer then what are you using? DIY or a TA library?
Sign In or Register to comment.