storing level 2 data

[otctrade]

What would be the best way to store Level2 data in mysql/postgres ?

 

[otctrade]

I was thinking using the following structure:

symbol, mmid, bid_price,bid_size,ask_price,ask_size

 

[Girija]

You are missing timestamp. Create table, optionally index and insert rows?.....
What is the use case?. This changes pretty fast. Barring time and sale, how do you plan to use the information?
If you know exactly what you are doing even sqllite is more than enough.
Look for lightweight in memory type databases.

 

[otctrade]

You are right I did miss timestamp. It would be for display only (vuejs web app) and only 1 client would be fetching the data from db.

Would Redis be better? I was thinking of using a combination of the pub/sub feature of Redis and socket.io to stream from the C# API into my web app. (each symbol being a separate channel)

 

[traider]

Flat file is one option. You need to consider the speed of write and read. Best if you know HFT traders and ask them instead of reinventing the wheel.

 

[groovychicken]

I second the flat-file option. Databases aren't made for this kind of an access profile. If the data is large and you will *always* be looking through the old data linearly, just don't use a database.

Separate file for each instrument/day or maybe instrument/hour.

 

[Girija]

Displays don't need a database. As you fetch data from the stream format and route it to the gui content.

 

[dholliday]

As others have said, always store your data in a flat-files. Store all the fields available. I store each symbol in its own file exactly as it comes from the vendor in CSV format, which makes it easy to look at the raw data to debug problems. Also, it's easy to stick it in a spreadsheet if you have the need.
If you find you need a database for something, write a script to load it from the files.

 

[2rosy]

take a look at apache parquet
and if you use python then this package pystore