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

Zeroda uses a system called "Dung Beetle," a lightweight Go application, to handle millions of daily report requests from a massive database. Instead of directly querying the slow, large database, requests are queued, processed asynchronously, and results are written to individual tables in a separate, ephemeral PostgreSQL database. This caching strategy, involving millions of tables, allows for near-instantaneous report delivery to users, even during peak demand, without overwhelming the main database. The system is highly scalable and easily manages diverse report types and data sources. This segment explains the shift from synchronous to asynchronous report generation. The speaker details the limitations of synchronous processing, where each report request directly hits the database, leading to bottlenecks and slow response times. The solution proposed is asynchronous queuing: requests are placed in a queue, processed sequentially by the database based on its capacity, and results are returned asynchronously to the user. The speaker correctly points out that while this is an established technique, the novel approach lies in how they handle the caching of results. This segment introduces the core problem: the slow response times of massive databases (hundreds of billions of rows) when serving millions of daily report requests. It highlights the contrast between the need for real-time access to recent data and the inherent limitations of querying vast historical datasets. The speaker emphasizes the common industry practice of separating hot and cold data, leading to complex report generation processes and multiple UIs for accessing different data periods. This segment focuses on the unique approach of creating a new table in the results database for every report generated. The speaker explains the rationale behind this seemingly inefficient method: the small size of individual report results allows for near-instantaneous retrieval, even with millions of tables. The segment also showcases the impressive performance of PostgreSQL in handling this extreme number of tables, highlighting its robustness. This segment explains why a simple asynchronous queuing system isn't sufficient in a complex environment like Zeroda's. The speaker highlights the challenges of integrating asynchronous queuing logic into multiple applications written in different languages and using diverse database systems. This motivates the introduction of an independent middleware system to handle report generation, traffic control, and response delivery, decoupling the application and database layers to enable independent scaling.This segment introduces the "Dung Beetle" system, a middleware solution designed to manage asynchronous report generation. It describes the system's architecture: accepting report requests, queuing them, querying the database at a manageable pace, storing results in a separate database, and delivering results back to the application. The key feature is the use of a separate, ephemeral "results DB" to cache report results, ensuring fast retrieval for users. Zeroda uses a "hack" involving a single PostgreSQL instance to handle more than 7 million tables as a caching strategy for serving reports from a massive database. The system addresses the challenge of serving millions of daily report requests from a trillion-row database, where synchronous query processing is unfeasible due to performance limitations. Asynchronous queuing is employed to manage incoming report requests, preventing the overloading of the main database. A middleware system called "Dung Beetle" manages the asynchronous processing, queuing requests, and directing queries to the database based on its capacity. Results are written to a separate, ephemeral PostgreSQL database (the "results DB") as individual tables, one per report request. This allows for near-instantaneous retrieval of results by the application. The results DB is a "throwaway" database, reset nightly by detaching and replacing the disk to efficiently manage the millions of tables. The system is highly scalable and adaptable, handling various report complexities and database types. It's implemented in a lightweight Go application. The approach demonstrates the robustness and scalability of PostgreSQL, even when used in unconventional ways.