7+ million Postgres tables | Kailash Nadh | IndiaFOSS 2024 | FOSS United

Summary

Kalash Nad, founder of Zerodha, discusses a novel hack developed to scale financial report generation for millions of users. The system relies on asynchronous queuing and caching via PostgreSQL to handle vast amounts of data from a trillion-row database. Reports are generated by queuing requests and storing results in temporary tables, allowing for quick retrieval. With over 7 million tables on average, this lightweight, distributed system ensures high performance without overloading the backend, proving PostgreSQL’s robustness for large-scale data handling. The solution has become a critical part of Zerodha's infrastructure for efficient and scalable reporting.

Takeaways

• 😀 **Massive Scale Problem**: Zerodha deals with trillions of rows in their financial database, making it challenging to generate reports quickly, especially during peak usage (e.g., tax filing day).
• 😀 **Synchronous Querying is Not Viable**: Direct queries to the database for complex reports lead to slow response times and overloading of resources.
• 😀 **Asynchronous Queuing Solution**: Instead of querying the database directly, requests for reports are queued asynchronously, allowing the system to process them at a controlled pace.
• 😀 **Dung Beetle Middleware**: A lightweight middleware system called Dung Beetle manages report requests, queuing them, and ensuring the reports are generated efficiently without overloading the backend database.
• 😀 **Postgres Results Database**: Instead of re-running SQL queries for every request, the results are cached in separate tables within a Postgres database, improving speed and scalability.
• 😀 **Independent Scaling**: By separating the report generation logic from the main app, Zerodha can scale reporting and database queries independently of the application layer.
• 😀 **Why Postgres?**: Postgres is used as the results database due to its robustness, efficiency in handling large numbers of tables, and its ability to restart quickly even with millions of tables.
• 😀 **Traffic Control and Load Management**: The Dung Beetle system controls the traffic flow, ensuring that queries are processed at a rate that the backend database can handle, without overwhelming it.
• 😀 **Distributed and Parallel Processing**: Multiple instances of Dung Beetle can run concurrently, distributing the load and making it possible to handle millions of simultaneous requests efficiently.
• 😀 **Ephemeral Caching**: The system uses a temporary cache, where results are stored and discarded as needed, preventing the database from being overloaded by unnecessary data storage.
• 😀 **Simple, Effective Architecture**: Despite being a simple hack, the Dung Beetle solution scales to handle massive loads, with the system handling up to 7 million tables in a single Postgres instance on an average day.

Q & A

What is the core problem Zerodha faced with report generation?

Zerodha struggled with generating complex financial reports for millions of users daily, as their databases contained hundreds of billions of rows, making traditional synchronous queries slow and difficult to scale.

How does the Dung Beetle system help scale report generation at Zerodha?

The Dung Beetle system asynchronously queues report generation requests, processes them at a manageable rate, and caches the results in PostgreSQL. This reduces the load on the databases, preventing them from being overwhelmed by concurrent requests.

What role does PostgreSQL play in Zerodha's reporting system?

PostgreSQL serves as the results cache, where the output of each query is stored in a unique table. When users request the same report, it is served instantly from this cache, avoiding the need to run heavy queries repeatedly.

Why are the results stored in separate tables for each report in PostgreSQL?

Each report's results are stored in a separate table to ensure that different reports don't interfere with each other. This also allows Zerodha to scale and manage millions of individual report tables without overloading the database.

What is the significance of asynchronous queuing in the system?

Asynchronous queuing allows report generation requests to be processed without overwhelming the database. It ensures that queries are executed slowly and steadily, based on the database's capacity, instead of processing millions of requests simultaneously.

How does Dung Beetle control traffic to prevent database overload?

Dung Beetle uses a traffic control mechanism where it queues incoming report requests and then processes them at a manageable rate. It ensures that the large databases, like PostgreSQL and ClickHouse, are not overloaded with too many concurrent queries.

What happens when a report is generated in Zerodha's system?

When a report is generated, Dung Beetle queues the request, processes it asynchronously, stores the results in a dedicated PostgreSQL table, and then the app fetches the data from this table. The report is served to the user once it's available.

Why did Zerodha's system end up with millions of PostgreSQL tables?

Each report request results in a new PostgreSQL table being created to store the query results. As Zerodha's user base and report demand grew, this approach led to millions of tables, with the system handling an average of 7 million new tables daily.

How does PostgreSQL handle the challenge of millions of tables?

Despite having millions of tables, PostgreSQL handles the challenge efficiently. Operations like database restarts can be completed in a few seconds, thanks to the system's resilience and efficient handling of metadata.

What is the main advantage of using PostgreSQL in Zerodha's reporting system?

The main advantage is PostgreSQL's ability to handle a large number of tables and its fast recovery times. This allows Zerodha to manage a vast amount of cached data efficiently, ensuring that reports are served quickly and without overloading the backend.