Robert Bernier: How To Scale a Single-Host PostgreSQL Database With Citus

When it comes to Citus, successfully building out and scaling a PostgreSQL cluster across multiple nodes and even across data centers can feel, at times, to be an art form because there are so many ways of building it out. There’s an axiom that I think aptly applies to this situation describing the differences between science and art: – Science: 1 problem -> 1 solution – Art: 1 problem -> 1,000 solutions While the two previous Citus blogs that I wrote covered the fairly straightforward concepts of creating columnar tables and leveraging data redundancy, this blog explores the architectural design considerations using this freely downloadable and fully featured PostgreSQL extension. Rather than listing the concepts, function calls, etc, available in Citus, which frankly is a bit boring, I’m going to explore scaling out a database system starting with a single host. I won’t cover all the features but show just enough that you’ll want to see more of what you can learn to accomplish for yourself. About the cluster Following a step-by-step process, the objective is to create a four-node cluster consisting of: PostgreSQL version 15 Citus extension (I’ll be using version 11, but there are newer ones available.) PostgreSQL Cluster One coordinator node citus-coord-01 Three worker nodes citus1 citus2 citus3 Hardware AWS Instance Ubuntu Server 20.04, SSD volume type 64-bit (x86) c5.xlarge 4vCPU 8GB-RAM Storage: EBS volume (root) 80GB gp2 (IOPS 240/3000) As well, high availability will be integrated, guaranteeing cluster viability in the case that one worker node goes down. Leveraging pgbench, which is a benchmarking utility that comes bundled with PostgreSQL, I will put the cluster through its paces by executing a series of DML operations. Using its default tpc-b benchmark, one c[...]