Citus cluster support (Postgres sharding)

[vitabaks]

With Citus you get distributed Postgres features like sharding, distributed tables, reference tables, a distributed query engine, columnar storage, and the ability to query from any node.

What is Citus?

Citus is a PostgreSQL extension that transforms Postgres into a distributed database—so you can achieve high performance at any scale.

With Citus, you extend your PostgreSQL database with new superpowers:

• Distributed tables are sharded across a cluster of PostgreSQL nodes to combine their CPU, memory, storage and I/O capacity.
• References tables are replicated to all nodes for joins and foreign keys from distributed tables and maximum read performance.
• Distributed query engine routes and parallelizes SELECT, DML, and other operations on distributed tables across the cluster.
• Columnar storage compresses data, speeds up scans, and supports fast projections, both on regular and distributed tables.
• Query from any node enables you to utilize the full capacity of your cluster for distributed queries

You can use these Citus superpowers to make your Postgres database scale-out ready on a single Citus node. Or you can build a large cluster capable of handling high transaction throughputs, especially in multi-tenant apps, run fast analytical queries, and process large amounts of time series or IoT data for real-time analytics. When your data size and volume grow, you can easily add more worker nodes to the cluster and rebalance the shards.

Details:

• https://www.citusdata.com/product#citus-diagram
• https://docs.citusdata.com/en/stable/get_started/what_is_citus.html
• How Citus Distributes PostgreSQL - https://youtu.be/JwjjUT8K7po
• DEMO - High performance HTAP with Postgres & Hyperscale (Citus)
• More Demos of how Citus speeds up Postgres

TODO: Deploy Multi-Node High Availability Citus clusters with Patroni using Ansible.

+ Citus cluster: demo ----------+--------------+---------+----+-----------+
| Group | Member  | Host        | Role         | State   | TL | Lag in MB |
+-------+---------+-------------+--------------+---------+----+-----------+
|     0 | coord1  | 172.27.0.10 | Replica      | running |  1 |         0 |
|     0 | coord2  | 172.27.0.6  | Sync Standby | running |  1 |         0 |
|     0 | coord3  | 172.27.0.4  | Leader       | running |  1 |           |
|     1 | work1-1 | 172.27.0.8  | Sync Standby | running |  1 |         0 |
|     1 | work1-2 | 172.27.0.2  | Leader       | running |  1 |           |
|     2 | work2-1 | 172.27.0.5  | Sync Standby | running |  1 |         0 |
|     2 | work2-2 | 172.27.0.7  | Leader       | running |  1 |           |
+-------+---------+-------------+--------------+---------+----+-----------+

• https://patroni.readthedocs.io/en/latest/citus.html

---

Consider sponsoring the project via GitHub or Patreon

[vitabaks]

https://www.citusdata.com/blog/2023/03/06/patroni-3-0-and-citus-scalable-ha-postgres