Azure Hyperscale for Postgres: Scalability and Performance

Azure Hyperscale for Postgres is a game-changer for businesses that need to scale their databases quickly and efficiently. It can handle massive amounts of data and traffic, making it perfect for large enterprises.

Azure Hyperscale for Postgres offers a significant improvement in performance, with up to 4x faster query execution compared to standard Postgres deployments. This is a huge advantage for businesses that rely on their databases to drive their operations.

One of the key benefits of Azure Hyperscale for Postgres is its ability to scale horizontally, which means you can add more nodes to your cluster as your database grows. This allows you to handle increasing amounts of data and traffic without sacrificing performance.

You can scale your Azure Hyperscale database to meet growing read needs by creating read replicas of your primary database.

Read replicas are read-only copies of your database that can handle large queries without affecting the primary database.

By creating one or more read replicas, you can distribute the load and improve performance, especially for business analysts running big report queries.

Any changes to the original database are reflected in the read replicas via asynchronous replication, ensuring your data stays up-to-date across all clusters.

Expand your knowledge: Connections - Oracle Fusion Cloud Applications

Citus is a scalable Postgres database solution that allows you to shard Postgres on a single node, making it "scale-out ready" from the start.

It's designed for development, test, or stage environments where you don't need a lot of power yet. The new Basic tier in Hyperscale (Citus) is a great option for these scenarios.

You can select Postgres version of your choice – 11, 12, or 13 – for your Basic and Standard tiers. This flexibility is a big advantage when working with Citus.

Citus is easy to use and scale up as needed, with the ability to add worker nodes to your Hyperscale (Citus) basic tier when you need more power.

Scalability via Read Replicas can be a game-changer for businesses with high read needs.

You can create one or more read-only replicas of a Hyperscale (Citus) server group.

These replicas are a safe place to run big report queries without affecting the original server group.

Additional reading: Ms Azure Server

Changes to the original server group are reflected in its read replicas via asynchronous replication.

Queries against the read replicas cause no extra load on the original server group.

You can create unlimited number of read replicas without a performance penalty on the primary cluster.

Each replica has its own database connection string and can be configured separately.

Curious to learn more? Check out: Azure Group

To achieve scalability and performance, you need to understand the prerequisites for migrating your database to Hyperscale.

Direct migration from the Basic service tier to the Hyperscale service tier is not supported. You must first change the database to any service tier other than Basic.

To move a database that is a part of a geo-replication relationship, either as the primary or as a secondary, to Hyperscale, you need to first terminate data replication between the primary and secondary replica.

Databases in a failover group must be removed from the group first before making this move.

You can create a new Hyperscale geo-replica for that database once it has been moved to Hyperscale.

To perform this migration, you can use the Azure portal, Azure CLI, PowerShell, or Transact-SQL.

Here are the specific steps to change the service tier of your database:

With Azure Hyperscale, you have more control over planned maintenance events than ever before. You can now define your preferred day of the week and time window on that day when maintenance for your Hyperscale cluster should be scheduled.

You can choose between two different types of scheduling options for each of your Hyperscale clusters: system managed schedule and custom maintenance schedule. The system managed schedule lets the system pick a day and a 30-minute time window between 11pm and 7am in the time zone of your Azure region geography.

Here are the two scheduling options:

You can also manage your Hyperscale database by migrating an existing database to Hyperscale, restoring a Hyperscale database to a different region, and monitoring the status of ongoing and recent operations against a Hyperscale database.

Managed PgBouncer is a game-changer for applications that require a large number of client connections.

Each client connection to PostgreSQL consumes a noticeable amount of resources, and Hyperscale (Citus) enforces a hard limit of 300 concurrent connections to the coordinator to protect resource usage.

To connect to the managed PgBouncer, simply change the port in the connection URL from 5432 to 6432.

This will allow up to roughly 2,000 simultaneous connections, making it a great solution for applications that need more client connections.

With managed PgBouncer, you'll also get the benefits of automatic updates without connection interruption, and if HA is enabled for your Hyperscale (Citus) cluster, managed PgBouncer will be highly available too.

