Data Redundancy With the PostgreSQL Citus Extension

Over the years, I’ve had the opportunity to architect all sorts of configurations using Postgres as a backend. I’ve always found it very cool and satisfying to implement sophisticated business rules, often in more ways than one has fingers and toes. So, it’s not an understatement when I say that Citus is one of the more interesting technologies that I’ve come across when scaling PostgreSQL.

Citus is an extension that horizontally scales PostgreSQL across multiple machines by sharding and creating redundant copies of tables. Its query engine not only parallelizes incoming SQL queries for time series but also has the ability to create column-wise tables making it ideal for OLAP analysis duties.

Among its many capabilities, a Citus cluster can:

• Create distributed tables that are sharded across a cluster of PostgreSQL nodes to combine their CPU, memory, storage, and I/O capacity.
• Reference tables can be replicated to all nodes for joins and foreign keys from distributed tables and for maximum read performance.
• The distributed query engine can route and parallelize SELECT, DML, and other operations on distributed tables across the cluster.
• Columnar storage of tables can compress data, speeding up scans and supporting fast projections, both on regular and distributed tables.

Data redundancy, a database version of a RAID

Pondering the case of high availability and redundancy, one replicates data by creating a replica via streaming replication.

Now let’s stretch our imagination and consider a second method of high availability, ala Citus.

The best way to describe the Citus way of doing things is to reflect how data is managed by a disk RAID array. Depending on the configuration, one can tune a hardware RAID for either performance or redundancy. The same can be said for Citus data sharding.

Here is an example of a table named replica2x, which has 2X redundancy across a cluster of four (4) nodes. The colors indicate duplicated shards of the table. For example, if node citus1 goes offline, the sharded table it holds still has copies on nodes citus2 and citus4. Likewise, it can be said that if node citus2 goes offline, the same data is still available on nodes 1, 3, and 4.

About this POC

I’ll be upfront: I love working with Linux Containers, LXD. Much of what I will show you makes heavy use of them. You won’t need LXD to replicate this POC, of course, but I can’t say enough how flexible and versatile such an environment can be when prototyping a cluster of Postgres nodes, let alone an entire multi-data center infrastructure on a single workstation.

There are two parts to this POC;

• Part A: Setup
• Part B: Redundancy demonstration

Part A: POC setup

Step one: Getting and installing Citus

Referring to this earlier blog, you’ll see how to get and install Citus into your environment.

Step two: Creating the Citus nodes

The Citus cluster consists of a five (5) node cluster:

• citus-coord-01: coordinator
• citus1: worker
• citus2: worker
• citus3: worker
• citus4: worker

By using LXD, I created a single templated container with Citus on Ubuntu 20.04, where the various nodes were copied from this template.

And here’s the resultant cluster:

It’s understood that on each of the five nodes:

1. Database db01 has already been created.
2. The postgresql.conf configuration file has been appropriately edited for remote access.
3. The .pgpass file has been configured to supply the superuser password for all nodes.
4. Extension citus has been created in database db01.

Step three: Check packages

Inspecting the nodes confirms Citus has been correctly installed:

Properly installed, the Citus runtime variables are now available:

Step 4: Define/configure the cluster

Log into the coordinator in order to declare and configure the cluster:

And here’s the cluster’s organization:

Step four: Create, distribute, and populate a single table

Table myevents is created, and the newly inserted records are evenly distributed across the cluster of nodes.

Login to the coordinator and execute the following commands. Notice that all DML and SQL statements are executed on the coordinator node:

Querying the coordinator:

Step five: A review of table distributions across the cluster

This demonstrates clearly that table myevents is hash-sharded across every node member:

• Notice how well-balanced the sharded tables are in size.
• Notice the table name numbering order; see how each named shard that is incremented by one is found on the next node.

Part B: Redundancy demonstration

Method

• Step 1: Update shard replication factor from 1X to 2X
• Step 2: Create table myevents2x with 2X redundancy
• Step 3: Identify shard myevents2x_102040 across citus1 and citus2
• Step 4: Identify some records to query from shards known to be on nodes citus1 and citus2
• Step 5: Test

  • Shutdown citus1; perform the aforementioned identified query
  • Startup citus1, shutdown citus2; perform afore identified query
  • Restart citus2; perform the aforementioned identified query

ATTENTION: Please note that you may have to edit your own queries as the values may be different for your setup.

Step one:

Update shard replication factor from 1X to 2X:

Step two:

Table myevents2x is created and populated with a redundancy of 2X across the cluster:

Here’s the output:

Step three:

Locate shard myevents2x_102040, which should be on nodes citus1 and citus2:

Here’s the output:

Step four:

Locate and return the first three records of shard myevents2x_102040 on nodes citus1 and citus2:

Here’s the output:

Step five:

The next few steps demonstrate our ability to continuously query and return those records found in shard myevents2x_102040.

Step 5a: Test, shutdown node citus1

Here’s the output:

Step 5b: Test, restart citus1, and shutdown citus2

Here’s the output; note that it’s exactly the same as the previous test:

Step 5c: Test, restart node citus2

Here’s the output, of course!

Conclusion

Data redundancy is the hallmark of high availability. But with Citus, we’ve raised the bar. Can you think of a better system that can stay up without losing time initiating failovers when a node fails?

Have fun!

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!