# Monitoring Aurora PostgreSQL Limitless Database Monitoring Limitless Database You can use Amazon CloudWatch, Enhanced Monitoring, and Performance Insights to monitor Aurora PostgreSQL Limitless Database. There are also new statistics functions and views, and wait events, for Aurora PostgreSQL Limitless Database that you can use for monitoring and diagnostics. **Topics** + [ # Monitoring Aurora PostgreSQL Limitless Database with Amazon CloudWatch ](limitless-monitoring.cw.md) + [ # Monitoring Aurora PostgreSQL Limitless Database with CloudWatch Database Insights ](limitless-monitoring.cwdbi.md) + [ # Monitoring Aurora PostgreSQL Limitless Database with Amazon CloudWatch Logs ](limitless-monitoring.cwl.md) + [ # Monitoring Aurora PostgreSQL Limitless Database with Enhanced Monitoring ](limitless-monitoring.em.md) + [ # Monitoring Aurora PostgreSQL Limitless Database with Performance Insights ](limitless-monitoring.pi.md) + [ # Monitoring Aurora PostgreSQL Limitless Database with Amazon GuardDuty RDS Protection ](limitless-monitoring.gd.md) + [ # Functions and views for Aurora PostgreSQL Limitless Database ](limitless-monitoring-fns-views.md) + [ # Wait events for Aurora PostgreSQL Limitless Database ](limitless-monitoring-waits.md) + [ # Building for efficiency with functions ](limitless-performance-functions.md) # Monitoring Aurora PostgreSQL Limitless Database with Amazon CloudWatch Monitoring Limitless Database with CloudWatch CloudWatch metrics for Aurora PostgreSQL Limitless Database are reported under the following dimensions: + [DBShardGroup](#limitless-monitoring.cw.DBShardGroup) + [DBShardGroupRouterAggregation](#limitless-monitoring.cw.DBShardGroupRouterAggregate) + [DBShardGroupInstance](#limitless-monitoring.cw.DBShardGroupInstance) + [DBClusterIdentifier](#limitless-monitoring.cw.DBClusterIdentifier) For more information on CloudWatch metrics, see [Monitoring Amazon Aurora metrics with Amazon CloudWatch](monitoring-cloudwatch.md). ## DBShardGroup metrics To see `DBShardGroup` metrics for Aurora PostgreSQL Limitless Database in the CloudWatch console, choose **RDS**, and then choose **DBShardGroup**. You can track the following CloudWatch metrics: + `DBShardGroupACUUtilization` – Aurora capacity unit (ACU) usage as a percentage calculated from `DBShardGroupCapacity` divided by `DBShardGroupMaxACU`. + `DBShardGroupCapacity` – Number of ACUs consumed by the writer instances of the DB shard group. + `DBShardGroupComputeRedundancyCapacity` – Number of ACUs consumed by the standby instances of DB shard group. + `DBShardGroupMaxACU` – Maximum number of ACUs configured for the DB shard group. + `DBShardGroupMinACU` – Minimum number of ACUs required by the DB shard group. The `DBShardGroupIdentifier` dimension key is available for aggregating the `DBShardGroup` metrics. ## DBShardGroupRouterAggregation metrics To see `DBShardGroupRouterAggregation` metrics for Aurora PostgreSQL Limitless Database in the CloudWatch console, choose **RDS**, and then choose **DBShardGroupRouterAggregation**. You can track the following CloudWatch metrics: + `CommitThroughput` – The average number of commit operations per second across all of the router nodes in the DB shard group. + `DatabaseConnections` – The sum of all connections across all of the router nodes in the DB shard group. ## DBShardGroupInstance metrics A DBShardGroupInstance is the individual DB instance within each shard or router subcluster. To see `DBShardGroupInstance` metrics for Aurora PostgreSQL Limitless Database in the CloudWatch console, choose **RDS**, and then choose **DBShardGroupInstance**. You can track the following CloudWatch metrics: + `ACUUtilization` – The percentage calculated as the `ServerlessDatabaseCapacity` metric divided by the maximum assigned ACU value of the subcluster. + `AuroraReplicaLag` - For compute redundancy enabled Limitless clusters, this is the amount of lag when replicating updates from the primary instance in the subcluster. + `AuroraReplicaLagMaximum` – For compute redundancy enabled Limitless clusters, this is the maximum amount of lag when replicating updates from the primary instance in the subcluster. When read replicas are deleted or renamed, there might be a temporary spike in replication lag as the old resource recycles. Use this metric to find if a failover occurred due to high replication lag on one of the it's readers. + `AuroraReplicaLagMinimum` – For compute redundancy enabled Limitless clusters, this is the minimum amount of lag when replicating updates from the primary instance in the subcluster. + `BufferCacheHitRatio` – The percentage of data and indexes served from an instance’s memory cache (as opposed to the storage volume). + `CommitLatency` – The average duration for the engine and storage to complete the commit operations for a particular node (router or shard). + `CommitThroughput` – The average number of commit operations per second. + `CPUUtilization` – CPU usage as a percentage of the maximum assigned ACU value of the subcluster. + `FreeableMemory` – The amount of unused memory that's available when the shard group is scaled to its maximum capacity. This is determined by the assigned ACUs of the shard group. For every ACU the current capacity is below the maximum capacity, this value increases by approximately 2 GiB. Thus, this metric doesn't approach zero until the DB shard group is scaled up to the maximum limit. + `MaximumUsedTransactionIDs` – The age of the oldest unvacuumed transaction ID, in transactions. If this value reaches 2,146,483,648 (2^31 - 1,000,000), the database is forced into read-only mode, to avoid transaction ID wraparound. For more information, see [Preventing transaction ID wraparound failures](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) in the PostgreSQL documentation. + `NetworkReceiveThroughput` – The amount of network throughput received from clients by each instance in the DB shard group. This throughput doesn't include network traffic between instances in the DB shard group and the cluster volume. + `NetworkThroughput` – The aggregated network throughput (both transmitted and received) between clients and routers, and routers and shards in the DB shard group. This throughput doesn't include network traffic between instances in the DB shard group and the cluster volume. + `NetworkTransmitThroughput` – The amount of network throughput sent to clients by each instance in the DB shard group. This throughput doesn't include network traffic between instances in the DB shard group and the cluster volume. + `ReadIOPS` – The average number of disk read input/output operations per second (IOPS). + `ReadLatency` – The average amount time taken per disk read input/output (I/O) operation. + `ReadThroughput` – The average number of bytes read from disk per second. + `ServerlessDatabaseCapacity` – The current capacity of the DB shard or router subcluster within the DB shard group. + `StorageNetworkReceiveThroughput` – The amount of network throughput received from the Aurora storage subsystem by each instance in the DB shard group. + `StorageNetworkThroughput` – The aggregated network throughput both transmitted to and received from the Aurora storage subsystem by each instance in the DB shard group. + `StorageNetworkTransmitThroughput` – The amount of network throughput sent to the Aurora storage subsystem by each instance in the DB shard group. + `SwapUsage` – The amount of swap space used by the DB shard group. + `TempStorageIOPS` – The average number of I/O operations performed on local storage attached to the DB instance. It includes both read and write I/O operations. `TempStorageIOPS` can be used with `TempStorageThroughput` to diagnose the rare cases where network activity for transfers between your DB instances and local storage devices is responsible for unexpected capacity increases. + `TempStorageThroughput` – The amount of data transferred to and from local storage associated with either a router or a shard. + `WriteIOPS` – The average number of disk write IOPS. + `WriteLatency` – The average amount time taken per disk write I/O operation. + `WriteThroughput` – The average number of bytes written to disk per second. The following dimension keys are available for aggregating the `DBShardGroupInstance` metrics: + `DBClusterIdentifier` – The Aurora PostgreSQL DB cluster. + `DBShardGroupIdentifier` – The DB shard group to which the instance belongs. + `DBShardGroupSubClusterType` – The node type, either `Distributed Transaction Router` (router) or `Data Access Shard` (shard). + `DBShardGroupSubClusterIdentifier` – The name of the router or shard to which the instance belongs. The following are examples of aggregating CloudWatch metrics: + Total `CPUUtilization` of all instances that belong to a particular shard or router in a DB shard group. + Total `CPUUtilization` of all instances in a DB shard group. ## DBClusterIdentifier metrics To see `DBClusterIdentifier` metrics for Aurora PostgreSQL Limitless Database in the CloudWatch console, choose **RDS**, and then choose **DBClusterIdentifier**. When you use Aurora PostgreSQL Limitless Database, you might have more input/output (I/O) operations than you would for an Aurora DB cluster. You can track the following CloudWatch metrics for your Limitless Database cluster: + `VolumeReadIops` – The number of billed read I/O operations from a cluster volume, reported at 5-minute intervals. + `VolumeWriteIops` – The number of write disk I/O operations to the cluster volume, reported at 5-minute intervals. Aurora PostgreSQL Limitless Database uses the Aurora I/O-Optimized cluster storage configuration. With Aurora I/O-Optimized, you pay a single monthly price for all I/O operations, rather than paying for every one million I/O requests. For more information, see [Storage configurations for Amazon Aurora DB clusters](Aurora.Overview.StorageReliability.md#aurora-storage-type). You might also use more storage than you would for an Aurora DB cluster. You can track the following CloudWatch metrics for storage: + `BackupRetentionPeriodStorageUsed` – The total billed continuous backup storage usage of your Aurora PostgreSQL Limitless Database cluster. + `SnapshotStorageUsed` – The total billed snapshot storage usage of your Aurora PostgreSQL Limitless Database cluster. + `TotalBackupStorageBilled` – The sum of your costs for automated backup retention and DB cluster snapshots. For more information on backup storage costs, see [Understanding Amazon Aurora backup storage usage](aurora-storage-backup.md). + `VolumeBytesUsed` – The amount of storage used by your Aurora PostgreSQL Limitless Database cluster, reported at 5-minute intervals. # Monitoring Aurora PostgreSQL Limitless Database with CloudWatch Database Insights Monitoring Limitless Database with Database Insights The Standard mode of Database Insights is required as part of enabling Aurora PostgreSQL Limitless Database. You can use it to monitor the database load (DB Load) of your Limitless Database DB instances in real time. DB Load measures the level of session activity in a database. You can use Database Insights to analyze and troubleshoot the performance of your Aurora PostgreSQL Limitless Database DB instances at scale. For more information about CloudWatch Database Insights, see the following. + [Monitoring Amazon Aurora databases with CloudWatch Database Insights](USER_DatabaseInsights.md) + [CloudWatch Database Insights](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/Database-Insights.html) in the *Amazon CloudWatch User Guide* + [Get started with CloudWatch Database Insights](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/Database-Insights-Get-Started.html) in the *Amazon CloudWatch User Guide* + [Configuring your database to monitor slow SQL queries with Database Insights for Amazon Aurora](USER_DatabaseInsights.SlowSQL.md) For information about turning on the Advanced mode or Standard mode of Database Insights, see the following topics. **Topics** + [ # Turning on the Advanced mode of Database Insights for Aurora PostgreSQL Limitless Database ](limitless-monitoring.cwdbi.advanced.md) + [ # Turning on the Standard mode of Database Insights for Aurora PostgreSQL Limitless Database ](limitless-monitoring.cwdbi.standard.md) # Turning on the Advanced mode of Database Insights for Aurora PostgreSQL Limitless Database Turning on the Advanced mode of Database Insights To turn on the Advanced mode of Database Insights for your Aurora PostgreSQL Limitless Database, use the following procedures. ## Turning on the Advanced mode of Database Insights when creating a DB cluster for Aurora PostgreSQL Limitless Database Turning on the Advanced mode when creating a database Turn on the Advanced mode of Database Insights when creating a database for Aurora PostgreSQL Limitless Database. ------ #### [ Console ] In the console, you can turn on the Advanced mode of Database Insights when you create a DB cluster. Settings for Database Insights apply to all DB instances in your DB cluster. **To turn on the Advanced mode of Database Insights when creating a DB cluster using the console** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Databases**. 1. Choose **Create database**. 1. In the **Database Insights** section, select **Advanced mode**. Then, choose the following options: + **Retention** – The amount of time to retain Performance Insights data. The retention period must be 15-24 months for the Advanced mode of Database Insights. + **AWS KMS key** – Specify your KMS key. Performance Insights encrypts all potentially sensitive data using your KMS key. Data is encrypted in flight and at rest. For more information, see [Encrypting Amazon Aurora resources](Overview.Encryption.md). 1. Choose **Create database**. ------ #### [ AWS CLI ] To turn on the Advanced mode of Database Insights when creating a DB cluster, call the [create-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-cluster.html) AWS CLI command and supply the following values: + `--db-cluster-identifier` – The identifier for the DB cluster. + `--database-insights-mode advanced` to turn on the Advanced mode of Database Insights. + `--engine` – The DB cluster must use the `aurora-postgresql` DB engine. + `--engine-version` – The DB cluster must use one of the DB engine versions: + `16.4-limitless` + `16.6-limitless` + `--storage-type` – The DB cluster must use the `aurora-iopt1` DB cluster storage configuration. + `--cluster-scalability-type` – Specifies the scalability mode of the Aurora DB cluster. When set to `limitless`, the cluster operates as an Aurora PostgreSQL Limitless Database. When set to `standard` (the default), the cluster uses normal DB instance creation. **Note** You can't modify this setting after you create the DB cluster. + `--master-username` – The name of the master user for the DB cluster. + `--master-user-password` – The password for the master user. + `--enable-performance-insights` to turn on Performance Insights for Database Insights. + `--performance-insights-retention-period` – The retention period for data for your DB cluster. To turn on Database Insights, the retention period must be at least 465 days. + `--monitoring-interval` – The interval, in seconds, between points when Enhanced Monitoring metrics are collected for the DB cluster. This value can't be `0`. + `--monitoring-role-arn` – The Amazon Resource Name (ARN) for the IAM role that permits RDS to send Enhanced Monitoring metrics to Amazon CloudWatch Logs. + `--enable-cloudwatch-logs-exports` – You must export `postgresql` logs to CloudWatch Logs. The following example enables the Advanced mode of Database Insights when creating a DB cluster. For Linux, macOS, or Unix: ``` aws rds create-db-cluster \ --db-cluster-identifier my-limitless-cluster \ --database-insights-mode advanced \ --engine aurora-postgresql \ --engine-version 16.6-limitless \ --storage-type aurora-iopt1 \ --cluster-scalability-type limitless \ --master-username myuser \ --master-user-password mypassword \ --enable-performance-insights \ --performance-insights-retention-period 465 \ --monitoring-interval 5 \ --monitoring-role-arn arn:aws:iam::123456789012:role/EMrole \ --enable-cloudwatch-logs-exports postgresql ``` For Windows: ``` aws rds create-db-cluster ^ --db-cluster-identifier my-limitless-cluster ^ --database-insights-mode advanced ^ --engine aurora-postgresql ^ --engine-version 16.6-limitless ^ --storage-type aurora-iopt1 ^ --cluster-scalability-type limitless ^ --master-username myuser ^ --master-user-password mypassword ^ --enable-performance-insights ^ --performance-insights-retention-period 465 ^ --monitoring-interval 5 ^ --monitoring-role-arn arn:aws:iam::123456789012:role/EMrole ^ --enable-cloudwatch-logs-exports postgresql ``` ------ #### [ RDS API ] To turn on the Advanced mode of Database Insights when you create a DB cluster, specify the following parameters for your [CreateDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBCluster.html) Amazon RDS API operation. + `DatabaseInsightsMode` to `advanced` + `Engine` to `aurora-postgresql` + `EngineVersion` to an available engine version for Limitless Database + `StorageType` to `aurora-iopt1` + `ClusterScalabilityType` to `limitless` + `MasterUsername` + `MasterUserPassword` + `EnablePerformanceInsights` to `True` + `PerformanceInsightsRetentionPeriod` to at least `465` days + `MonitoringInterval` to a value that isn't `0` + `MonitoringRoleArn` to the Amazon Resource Name (ARN) for the IAM role that permits RDS to send Enhanced Monitoring metrics to Amazon CloudWatch Logs ------ ## Turning on the Advanced mode of Database Insights when modifying a DB cluster for Aurora PostgreSQL Limitless Database Turning on the Advanced mode when modifying a database Turn on Database Insights when modifying a database for Aurora PostgreSQL Limitless Database. **Note** To enable Database Insights, each DB instance in aDB cluster must have the same Performance Insights and Enhanced Monitoring settings. ------ #### [ Console ] In the console, you can turn on the Advanced mode of Database Insights when you modify a DB cluster. Settings for Database Insights apply to all DB instances in your DB cluster. **To turn on the Advanced mode of Database Insights when modifying a DB cluster using the console** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Databases**. 1. Choose a DB cluster, and choose **Modify**. 1. In the **Database Insights** section, select **Advanced mode**. Then, choose the following options: + **Retention** – The amount of time to retain Performance Insights data. The retention period must be 15-24 months for the Advanced mode of Database Insights. + **AWS KMS key** – Specify your KMS key. Performance Insights encrypts all potentially sensitive data using your KMS key. Data is encrypted in flight and at rest. For more information, see [Encrypting Amazon Aurora resources](Overview.Encryption.md). 1. Choose **Continue**. 1. For **Scheduling of Modifications**, choose **Apply immediately**. If you choose **Apply during the next scheduled maintenance window**, your database ignores this setting and turns on the Advanced mode of Database Insights immediately. 1. Choose **Modify cluster**. ------ #### [ AWS CLI ] To turn on the Advanced mode of Database Insights when modifying a DB cluster, call the [modify-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster.html) AWS CLI command and supply the following values: + `--database-insights-mode advanced` to turn on the Advanced mode of Database Insights. + `--db-cluster-identifier` – The identifier for the DB cluster. + `--enable-performance-insights` to turn on Performance Insights for Database Insights. + `--performance-insights-retention-period` – The retention period for data for your DB cluster. To turn on the Advanced mode of Database Insights, the retention period must be at least 465 days. The following example enables the Advanced mode of Database Insights when modifying a DB cluster. For Linux, macOS, or Unix: ``` aws rds modify-db-cluster \ --database-insights-mode advanced \ --db-cluster-identifier sample-db-identifier \ --enable-performance-insights \ --performance-insights-retention-period 465 ``` For Windows: ``` aws rds modify-db-cluster ^ --database-insights-mode advanced ^ --db-cluster-identifier sample-db-identifier ^ --enable-performance-insights ^ --performance-insights-retention-period 465 ``` ------ #### [ RDS API ] To turn on the Advanced mode of Database Insights when you modify a DB cluster, specify the following parameters for your [ModifyDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBCluster.html) Amazon RDS API operation. + `DatabaseInsightsMode` to `advanced` + `EnablePerformanceInsights` to `True` + `PerformanceInsightsRetentionPeriod` to at least `465` days ------ # Turning on the Standard mode of Database Insights for Aurora PostgreSQL Limitless Database Turning on the Standard mode of Database Insights To turn on the Standard mode of Database Insights for your Aurora PostgreSQL Limitless Database, use the following procedures. ## Turning on the Standard mode of Database Insights when creating a DB cluster for Aurora PostgreSQL Limitless Database Turning on the Standard mode when creating a database Turn on the Standard mode of Database Insights when creating a database for Aurora PostgreSQL Limitless Database. ------ #### [ Console ] In the console, you can turn on the Standard mode of Database Insights when you create a DB cluster. Settings for Database Insights apply to all DB instances in your DB cluster. **To turn on the Standard mode of Database Insights when creating a DB cluster using the console** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Databases**. 1. Choose **Create database**. 1. In the **Database Insights** section, select **Standard mode**. Then, choose the following options: + **Retention** – The amount of time to retain Performance Insights data. To create a DB cluster for Aurora PostgreSQL Limitless Database, the retention period must be at least 31 days. + **AWS KMS key** – Specify your KMS key. Performance Insights encrypts all potentially sensitive data using your KMS key. Data is encrypted in flight and at rest. For more information, see [Encrypting Amazon Aurora resources](Overview.Encryption.md). 1. Choose **Create database**. ------ #### [ AWS CLI ] To turn on the Standard mode of Database Insights when creating a DB cluster, call the [create-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-cluster.html) AWS CLI command and supply the following values: + `--db-cluster-identifier` – The identifier for the DB cluster. + `--database-insights-mode standard` to turn on the Standard mode of Database Insights. + `--engine` – The DB cluster must use the `aurora-postgresql` DB engine. + `--engine-version` – The DB cluster must use one of the DB engine versions: + `16.4-limitless` + `16.6-limitless` + `--storage-type` – The DB cluster must use the `aurora-iopt1` DB cluster storage configuration. + `--cluster-scalability-type` – Specifies the scalability mode of the Aurora DB cluster. When set to `limitless`, the cluster operates as an Aurora PostgreSQL Limitless Database. When set to `standard` (the default), the cluster uses normal DB instance creation. **Note** You can't modify this setting after you create the DB cluster. + `--master-username` – The name of the master user for the DB cluster. + `--master-user-password` – The password for the master user. + `--enable-performance-insights` to turn on Performance Insights for Database Insights. + `--performance-insights-retention-period` – The retention period for data for your DB cluster. To create a DB cluster for Aurora PostgreSQL Limitless Database, the retention period must be at least 31 days. + `--monitoring-interval` – The interval, in seconds, between points when Enhanced Monitoring metrics are collected for the DB cluster. This value can't be `0`. + `--monitoring-role-arn` – The Amazon Resource Name (ARN) for the IAM role that permits RDS to send Enhanced Monitoring metrics to Amazon CloudWatch Logs. + `--enable-cloudwatch-logs-exports` – You must export `postgresql` logs to CloudWatch Logs. The following example enables the Standard mode of Database Insights when creating a DB cluster. For Linux, macOS, or Unix: ``` aws rds create-db-cluster \ --db-cluster-identifier my-limitless-cluster \ --database-insights-mode standard \ --engine aurora-postgresql \ --engine-version 16.6-limitless \ --storage-type aurora-iopt1 \ --cluster-scalability-type limitless \ --master-username myuser \ --master-user-password mypassword \ --enable-performance-insights \ --performance-insights-retention-period 31 \ --monitoring-interval 5 \ --monitoring-role-arn arn:aws:iam::123456789012:role/EMrole \ --enable-cloudwatch-logs-exports postgresql ``` For Windows: ``` aws rds create-db-cluster ^ --db-cluster-identifier my-limitless-cluster ^ --database-insights-mode standard ^ --engine aurora-postgresql ^ --engine-version 16.6-limitless ^ --storage-type aurora-iopt1 ^ --cluster-scalability-type limitless ^ --master-username myuser ^ --master-user-password mypassword ^ --enable-performance-insights ^ --performance-insights-retention-period 31 ^ --monitoring-interval 5 ^ --monitoring-role-arn arn:aws:iam::123456789012:role/EMrole ^ --enable-cloudwatch-logs-exports postgresql ``` ------ #### [ RDS API ] To turn on the Standard mode of Database Insights when you create a DB cluster, specify the following parameters for your [CreateDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBCluster.html) Amazon RDS API operation. + `DatabaseInsightsMode` to `standard` + `Engine` to `aurora-postgresql` + `EngineVersion` to an available engine version for Limitless Database + `StorageType` to `aurora-iopt1` + `ClusterScalabilityType` to `limitless` + `MasterUsername` + `MasterUserPassword` + `EnablePerformanceInsights` to `True` + `PerformanceInsightsRetentionPeriod` to at least `31` days + `MonitoringInterval` to a value that isn't `0` + `MonitoringRoleArn` to the Amazon Resource Name (ARN) for the IAM role that permits RDS to send Enhanced Monitoring metrics to Amazon CloudWatch Logs ------ ## Turning on the Standard mode of Database Insights when modifying a DB cluster for Aurora PostgreSQL Limitless Database Turning on the Standard mode when modifying a database Turn on Database Insights when modifying a database for Aurora PostgreSQL Limitless Database. **Note** To enable Database Insights, each DB instance in a DB cluster must have the same Performance Insights and Enhanced Monitoring settings. ------ #### [ Console ] In the console, you can turn on the Standard mode of Database Insights when you create a DB cluster. Settings for Database Insights apply to all DB instances in your DB cluster. **To turn on the Standard mode of Database Insights when modifying a DB cluster using the console** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Databases**. 1. Choose a DB cluster, and choose **Modify**. 1. In the **Database Insights** section, select **Standard mode**. Then, choose the following options: + **Retention** – The amount of time to retain Performance Insights data. To create a DB cluster for Aurora PostgreSQL Limitless Database, the retention period must be at least 31 days. + **AWS KMS key** – Specify your KMS key. Performance Insights encrypts all potentially sensitive data using your KMS key. Data is encrypted in flight and at rest. For more information, see [Encrypting Amazon Aurora resources](Overview.Encryption.md). 1. Choose **Continue**. 1. For **Scheduling of Modifications**, choose **Apply immediately**. If you choose **Apply during the next scheduled maintenance window**, your database ignores this setting and turns on the Standard mode of Database Insights immediately. 1. Choose **Modify cluster**. ------ #### [ AWS CLI ] To turn on the Standard mode of Database Insights when modifying a DB cluster, call the [modify-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster.html) AWS CLI command and supply the following values: + `--db-cluster-identifier` – The identifier for the DB cluster. + `--database-insights-mode standard` to turn on the Standard mode of Database Insights. + `--enable-performance-insights` to turn on Performance Insights for Database Insights. + `--performance-insights-retention-period` – The retention period for data for your DB cluster. To turn on the Standard mode of Database Insights, the retention period must be at least 31 days. The following example enables the Standard mode of Database Insights when modifying a DB cluster. For Linux, macOS, or Unix: ``` aws rds modify-db-cluster \ --database-insights-mode standard \ --db-cluster-identifier sample-db-identifier \ --enable-performance-insights \ --performance-insights-retention-period 31 ``` For Windows: ``` aws rds modify-db-cluster ^ --database-insights-mode standard ^ --db-cluster-identifier sample-db-identifier ^ --enable-performance-insights ^ --performance-insights-retention-period 31 ``` ------ #### [ RDS API ] To turn on the Standard mode of Database Insights when you modify a DB cluster, specify the following parameters for your [ModifyDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBCluster.html) Amazon RDS API operation. + `DatabaseInsightsMode` to `standard` + `EnablePerformanceInsights` to `True` + `PerformanceInsightsRetentionPeriod` to at least `31` days ------ # Monitoring Aurora PostgreSQL Limitless Database with Amazon CloudWatch Logs Monitoring Limitless Database with CloudWatch Logs Exporting PostgreSQL logs to CloudWatch Logs is required as part of enabling Aurora PostgreSQL Limitless Database. You can access and analyze these logs in CloudWatch Logs Insights, similar to accessing PostgreSQL logs for a standard Aurora PostgreSQL DB cluster. For more information, see [Analyzing PostgreSQL logs using CloudWatch Logs Insights](AuroraPostgreSQL.CloudWatch.Analyzing.md). The log group name for the DB cluster is the same as in Aurora PostgreSQL: ``` /aws/rds/cluster/DB_cluster_ID/postgresql ``` The log group name for the DB shard group takes the following form: ``` /aws/rds/cluster/DB_cluster_ID/DB_shard_group_ID/postgresql ``` There are log streams for each node (router or shard). Their names have the following form: ``` [DistributedTransactionRouter|DataAccessShard]/node_cluster_serial_ID-node_instance_serial_ID/n ``` For example: + Router – `DistributedTransactionRouter/6-6.2` + Shard – `DataAccessShard/22-22.0` **Note** You can't view PostgreSQL log files for the DB shard group directly in the RDS console, AWS CLI, or RDS API as you can for the DB cluster. You must use CloudWatch Logs Insights to view them. # Monitoring Aurora PostgreSQL Limitless Database with Enhanced Monitoring Monitoring Limitless Database with Enhanced Monitoring Enhanced Monitoring is required as part of enabling Aurora PostgreSQL Limitless Database. You can use it to monitor the operating system of your Limitless Database DB instances in real time. Aurora publishes Enhanced Monitoring metrics in CloudWatch Logs. Some of the key metrics available include database connections, storage usage, and query latency. These can help identify performance bottlenecks. For more information on Enhanced Monitoring metrics, see [OS metrics for Aurora](USER_Monitoring-Available-OS-Metrics.md#USER_Monitoring-Available-OS-Metrics-RDS). # Monitoring Aurora PostgreSQL Limitless Database with Performance Insights Monitoring Limitless Database with Performance Insights Use Performance Insights to monitor your Aurora PostgreSQL Limitless Database cluster. Performance Insights works similarly for Aurora PostgreSQL Limitless Database as it does for standard Aurora DB clusters. However, you track metrics at the shard group level for Aurora PostgreSQL Limitless Database. The two main Performance Insights metrics to track are the following: + Database load – Measures the level of activity in your database. The key metric in Performance Insights is `DBLoad`, which is collected every second. The unit for the `DBLoad` metric in Performance Insights is average active sessions (AAS). To get the average active sessions, Performance Insights samples the number of sessions concurrently running a query. The AAS is the total number of sessions divided by the total number of samples for a specific time period. For more information on `DBLoad` and AAS, see [Database load](USER_PerfInsights.Overview.ActiveSessions.md). + Maximum CPU – The maximum computational power available to your database. To see whether active sessions are exceeding the maximum CPU, look at their relationship to the `Max vCPU` line. The `Max vCPU` value is determined by the number of vCPU (virtual CPU) cores for your DB instance. For more information on `Max vCPU`, see [Maximum CPU](USER_PerfInsights.Overview.MaxCPU.md). In addition, you can "slice" the `DBLoad` metric into *dimensions*, which are subcategories of the metric. The most useful dimensions are the following: + Top instances – Shows the relative DB load for your instances (shards and routers) in descending order. + Wait events – Cause SQL statements to wait for specific events to happen before they can continue running. Wait events indicate where work is impeded. + Top SQL – Shows which queries contribute the most to DB load. For more information about Performance Insights dimensions, see [Dimensions](USER_PerfInsights.Overview.ActiveSessions.md#USER_PerfInsights.Overview.ActiveSessions.dimensions). The following figure shows the **Top instances** dimension for a DB shard group. ![\[Top instances dimension for a DB shard group.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/pi-top-instances.png) **Topics** + [ # Analyzing DB load for Aurora PostgreSQL Limitless Database using the Performance Insights dashboard ](USER_PerfInsights.AnalyzeLimitlessTables.md) # Analyzing DB load for Aurora PostgreSQL Limitless Database using the Performance Insights dashboard Analyzing DB load With Performance Insights, you can track metrics at the shard group level and at the instance level for an Aurora PostgreSQL Limitless Database. When analyzing DB load for an Aurora PostgreSQL Limitless Database, you might want to compare the DB load for each shard and router to the maximum vCPU. **Note** Aurora PostgreSQL Limitless Database always has Performance Insights and Enhanced Monitoring enabled. The minimum retention period for Performance Insights data for Limitless Database is 31 days (1 month). The **Absolute** view shows the number of Average active sessions (AAS) and the estimated vCPU. The **Relative** view shows the ratio of AAS to the estimated vCPU. **Topics** + [ ## Analyzing relative DB load for Aurora PostgreSQL Limitless Database using the Performance Insights dashboard ](#USER_PerfInsights.AnalyzeLimitlessTables.RelativeLoad) + [ # Analyzing DB load by waits for Aurora PostgreSQL Limitless Database using the Performance Insights dashboard ](USER_PerfInsights.AnalyzeLimitlessTables.Waits.md) + [ # Analyzing load distribution for Aurora PostgreSQL Limitless Database using the Performance Insights dashboard ](USER_PerfInsights.AnalyzeLimitlessTables.LoadDistribution.md) ## Analyzing relative DB load for Aurora PostgreSQL Limitless Database using the Performance Insights dashboard Analyzing relative DB load You might want to improve the performance of your Aurora PostgreSQL Limitless Database by tracking relative DB load. To analyze relative DB load by instance for your Aurora PostgreSQL Limitless Database, use the following procedure. **To analyze relative DB load for Aurora PostgreSQL Limitless Database using the console** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Performance Insights**. 1. Choose an Aurora PostgreSQL Limitless Database. The Performance Insights dashboard is displayed for that Aurora PostgreSQL Limitless Database. 1. In the **Database load (DB load)** section, choose **Instances** for **Sliced by**. To see the ratio of Average active sessions (AAS) to vCPU cores for all of the instances in your Aurora PostgreSQL Limitless Database, choose **Relative** for **Viewed as**. The Average active sessions chart shows the DB load for instances in your Aurora PostgreSQL Limitless Database. ![\[View the Performance Insights dashboard for your Aurora PostgreSQL Limitless Database sliced by instances.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/pi-relative-instances.png) 1. To view the top instances, choose the **Top instances** tab. In the following example, the instance with the highest DB load is `DTR-2-2`. ![\[Use the Top instances tab for an Aurora PostgreSQL Limitless Database sliced by instances.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/pi-top-instances.png) 1. (Optional) To analyze DB load for an instance in your Aurora PostgreSQL Limitless Database, choose the instance name in the **Instances** column. To view the DB load for `DTR-2-2`, choose `DTR-2-2` in the **Instances** column. **Note** You can view Performance Insights metrics only for instances in an Aurora PostgreSQL Limitless Database. # Analyzing DB load by waits for Aurora PostgreSQL Limitless Database using the Performance Insights dashboard Analyzing DB load by waits You might want to improve the performance for your Aurora PostgreSQL Limitless Database by tracking wait events. To analyze DB load by wait events for your Aurora PostgreSQL Limitless Database, use the following procedure. **To analyze DB load by waits for Aurora PostgreSQL Limitless Database using the console** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Performance Insights**. 1. Choose an Aurora PostgreSQL Limitless Database. The Performance Insights dashboard is displayed for that Aurora PostgreSQL Limitless Database. 1. In the **Database load (DB load)** section, choose **Waits** for **Sliced by**. To view the number of AAS and the estimated vCPU, choose **Absolute** for **Viewed as**. The Average active sessions chart shows the DB load for instances in your Aurora PostgreSQL Limitless Database. ![\[Sliced by waits.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/pi-absolute-waits.png) 1. Scroll down to the **Top SQL** tab. In the following example, the SQL statement with the highest load by waits is the `DELETE` statement. ![\[Top SQL tab when sliced by waits.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/pi-waits-top-sql.png) 1. Choose the SQL statement to expand it into its component statements. In the following example, the `SELECT` statement has 3 component statements. ![\[Choose a SQL statement to expand it.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/pi-waits-top-sql-selected.png) # Analyzing load distribution for Aurora PostgreSQL Limitless Database using the Performance Insights dashboard Analyzing load distribution You might want to balance the load distribution for instances on your Aurora PostgreSQL Limitless Database. To analyze load distribution of the instances on an Aurora PostgreSQL Limitless Database, use the following procedure. **To analyze load distribution of the instances on an Aurora PostgreSQL Limitless Database using the console** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Performance Insights**. 1. Choose an Aurora PostgreSQL Limitless Database. The Performance Insights dashboard is displayed for that Aurora PostgreSQL Limitless Database. 1. In the **Database load (DB load)** section, choose **Instances** for **Sliced by**. To view the number of AAS and the estimated vCPU for all instances in your Aurora PostgreSQL Limitless Database, choose **Absolute** for **Viewed as**. The Average active sessions chart shows the DB load for instances in your Aurora PostgreSQL Limitless Database. ![\[View the absolute Performance Insights dashboard for your Aurora PostgreSQL Limitless Database sliced by instances.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/pi-absolute-instances.png) 1. To see a chart of the load distribution of the instances in your Aurora PostgreSQL Limitless Database, choose the **Load distribution** tab. In the following example, the instance with the highest DB load is `DTR-2-2`. ![\[Top SQL tab when you slice by waits at the instance level.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/pi-load-distribution.png) # Monitoring Aurora PostgreSQL Limitless Database with Amazon GuardDuty RDS Protection Monitoring Limitless Database with GuardDuty RDS Protection Amazon GuardDuty is a threat detection service that helps protect your accounts, containers, workloads, and the data within your AWS environment. Using machine learning (ML) models, and anomaly and threat detection capabilities, GuardDuty continuously monitors different log sources and runtime activity to identify and prioritize potential security risks and malicious activities in your environment. GuardDuty RDS Protection analyzes and profiles login events for potential access threats to your Amazon Aurora databases. When you turn on RDS Protection, GuardDuty consumes RDS login events from your Aurora databases. RDS Protection monitors these events and profiles them for potential insider threats or external actors. For more information about GuardDuty RDS Protection in Aurora, see [Monitoring threats with Amazon GuardDuty RDS Protectionfor Amazon Aurora](guard-duty-rds-protection.md). For more information about enabling GuardDuty RDS Protection, see [GuardDuty RDS Protection](https://docs.aws.amazon.com/guardduty/latest/ug/rds-protection.html) in the *Amazon GuardDuty User Guide*. # Functions and views for Aurora PostgreSQL Limitless Database Functions and views for Limitless Database Aurora PostgreSQL Limitless Database has added functions and views. They're based on the corresponding Aurora PostgreSQL functions and views. **Note** Some statistics can return inconsistent results if there are transactions in progress. **Topics** + [ # Aurora PostgreSQL Limitless Database functions ](limitless-monitoring-functions.md) + [ # Aurora PostgreSQL Limitless Database views ](limitless-monitoring-views.md) # Aurora PostgreSQL Limitless Database functions Functions The following table shows the new functions for Aurora PostgreSQL Limitless Database. **Note** The functions listed in this table are located in the `rds_aurora` schema. When using a Limitless Database function, make sure to include the fully qualified object name: `rds_aurora`.`object_name`. | Aurora PostgreSQL Limitless Database function | Corresponding Aurora PostgreSQL function | | --- | --- | | [limitless\$1backend\$1dsid](#limitless_backend_dsid) | pg\$1backend\$1pid | | [limitless\$1cancel\$1session](#limitless_cancel_session) | pg\$1cancel\$1backend | | [limitless\$1stat\$1clear\$1snapshot](#limitless_stat_clear_snapshot) | pg\$1stat\$1clear\$1snapshot | | [limitless\$1stat\$1database\$1size](#limitless_stat_database_size) | pg\$1database\$1size | | [limitless\$1stat\$1get\$1snapshot\$1timestamp](#limitless_stat_get_snapshot_timestamp) | pg\$1stat\$1get\$1snapshot\$1timestamp | | [limitless\$1stat\$1prepared\$1xacts](#limitless_stat_prepared_xacts) | pg\$1prepared\$1xacts | | [limitless\$1stat\$1relation\$1sizes](#limitless_stat_relation_sizes) | pg\$1indexes\$1size, pg\$1relation\$1size, pg\$1table\$1size, pg\$1total\$1relation\$1size | | [limitless\$1stat\$1reset](#limitless_stat_reset) | pg\$1stat\$1reset | | [limitless\$1stat\$1statements\$1reset](#limitless_stat_statements_reset) | pg\$1stat\$1statements\$1reset | | [limitless\$1stat\$1system\$1waits](#limitless_stat_system_waits) | aurora\$1stat\$1system\$1waits | | [limitless\$1terminate\$1session](#limitless_terminate_session) | pg\$1terminate\$1backend | | [limitless\$1wait\$1report](#limitless_wait_report) | aurora\$1wait\$1report | The following examples provide details about the Aurora PostgreSQL Limitless Database functions. For more information on PostgreSQL functions, see [Functions and operators](https://www.postgresql.org/docs/15/functions.html) in the PostgreSQL documentation. **limitless\$1backend\$1dsid** The `limitless_backend_dsid` function returns the distributed session ID for the current session. A distributed session runs on a router in a DB shard group and involves backend processes on one or more shards in the DB shard group. The following example shows how to use the `limitless_backend_dsid` function. ``` SELECT rds_aurora.limitless_backend_dsid(); limitless_backend_dsid ------------------------ 8CACD7B04D0FC2A5 (1 row) ``` **limitless\$1cancel\$1session** The `limitless_cancel_session` function works similarly to `pg_cancel_backend`, but it tries to cancel all backend processes related to the provided distributed session ID by sending a `SIGINT` (interruption signal). The input parameter is the following: + `distributed_session_id` (text) – The ID of the distributed session to be canceled. The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `pid` (text) – The backend process ID. + `success` (boolean) – Whether the cancellation was successful. The following example shows how to use the `limitless_cancel_session` function. ``` SELECT * FROM rds_aurora.limitless_cancel_session('940CD5C81E3C796B'); subcluster_id | pid | success ---------------+-------+--------- 1 | 26920 | t (1 row) ``` **limitless\$1stat\$1clear\$1snapshot** The `limitless_stat_clear_snapshot` function discards the current statistics snapshot or cached information on all nodes. The following example shows how to use the `limitless_stat_clear_snapshot` function. ``` SELECT rds_aurora.limitless_stat_clear_snapshot(); ``` **limitless\$1stat\$1database\$1size** The `limitless_stat_database_size` function returns the sizes of a database in the DB shard group. The input parameter is the following: + `dbname` (name) – The database for which to get the sizes. The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `subcluster_type` (text) – The type of subcluster to which this process belongs: `router` or `shard`. + `db_size` – The size of the database in this subcluster in bytes. The following example shows how to use the `limitless_stat_database_size` function. ``` SELECT * FROM rds_aurora.limitless_stat_database_size('postgres_limitless'); subcluster_id | subcluster_type | db_size ---------------+-----------------+---------- 1 | router | 8895919 2 | router | 8904111 3 | shard | 21929391 4 | shard | 21913007 5 | shard | 21831087 (5 rows) ``` **limitless\$1stat\$1get\$1snapshot\$1timestamp** The `limitless_stat_get_snapshot_timestamp` function returns the timestamp of the current statistics snapshot, or `NULL` if no statistics snapshot has been taken. A snapshot is taken the first time cumulative statistics are accessed in a transaction if `stats_fetch_consistency` is set to `snapshot`. Returns a consolidated view of snapshot timestamps from all nodes. The `subcluster_id` and `subcluster_type` columns show which node the data is from. The following example shows how to use the `limitless_stat_get_snapshot_timestamp` function. ``` SELECT * FROM rds_aurora.limitless_stat_get_snapshot_timestamp(); subcluster_id | subcluster_type | snapshot_timestamp ---------------+-----------------+-------------------- 1 | router | 2 | router | 3 | shard | 4 | shard | 5 | shard | (5 rows) ``` **limitless\$1stat\$1prepared\$1xacts** The `limitless_stat_prepared_xacts` function returns information about transactions on all nodes that are currently prepared for two-phase commit. For more information, see [pg\$1prepared\$1xacts](https://www.postgresql.org/docs/current/view-pg-prepared-xacts.html) in the PostgreSQL documentation. The following example shows how to use the `limitless_stat_prepared_xacts` function. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_prepared_xacts; subcluster_id | subcluster_type | transaction_id | gid | prepared | owner_id | database_id ---------------+-----------------+----------------+------------------------------+-------------------------------+------------+-------------------- 8 | shard | 5815978 | 7_4599899_postgres_limitless | 2024-09-03 15:51:17.659603+00 | auroraperf | postgres_limitless 12 | shard | 4599138 | 7_4599899_postgres_limitless | 2024-09-03 15:51:17.659637+00 | auroraperf | postgres_limitless (2 rows) ``` **limitless\$1stat\$1relation\$1sizes** The `limitless_stat_relation_sizes` function returns the different sizes of a table in the DB shard group. The input parameters are the following: + `relnspname` (name) – The name of the schema containing the table. + `relname` (name) – The name of the table. The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `subcluster_type` (text) – The type of subcluster to which this process belongs: `router` or `shard`. + `main_size` – The size in bytes of the main data fork in this node. + `fsm_size` – The size in bytes of the free space map for the table in this node. + `vm_size` – The size in bytes of the visibility map for the table in this node. + `init_size` – The size in bytes of the initialization of the table in this node. + `toast_size` – The size in bytes of the toast table associated with the table in this fork. + `index_size` – The size in bytes of all of the indexes for the table in this node. + `total_size` – The size in bytes of all of the segments of the table in this node. The following example shows how to use the `limitless_stat_relation_sizes` function (some columns are omitted). ``` SELECT * FROM rds_aurora.limitless_stat_relation_sizes('public','customers'); subcluster_id | subcluster_type | main_size | fsm_size | vm_size | toast_size | table_size | total_size ---------------+-----------------+-----------+----------+---------+------------+------------+------------ 1 | router | 0 | 0 | 0 | 0 | 0 | 0 2 | router | 0 | 0 | 0 | 0 | 0 | 0 3 | shard | 4169728 | 4177920 | 1392640 | 1392640 | 11132928 | 11132928 4 | shard | 4169728 | 4177920 | 1392640 | 1392640 | 11132928 | 11132928 5 | shard | 3981312 | 4227072 | 1409024 | 1409024 | 11026432 | 11026432 (5 rows) ``` **limitless\$1stat\$1reset** The `limitless_stat_reset` function resets all statistics counters for the current database to zero (0). If `track_functions` is enabled, the `stats_reset` column in `limitless_stat_database` shows the last time statistics were reset for the database. By default, `limitless_stat_reset` can be run only by a superuser. Other users can be granted permission by using the `EXECUTE` privilege. The following example shows how to use the `limitless_stat_reset` function. ``` SELECT tup_inserted, tup_deleted FROM pg_stat_database WHERE datname = 'postgres_limitless'; tup_inserted | tup_deleted --------------+------------- 896 | 0 (1 row) SELECT rds_aurora.limitless_stat_reset(); limitless_stat_reset --------------------- (1 row) SELECT tup_inserted, tup_deleted FROM pg_stat_database WHERE datname = 'postgres_limitless'; tup_inserted | tup_deleted -------------+------------- 0 | 0 (1 row) ``` **limitless\$1stat\$1statements\$1reset** The `limitless_stat_statements_reset` function discards statistics gathered so far by `limitless_stat_statements` corresponding to the specified `username`, `dbname`, `distributed_query_id`, and `queryid` parameters. If any of the parameters aren't specified, the default value `""` or `0` (invalid) is used for each of them, and the statistics that match with other parameters are reset. If no parameter is specified, or all the specified parameters are `""` or `0` (invalid), the function discards all statistics. If all statistics in the `limitless_stat_statements` view are discarded, the function also resets the statistics in the `limitless_stat_statements_info` view. The input parameters are the following: + `username` (name) – The user that queried the statement. + `dbname` (name) – The database where the query was run. + `distributed_query_id` (bigint) – The query ID of the parent query from the coordinator node. This column is `NULL` if it's the parent query. The coordinator node pushes down the distributed query ID to the participant nodes. So for the participant nodes, the values for distributed query ID and query ID are different. + `queryid` (bigint) – The query ID of the statement. The following example shows how to use the `limitless_stat_statements_reset` function to reset all of the statistics gathered by `limitless_stat_statements`. ``` SELECT rds_aurora.limitless_stat_statements_reset(); ``` **limitless\$1stat\$1system\$1waits** The `limitless_stat_system_waits` function returns a consolidated view of the wait event data from `aurora_stat_system_waits`, which reports system wide wait activity in an instance, from all nodes. The `subcluster_id` and `subcluster_type` columns show which node the data is from. The following example shows how to use the `limitless_stat_system_waits` function. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_system_waits() lssw, pg_catalog.aurora_stat_wait_event() aswe WHERE lssw.event_id=aswe.event_id and aswe.event_name='LimitlessTaskScheduler'; subcluster_id | subcluster_type | type_id | event_id | waits | wait_time | event_name ---------------+-----------------+---------+-----------+--------+--------------+------------------------ 1 | router | 12 | 201326607 | 677068 | 616942216307 | LimitlessTaskScheduler 2 | router | 12 | 201326607 | 678586 | 616939897111 | LimitlessTaskScheduler 3 | shard | 12 | 201326607 | 756640 | 616965545172 | LimitlessTaskScheduler 4 | shard | 12 | 201326607 | 755184 | 616958057620 | LimitlessTaskScheduler 5 | shard | 12 | 201326607 | 757522 | 616963183539 | LimitlessTaskScheduler (5 rows) ``` **limitless\$1terminate\$1session** The `limitless_terminate_session` function works similarly to `pg_terminate_backend`, but it tries to end all backend processes related to the provided distributed session ID by sending a `SIGTERM` (end signal). The input parameter is the following: + `distributed_session_id` (text) – The ID of the distributed session to be ended. The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `pid` (text) – The backend process ID. + `success` (boolean) – Whether the process was successfully ended. The following example shows how to use the `limitless_terminate_session` function. ``` SELECT * FROM rds_aurora.limitless_terminate_session('940CD5C81E3C796B'); subcluster_id | pid | success ---------------+-------+--------- 1 | 26920 | t (1 row) ``` **limitless\$1wait\$1report** The `limitless_wait_report` function returns the wait event activity over a period of time from all nodes. The `subcluster_id` and `subcluster_type` columns show which node the data is from. The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `subcluster_type` (text) – The type of subcluster to which this process belongs: `router` or `shard`. The rest of the columns are the same as in `aurora_wait_report`. The following example shows how to use the `limitless_wait_report` function. ``` postgres_limitless=> select * from rds_aurora.limitless_wait_report(); subcluster_id | subcluster_type | type_name | event_name | waits | wait_time | ms_per_wait | waits_per_xact | ms_per_xact ---------------+-----------------+-----------+------------+-------+-----------+-------------+--------------- +------------- 1 | router | Client | ClientRead | 57 | 741550.14 | 13009.652 | 0.19 | 2505.237 5 | shard | Client | ClientRead | 54 | 738897.68 | 13683.290 | 0.18 | 2496.276 4 | shard | Client | ClientRead | 54 | 738859.53 | 13682.584 | 0.18 | 2496.147 2 | router | Client | ClientRead | 53 | 719223.64 | 13570.257 | 0.18 | 2429.810 3 | shard | Client | ClientRead | 54 | 461720.40 | 8550.378 | 0.18 | 1559.86 ``` # Aurora PostgreSQL Limitless Database views Views The following table shows the new views for Aurora PostgreSQL Limitless Database. **Note** The views listed in this table are located in the `rds_aurora` schema. When using a Limitless Database view, make sure to include the fully qualified object name: `rds_aurora`.`object_name`. | Aurora PostgreSQL Limitless Database view | Corresponding Aurora PostgreSQL view | | --- | --- | | [limitless\$1database](#limitless_database) | pg\$1database | | [limitless\$1locks](#limitless_locks) | pg\$1locks | | [limitless\$1stat\$1activity](#limitless_stat_activity) | pg\$1stat\$1activity | | [limitless\$1stat\$1all\$1indexes](#limitless_stat_all_indexes) | pg\$1stat\$1all\$1indexes | | [limitless\$1stat\$1all\$1tables](#limitless_stat_all_tables) | pg\$1stat\$1all\$1tables | | [limitless\$1stat\$1database](#limitless_stat_database) | pg\$1stat\$1database | | [limitless\$1stat\$1progress\$1vacuum](#limitless_stat_progress_vacuum) | pg\$1stat\$1progress\$1vacuum | | [limitless\$1stat\$1statements](#limitless_stat_statements) | pg\$1stat\$1statements | | [limitless\$1stat\$1subclusters](#limitless_stat_subclusters) | None | | [limitless\$1stat\$1statements\$1info](#limitless_stat_statements_info) | pg\$1stat\$1statements\$1info | | [limitless\$1statio\$1all\$1indexes](#limitless_statio_all_indexes) | pg\$1statio\$1all\$1indexes | | [limitless\$1statio\$1all\$1tables](#limitless_statio_all_tables) | pg\$1statio\$1all\$1tables | | [limitless\$1tables](#limitless_tables) | pg\$1tables | | [limitless\$1table\$1collocations](#limitless_table_collocations) | None | | [limitless\$1table\$1collocation\$1distributions](#limitless_table_collocation_distributions) | None | The following examples provide details about the Aurora PostgreSQL Limitless Database views. For more information on PostgreSQL views, see [Viewing statistics](https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-STATS-VIEWS) in the PostgreSQL documentation. **Note** Some statistics views can return inconsistent results if you have ongoing transactions. **limitless\$1database** This view contains information about the available databases in the DB shard group. For example: ``` postgres_limitless=> SELECT subcluster_id, subcluster_type, oid, datname, datacl FROM rds_aurora.limitless_database; subcluster_id | subcluster_type | oid | datname | datacl ---------------+-----------------+-------+--------------------+------------------------------------------------------------------------------------------------------------------------ 2 | router | 4 | template0 | {=c/rdsadmin,rdsadmin=CTc/rdsadmin} 2 | router | 5 | postgres | 2 | router | 16384 | rdsadmin | {rdsadmin=CTc/rdsadmin,rds_aurora_limitless_metadata_admin=c/rdsadmin,rds_aurora_limitless_heat_mgmt_admin=c/rdsadmin} 2 | router | 16477 | postgres_limitless | 2 | router | 1 | template1 | {=c/rdsadmin,rdsadmin=CTc/rdsadmin} 6 | shard | 4 | template0 | {=c/rdsadmin,rdsadmin=CTc/rdsadmin} ``` The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster (node) + `subcluster_type` (text) – The type of subcluster (node), router or shard The rest of the columns are the same as in `pg_database`. **limitless\$1locks** This view contains one row per process per node. It provides access to information about the locks held by active processes in the database server. **Example of creating a lock with two transactions** In this example, we run two transactions simultaneously on two routers. ``` # Transaction 1 (run on router 1) BEGIN; SET search_path = public; SELECT * FROM customers; INSERT INTO customers VALUES (400,'foo','bar'); # Transaction 2 (run on router 2) BEGIN; SET search_path = public; ALTER TABLE customers ADD COLUMN phone VARCHAR; ``` The first transaction is run. Subsequent transactions have to wait until the first transaction is completed. Therefore the second transaction is blocked with a lock. To check the root cause of it, we run a command by joining `limitless_locks` with `limitless_stat_activity`. ``` # Run on router 2 SELECT distributed_session_id, state, usename, query, query_start FROM rds_aurora.limitless_stat_activity WHERE distributed_session_id in ( SELECT distributed_session_id FROM rds_aurora.limitless_locks WHERE relname = 'customers' ); distributed_session_id | state | usename | query | query_start ------------------------+---------------------+--------------------------+---------------------------------- -------------+------------------------------- 47BDE66E9A5E8477 | idle in transaction | limitless_metadata_admin | INSERT INTO customers VALUES (400,'foo','bar'); | 2023-04-13 17:44:45.152244+00 2AD7F370202D0FA9 | active | limitless_metadata_admin | ALTER TABLE customers ADD COLUMN phone VARCHAR; | 2023-04-13 17:44:55.113388+00 47BDE66E9A5E8477 | | limitless_auth_admin | | 2AD7F370202D0FA9 | | limitless_auth_admin | | 47BDE66E9A5E8477 | | limitless_auth_admin | | 2AD7F370202D0FA9 | | limitless_auth_admin | | (6 rows) ``` **Example of creating a lock explicitly** In this example, we create a lock explicitly, then use the `limitless_locks` view to see the locks (some columns are omitted). ``` BEGIN; SET search_path = public; LOCK TABLE customers IN ACCESS SHARE MODE; SELECT * FROM rds_aurora.limitless_locks WHERE relname = 'customers'; subcluster_id | subcluster_type | distributed_session_id | locktype | datname | relnspname | relname | virtualtransaction | pid | mode ---------------+-----------------+------------------------+----------+--------------------+------------+ ----------+--------------------+-------+----------------- 1 | router | 7207702F862FC937 | relation | postgres_limitless | public | customers | 28/600787 | 59564 | AccessShareLock 2 | router | 7207702F862FC937 | relation | postgres_limitless | public | customers | 28/600405 | 67130 | AccessShareLock 3 | shard | 7207702F862FC937 | relation | postgres_limitless | public | customers | 15/473401 | 27735 | AccessShareLock 4 | shard | 7207702F862FC937 | relation | postgres_limitless | public | customers | 13/473524 | 27734 | AccessShareLock 5 | shard | 7207702F862FC937 | relation | postgres_limitless | public | customers | 13/472935 | 27737 | AccessShareLock 6 | shard | 7207702F862FC937 | relation | postgres_limitless | public | customers | 13/473015 | 48660 | AccessShareLock (6 rows) ``` **limitless\$1stat\$1activity** This view contains one row per process per node. It can be used to track overall system health and triage processes that are taking a long time. For example: ``` postgres=# SELECT subcluster_id, subcluster_type, distributed_session_id, distributed_session_state, datname, distributed_query_id, is_sso_query FROM rds_aurora.limitless_stat_activity WHERE distributed_session_id in ('D2470C97E3D07E06', '5A3CD7B8E5FD13FF') order by distributed_session_id; subcluster_id | subcluster_type | distributed_session_id | distributed_session_state | datname | distributed_query_id | is_sso_query ---------------+-----------------+------------------------+---------------------------+--------------------+----------------------+-------------- 2 | router | 5A3CD7B8E5FD13FF | coordinator | postgres_limitless | | f 3 | shard | 5A3CD7B8E5FD13FF | participant | postgres_limitless | 6808291725541680947 | 4 | shard | 5A3CD7B8E5FD13FF | participant | postgres_limitless | 6808291725541680947 | 2 | router | D2470C97E3D07E06 | coordinator | postgres_limitless | | t 3 | shard | D2470C97E3D07E06 | participant | postgres_limitless | 4058400544464210222 | (5 rows) ``` The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `subcluster_type` (text) – The type of subcluster to which this process belongs: `router` or `shard`. + `distributed_session_id` (text) – The ID of the distributed session to which this process belongs. + `distributed_session_state` (text) – Whether this is a coordinator, participant, or standalone/nondistributed process (shown as `NULL`). + `datname` (text) – The database to which this process is connected. + `distributed_query_id` (bigint) – The query ID of the parent query from the coordinator node. This column is `NULL` if it's the parent query. The coordinator node pushes down the distributed query ID to the participant nodes. So for the participant nodes, the values for distributed query ID and query ID are different. + `is_sso_query` (text) – This lets us know whether the query is single shard optimized or not. The rest of the columns are the same as in `pg_stat_activity`. **limitless\$1stat\$1all\$1indexes** This view contains usage statistics on indexes in the DB shard group. For example: ``` postgres_limitless=> SELECT schemaname, relname, indexrelname, idx_scan FROM rds_aurora.limitless_stat_all_indexes WHERE relname LIKE 'orders_ts%' ORDER BY indexrelname LIMIT 10; schemaname | relname | indexrelname | idx_scan ------------+----------------+---------------------+---------- ec_sample | orders_ts00001 | orders_ts00001_pkey | 196801 ec_sample | orders_ts00002 | orders_ts00002_pkey | 196703 ec_sample | orders_ts00003 | orders_ts00003_pkey | 196376 ec_sample | orders_ts00004 | orders_ts00004_pkey | 197966 ec_sample | orders_ts00005 | orders_ts00005_pkey | 195301 ec_sample | orders_ts00006 | orders_ts00006_pkey | 195673 ec_sample | orders_ts00007 | orders_ts00007_pkey | 194475 ec_sample | orders_ts00008 | orders_ts00008_pkey | 191694 ec_sample | orders_ts00009 | orders_ts00009_pkey | 193744 ec_sample | orders_ts00010 | orders_ts00010_pkey | 195421 (10 rows) ``` **limitless\$1stat\$1all\$1tables** This view contains statistics about all tables in the current database in the DB shard group. This is useful when tracking vacuum operations and Data Manipulation language (DML) operations. For example: ``` postgres_limitless=> SELECT subcluster_id, subcluster_type, relname, n_ins_since_vacuum, n_tup_ins, last_vacuum FROM rds_aurora.limitless_stat_all_tables WHERE relname LIKE 'orders_ts%' ORDER BY relname LIMIT 10; subcluster_id | subcluster_type | relname | n_ins_since_vacuum | n_tup_ins | last_vacuum ---------------+-----------------+----------------+--------------------+-----------+------------- 5 | shard | orders_ts00001 | 34779 | 196083 | 5 | shard | orders_ts00002 | 34632 | 194721 | 5 | shard | orders_ts00003 | 34950 | 195965 | 5 | shard | orders_ts00004 | 34745 | 197283 | 5 | shard | orders_ts00005 | 34879 | 195754 | 5 | shard | orders_ts00006 | 34340 | 194605 | 5 | shard | orders_ts00007 | 33779 | 192203 | 5 | shard | orders_ts00008 | 33826 | 191293 | 5 | shard | orders_ts00009 | 34660 | 194117 | 5 | shard | orders_ts00010 | 34569 | 195560 | (10 rows) ``` The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `subcluster_type` (text) – The type of subcluster to which this process belongs: `router` or `shard`. + `relname` (name) – The name of the table. The rest of the columns are the same as in `pg_stat_all_tables`. **limitless\$1stat\$1database** This view contains statistics about all databases in the DB shard group. Returns one row per database per node. For example: ``` postgres_limitless=> SELECT subcluster_id, subcluster_type, datname, blks_read, blks_hit FROM rds_aurora.limitless_stat_database WHERE datname='postgres_limitless'; subcluster_id | subcluster_type | datname | blks_read | blks_hit ---------------+-----------------+--------------------+-----------+---------- 1 | router | postgres_limitless | 484 | 34371314 2 | router | postgres_limitless | 673 | 33859317 3 | shard | postgres_limitless | 1299 | 17749550 4 | shard | postgres_limitless | 1094 | 17492849 5 | shard | postgres_limitless | 1036 | 17485098 6 | shard | postgres_limitless | 1040 | 17437257 (6 rows) ``` The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `subcluster_type` (text) – The type of subcluster to which this process belongs: `router` or `shard`. + `datname` (name) – The name of the database. The rest of the columns are the same as in `pg_stat_database`. **limitless\$1stat\$1progress\$1vacuum** This view contains information about ongoing vacuuming operations. For example: ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_progress_vacuum; -[ RECORD 1 ]----------+------------------ subcluster_id | 3 subcluster_type | shard distributed_session_id | A56D96E2A5C9F426 pid | 5270 datname | postgres nspname | public relname | customer_ts2 phase | vacuuming heap heap_blks_total | 130500 heap_blks_scanned | 100036 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuples | 11184810 num_dead_tuples | 0 -[ RECORD 2 ]----------+------------------ subcluster_id | 3 subcluster_type | shard distributed_session_id | 56DF26A89EC23AB5 pid | 6854 datname | postgres nspname | public relname | sales_ts1 phase | vacuuming heap heap_blks_total | 43058 heap_blks_scanned | 24868 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuples | 8569523 num_dead_tuples | 0 ``` The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `subcluster_type` (text) – The type of subcluster to which this process belongs: `router` or `shard`. + `distributed_session_id` (text) – The identifier for the session that initiated the vacuuming operation. + `datname` (name) – The database where the vacuuming is being done. + `nspname` (name) – The name of the schema of the table that is being vacuumed. It's `null` if the table being vacuumed is not in the same database as the one to which the user is connected. + `relname` (name) – The name of the table that is being vacuumed. It's `null` if the table being vacuumed is not in the same database as the one to which the user is connected. The rest of the columns are the same as in `pg_stat_progress_vacuum`. **limitless\$1stat\$1statements** This view provides a means for tracking planning and running statistics of all SQL statements run on all nodes. You must install the [pg\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html) extension to use the `limitless_stat_statements` view. ``` -- CREATE EXTENSION must be run by a superuser CREATE EXTENSION pg_stat_statements; -- Verify that the extension is created on all nodes in the DB shard group SELECT distinct node_id FROM rds_aurora.limitless_stat_statements LIMIT 10; ``` The following example shows the use of the `limitless_stat_statements` view. ``` postgres_limitless=> SELECT subcluster_id, subcluster_type, distributedqueryid, username, dbname, sso_calls FROM rds_aurora.limitless_stat_statements; subcluster_id | subcluster_type | distributedqueryid | username | dbname | sso_calls ---------------+-----------------+----------------------+-------------------------------------+--------------------+----------- 2 | router | | postgres | postgres_limitless | 0 2 | router | | postgres | postgres_limitless | 0 2 | router | | postgres | postgres_limitless | 0 2 | router | | postgres | postgres_limitless | 0 2 | router | | postgres | postgres_limitless | 0 2 | router | | postgres | postgres_limitless | 1 3 | shard | -7975178695405682176 | postgres | postgres_limitless | [...] ``` The output parameters are the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. + `subcluster_type` (text) – The type of subcluster to which this process belongs: `router` for or `shard`. + `distributedqueryid` (bigint) – The query ID of the parent query from the coordinator node. This column is `NULL` if it's the parent query. The coordinator node pushes down the distributed query ID to the participant nodes. So for the participant nodes, the values for distributed query ID and query ID are different. + `username` (name) – The user that queried the statement. + `dbname` (name) – The database where the query was run. + `sso_calls` (name) – The number of times statement was single-shard optimized. The rest of the columns are the same as in [pg\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html). **limitless\$1stat\$1statements\$1info** This view contains statistics for the `limitless_stat_statements` view. Each row contains data for the [pg\$1stat\$1statements\$1info](https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.41.7) view from each node. The `subcluster_id` column identifies each node. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_statements_info; subcluster_id | subcluster_type | dealloc | stats_reset ---------------+-----------------+---------+------------------------------- 1 | router | 0 | 2023-06-30 21:22:09.524781+00 2 | router | 0 | 2023-06-30 21:21:40.834111+00 3 | shard | 0 | 2023-06-30 21:22:10.709942+00 4 | shard | 0 | 2023-06-30 21:22:10.740179+00 5 | shard | 0 | 2023-06-30 21:22:10.774282+00 6 | shard | 0 | 2023-06-30 21:22:10.808267+00 (6 rows) ``` The output parameter is the following: + `subcluster_id` (text) – The ID of the subcluster to which this process belongs. The rest of the columns are the same as in [pg\$1stat\$1statements\$1info](https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.41.7). **limitless\$1stat\$1subclusters** This view contains network statistics between routers and other nodes. It contains a row per pair of router and other node, for example: ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_subclusters; orig_subcluster | orig_instance_az | dest_subcluster | dest_instance_az | latency_us | latest_collection | failed_requests | received_bytes | sent_bytes | same_az_requests | cross_az_requests | stat_reset_timestamp -----------------+------------------+-----------------+------------------+------------+-------------------------------+-----------------+----------------+------------+------------------+-------------------+------------------------------- 3 | us-west-2b | 2 | us-west-2a | 847 | 2024-10-07 17:25:39.518617+00 | 0 | 35668633 | 92090171 | 0 | 302787 | 2024-10-05 12:39:55.239675+00 3 | us-west-2b | 4 | us-west-2b | 419 | 2024-10-07 17:25:39.546376+00 | 0 | 101190464 | 248795719 | 883478 | 0 | 2024-10-05 12:39:55.231218+00 3 | us-west-2b | 5 | us-west-2c | 1396 | 2024-10-07 17:25:39.52122+00 | 0 | 72864849 | 172086292 | 0 | 557726 | 2024-10-05 12:39:55.196412+00 3 | us-west-2b | 6 | us-west-2c | 729 | 2024-10-07 17:25:39.54828+00 | 0 | 35668584 | 92090171 | 0 | 302787 | 2024-10-05 12:39:55.247334+00 3 | us-west-2b | 7 | us-west-2a | 1702 | 2024-10-07 17:25:39.545307+00 | 0 | 71699576 | 171634844 | 0 | 556278 | 2024-10-05 12:39:52.715168+00 2 | us-west-2a | 3 | us-west-2b | 868 | 2024-10-07 17:25:40.293927+00 | 0 | 35659611 | 92011872 | 0 | 302817 | 2024-10-05 12:39:54.420758+00 2 | us-west-2a | 4 | us-west-2b | 786 | 2024-10-07 17:25:40.296863+00 | 0 | 102437253 | 251838024 | 0 | 895060 | 2024-10-05 12:39:54.404081+00 2 | us-west-2a | 5 | us-west-2c | 1232 | 2024-10-07 17:25:40.292021+00 | 0 | 71990027 | 168828110 | 0 | 545453 | 2024-10-05 12:39:36.769549+00 ``` The output parameters are the following: + `orig_subcluster` (text) – The ID of the router where the communications originate + `orig_subcluster_az` (text) – The Availability Zone (AZ) of the originator router + `dest_subcluster` (text) – The ID of the destination node + `dest_subcluster_az` (text) – The last collected AZ of the destination node + `latency_us` (bigint) – The last collected network latency between nodes, in microseconds. The value is `0` if the node is unreachable. + `latest_collection` (timestamp) – The timestamp of the latest collection of AZ and latency for the destination node + `failed_requests` (bigint) – The cumulative count of failed internal requests + `received_bytes` (bigint) – The estimated cumulative number of bytes received from this node + `sent_bytes` (bigint) – The estimated cumulative number of bytes sent to this node + `same_az_requests` (bigint) – The cumulative number of internal DB requests to this node when it's in the same AZ as the originator router + `cross_az_requests` (bigint) – The cumulative number of internal DB requests to this node when it's in a different AZ from the originator router + `stat_reset_timestamp` (timestamp) – The timestamp when the cumulative statistics for this view were last reset **limitless\$1statio\$1all\$1indexes** This view contains input/output (I/O) statistics for all indexes in the DB shard group. For example: ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_statio_all_indexes WHERE relname like'customers_ts%'; subcluster_id | subcluster_type | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit ---------------+-----------------+------------+-------------------+-------------------------------------+ --------------+-------------- 3 | shard | public | customers_ts00002 | customers_ts00002_customer_name_idx | 1 | 0 3 | shard | public | customers_ts00001 | customers_ts00001_customer_name_idx | 1 | 0 4 | shard | public | customers_ts00003 | customers_ts00003_customer_name_idx | 1 | 0 4 | shard | public | customers_ts00004 | customers_ts00004_customer_name_idx | 1 | 0 5 | shard | public | customers_ts00005 | customers_ts00005_customer_name_idx | 1 | 0 5 | shard | public | customers_ts00006 | customers_ts00006_customer_name_idx | 1 | 0 6 | shard | public | customers_ts00007 | customers_ts00007_customer_name_idx | 1 | 0 6 | shard | public | customers_ts00008 | customers_ts00008_customer_name_idx | 1 | 0 (8 rows) ``` **limitless\$1statio\$1all\$1tables** This view contains input/output (I/O) statistics for all tables in the DB shard group. For example: ``` postgres_limitless=> SELECT subcluster_id, subcluster_type, schemaname, relname, heap_blks_read, heap_blks_hit FROM rds_aurora.limitless_statio_all_tables WHERE relname LIKE 'customers_ts%'; subcluster_id | subcluster_type | schemaname | relname | heap_blks_read | heap_blks_hit ---------------+-----------------+------------+-------------------+----------------+--------------- 3 | shard | public | customers_ts00002 | 305 | 57780 3 | shard | public | customers_ts00001 | 300 | 56972 4 | shard | public | customers_ts00004 | 302 | 57291 4 | shard | public | customers_ts00003 | 302 | 57178 5 | shard | public | customers_ts00006 | 300 | 56932 5 | shard | public | customers_ts00005 | 302 | 57386 6 | shard | public | customers_ts00008 | 300 | 56881 6 | shard | public | customers_ts00007 | 304 | 57635 (8 rows) ``` **limitless\$1tables** This view contains information about tables in Aurora PostgreSQL Limitless Database. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_tables; table_gid | local_oid | schema_name | table_name | table_status | table_type | distribution_key -----------+-----------+-------------+-------------+--------------+-------------+------------------ 5 | 18635 | public | placeholder | active | placeholder | 6 | 18641 | public | ref | active | reference | 7 | 18797 | public | orders | active | sharded | HASH (order_id) 2 | 18579 | public | customer | active | sharded | HASH (cust_id) (4 rows) ``` **limitless\$1table\$1collocations** This view contains information about collocated sharded tables. In the following example, the `orders` and `customers` tables are collocated, and the `users` and `followers` tables are collocated. Collocated tables have the same `collocation_id`. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocations ORDER BY collocation_id; collocation_id | schema_name | table_name ----------------+-------------+------------ 2 | public | orders 2 | public | customers 5 | public | users 5 | public | followers (4 rows) ``` **limitless\$1table\$1collocation\$1distributions** This view shows the key distribution for each collocation. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocation_distributions ORDER BY collocation_id, lower_bound; collocation_id | subcluster_id | lower_bound | upper_bound ----------------+---------------+----------------------+---------------------- 2 | 6 | -9223372036854775808 | -4611686018427387904 2 | 5 | -4611686018427387904 | 0 2 | 4 | 0 | 4611686018427387904 2 | 3 | 4611686018427387904 | 9223372036854775807 5 | 6 | -9223372036854775808 | -4611686018427387904 5 | 5 | -4611686018427387904 | 0 5 | 4 | 0 | 4611686018427387904 5 | 3 | 4611686018427387904 | 9223372036854775807 (8 rows) ``` # Wait events for Aurora PostgreSQL Limitless Database Wait events for Limitless Database A wait event in Aurora PostgreSQL indicates a resource for which a session is waiting, such as input/output (I/O) and locks. Wait events are helpful in finding out why sessions are waiting for resources, and identifying bottlenecks. For more information, see [Aurora PostgreSQL wait events](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.waits). Aurora PostgreSQL Limitless Database has its own wait events that are related to routers and shards. Many of them are for routers waiting on shards to complete tasks. Shard wait events contain details on tasks that are being performed. **Topics** + [ ## Querying for wait events ](#limitless-monitoring-waits.query) + [ # Limitless Database wait events ](limitless-waits-reference.md) ## Querying for wait events You can use the [limitless\$1stat\$1activity](limitless-monitoring-views.md#limitless_stat_activity) view to query for wait events, as shown in the following example. ``` SELECT wait_event FROM rds_aurora.limitless_stat_activity WHERE wait_event_type='AuroraLimitless'; wait_event ---------------------- RemoteStatementSetup RemoteStatementSetup (2 rows) ``` You can also use the `aurora_stat_system_waits` function to list the number of waits and the total time spent on each wait event, as shown in the following example. ``` postgres_limitless=> SELECT type_name,event_name,waits,wait_time FROM aurora_stat_system_waits() NATURAL JOIN aurora_stat_wait_event() NATURAL JOIN aurora_stat_wait_type() WHERE type_name='AuroraLimitless' ORDER BY wait_time DESC; type_name | event_name | waits | wait_time -----------------+---------------------------+---------+------------- AuroraLimitless | RemoteStatementSetup | 7518 | 75236507897 AuroraLimitless | RemoteStatementExecution | 40 | 132986 AuroraLimitless | Connect | 5 | 1453 (3 rows) ``` # Limitless Database wait events Wait events The following wait events apply to Aurora PostgreSQL Limitless Database. You can monitor these wait events to identify bottlenecks in Aurora PostgreSQL Limitless Database processing. **Topics** + [ ## IO:TwophaseFilePoolWrite wait event ](#limitless-waits-TwophaseFilePoolWrite) + [ ## IO:TwophaseFilePoolRead wait event ](#limitless-waits-TwophaseFilePoolRead) + [ ## AuroraLimitless:Connect wait event ](#limitless-waits-Connect) + [ ## AuroraLimitless:AsyncConnect wait event ](#limitless-waits-AsyncConnect) + [ ## AuroraLimitless:RemoteStatementSetup wait event ](#limitless-waits-RemoteStatementSetup) + [ ## AuroraLimitless:RemoteDDLExecution wait event ](#limitless-waits-RemoteDDLExecution) + [ ## AuroraLimitless:RemoteStatementExecution wait event ](#limitless-waits-RemoteStatementExecution) + [ ## AuroraLimitless:FetchRemoteResults wait event ](#limitless-waits-FetchRemoteResults) + [ ## AuroraLimitless:AsyncGetInitialResponse wait event ](#limitless-waits-AsyncGetInitialResponse) + [ ## AuroraLimitless:AsyncGetNextResponse wait event ](#limitless-waits-AsyncGetNextResponse) + [ ## AuroraLimitless:AbortedCommandCleanup wait event ](#limitless-waits-AbortedCommandCleanup) + [ ## AuroraLimitless:DistributedCommitPrepare wait event ](#limitless-waits-DistributedCommitPrepare) + [ ## AuroraLimitless:DistributedCommit wait event ](#limitless-waits-DistributedCommit) + [ ## AuroraLimitless:DistributedCommitPrepareThrottle wait event ](#limitless-waits-DistributedCommitPrepareThrottle) + [ ## AuroraLimitless:PreparedTransactionResolution wait event ](#limitless-waits-PreparedTransactionResolution) + [ ## AuroraLimitless:SendPreparedTransactionOutcome wait event ](#limitless-waits-SendPreparedTransactionOutcome) + [ ## AuroraLimitless:CommitClockBarrier wait event ](#limitless-waits-CommitClockBarrier) + [ ## AuroraLimitless:SnapshotClockBarrier wait event ](#limitless-waits-SnapshotClockBarrier) + [ ## AuroraLimitless:ReaderSnapshotClockBarrier wait event ](#limitless-waits-ReaderSnapshotClockBarrier) + [ ## AuroraLimitless:GatherDistributedDeadlockGraph wait event ](#limitless-waits-GatherDistributedDeadlockGraph) + [ ## AuroraLimitless:DistributedDeadlockDetection wait event ](#limitless-waits-DistributedDeadlockDetection) + [ ## AuroraLimitless:DistributedDeadlockAbort wait event ](#limitless-waits-DistributedDeadlockAbort) + [ ## AuroraLimitless:GatherRemoteStats wait event ](#limitless-waits-GatherRemoteStats) + [ ## AuroraLimitless:GlobalSequenceRefresh wait event ](#limitless-waits-GlobalSequenceRefresh) + [ ## AuroraLimitless:GlobalVacuumTimeExchange wait event ](#limitless-waits-GlobalVacuumTimeExchange) + [ ## AuroraLimitless:DistributedTransactionMonitorGather wait event ](#limitless-waits-DistributedTransactionMonitorGather) + [ ## AuroraLimitlessActivity:AdminTaskSchedulerMain wait event ](#limitless-waits-AdminTaskSchedulerMain) + [ ## AuroraLimitlessActivity:AdminTaskExecutorMain wait event ](#limitless-waits-AdminTaskExecutorMain) + [ ## AuroraLimitlessActivity:AdminTaskMonitorMain wait event ](#limitless-waits-AdminTaskMonitorMain) + [ ## AuroraLimitlessActivity:DatabaseCleanupMonitorMain wait event ](#limitless-waits-DatabaseCleanupMonitorMain) + [ ## AuroraLimitlessActivity:TopologyCleanupMonitorMain wait event ](#limitless-waits-TopologyCleanupMonitorMain) + [ ## AuroraLimitlessActivity:ToplogyChangeMonitorMain wait event ](#limitless-waits-ToplogyChangeMonitorMain) + [ ## AuroraLimitlessActivity:DistributedTransactionMonitorMain wait event ](#limitless-waits-DistributedTransactionMonitorMain) + [ ## AuroraLimitlessActivity:GlobalVacuumMonitorMain wait event ](#limitless-waits-GlobalVacuumMonitorMain) ## IO:TwophaseFilePoolWrite wait event Waiting for a write of a two-phase state file within the two-phase state file pool. This is an Aurora specific event. ### Causes Processes executing a `PREPARED TRANSACTION` command, including participants in a Limitless Database distributed transaction, must persist transaction state in a two-phase file. Aurora uses a file pool to improve performance of this operation. ### Action This is a synchronous write I/O operation and therefore a high latency in this event has similar causes to `IO:XactSync` and can be investigated in the same way. If using Limitless Database, you might need to reduce the number of distributed transactions being executed. ## IO:TwophaseFilePoolRead wait event Waiting for a read of a two-phase state file within the two-phase state file pool. ### Causes Processes executing a `COMMIT PREPARED` command against a previously prepared transaction, including participants in a Limitless Database distributed transaction, might need to read previosly persisted transaction state from a two-phase file. Aurora uses a file pool to improve performance of this operation. ### Action This is a read I/O operation. Therefore, a high latency in this event has similar causes to `IO:DataFileRead` and can be investigated the same. If using Limitless Database, you might need to reduce the number of distributed transactions being executed. ## AuroraLimitless:Connect wait event The process is waiting for a connection to another node in the cluster to be established. ### Causes Connections are established between processes and remote nodes to execute queries, distributed transactions, and perform DDLs. ### Action Reduce the number of simultaneous connections to the cluster or tune the use of cross-shard queries. ## AuroraLimitless:AsyncConnect wait event This event is similar to `Connect`, but represents a process waiting for parallel connections to a set of nodes to be established. ### Causes Parallel connection establishment is most commonly done when executing DDL statements. ### Action Reduce the number of DDL statements or combine multiple DDLs in the same session to improve connection reuse. ## AuroraLimitless:RemoteStatementSetup wait event The process is waiting for remote query execution setup, such as cursor open, close, or prepared statement creation. ### Causes This wait event increases with the number of scans on sharded tables where the statement could not be single-shard optimized. ### Action Optimize queries to reduce the number of scan operations or increase eligibility for single-shard optimization. ## AuroraLimitless:RemoteDDLExecution wait event The process is waiting for a remote DDL command to finish. ### Causes When you issue a DDL command on a DB shard group, it must be distributed to other router and shard nodes before confirming the operation. Some DDL operations can run for a long time, because data must be adapted to schema changes. ### Action Identify long-running DDL commands so that you can optimize them. ## AuroraLimitless:RemoteStatementExecution wait event A process is waiting for a remote command to finish. ### Causes A SQL command is running on a remote node. This event will appear frequently for internal communications, such as `auto_analyze` and heartbeat checks. ### Action Idenfify long-running commands using the limitless\$1stat\$1statements view. In many cases this is an expected event, especially for background workers or internal processes and no action is needed. ## AuroraLimitless:FetchRemoteResults wait event A process is waiting to retrieve rows from a remote node. ### Causes This wait event can increase when fetching a large number of rows from a remote table, such as a sharded or reference table. ### Action Identify unoptimized `SELECT` queries using the `limitless_stat_statements` view. Optimize queries to retrieve only necessary data. You can also tune the `rds_aurora.limitless_maximum_adaptive_fetch_size` parameter. ## AuroraLimitless:AsyncGetInitialResponse wait event The process is waiting for an initial response when pipeline mode is used in query execution. ### Causes This will typically be seen during router to shard execution for queries with single-shard data placement and is an expected part of normal execution. ### Action No further action is required. ## AuroraLimitless:AsyncGetNextResponse wait event The process is waiting for an additional responses when pipeline mode is used in query execution. ### Causes This will typically be seen during router to shard execution for queries with single-shard data placement and is an expected part of normal execution. ### Action No further action is required. ## AuroraLimitless:AbortedCommandCleanup wait event The process is waiting for the result of a remote cleanup query. Cleanup queries are issued to shard nodes to return them to an appropriate state when a distributed transaction is ended. ### Causes Transaction cleanup is done when a transaction is aborted either because an error was found or because an user issued an explicit ABORT command or canceled the running query. ### Action Investigate the cause for the transaction being canceled. ## AuroraLimitless:DistributedCommitPrepare wait event The process is committing a distributed transaction and is waiting for all the participants to acknowledge the prepare command. ### Causes Transactions that modify multiple nodes must perform a distributed commit. A long wait in `DistributedCommitPrepare` could be caused by long waits in the `IO:TwophaseFilePoolWrite` event on participating nodes. ### Action Reduce the number of transactions that modify data on multiple nodes. Investigate `IO:TwophaseFilePoolWrite` events in other nodes of the cluster. ## AuroraLimitless:DistributedCommit wait event The process is committing a distributed transaction and is waiting for the lead participant to acknowledge the commit command. ### Causes Transactions that modify multiple nodes must perform a distributed commit. A long wait in `DistributedCommit` could be caused by long waits in the `IO:XactSync` event on the lead participant. ### Action Reduce the number of transactions that modify data on multiple nodes. Investigate `IO:XactSync` events in other nodes of the cluster. ## AuroraLimitless:DistributedCommitPrepareThrottle wait event The process is attempting to prepare a distributed transaction and is being throttled to due to existing prepared transactions. ### Causes Transactions that modify multiple nodes must perform a distributed commit. Participants in these transactions must perform a prepare operation as part of the commit protocol. Aurora limits the number of concurrent prepares, and if this limit is exceeded the process will wait in the `DistributedCommitPrepareThrottle` event. ### Action Reduce the number of transactions that modify data on multiple nodes. Investigate `IO:TwophaseFilePoolWrite` events as increased time in those events could cause existing prepared transactions to build up, resulting in throttling for new prepare attempts. ## AuroraLimitless:PreparedTransactionResolution wait event The process has encountered a tuple modified by a distributed transaction that is in the prepared state. The process must determine if the distributed transaction will become visible in its snapshot. ### Causes Transactions that modify multiple nodes must perform a distributed commit which includes a prepare phase. A high number of distributed transactions or increased latency on distributed commits can cause other processes to encounter the `PreparedTransactionResolution` wait event. ### Action Reduce the number of transactions that modify data on multiple nodes. Investigate distributed commit related events as increased time in those events could increase latency in the commit path of distributed transactions. You might also wish to investigate network and CPU loads. ## AuroraLimitless:SendPreparedTransactionOutcome wait event The process is executing on a node that is coordinating a distributed transaction and another process has inquired as to the state of that transaction, or the process has committed a distributed transaction and is sending the outcome to participants. ### Causes Processes that encounter the `PreparedTransactionResolution` wait event will query the transaction coordinator. The response on the transaction coordinator will ecounter SendPreparedTransactionOutcome. ### Action Reduce the number of transactions that modify data on multiple nodes. Investigate distributed commit related events and `IO:TwophaseFilePoolWrite` and `IO:TwophaseFilePoolRead` events as those events could increase latency in the commit path of distributed transactions. You might also wish to investigate network and CPU loads. ## AuroraLimitless:CommitClockBarrier wait event The process is committing a transaction and must wait to ensure that the assigned commit time is guaranteed to be in the past for all nodes in the cluster. ### Causes CPU or network staturation could cause increased clock drift, resulting in time spent in this wait event. ### Action Investigate CPU or network saturation in your cluster. ## AuroraLimitless:SnapshotClockBarrier wait event The process has received a snapshot time from another node with a clock in the future and is waiting for its own clock to reach that time. ### Causes This typically occurs after the process has received results from a function that was pushed down to a shard and there is clock drift between the nodes. CPU or network staturation could cause increased clock drift, resulting in time spent in this wait event. ### Action Investigate CPU or network staturation in your cluster. ## AuroraLimitless:ReaderSnapshotClockBarrier wait event This event occurs on read nodes. The process is waiting for the read node to replay the write stream so that all writes that happened before the process snapshot time have been applied. ### Causes An increase in Aurora replica lag can cause increased wait time in this event. ### Action Investigate Aurora replica lag. ## AuroraLimitless:GatherDistributedDeadlockGraph wait event The process is communicating with other nodes to collect lock graphs as part of distributed deadlock detection. ### Causes When a process is waiting on a lock it will perform a distributed deadlock check after waiting longer than `rds_aurora.limitless_distributed_deadlock_timeout`. ### Action Investigate causes of lock contention in your application and consider tuning `rds_aurora.limitless_distributed_deadlock_timeout`. ## AuroraLimitless:DistributedDeadlockDetection wait event The process is communicating with other nodes to detect a distributed deadlock. ### Causes When a process is waiting on a lock it will perform a distributed deadlock check after waiting longer than `rds_aurora.limitless_distributed_deadlock_timeout`. ### Action Investigate causes of lock contention in your application and consider tuning `rds_aurora.limitless_distributed_deadlock_timeout`. ## AuroraLimitless:DistributedDeadlockAbort wait event The process is communicating with another node to abort a session chosen as the victim in a distributed deadlock. ### Causes Application patterns are resulting in distributed deadlocks. ### Action Investigate application patterns resulting in distributed deadlocks. ## AuroraLimitless:GatherRemoteStats wait event The process is gathering statistics from other nodes in the cluster. ### Causes Monitoring or activty queries and views, such as `limitless_stat_activity`, will retrieve statistics from other nodes. ### Action No further action is required. ## AuroraLimitless:GlobalSequenceRefresh wait event The process is generating a new sequence value and must request a new chunk from the global sequence. ### Causes A high rate of sequence value generation can result in stalls in this event if `rds_aurora.limitless_sequence_chunk_size` is insufficient. ### Action This is a normal occurrence. If you see excessive time in this event consider tuning `rds_aurora.limitless_sequence_chunk_size`. See documentation on sequences in Limitless Database. ## AuroraLimitless:GlobalVacuumTimeExchange wait event The process is exchanging snapshot data to support vacuum. ### Causes Nodes in Limitless Database exchange oldest active snapshot time data with other nodes to compute the correct cutoff time for vacuum execution. ### Action No further action is required. ## AuroraLimitless:DistributedTransactionMonitorGather wait event The process is gathering transaction metadata from other nodes to support distributed transaction cleanup. ### Causes Nodes in Limitless Database exchange transaction metadata with other nodes to determine when distributed transaction state can be purged. ### Action No further action is required. ## AuroraLimitlessActivity:AdminTaskSchedulerMain wait event Waiting in main loop of task scheduler process. ## AuroraLimitlessActivity:AdminTaskExecutorMain wait event Waiting in main loop of task executor process. ## AuroraLimitlessActivity:AdminTaskMonitorMain wait event Waiting in main loop of task monitor process. ## AuroraLimitlessActivity:DatabaseCleanupMonitorMain wait event Waiting in main loop of database cleanup monitor process. ## AuroraLimitlessActivity:TopologyCleanupMonitorMain wait event Waiting in main loop of topology cleanup monitor process. ## AuroraLimitlessActivity:ToplogyChangeMonitorMain wait event Waiting in main loop of topology change monitor process. ## AuroraLimitlessActivity:DistributedTransactionMonitorMain wait event Waiting in main loop of distributed transaction monitor process. ## AuroraLimitlessActivity:GlobalVacuumMonitorMain wait event Waiting in main loop of global vacuum monitor process. # Building for efficiency with functions Building for efficiency with functions User-defined functions are not single-shard optimized by default, but they can be configured to execute as single-shard operations. Functions can encapsulate logic and ensure it is executed in a single-shard optimized manner. ## Why single-shard operations are important Resource utilization is important for performance and cost efficiency. Single-shard operations use significantly fewer resources compared to cross-shard operations. For example, when executing a function to insert one million rows, single-shard execution uses approximately 90.5 ACUs compared to 126.5 ACUs for cross-shard execution—a 35% improvement in resource efficiency. Single-shard execution also provides: + 35% higher throughput than cross-shard operations + More predictable response times + Better scalability as data grows ## Single-shard operations and functions Functions execute on shards when either of these prerequisites are met: + The function is created as immutable and included in a single-shard optimized query + The function is distributed by a user Functions that execute on shards perform and scale better because they execute where the data is located. ## Functions and volatility To check a function's volatility, use this query on PostgreSQL's system tables: ``` SELECT DISTINCT nspname, proname, provolatile FROM pg_proc PRO JOIN pg_namespace NSP ON PRO.pronamespace = NSP.oid WHERE proname IN ('random', 'md5'); ``` Example output: ``` nspname | proname | provolatile ------------+---------+------------- pg_catalog | md5 | i pg_catalog | random | v (2 rows) ``` In this example, `md5()` is immutable and `random()` is volatile. This means that a single-shard optimized statement that includes `md5()` remains single-shard optimized, while a statement that includes `random()` does not. Example with immutable function: ``` EXPLAIN ANALYZE SELECT pg_catalog.md5('123') FROM s1.t1 WHERE col_a = 776586194 AND col_b = 654849524 AND col_c = '3ac2f2affb02987159ccd6ebd23e1ae5'; ``` ``` QUERY PLAN ---------------------------------------------------- Foreign Scan (cost=100.00..101.00 rows=100 width=0) (actual time=3.409..3.409 rows=1 loops=1) Single Shard Optimized Planning Time: 0.313 ms Execution Time: 4.253 ms (4 rows) ``` Example with volatile function: ``` EXPLAIN ANALYZE SELECT pg_catalog.random() FROM s1.t1 WHERE col_a = 776586194 AND col_b = 654849524 AND col_c = '3ac2f2affb02987159ccd6ebd23e1ae5'; ``` ``` QUERY PLAN ------------------------------------------------------ Foreign Scan on t1_fs00001 t1 (cost=100.00..15905.15 rows=1 width=8) (actual time=0.658..0.658 rows=1 loops=1) Planning Time: 0.263 ms Execution Time: 2.892 ms (3 rows) ``` The output shows that `md5()` is pushed down and executed as single-shard optimized, while `random()` is not. ## Distributing functions A function that accesses data on only one shard should execute on that shard to gain performance benefits. The function must be distributed, and the function signature must include the complete shard key—all columns in the shard key must be passed as parameters to the function. Example function: ``` CREATE OR REPLACE FUNCTION s1.func1( param_a bigint, param_b bigint, param_c char(100) ) RETURNS int AS $$ DECLARE res int; BEGIN SELECT COUNT(*) INTO res FROM s1.t1 WHERE s1.t1.col_a = param_a AND s1.t1.col_b = param_b AND s1.t1.col_c = param_c; RETURN res; END $$ LANGUAGE plpgsql; ``` Before distribution, the function is not single-shard optimized: ``` EXPLAIN ANALYZE SELECT * FROM s1.func1(776586194, 654849524, '3ac2f2affb02987159ccd6ebd23e1ae5'); ``` ``` QUERY PLAN ------------------------------------------------------------------------------------------------------ Function Scan on func1 (cost=0.25..0.26 rows=1 width=4) (actual time=37.503..37.503 rows=1 loops=1) Planning Time: 0.901 ms Execution Time: 51.647 ms (3 rows) ``` To distribute the function: ``` SELECT rds_aurora.limitless_distribute_function( 's1.func1(bigint,bigint,character)', ARRAY['param_a','param_b','param_c'], 's1.t1' ); ``` After distribution, the function is single-shard optimized: ``` EXPLAIN ANALYZE SELECT * FROM s1.func1(776586194, 654849524, '3ac2f2affb02987159ccd6ebd23e1ae5'); ``` ``` QUERY PLAN ------------------------------------------------------------------------------------------------ Foreign Scan (cost=100.00..101.00 rows=100 width=0) (actual time=4.332..4.333 rows=1 loops=1) Single Shard Optimized Planning Time: 0.857 ms Execution Time: 5.116 ms (4 rows) ``` You can confirm single-shard optimization by checking the `sso_calls` column in `rds_aurora.limitless_stat_statements`: ``` subcluster_id | subcluster_type | calls | sso_calls | query --------------+-----------------+-------+-----------+-------------------------------------- 2 | router | 2 | 1 | SELECT * FROM s1.func1( $1, $2, $3 ) 3 | router | 1 | 1 | SELECT * FROM s1.func1( $1, $2, $3 ) (2 rows) ``` ## Functions and efficiency patterns Executing logic close to the data is more efficient, and functions play a key role in achieving this. There are two main use cases for improving efficiency with functions: 1. Extracting shard key from complex data to invoke a separate single-shard optimized function 1. Turning cross-shard workloads into single-shard optimized by separating cross-shard logic from single-shard optimized statements ### Extracting shard key from complex data Consider a function with signature `s3.func3(p_json_doc json)` that performs several database operations. These operations will execute across all shards within a transaction that spans all shards. If the JSON document contains the shard key, you can build a single-shard optimized function to perform the database operations. Original pattern: ``` s3.func3(p_json_doc json) database operation 1; database operation 2; database operation 3; ``` Optimized pattern: ``` s3.func3(p_json_doc json) DECLARE v_a bigint; BEGIN v_a := (p_json_doc->>'field_a')::bigint; SELECT s3.func3_INNER(v_a, p_json_doc); END; ``` Where the inner function does: ``` s3.func3_INNER(p_a, p_json_doc) database operation 1 WHERE shard_key = p_a; database operation 2 WHERE shard_key = p_a; database operation 3 WHERE shard_key = p_a; ``` In this pattern, the shard key is encapsulated in a complex datatype or deducible from other parameters. Logic, data access, and functions can determine, extract, or construct the shard key, then invoke a single-shard optimized function that performs operations concerning only a single shard. Since the application interface doesn't change, optimization is comparatively easy to test. ### Deferring shard key from other functions or data Another design pattern applies when logic or data access calculates or determines the shard key. This is useful when a function can be executed on a single shard for most invocations, but occasionally requires cross-shard execution. Original pattern: ``` NEWORD(INTEGER, …) RETURNS NUMERIC DECLARE all_whid_local := true; LOOP through the order lines Generate warehouse ID; IF generated warehouse ID == input warehouse ID THEN ol_supply_whid := input warehouse ID; ELSE all_whid_local := false; ol_supply_whid := generated warehouse ID; END IF; … END LOOP; … RETURN no_s_quantity; ``` Optimized pattern with separate functions: ``` CREATE OR REPLACE FUNCTION NEWORD_sso(no_w_id INTEGER, …) RETURNS NUMERIC … RETURN no_s_quantity; … END; LANGUAGE 'plpgsql'; SELECT rds_aurora.limitless_distribute_function( 'NEWORD_sso(int,…)', ARRAY['no_w_id'], 'warehouse' ); CREATE OR REPLACE FUNCTION NEWORD_crosshard(no_w_id INTEGER, …) RETURNS NUMERIC … RETURN no_s_quantity; … END; LANGUAGE 'plpgsql'; ``` Then have the main function call either the single-shard optimized or cross-shard version: ``` IF all_whid_local THEN SELECT NEWORD_sso(…) INTO no_s_quantity; ELSE SELECT NEWORD_crosshard(…) INTO no_s_quantity; END IF; ``` This approach allows the majority of invocations to benefit from single-shard optimization while maintaining correct behavior for cases that require cross-shard execution. ## Checking for single-shard operations Use `EXPLAIN` to verify whether a statement is single-shard optimized. The output explicitly reports "Single Shard Optimized" for optimized operations. Cross-shard invocation before distribution: ``` QUERY PLAN --------------------------------------------------------------------- Function Scan on func1 (cost=0.25..0.26 rows=1 width=4) (actual time=59.622..59.623 rows=1 loops=1) Planning Time: 0.925 ms Execution Time: 60.211 ms ``` Single-shard invocation after distribution: ``` QUERY PLAN ---------------------------------------------------------------------- Foreign Scan (cost=100.00..101.00 rows=100 width=0) (actual time=4.576..4.577 rows=1 loops=1) Single Shard Optimized Planning Time: 1.483 ms Execution Time: 5.404 ms ``` The difference in execution times demonstrates the performance benefit of single-shard optimization.