Zerodha's Hack on Postgres

Recently, I was reading about Zerodha's scaling architecture and design and I came across a few of their videos explaining how they found in tricks in upscaling tables on Postgres.

Scaling PostgreSQL Tables

Zerodha addresses the challenges of handling large volumes of data by utilizing table partitioning. Partitioning splits large tables into smaller, more manageable chunks based on specific criteria (e.g., date ranges, user IDs). This significantly improves query performance, as PostgreSQL only needs to scan relevant partitions instead of entire tables.

To scale horizontally, Zerodha implements read replicas and sharded clusters. The platform can offload read-heavy queries from the primary node by deploying multiple read replicas, improving both availability and query performance. Write operations are directed to a single primary database, while read operations are distributed across replicas.

In addition, Zerodha utilizes connection pooling to efficiently manage database connections, reducing overhead caused by opening and closing individual database connections and mitigating the impact of high concurrency.

Transaction Handling

PostgreSQL's ACID compliance (Atomicity, Consistency, Isolation, Durability) is crucial in a trading environment where data integrity is paramount. Zerodha ensures the consistency of transactions by leveraging transaction isolation levels, particularly Serializable isolation, which prevents phenomena like dirty reads, non-repeatable reads, and phantom reads. This isolation level is fundamental when handling high-frequency financial transactions, ensuring that each trade is processed with the utmost accuracy.

To further optimize transaction handling, Zerodha uses optimistic locking to handle high-concurrency situations without introducing significant performance bottlenecks. This technique involves checking whether a transaction conflict exists before committing, allowing multiple transactions to proceed concurrently while minimizing lock contention.

Advanced Indexing and Query Optimization

PostgreSQL's advanced indexing techniques, such as B-tree indexes, GIN (Generalized Inverted Indexes) for full-text search, and BRIN (Block Range INdexes) for large, time-series datasets, are critical to speed up read operations. Zerodha fine-tunes its database schema by creating composite indexes, which allow queries that filter on multiple columns to be executed more efficiently.

Moreover, Zerodha employs database-level caching and regularly analyzes slow queries using PostgreSQL’s EXPLAIN ANALYZE to identify performance bottlenecks and optimize query execution plans.

Conclusion

Zerodha’s use of PostgreSQL goes beyond basic implementations. The platform handles substantial growth by leveraging partitioning, sharding, read replicas, and connection pooling while maintaining high performance. PostgreSQL’s robust transaction management features, including support for ACID compliance and sophisticated isolation mechanisms, ensure the integrity and consistency of financial transactions, which are critical in a high-volume trading environment.

Links - https://www.youtube.com/watch?v=4TE1xErXwGc&pp=ygUQemVyb2RoYSBwb3N0Z3Jlcw%3D%3D