This talk explores a unique approach to scaling financial reporting at Zerodha, a leading Indian stock brokerage firm. Faced with the challenge of delivering millions of reports daily to millions of users, the team developed a novel system, aptly named “Dung Beetle,” that leverages PostgreSQL in unconventional ways.
The Challenge:
Massive Data Volumes: Zerodha handles an immense volume of financial transactions, resulting in massive datasets.
Complex Reports: Many reports require intricate SQL queries involving multiple joins across large tables, leading to slow query execution.
Scalability Issues: Traditional approaches, such as adding read replicas, become prohibitively expensive and complex at this scale.
The Dung Beetle Solution:
Dung Beetle is a lightweight middleware system designed to:
Asynchronous Request Handling: Instead of directly hitting the main databases, user requests are queued asynchronously. This prevents overloading the databases and allows for controlled query execution.
Results Caching:
Each report is executed once and its results are stored in a dedicated table within a separate PostgreSQL “results” database.
Subsequent requests for the same report simply retrieve the results from the cached table, significantly improving response times.
Dynamic Table Creation:
Every user’s report generates a unique table in the results database. This approach, while unconventional, demonstrates the flexibility of PostgreSQL.
Efficient Metadata Handling: Despite millions of tables, the results database maintains high performance due to efficient metadata management within PostgreSQL.
Daily Reset: The results database is reset daily to free up space and maintain performance.
Key Takeaways:
PostgreSQL’s Versatility: The success of Dung Beetle highlights the versatility and robustness of PostgreSQL, even in unconventional use cases.
Efficient Scalability: This approach provides a highly scalable and cost-effective solution for handling large volumes of complex reporting queries.
Innovative Solutions: By thinking outside the box, the team at Zerodha demonstrated how to leverage existing technologies in novel ways to solve challenging problems.This innovative approach to scaling financial reporting at Zerodha provides valuable insights into how organizations can leverage the power of PostgreSQL and creative engineering to address complex challenges in data management and analysis.
Leave a Reply