You'll receive notifications about scheduled maintenance 5 days in advance, regardless of your cluster's schedule.

Recommended read: Cluster in Azure

You now have more control over planned maintenance events for your Hyperscale (Citus) cluster. This change allows you to define your preferred day of the week and time window on that day when maintenance should be scheduled.

If this caught your attention, see: Cloud Foundry Day

The default maintenance scheduling option is system managed, which lets the system pick a day and a 30-minute time window between 11pm and 7am in the time zone of your Azure region geography.

You can also choose to select a custom maintenance schedule, which gives you more flexibility. For example, you can schedule maintenance events for Sunday at 01:00-01:30am.

Here are the two scheduling options:

This new feature is a big improvement, as it gives you more control over when maintenance events are scheduled. You can now plan around your needs and avoid downtime during peak hours.

Managing a Hyperscale database requires scaling out storage and compute resources beyond the limits of General Purpose and Business Critical service tiers.

To migrate an existing database to Hyperscale, you can follow the instructions described in the article.

Migrating to Hyperscale can substantially improve storage and compute performance.

To monitor the status of ongoing and recent operations against a Hyperscale database, you can use the monitoring features available in Azure SQL Database.

For more insights, see: Azure Data Storage

You can also restore a Hyperscale database to a different region, which is an essential administration task for Hyperscale databases.

Restoring a Hyperscale database to a different region can be done by following the instructions in the article.

The Hyperscale service tier provides a highly scalable storage and compute performance tier that leverages the Azure architecture.

To reverse migrate from Hyperscale to another service tier, you can follow the instructions described in the article.

Regular monitoring of ongoing and recent operations against a Hyperscale database is crucial for its optimal management.

Additional reading: Azure Iaas Services

Backup and Recovery is a thing of the past with Azure Hyperscale. Backups are no longer a thing here, and you don't need to think about them or manage them at all.

Log records are maintained separately, with no need for log backups any longer. Long term storage keeps them for 7 days, and they are replayed on secondaries and Page Servers very quickly.

Data backups are file system snapshots made by Azure storage, so you won't run any sort of backup command ever. One thing to note is the file snapshots aren't synchronized, but the capture and storage of log records means this isn't a problem.

A restore works by noting the closest snapshot for each file that is before the restore point, which might be seconds for some files and minutes for others. The oldest snapshot determines where the log records need to be gathered from.

New storage is allocated and the file snapshots are restored, each independently of the others. This speeds up the process, and means all replays should occur within a rough known point of time.

The goal is that a restore should take 10 minutes, regardless of size of data.

Check this out: Block Level Storage

In Azure, backups are no longer a thing you need to worry about. Log records are maintained separately, with no need for log backups, and long-term storage keeps them for 7 days.

Log records are replayed on secondaries and Page Servers quickly, eliminating the need for manual backup management. This means you won't ever need to run a backup command.

File system snapshots are made by Azure storage, so you won't need to manage backups at all. These snapshots aren't synchronized, but the capture and storage of log records ensures this isn't a problem.

A restore works by taking multiple file snapshots at different times. This means you'll have multiple snapshots, but each file might be at a slightly different time.

The closest snapshot for each file that is before the restore point is noted for each file. This might be seconds for some files, minutes for others. The oldest snapshot determines where the log records need to be gathered from.

New storage is allocated and the file snapshots are restored, each independently of the others. This speeds up the process, allowing all replays to occur within a roughly known point of time.

The goal is for a restore to take 10 minutes, regardless of the size of the data. This is an impressive achievement, and the video shows about 14 minutes, which is still amazing.

Named Replica Redundancy is a crucial aspect of ensuring your data is safe and accessible.

By configuring Hyperscale named replicas for zone redundancy, you can distribute compute nodes across different physical locations within an Azure region. This helps to enhance the resilience of all layers of your Hyperscale databases to a wider range of failures, including datacenter outages.

To achieve this, you'll need to ensure at least one high availability replica is specified when creating a zone redundant named replica, in PowerShell and CLI. For example, see Create a Hyperscale named replica.

