Hi Currently I am using PostgresQL to store my Interactive Broker market data. Thinking to move my data to influxDB, then use Grafana to visualise (see example of Grafana below). Anyone have used influxdb and grafana? Any thoughts on these two software? From this website, it seems InfluxDB is #1. Any alternative recommended? Thanks
I've tried or looked at basically everything reasonable. The choice of DB is really dependent on the type of data you have. If you have order book data, you can pretty much throw relational DBs out the window in my experience; but they work okay to discrete bar data. I used kdb+ 32 bit and relation dbs when I was first starting out, then moved on to MongoDB. Threw mongo out fairly quickly and finally decided to roll my own. The stuff like contract specs and bars is boring, so I'll leave that aside, you could do those in pretty much any DB easily. For market depth data, I use binary files with a <key, value>, where values are book updates which implement an interface containing common attributes I use to query the collection. I then read the files into a dictionary<long,IMyInterface> and use LINQ to perform all my queries on it. It has worked fairly well for me. I'm sure it's not perfect, but I prefer it to wrestling with databases honestly.
The choice of database should take throughput and schema into account. If you're storing OHLC bars for 1000 tickers, any database would do. If your insert rate is over 100K per second, than it's a different story. It also depends how you use the data - backtesting vs streaming calculations, e.g. pairs trading. How do you move data from IB to PostgreSQL now?
I am focusing on just Emini future, S&P 500. In the next two months, I am just using IB as data source. For IB historical data, I store them in PostgreSQL. However for market data and order book, I am not sure what to use, that is why I am exploring InfluxDB. Also considering MongoDB. Still working on understanding which methods should I call to capture the tick prices, I believe tickPrice() and tickSize(). Not sure about tickGeneric(), tickString(), still looking at the post below. https://www.sierrachart.com/SupportBoard.php?PostID=1709#P1709 " reqMktData() - Call this function to request market data. The market data will be returned by the tickPrice() and tickString() events. void reqMktData(TickerID id, const Contract &contract, CString genericTicklist, bool snapshot) The String, generericTickList is passed as string "233" 1.Use event tickPrice() to retrieve the latest askprice (ticktype=1) and bidprice (ticktype=2). 2.Use event tickString() to retrieve rtVolume events (tickType=48). IB's documentation mistakenly says that RTvolume is passed by tickGeneric() but this is wrong. RTVolume is a string and is passed by the tickString() event. " For historical data OHLC 1 second bar for S&P 500, I just use Java API to insert directly to PostgreSQL, there is nothing in between, thinking to use RabbitMQ, seems overkill for 1 second bar. For tick data and market order, of emini S&P 500, not sure how many insert per second, I believe should be less than 10K insert rate per second. However as you mentioned, on the schema, I need to think how should I store this tick data and order book. May be I end up building my own structure like cjbuckley4.
The time resolution of reqMktData https://www.interactivebrokers.com/en/software/api/api.htm is 1 ms but I'm not sure how often you see multiple events with the same timestamp. Someone probably has statistics into how many trades are executed per day for any given ticker on average. Lets assume there is 1 event per 1 millisecond. This means 1K events per second. Columnar databases often measure throughput for each metric separately. Assuming each event is RTVolume which has 4 numeric fields (or 2 if you don't need daily volume and vwap) this translates into 4K/second. Just about any time series database will be able to handle it.