[Python] Where to begin designing a high concurrency system for realtime trading feeds?...

I am in a bit of a predicament. I just recently (last week) took up a job as a fresher(had no real experience programming 6 months ago) to now they have handed me a live project(in python) to optimize the websocket and the overall flow of things. The project is a realtime stock market data web-application which uses websockets(using FastAPI's websockets) to serve the data to the client.

I honestly have no clue what I am doing? The main database in MS SQL Server in which there are two tables namely tokenDetails(token - symbol & some values) and FeedFO(symbol-values). For every screen(like gainer-loser, straddle), i have separated out the processes that does the calculation and the websocket part so the calculation does not hinder client communication.

I have also implemented redis pub-sub and cache the latest calculated values to mitigate any delays in sending the data to client. I have also added connection pooling to make sure no two queries are run using the same client. But I the most problematic thing is the SQL queries for the calculation of the screener's data. I constantly face deadlocks due the live data being updated on the main tables tick by tick. Some numbers: for now there are three screens. Each provides data for 250 symbols each all of which have about 3 expiry dates on average so the calculation for everything is separate one cannot be used for the other not even for the same symbol.

How do I avoid this? Is there a workaround that doesn't require changing the server's settings. I know anything would be better than what I am doing. Assume I have not tried your solution and tell me please.

Is there a database better suited for this type of workload?

Continue reading...