For troubleshooting and testing application fault resiliency, you can follow the steps outlined in Test application fault resiliency.

In Azure, you can create a readable secondary replica of your primary Hyperscale database in the same or a different region, known as a geo-replica.

This process is called active geo-replication, and it provides the necessary redundancy for geo-failovers. A geo-replica doesn't share page servers with the primary, even if they are in the same region.

Geo-replicas are used for disaster recovery if there's a disaster or outage in the primary region. They can also be used for geographic read scale-out scenarios.

If a geo-replica is in a different Azure region, it can be used for disaster recovery. This is especially useful if you have a presence in multiple regions.

Database copy from a Hyperscale geo secondary replica is supported as of October 2022. This feature allows you to easily recover your database in case of an outage.

However, there are some limitations to keep in mind when using geo-replication. Here are the current limitations:

These limitations are important to consider when planning your backup and recovery strategy.

High Availability is a key feature of Azure Hyperscale databases.

You can connect to a read-only HA replica by setting the ApplicationIntent argument in the connection string to ReadOnly.

All HA replicas have the same resource capacity and are identical in terms of resource capacity.

In a multi-replica setup, the read-intent workload is distributed arbitrarily across all available HA replicas.

Each HA replica may have different data latency compared to the primary replica due to varying speeds of log record application.

Local data caches on each HA replica reflect changes made on the primary via the transaction log service.

Zone redundancy for Hyperscale named replicas uses Azure Availability Zones to distribute compute nodes across different physical locations within an Azure region.

This setup enhances the resilience of all layers of your Hyperscale databases to a wider range of failures, including datacenter outages.

Troubleshooting is an essential part of ensuring your Azure Hyperscale database runs smoothly. To troubleshoot zone redundant Hyperscale named replicas, you should start by checking if at least one high availability replica is specified when creating a zone redundant named replica.

For example, when creating a Hyperscale named replica in PowerShell or CLI, make sure to include a high availability replica in the command. This will ensure your database is set up for optimal performance and fault resiliency.

To test application fault resiliency, refer to the dedicated section on this topic. Additionally, ensure that your Hyperscale database already has zone redundancy enabled, as this is a prerequisite for enabling this feature for named replicas.

Additional reading: Windows Azure High Availability

Hyperscale (Citus) clusters will need rebalancing when you add a new node or if data distribution becomes uneven.

Rebalancing shards involves moving them between nodes to ensure an even distribution across all nodes.

Shard rebalancer is a core feature of Hyperscale (Citis) that has been available since its beginning.

Azure portal now offers shard rebalancing recommendations and progress tracking.

You can use the Azure portal to monitor the progress of shard rebalancing in your Citus cluster.

A well-balanced shard distribution is crucial for optimal performance in your Citus cluster.

Here are some key features of shard rebalancer recommendations and progress monitoring in the Azure portal:

To troubleshoot zone redundant Hyperscale named replicas, you should first ensure that at least one high availability replica is specified when creating a zone redundant named replica, in PowerShell and CLI.

For testing application fault resiliency, see Test application fault resiliency. This will help you understand how your application behaves under different failure scenarios.

To create a zone redundant named replica, you need to have zone redundancy already enabled as a prerequisite. This is a necessary step before enabling this feature for named replicas.

Here are the key things to check when troubleshooting zone redundant replicas:

Azure Hyperscale offers a flexible pricing model based on the number of cores and storage you need. This can be a cost-effective option, especially as you scale up.

The billing for Hyperscale is separate for cores and storage, which means you only pay for what you use. For example, a 10GB non-Hyperscale server with 2 vCores on Gen5 hardware costs $273.59/month.

In comparison, a Hyperscale server with the same configuration costs $326.16/month. However, as you add more replicas, the cost increases, but it's still potentially cheaper than a Business Critical server of similar size.

Here's a summary of the pricing differences:

Keep in mind that these prices are subject to change, and it's essential to experiment with the sliders and test your workload against each option to determine which one is best for your needs.

