Citus & Patroni: The Key to Scalable and Fault-Tolerant PostgreSQL

Citus is an open source extension to PostgreSQL that enables you to scale out your database horizontally by sharding your data across many nodes, and Patroni is an open source tool for managing and automating PostgreSQL High Availability. Combined together three open source projects become a superhero – a scalable PostgreSQL cluster with self-healing capabilities.

In my presentation I will cover implementation details of Patroni & Citus integration, and do a live-demo of cluster deployment and showcase maintenance on Citus worker nodes without interrupting client connections.

Transcript

2. Agenda for this Citus & Patroni talk @ Citus Con: An Event for Postgres 2023 ▪ What (business)problems do we solve? ▪ High Availability with Patroni ▪ Horizontal scalability with Citus ▪ PostgreSQL + Citus + Patroni for the win ▪ Live demo! ▪ Conclusion 1 2 3 4 5 6
3. As an application developer I want... • A database 3
4. As an application developer I want... • A database • A highly available database 4
5. As an application developer I want... • A database • A highly available database • A highly available database that scales 5
6. As an application developer I want... • A database • A highly available database • A highly available database that scales • A highly available database that scales horizontally! 6
7. As an application developer I want... • A database • A highly available database • A highly available database that scales • A highly available database that scales horizontally! • Highly available database that scales horizontally for free! 7
8. PostgreSQL: The World's Most Advanced Open Source Relational Database 8
9. High availability with Patroni 9 Primary Standby Etcd cluster
10. Vertical Scalability 10 Primary Standby Etcd cluster
11. Horizontal Scalability (sharding) 11 Primary Standby Primary Standby Primary Standby
12. Application Level Sharding 12 Shard 1 Shard 2 Shard 3 Shard 4 Application SELECT * FROM u WHERE id=4 SELECT * FROM u WHERE id=5 SELECT * FROM u WHERE id=6 SELECT * FROM u WHERE id=7 ID%4 == 0 ID%4 == 1 ID%4 == 2 ID%4 == 3
13. Disadvantages of Application Level Sharding • Must be implemented in the application • Separate connection (pool) for every shard • Must be introduced from day 1 • Depending on implementation adding new shards is either impossible or hard • If new shards are added, they are heavily disbalanced • Rebalancing (without downtime) is a challenge • Schema changes must be deployed on all shards 13
14. Sharding with Citus 14 Worker 1 Worker 2 Worker 3 Worker 4 Application Coordinator
15. Sharding with Citus: Pros & Cons Pros • Transparent sharding • A single connection (pool) • Query from any node! • Schema changes via coordinator • Start with small and scale-out • Rebalancing without downtime! 15 Cons • Increased query latency
16. Citus HA with Patroni 3.0 16 primary standby Citus worker 1 primary standby Citus coordinator primary standby Citus worker 2
17. Enabling Citus support in Patroni 3.0 scope: my-citus-cluster # must be the same on all nodes citus: group: X # 0 for coordinator and 1, 2, 3, etc for workers database: citus # must be the same on all nodes 17
18. Adding new workers to the Citus cluster 1. Adjust patroni.yaml, citus.group parameter 2. Start Patroni on the new node 3. SELECT * FROM citus_rebalance_start(); -- rebalance shards 4. SELECT * FROM citus_rebalance_status(); -- check rebalance progress 18
19. DEMO 19
20. 20 Conclusion
21. Conclusion • Enterprise-grade sharding with Citus • Enterprise-grade HA with Patroni • Everything is open-source! • Start with small and scale • Vertically • Horizontally 21
22. Thank you! • Citus open source repo on GitHub aka.ms/citus • Patroni repo on GitHub github.com/zalando/patroni • Alexander on Twitter @cyberdemn