Azure Database for PostgreSQL Blog

7 MIN READ

Postgres horizontal scaling with elastic clusters on Azure Database for PostgreSQL Flexible server

awolk

Microsoft

Nov 19, 2024

We are excited to announce the launch of elastic clusters on Azure Database for PostgreSQL Flexible server, a feature that enables a database to be scaled out horizontally beyond a single node.

Elastic clusters (Preview) is a Flexible server feature that enables a database to be scaled out horizontally beyond a single node. Powered by the open-source Citus extension developed by Microsoft, elastic clusters offer horizontal scale out through row and schema-based sharding capabilities.

With elastic clusters, you can future proof horizontal scale out, adding nodes as needed as the application grows and outgrows the capacity of a single node. By providing a unified endpoint to the cluster, elastic clusters eliminate the need for complex application-layer sharding, significantly simplifying the management of growing Flexible Server deployments. Online shard management and isolation capabilities are a core part of the services offloading this burden from the application stack.

With elastic clusters, scaling out your applications has never been easier or more efficient.

Benefits of using elastic clusters (Preview)

Familiarity: It’s the same Flexible server you know and love, with the same feature set.
Horizontal scaling: Grow your cluster out by adding more Flexible servers – lifting the single node limitation for your application growth.
Simplicity: Offload complexity of sharding from your application layer to the managed service.
Cost efficiency: You are not paying more than you would for the same amount of Flexible servers as nodes in your elastic cluster with the benefit of simplified management of the fleet.
AI ready: Modern AI applications require storage of vast amounts of vectorized data. Their data models, however, are usually simple and scale out very well with sharding models provided by elastic clusters.

What are elastic clusters?

The elastic clusters feature of Azure Database for PostgreSQL Flexible server is a managed offering of horizontal scaling, powered by the Citus extension. Elastic clusters handle managing and configuring multiple PostgreSQL instances as a single resource, setting up the nodes, and providing cluster level choices for high availability, disaster recovery, compute and storage and resiliency. Nodes in your cluster reside in the same availability zones for optimal performance and the various Flexible server features and capabilities to become cluster aware such as backups, metrics, Entra ID authentication to just name a few.

Every node in the elastic cluster is capable of handling DML, you can run your read and write queries on any node on the cluster, and it will automatically fetch the data from their current placement. In many distributed systems, often all queries go through a single node which can make it a bottleneck. With Elastic Clusters, however, when you connect via port 7432, the workload is automatically distributed across all nodes.

What is sharding?

Sharding is the act of splitting data into logical groups, that can then be moved into separate machines and retrieved individually. Sharding also supports queries that need data from two or more different shard groups living on different nodes. Such queries would be computed individually by each node and then rolled up by the initiating node before providing the result.

You can also read more about sharding models here: Sharding models - elastic clusters

Schema-based sharding

Schema-based sharding is the most intuitive way to understand how this works. Every table in a distributed schema becomes its own shard. Shards can be co-located - moved around between nodes as a group. If your application creates a schema for every tenant in the system (or a microservice) all the data will live together on the same node and even if it moves around, the system will route queries to the correct node.

The benefit is a pure architectural lift-and-shift for any application that already uses schema based sharding. Suddenly your legacy application can scale across many machines without a single line of code being changed. This is assuming that your application already uses schema based sharding but for database per tenant models the conversion to a schema-based model is easy.

Follow the Tutorial: Design for microservices with elastic cluster to try schema-based sharding yourself!

Row-based sharding

Row-based sharding is different and requires some DDL modifications and choice of distribution key but with those few changes, it is a very scalable method when there is a need for high density packing of tenants.

One way to think about row-based sharding is to paint parallels to table partitioning. With partitioning you decide what column can split the data into sensible chunks that can be queried in isolation – greatly reducing the amount of data you need to weed through to get results. A good example is partitioning by year, knowing that you rarely reach to past year. You then need to make sure that your queries all use the partition key in the WHERE clause, because without it the system doesn’t know how to filter out (prune) partitions that don’t hold the required data – neglecting the benefit of the partitioning.