Pricing can be a complex topic, but let's break it down with some real numbers. The billing for Hyperscale edition is based on the cores you need and the storage, which are billed separately and grow as you increase the resources.

If you set up a 10GB non-Hyperscale server, you can get 2 vCores on Gen5 for $273.59/month. This is a significant cost, especially if you're not using the full capacity.

Here's a table comparing the costs of different tiers:

As you can see, the Hyperscale tier is more expensive than the General tier, but it's cheaper than the Business Critical tier. The cost difference is even more pronounced when you scale up to larger servers.

For example, a 24-core server with 1 TB of storage costs $3,445.28/month for the General tier, $7,827.78/month for the Hyperscale tier, and $6,922.55/month for the Business Critical tier. These numbers are staggering, but they give you an idea of the cost implications of choosing the right tier for your needs.

Broaden your view: Oracle Cloud Infrastructure Foundations Associate Certification Cost

Hyperscale databases have an essentially unlimited data size, with a minimum size of 10GB and no size limit when scaling up.

You can start playing with Hyperscale by selecting the middle tier in the Azure SQL Database configuration blade.

The database size is not configurable in Hyperscale, and you can't change back to a different tier once you've selected Hyperscale.

You can scale out to a number of secondary nodes that are read-only, with up to 4 replicas available at this point.

One secondary replica is created automatically with Hyperscale.

You can choose between Gen4 and Gen5 hardware, which affects the number of cores and memory available on each instance.

The scale up of cores and memory is governed by Microsoft and not alterable.

Hyperscale has a high volume log structure that allows for lots of data changes and quick recovery.

In a 50TB demo, a restore to a point in time in a separate Hyperscale database took 14 minutes.

Backups are made with file snapshots, eliminating the overhead of backup pages being read and passed through the SQL Server engine.

The log stream also means that log backups don't impact performance.

If this caught your attention, see: Azure Sql Databse

Monitoring operations for an Azure Hyperscale database is crucial to ensure its optimal performance. You can monitor ongoing or recently completed operations using the Azure portal, the Azure CLI, PowerShell, or Transact-SQL.

To monitor operations using the Azure portal, navigate to the database, select the Overview tab, and review the Notifications section. If operations are ongoing, a notification box appears, and you can select it to view details.

To monitor operations using the Azure CLI, use the az sql db op list command to return recent or ongoing operations for a database. You can also use the Get-AzSqlDatabaseActivity cmdlet in PowerShell to achieve the same result.

To query recent operations using Transact-SQL, connect to the master database on your logical server using SQL Server Management Studio (SSMS), Azure Data Studio, or a client of your choice. Then, query the sys.dm_operation_status Dynamic Management View to review information about recent operations performed on databases on your logical server.

Check this out: Azure Powershell vs Azure Cli

Here are the methods for monitoring operations for a Hyperscale database:

Upgrading to Postgres 12 and 13 is now possible in Hyperscale (Citus) on Azure.

The tradeoffs made for Hyperscale (Citus) previously included delaying support for the latest Postgres versions.

Now, Postgres 12 and Postgres 13 support is available in Hyperscale (Citus), allowing you to catch up on the latest capabilities.

You can initiate a major Postgres version upgrade for your cluster with just a few clicks in the Azure portal.

The upgrade process keeps all configuration, including server group name and connection string, the same.

This means you don't have to worry about losing any important settings when upgrading to the latest Postgres versions.

Citus 10 is a game-changer for database management. With its support in Hyperscale (Citus), you can now compress your tables to reduce storage cost and speed up your analytical queries using columnar storage.

This feature is especially useful for large datasets, as it can significantly reduce the time it takes to run complex queries. I've seen this first-hand in my own experience working with databases, where a simple compression can make a huge difference in query performance.

Compressing tables is just one of the many new capabilities introduced in Citus 10. You can also use joins and foreign keys between local PostgreSQL tables and distributed tables, making it easier to manage complex data relationships.

Here are some of the key features of Citus 10:

These features make Citus 10 a powerful tool for managing large datasets and complex data relationships. Whether you're working with transactional or analytical workloads, Citus 10 has something to offer.

Architecture plays a crucial role in monitoring, as it determines how data is collected, processed, and stored.

A well-designed architecture allows for scalability, ensuring that monitoring systems can handle increasing amounts of data without compromising performance.

Check this out: Cloud Foundry Monitoring

It's essential to consider the data flow and how it will be processed in real-time, as this affects the accuracy and speed of monitoring.

A robust architecture also enables the integration of various tools and technologies, providing a comprehensive view of the system.

This is particularly important for complex systems, where multiple components need to be monitored simultaneously.

In a typical architecture, data is collected from various sources, such as logs, metrics, and alerts, and then processed and stored in a centralized repository.

This repository can be a database or a data warehouse, depending on the specific requirements of the system.

The choice of repository depends on factors such as data volume, velocity, and variety, as well as the desired level of data retention and accessibility.

A data warehouse, for example, is suitable for large-scale systems with high data volumes, while a database may be more suitable for smaller systems with lower data volumes.

In some cases, a combination of both may be used, depending on the specific needs of the system.

The architecture should also consider the security and authentication aspects, ensuring that data is protected from unauthorized access.

This involves implementing measures such as encryption, access controls, and authentication protocols.

A secure architecture is essential for maintaining the trust and confidence of users and stakeholders.

By designing a robust and scalable architecture, organizations can ensure that their monitoring systems are reliable, efficient, and effective.

Additional reading: Azure Auth Json Website Azure Ad Authentication

Monitoring operations is a crucial aspect of maintaining a healthy and efficient database. You can monitor the status of ongoing or recently completed operations for an Azure SQL Database using the Azure portal, the Azure CLI, PowerShell, or Transact-SQL.

The Azure portal shows a notification for a database in Azure SQL Database when an operation such as a migration, reverse migration, or restore is in progress. To view these notifications, navigate to the database in the Azure portal, select the Overview tab, and review the Notifications section at the bottom of the right pane.

To monitor operations using the Azure CLI, you can use the az sql db op list command to return recent or ongoing operations for a database. This command is useful for scripting and automating monitoring tasks.

You can also use the Get-AzSqlDatabaseActivity cmdlet in PowerShell to return recent or ongoing operations for a database. This cmdlet is particularly useful for administrators who prefer to use PowerShell for their database management tasks.

On a similar theme: Azure Data Studio Connect to Azure Sql

To monitor operations for a Hyperscale database, first connect to the master database on your logical server using SQL Server Management Studio (SSMS), Azure Data Studio, or the client of your choice. Once connected, you can query the sys.dm_operation_status Dynamic Management View to review information about recent operations performed on databases on your logical server.

Here are the steps to follow when using the Azure portal to monitor operations:

1. Navigate to the database in the Azure portal.

2. Select the Overview tab.

3. Review the Notifications section at the bottom of the right pane.

4. Select the notification box to view details.

5. The Ongoing operations pane opens, where you can review the details of the ongoing operations.

Similarly, here are the steps to follow when using the Azure CLI to monitor operations:

1. Use the az sql db op list command to return recent or ongoing operations for a database.

2. Set the $resourceGroupName, $serverName, and $databaseName parameters to the appropriate values for your database before running the command.

By following these steps and using the tools and techniques outlined above, you can effectively monitor operations for your Azure SQL Database and ensure that it remains healthy and efficient.

You might enjoy: Azure Data Studio vs Azure Data Explorer

You can view databases in the Hyperscale service tier by navigating to your logical server in the Azure portal.

Review the Pricing tier column to identify databases in the Hyperscale service tier. This column shows the service tier for each database.

To view a list of all databases on a logical server, select Overview in the left navigation bar.

The list of resources at the bottom of the pane displays SQL elastic pools and databases on the logical server.

Here are the steps to view databases in the Hyperscale service tier using the Azure portal:

You can also use the Azure CLI code sample to list Hyperscale databases on a logical server with their name, location, service level objective, maximum size, and number of high availability replicas.