As with partitioning, row-based sharding required determining a distribution key. Selecting a good key can be a challenge itself – something with good cardinality (so data can be spread out), not skewed (so that data doesn’t fully land into one big shard) and logically splitting your tenants (avoiding cross tenant queries). Typical good distribution keys are tenant_id and device_id (to fan out writes) but this will heavily depend on your application. If you go back to the partitioning example, year was a great partition key, but it is a terrible distribution key. It has low cardinality, and only one node per year would be taking writes by not leveraging spreading out data to multiple shards. Instead of distributing on year, use a key with better cardinality in your system – you can still leverage partitioning on top of distribution as partitioned tables can be distributed.

Having selected the key, the next step is easy. Just distribute the tables and this is as simple as calling:

SELECT create_distributed_table('accounts', 'account_id');

SELECT create_distributed_table('campaigns', 'campaign_id');

There are many table types in elastic clusters, you can read more about them here: Table types - elastic clusters

As with schema based sharding, each tenant is assigned into a shard but unlike row-based sharding a shard can be shared among tenants. Like with partitioning you can then think of a shard as a smaller PostgreSQL table and if you put all of them together – you would get the whole thing. In the same vein, as with partitioning if queries ran by the application miss the distribution key in their WHERE clause – they would start hitting all nodes in the cluster to find the required data.

In some cases, such fan-out queries are desired and a form of implementing parallelization of query execution among all cluster nodes. In most cases, however, it is more efficient to have queries that filter down to one node.

The biggest benefit of row-based sharding is the density of how tight tenants can be packed on the same machine. It’s as good as it gets, which is rows in the same table – the drawback, is the work up front required to decide on the schema and potential query changes in the application.

Follow either tutorial: Design multitenant database with elastic cluster or Design a real-time dashboard with elastic cluster, to try out row-based sharding yourself!

When things get hot

There will be times where either the system needs room to grow (CPU, Storage, connections), or a specific tenant becomes very noisy (making other tenants experience degraded performance). Elastic clusters allow you to address both.

In the case of capacity (either CPU, Storage, maximum connections) being reached, just grow your cluster by adding nodes and call trigger online rebalancer. Without blocking your existing workloads, data will be redistributed among the available nodes (including the new node) by disk size, shard count or any strategy that you decide to implement yourself.

This is as easy as connecting to the cluster and issuing:

SELECT citus_rebalance_start();

You may not need to rebalance depending on how you sharded your data. The new node will be immediately used for new inserts as soon as it becomes online and that may be just enough to redistribute the load on your cluster.

When one tenant throws a party, you can decide to move them out to their own node though, sometimes it is easier to move the well behaving tenants if they happen to store less data than the noisy one.

With elastic clusters this can be as easy as running:

SELECT citus_schema_move('wideworldimporters','10.54.0.254', 7003);

Using elastic clusters in Azure Database for PostgreSQL Flexible server

Step 1: Create an elastic cluster

Begin by setting up a new instance of elastic cluster with Azure Database for PostgreSQL Flexible server Azure portal Quickstart: Create elastic cluster

Select your cluster size, up to 10 nodes can be deployed during preview.

Review your settings after confirming the compute and storage configuration.

Step 2: Choosing a sharding model

Citus on Flexible server offers two sharding models: row-based sharding and schema-based sharding. You can learn more about sharding models here: Sharding models - elastic clusters

Step 3: Follow one of the tutorials

For row-based sharding:

Design multitenant database with elastic cluster

Design a real-time dashboard with elastic cluster

For schema-based sharding:

Design for microservices with elastic cluster

Frequently Asked Questions

In what regions are elastic clusters available?

Elastic clusters are currently available in the following regions:

East Asia
East US
North Europe
UK South
West US
West US 3

Please see Limitations of elastic clusters for an up-to date list.

Will all features of Flexible server be available on elastic clusters?

We aim to feature parity, some cluster incompatible extensions like TimescaleDB may not be available. Other features may need a bit of work to become cluster aware.