# Using Amazon Aurora PostgreSQL Limitless Database Amazon Aurora PostgreSQL Limitless Database provides automated horizontal scaling to process millions of write transactions per second and manages petabytes of data while maintaining the simplicity of operating inside a single database. With Aurora PostgreSQL Limitless Database, you can focus on building high-scale applications without having to build and maintain complex solutions for scaling your data across multiple DB instances to support your workloads. **Topics** + [ # Aurora PostgreSQL Limitless Database architecture ](limitless-architecture.md) + [ # Getting started with Aurora PostgreSQL Limitless Database ](limitless-getting-started.md) + [ # Aurora PostgreSQL Limitless Database requirements and considerations ](limitless-reqs-limits.md) + [ # Prerequisites for using Aurora PostgreSQL Limitless Database ](limitless-prereqs.md) + [ # Creating a DB cluster that uses Aurora PostgreSQL Limitless Database ](limitless-cluster.md) + [ # Working with DB shard groups ](limitless-shard.md) + [ # Creating Aurora PostgreSQL Limitless Database tables ](limitless-creating.md) + [ # Loading data into Aurora PostgreSQL Limitless Database ](limitless-load.md) + [ # Querying Aurora PostgreSQL Limitless Database ](limitless-query.md) + [ # Managing Aurora PostgreSQL Limitless Database ](limitless-managing.md) + [ # Monitoring Aurora PostgreSQL Limitless Database ](limitless-monitoring.md) + [ # Instance-specific performance and resource monitoring ](limitless-instance-monitoring.md) + [ # Backing up and restoring Aurora PostgreSQL Limitless Database ](limitless-bak.md) + [ # Upgrading Amazon Aurora PostgreSQL Limitless Database ](limitless-upg.md) + [ # Aurora PostgreSQL Limitless Database reference ](limitless-reference.md) # Aurora PostgreSQL Limitless Database architecture Limitless Database achieves scale with a two-layer architecture consisting of multiple database nodes. Nodes are either routers or shards. + Shards are Aurora PostgreSQL DB instances that each store a subset of the data for your database, allowing for simultaneous processing to achieve higher write throughput. + Routers manage the distributed nature of the database and present a single database image to database clients. Routers maintain metadata about where data is stored, parse incoming SQL commands and send those commands to shards. Then they aggregate data from shards to return a single result to the client, and manage distributed transactions to maintain consistency across the entire distributed database. Aurora PostgreSQL Limitless Database differs from standard [Aurora DB clusters](Aurora.Overview.md) by having a DB shard group instead of a writer DB instance and reader DB instances. All of the nodes that make up your Limitless Database architecture are contained in the DB shard group. The individual shards and routers in the DB shard group aren't visible in your AWS account. You use the DB cluster endpoint to access Limitless Database. The following figure shows the high-level architecture of Aurora PostgreSQL Limitless Database. ![\[High-level architecture of Aurora PostgreSQL Limitless Database showing primary cluster, shard groups, and data distribution.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_high_level_arch_GA.png) For more information on the architecture of Aurora PostgreSQL Limitless Database and how you can use it, see this video on the AWS Events channel on YouTube: [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/pUqVCK7Ggh0/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/pUqVCK7Ggh0) For more information on the architecture of a standard Aurora DB cluster, see [Amazon Aurora DB clusters](Aurora.Overview.md). ## Key terms for Aurora PostgreSQL Limitless Database **DB shard group** A container for Limitless Database nodes (shards and routers). **Router** A node that accepts SQL connections from clients, sends SQL commands to shards, maintains system-wide consistency, and returns results to clients. **Shard** A node that stores a subset of sharded tables, full copies of reference tables, and standard tables. Accepts queries from routers, but can't be connected to directly by the clients. **Sharded table** A table with its data partitioned across shards. **Shard key** A column or set of columns in a sharded table that's used to determine partitioning across shards. **Collocated tables** Two sharded tables that share the same shard key and are explicitly declared as collocated. All data for the same shard key value is sent to the same shard. **Reference table** A table with its data copied in full on every shard. **Standard table** The default table type in Limitless Database. You can convert standard tables into sharded and reference tables. All standard tables are stored on the same shard selected by the system, enabling joins between standard tables to be performed within a single shard. However, standard tables are limited by the shard's maximum capacity (128 TiB). This shard also stores data from sharded and reference tables, so the effective limit for standard tables is lower than 128 TiB. ## Table types for Aurora PostgreSQL Limitless Database Aurora PostgreSQL Limitless Database supports three types of table: *sharded*, *reference*, and *standard*. Sharded tables have their data distributed across all of the shards in the DB shard group. Limitless Database does this automatically by using a *shard key*, which is a column or set of columns that you specify when partitioning the table. All of the data with the same value for the shard key is sent to the same shard. Sharding is hash-based, not range- or list-based. The following are good use cases for sharded tables: + The application works with a distinct subset of data. + The table is very large. + The table potentially grows faster than other tables. Sharded tables can be *collocated*, meaning that they share the same shard key, and that all of the data from both tables with the same shard key value is sent to the same shard. If you collocate tables and join them using the shard key, the join can be performed on a single shard because all of the necessary data is present on that shard. Reference tables have a full copy of all their data on every shard in the DB shard group. Reference tables are commonly used for smaller tables with a lower write volume, but that still need to be joined frequently and don't lend themselves to sharding. Examples of reference tables include date tables, and tables of geographic data such as state, city, and postal code. Standard tables are the default table type in Aurora PostgreSQL Limitless Database. They aren't distributed tables. Aurora PostgreSQL Limitless Database supports joins between standard tables and standard, sharded, and reference tables. ## Billing for Aurora PostgreSQL Limitless Database For information on how you're charged for Aurora PostgreSQL Limitless Database, see [ DB instance billing for Aurora](User_DBInstanceBilling.md). For Aurora pricing information, see the [Aurora pricing page](https://aws.amazon.com/rds/aurora/pricing). # Getting started with Aurora PostgreSQL Limitless Database You perform the following actions to get started with Aurora PostgreSQL Limitless Database: 1. Create an Aurora PostgreSQL DB cluster and a DB shard group for Limitless Database. For more information, see [Creating a DB cluster that uses Aurora PostgreSQL Limitless Database](limitless-cluster.md). 1. Create sharded and reference tables in the DB shard group. For more information, see [Creating Aurora PostgreSQL Limitless Database tables](limitless-creating.md). 1. Change the capacity for your DB shard group, split shards, and add routers. For more information, see [Working with DB shard groups](limitless-shard.md). 1. Load data into the DB shard group. For more information, see [Loading data into Aurora PostgreSQL Limitless Database](limitless-load.md). 1. Run queries and other SQL statements on the DB shard group. For more information, see [Querying Aurora PostgreSQL Limitless Database](limitless-query.md). 1. Monitor the performance of Limitless Database. For more information, see [Monitoring Aurora PostgreSQL Limitless Database](limitless-monitoring.md). # Aurora PostgreSQL Limitless Database requirements and considerations Aurora PostgreSQL Limitless Database has the following requirements and considerations. **Topics** + [ ## Requirements for Aurora PostgreSQL Limitless Database ](#limitless-requirements) + [ ## Considerations for Aurora PostgreSQL Limitless Database ](#limitless-limitations) + [ ## Features not supported in Aurora PostgreSQL Limitless Database ](#limitless-not-supported) ## Requirements for Aurora PostgreSQL Limitless Database Make sure to follow these requirements for Aurora PostgreSQL Limitless Database. + Aurora PostgreSQL Limitless Database is available in all AWS Regions except Asia Pacific (Taipei). **Note** If you create your Aurora PostgreSQL Limitless Database DB cluster in US East (N. Virginia), don't include the `us-east-1e` Availability Zone (AZ) in your DB subnet group. Because of resource limitations, Aurora Serverless v2 (and therefore Aurora PostgreSQL Limitless Database) isn't supported in the `us-east-1e` AZ. + Aurora PostgreSQL Limitless Database supports only the Aurora I/O-Optimized DB cluster storage configuration. For more information, see [Storage configurations for Amazon Aurora DB clusters](Aurora.Overview.StorageReliability.md#aurora-storage-type). + Aurora PostgreSQL Limitless Database uses special Aurora PostgreSQL DB engine versions for Aurora PostgreSQL Limitless Database: `16.X-limitless`. See also [Available Aurora PostgreSQL Limitless Database Versions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/limitless-updates.html) + Your DB cluster can't have any writer or reader DB instances. + You must use Enhanced Monitoring and Performance Insights. The Performance Insights retention time must be at least 1 month (31 days). + You must export the PostgreSQL log to Amazon CloudWatch Logs. **Note** Some required features, such as Enhanced Monitoring, Performance Insights, and CloudWatch Logs, incur extra charges. For Aurora pricing information, see the [Aurora pricing page](https://aws.amazon.com/rds/aurora/pricing). ## Considerations for Aurora PostgreSQL Limitless Database The following considerations apply to DB shard groups in Aurora PostgreSQL Limitless Database: + You can have only one DB shard group per DB cluster. + You can have up to five DB shard groups per AWS Region. Therefore, you can have up to five Aurora PostgreSQL Limitless Database DB clusters per AWS Region. For more information, see [Quotas in Amazon Aurora](CHAP_Limits.md#RDS_Limits.Limits). + You can set the maximum capacity of a DB shard group to 16–6144 ACUs. For capacity limits higher than 6144 ACUs, contact AWS. The initial number of routers and shards is determined by the maximum capacity that you set when you create a DB shard group. For more information, see [Correlating DB shard group maximum capacity with the number of routers and shards created](limitless-cluster.md#limitless-capacity-mapping). + The number of routers and shards doesn't change when you modify the maximum capacity of a DB shard group. + Make sure that the DB subnet where you create the DB shard group has enough free IP addresses for connecting with the DB shard group. You need one IP address for each router and up to three IP addresses for each shard in the DB shard group. For more information on the number of routers created when you create a DB shard group, see [Correlating DB shard group maximum capacity with the number of routers and shards created](limitless-cluster.md#limitless-capacity-mapping). + If you make your DB shard group publicly accessible, make sure to set up an internet gateway in your VPC. + You use SQL functions to [split shards](limitless-shard-split.md) and [add routers](limitless-add-router.md). + Merging shards isn't supported. + You can't delete individual shards and routers. + You can't modify (perform `UPDATE` operations on) shard keys in any way, including changing their values in table rows. To change a shard key, delete and then re-create it. + The repeatable read, read committed, and read uncommitted isolation levels are supported. You can't set the isolation level to serializable. + Some SQL commands aren't supported. For more information, see [Aurora PostgreSQL Limitless Database referenceLimitless Database reference](limitless-reference.md). + Not all PostgreSQL extensions are supported. For more information, see [Extensions](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Extensions). + When creating a shard group, or when adding new shard group nodes (shards or routers), those nodes are created in one of the Availability Zones (AZs) available to the DB cluster. You can't choose a specific AZ for individual nodes. + If you use a compute redundancy of 2 (two compute standbys for the DB shard group), make sure that your DB subnet group has at least three AZs. + Aurora PostgreSQL Limitless Database supports up to 54 characters for sharded table names. The following considerations apply to the Aurora PostgreSQL Limitless Database DB cluster: + We recommend that you use AWS managed policies to limit permissions for your database and applications to those that customers need for their use cases. For more information, see [Policy best practices](security_iam_id-based-policy-examples.md#security_iam_service-with-iam-policy-best-practices). + When you create your Aurora PostgreSQL Limitless Database DB cluster, you only set scaling parameters for the DB shard group. + If you need to delete your DB cluster, you must delete the DB shard group first. + Aurora PostgreSQL Limitless Database can't be a replication source. ## Features not supported in Aurora PostgreSQL Limitless Database The following Aurora PostgreSQL features aren't supported in Aurora PostgreSQL Limitless Database: + Active Directory (Kerberos) authentication + Amazon DevOps Guru + Amazon ElastiCache + Amazon RDS Blue/Green Deployments + Amazon RDS Proxy + Aurora Auto Scaling (adding reader instances to the DB cluster automatically) + Aurora Global Database + Aurora machine learning + Aurora recommendations + Aurora Serverless v1 + Aurora zero-ETL integrations + AWS Backup + AWS Lambda integration + AWS Secrets Manager + Babelfish for Aurora PostgreSQL + Cloning DB clusters + Custom endpoints + Database activity streams + Read replicas + RDS Data API # Prerequisites for using Aurora PostgreSQL Limitless Database To use Aurora PostgreSQL Limitless Database, you must first perform the following tasks. **Topics** + [ ## Enabling DB shard group operations ](#limitless-enable-iam) ## Enabling DB shard group operations Before you can create a DB shard group, you must enable DB shard group operations. + Add the following section to the IAM policy of the IAM role of the user that accesses Aurora PostgreSQL Limitless Database: ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "AllowDBShardGroup", "Effect": "Allow", "Action": [ "rds:CreateDBShardGroup", "rds:DescribeDBShardGroups", "rds:DeleteDBShardGroup", "rds:ModifyDBShardGroup", "rds:RebootDBShardGroup" ], "Resource": [ "arn:aws:rds:*:*:shard-group:*", "arn:aws:rds:*:*:cluster:*" ] } ] } ``` ------ # Creating a DB cluster that uses Aurora PostgreSQL Limitless Database You create a new Aurora DB cluster using the Limitless Database version of Aurora PostgreSQL, and add a DB shard group to the cluster. When adding a DB shard group, you specify the maximum compute capacity for the entire DB shard group (sum of the capacities for all routers and shards) in Aurora capacity units (ACUs). Each ACU is a combination of approximately 2 gibibytes (GiB) of memory, corresponding CPU, and networking. Scaling increases or decreases capacity for your DB shard group, depending on your application workload, similar to how [Aurora Serverless v2](aurora-serverless-v2.how-it-works.md) works. **Topics** + [ ## Correlating DB shard group maximum capacity with the number of routers and shards created ](#limitless-capacity-mapping) + [ # Creating your DB cluster ](limitless-create-cluster.md) ## Correlating DB shard group maximum capacity with the number of routers and shards created The initial number of routers and shards in a DB shard group is determined by the maximum capacity that you set when you create the DB shard group. The higher the maximum capacity, the greater the number of routers and shards that are created in the DB shard group. Each node (shard or router) has its own current capacity value, also measured in ACUs. + Limitless Database scales a node up to a higher capacity when its current capacity is too low to handle the load. However, nodes won't scale up when the total capacity is at the maximum. + Limitless Database scales the node down to a lower capacity when its current capacity is higher than needed. However, nodes won't scale down when the total capacity is at the minimum. The following table shows the correlation between the DB shard group maximum capacity in Aurora capacity units (ACUs) and the number of nodes (routers and shards) created. **Note** These values are subject to change. If you set the compute redundancy to a nonzero value, the total number of shards will be doubled or tripled. This will incur extra costs. The nodes in compute standbys are scaled up and down to the same capacity as the writer. You don't set the capacity range separately for the standbys. | Total nodes | Routers | Shards | Default minimum capacity (ACUs) | Maximum capacity range (ACUs) | | --- | --- | --- | --- | --- | | 4 | 2 | 2 | 16 | 16–400 | | 5 | 2 | 3 | 20 | 401–500 | | 6 | 2 | 4 | 24 | 501–600 | | 7 | 3 | 4 | 28 | 601–700 | | 8 | 3 | 5 | 32 | 701–800 | | 9 | 3 | 6 | 36 | 801–900 | | 10 | 4 | 6 | 40 | 901–1000 | | 11 | 4 | 7 | 44 | 1001–1100 | | 12 | 4 | 8 | 48 | 1101–1200 | | 13 | 5 | 8 | 52 | 1201–1300 | | 14 | 5 | 9 | 56 | 1301–1400 | | 15 | 5 | 10 | 60 | 1401–1500 | | 16 | 6 | 10 | 64 | 1501–1600 | | 17 | 6 | 11 | 68 | 1601–1700 | | 18 | 6 | 12 | 72 | 1701–1800 | | 19 | 7 | 12 | 76 | 1801–1900 | | 20 | 7 | 13 | 80 | 1901–2000 | | 21 | 7 | 14 | 84 | 2001–2100 | | 22 | 8 | 14 | 88 | 2101–2200 | | 23 | 8 | 15 | 92 | 2201–2300 | | 24 | 8 | 16 | 96 | 2301–6144 | Maximum capacity–based dynamic configuration for the DB shard group is available only during creation. The number of routers and shards remains the same when the maximum capacity is modified. For more information, see [Changing the capacity of a DB shard group](limitless-capacity.md). You can use SQL commands to add shards and routers to a DB shard group. For more information, see the following: + [Splitting a shard in a DB shard group](limitless-shard-split.md) + [Adding a router to a DB shard group](limitless-add-router.md) **Note** You can't delete shards or routers. # Creating your DB cluster Use the following procedures to create an Aurora PostgreSQL DB cluster that uses Aurora PostgreSQL Limitless Database. You can use either the AWS Management Console or the AWS CLI to create your DB cluster that uses Aurora PostgreSQL Limitless Database. You create the primary DB cluster and the DB shard group. ## Console When you use the AWS Management Console to create the primary DB cluster, the DB shard group is also created in the same procedure. **To create the 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 **Create database**. The **Create database** page displays. 1. For **Engine type**, choose **Aurora (PostgreSQL Compatible)**. 1. For **Version**, choose one of the following: + **Aurora PostgreSQL with Limitless Database (Compatible with PostgreSQL 16.4)** + **Aurora PostgreSQL with Limitless Database (Compatible with PostgreSQL 16.6)** 1. For **Aurora PostgreSQL Limitless Database**: ![\[Aurora PostgreSQL Limitless Database console settings with configuration options for sharding and distribution parameters.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_create_database.png) 1. Enter a **DB shard group identifier**. **Important** After you create the DB shard group, you can't change the DB cluster identifier or the DB shard group identifier. 1. For **DB shard group capacity range**: 1. Enter the **Minimum capacity (ACUs)**. Use a value of at least 16 ACUs. For a development environment, the default value is 16 ACUs. For a production environment, the default value is 24 ACUs. 1. Enter the **Maximum capacity (ACUs)**. Use a value of at least 16 ACUs or at most 6144 ACUs. For a development environment, the default value is 64 ACUs. For a production environment, the default value is 384 ACUs. For more information, see [Correlating DB shard group maximum capacity with the number of routers and shards created](limitless-cluster.md#limitless-capacity-mapping). 1. For **DB shard group deployment**, choose whether to create standbys for the DB shard group: + **No compute redundancy** – Creates a DB shard group without standbys for each shard. This is the default value. + **Compute redundancy with a single failover target** – Creates a DB shard group with one compute standby in a different Availability Zone (AZ). + **Compute redundancy with two failover targets** – Creates a DB shard group with two compute standbys in two different AZs. **Note** If you set the compute redundancy to a nonzero value, the total number of shards DB instances will be doubled or tripled. These extra DB instances are compute standbys, scaled up and down to the same capacity as the writer instance. You don't set the capacity range separately for the standbys. Therefore, ACU usage and bill correspondingly doubles and triples. To know the exact ACU usage incurred from compute redundancy, refer to the `DBShardGroupComputeRedundancyCapacity` metric in [DBShardGroup metrics](limitless-monitoring.cw.md#limitless-monitoring.cw.DBShardGroup). 1. Choose whether to make the DB shard group publicly accessible. **Note** You can't modify this setting after you create the DB shard group. 1. For **Connectivity**: 1. (Optional) Select **Connect to an EC2 compute resource**, then choose an existing EC2 instance or create a new one. **Note** If you connect to an EC2 instance, you can't make the DB shard group publicly accessible. 1. For **Network type**, choose either **IPv4** or **Dual-stack mode**. 1. Choose the **Virtual private cloud (VPC)** and **DB subnet group**, or use the default settings. **Note** If you create your Limitless Database DB cluster in the US East (N. Virginia) Region, don't include the `us-east-1e` Availability Zone (AZ) in your DB subnet group. Because of resource limitations, Aurora Serverless v2—and therefore Limitless Database—isn't supported in the `us-east-1e` AZ. 1. Choose the **VPC security group (firewall)**, or use the default setting. 1. For **Database authentication**, choose either **Password authentication** or **Password and IAM database authentication**. 1. For **Monitoring**, make sure that the **Turn on Performance Insights** and **Enable Enhanced Monitoring** check boxes are selected. For Performance Insights, choose a retention time of at least 1 month. 1. Expand the last **Additional configuration** on the page. 1. For **Log exports**, make sure that the **PostgreSQL log** check box is selected. 1. Specify other settings as needed. For more information, see [Settings for Aurora DB clusters](Aurora.CreateInstance.md#Aurora.CreateInstance.Settings). 1. Choose **Create database**. After the primary DB cluster and DB shard group are created, they're displayed on the **Databases** page. ![\[Aurora PostgreSQL Limitless Database primary DB cluster and DB shard group.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_cluster_164.png) ## CLI When you use the AWS CLI to create a DB cluster that uses Aurora PostgreSQL Limitless Database, you perform the following tasks: 1. [Create the primary DB cluster](#limitless-create-CLI.cluster). 1. [Create the DB shard group](#limitless-create-CLI.shard-group). ### Create the primary DB cluster The following parameters are required to create the DB cluster: + `--db-cluster-identifier` – The name of your DB cluster. + `--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` – You must enable Performance Insights. + `--performance-insights-retention-period` – The Performance Insights 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 parameters are optional: + `--db-subnet-group-name` – The DB subnet group to associate with the DB cluster. This also determines the VPC associated with the DB cluster. **Note** If you create your Limitless Database DB cluster in the US East (N. Virginia) Region, don't include the `us-east-1e` Availability Zone (AZ) in your DB subnet group. Because of resource limitations, Aurora Serverless v2—and therefore Limitless Database—isn't supported in the `us-east-1e` AZ. + `--vpc-security-group-ids` – A list of VPC security groups to associate with the DB cluster. + `--performance-insights-kms-key-id` – The AWS KMS key identifier for encryption of Performance Insights data. If you don't specify a KMS key, the default key for your AWS account is used. + `--region` – The AWS Region where you create the DB cluster. It must be one that supports Aurora PostgreSQL Limitless Database. To use the default VPC and VPC security group, omit the `--db-subnet-group-name` and `--vpc-security-group-ids` options. **To create the primary DB cluster** + ``` aws rds create-db-cluster \ --db-cluster-identifier my-limitless-cluster \ --engine aurora-postgresql \ --engine-version 16.6-limitless \ --storage-type aurora-iopt1 \ --cluster-scalability-type limitless \ --master-username myuser \ --master-user-password mypassword \ --db-subnet-group-name mysubnetgroup \ --vpc-security-group-ids sg-c7e5b0d2 \ --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 more information, see [create-db-cluster](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/rds/create-db-cluster.html). ### Create the DB shard group Next you create the DB shard group in the DB cluster that you just created. The following parameters are required: + `--db-shard-group-identifier` – The name of your DB shard group. The DB shard group identifier has the following constraints: + It must be unique in the AWS account and AWS Region where you create it. + It must contain 1–63 letters, numbers, or hyphens. + The first character must be a letter. + It can't end with a hyphen or contain two consecutive hyphens. + **Important** After you create the DB shard group, you can't change the DB cluster identifier or the DB shard group identifier. + `--db-cluster-identifier` – The name of the DB cluster in which you're creating the DB shard group. + `--max-acu` – The maximum capacity of your DB shard group. It must be from 16–6144 ACUs. For capacity limits higher than 6144 ACUs, contact AWS. The initial number of routers and shards is determined by the maximum capacity that you set when you create the DB shard group. The higher the maximum capacity, the greater the number of routers and shards that are created in the DB shard group. For more information, see [Correlating DB shard group maximum capacity with the number of routers and shards created](limitless-cluster.md#limitless-capacity-mapping). The following parameters are optional: + `--compute-redundancy` – Whether to create standbys for the DB shard group. This parameter can have the following values: + `0` – Creates a DB shard group without standbys for each shard. This is the default value. + `1` – Creates a DB shard group with one compute standby in a different Availability Zone (AZ). + `2` – Creates a DB shard group with two compute standbys in two different AZs. **Note** If you set the compute redundancy to a nonzero value, the total number of shards will be doubled or tripled. This will incur extra costs. The nodes in compute standbys are scaled up and down to the same capacity as the writer. You don't set the capacity range separately for the standbys. + `--min-acu` – The minimum capacity of your DB shard group. It must be at least 16 ACUs, which is the default value. + `--publicly-accessible|--no-publicly-accessible` – Whether to assign publicly accessible IP addresses to the DB shard group. Access to the DB shard group is controlled by the security groups used by the cluster. The default is `--no-publicly-accessible`. **Note** You can't modify this setting after you create the DB shard group. **To create the DB shard group** + ``` aws rds create-db-shard-group \ --db-shard-group-identifier my-db-shard-group \ --db-cluster-identifier my-limitless-cluster \ --max-acu 1000 ``` # Working with DB shard groups You perform the following tasks to add and manage a DB shard group for Aurora PostgreSQL Limitless Database. **Topics** + [ ## Connecting to your Aurora PostgreSQL Limitless Database DB cluster ](#limitless-endpoint) + [ ## Finding the number of routers and shards in a DB shard group ](#limitless-shard.number) + [ ## Describing DB shard groups ](#limitless-describe) + [ ## Rebooting a DB shard group ](#limitless-reboot) + [ # Changing the capacity of a DB shard group ](limitless-capacity.md) + [ # Splitting a shard in a DB shard group ](limitless-shard-split.md) + [ # Adding a router to a DB shard group ](limitless-add-router.md) + [ # Deleting a DB shard group ](limitless-shard-delete.md) + [ # Adding a DB shard group to an existing Aurora PostgreSQL Limitless Database DB cluster ](limitless-shard-add.md) ## Connecting to your Aurora PostgreSQL Limitless Database DB cluster To work with Aurora PostgreSQL Limitless Database, you connect to the cluster writer or reader endpoint. You can use `psql` or any other connection utility that works with PostgreSQL: ``` $ psql -h DB_cluster_endpoint -p port_number -U database_username -d postgres_limitless ``` The following example uses the endpoint for the DB cluster that you created in [CLI](limitless-create-cluster.md#limitless-create-CLI). ``` $ psql -h my-limitless-cluster.cluster-ckifpdyyyxxx.us-east-1.rds.amazonaws.com -p 5432 -U postgres -d postgres_limitless ``` **Note** The default database for the DB shard group in Aurora PostgreSQL Limitless Database is `postgres_limitless`. ### Using the Limitless Connection Plugin When connecting to Aurora PostgreSQL Limitless Database, clients connect using the cluster endpoint, and are routed to a transaction router by Amazon Route 53. However, Route 53 is limited in its ability to load balance, and can allow uneven workloads on transaction routers. The [Limitless Connection Plugin](https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/using-plugins/UsingTheLimitlessConnectionPlugin.md) for the [AWS JDBC Driver](https://github.com/awslabs/aws-advanced-jdbc-wrapper) addresses this by performing client-side load balancing with load awareness. For more information on the [AWS JDBC Driver](https://github.com/awslabs/aws-advanced-jdbc-wrapper), see [Connecting to Aurora PostgreSQL with the Amazon Web Services (AWS) JDBC Driver](Aurora.Connecting.md#Aurora.Connecting.JDBCDriverPostgreSQL). ## Finding the number of routers and shards in a DB shard group You can use the following query to find the number of routers and shards: ``` SELECT * FROM rds_aurora.limitless_subclusters; subcluster_id | subcluster_type ---------------+----------------- 1 | router 2 | router 3 | shard 4 | shard 5 | shard 6 | shard ``` ## Describing DB shard groups Use the `describe-db-shard-groups` AWS CLI command to describe your DB shard groups. The following parameter is optional: + `--db-shard-group-identifier` – The name of a DB shard group. The following example describes a specific DB shard group. ``` aws rds describe-db-shard-groups --db-shard-group-identifier my-db-shard-group ``` The output resembles the following example. ``` { "DBShardGroups": [ { "DBShardGroupResourceId": "shardgroup-8986d309a93c4da1b1455add17abcdef", "DBShardGroupIdentifier": "my-shard-group", "DBClusterIdentifier": "my-limitless-cluster", "MaxACU": 1000.0, "ComputeRedundancy": 0, "Status": "available", "PubliclyAccessible": false, "Endpoint": "my-limitless-cluster.limitless-ccetp2abcdef.us-east-1.rds.amazonaws.com" } ] } ``` ## Rebooting a DB shard group Sometimes you have to reboot your DB shard group, for example when the `max_connections` parameter changes because of a maximum capacity change. You can use the AWS Management Console or AWS CLI to change the capacity of a DB shard group. ### Console Use the following procedure. 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. Navigate to the **Databases** page. 1. Select the DB shard group that you want to reboot. 1. For **Actions**, choose **Reboot**. 1. Choose **Confirm**. ### CLI To reboot a DB shard group, use the `reboot-db-shard-group` AWS CLI command with the following parameter: + `--db-shard-group-identifier` – The name of a DB shard group. The following example reboots a DB shard group. ``` aws rds reboot-db-shard-group --db-shard-group-identifier my-db-shard-group ``` # Changing the capacity of a DB shard group You can use the AWS Management Console or AWS CLI to change the capacity of a DB shard group. ## Console Use the following procedure. 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. Navigate to the **Databases** page. 1. Select the DB shard group that you want to modify. 1. For **Actions**, choose **Modify**. The **Modify DB shard group** page displays. ![\[Modify DB shard group page.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_modify_shard_group.png) 1. Enter a new **Minimum capacity (ACUs)** value, for example **100**. 1. Enter a new **Maximum capacity (ACUs)** value, for example **1000**. 1. Choose **Continue**. The confirmation page displays, with a summary of your changes. 1. Review your changes, then choose **Modify DB shard group**. ## CLI Use the `modify-db-shard-group` AWS CLI command with the following parameters: + `--db-shard-group-identifier` – The name of the DB shard group. + `--max-acu` – The new maximum capacity of the DB shard group. You can set the maximum capacity of the DB shard group to 16–6144 ACUs. For capacity limits higher than 6144 ACUs, contact AWS. The number of routers and shards doesn't change. + `--min-acu` – The new minimum capacity of your DB shard group. It must be at least 16 ACUs, which is the default value. The following CLI example changes the capacity range of a DB shard group to 100–1000 ACUs. ``` aws rds modify-db-shard-group \ --db-shard-group-identifier my-db-shard-group \ --min-acu 100 \ --max-acu 1000 ``` # Splitting a shard in a DB shard group You can split a shard in a DB shard group manually into two smaller shards. This is called a *user-initiated* shard split. Aurora PostgreSQL Limitless Database can also split shards when they have very large amounts of data or very high usage. This is called a *system-initiated* shard split. **Topics** + [ ## Prerequisites ](#limitless-shard-split.prereqs) + [ ## Splitting a shard ](#limitless-shard-split.proc) + [ ## Tracking shard splits ](#limitless-shard-split.track) + [ ## Finalizing shard splits ](#limitless-shard-split.finalize) + [ ## Canceling a shard split ](#limitless-shard-split.cancel) ## Prerequisites User-initiated shard splits have the following prerequisites: + You must have a DB shard group. + The DB shard group can't be empty: it must contain at least one sharded table. + A user must have the `rds_aurora_limitless_cluster_admin` privilege. The `rds_superuser` has this privilege; therefore the master user also has it. The `rds_superuser` can grant the privilege to other users: ``` /* Logged in as the master user or a user with rds_superuser privileges */ CREATE USER username; GRANT rds_aurora_limitless_cluster_admin to username; ``` + You must know the subcluster (node) ID of the shard that you want to split. You can obtain the ID by using the following query: ``` SELECT * FROM rds_aurora.limitless_subclusters; subcluster_id | subcluster_type ---------------+----------------- 1 | router 2 | router 3 | shard 4 | shard 5 | shard 6 | shard ``` To enable system-initiated shard splits, set the following DB cluster parameters in a custom DB cluster parameter group associated with your DB cluster: | Parameter | Value | | --- | --- | | `rds_aurora.limitless_enable_auto_scale` | `on` | | `rds_aurora.limitless_auto_scale_options` | Either `split_shard`or `add_router,split_shard` | | `rds_aurora.limitless_finalize_split_shard_mode` | This parameter determines how *system-initiated* shard splits are finalized. The value can be one of the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/limitless-shard-split.html) For more information, see [Finalizing shard splits](#limitless-shard-split.finalize). This parameter applies only to system-initiated shard splits. | For more information, see [DB cluster parameter groups for Amazon Aurora DB clusters](USER_WorkingWithDBClusterParamGroups.md). ## Splitting a shard To split a shard in a DB shard group, use the `rds_aurora.limitless_split_shard` function. This function starts a shard-split job that runs asynchronously. ``` SELECT rds_aurora.limitless_split_shard('subcluster_id'); ``` Wait for the return of a job ID upon successful submission of the job, for example: ``` SELECT rds_aurora.limitless_split_shard('3'); job_id --------------- 1691300000000 (1 row) ``` **Note** Concurrent shard split operations are not supported. Execute each operation sequentially and complete each operation before initiating another addition operation. ## Tracking shard splits You can use the job ID to track a shard-split job. To describe a particular job and get more details about it, run the following query: ``` SELECT * FROM rds_aurora.limitless_list_shard_scale_jobs(job_id); ``` For example: ``` SELECT * FROM rds_aurora.limitless_list_shard_scale_jobs(1691300000000); job_id | action | job_details | status | submission_time | message ---------------+-------------+-----------------------+---------+------------------------+------------------------------------------- 1691300000000 | SPLIT_SHARD | Split Shard 3 by User | SUCCESS | 2023-08-06 05:33:20+00 | Scaling job succeeded. + | | | | | New shard instance with ID 7 was created. (1 row) ``` The query returns an error when you pass a nonexistent job as the input. ``` SELECT * from rds_aurora.limitless_list_shard_scale_jobs(1691300000001); ERROR: no job found with the job ID provided ``` You can track the status of all shard-split jobs by using the same query without a job ID, for example: ``` SELECT * FROM rds_aurora.limitless_list_shard_scale_jobs(); job_id | action | job_details | status | submission_time | message ---------------+-------------+-----------------------+-------------+------------------------+-------------------------------------------------------------- 1691200000000 | SPLIT_SHARD | Split Shard 3 by User | IN_PROGRESS | 2023-08-05 01:46:40+00 | 1691300000000 | SPLIT_SHARD | Split Shard 4 by User | SUCCESS | 2023-08-06 05:33:20+00 | Scaling job succeeded. + | | | | | New shard instance with ID 7 was created. 1691400000000 | SPLIT_SHARD | Split Shard 5 by User | FAILED | 2023-08-07 09:20:00+00 | Error occurred for the add shard job 1691400000000. | | | | | Retry the command. If the issue persists, contact AWS Support. 1691500000000 | SPLIT_SHARD | Split Shard 5 by User | CANCELED | 2023-08-07 09:20:00+00 | Scaling job was cancelled. (4 rows) ``` The job status can be one of the following: + `IN_PROGRESS` – The shard-split job has been submitted and is in progress. You can have only one job in progress at a time. + `PENDING` – The shard-split job is waiting for you to finalize it. For more information, see [Finalizing shard splits](#limitless-shard-split.finalize). + `CANCELLATION_IN_PROGRESS` – The shard-split job is being canceled by the user. + `CANCELED` – The shard-split job has been successfully canceled by the user or by the system. + `SUCCESS` – The shard-split job completed successfully. The `message` field contains the instance ID of the new shard. + `FAILED` – The shard-split job failed. The `message` field contains the details of the failure and any actions that can be taken as a followup to the failed job. ## Finalizing shard splits Finalizing is the last step of the shard-split process. It causes some downtime. If you start a shard-split job, then finalizing happens immediately after the job completes successfully. Sometimes the system splits shards based on workload, when you've enabled system-initiated shard splits by using the `rds_aurora.limitless_enable_auto_scale` parameter. In this case, you can choose whether finalizing happens immediately, or at a time that you choose. You use the `rds_aurora.limitless_finalize_split_shard_mode` DB cluster parameter to choose when it happens: + If you set the value to `immediate`, it happens immediately. + If you set the value to `user_initiated`, you have to finalize the shard-split job manually. An RDS event is sent to you, and the status of the shard-split job is set to `PENDING`. When set to `user_initiated`, you use the `rds_aurora.limitless_finalize_split_shard` function to finalize the shard-split job: ``` SELECT * FROM rds_aurora.limitless_finalize_split_shard(job_id); ``` **Note** This function applies only to shard splits that are initiated by the system, not by you. ## Canceling a shard split You can cancel a user-initiated or system-initiated shard split that's `IN_PROGRESS` or `PENDING`. You need the job ID to cancel it. ``` SELECT * from rds_aurora.limitless_cancel_shard_scale_jobs(job_id); ``` No output is returned unless there's an error. You can track the cancellation using a job-tracking query. # Adding a router to a DB shard group You can add a router to a DB shard group. **Topics** + [ ## Prerequisites ](#limitless-add-router.prereqs) + [ ## Adding a router ](#limitless-add-router.proc) + [ ## Tracking router additions ](#limitless-add-router.track) + [ ## Canceling a router addition ](#limitless-add-router.cancel) ## Prerequisites Adding a router has the following prerequisites: + You must have a DB shard group. + A user must have the `rds_aurora_limitless_cluster_admin` privilege. The `rds_superuser` has this privilege; therefore the master user also has it. The `rds_superuser` can grant the privilege to other users: ``` /* Logged in as the master user or a user with rds_superuser privileges */ CREATE USER username; GRANT rds_aurora_limitless_cluster_admin to username; ``` **Note** If you change your AWS account's default CA certificate after the DB shard group is created, the new router will use the new CA certificate, which is different from the existing router's CA certificate. Depending on your trust store, some connections might fail. + To enable system-initiated router addition, set the following DB cluster parameters in a custom DB cluster parameter group associated with your DB cluster: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/limitless-add-router.html) For more information, see [DB cluster parameter groups for Amazon Aurora DB clusters](USER_WorkingWithDBClusterParamGroups.md). ## Adding a router To add a router, use the `rds_aurora.limitless_add_router` function. This function starts a router-addition job that runs asynchronously. ``` SELECT rds_aurora.limitless_add_router(); ``` Wait for the return of a job ID upon successful submission of the job, for example: ``` job_id --------------- 1691300000000 (1 row) ``` **Note** Concurrent router addition operations are not supported. Execute operations sequentially and complete each operation before initiating another addition operation. ## Tracking router additions You can use the job ID to track a router-addition job. To describe a particular job and get more details about it, run the following query: ``` SELECT * FROM rds_aurora.limitless_list_router_scale_jobs(job_id); ``` For example: ``` SELECT * FROM rds_aurora.limitless_list_router_scale_jobs(1691300000000); job_id | action | job_details | status | submission_time | message ---------------+------------+--------------------------+---------+------------------------+------------------------------------------- 1691300000000 | ADD_ROUTER | Add 1 new Router by User | SUCCESS | 2023-08-06 05:33:20+00 | Scaling job succeeded. + | | | | | New router instance with ID 7 was created. (1 row) ``` The query returns an error when you pass a nonexistent job as the input. ``` SELECT * from rds_aurora.limitless_list_router_scale_jobs(1691300000001); ERROR: no job found with the job ID provided ``` You can track the status of all router-addition jobs by using the same query without a job ID, for example: ``` SELECT * FROM rds_aurora.limitless_list_router_scale_jobs(); job_id | action | job_details | status | submission_time | message ---------------+------------+--------------------------+-------------+------------------------+------------------------------------------- 1691200000000 | ADD_ROUTER | Add 1 new Router by User | IN_PROGRESS | 2023-08-05 01:46:40+00 | 1691300000000 | ADD_ROUTER | Add 1 new Router by User | SUCCESS | 2023-08-06 05:33:20+00 | Scaling job succeeded. + | | | | | New router instance with ID 7 was created. 1691400000000 | ADD_ROUTER | Add 1 new Router by User | FAILED | 2023-08-07 09:20:00+00 | Error occurred for the add router job 1691400000000. | | | | | Retry the command. If the issue persists, contact AWS Support. 1691500000000 | ADD_ROUTER | Add 1 new Router by User | CANCELED | 2023-08-07 09:20:00+00 | Scaling job was cancelled. (4 rows) ``` The job status can be one of the following: + `IN_PROGRESS` – The router-addition job has been submitted and is in progress. You can have only one job in progress at a time. + `CANCELLATION_IN_PROGRESS` – The router-addition job is being canceled by the user. + `CANCELED` – The router-addition job has been successfully canceled by the user or by the system. + `SUCCESS` – The router-addition job completed successfully. The `message` field contains the instance ID of the new router. + `FAILED` – The router-addition job failed. The `message` field contains the details of the failure and any actions that can be taken as a followup to the failed job. **Note** There's no `PENDING` status because router additions don't need to be finalized. They incur no downtime. ## Canceling a router addition You can cancel a router addition that's `IN_PROGRESS`. You need the job ID to cancel it. ``` SELECT * from rds_aurora.limitless_cancel_router_scale_jobs(job_id); ``` No output is returned unless there's an error. You can track the cancellation using a job-tracking query. # Deleting a DB shard group You can delete a DB shard group if necessary. Deleting the DB shard group deletes the compute nodes (shards and routers), but not the storage. ## Console 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. Navigate to the **Databases** page. 1. Select the DB shard group that you want to delete. 1. For **Actions**, choose **Delete**. 1. Enter **delete me** in the box, then choose **Delete**. The DB shard group is deleted. ## AWS CLI To delete your DB shard group, use the `delete-db-shard-group` AWS CLI command with the following parameter: + `--db-shard-group-identifier` – The name of the DB shard group. The following example deletes a DB shard group in the Aurora PostgreSQL DB cluster that you created previously. ``` aws rds delete-db-shard-group --db-shard-group-identifier my-db-shard-group ``` # Adding a DB shard group to an existing Aurora PostgreSQL Limitless Database DB cluster You can create a DB shard group in an existing DB cluster, for example if you're restoring a DB cluster or you had deleted the DB shard group. For more information on primary DB cluster and DB shard group requirements, see [Aurora PostgreSQL Limitless Database requirements and considerationsAurora PostgreSQL Limitless Database requirements and considerations](limitless-reqs-limits.md). **Note** You can have only one DB shard group per cluster. The Limitless Database DB cluster must be in the `available` state before you can create a DB shard group. ## Console You can use the AWS Management Console to add a DB shard group to an existing DB cluster. **To add a DB shard group** 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. Navigate to the **Databases** page. 1. Select the Limitless Database DB cluster to which you want to add a DB shard group. 1. For **Actions**, choose **Add a DB shard group**. ![\[Add a DB shard group.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_add_shard_group.png) 1. Enter a **DB shard group identifier**. **Important** After you create the DB shard group, you can't change the DB cluster identifier or the DB shard group identifier. 1. Enter the **Minimum capacity (ACUs)**. Use a value of at least 16 ACUs. 1. Enter the **Maximum capacity (ACUs)**. Use a value from 16–6144 ACUs. For more information, see [Correlating DB shard group maximum capacity with the number of routers and shards created](limitless-cluster.md#limitless-capacity-mapping). 1. For **DB shard group deployment**, choose whether to create standbys for the DB shard group: + **No compute redundancy** – Creates a DB shard group without standbys for each shard. This is the default value. + **Compute redundancy with a single failover target** – Creates a DB shard group with one compute standby in a different Availability Zone (AZ). + **Compute redundancy with two failover targets** – Creates a DB shard group with two compute standbys in two different AZs. 1. Choose whether to make the DB shard group publicly accessible. **Note** You can't modify this setting after you create the DB shard group. 1. Choose **Add a DB shard group**. ## AWS CLI Use the `create-db-shard-group` AWS CLI command to create a DB shard group. The following parameters are required: + `--db-cluster-identifier` – The DB cluster to which the DB shard group belongs. + `--db-shard-group-identifier` – The name of the DB shard group. The DB shard group identifier has the following constraints: + It must be unique in the AWS account and AWS Region where you create it. + It must contain 1–63 letters, numbers, or hyphens. + The first character must be a letter. + It can't end with a hyphen or contain two consecutive hyphens. **Important** After you create the DB shard group, you can't change the DB cluster identifier or the DB shard group identifier. + `--max-acu` – The maximum capacity of the DB shard group. Use a value from 16–6144 ACUs. The following parameters are optional: + `--compute-redundancy` – Whether to create standbys for the DB shard group. This parameter can have the following values: + `0` – Creates a DB shard group without standbys for each shard. This is the default value. + `1` – Creates a DB shard group with one compute standby in a different Availability Zone (AZ). + `2` – Creates a DB shard group with two compute standbys in two different AZs. **Note** If you set the compute redundancy to a nonzero value, the total number of nodes will be doubled or tripled. This will incur extra costs. + `--min-acu` – The minimum capacity of your DB shard group. It must be at least 16 ACUs, which is the default value. + `--publicly-accessible|--no-publicly-accessible` – Whether to assign publicly accessible IP addresses to the DB shard group. Access to the DB shard group is controlled by the security groups used by the cluster. The default is `--no-publicly-accessible`. **Note** You can't modify this setting after you create the DB shard group. The following example creates a DB shard group in an Aurora PostgreSQL DB cluster. ``` aws rds create-db-shard-group \ --db-cluster-identifier my-db-cluster \ --db-shard-group-identifier my-new-shard-group \ --max-acu 1000 ``` The output resembles the following example. ``` { "Status": "CREATING", "Endpoint": "my-db-cluster.limitless-ckifpdyyyxxx.us-east-1.rds.amazonaws.com", "PubliclyAccessible": false, "DBClusterIdentifier": "my-db-cluster", "MaxACU": 1000.0, "DBShardGroupIdentifier": "my-new-shard-group", "DBShardGroupResourceId": "shardgroup-8986d309a93c4da1b1455add17abcdef", "ComputeRedundancy": 0 } ``` # Creating Aurora PostgreSQL Limitless Database tables There are three types of tables that contain your data in Aurora PostgreSQL Limitless Database: + Standard – This is the default table type in Aurora PostgreSQL Limitless Database. You create standard tables using the [CREATE TABLE](https://www.postgresql.org/docs/current/sql-createtable.html) command, and can run Data Description Language (DDL) and Data Manipulation Language (DML) operations on them. Standard tables aren't distributed tables. They're stored on one of the shards chosen internally by the system. + Sharded – These tables are distributed across multiple shards. Data is split among the shards based on the values of designated columns in the table. This set of columns is called a shard key. + Reference – These tables are replicated on all shards. They're used for infrequently modified reference data, such as product catalogs and zip codes. Join queries between reference and sharded tables can be run on shards, eliminating unnecessary data movement between shards and routers. There are two ways to create limitless tables: + [Creating limitless tables by using variables](limitless-creating-config.md) – Use this method when you want to create new sharded and reference tables. + [Converting standard tables to limitless tables](limitless-converting-standard.md) – Use this method when you want to convert existing standard tables into sharded and reference tables. We also provide [sample schemas](limitless-sample-schemas.md) for Aurora PostgreSQL Limitless Database. # Creating limitless tables by using variables You can use variables to create sharded and reference tables by setting the table creation mode. Then the tables that you create will use this mode until you set a different mode. Use the following variables to create sharded and reference tables: + `rds_aurora.limitless_create_table_mode` – Set this session variable to `sharded` or `reference`. The default value of this variable is `standard`. + `rds_aurora.limitless_create_table_shard_key` – Set this session variable to an array of column names to use as shard keys. This variable is ignored when `rds_aurora.limitless_create_table_mode` isn't `sharded`. Format the value as `untyped array literal`, similar to when you insert literals into an array column. For more information, see [Arrays](https://www.postgresql.org/docs/current/arrays.html) in the PostgreSQL documentation. + `rds_aurora.limitless_create_table_collocate_with` – Set this session variable to a specific table name to collocate newly created tables with that table. If two or more tables are sharded using the same shard key, you can explicitly align (collocate) those tables. When two or more tables are collocated, rows from those tables with the same shard key values are placed on the same shard. Collocation helps to restrict some operations to a single shard, which results in better performance. **Note** All primary and unique keys must include the shard key. This means that the shard key is a subset of the primary or unique key. Limitless tables have some limitations. For more information, see [DDL limitations and other information for Aurora PostgreSQL Limitless Database](limitless-reference.DDL-limitations.md). **Topics** + [ ## Examples using variables to create limitless tables ](#limitless-tables-examples) + [ ## Aurora PostgreSQL Limitless Database table views ](#limitless-table-views) ## Examples using variables to create limitless tables The following examples show how to use these variables to create sharded and reference tables. Create a sharded table named `items`, with the shard key `id`. ``` BEGIN; SET LOCAL rds_aurora.limitless_create_table_mode='sharded'; SET LOCAL rds_aurora.limitless_create_table_shard_key='{"id"}'; CREATE TABLE items(id int, val int, item text); COMMIT; ``` Create a sharded table named `items`, with a shard key composed of the `item_id` and `item_cat` columns. ``` BEGIN; SET LOCAL rds_aurora.limitless_create_table_mode='sharded'; SET LOCAL rds_aurora.limitless_create_table_shard_key='{"item_id", "item_cat"}'; CREATE TABLE items(item_id int, item_cat varchar, val int, item text); COMMIT; ``` Create a sharded table named `item_description`, with a shard key composed of the `item_id` and `item_cat` columns, and collocate it with the `items` table from the previous example. ``` BEGIN; SET LOCAL rds_aurora.limitless_create_table_mode='sharded'; SET LOCAL rds_aurora.limitless_create_table_shard_key='{"item_id", "item_cat"}'; SET LOCAL rds_aurora.limitless_create_table_collocate_with='items'; CREATE TABLE item_description(item_id int, item_cat varchar, color_id int); COMMIT; ``` Create a reference table named `colors`. ``` BEGIN; SET LOCAL rds_aurora.limitless_create_table_mode='reference'; CREATE TABLE colors(color_id int primary key, color varchar); COMMIT; ``` To reset the `rds_aurora.limitless_create_table_mode` session variable to `standard`, use the following statement: ``` RESET rds_aurora.limitless_create_table_mode; ``` After you reset this variable, tables are created as standard tables, which is the default. For more information on standard tables, see [Converting standard tables to limitless tables](limitless-converting-standard.md). ## Aurora PostgreSQL Limitless Database table views You can find information about Limitless Database tables by using the following views. **rds\$1aurora.limitless\$1tables** The `rds_aurora.limitless_tables` view contains information about limitless tables and their types. ``` 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 | standard | active | standard | 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) ``` **rds\$1aurora.limitless\$1table\$1collocations** The `rds_aurora.limitless_table_collocations` view contains information about collocated sharded tables. For example, the `orders` and `customers` tables are collocated, and have the same `collocation_id`. The `users` and `followers` tables are collocated, and have the same `collocation_id`. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocations ORDER BY collocation_id; collocation_id | schema_name | table_name ----------------+-------------+------------ 16002 | public | orders 16002 | public | customers 16005 | public | users 16005 | public | followers (4 rows) ``` **rds\$1aurora.limitless\$1table\$1collocation\$1distributions** The `rds_aurora.limitless_table_collocation_distributions` 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 ----------------+---------------+----------------------+---------------------- 16002 | 6 | -9223372036854775808 | -4611686018427387904 16002 | 5 | -4611686018427387904 | 0 16002 | 4 | 0 | 4611686018427387904 16002 | 3 | 4611686018427387904 | 9223372036854775807 16005 | 6 | -9223372036854775808 | -4611686018427387904 16005 | 5 | -4611686018427387904 | 0 16005 | 4 | 0 | 4611686018427387904 16005 | 3 | 4611686018427387904 | 9223372036854775807 (8 rows) ``` # Converting standard tables to limitless tables You can convert standard tables into sharded or reference tables. During the conversion, data is moved from the standard table to the distributed table, then the source standard table is deleted. Data is moved using the `INSERT INTO SELECT FROM` command. **Contents** + [ ## Creating sharded tables ](#limitless-creating-sharded) + [ ## Creating collocated tables ](#limitless-creating-sharded.colocated) + [ ## Creating reference tables ](#limitless-creating-reference) ## Creating sharded tables You create sharded tables by running the `rds_aurora.limitless_alter_table_type_sharded` procedure on standard tables. This procedure takes a standard table and a list of columns, then distributes the given table using the list of columns as the shard key. The procedure runs synchronously, and acquires an `ACCESS EXCLUSIVE` lock on the table. After the procedure finishes successfully, the source standard table is deleted, and a sharded table with the same name becomes available. The `rds_aurora.limitless_alter_table_type_sharded` procedure uses the following syntax: ``` postgres=> CALL rds_aurora.limitless_alter_table_type_sharded('schema.table', ARRAY['shard_key1', 'shard_key2', ... 'shard_keyn']); ``` The procedure requires the following parameters: + `schema` – The database schema that contains the table to be sharded. If the schema isn't specified, the procedure uses the `search_path`. + `table` – The table to be sharded. + `shard_keyn` – An array of table columns to use as the shard key. Shard key values are string literals, and are therefore case sensitive. If a shard key contains a single quote ('), use another single quote to escape it. For example, if a table column is named `customer's id`, use `customer''s id` as the shard key. Backslashes (\$1) and double quotes (") don't need to be escaped. **Note** All primary and unique keys must include the shard key. This means that the shard key is a subset of the primary or unique key. In sharded tables, the `CHECK` constraint doesn't support expressions. For more information, see [Constraints](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Constraints). **To create a sharded table** The following example shows how to create the `customer` sharded table with the shard key `customer_id`. 1. Create the standard table. ``` CREATE TABLE customer (customer_id INT PRIMARY KEY NOT NULL, zipcode INT, email VARCHAR); ``` 1. Convert the standard table to a sharded table. ``` postgres=> CALL rds_aurora.limitless_alter_table_type_sharded('public.customer', ARRAY['customer_id']); postgres=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------------------+-------------------- public | customer | partitioned table | postgres_limitless public | customer_fs1 | foreign table | postgres_limitless public | customer_fs2 | foreign table | postgres_limitless public | customer_fs3 | foreign table | postgres_limitless public | customer_fs4 | foreign table | postgres_limitless public | customer_fs5 | foreign table | postgres_limitless (6 rows) ``` ## Creating collocated tables If two or more tables are sharded using the same shard key, you can explicitly align (collocate) those tables. When two or more tables are collocated, rows from those tables with the same shard key values are placed on the same shard. Collocation helps to restrict some operations to a single shard, which results in better performance. You use the `rds_aurora.limitless_alter_table_type_sharded` procedure with the following syntax: ``` postgres=> CALL rds_aurora.limitless_alter_table_type_sharded('schema.collocated_table', ARRAY['shard_key1', 'shard_key2', ... 'shard_keyn'], 'schema.sharded_table'); ``` The procedure requires the following parameters: + `schema` – The database schema that contains the tables to be collocated. If the schema isn't specified, the procedure uses the `search_path`. + `collocated_table` – The table to be collocated. + `shard_keyn` – An array of table columns to use as the shard key. You must use the same shard key as for the original sharded table, including the same column names and column types. + `sharded_table` – The sharded table with which you're collocating the `collocated_table`. **To create a collocated table** 1. Create the first sharded table by following the procedure in [Creating sharded tables](#limitless-creating-sharded). 1. Create the standard table for the collocated table. ``` CREATE TABLE mytable2 (customer_id INT PRIMARY KEY NOT NULL, column1 INT, column2 VARCHAR); ``` 1. Convert the standard table to a collocated table. ``` postgres=> CALL rds_aurora.limitless_alter_table_type_sharded('public.mytable2', ARRAY['customer_id'], 'public.customer'); postgres=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------------------+-------------------- public | customer | partitioned table | postgres_limitless public | customer_fs1 | foreign table | postgres_limitless public | customer_fs2 | foreign table | postgres_limitless public | customer_fs3 | foreign table | postgres_limitless public | customer_fs4 | foreign table | postgres_limitless public | customer_fs5 | foreign table | postgres_limitless public | mytable2 | partitioned table | postgres_limitless public | mytable2_fs1 | foreign table | postgres_limitless public | mytable2_fs2 | foreign table | postgres_limitless public | mytable2_fs3 | foreign table | postgres_limitless public | mytable2_fs4 | foreign table | postgres_limitless public | mytable2_fs5 | foreign table | postgres_limitless (12 rows) ``` ## Creating reference tables You create reference tables by running the `rds_aurora.limitless_alter_table_type_reference` procedure on standard tables. This procedure replicates a given table to all shards in the DB shard group, and changes the table type to reference. The procedure runs synchronously, and acquires an `ACCESS EXCLUSIVE` lock on the table. After the procedure finishes successfully, the source standard table is deleted, and a reference table with the same name becomes available. The `rds_aurora.limitless_alter_table_type_reference` procedure uses the following syntax: ``` postgres=> CALL rds_aurora.limitless_alter_table_type_reference('schema.table'); ``` The stored procedure requires the following parameters: + `schema` – The database schema that contains the table to be replicated. If the schema isn't specified, the procedure uses the `search_path`. + `table` – The table to be replicated. **Note** The standard table from which you create the reference table must have a primary key. In reference tables, the `CHECK` constraint doesn't support expressions. The previous function, `limitless_table_alter_type_reference`, is deprecated. **To create a reference table** The following example shows how to create the `zipcodes` reference table. 1. Create the standard table. ``` CREATE TABLE zipcodes (zipcode INT PRIMARY KEY, details VARCHAR); ``` 1. Convert the standard table to a reference table. ``` CALL rds_aurora.limitless_alter_table_type_reference('public.zipcodes'); postgres=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------------------+-------------------- public | customer | partitioned table | postgres_limitless public | customer_fs1 | foreign table | postgres_limitless public | customer_fs2 | foreign table | postgres_limitless public | customer_fs3 | foreign table | postgres_limitless public | customer_fs4 | foreign table | postgres_limitless public | customer_fs5 | foreign table | postgres_limitless public | zipcodes | foreign table | postgres_limitless (7 rows) ``` The output shows the `customer` sharded table and the `zipcodes` reference table. # Aurora PostgreSQL Limitless Database sample schemas We provide the following sample schemas for Aurora PostgreSQL Limitless Database: + [Limitless E-Commerce sample schema](https://github.com/aws-samples/sample-schemas-for-amazon-aurora-postgresql-limitless-database/tree/main/limitless_ec_sample_schema) + [Limitless pgbench](https://github.com/aws-samples/sample-schemas-for-amazon-aurora-postgresql-limitless-database/tree/main/limitless_pgbench) You can use these schemas to quickly create a sample database and load data into Aurora PostgreSQL Limitless Database tables. For more information, see the [GitHub repository](https://github.com/aws-samples/sample-schemas-for-amazon-aurora-postgresql-limitless-database). # Loading data into Aurora PostgreSQL Limitless Database You can load data into Aurora PostgreSQL Limitless Database tables by using the `COPY` command or by using the data loading utility. **Note** You can load data into standard, sharded, and reference tables. **Contents** + [ # Using the COPY command with Aurora PostgreSQL Limitless Database ](limitless-load.copy.md) + [ ## Using the COPY command to load data into Aurora PostgreSQL Limitless Database ](limitless-load.copy.md#limitless-load.copy-to) + [ ### Splitting data into multiple files ](limitless-load.copy.md#limitless-load.copy-split) + [ ## Using the COPY command to copy Limitless Database data to a file ](limitless-load.copy.md#limitless-load.copy-from) + [ # Using the Aurora PostgreSQL Limitless Database data loading utility ](limitless-load.utility.md) + [ ## Limitations ](limitless-load.utility.md#limitless-load.limitations) + [ ## Prerequisites ](limitless-load.utility.md#limitless-load.prereqs) + [ ## Preparing the source database ](limitless-load.utility.md#limitless-load.source) + [ ## Preparing the destination database ](limitless-load.utility.md#limitless-load.destination) + [ ## Creating database credentials ](limitless-load.utility.md#limitless-load.users) + [ ### Create the source database credentials ](limitless-load.utility.md#limitless-load.users.source) + [ ### Create the destination database credentials ](limitless-load.utility.md#limitless-load.users.destination) + [ # Setting up database authentication and resource access using a script ](limitless-load.script.md) + [ ## Setup script for the data loading utility ](limitless-load.script.md#limitless-load.script.file) + [ ## Output from the data loading utility setup script ](limitless-load.script.md#limitless-load.script.output) + [ ## Cleaning up failed resources ](limitless-load.script.md#limitless-load.script.cleanup) + [ # Setting up database authentication and resource access manually ](limitless-load.manual.md) + [ ## Creating the customer-managed AWS KMS key ](limitless-load.manual.md#limitless-load.auth.create-kms) + [ ## Creating the database secrets ](limitless-load.manual.md#limitless-load.auth.secrets) + [ ## Creating the IAM role ](limitless-load.manual.md#limitless-load.auth.iam-role) + [ ## Updating the customer-managed AWS KMS key ](limitless-load.manual.md#limitless-load.auth.update-kms) + [ ## Adding the IAM role permission policies ](limitless-load.manual.md#limitless-load.auth.iam-policy) + [ # Loading data from an Aurora PostgreSQL DB cluster or RDS for PostgreSQL DB instance ](limitless-load.data.md) + [ # Monitoring data loading ](limitless-load.monitor.md) + [ ## Listing data loading jobs ](limitless-load.monitor.md#limitless-load.monitor-list) + [ ## Viewing details of data loading jobs using the job ID ](limitless-load.monitor.md#limitless-load.monitor-describe) + [ ## Monitoring the Amazon CloudWatch log group ](limitless-load.monitor.md#limitless-load.monitor-cwl) + [ ## Monitoring RDS events ](limitless-load.monitor.md#limitless-load.monitor-events) + [ # Canceling data loading ](limitless-load.cancel.md) # Using the COPY command with Aurora PostgreSQL Limitless Database You can use the [\$1copy](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-COPY) functionality in the `psql` utility for importing data into and exporting data from Aurora PostgreSQL Limitless Database ## Using the COPY command to load data into Aurora PostgreSQL Limitless Database Aurora PostgreSQL Limitless Database is compatible with the [\$1copy](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-COPY) functionality in the `psql` utility for importing data. In Limitless Database as in Aurora PostgreSQL, the following aren't supported: + Direct SSH access to DB instances – You can't copy a data file (such as in .csv format) to the DB instance host and run `COPY` from the file. + Using local files on the DB instance – Use `COPY ... FROM STDIN` and `COPY ... TO STDOUT`. The `COPY` command in PostgreSQL has options for working with local files (`FROM/TO`) and transmitting data using a connection between the client and the server (`STDIN/STDOUT`). For more information, see [COPY](https://www.postgresql.org/docs/current/sql-copy.html) in the PostgreSQL documentation. The `\copy` command in the PostgreSQL `psql` utility works with local files on the computer where you run the `psql` client. It invokes the respective `COPY ... FROM STDIN` or `COPY ... FROM STDOUT` command on the remote (for example, Limitless Database) server to which you connect. It reads data from the local file to `STDIN` or writes to it from `STDOUT`. ### Splitting data into multiple files Data is stored on multiple shards in Aurora PostgreSQL Limitless Database. To speed up data loading using `\copy`, you can split your data into multiple files. Then import independently for each data file by running separate `\copy` commands in parallel. For example, you have an input data file in CSV format with 3 million rows to import. You can split the file into chunks each holding 200,000 rows (15 chunks): ``` split -l200000 data.csv data_ --additional-suffix=.csv -d ``` This results in files `data_00.csv` through `data_14.csv`. You can then import data using 15 parallel `\copy` commands, for example: ``` psql -h dbcluster.limitless-111122223333.aws-region.rds.amazonaws.com -U username -c "\copy test_table from '/tmp/data_00.csv';" postgres_limitless & psql -h dbcluster.limitless-111122223333.aws-region.rds.amazonaws.com -U username -c "\copy test_table FROM '/tmp/data_01.csv';" postgres_limitless & ... psql -h dbcluster.limitless-111122223333.aws-region.rds.amazonaws.com -U username -c "\copy test_table FROM '/tmp/data_13.csv';" postgres_limitless & psql -h dbcluster.limitless-111122223333.aws-region.rds.amazonaws.com -U username -c "\copy test_table FROM '/tmp/data_14.csv';" postgres_limitless ``` Using this technique, the same amount of data is imported approximately 10 times faster than using a single `\copy` command. ## Using the COPY command to copy Limitless Database data to a file You can use the [\$1copy](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-COPY) command to copy data from a limitless table to a file, as shown in the following example: ``` postgres_limitless=> \copy test_table TO '/tmp/test_table.csv' DELIMITER ',' CSV HEADER; ``` # Using the Aurora PostgreSQL Limitless Database data loading utility Aurora provides a utility for loading data directly into Limitless Database from an Aurora PostgreSQL DB cluster or RDS for PostgreSQL DB instance. You perform the following steps to use the data loading utility: 1. [Prerequisites](#limitless-load.prereqs) 1. [Preparing the source database](#limitless-load.source) 1. [Preparing the destination database](#limitless-load.destination) 1. [Creating database credentials](#limitless-load.users) 1. One of the following: + [Setting up database authentication and resource access using a script](limitless-load.script.md) (recommended) + [Setting up database authentication and resource access manually](limitless-load.manual.md) 1. [Loading data from an Aurora PostgreSQL DB cluster or RDS for PostgreSQL DB instance](limitless-load.data.md) ## Limitations The data loading utility has the following limitations: + The following data types aren't supported: `enum`, `ARRAY`, `BOX`, `CIRCLE`, `LINE`, `LSEG`, `PATH`, `PG_LSN`, `PG_SNAPSHOT`, `POLYGON`, `TSQUERY`, `TSVECTOR`, and `TXID_SNAPSHOT`. + Leading zeroes (`0`) are stripped from the `VARBIT` data type during loading. + Data migration fails when there are foreign keys on the destination tables. + Limitless Data Utility supports the following source configurations for Amazon RDS for PostgreSQL Multi-AZ DB clusters: + Primary instance + Supported modes: snapshot, snapshot\$1then\$1cdc + Replica instance + Supported mode: snapshot only + Requirement: hot\$1standby\$1feedback must be enabled + Not supported: snapshot\$1then\$1cdc ## Prerequisites The data loading utility has the following prerequisites: + The source database uses Aurora PostgreSQL or RDS for PostgreSQL version 11.x and higher. + The source database is in the same AWS account and AWS Region as the destination DB shard group. + The source DB cluster or DB instance is in the `available` state. + Tables on the source database and limitless database have the same table names, column names, and column data types. + The source and destination tables have primary keys that use the same columns and column orders. + You must have an environment for connecting to a limitless database to run data loading commands. Available commands are the following: + `rds_aurora.limitless_data_load_start` + `rds_aurora.limitless_data_load_cancel` + For CDC: + Both the source database and the destination DB shard group must use the same DB subnet group, VPC security group, and database port. These setups are for network connections to both the source database and the routers in the DB shard group. + You must enable logical replication on the source database. The source database user must have privileges to read logical replication. ## Preparing the source database To access the source database for data loading, you must allow incoming network traffic to it. Perform the following steps. **To allow network traffic to the source database** 1. Sign in to the AWS Management Console and open the Amazon EC2 console at [https://console.aws.amazon.com/ec2/](https://console.aws.amazon.com/ec2/). 1. Navigate to the **Security groups** page. 1. Choose the **Security group ID** for the security group used by the source DB cluster or instance. For example, its security group ID is `sg-056a84f1712b77926`. 1. On the **Inbound rules** tab: 1. Choose **Edit inbound rules**. 1. Add a new inbound rule for the source DB cluster or instance: + Port range – Database port for the source database, usually `5432` + Security group ID – `sg-056a84f1712b77926` in this example ![\[Add inbound rule for the source database.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_self_access_inbound_rule.png) 1. On the **Outbound rules** tab: 1. Choose **Edit outbound rules**. 1. Add a new outbound rule for the source DB cluster or instance: + Database port – `All traffic` (includes ports `0-65535`) + Security group ID – `sg-056a84f1712b77926` in this example ![\[Add outbound rule for the source database.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_self_access_outbound_rule.png) 1. Sign in to the AWS Management Console and open the Amazon VPC console at [https://console.aws.amazon.com/vpc/](https://console.aws.amazon.com/vpc/). 1. Navigate to the **Network ACLs** page. 1. Add the default network ACL configuration as outlined in [Default network ACL](https://docs.aws.amazon.com/vpc/latest/userguide/vpc-network-acls.html#default-network-acl). ## Preparing the destination database Follow the procedures in [Creating Aurora PostgreSQL Limitless Database tables](limitless-creating.md) to create the destination tables in the DB shard group. Your destination tables must have the same schemas, table names, and primary keys as the source tables. ## Creating database credentials You must create database users in the source and destination databases, and grant necessary privileges to the users. For more information, see [CREATE USER](https://www.postgresql.org/docs/current/sql-createuser.html) and [GRANT](https://www.postgresql.org/docs/current/sql-grant.html) in the PostgreSQL documentation. ### Create the source database credentials The source database user is passed in the command to start loading. This user must have privileges to perform replication from the source database. 1. Use the database master user (or another user with the `rds_superuser` role) to create a source database user with `LOGIN` privileges. ``` CREATE USER source_db_username WITH PASSWORD 'source_db_user_password'; ``` 1. Grant the `rds_superuser` role to your source database user. ``` GRANT rds_superuser to source_db_username; ``` 1. If you're using `full_load_and_cdc` mode, grant the `rds_replication` role to your source database user. The `rds_replication` role grants permissions to manage logical slots and to stream data using logical slots. ``` GRANT rds_replication to source_db_username; ``` ### Create the destination database credentials The destination database user must have permission to write to the destination tables in the DB shard group. 1. Use the database master user (or another user with the `rds_superuser` role) to create a destination database user with `LOGIN` privileges. ``` CREATE USER destination_db_username WITH PASSWORD 'destination_db_user_password'; ``` 1. Grant the `rds_superuser` role to your destination database user. ``` GRANT rds_superuser to destination_db_username; ``` # Setting up database authentication and resource access using a script The setup script creates one customer-managed AWS KMS key, one AWS Identity and Access Management (IAM) role, and two AWS Secrets Manager secrets. Perform the following steps to use the setup script: 1. Make sure that you have the AWS CLI installed and configured with your AWS account credentials. 1. Install the `jq` command-line JSON processor. For more information, see [jqlang/jq](https://github.com/jqlang/jq). 1. Copy the [data\$1loading\$1script.zip](samples/data_loading_script.zip) file to your computer, and extract the `data_load_aws_setup_script.sh` file from it. 1. Edit the script to replace the placeholder variables with the appropriate values for the following: + Your AWS account + The AWS Region + Source database credentials + Destination database credentials 1. Open a new terminal on your computer and run the following command: ``` bash ./data_load_aws_setup_script.sh ``` ## Setup script for the data loading utility We provide the text of the `data_load_aws_setup_script.sh` file here for reference. ``` #!/bin/bash # Aurora Limitless data loading - AWS resources setup script # # Set up the account credentials in advance. # # Update the following script variables. # ################################### #### Start of variable section #### ACCOUNT_ID="12-digit_AWS_account_ID" REGION="AWS_Region" DATE=$(date +'%m%d%H%M%S') RANDOM_SUFFIX="${DATE}" SOURCE_SECRET_NAME="secret-source-${DATE}" SOURCE_USERNAME="source_db_username" SOURCE_PASSWORD="source_db_password" DESTINATION_SECRET_NAME="secret-destination-${DATE}" DESTINATION_USERNAME="destination_db_username" DESTINATION_PASSWORD="destination_db_password" DATA_LOAD_IAM_ROLE_NAME="aurora-data-loader-${RANDOM_SUFFIX}" TMP_WORK_DIR="./tmp_data_load_aws_resource_setup/" #### End of variable section #### ################################# # Main logic start echo "DATE - [${DATE}]" echo "RANDOM_SUFFIX - [${RANDOM_SUFFIX}]" echo 'START!' mkdir -p $TMP_WORK_DIR # Create the symmetric KMS key for encryption and decryption. TMP_FILE_PATH="${TMP_WORK_DIR}tmp_create_key_response.txt" aws kms create-key --region $REGION | tee $TMP_FILE_PATH KMS_KEY_ARN=$(cat $TMP_FILE_PATH | jq -r '.KeyMetadata.Arn') aws kms create-alias \ --alias-name alias/"${DATA_LOAD_IAM_ROLE_NAME}-key" \ --target-key-id $KMS_KEY_ARN \ --region $REGION # Create the source secret. TMP_FILE_PATH="${TMP_WORK_DIR}tmp_create_source_secret_response.txt" aws secretsmanager create-secret \ --name $SOURCE_SECRET_NAME \ --kms-key-id $KMS_KEY_ARN \ --secret-string "{\"username\":\"$SOURCE_USERNAME\",\"password\":\"$SOURCE_PASSWORD\"}" \ --region $REGION \ | tee $TMP_FILE_PATH SOURCE_SECRET_ARN=$(cat $TMP_FILE_PATH | jq -r '.ARN') # Create the destination secret. TMP_FILE_PATH="${TMP_WORK_DIR}tmp_create_destination_secret_response.txt" aws secretsmanager create-secret \ --name $DESTINATION_SECRET_NAME \ --kms-key-id $KMS_KEY_ARN \ --secret-string "{\"username\":\"$DESTINATION_USERNAME\",\"password\":\"$DESTINATION_PASSWORD\"}" \ --region $REGION \ | tee $TMP_FILE_PATH DESTINATION_SECRET_ARN=$(cat $TMP_FILE_PATH | jq -r '.ARN') # Create the RDS trust policy JSON file. # Use only rds.amazonaws.com for RDS PROD use cases. TRUST_POLICY_PATH="${TMP_WORK_DIR}rds_trust_policy.json" echo '{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "rds.amazonaws.com" ] }, "Action": "sts:AssumeRole" } ] }' > $TRUST_POLICY_PATH # Create the IAM role. TMP_FILE_PATH="${TMP_WORK_DIR}tmp_create_iam_role_response.txt" aws iam create-role \ --role-name $DATA_LOAD_IAM_ROLE_NAME \ --assume-role-policy-document "file://${TRUST_POLICY_PATH}" \ --tags Key=assumer,Value=aurora_limitless_table_data_load \ --region $REGION \ | tee $TMP_FILE_PATH IAM_ROLE_ARN=$(cat $TMP_FILE_PATH | jq -r '.Role.Arn') # Create the permission policy JSON file. PERMISSION_POLICY_PATH="${TMP_WORK_DIR}data_load_permission_policy.json" permission_json_policy=$(cat <<EOF { "Version": "2012-10-17", "Statement": [ { "Sid": "Ec2Permission", "Effect": "Allow", "Action": [ "ec2:DescribeNetworkInterfaces", "ec2:CreateNetworkInterface", "ec2:DeleteNetworkInterface", "ec2:CreateNetworkInterfacePermission", "ec2:DeleteNetworkInterfacePermission", "ec2:DescribeNetworkInterfacePermissions", "ec2:ModifyNetworkInterfaceAttribute", "ec2:DescribeNetworkInterfaceAttribute", "ec2:DescribeAvailabilityZones", "ec2:DescribeRegions", "ec2:DescribeVpcs", "ec2:DescribeSubnets", "ec2:DescribeSecurityGroups", "ec2:DescribeNetworkAcls" ], "Resource": "*" }, { "Sid": "SecretsManagerPermissions", "Effect": "Allow", "Action": [ "secretsmanager:GetSecretValue", "secretsmanager:DescribeSecret" ], "Resource": [ "$SOURCE_SECRET_ARN", "$DESTINATION_SECRET_ARN" ] }, { "Sid": "KmsPermissions", "Effect": "Allow", "Action": [ "kms:Decrypt", "kms:DescribeKey", "kms:GenerateDataKey" ], "Resource": "$KMS_KEY_ARN" }, { "Sid": "RdsPermissions", "Effect": "Allow", "Action": [ "rds:DescribeDBClusters", "rds:DescribeDBInstances" ], "Resource": "*" } ] } EOF ) echo $permission_json_policy > $PERMISSION_POLICY_PATH # Add the inline policy. aws iam put-role-policy \ --role-name $DATA_LOAD_IAM_ROLE_NAME \ --policy-name aurora-limitless-data-load-policy \ --policy-document "file://${PERMISSION_POLICY_PATH}" \ --region $REGION # Create the key policy JSON file. KEY_POLICY_PATH="${TMP_WORK_DIR}data_load_key_policy.json" key_json_policy=$(cat <<EOF { "Id": "key-aurora-limitless-data-load-$RANDOM_SUFFIX", "Version": "2012-10-17", "Statement": [ { "Sid": "Enable IAM User Permissions", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::$ACCOUNT_ID:root" }, "Action": "kms:*", "Resource": "*" }, { "Sid": "Allow use of the key", "Effect": "Allow", "Principal": { "AWS": "$IAM_ROLE_ARN" }, "Action": [ "kms:Decrypt", "kms:DescribeKey", "kms:GenerateDataKey" ], "Resource": "*" } ] } EOF ) echo $key_json_policy > $KEY_POLICY_PATH # Add the key policy. TMP_FILE_PATH="${TMP_WORK_DIR}tmp_put_key_policy_response.txt" sleep 10 # sleep 10 sec for IAM role ready aws kms put-key-policy \ --key-id $KMS_KEY_ARN \ --policy-name default \ --policy "file://${KEY_POLICY_PATH}" \ --region $REGION \ | tee $TMP_FILE_PATH echo 'DONE!' echo "ACCOUNT_ID : [${ACCOUNT_ID}]" echo "REGION : [${REGION}]" echo "RANDOM_SUFFIX : [${RANDOM_SUFFIX}]" echo "IAM_ROLE_ARN : [${IAM_ROLE_ARN}]" echo "SOURCE_SECRET_ARN : [${SOURCE_SECRET_ARN}]" echo "DESTINATION_SECRET_ARN : [${DESTINATION_SECRET_ARN}]" # Example of a successful run: # ACCOUNT_ID : [012345678912] # REGION : [ap-northeast-1] # RANDOM_SUFFIX : [0305000703] # IAM_ROLE_ARN : [arn:aws:iam::012345678912:role/aurora-data-loader-0305000703] # SOURCE_SECRET_ARN : [arn:aws:secretsmanager:ap-northeast-1:012345678912:secret:secret-source-0305000703-yQDtow] # DESTINATION_SECRET_ARN : [arn:aws:secretsmanager:ap-northeast-1:012345678912:secret:secret-destination-0305000703-5d5Jy8] # If you want to manually clean up failed resource, # please remove them in the following order: # 1. IAM role. # aws iam delete-role-policy --role-name Test-Role --policy-name ExamplePolicy --region us-east-1 # aws iam delete-role --role-name Test-Role --region us-east-1 # 2. Source and destination secrets. # aws secretsmanager delete-secret --secret-id MyTestSecret --force-delete-without-recovery --region us-east-1 # 3. KDM key. # aws kms schedule-key-deletion --key-id arn:aws:kms:us-east-1:123456789012:key/1234abcd-12ab-34cd-56ef-1234567890ab --pending-window-in-days 7 --region us-east-1 ``` ## Output from the data loading utility setup script The following example shows the output from a successful run of the script. ``` % bash ./data_load_aws_setup_script.sh DATE - [0305000703] RANDOM_SUFFIX - [0305000703] START! { "KeyMetadata": { "AWSAccountId": "123456789012", "KeyId": "1234abcd-12ab-34cd-56ef-1234567890ab", "Arn": "arn:aws:kms:ap-northeast-1:123456789012:key/1234abcd-12ab-34cd-56ef-1234567890ab", "CreationDate": "2024-03-05T00:07:49.852000+00:00", "Enabled": true, "Description": "", "KeyUsage": "ENCRYPT_DECRYPT", "KeyState": "Enabled", "Origin": "AWS_KMS", "KeyManager": "CUSTOMER", "CustomerMasterKeySpec": "SYMMETRIC_DEFAULT", "KeySpec": "SYMMETRIC_DEFAULT", "EncryptionAlgorithms": [ "SYMMETRIC_DEFAULT" ], "MultiRegion": false } } { "ARN": "arn:aws:secretsmanager:ap-northeast-1:123456789012:secret:secret-source-0305000703-yQDtow", "Name": "secret-source-0305000703", "VersionId": "a017bebe-a71b-4220-b923-6850c2599c26" } { "ARN": "arn:aws:secretsmanager:ap-northeast-1:123456789012:secret:secret-destination-0305000703-5d5Jy8", "Name": "secret-destination-0305000703", "VersionId": "32a1f989-6391-46b1-9182-f65d242f5eb6" } { "Role": { "Path": "/", "RoleName": "aurora-data-loader-0305000703", "RoleId": "AROAYPX63ITQOYORQSC6U", "Arn": "arn:aws:iam::123456789012:role/aurora-data-loader-0305000703", "CreateDate": "2024-03-05T00:07:54+00:00", "AssumeRolePolicyDocument": { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "rds.amazonaws.com" ] }, "Action": "sts:AssumeRole" } ] }, "Tags": [ { "Key": "assumer", "Value": "aurora_limitless_table_data_load" } ] } } DONE! ACCOUNT_ID : [123456789012] REGION : [ap-northeast-1] RANDOM_SUFFIX : [0305000703] IAM_ROLE_ARN : [arn:aws:iam::123456789012:role/aurora-data-loader-0305000703] SOURCE_SECRET_ARN : [arn:aws:secretsmanager:ap-northeast-1:123456789012:secret:secret-source-0305000703-yQDtow] DESTINATION_SECRET_ARN : [arn:aws:secretsmanager:ap-northeast-1:123456789012:secret:secret-destination-0305000703-5d5Jy8] ``` ## Cleaning up failed resources If you want to clean up failed resources manually, remove them in the following order: 1. IAM role, for example: ``` aws iam delete-role-policy \ --role-name Test-Role \ --policy-name ExamplePolicy aws iam delete-role \ --role-name Test-Role ``` 1. Source and destination secrets, for example: ``` aws secretsmanager delete-secret \ --secret-id MyTestSecret \ --force-delete-without-recovery ``` 1. KMS key, for example: ``` aws kms schedule-key-deletion \ --key-id arn:aws:kms:us-west-2:123456789012:key/1234abcd-12ab-34cd-56ef-1234567890ab \ --pending-window-in-days 7 ``` Then you can retry the script. # Setting up database authentication and resource access manually The manual process for setting up database authentication and resource access has the following steps: 1. [Creating the customer-managed AWS KMS key](#limitless-load.auth.create-kms) 1. [Adding the IAM role permission policies](#limitless-load.auth.iam-policy) 1. [Creating the database secrets](#limitless-load.auth.secrets) 1. [Creating the IAM role](#limitless-load.auth.iam-role) 1. [Updating the customer-managed AWS KMS key](#limitless-load.auth.update-kms) This process is optional, and performs the same tasks as in [Setting up database authentication and resource access using a script](limitless-load.script.md). We recommend using the script. ## Creating the customer-managed AWS KMS key Follow the procedures in [Creating symmetric encryption keys](https://docs.aws.amazon.com/kms/latest/developerguide/create-keys.html#create-symmetric-cmk) to create a customer-managed KMS key. You can also use an existing key if it meets these requirements. **To create a customer-managed KMS key** 1. Sign in to the AWS Management Console and open the AWS KMS console at [https://console.aws.amazon.com/kms](https://console.aws.amazon.com/kms). 1. Navigate to the **Customer managed keys** page. 1. Choose **Create key**. 1. On the **Configure key** page: 1. For **Key type**, select **Symmetric**. 1. For **Key usage**, select **Encrypt and decrypt**. 1. Choose **Next**. 1. On the **Add labels** page, enter an **Alias** such as **limitless**, then choose **Next**. 1. On the **Define key administrative permissions** page, make sure that the **Allow key administrators to delete this key** check box is selected, then choose **Next**. 1. On the **Define key usage permissions** page, choose **Next**. 1. On the **Review** page, choose **Finish**. You update the key policy later. Record the Amazon Resource Names (ARN) of the KMS key to use in [Adding the IAM role permission policies](#limitless-load.auth.iam-policy). For information on using the AWS CLI to create the customer-managed KMS key, see [create-key](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/kms/create-key.html) and [create-alias](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/kms/create-alias.html). ## Creating the database secrets To allow the data loading utility to access the source and destination database tables, you create two secrets in AWS Secrets Manager: one for the source database and one for the destination database. These secrets store the usernames and passwords for accessing the source and destination databases. Follow the procedures in [Create an AWS Secrets Manager secret](https://docs.aws.amazon.com/secretsmanager/latest/userguide/create_secret.html) to create the key-value pair secrets. **To create the database secrets** 1. Open the Secrets Manager console at [https://console.aws.amazon.com/secretsmanager/](https://console.aws.amazon.com/secretsmanager/). 1. Choose **Store a new secret**. 1. On the **Choose secret type** page: 1. For **Secret type**, select **Other type of secret**. 1. For **Key/value pairs**, choose the **Plaintext** tab. 1. Enter the following JSON code, where `sourcedbreader` and `sourcedbpassword` are the credentials for the source database user from [Create the source database credentials](limitless-load.utility.md#limitless-load.users.source). ``` { "username":"sourcedbreader", "password":"sourcedbpassword" } ``` 1. For **Encryption key**, choose the KMS key that you created in [Creating the customer-managed AWS KMS key](#limitless-load.auth.create-kms), for example `limitless`. 1. Choose **Next**. 1. On the **Configure secret** page, enter a **Secret name**, such as **source\$1DB\$1secret**, then choose **Next**. 1. On the **Configure rotation - *optional*** page, choose **Next**. 1. On the **Review** page, choose **Store**. 1. Repeat the procedure for the destination database secret: 1. Enter the following JSON code, where `destinationdbwriter` and `destinationdbpassword` are the credentials for the destination database user from [Create the destination database credentials](limitless-load.utility.md#limitless-load.users.destination). ``` { "username":"destinationdbwriter", "password":"destinationdbpassword" } ``` 1. Enter a **Secret name**, such as **destination\$1DB\$1secret**. Record the ARNs of the secrets to use in [Adding the IAM role permission policies](#limitless-load.auth.iam-policy). ## Creating the IAM role Data loading requires you to provide access to AWS resources. To provide access, you create the `aurora-data-loader` IAM role by following the procedures in [Creating a role to delegate permissions to an IAM user.](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-user.html) **To create the IAM role** 1. Sign in to the AWS Management Console and open the IAM console at [https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/). 1. Navigate to the **Roles** page. 1. Choose **Create role**. 1. On the **Select trusted entity** page: 1. For **Trusted entity type**, select **Custom trust policy**. 1. Enter the following JSON code for the custom trust policy: ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "rds.amazonaws.com" ] }, "Action": "sts:AssumeRole" } ] } ``` ------ 1. Choose **Next**. 1. On the **Add permissions** page, choose **Next**. 1. On the **Name, review, and create** page: 1. For **Role name**, enter **aurora-data-loader** or another name that you prefer. 1. Choose **Add tag**, and enter the following tag: + **Key**: **assumer** + **Value**: **aurora\$1limitless\$1table\$1data\$1load** **Important** The Aurora PostgreSQL Limitless Database can only assume an IAM role that has this tag. 1. Choose **Create role**. ## Updating the customer-managed AWS KMS key Follow the procedures in [Changing a key policy](https://docs.aws.amazon.com/kms/latest/developerguide/key-policy-modifying.html) to add the IAM role `aurora-data-loader` to the default key policy. **To add the IAM role to the key policy** 1. Sign in to the AWS Management Console and open the AWS KMS console at [https://console.aws.amazon.com/kms](https://console.aws.amazon.com/kms). 1. Navigate to the **Customer managed keys** page. 1. Choose the KMS key that you created in [Creating the customer-managed AWS KMS key](#limitless-load.auth.create-kms), for example `limitless`. 1. On the **Key policy** tab, for **Key users**, choose **Add**. 1. In the **Add key users** window, select the name of the IAM role that you created in [Creating the IAM role](#limitless-load.auth.iam-role), for example **aurora-data-loader**. 1. Choose **Add**. ## Adding the IAM role permission policies You must add permission policies to the IAM role that you created. This allows the Aurora PostgreSQL Limitless Database data loading utility to access related AWS resources for building network connections and retrieving the source and destination DB credential secrets. For more information, see [Modifying a role](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_manage_modify.html#roles-modify_gen-policy). **To add the permission policies** 1. Sign in to the AWS Management Console and open the IAM console at [https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/). 1. Navigate to the **Roles** page. 1. Choose the IAM role that you created in [Creating the IAM role](#limitless-load.auth.iam-role), for example **aurora-data-loader**. 1. On the **Permissions** tab, for **Permissions policies** choose **Add permissions**, then **Create inline policy**. 1. On the **Specify permissions** page, choose the **JSON** editor. 1. Copy and paste the following template into the JSON editor, replacing the placeholders with the ARNs for your database secrets and KMS key. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "Ec2Permission", "Effect": "Allow", "Action": [ "ec2:DescribeNetworkInterfaces", "ec2:CreateNetworkInterface", "ec2:DeleteNetworkInterface", "ec2:CreateNetworkInterfacePermission", "ec2:DeleteNetworkInterfacePermission", "ec2:DescribeNetworkInterfacePermissions", "ec2:ModifyNetworkInterfaceAttribute", "ec2:DescribeNetworkInterfaceAttribute", "ec2:DescribeAvailabilityZones", "ec2:DescribeRegions", "ec2:DescribeVpcs", "ec2:DescribeSubnets", "ec2:DescribeSecurityGroups", "ec2:DescribeNetworkAcls" ], "Resource": "*" }, { "Sid": "SecretsManagerPermissions", "Effect": "Allow", "Action": [ "secretsmanager:GetSecretValue", "secretsmanager:DescribeSecret" ], "Resource": [ "arn:aws:secretsmanager:us-east-1:123456789012:secret:source_DB_secret-ABC123", "arn:aws:secretsmanager:us-east-1:123456789012:secret:destination_DB_secret-456DEF" ] }, { "Sid": "KmsPermissions", "Effect": "Allow", "Action": [ "kms:Decrypt", "kms:DescribeKey", "kms:GenerateDataKey" ], "Resource": "arn:aws:kms:us-east-1:123456789012:key/aa11bb22-####-####-####-fedcba123456" }, { "Sid": "RdsPermissions", "Effect": "Allow", "Action": [ "rds:DescribeDBClusters", "rds:DescribeDBInstances" ], "Resource": "*" } ] } ``` ------ 1. Check for errors and correct them. 1. Choose **Next**. 1. On the **Review and create** page, enter a **Policy name** such as **data\$1loading\$1policy**, then choose **Create policy**. # Loading data from an Aurora PostgreSQL DB cluster or RDS for PostgreSQL DB instance After you complete the resource and authentication setup, connect to the cluster endpoint and call the `rds_aurora.limitless_data_load_start` stored procedure from a limitless database, such as `postgres_limitless`. The limitless database is a database on the DB shard group into which you want to migrate data. This function connects asynchronously in the background to the source database specified in the command, reads the data from the source, and loads the data onto the shards. For better performance, the data is loaded using parallel threads. The function retrieves a point-in-time table snapshot by running a `SELECT` command to read the data of the table(s) provided in the command. You can load data into sharded, reference, and standard tables. You can load data at the database, schema, or table level in `rds_aurora.limitless_data_load_start` calls. + Database – You can load one database at a time in each call, with no limit on the schema or table count within the database. + Schema – You can load a maximum of 15 schemas in each call, with no limit on the table count within each schema. + Table – You can load a maximum of 15 tables in each call. **Note** This feature doesn't use Amazon RDS snapshots or point-in-time isolation of the database. For consistency across tables, we recommend cloning the source database and pointing to that cloned database as the source. The stored procedure uses the following syntax: ``` CALL rds_aurora.limitless_data_load_start('source_type', 'source_DB_cluster_or_instance_ID', 'source_database_name', 'streaming_mode', 'data_loading_IAM_role_arn', 'source_DB_secret_arn', 'destination_DB_secret_arn', 'ignore_primary_key_conflict_boolean_flag', 'is_dry_run', (optional parameter) schemas/tables => ARRAY['name1', 'name2', ...]); ``` The input parameters are the following: + `source_type` – The source type: `aurora_postgresql` or `rds_postgresql` + `source_DB_cluster_or_instance_ID` – The source Aurora PostgreSQL DB cluster identifier or RDS for PostgreSQL DB instance identifier + `source_database_name` – The source database name, such as *postgres* + `streaming_mode` – Whether to include change data capture (CDC): `full_load` or `full_load_and_cdc` + `data_loading_IAM_role_arn` – The IAM role Amazon Resource Name (ARN) for `aurora-data-loader` + `source_DB_secret_arn` – The source DB secret ARN + `destination_DB_secret_arn` – The destination DB secret ARN + `ignore_primary_key_conflict_boolean_flag` – Whether to continue if a primary key conflict occurs: + If set to `true`, data loading ignores new changes for rows with a primary key conflict. + If set to `false`, data loading overwrites the existing rows on destination tables when it encounters a primary key conflict. + `is_dry_run` – Whether to test that the data loading job can connect to the source and destination databases: + If set to `true`, tests the connections without loading data + If set to `false`, loads the data + (optional) `schemas` or `tables` – An array of schemas or tables to load. You can specify either of the following: + A list of tables in the format `tables => ARRAY['schema1.table1', 'schema1.table2', 'schema2.table1', ...]` + A list of schemas in the format `schemas => ARRAY['schema1', 'schema2', ...]` If you don't include this parameter, the entire specified source database is migrated. The output parameter is the job ID with a message. The following example shows how to use the `rds_aurora.limitless_data_load_start` stored procedure to load data from an Aurora PostgreSQL DB cluster. ``` CALL rds_aurora.limitless_data_load_start('aurora_postgresql', 'my-db-cluster', 'postgres', 'full_load_and_cdc', 'arn:aws:iam::123456789012:role/aurora-data-loader-8f2c66', 'arn:aws:secretsmanager:us-east-1:123456789012:secret:secret-source-8f2c66-EWrr0V', 'arn:aws:secretsmanager:us-east-1:123456789012:secret:secret-destination-8f2c66-d04fbD', 'true', 'false', tables => ARRAY['public.customer', 'public.order', 'public.orderdetails']); INFO: limitless data load job id 1688761223647 is starting. ``` # Monitoring data loading Aurora PostgreSQL Limitless Database provides several ways to monitor data loading jobs: + [Listing data loading jobs](#limitless-load.monitor-list) + [Viewing details of data loading jobs using the job ID](#limitless-load.monitor-describe) + [Monitoring the Amazon CloudWatch log group](#limitless-load.monitor-cwl) + [Monitoring RDS events](#limitless-load.monitor-events) ## Listing data loading jobs You can connect to the cluster endpoint and use the `rds_aurora.limitless_data_load_jobs` view to list data loading jobs. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_data_load_jobs LIMIT 6; job_id | status | message | source_db_identifier | source_db_name | full_load_complete_time | progress_details | start_time | last_updated_time | streaming_mode | source_engine_type | ignore_primary_key_conflict | is_dryrun ---------------+-----------+---------+-------------------------------+----------------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------------+-------------------+--------------------+-----------------------------+----------- 1725697520693 | COMPLETED | | persistent-kdm-auto-source-01 | postgres | 2024-09-07 08:48:15+00 | {"FULL_LOAD": {"STATUS": "COMPLETED", "DETAILS": "9 of 9 tables loaded", "COMPLETED_AT": "2024/09/07 08:48:15+00", "RECORDS_MIGRATED": 600003}} | 2024-09-07 08:47:13+00 | 2024-09-07 08:48:15+00 | full_load | aurora_postgresql | t | f 1725696114225 | COMPLETED | | persistent-kdm-auto-source-01 | postgres | 2024-09-07 08:24:20+00 | {"FULL_LOAD": {"STATUS": "COMPLETED", "DETAILS": "3 of 3 tables loaded", "COMPLETED_AT": "2024/09/07 08:24:20+00", "RECORDS_MIGRATED": 200001}} | 2024-09-07 08:23:56+00 | 2024-09-07 08:24:20+00 | full_load | aurora_postgresql | t | f 1725696067630 | COMPLETED | | persistent-kdm-auto-source-01 | postgres | 2024-09-07 08:23:45+00 | {"FULL_LOAD": {"STATUS": "COMPLETED", "DETAILS": "6 of 6 tables loaded", "COMPLETED_AT": "2024/09/07 08:23:45+00", "RECORDS_MIGRATED": 400002}} | 2024-09-07 08:23:10+00 | 2024-09-07 08:23:45+00 | full_load | aurora_postgresql | t | f 1725694221753 | CANCELED | | persistent-kdm-auto-source-01 | postgres | | {} | 2024-09-07 07:31:18+00 | 2024-09-07 07:51:49+00 | full_load_and_cdc | aurora_postgresql | t | f 1725691698210 | COMPLETED | | persistent-kdm-auto-source-01 | postgres | 2024-09-07 07:10:51+00 | {"FULL_LOAD": {"STATUS": "COMPLETED", "DETAILS": "1 of 1 tables loaded", "COMPLETED_AT": "2024/09/07 07:10:51+00", "RECORDS_MIGRATED": 100000}} | 2024-09-07 07:10:42+00 | 2024-09-07 07:10:52+00 | full_load | aurora_postgresql | t | f 1725691695049 | COMPLETED | | persistent-kdm-auto-source-01 | postgres | 2024-09-07 07:10:48+00 | {"FULL_LOAD": {"STATUS": "COMPLETED", "DETAILS": "1 of 1 tables loaded", "COMPLETED_AT": "2024/09/07 07:10:48+00", "RECORDS_MIGRATED": 100000}} | 2024-09-07 07:10:41+00 | 2024-09-07 07:10:48+00 | full_load | aurora_postgresql | t | f (6 rows) ``` Job records are deleted after 90 days. ## Viewing details of data loading jobs using the job ID If you know a job ID, you can connect to the cluster endpoint and use the `rds_aurora.limitless_data_load_job_details` view to see the details of that data loading job, including the table name, job status, and number of rows loaded. You can get the job ID in the responses to the data loading start functions, or from the `rds_aurora.limitless_data_load_jobs` view. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_data_load_job_details WHERE job_id='1725696114225'; job_id | destination_table_name | destination_schema_name | start_time | status | full_load_rows | full_load_total_rows | full_load_complete_time | cdc_insert | cdc_update | cdc_delete --------------+------------------------+-------------------------+------------------------+-----------+----------------+----------------------+-------------------------+------------+------------+------------ 1725696114225 | standard_1 | public | 2024-09-07 08:23:57+00 | COMPLETED | 100000 | 100000 | 2024-09-07 08:24:08+00 | 0 | 0 | 0 1725696114225 | standard_2 | public | 2024-09-07 08:24:08+00 | COMPLETED | 100000 | 100000 | 2024-09-07 08:24:17+00 | 0 | 0 | 0 1725696114225 | standard_3 | public | 2024-09-07 08:24:18+00 | COMPLETED | 1 | 1 | 2024-09-07 08:24:20+00 | 0 | 0 | 0 1725696114225 | standard_4 | public | 2024-09-07 08:23:58+00 | PENDING | 0 | 0 | | 0 | 0 | 0 (4 rows) ``` Job records are deleted after 90 days. ## Monitoring the Amazon CloudWatch log group After the data loading job status changes to `RUNNING`, you can check the runtime progress using Amazon CloudWatch Logs. **To monitor CloudWatch log streams** Sign in to the AWS Management Console and open the CloudWatch console at [https://console.aws.amazon.com/cloudwatch/](https://console.aws.amazon.com/cloudwatch/). 1. Navigate to **Logs**, then **Log groups**. 1. Choose the **/aws/rds/aurora-limitless-database** log group. 1. Search for the log stream of your data loading job by **job\$1id**. The log stream has the pattern **Data-Load-Job-*job\$1id***. 1. Choose the log stream to see the log events. Each log stream shows events containing the job status and the number of rows loaded to the Aurora PostgreSQL Limitless Database destination tables. If a data loading job fails, an error log is also created that shows the failure status and the reason. Job records are deleted after 90 days. ## Monitoring RDS events The data loading job also publishes RDS events, for example when a job succeeds, fails, or is canceled. You can view the events from the destination database. For more information, see [DB shard group events](USER_Events.Messages.md#USER_Events.Messages.shard-group). # Canceling data loading To cancel a data loading job, call the `rds_aurora.limitless_data_load_cancel` stored procedure, with the job ID as the input parameter. You call this stored procedure from the same database in the DB shard group where the specific data loading job was started. For example: ``` CALL rds_aurora.limitless_data_load_cancel(12345); INFO: limitless data load job with id 12345 is canceling without rollback. ``` You can't cancel a data loading job that doesn't exist or isn't running in the same DB shard group. The Aurora PostgreSQL Limitless Database data loading utility leaves loaded data in the destination tables without rollback, as the response shows. If you don’t want to keep the loaded data, we recommend truncating the destination tables. # Querying Aurora PostgreSQL Limitless Database Aurora PostgreSQL Limitless Database is compatible with PostgreSQL syntax for queries. You can query your Limitless Database using `psql` or any other connection utility that works with PostgreSQL. To run queries, you connect to the limitless endpoint as shown in [Connecting to your Aurora PostgreSQL Limitless Database DB cluster](limitless-shard.md#limitless-endpoint). All PostgreSQL `SELECT` queries are supported in Aurora PostgreSQL Limitless Database. However, queries are performed on two layers: 1. Router to which the client sends the query 1. Shards where the data is located Performance depends on querying the database in a way that allows it to achieve a high degree of simultaneous processing of different queries on different shards. Queries are first parsed in the distributed transaction layer (router). Before planning the query execution, there's an analysis phase to identify the location for all relations participating in the query. If all relations are sharded tables with a filtered shard key on the same shard, or reference tables, then query planning is skipped on the router layer and completely pushed down to the shard for planning and execution. This process reduces the number of round trips between different nodes (router and shard) and results in better performance in most cases. For more information, see [Single-shard queries in Aurora PostgreSQL Limitless Database](limitless-query.single-shard.md). **Note** There can be specific cases, such as a [Cartesian product](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-FROM) (cross join), where the query performs better by retrieving data separately from the shard. For more information on query execution plans, see [EXPLAIN](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.EXPLAIN) in the [Aurora PostgreSQL Limitless Database referenceLimitless Database reference](limitless-reference.md). For general information on queries, see [Queries](https://www.postgresql.org/docs/current/queries-overview.html) in the PostgreSQL documentation. **Topics** + [ # Single-shard queries in Aurora PostgreSQL Limitless Database ](limitless-query.single-shard.md) + [ # Distributed queries in Aurora PostgreSQL Limitless Database ](limitless-query.distributed.md) + [ # Distributed query tracing in PostgreSQL logs in Aurora PostgreSQL Limitless Database ](limitless-query.tracing.md) + [ # Distributed deadlocks in Aurora PostgreSQL Limitless Database ](limitless-query.deadlocks.md) # Single-shard queries in Aurora PostgreSQL Limitless Database A *single-shard query* is a query that can be run directly on a shard while maintaining SQL [ACID](https://en.wikipedia.org/wiki/ACID) semantics. When such a query is encountered by the query planner on the router, the planner detects it and proceeds to push down the entire SQL query to the corresponding shard. This optimization reduces the number of network round trips from the router to the shard, improving the performance. Currently this optimization is performed for `INSERT`, `SELECT`, `UPDATE`, and `DELETE` queries. **Topics** + [ ## Single-shard query examples ](#limitless-query.single-shard.examples) + [ ## Restrictions for single-shard queries ](#limitless-query.single-shard.restrictions) + [ ## Fully qualified (explicit) joins ](#limitless-query.single-shard.fq) + [ ## Setting an active shard key ](#limitless-query.single-shard.active) ## Single-shard query examples In the following examples, we have the sharded table `customers`, with the shard key `customer_id`, and the reference table `zipcodes`. **SELECT** ``` postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM customers WHERE customer_id = 100; QUERY PLAN --------------------------------------------------------- Foreign Scan Output: customer_id, other_id, customer_name, balance Remote SQL: SELECT customer_id, other_id, customer_name, balance FROM public.customers WHERE (customer_id = 100) Single Shard Optimized (9 rows) ``` ``` postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM orders LEFT JOIN zipcodes ON orders.zipcode_id = zipcodes.zipcode_id WHERE customer_id = 11; QUERY PLAN --------------------------------------------------------------------------------------------------------- Foreign Scan Output: customer_id, order_id, zipcode_id, customer_name, balance, zipcodes.zipcode_id, zipcodes.city Remote SQL: SELECT orders.customer_id, orders.order_id, orders.zipcode_id, orders.customer_name, orders.balance, zipcodes.zipcode_id, zipcodes.city FROM (public.orders LEFT JOIN public.zipcodes ON ((orders.zipcode_id = zipcodes.zipcode_id))) WHERE (orders.customer_id = 11) Single Shard Optimized (13 rows) ``` **INSERT** ``` postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO customers (customer_id, other_id, customer_name, balance) VALUES (1, 10, 'saikiran', 1000); QUERY PLAN ------------------------------------------------------- Insert on public.customers -> Result Output: 1, 10, 'saikiran'::text, '1000'::real Single Shard Optimized (4 rows) ``` **UPDATE** ``` postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) UPDATE orders SET balance = balance + 100 WHERE customer_id = 100; QUERY PLAN --------------------------------------------------------------------------------------------- Update on public.orders Foreign Update on public.orders_fs00002 orders_1 -> Foreign Update Remote SQL: UPDATE public.orders SET balance = (balance + (100)::double precision) WHERE (customer_id = 100) Single Shard Optimized (6 rows) ``` **DELETE** ``` postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM orders WHERE customer_id = 100 and balance = 0; QUERY PLAN --------------------------------------------------------------------- Delete on public.orders Foreign Delete on public.orders_fs00002 orders_1 -> Foreign Delete Remote SQL: DELETE FROM public.orders WHERE ((customer_id = 100) AND (balance = (0)::double precision)) Single Shard Optimized (6 rows) ``` ## Restrictions for single-shard queries Single-shard queries have the following restrictions: **Functions** If a single-shard query contains a function, the query qualifies for single-shard optimization only if one of the following conditions applies: + The function is immutable. For more information, see [Function volatility](limitless-reference.DDL-limitations.md#limitless-function-volatility). + The function is mutable, but is registered in the `rds_aurora.limitless_distributed_functions` view. For more information, see [Function distribution](limitless-reference.DDL-limitations.md#limitless-function-distribution). **Views** If a query contains one or more views, single-shard optimization is disabled for the query if it has one of the following conditions: + Any view has the `security_barrier` attribute. + Objects used in the query require multiple user privileges. For example, a query contains two views, and the views are run under two different users. ``` CREATE VIEW v1 AS SELECT customer_name FROM customers c WHERE c.customer_id = 1; CREATE VIEW v2 WITH (security_barrier) AS SELECT customer_name FROM customers c WHERE c.customer_id = 1; postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v1; QUERY PLAN ------------------------------------------------------------------------------------ Foreign Scan (cost=100.00..101.00 rows=100 width=0) Output: customer_name Remote Plans from Shard postgres_s3: Seq Scan on public.customers_ts00001 c (cost=0.00..24.12 rows=6 width=32) Output: c.customer_name Filter: (c.customer_id = 1) Query Identifier: -6005737533846718506 Remote SQL: SELECT customer_name FROM ( SELECT c.customer_name FROM public.customers c WHERE (c.customer_id = 1)) v1 Query Identifier: -5754424854414896228 (12 rows) postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v2; QUERY PLAN -------------------------------------------------------------------------------------------- Foreign Scan on public.customers_fs00001 c (cost=100.00..128.41 rows=7 width=32) Output: c.customer_name Remote Plans from Shard postgres_s3: Seq Scan on public.customers_ts00001 customers (cost=0.00..24.12 rows=6 width=32) Output: customers.customer_name Filter: (customers.customer_id = 1) Query Identifier: 4136563775490008117 Remote SQL: SELECT customer_name FROM public.customers WHERE ((customer_id = 1)) Query Identifier: 5056054318010163757 (9 rows) ``` **PREPARE and EXECUTE statements** Aurora PostgreSQL Limitless Database supports single-shard optimization for prepared `SELECT`, `UPDATE`, and `DELETE` statements. However, if you use prepared statements for `PREPARE` and `EXECUTE` with `plan_cache_mode` set to `'force_generic_plan'`, the query planner rejects single-shard optimization for that query. **PL/pgSQL** Queries with PL/pgSQL variables are run as implicitly prepared statements. If a query contains any PL/pgSQL variables, the query planner rejects single-shard optimization. Optimization is supported in the PL/pgSQL block if the statement doesn't contain any PL/pgSQL variables. ## Fully qualified (explicit) joins Single-shard optimization is based on partition eliminations. The PostgreSQL optimizer eliminates partitions based on constant conditions. If Aurora PostgreSQL Limitless Database finds that all of the remaining partitions and tables are on the same shard, it marks the query eligible for single-shard optimization. All filter conditions must be explicit for partition elimination to work. Aurora PostgreSQL Limitless Database can't eliminate partitions without one or more join predicates or filter predicates on the shard keys of every sharded table in the statement. Assume that we've partitioned the `customers`, `orders`, and `order_details` tables based on the `customer_id` column. In this schema, the application tries to keep all of the data for a customer on a single shard. Consider the following query: ``` SELECT * FROM customers c, orders o, order_details od WHERE c.customer_id = o.customer_id AND od.order_id = o.order_id AND c.customer_id = 1; ``` This query retrieves all of the data for a customer (`c.customer_id = 1`). Data for this customer is on a single shard, but Aurora PostgreSQL Limitless Database doesn't qualify this query as a single-shard query. The optimizer process for the query is as follows: 1. The optimizer can eliminate partitions for `customers` and `orders` based on the following condition: ``` c.customer_id = 1 c.customer_id = o.customer_id o.customer_id = 1 (transitive implicit condition) ``` 1. The optimizer can't eliminate any partitions for `order_details`, because there's no constant condition on the table. 1. The optimizer concludes that it has read all of the partitions from `order_details`. Therefore, the query can't be qualified for single-shard optimization. To make this a single-shard query, we add the following explicit join condition: ``` o.customer_id = od.customer_id ``` The changed query looks like this: ``` SELECT * FROM customers c, orders o, order_details od WHERE c.customer_id = o.customer_id AND o.customer_id = od.customer_id AND od. order_id = o. order_id AND c.customer_id = 1; ``` Now the optimizer can eliminate partitions for `order_details`. The new query becomes a single-shard query and qualifies for optimization. ## Setting an active shard key This feature allows you to set a single shard key while querying the database, causing all `SELECT` and DML queries to be appended with the shard key as a constant predicate. This feature is useful if you've migrated to Aurora PostgreSQL Limitless Database and have denormalized the schema by adding shard keys to tables. You can append a shard key predicate automatically to the existing SQL logic, without changing the semantics of the queries. Appending an active shard key predicate is done only for [compatible tables](#active-shard-key-compatible-tables). The active shard key feature uses the `rds_aurora.limitless_active_shard_key` variable, which has the following syntax: ``` SET [session | local] rds_aurora.limitless_active_shard_key = '{"col1_value", "col2_value", ...}'; ``` Some considerations about active shard keys and foreign keys: + A sharded table can have a foreign key constraint if the parent and child tables are collocated and the foreign key is a superset of the shard key. + A sharded table can have a foreign key constraint to a reference table. + A reference table can have a foreign key constraint to another reference table. Assume that we have a `customers` table that is sharded on the `customer_id` column. ``` BEGIN; SET local rds_aurora.limitless_create_table_mode='sharded'; SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}'; CREATE TABLE customers(customer_id int PRIMARY KEY, name text , email text); COMMIT; ``` With an active shard key set, queries have the following transformations. **SELECT** ``` SET rds_aurora.limitless_active_shard_key = '{"123"}'; SELECT * FROM customers; -- This statement is changed to: SELECT * FROM customers WHERE customer_id = '123'::int; ``` **INSERT** ``` SET rds_aurora.limitless_active_shard_key = '{"123"}'; INSERT INTO customers(name, email) VALUES('Alex', 'alex@example.com'); -- This statement is changed to: INSERT INTO customers(customer_id, name, email) VALUES('123'::int, 'Alex', 'alex@example.com'); ``` **UPDATE** ``` SET rds_aurora.limitless_active_shard_key = '{"123"}'; UPDATE customers SET email = 'alex_new_email@example.com'; -- This statement is changed to: UPDATE customers SET email = 'alex_new_email@example.com' WHERE customer_id = '123'::int; ``` **DELETE** ``` SET rds_aurora.limitless_active_shard_key = '{"123"}'; DELETE FROM customers; -- This statement is changed to: DELETE FROM customers WHERE customer_id = '123'::int; ``` **Joins** When performing join operations on tables with an active shard key, the shard key predicate is automatically added to all tables involved in the join. This automatic addition of the shard key predicate occurs only when all tables in the query belong to the same collocation group. If the query involves tables from different collocation groups, an error is raised instead. Assume that we also have `orders` and `order_details` tables that are collocated with the `customers` table. ``` SET local rds_aurora.limitless_create_table_mode='sharded'; SET local rds_aurora.limitless_create_table_collocate_with='customers'; SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}'; CREATE TABLE orders (id int , customer_id int, total_amount int, date date); CREATE TABLE order_details (id int , order_id int, customer_id int, product_name VARCHAR(100), price int); COMMIT; ``` Retrieve the last 10 order invoices for a customer whose customer ID is 10. ``` SET rds_aurora.limitless_active_shard_key = '{"10"}'; SELECT * FROM customers, orders, order_details WHERE orders.customer_id = customers.customer_id AND order_details.order_id = orders.order_id AND customers.customer_id = 10 order by order_date limit 10; ``` This query is transformed into the following: ``` SELECT * FROM customers, orders, order_details WHERE orders.customer_id = customers.customer_id AND orders.order_id = order_details.order_id AND customers.customer_id = 10 AND order_details.customer_id = 10 AND orders.customer_id = 10 AND ORDER BY "order_date" LIMIT 10; ``` **Active shard key compatible tables** The shard key predicate is added only to tables that are compatible with the active shard key. A table is considered compatible if it has the same number of columns in its shard key as specified in the `rds_aurora.limitless_active_shard_key` variable. If the query involves tables that are incompatible with the active shard key, the system raises an error instead of proceeding with the query. For example: ``` -- Compatible table SET rds_aurora.limitless_active_shard_key = '{"10"}'; -- The following query works because the customers table is sharded on one column. SELECT * FROM customers; -- Incompatible table SET rds_aurora.limitless_active_shard_key = '{"10","20"}'; -- The following query raises a error because the customers table isn't sharded on two columns. SELECT * FROM customers; ``` # Distributed queries in Aurora PostgreSQL Limitless Database Distributed queries run on a router and more than one shard. The query is received by one of the routers. The router creates and manages the distributed transaction, which is sent to the participating shards. The shards create a local transaction with the context provided by the router, and the query is run. When the transaction is committed, the router uses an optimized two-phase commit protocol if needed, and time-based Multi Version Concurrency Control (MVCC) to provide [ACID](https://en.wikipedia.org/wiki/ACID) semantics in a distributed database system. Time-based MVCC records the commit time for each transaction and uses the transaction start time to generate the data snapshot time. To identify whether a transaction is committed (visible) given a reader’s snapshot, the database compares its commit time with the snapshot time. If its commit time is less than the reader’s snapshot time, it is visible; otherwise, invisible. Under this protocol, you will always expect to see strongly consistent data on Aurora PostgreSQL Limitless Database. # Distributed query tracing in PostgreSQL logs in Aurora PostgreSQL Limitless Database Distributed query tracing is a tool to trace and correlate queries in PostgreSQL logs across Aurora PostgreSQL Limitless Database. In Aurora PostgreSQL, you use the transaction ID to identify a transaction. But in Aurora PostgreSQL Limitless Database, the transaction ID can repeat across various routers. Therefore, we recommend that you use the tracing ID instead in Limitless Database. The key use cases are the following: + Use the `rds_aurora.limitless_get_last_trace_id()` function to find the unique tracing ID of the last query run in the current session. Then search the DB cluster log group in Amazon CloudWatch Logs using that tracing ID to find all the related logs. You can use the `log_min_messages` and `log_min_error_statement` parameters together to control the volume of logs printed and print a statement that contains the tracing ID. + Use the `log_min_duration_statement` parameter to determine the run time above which all of the queries print their run duration and tracing ID. This run time can then be searched in the DB cluster log group in CloudWatch Logs to help determine the bottleneck nodes and planner optimization efforts. The `log_min_duration_statement` parameter enables the tracing ID for all nodes regardless of the values of `log_min_messages` and `log_min_error_statement` parameters. **Topics** + [ ## Tracing ID ](#limitless-query.tracing.ID) + [ ## Using query tracing ](#limitless-query.tracing.using) + [ ## Log examples ](#limitless-query.tracing.examples) ## Tracing ID Central to this feature is a unique identifier known as the *tracing ID*. The tracing ID is a 31-digit string appended to the STATEMENT log lines of PostgreSQL logs, acting as a precise identifier for correlating logs related to a specific query execution. Examples are `1126253375719408504000000000011` and `1126253375719408495000000000090`. The tracing ID is composed of the following: + Transaction identifier – The first 20 digits, uniquely identifying the transaction. + Command identifier – The first 30 digits, indicating an individual query within a transaction. If more than 4,294,967,294 queries are run inside an explicit transaction block, the command Identifier wraps around to `1`. When this happens, you're notified by the following `LOG` message in the PostgreSQL log: ``` wrapping around the tracing ID back to 1 after running 4294967294 (4.2 billion or 2^32-2) queries inside of an explicit transaction block ``` + Node type identifier – The last digit, indicating whether the node is functioning as a coordinator router (`1`) or a participant node (`0`). The following examples illustrate the components of the tracing ID: + `1126253375719408504000000000011`: + Transaction identifier – `1126253375719408504` + Command identifier – `112625337571940850400000000001` indicates the first command in the transaction block + Node type identifier – `1` indicates a coordinator router + `1126253375719408495000000000090`: + Transaction identifier – `1126253375719408495` + Command identifier – `112625337571940849500000000009` indicates the ninth command in the transaction block + Node type identifier – `0` indicates a participant node ## Using query tracing Perform the following tasks to use query tracing: 1. Make sure that tracing is enabled. You can check using the following command: ``` SHOW rds_aurora.limitless_log_distributed_trace_id; ``` It's enabled by default (`on`). If it's not enabled, set it using the following command: ``` SET rds_aurora.limitless_log_distributed_trace_id = on; ``` 1. Control the volume of logs printed by configuring the log severity level. The log volume is controlled by the `log_min_messages` parameter. The `log_min_error_statement` parameter is used to print the `STATEMENT` line with the tracing ID. Both are set to `ERROR` by default. You can check using the following commands: ``` SHOW log_min_messages; SHOW log_min_error_statement; ``` To update the severity level and print the `STATEMENT` line for the current session, use the following commands with one of these severity levels: ``` SET log_min_messages = 'DEBUG5 | DEBUG4 | DEBUG3 | DEBUG2 | DEBUG1 | INFO | NOTICE | WARNING | ERROR | LOG | FATAL | PANIC'; SET log_min_error_statement = 'DEBUG5 | DEBUG4 | DEBUG3 | DEBUG2 | DEBUG1 | INFO | NOTICE | WARNING | ERROR | LOG | FATAL | PANIC'; ``` For example: ``` SET log_min_messages = 'WARNING'; SET log_min_error_statement = 'WARNING'; ``` 1. Enable printing the tracing ID in the logs above a specific run time. The `log_min_duration_statement` parameter can be changed to the minimum query run time above which the query prints a log line with the run duration, along with the tracing IDs across the DB cluster. This parameter is set to `-1` by default, which means it's disabled. You can check using the following command: ``` SHOW log_min_duration_statement; ``` Changing it to `0` prints the duration and tracing ID in the logs for every query across the DB cluster. You can set it to `0` for the current session by using the following command: ``` SET log_min_duration_statement = 0; ``` 1. Obtain the tracing ID. After running a query (even inside an explicit transaction block), call the `rds_aurora.limitless_get_last_trace_id` function to obtain the tracing ID of the last query run: ``` SELECT * FROM rds_aurora.limitless_get_last_trace_id(); ``` This function returns the transaction identifier and the command identifier. It doesn't return the node type identifier. ``` => SELECT * FROM customers; customer_id | fname | lname -------------+-------+------- (0 rows) => SELECT * FROM rds_aurora.limitless_get_last_trace_id(); transaction_identifier | command_identifier ------------------------+-------------------------------- 10104661421959001813 | 101046614219590018130000000001 (1 row) ``` The function returns a blank line for nondistributed queries, as they don't have a tracing ID. ``` => SET search_path = public; SET => SELECT * FROM rds_aurora.limitless_get_last_trace_id(); transaction_identifier | command_identifier ------------------------+-------------------- | (1 row) ``` **Note** For VACUUM and ANALYZE queries, the duration statement isn't logged with the tracing ID. Therefore, `limitless_get_last_trace_id()` doesn't return the tracing ID. If VACUUM or ANALYZE is a long-running operation, you can use the following query to obtain the tracing ID for that operation: ``` SELECT * FROM rds_aurora.limitless_stat_activity WHERE distributed_tracing_id IS NOT NULL; ``` If the server stops before you can find the last tracing ID, you will have to search the PostgreSQL logs manually to find the tracing IDs in the logs from right before the failure. 1. Search for the tracing ID across the DB cluster logs using CloudWatch. Use [CloudWatch Insights](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/AnalyzingLogData.html) to query the DB cluster's log group, as shown in the following examples. + Query for a particular transaction identifier and all of the commands run inside it: ``` fields @timestamp, @message | filter @message like /10104661421959001813/ | sort @timestamp desc ``` + Query for a particular command identifier: ``` fields @timestamp, @message | filter @message like /101046614219590018130000000001/ | sort @timestamp desc ``` 1. Examine all of the logs across the DB cluster produced by the distributed query. ## Log examples The following examples show the use of query tracing. ### Correlating logs for error-prone queries In this example the `TRUNCATE` command is run on the `customers` table when that table doesn't exist. **Without query tracing** PostgreSQL log file on the coordinator router: ``` 2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[27503]:ERROR: failed to execute remote query 2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[27503]:DETAIL: relation "public.customers" does not exist 2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[27503]:CONTEXT: remote SQL command: truncate public.customers; 2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[27503]:STATEMENT: truncate customers; ``` PostgreSQL log file on a participant shard: ``` 2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[ 27503]:ERROR: failed to execute remote query 2023-09-26 04:03:19 UTC:[local]: postgres@postgres_limitless:[ 27503]:STATEMENT: truncate customers; ``` These logs are typical. They lack the precise identifiers needed to easily correlate queries across the DB cluster. **With query tracing** PostgreSQL log file on the coordinator router: ``` 2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:ERROR: failed to execute remote query 2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:DETAIL: relation "public.customers" does not exist 2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:CONTEXT: remote SQL command: truncate public.customers; 2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:STATEMENT: /* tid = 1126253375719408502700000000011 */ truncate customers; ``` PostgreSQL log file on a participant shard: ``` 2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:ERROR: failed to execute remote query 2023-09-26 04:03:19 UTC:[local]:postgres@postgres_limitless:[27503]:STATEMENT: /* tid = 1126253375719408502700000000010 */ truncate customers; ``` In the presence of query tracing, each log line is appended with a 31-digit unique identifier. Here, `1126253375719408502700000000011` and `1126253375719408502700000000010` represent the tracing IDs for the coordinator and participant nodes, respectively. + Transaction identifier – `11262533757194085027` + Command identifier: –`112625337571940850270000000001` + Node type identifier – The last digit, `1` or `0`, indicates a coordinator router and participant node, respectively. ### Correlating logs to find the query run time on various nodes In this example, the `log_min_duration_statement` parameter has been updated to `0` to print the duration for all queries. **Without query tracing** ``` 2024-01-15 07:28:46 UTC:[local]:postgres@postgres_limitless:[178322]:LOG: duration: 12.779 ms statement: select * from customers; ``` **With query tracing** PostgreSQL log file on the coordinator router: ``` 2024-01-15 07:32:08 UTC:[local]:postgres@postgres_limitless:[183877]:LOG: duration: 12.618 ms statement: /* tid = 0457669566240497088400000000011 */ select * from customers; ``` PostgreSQL log file on a participant shard: ``` 2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.279 ms statement: /* tid = 0457669566240497088400000000010 */ START TRANSACTION ISOLATION LEVEL READ COMMITTED 2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.249 ms parse : SELECT customer_id, fname, lname FROM public.customers 2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.398 ms bind /c1: SELECT customer_id, fname, lname FROM public.customers 2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.019 ms execute /c1: SELECT customer_id, fname, lname FROM public.customers 2024-01-15 07:32:08 UTC:localhost(46358):postgres@postgres_limitless:[183944]:LOG: duration: 0.073 ms statement: /* tid = 0457669566240497088400000000010 */ COMMIT TRANSACTION ``` # Distributed deadlocks in Aurora PostgreSQL Limitless Database In a DB shard group, deadlocks can occur between transactions that are distributed among different routers and shards. For example, two concurrent distributed transactions that span two shards are run, as shown in the following figure. ![\[Distributed deadlock on two distributed transactions.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_distributed_transaction_deadlock.png) The transactions lock tables and create wait events in the two shards as follows: 1. Distributed transaction 1: ``` UPDATE table SET value = 1 WHERE key = 'shard1_key'; ``` This holds a lock on shard 1. 1. Distributed transaction 2: ``` UPDATE table SET value = 2 WHERE key = 'shard2_key'; ``` This holds a lock on shard 2. 1. Distributed transaction 1: ``` UPDATE table SET value = 3 WHERE key = 'shard2_key'; ``` Distributed transaction 1 is waiting on shard 2. 1. Distributed transaction 2: ``` UPDATE table SET value = 4 WHERE key = 'shard1_key'; ``` Distributed transaction 2 is waiting on shard 1. In this scenario, neither shard 1 nor shard 2 sees the problem: transaction 1 is waiting for transaction 2 on shard 2, and transaction 2 is waiting for transaction 1 on shard 1. From a global view, transaction 1 is waiting for transaction 2, and transaction 2 is waiting for transaction 1. This situation where two transactions on two different shards are waiting for each other is called a distributed deadlock. Aurora PostgreSQL Limitless Database can detect and resolve distributed deadlocks automatically. A router in the DB shard group is notified when a transaction is waiting too long to acquire a resource. The router receiving the notification starts to collect the necessary information from all of the routers and shards within the DB shard group. The router then proceeds to end transactions that are participating in a distributed deadlock, until the rest of the transactions in the DB shard group can proceed without being blocked by each other. You receive the following error when your transaction was part of a distributed deadlock, and was then ended by the router: ``` ERROR: aborting transaction participating in a distributed deadlock ``` The `rds_aurora.limitless_distributed_deadlock_timeout` DB cluster parameter sets the time for each transaction to wait on a resource before notifying the router to check for a distributed deadlock. You can increase the parameter value if your workload is less prone to deadlock situations. The default is `1000` milliseconds (1 second). The distributed deadlock cycle is published to the PostgreSQL logs when a cross-node deadlock is found and resolved. The information about each process that's part of the deadlock includes the following: + Coordinator node that started the transaction + Virtual transaction ID (xid) of the transaction on the coordinator node, in the format `backend_id/backend_local_xid` + Distributed session ID of the transaction # Managing Aurora PostgreSQL Limitless Database The following topics describe how to manage your Aurora PostgreSQL Limitless Database DB clusters. **Topics** + [ ## Database and table size considerations ](#limitless-db-size) + [ # Reclaiming storage space by vacuuming ](limitless-vacuum.md) ## Database and table size considerations For Aurora PostgreSQL Limitless Database, on each shard a sharded table is divided into a number of table slices that varies depending on how many shards are available in the DB shard group. Each table slice can grow up to 32 TiB, but each shard has a maximum capacity of 128 TiB. Reference tables have a size limit of 32 TiB for the entire DB shard group. **Note** The maximum capacity of each node (router or shard) is 128 TiB, as this is the maximum capacity for an Aurora PostgreSQL DB cluster. The maximum number of relations per database (including tables, views, and indexes) in both Aurora PostgreSQL and Aurora PostgreSQL Limitless Database is 1,431,650,303. For more information, see [Appendix K. PostgreSQL limits](https://www.postgresql.org/docs/current/limits.html) in the PostgreSQL documentation and [Amazon Aurora size limits](CHAP_Limits.md#RDS_Limits.FileSize.Aurora). # Reclaiming storage space by vacuuming PostgreSQL Multiversion Concurrency Control (MVCC) helps to preserve data integrity by saving an internal copy of updated or deleted rows until a transaction is either committed or rolled back. These copies, also called *tuples*, can cause table bloat if they aren't cleaned up regularly. PostgreSQL instances order transactions by their transaction IDs, and PostgreSQL uses transaction ID–based MVCC to control tuple visibility and provide transaction isolation. Each transaction establishes a snapshot of data, and each tuple has a version. Both the snapshot and version are transaction ID–based. To clean up data, the `VACUUM` utility performs four key functions in PostgreSQL: + `VACUUM` – Removes expired row versions, making the space available for reuse. + `VACUUM FULL` – Provides complete defragmentation by removing dead row versions and compacting the tables, reducing the size and increasing efficiency. + `VACUUM FREEZE` – Protects against transaction ID wraparound issues by marking older row versions as frozen. + `VACUUM ANALYZE` – Removes dead row versions and updates the database's query planning statistics. It's a combination of the `VACUUM` and `ANALYZE` functions. For more information on how `ANALYZE` works in Aurora PostgreSQL Limitless Database, see [ANALYZE](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.ANALYZE). As with MVCC, vacuuming in Aurora PostgreSQL is transaction ID–based. If there's an ongoing transaction when vacuuming starts, rows that are still visible to that transaction aren't removed. For more information on the `VACUUM` utility, see [VACUUM](https://www.postgresql.org/docs/current/sql-vacuum.html) in the PostgreSQL documentation. For more information about `VACUUM` support in Aurora PostgreSQL Limitless Database, see [VACUUM](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.VACUUM). **Topics** + [ ## AUTOVACUUM ](#limitless-autovacuum) + [ ## Time-based vacuuming in Aurora PostgreSQL Limitless Database ](#limitless-vacuum.time-based) + [ ## Using database statistics for vacuuming ](#limitless-vacuum.stats) + [ ## Differences in vacuuming behavior between Aurora PostgreSQL and Aurora PostgreSQL Limitless Database ](#limitless-vacuum-limitations) ## AUTOVACUUM Aurora PostgreSQL uses the `VACUUM` and `AUTOVACUUM` utilities to remove unneeded tuples. The underlying mechanism for `AUTOVACUUM` and manual `VACUUM` is the same; the only difference is the automation. `AUTOVACUUM` in Aurora PostgreSQL and Aurora PostgreSQL Limitless Database is a combination of the `VACUUM` and `ANALYZE` utilities. `AUTOVACUUM` determines which databases and tables to clean up, according to a predefined rule, such as the percentage of dead tuples and the number of inserts. For example, `AUTOVACUUM` "wakes up" periodically to perform cleanup. The interval is controlled by the `autovacuum_naptime` parameter. The default value is 1 minute. The default values for `AUTOVACUUM` and `VACUUM` configuration parameters are the same for Aurora PostgreSQL Limitless Database as for Aurora PostgreSQL. The `AUTOVACUUM` daemon, if enabled, automatically issues `ANALYZE` commands whenever the content of a table has changed sufficiently. In Aurora PostgreSQL Limitless Database, `AUTOVACUUM` issues `ANALYZE` on both routers and shards. For more information about the `AUTOVACUUM` daemon and table storage parameters associated with `AUTOVACUUM`, see [The autovacuum daemon](https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM ) and [Storage parameters](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html) in the PostgreSQL documentation. ## Time-based vacuuming in Aurora PostgreSQL Limitless Database Aurora PostgreSQL Limitless Database is a distributed system, meaning that multiple instances can be involved in a transaction. Therefore, transaction ID–based visibility doesn't apply. Instead, Aurora PostgreSQL Limitless Database uses *time-based* visibility, because transaction IDs aren't “unified” across instances, but time can be “unified” across instances. Each transaction snapshot and each tuple version obeys the time instead of the transaction ID. To be more specific, a transaction snapshot has a snapshot start time, and a tuple has a creation time (when an `INSERT` or `UPDATE` happens) and a deletion time (when a `DELETE` happens). To maintain data consistency across the instances in the DB shard group, Aurora PostgreSQL Limitless Database has to make sure that vacuuming doesn't remove any tuples that are still visible to any active transaction in the DB shard group. Therefore, vacuuming in Aurora PostgreSQL Limitless Database is also time-based. Other aspects of `VACUUM` remain the same, including that to run `VACUUM` on a particular table, a user must have access to that table. **Note** We strongly recommend that you don't leave transactions open for long periods of time. Time-based vacuuming consumes more memory than transaction ID–based vacuuming. The following example illustrates how time-based vacuuming works. 1. A customer table is distributed across four shards. 1. Transaction 1 starts with a repeatable read, and targets only one shard (shard 1). This transaction remains open. Transaction 1 is older than any other transaction started after it. 1. Transaction 2 starts later, and deletes all tuples from a table, then commits. 1. If `AUTOVACUUM` or manual `VACUUM` tries to clean up dead tuples (dead due to transaction 2), it doesn't remove anything. This is true not only for shard 1, but also for shards 2–4, because transaction 1 might still need to access these tuples. They're still visible to transaction 1 because of MVCC. The last step is achieved through synchronization, so that all shards are aware of transaction 1, even though transaction 1 doesn't touch all of them. ## Using database statistics for vacuuming To get information on tuples that you might need to clean up, use the [limitless\$1stat\$1all\$1tables](limitless-monitoring-views.md#limitless_stat_all_tables) view, which works similarly to [pg\$1stat\$1all\$1tables](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW). The following example queries the view. ``` SELECT * FROM rds_aurora.limitless_stat_all_tables WHERE relname LIKE '%customer%'; ``` Similarly, for database statistics use [limitless\$1stat\$1database](limitless-monitoring-views.md#limitless_stat_database) instead of [pg\$1stat\$1database](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW), and [limitless\$1stat\$1activity](limitless-monitoring-views.md#limitless_stat_activity) instead of [pg\$1stat\$1activity](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW). To check table disk usage, use the [limitless\$1stat\$1relation\$1sizes](limitless-monitoring-functions.md#limitless_stat_relation_sizes) function, which works similarly to [pg\$1relation\$1size](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT). The following example queries the function. ``` SELECT * FROM rds_aurora.limitless_stat_relation_sizes('public','customer'); ``` To track the progress of a `VACUUM` operation on Aurora PostgreSQL Limitless Database, use the [limitless\$1stat\$1progress\$1vacuum](limitless-monitoring-views.md#limitless_stat_progress_vacuum) view instead of [pg\$1stat\$1progress\$1vacuum](https://www.postgresql.org/docs/15/progress-reporting.html#VACUUM-PROGRESS-REPORTING). The following example queries the view. ``` SELECT * FROM rds_aurora.limitless_stat_progress_vacuum; ``` For more information, see [Aurora PostgreSQL Limitless Database views](limitless-monitoring-views.md) and [Aurora PostgreSQL Limitless Database functions](limitless-monitoring-functions.md). ## Differences in vacuuming behavior between Aurora PostgreSQL and Aurora PostgreSQL Limitless Database Some other differences between Aurora PostgreSQL and Aurora PostgreSQL Limitless Database in how vacuuming works are the following: + Aurora PostgreSQL performs `VACUUM` operations on transaction IDs up to the oldest ongoing transaction. If there's no ongoing transaction in the database, `VACUUM` performs the operation until the last transaction. + Aurora PostgreSQL Limitless Database synchronizes the oldest time snapshot every 10 seconds. Therefore, `VACUUM` might not perform the operation on any transactions that were run within the last 10 seconds. For information on support for `VACUUM` in Aurora PostgreSQL Limitless Database, see [VACUUM](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.VACUUM) in the [Aurora PostgreSQL Limitless Database referenceLimitless Database reference](limitless-reference.md). # Monitoring Aurora PostgreSQL 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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. # Instance-specific performance and resource monitoring Monitoring on instance level is key to understand connection skew, workload skew and data skew, as well as when to add routers or split shards to scale up for higher throughput with retained latency. ## Overview When your application issues a query against , that request traverses a sophisticated distributed system before returning results. A seemingly simple `SELECT` statement might touch multiple database instances, each playing a distinct role in processing your request. Understanding this journey—and the instances that power it—transforms how you design applications, interpret monitoring data, and diagnose performance issues. This guide provides deep technical insight into instance architecture: + Limitless Architecture refresher, router and shards + When and how to scale each instance type to meet your performance and capacity requirements + How to monitor, troubleshoot, and optimize instance-level performance + Best practices for application design that leverage the distributed architecture effectively ## Instance architecture fundamentals achieves horizontal scalability through functional separation across two specialized instance types: + **Router instances** provide the orchestration layer—they accept client connections, analyze queries, coordinate distributed operations, and aggregate results. Routers are stateless, meaning they don't store data and can be added or removed without data migration. + **Shard instances** provide the data and compute layer—they store table data, execute queries against local data, and handle transactions. Shards are stateful, each owning a specific subset of your data determined by consistent hashing. This separation allows to scale connection handling, query coordination, and data storage independently based on your workload characteristics. ### Router and shard comparison | Characteristic | Router Instances | Shard Instances | | --- | --- | --- | | Primary Role | Query coordination and distribution | Data storage and query execution | | State | Stateless (no data storage) | Stateful (owns data) | | Scalability | Add/remove instantly | Requires data rebalancing | | Resource Focus | CPU for coordination; moderate memory | CPU for queries; high memory for cache | | Scaling Trigger | High connection count, distributed txn rate | High CPU, data volume, query throughput | ## Monitoring instance performance Understanding instance-level performance is critical for operating effectively. Instance-specific monitoring reveals the distribution patterns that impact performance: connection skew, workload skew, and data skew. ### Detecting skew In an ideal deployment, workload and resources distribute evenly across instances. In practice, applications frequently experience skew—uneven distribution that concentrates load on specific instances. Three types of skew to monitor: + **Connection skew**: Uneven distribution of client connections across routers + **Workload skew**: Uneven query load across shards due to hot shard keys + **Data skew**: Uneven data volume across shards due to shard key frequency ### Database Insights load distribution The fastest way to assess instance-level health is Database Insights' Load Distribution view, which provides immediate visibility into how Active Sessions distribute across instances. To access Load Distribution: 1. Navigate to RDS Console → Your Limitless Cluster 1. Select "Performance Insights" tab 1. Click "Load Distribution" section **Healthy pattern:** Load distributed relatively evenly across instances + Routers may show slightly higher AAS than shards (coordination overhead) + Shard AAS values within 20% of each other indicates good balance **Concerning pattern:** Significant concentration on specific instances + One router with >70% of router load → Connection skew + One shard with >50% of shard load → Workload or data skew + Large variance between shards → Investigate shard key distribution ### CloudWatch metrics For deeper analysis beyond Database Insights, CloudWatch provides instance-specific metrics that reveal resource utilization patterns. The `ServerlessDatabaseCapacity` metric with dimension `DBShardGroupInstance` shows ACU consumption per instance, providing the most direct view of resource utilization. **When to investigate:** + Router ACU variance >30% → Connection skew or cross-shard workload concentration + Shard ACU variance >40% → Data or workload skew + Any instance consistently at max ACU → Capacity constraint ## Router monitoring and troubleshooting Routers can experience performance issues from two primary causes: uneven connection distribution and cross-shard workload concentration. ### Unevenly distributed sessions **Symptom:** One router handles disproportionate share of connections **Root cause:** DNS caching causes multiple connection requests to resolve to the same router endpoint. **Most common during:** + Benchmarking with tools like pgbench + Connection pool initialization (many connections established rapidly) + Application server restarts **Remedies:** + Make sure to use the Limitless endpoint specified in the console + Manual balancing: extract router endpoints and connect different applications to different routers + For libpq applications use the feature `LOADBALANCEHOSTS` + For JDBC applications use the Limitless connection Plugin + Use an NLB to manage sessions and distributions ## Shard monitoring and troubleshooting Shards experience performance issues from three primary causes: resource constraints, data skew, and workload skew. ### Shard resource utilization A shard with popular shard keys will have more data and higher workloads. This manifests as resource utilization, i.e. the instance will consume more ACUs. **Remediation strategies:** 1. **Re-assess shard key selection:** Review shard key cardinality and access patterns. Consider composite shard keys for better distribution. 1. **Split the shard:** Distribute load across more shard instances **When to split shards:** + Single shard consistently at >80% max ACU + Query throughput limited by single shard capacity ### Shard data volumes Use SQL functions to query data volumes: ``` SELECT subcluster_id, subcluster_type, pg_size_pretty(db_size) FROM rds_aurora.limitless_stat_database_size('postgres_limitless') ORDER BY 1; ``` To view per-table and per-shard data: ``` SELECT * FROM rds_aurora.limitless_stat_relation_sizes('public', 'table_name'); ``` ### Resolving uneven utilization When workload or data skew concentrates on specific shards, splitting shards redistributes load across more instances. Important considerations: + Which shard keys to move cannot be controlled + There is no way of undoing a split without recovering to a manual snapshot taken before the split + All instances, including a new shard, consume instance minimum ACU when idling Splitting shards allow further scaling, and consecutive shard splits is the path to higher throughput and further scaling, while retaining low latency. ## Limitations Be aware of these operational constraints: **Router limitations:** + **Routers cannot be removed** - Once added, routers remain in cluster + Plan router additions carefully to avoid unnecessary baseline costs **Shard limitations:** + **Shards cannot be merged** - Shard splits are one-way operations + Only recovery option: Restore from snapshot taken before split **Mitigation strategies:** + Start with minimum viable instance count + Add capacity incrementally as needed + Take snapshots before major topology changes + Monitor baseline costs as cluster grows # Backing up and restoring Aurora PostgreSQL Limitless Database You can back up and restore a DB cluster that uses Aurora PostgreSQL Limitless Database. **Contents** + [ ## Backing up a DB cluster that uses Aurora PostgreSQL Limitless Database ](#limitless-backup) + [ ### Creating a DB cluster snapshot ](#limitless-backup-snapshot) + [ ## Restoring a DB cluster that uses Aurora PostgreSQL Limitless Database ](#limitless-restore) + [ ### Restoring a DB cluster from a DB snapshot ](#limitless-restore-snapshot) + [ ### Restoring a DB cluster using point-in-time recovery ](#limitless-restore-pitr) + [ ## PostgreSQL backup and restore utilities not supported ](#limitless-backup-utilities) ## Backing up a DB cluster that uses Aurora PostgreSQL Limitless Database Backing up a DB cluster with Aurora PostgreSQL Limitless Database has similarities and differences in functionality compared to backing up a standard Aurora DB cluster. + When you take a manual DB cluster snapshot of an Aurora DB cluster that uses Limitless Database, the snapshot includes data from the DB shard group. + Continuous backups include data from the DB shard group. + Automated daily snapshots include data from the DB shard group. + Copying DB cluster snapshots is supported. For more information, see [DB cluster snapshot copying](aurora-copy-snapshot.md). + Sharing DB cluster snapshots is supported. For more information, see [Sharing a DB cluster snapshot](aurora-share-snapshot.md). + You can't use the `pg_dump` or `pg_dumpall` utility to back up databases in the DB shard group. + Taking final snapshots when deleting DB clusters is supported for Aurora PostgreSQL Limitless Database. + Retaining automated backups when deleting DB clusters isn't supported for Aurora PostgreSQL Limitless Database. ### Creating a DB cluster snapshot You create an Aurora PostgreSQL Limitless Database DB cluster snapshot in the same way as for a standard Aurora DB cluster, as shown in the following AWS CLI example: ``` aws rds create-db-cluster-snapshot \ --db-cluster-identifier my-db-cluster \ --db-cluster-snapshot-identifier my-db-cluster-snapshot ``` For more information on backing up DB clusters, see [Overview of backing up and restoring an Aurora DB cluster](Aurora.Managing.Backups.md). ## Restoring a DB cluster that uses Aurora PostgreSQL Limitless Database Restoring a DB cluster with Aurora PostgreSQL Limitless Database has similarities and differences in functionality compared to restoring a standard Aurora DB cluster. + You can restore a Limitless Database DB cluster only from a source DB cluster that uses a DB engine version compatible with Limitless Database, such as `16.4-limitless`. + When you restore a DB cluster from a manual snapshot of a DB cluster that uses Limitless Database, the entire DB cluster storage is restored. This includes the storage of the DB shard group. You must create a DB shard group to access the storage for your Limitless Database. + You can restore a DB cluster using point-in-time recovery (PITR) to any point within the retention period. The restored DB cluster includes the storage of the DB shard group. You must create a DB shard group to access the storage for your Limitless Database. + PITR isn't supported for deleted Aurora PostgreSQL Limitless Database DB clusters. + When you restore a DB cluster from an automated daily snapshot, the storage for the DB shard group is also restored. + When you restore an Aurora PostgreSQL Limitless Database DB cluster, you must enable Enhanced Monitoring and Performance Insights. Make sure to include the Performance Insights KMS key ID. After you restore an Aurora PostgreSQL Limitless Database DB cluster, make sure to verify its functionality by running your queries on it. ### Restoring a DB cluster from a DB snapshot The following AWS CLI examples show how to restore an Aurora PostgreSQL Limitless Database DB cluster from a DB cluster snapshot. You must use the `16.4-limitless` DB engine version. **To restore a Limitless Database DB cluster from a DB cluster snapshot** 1. Restore the DB cluster: ``` aws rds restore-db-cluster-from-snapshot \ --db-cluster-identifier my-new-db-cluster \ --snapshot-identifier my-db-cluster-snapshot \ --engine aurora-postgresql \ --engine-version 16.4-limitless \ --enable-performance-insights \ --performance-insights-retention-period 31 \ --performance-insights-kms-key-id arn:aws:kms:us-east-1:123456789012:key/1234abcd-12ab-34cd-56ef-1234567890ab \ --monitoring-interval 5 \ --monitoring-role-arn arn:aws:iam::123456789012:role/EMrole ``` 1. Create the DB shard group: ``` aws rds create-db-shard-group \ --db-cluster-identifier my-new-db-cluster \ --db-shard-group-identifier my-new-DB-shard-group \ --max-acu 1000 ``` For more information, see [Adding a DB shard group to an existing Aurora PostgreSQL Limitless Database DB cluster](limitless-shard-add.md). For more information on restoring Aurora DB clusters from DB cluster snapshots, see [Restoring from a DB cluster snapshot](aurora-restore-snapshot.md). ### Restoring a DB cluster using point-in-time recovery The following AWS CLI examples show how to restore an Aurora PostgreSQL Limitless Database DB cluster using point-in-time recovery (PITR). **To restore a Limitless Database DB cluster using PITR** 1. Restore the DB cluster: ``` aws rds restore-db-cluster-to-point-in-time \ --source-db-cluster-identifier my-db-cluster \ --db-cluster-identifier my-new-db-cluster \ --use-latest-restorable-time \ --enable-performance-insights \ --performance-insights-retention-period 31 \ --performance-insights-kms-key-id arn:aws:kms:us-east-1:123456789012:key/1234abcd-12ab-34cd-56ef-1234567890ab \ --monitoring-interval 5 \ --monitoring-role-arn arn:aws:iam::123456789012:role/EMrole ``` 1. Create the DB shard group: ``` aws rds create-db-shard-group \ --db-cluster-identifier my-new-db-cluster \ --db-shard-group-identifier my-new-DB-shard-group \ --max-acu 1000 ``` For more information, see [Adding a DB shard group to an existing Aurora PostgreSQL Limitless Database DB cluster](limitless-shard-add.md). For more information on PITR, see [Restoring a DB cluster to a specified time](aurora-pitr.md). ## PostgreSQL backup and restore utilities not supported The following PostgreSQL utilities aren't supported for either the primary DB cluster or the DB shard group: + `pg_dump` + `pg_dumpall` + `pg_restore` While you might be able to use them by open source binaries or alternative methods, doing so could yield inconsistent results. # Upgrading Amazon Aurora PostgreSQL Limitless Database The following apply to upgrading Aurora PostgreSQL Limitless Database: + Minor version upgrades are supported. + Patching Aurora PostgreSQL Limitless Database is supported. Patches appear as pending maintenance to be applied during your maintenance window. ## Upgrading DB clusters that use Amazon Aurora PostgreSQL Limitless Database You upgrade a DB cluster by modifying it and choosing a new DB engine version. You can use the AWS Management Console or the AWS CLI. ### Console **To upgrade your Limitless Database DB cluster** 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. Navigate to the **Databases** page. 1. Select the Limitless Database DB cluster that you want to upgrade. 1. Choose **Modify**. The **Modify DB cluster** page displays. 1. For **DB engine version**, choose the new DB engine version, for example **Aurora PostgreSQL with Limitless Database (Compatible with PostgreSQL 16.6)**. 1. Choose **Continue**. 1. On the summary page, select whether to apply the changes immediately or during the next scheduled maintenance window, then choose **Modify cluster**. ### CLI Use the [modify-db-cluster](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/rds/modify-db-cluster.html) AWS CLI command, as shown in the following example. ``` aws rds modify-db-cluster \ --db-cluster-identifier my-sv2-cluster \ --engine-version 16.6-limitless \ --apply-immediately ``` # Aurora PostgreSQL Limitless Database reference We provide the following reference topics for Aurora PostgreSQL Limitless Database. **Topics** + [ # Supported and unsupported Data Definition Language (DDL) SQL commands ](limitless-reference.DDL-support.md) + [ # DDL limitations and other information for Aurora PostgreSQL Limitless Database ](limitless-reference.DDL-limitations.md) + [ # Supported and unsupported Data Manipulation Language (DML) and query processing SQL commands ](limitless-reference.DML-support.md) + [ # DML limitations and other information for Aurora PostgreSQL Limitless Database ](limitless-reference.DML-limitations.md) + [ # Variables in Aurora PostgreSQL Limitless Database ](limitless-reference.variables.md) + [ # DB cluster parameters in Aurora PostgreSQL Limitless Database ](limitless-reference.DBCparams.md) # Supported and unsupported Data Definition Language (DDL) SQL commands The following table lists the DDL commands that are supported and not supported by Aurora PostgreSQL Limitless Database, with references to limitations or more information. | Command | Supported? | Limitations or more information | | --- | --- | --- | | ALTER AGGREGATE | No | Not applicable | | ALTER COLLATION | Yes | None | | ALTER CONVERSION | Yes | None | | ALTER DATABASE | No | Not applicable | | ALTER DEFAULT PRIVILEGES | No | Not applicable | | ALTER DOMAIN | No | Not applicable | | ALTER EVENT TRIGGER | No | Not applicable | | ALTER EXTENSION | Yes | [Extensions](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Extensions) | | ALTER FOREIGN DATA WRAPPER | No | Not applicable | | ALTER FOREIGN TABLE | No | Not applicable | | ALTER FUNCTION | Yes | [Functions](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Functions) | | ALTER GROUP | Yes | None | | ALTER INDEX | Yes | None | | ALTER LANGUAGE | No | Not applicable | | ALTER LARGE OBJECT | No | Not applicable | | ALTER MATERIALIZED VIEW | No | Not applicable | | ALTER OPERATOR | Yes | None | | ALTER OPERATOR CLASS | Yes | None | | ALTER OPERATOR FAMILY | Yes | None | | ALTER POLICY | No | Not applicable | | ALTER PROCEDURE | Yes | None | | ALTER PUBLICATION | No | Not applicable | | ALTER ROLE | Yes | None | | ALTER ROUTINE | No | Not applicable | | ALTER RULE | No | Not applicable | | ALTER SCHEMA | Yes | None | | ALTER SEQUENCE | Yes | [Sequences](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Sequences) | | ALTER SERVER | No | Not applicable | | ALTER STATISTICS | No | Not applicable | | ALTER SUBSCRIPTION | No | Not applicable | | ALTER SYSTEM | No | Not applicable | | ALTER TABLE | Yes | [ALTER TABLE](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.ALTER_TABLE) | | ALTER TABLESPACE | No | Not applicable | | ALTER TEXT SEARCH CONFIGURATION | No | Not applicable | | ALTER TEXT SEARCH DICTIONARY | No | Not applicable | | ALTER TEXT SEARCH PARSER | No | Not applicable | | ALTER TEXT SEARCH TEMPLATE | No | Not applicable | | ALTER TRIGGER | No | Not applicable | | ALTER TYPE | Yes | None | | ALTER USER | Yes | None | | ALTER USER MAPPING | No | Not applicable | | ALTER VIEW | Yes | None | | COMMENT | No | Not applicable | | CREATE ACCESS METHOD | No | Not applicable | | CREATE AGGREGATE | No | Not applicable | | CREATE CAST | Yes | None | | CREATE COLLATION | Yes | None | | CREATE CONVERSION | Yes | None | | CREATE DATABASE | Yes | [CREATE DATABASE](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.CREATE_DATABASE) | | CREATE DOMAIN | No | Not applicable | | CREATE EVENT TRIGGER | No | Not applicable | | CREATE EXTENSION | Yes | [Extensions](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Extensions) | | CREATE FOREIGN DATA WRAPPER | No | Not applicable | | CREATE FOREIGN TABLE | No | Not applicable | | CREATE FUNCTION | Yes | [Functions](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Functions) | | CREATE GROUP | Yes | None | | CREATE INDEX | Yes | [CREATE INDEX](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.CREATE_INDEX) | | CREATE LANGUAGE | No | Not applicable | | CREATE MATERIALIZED VIEW | No | Not applicable | | CREATE OPERATOR | Yes | None | | CREATE OPERATOR CLASS | Yes | None | | CREATE OPERATOR FAMILY | Yes | None | | CREATE POLICY | Yes | None | | CREATE PROCEDURE | Yes | None | | CREATE PUBLICATION | No | Not applicable | | CREATE ROLE | Yes | None | | CREATE RULE | No | Not applicable | | CREATE SCHEMA | Yes | [CREATE SCHEMA](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.CREATE_SCHEMA) | | CREATE SEQUENCE | Yes | [Sequences](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Sequences) | | CREATE SERVER | No | Not applicable | | CREATE STATISTICS | No | Not applicable | | CREATE SUBSCRIPTION | No | Not applicable | | CREATE TABLE | Yes | [CREATE TABLE](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.CREATE_TABLE) | | CREATE TABLE AS | Yes | [CREATE TABLE AS](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.CREATE_TABLE_AS) | | CREATE TABLESPACE | No | Not applicable | | CREATE TEMPORARY TABLE | No | Not applicable | | CREATE TEMPORARY TABLE AS | No | Not applicable | | CREATE TEXT SEARCH CONFIGURATION | No | Not applicable | | CREATE TEXT SEARCH DICTIONARY | No | Not applicable | | CREATE TEXT SEARCH PARSER | No | Not applicable | | CREATE TEXT SEARCH TEMPLATE | No | Not applicable | | CREATE TRANSFORM | No | Not applicable | | CREATE TRIGGER | No | Not applicable | | CREATE TYPE | Yes | None | | CREATE USER | Yes | None | | CREATE USER MAPPING | No | Not applicable | | CREATE VIEW | Yes | None | | DROP ACCESS METHOD | No | Not applicable | | DROP AGGREGATE | Yes | None | | DROP CAST | Yes | None | | DROP COLLATION | Yes | None | | DROP CONVERSION | Yes | None | | DROP DATABASE | Yes | [DROP DATABASE](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.DROP_DATABASE) | | DROP DOMAIN | No | Not applicable | | DROP EVENT TRIGGER | No | Not applicable | | DROP EXTENSION | Yes | [Extensions](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Extensions) | | DROP FOREIGN DATA WRAPPER | No | Not applicable | | DROP FOREIGN TABLE | No | Not applicable | | DROP FUNCTION | Yes | [Functions](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Functions) | | DROP GROUP | Yes | None | | DROP INDEX | Yes | None | | DROP LANGUAGE | No | Not applicable | | DROP MATERIALIZED VIEW | No | Not applicable | | DROP OPERATOR | Yes | None | | DROP OPERATOR CLASS | Yes | None | | DROP OPERATOR FAMILY | Yes | None | | DROP OWNED | No | Not applicable | | DROP POLICY | No | Not applicable | | DROP PROCEDURE | Yes | None | | DROP PUBLICATION | No | Not applicable | | DROP ROLE | Yes | None | | DROP ROUTINE | No | Not applicable | | DROP RULE | No | Not applicable | | DROP SCHEMA | Yes | None | | DROP SEQUENCE | Yes | None | | DROP SERVER | No | Not applicable | | DROP STATISTICS | No | Not applicable | | DROP SUBSCRIPTION | No | None | | DROP TABLE | Yes | None | | DROP TABLESPACE | No | Not applicable | | DROP TEXT SEARCH CONFIGURATION | No | Not applicable | | DROP TEXT SEARCH DICTIONARY | No | Not applicable | | DROP TEXT SEARCH PARSER | No | Not applicable | | DROP TEXT SEARCH TEMPLATE | No | Not applicable | | DROP TRANSFORM | No | Not applicable | | DROP TRIGGER | No | Not applicable | | DROP TYPE | Yes | None | | DROP USER | Yes | None | | DROP USER MAPPING | No | Not applicable | | DROP VIEW | Yes | None | | GRANT | Yes | None | | REASSIGN OWNED | No | Not applicable | | REVOKE | Yes | None | | SECURITY LABEL | No | Not applicable | | SELECT INTO | Yes | [SELECT INTO](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.SELECT_INTO) | | SET | Yes | None | | SET CONSTRAINTS | No | Not applicable | | SET ROLE | Yes | None | | SET SESSION AUTHORIZATION | Yes | None | | SET TRANSACTION | Yes | None | | TRUNCATE | Yes | None | # DDL limitations and other information for Aurora PostgreSQL Limitless Database The following topics describe limitations or provide more information for DDL SQL commands in Aurora PostgreSQL Limitless Database. **Topics** + [ ## ALTER TABLE ](#limitless-reference.DDL-limitations.ALTER_TABLE) + [ ## CREATE DATABASE ](#limitless-reference.DDL-limitations.CREATE_DATABASE) + [ ## CREATE INDEX ](#limitless-reference.DDL-limitations.CREATE_INDEX) + [ ## CREATE SCHEMA ](#limitless-reference.DDL-limitations.CREATE_SCHEMA) + [ ## CREATE TABLE ](#limitless-reference.DDL-limitations.CREATE_TABLE) + [ ## CREATE TABLE AS ](#limitless-reference.DDL-limitations.CREATE_TABLE_AS) + [ ## DROP DATABASE ](#limitless-reference.DDL-limitations.DROP_DATABASE) + [ ## SELECT INTO ](#limitless-reference.DDL-limitations.SELECT_INTO) + [ ## Constraints ](#limitless-reference.DDL-limitations.Constraints) + [ ## Default values ](#limitless-reference.DDL-limitations.DefaultValues) + [ ## Extensions ](#limitless-reference.DDL-limitations.Extensions) + [ ## Foreign keys ](#limitless-reference.DDL-limitations.FKs) + [ ## Functions ](#limitless-reference.DDL-limitations.Functions) + [ ## Sequences ](#limitless-reference.DDL-limitations.Sequences) ## ALTER TABLE The `ALTER TABLE` command is generally supported in Aurora PostgreSQL Limitless Database. For more information, see [ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html) in the PostgreSQL documentation. ### Limitations `ALTER TABLE` has the following limitations for supported options. **Removing a column** + On sharded tables, you can't remove columns that are part of the shard key. + On reference tables, you can't remove primary key columns. **Changing a column's data type** + The `USING` expression isn't supported. + On sharded tables, you can't change the type for columns that are part of the shard key. **Adding or removing a constraint** For details on what isn't supported, see [Constraints](#limitless-reference.DDL-limitations.Constraints). **Changing a column's default value** Default values are supported. For more information, see [Default values](#limitless-reference.DDL-limitations.DefaultValues). ### Unsupported options Some options aren't supported because they depend on unsupported features, such as triggers. The following table-level options for `ALTER TABLE` aren't supported: + `ALL IN TABLESPACE` + `ATTACH PARTITION` + `DETACH PARTITION` + `ONLY` flag + `RENAME CONSTRAINT` The following column-level options for `ALTER TABLE` aren't supported: + ADD GENERATED + DROP EXPRESSION [ IF EXISTS ] + DROP IDENTITY [ IF EXISTS ] + RESET + RESTART + SET + SET COMPRESSION + SET STATISTICS ## CREATE DATABASE In Aurora PostgreSQL Limitless Database, only limitless databases are supported. While `CREATE DATABASE` is running, databases that were successfully created in one or more nodes might fail in other nodes, because database creation is a nontransactional operation. In this case, the database objects that were successfully created are automatically removed from all of the nodes within a predetermined amount of time to keep consistency in the DB shard group. During this time, re-creation of a database with the same name might result in an error indicating that the database already exists. The following options are supported: + Collation: ``` CREATE DATABASE name WITH [LOCALE = locale] [LC_COLLATE = lc_collate] [LC_CTYPE = lc_ctype] [ICU_LOCALE = icu_locale] [ICU_RULES = icu_rules] [LOCALE_PROVIDER = locale_provider] [COLLATION_VERSION = collation_version]; ``` + `CREATE DATABASE WITH OWNER`: ``` CREATE DATABASE name WITH OWNER = user_name; ``` The following options aren't supported: + `CREATE DATABASE WITH TABLESPACE`: ``` CREATE DATABASE name WITH TABLESPACE = tablespace_name; ``` + `CREATE DATABASE WITH TEMPLATE`: ``` CREATE DATABASE name WITH TEMPLATE = template; ``` ## CREATE INDEX `CREATE INDEX CONCURRENTLY` is supported for sharded tables: ``` CREATE INDEX CONCURRENTLY index_name ON table_name(column_name); ``` `CREATE UNIQUE INDEX` is supported for all table types: ``` CREATE UNIQUE INDEX index_name ON table_name(column_name); ``` `CREATE UNIQUE INDEX CONCURRENTLY` isn't supported: ``` CREATE UNIQUE INDEX CONCURRENTLY index_name ON table_name(column_name); ``` For more information, see [UNIQUE](#unique-constraint). For general information on creating indexes, see [CREATE INDEX](https://www.postgresql.org/docs/current/sql-createindex.html) in the PostgreSQL documentation. **Showing indexes** Not all indexes are visible on routers when you use `\d table_name` or similar commands. Instead, use the `pg_catalog.pg_indexes` view to get indexes, as shown in the following example. ``` SET rds_aurora.limitless_create_table_mode='sharded'; SET rds_aurora.limitless_create_table_shard_key='{"id"}'; CREATE TABLE items (id int PRIMARY KEY, val int); CREATE INDEX items_my_index on items (id, val); postgres_limitless=> SELECT * FROM pg_catalog.pg_indexes WHERE tablename='items'; schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+----------------+------------+------------------------------------------------------------------------ public | items | items_my_index | | CREATE INDEX items_my_index ON ONLY public.items USING btree (id, val) public | items | items_pkey | | CREATE UNIQUE INDEX items_pkey ON ONLY public.items USING btree (id) (2 rows) ``` ## CREATE SCHEMA `CREATE SCHEMA` with a schema element isn't supported: ``` CREATE SCHEMA my_schema CREATE TABLE (column_name INT); ``` This generates an error similar to the following: ``` ERROR: CREATE SCHEMA with schema elements is not supported ``` ## CREATE TABLE Relations in `CREATE TABLE` statements aren't supported, for example: ``` CREATE TABLE orders (orderid int, customerId int, orderDate date) WITH (autovacuum_enabled = false); ``` `IDENTITY` columns aren't supported, for example: ``` CREATE TABLE orders (orderid INT GENERATED ALWAYS AS IDENTITY); ``` Aurora PostgreSQL Limitless Database supports up to 54 characters for sharded table names. ## CREATE TABLE AS To create a table using `CREATE TABLE AS`, you must use the `rds_aurora.limitless_create_table_mode` variable. For sharded tables, you must also use the `rds_aurora.limitless_create_table_shard_key` variable. For more information, see [Creating limitless tables by using variables](limitless-creating-config.md). ``` -- Set the variables. SET rds_aurora.limitless_create_table_mode='sharded'; SET rds_aurora.limitless_create_table_shard_key='{"a"}'; CREATE TABLE ctas_table AS SELECT 1 a; -- "source" is the source table whose columns and data types are used to create the new "ctas_table2" table. CREATE TABLE ctas_table2 AS SELECT a,b FROM source; ``` You can't use `CREATE TABLE AS` to create reference tables, because they require primary key constraints. `CREATE TABLE AS` doesn't propagate primary keys to new tables. For general information, see [CREATE TABLE AS](https://www.postgresql.org/docs/current/sql-createtableas.html) in the PostgreSQL documentation. ## DROP DATABASE You can drop databases that you've created. The `DROP DATABASE` command runs asynchronously in the background. While it's running, you will receive an error if you try to create a new database with the same name. ## SELECT INTO `SELECT INTO` is functionally similar to [CREATE TABLE AS](#limitless-reference.DDL-limitations.CREATE_TABLE_AS). You must use the `rds_aurora.limitless_create_table_mode` variable. For sharded tables, you must also use the `rds_aurora.limitless_create_table_shard_key` variable. For more information, see [Creating limitless tables by using variables](limitless-creating-config.md). ``` -- Set the variables. SET rds_aurora.limitless_create_table_mode='sharded'; SET rds_aurora.limitless_create_table_shard_key='{"a"}'; -- "source" is the source table whose columns and data types are used to create the new "destination" table. SELECT * INTO destination FROM source; ``` Currently, the `SELECT INTO` operation is performed through the router, not directly through the shards. Therefore, performance can be slow. For general information, see [SELECT INTO](https://www.postgresql.org/docs/current/sql-selectinto.html) in the PostgreSQL documentation. ## Constraints The following limitations apply to constraints in Aurora PostgreSQL Limitless Database. **CHECK** Simple constraints that involve comparison operators with literals are supported. More complex expressions and constraints that require function validations aren't supported, as shown in the following examples. ``` CREATE TABLE my_table ( id INT CHECK (id > 0) -- supported , val INT CHECK (val > 0 AND val < 1000) -- supported , tag TEXT CHECK (length(tag) > 0) -- not supported: throws "Expression inside CHECK constraint is not supported" , op_date TIMESTAMP WITH TIME ZONE CHECK (op_date <= now()) -- not supported: throws "Expression inside CHECK constraint is not supported" ); ``` You can give constraints explicit names, as shown in the following example. ``` CREATE TABLE my_table ( id INT CONSTRAINT positive_id CHECK (id > 0) , val INT CONSTRAINT val_in_range CHECK (val > 0 AND val < 1000) ); ``` You can use table-level constraint syntax with the `CHECK` constraint, as shown in the following example. ``` CREATE TABLE my_table ( id INT CONSTRAINT positive_id CHECK (id > 0) , min_val INT CONSTRAINT min_val_in_range CHECK (min_val > 0 AND min_val < 1000) , max_val INT , CONSTRAINT max_val_in_range CHECK (max_val > 0 AND max_val < 1000 AND max_val > min_val) ); ``` **EXCLUDE** Exclusion constraints aren't supported in Aurora PostgreSQL Limitless Database. **FOREIGN KEY** For more information, see [Foreign keys](#limitless-reference.DDL-limitations.FKs). **NOT NULL** `NOT NULL` constraints are supported with no restrictions. **PRIMARY KEY** Primary key implies unique constraints and therefore the same restrictions on unique constraints apply on primary key. This means: + If a table is converted into a sharded table, the shard key must be a subset of the primary key. That is, the primary key contains all columns of the shard key. + If a table is converted into a reference table, it must have a primary key. The following examples illustrate the use of primary keys. ``` -- Create a standard table. CREATE TABLE public.my_table ( item_id INT , location_code INT , val INT , comment text ); -- Change the table to a sharded table using the 'item_id' and 'location_code' columns as shard keys. CALL rds_aurora.limitless_alter_table_type_sharded('public.my_table', ARRAY['item_id', 'location_code']); ``` Trying to add a primary key that doesn't contain a shard key: ``` -- Add column 'item_id' as the primary key. -- Invalid because the primary key doesnt include all columns from the shard key: -- 'location_code' is part of the shard key but not part of the primary key ALTER TABLE public.my_table ADD PRIMARY KEY (item_id); -- ERROR -- add column "val" as primary key -- Invalid because primary key does not include all columns from shard key: -- item_id and location_code iare part of shard key but not part of the primary key ALTER TABLE public.my_table ADD PRIMARY KEY (item_id); -- ERROR ``` Trying to add a primary key that does contain a shard key: ``` -- Add the 'item_id' and 'location_code' columns as the primary key. -- Valid because the primary key contains the shard key. ALTER TABLE public.my_table ADD PRIMARY KEY (item_id, location_code); -- OK -- Add the 'item_id', 'location_code', and 'val' columns as the primary key. -- Valid because the primary key contains the shard key. ALTER TABLE public.my_table ADD PRIMARY KEY (item_id, location_code, val); -- OK ``` Change a standard table to a reference table. ``` -- Create a standard table. CREATE TABLE zipcodes (zipcode INT PRIMARY KEY, details VARCHAR); -- Convert the table to a reference table. CALL rds_aurora.limitless_alter_table_type_reference('public.zipcode'); ``` For more information on creating sharded and reference tables, see [Creating Aurora PostgreSQL Limitless Database tables](limitless-creating.md). **UNIQUE** In sharded tables, the unique key must contain the shard key, that is, the shard key must be a subset of the unique key. This is checked when changing the table type to sharded. In reference tables there's no restriction. ``` CREATE TABLE customer ( customer_id INT NOT NULL , zipcode INT , email TEXT UNIQUE ); ``` Table-level `UNIQUE` constraints are supported, as shown in the following example. ``` CREATE TABLE customer ( customer_id INT NOT NULL , zipcode INT , email TEXT , CONSTRAINT zipcode_and_email UNIQUE (zipcode, email) ); ``` The following example shows the use of a primary key and a unique key together. Both keys must include the shard key. ``` SET rds_aurora.limitless_create_table_mode='sharded'; SET rds_aurora.limitless_create_table_shard_key='{"p_id"}'; CREATE TABLE t1 ( p_id BIGINT NOT NULL, c_id BIGINT NOT NULL, PRIMARY KEY (p_id), UNIQUE (p_id, c_id) ); ``` For more information, see [Constraints](https://www.postgresql.org/docs/current/ddl-constraints.html) in the PostgreSQL documentation. ## Default values Aurora PostgreSQL Limitless Database supports expressions in default values. The following example shows the use of default values. ``` CREATE TABLE t ( a INT DEFAULT 5, b TEXT DEFAULT 'NAN', c NUMERIC ); CALL rds_aurora.limitless_alter_table_type_sharded('t', ARRAY['a']); INSERT INTO t DEFAULT VALUES; SELECT * FROM t; a | b | c ---+-----+--- 5 | NAN | (1 row) ``` Expressions are supported, as shown in the following example. ``` CREATE TABLE t1 (a NUMERIC DEFAULT random()); ``` The following example adds a new column that is `NOT NULL` and has a default value. ``` ALTER TABLE t ADD COLUMN d BOOLEAN NOT NULL DEFAULT FALSE; SELECT * FROM t; a | b | c | d ---+-----+---+--- 5 | NAN | | f (1 row) ``` The following example alters an existing column with a default value. ``` ALTER TABLE t ALTER COLUMN c SET DEFAULT 0.0; INSERT INTO t DEFAULT VALUES; SELECT * FROM t; a | b | c | d ---+-----+-----+----- 5 | NAN | | f 5 | NAN | 0.0 | f (2 rows) ``` The following example drops a default value. ``` ALTER TABLE t ALTER COLUMN a DROP DEFAULT; INSERT INTO t DEFAULT VALUES; SELECT * FROM t; a | b | c | d ---+-----+-----+----- 5 | NAN | | f 5 | NAN | 0.0 | f | NAN | 0.0 | f (3 rows) ``` For more information, see [Default values](https://www.postgresql.org/docs/current/ddl-default.html) in the PostgreSQL documentation. ## Extensions The following PostgreSQL extensions are supported in Aurora PostgreSQL Limitless Database: + `aurora_limitless_fdw` – This extension is preinstalled. You can't drop it. + `aws_s3` – This extension works in Aurora PostgreSQL Limitless Database similar to the way it does in Aurora PostgreSQL. You can import data from an Amazon S3 bucket to an Aurora PostgreSQL Limitless Database DB cluster, or export data from an Aurora PostgreSQL Limitless Database DB cluster to an Amazon S3 bucket. For more information, see [Importing data from Amazon S3 into an Aurora PostgreSQL DB cluster](USER_PostgreSQL.S3Import.md) and [Exporting data from an Aurora PostgreSQL DB cluster to Amazon S3](postgresql-s3-export.md). + `btree_gin` + `citext` + `ip4r` + `pg_buffercache` – This extension behaves differently in Aurora PostgreSQL Limitless Database from community PostgreSQL. For more information, see [pg\$1buffercache differences in Aurora PostgreSQL Limitless Database](#limitless-reference.DDL-limitations.Extensions.pg_buffercache). + `pg_stat_statements` + `pg_trgm` + `pgcrypto` + `pgstattuple` – This extension behaves differently in Aurora PostgreSQL Limitless Database from community PostgreSQL. For more information, see [pgstattuple differences in Aurora PostgreSQL Limitless Database](#limitless-reference.DDL-limitations.Extensions.pgstattuple). + `pgvector` + `plpgsql` – This extension is preinstalled, but you can drop it. + `PostGIS` – Long transactions and table management functions aren't supported. Modifying the spatial reference table isn't supported. + `unaccent` + `uuid` Most PostgreSQL extensions currently aren't supported in Aurora PostgreSQL Limitless Database. However, you can still use the [shared\$1preload\$1libraries](https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-PRELOAD) (SPL) configuration setting to load extensions into the Aurora PostgreSQL primary DB cluster. They are also loaded into Aurora PostgreSQL Limitless Database, but might not function correctly. For example, you can load the `pg_hint_plan` extension, but loading it doesn't guarantee that the hints passed in query comments are used. **Note** You can't modify objects associated with the [pg\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html) extension. For information on installing `pg_stat_statements`, see [limitless\$1stat\$1statements](limitless-monitoring-views.md#limitless_stat_statements). You can use the `pg_available_extensions` and `pg_available_extension_versions` functions to find the extensions that are supported in Aurora PostgreSQL Limitless Database. The following DDLs are supported for extensions: **CREATE EXTENSION** You can create extensions, as in PostgreSQL. ``` CREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema_name ] [ VERSION version ] [ CASCADE ] ``` For more information, see [CREATE EXTENSION](https://www.postgresql.org/docs/current/sql-createextension.html) in the PostgreSQL documentation. **ALTER EXTENSION** The following DDLs are supported: ``` ALTER EXTENSION name UPDATE [ TO new_version ] ALTER EXTENSION name SET SCHEMA new_schema ``` For more information, see [ALTER EXTENSION](https://www.postgresql.org/docs/current/sql-alterextension.html) in the PostgreSQL documentation. **DROP EXTENSION** You can drop extensions, as in PostgreSQL. ``` DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] ``` For more information, see [DROP EXTENSION](https://www.postgresql.org/docs/current/sql-dropextension.html) in the PostgreSQL documentation. The following DDLs aren't supported for extensions: **ALTER EXTENSION** You can't add or drop member objects from extensions. ``` ALTER EXTENSION name ADD member_object ALTER EXTENSION name DROP member_object ``` ### pg\$1buffercache differences in Aurora PostgreSQL Limitless Database In Aurora PostgreSQL Limitless Database, when you install the [pg\$1buffercache](https://www.postgresql.org/docs/current/pgbuffercache.html) extension and use the `pg_buffercache` view, you receive buffer-related information only from the node to which you're currently connected: the router. Similarly, using the function `pg_buffercache_summary` or `pg_buffercache_usage_counts` provides information only from the connected node. You can have numerous nodes and might need to access buffer information from any node to diagnose issues effectively. Therefore, Limitless Database provides the following functions: + `rds_aurora.limitless_pg_buffercache(subcluster_id)` + `rds_aurora.limitless_pg_buffercache_summary(subcluster_id)` + `rds_aurora.limitless_pg_buffercache_usage_counts(subcluster_id)` By inputting the subcluster ID of any node, whether it's a router or shard, you can easily access the buffer information specific to that node. These functions are directly available when you install the `pg_buffercache` extension in the limitless database. **Note** Aurora PostgreSQL Limitless Database supports these functions for version 1.4 and higher of the `pg_buffercache` extension. The columns shown in the `limitless_pg_buffercache` view differ slightly from those in the `pg_buffercache` view: + `bufferid` – Remains unchanged from `pg_buffercache`. + `relname` – Instead of displaying the file node number as in `pg_buffercache`, `limitless_pg_buffercache` presents the associated `relname` if available in the current database or shared system catalogs, otherwise `NULL`. + `parent_relname` – This new column, not present in `pg_buffercache`, displays the parent `relname` if the value in the `relname` column represents a partitioned table (in the case of sharded tables). Otherwise, it displays `NULL`. + `spcname` – Instead of displaying the tablespace object identifier (OID) as in `pg_buffercache`, `limitless_pg_buffercache` displays the tablespace name. + `datname` – Instead of displaying the database OID as in `pg_buffercache`, `limitless_pg_buffercache` displays the database name. + `relforknumber` – Remains unchanged from `pg_buffercache`. + `relblocknumber` – Remains unchanged from `pg_buffercache`. + `isdirty` – Remains unchanged from `pg_buffercache`. + `usagecount` – Remains unchanged from `pg_buffercache`. + `pinning_backends` – Remains unchanged from `pg_buffercache`. The columns in `limitless_pg_buffercache_summary` and `limitless_pg_buffercache_usage_counts` views are the same as those in the regular `pg_buffercache_summary` and `pg_buffercache_usage_counts` views, respectively. By using these functions, you can access detailed buffer cache information across all nodes in your Limitless Database environment, facilitating more effective diagnosis and management of your database systems. ### pgstattuple differences in Aurora PostgreSQL Limitless Database In Aurora PostgreSQL, the [pgstattuple](https://www.postgresql.org/docs/current/pgstattuple.html) extension currently doesn't support foreign tables, partitioned tables, or partitioned indexes. However, in Aurora PostgreSQL Limitless Database, user-created objects are often among these unsupported types. While there are regular tables and indexes (for example, catalog tables and their indexes), most objects reside on foreign nodes, making them foreign objects for the router. We recognize the importance of this extension for obtaining tuple-level statistics, which is crucial for tasks such as removing bloat and gathering diagnostic information. Therefore, Aurora PostgreSQL Limitless Database provides support for the `pgstattuple` extension in limitless databases. Aurora PostgreSQL Limitless Database includes the following functions in the `rds_aurora` schema: **Tuple-level statistics functions** **`rds_aurora.limitless_pgstattuple(relation_name)`** + Purpose: Extract tuple-level statistics for standard tables and their indexes + Input: `relation_name` (text) – The name of the relation + Output: Columns consistent with those returned by the `pgstattuple` function in Aurora PostgreSQL **`rds_aurora.limitless_pgstattuple(relation_name, subcluster_id)`** + Purpose: Extract tuple-level statistics for reference tables, sharded tables, catalog tables, and their indexes + Input: + `relation_name` (text) – The name of the relation + `subcluster_id` (text) – The subcluster ID of the node where the statistics are to be extracted + Output: + For reference and catalog tables (including their indexes), columns are consistent with those in Aurora PostgreSQL. + For sharded tables, the statistics represent only the partition of the sharded table residing on the specified subcluster. **Index statistics functions** **`rds_aurora.limitless_pgstatindex(relation_name)`** + Purpose: Extract statistics for B-tree indexes on standard tables + Input: `relation_name` (text) – The name of the B-tree index + Output: All columns except `root_block_no` are returned. The returned columns are consistent with the `pgstatindex` function in Aurora PostgreSQL. **`rds_aurora.limitless_pgstatindex(relation_name, subcluster_id)`** + Purpose: Extract statistics for B-tree indexes on reference tables, sharded tables, and catalog tables. + Input: + `relation_name` (text) – The name of the B-tree index + `subcluster_id` (text) – The subcluster ID of the node where the statistics are to be extracted + Output: + For reference and catalog table indexes, all columns (except `root_block_no`) are returned. The returned columns are consistent with Aurora PostgreSQL. + For sharded tables, the statistics represent only the partition of the sharded table index residing on the specified subcluster. The `tree_level` column shows the average across all table slices on the requested subcluster. **`rds_aurora.limitless_pgstatginindex(relation_name)`** + Purpose: Extract statistics for Generalized Inverted Indexes (GINs) on standard tables + Input: `relation_name` (text) – The name of the GIN + Output: Columns consistent with those returned by the `pgstatginindex` function in Aurora PostgreSQL **`rds_aurora.limitless_pgstatginindex(relation_name, subcluster_id)`** + Purpose: Extract statistics for GIN indexes on reference tables, sharded tables, and catalog tables. + Input: + `relation_name` (text) – The name of the index + `subcluster_id` (text) – The subcluster ID of the node where the statistics are to be extracted + Output: + For reference and catalog table GIN indexes, columns are consistent with those in Aurora PostgreSQL. + For sharded tables, the statistics represent only the partition of the sharded table index residing on the specified subcluster. **`rds_aurora.limitless_pgstathashindex(relation_name)`** + Purpose: Extract statistics for hash indexes on standard tables + Input: `relation_name` (text) – The name of the hash index + Output: Columns consistent with those returned by the `pgstathashindex` function in Aurora PostgreSQL **`rds_aurora.limitless_pgstathashindex(relation_name, subcluster_id)`** + Purpose: Extract statistics for hash indexes on reference tables, sharded tables, and catalog tables. + Input: + `relation_name` (text) – The name of the index + `subcluster_id` (text) – The subcluster ID of the node where the statistics are to be extracted + Output: + For reference and catalog table hash indexes, columns are consistent with Aurora PostgreSQL. + For sharded tables, the statistics represent only the partition of the sharded table index residing on the specified subcluster. **Page count functions** **`rds_aurora.limitless_pg_relpages(relation_name)`** + Purpose: Extract the page count for standard tables and their indexes + Input: `relation_name` (text) – The name of the relation + Output: Page count of the specified relation **`rds_aurora.limitless_pg_relpages(relation_name, subcluster_id)`** + Purpose: Extract the page count for reference tables, sharded tables, and catalog tables (including their indexes) + Input: + `relation_name` (text) – The name of the relation + `subcluster_id` (text) – The subcluster ID of the node where the page count is to be extracted + Output: For sharded tables, the page count is the sum of pages across all table slices on the specified subcluster. **Approximate tuple-level statistics functions** **`rds_aurora.limitless_pgstattuple_approx(relation_name)`** + Purpose: Extract approximate tuple-level statistics for standard tables and their indexes + Input: `relation_name` (text) – The name of the relation + Output: Columns consistent with those returned by the pgstattuple\$1approx function in Aurora PostgreSQL **`rds_aurora.limitless_pgstattuple_approx(relation_name, subcluster_id)`** + Purpose: Extract approximate tuple-level statistics for reference tables, sharded tables, and catalog tables (including their indexes) + Input: + `relation_name` (text) – The name of the relation + `subcluster_id` (text) – The subcluster ID of the node where the statistics are to be extracted + Output: + For reference and catalog tables (including their indexes), columns are consistent with those in Aurora PostgreSQL. + For sharded tables, the statistics represent only the partition of the sharded table residing on the specified subcluster. **Note** Currently, Aurora PostgreSQL Limitless Database doesn't support the `pgstattuple` extension on materialized views, TOAST tables, or temporary tables. In Aurora PostgreSQL Limitless Database, you must provide the input as text, although Aurora PostgreSQL supports other formats. ## Foreign keys Foreign key (`FOREIGN KEY`) constraints are supported with some limitations: + `CREATE TABLE` with `FOREIGN KEY` is supported only for standard tables. To create a sharded or reference table with `FOREIGN KEY`, first create the table without a foreign key constraint. Then modify it using the following statement: ``` ALTER TABLE ADD CONSTRAINT; ``` + Converting a standard table to a sharded or reference table isn't supported when the table has a foreign key constraint. Drop the constraint, then add it after conversion. + The following limitations apply to table types for foreign key constraints: + A standard table can have a foreign key constraint to another standard table. + A sharded table can have a foreign key constraint if the parent and child tables are collocated and the foreign key is a superset of the shard key. + A sharded table can have a foreign key constraint to a reference table. + A reference table can have a foreign key constraint to another reference table. **Topics** + [ ### Foreign key options ](#limitless-reference.DDL-limitations.FKs.options) + [ ### Examples ](#limitless-reference.DDL-limitations.FKs.examples) ### Foreign key options Foreign keys are supported in Aurora PostgreSQL Limitless Database for some DDL options. The following table lists options that are supported and not supported between Aurora PostgreSQL Limitless Database tables. | DDL option | Reference to reference | Sharded to sharded (collocated) | Sharded to reference | Standard to standard | | --- | --- | --- | --- | --- | | `DEFERRABLE` | Yes | Yes | Yes | Yes | | `INITIALLY DEFERRED` | Yes | Yes | Yes | Yes | | `INITIALLY IMMEDIATE` | Yes | Yes | Yes | Yes | | `MATCH FULL` | Yes | Yes | Yes | Yes | | `MATCH PARTIAL` | No | No | No | No | | `MATCH SIMPLE` | Yes | Yes | Yes | Yes | | `NOT DEFERRABLE` | Yes | Yes | Yes | Yes | | `NOT VALID` | Yes | No | No | Yes | | `ON DELETE CASCADE` | Yes | Yes | Yes | Yes | | `ON DELETE NO ACTION` | Yes | Yes | Yes | Yes | | `ON DELETE RESTRICT` | Yes | Yes | Yes | Yes | | `ON DELETE SET DEFAULT` | No | No | No | No | | `ON DELETE SET NULL` | Yes | No | No | Yes | | `ON UPDATE CASCADE` | No | No | No | Yes | | `ON UPDATE NO ACTION` | Yes | Yes | Yes | Yes | | `ON UPDATE RESTRICT` | Yes | Yes | Yes | Yes | | `ON UPDATE SET DEFAULT` | No | No | No | No | | `ON UPDATE SET NULL` | Yes | No | No | Yes | ### Examples + Standard to standard: ``` set rds_aurora.limitless_create_table_mode='standard'; CREATE TABLE products( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no), quantity integer ); SELECT constraint_name, table_name, constraint_type FROM information_schema.table_constraints WHERE constraint_type='FOREIGN KEY'; constraint_name | table_name | constraint_type -------------------------+-------------+----------------- orders_product_no_fkey | orders | FOREIGN KEY (1 row) ``` + Sharded to sharded (collocated): ``` set rds_aurora.limitless_create_table_mode='sharded'; set rds_aurora.limitless_create_table_shard_key='{"product_no"}'; CREATE TABLE products( product_no integer PRIMARY KEY, name text, price numeric ); set rds_aurora.limitless_create_table_shard_key='{"order_id"}'; set rds_aurora.limitless_create_table_collocate_with='products'; CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer, quantity integer ); ALTER TABLE orders ADD CONSTRAINT order_product_fk FOREIGN KEY (product_no) REFERENCES products (product_no); ``` + Sharded to reference: ``` set rds_aurora.limitless_create_table_mode='reference'; CREATE TABLE products( product_no integer PRIMARY KEY, name text, price numeric ); set rds_aurora.limitless_create_table_mode='sharded'; set rds_aurora.limitless_create_table_shard_key='{"order_id"}'; CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer, quantity integer ); ALTER TABLE orders ADD CONSTRAINT order_product_fk FOREIGN KEY (product_no) REFERENCES products (product_no); ``` + Reference to reference: ``` set rds_aurora.limitless_create_table_mode='reference'; CREATE TABLE products( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer, quantity integer ); ALTER TABLE orders ADD CONSTRAINT order_product_fk FOREIGN KEY (product_no) REFERENCES products (product_no); ``` ## Functions Functions are supported in Aurora PostgreSQL Limitless Database. The following DDLs are supported for functions: **CREATE FUNCTION** You can create functions, as in Aurora PostgreSQL, with the exception of changing their volatility while replacing them. For more information, see [CREATE FUNCTION](https://www.postgresql.org/docs/current/sql-createfunction.html) in the PostgreSQL documentation. **ALTER FUNCTION** You can alter functions, as in Aurora PostgreSQL, with the exception of changing their volatility. For more information, see [ALTER FUNCTION](https://www.postgresql.org/docs/current/sql-alterfunction.html) in the PostgreSQL documentation. **DROP FUNCTION** You can drop functions, as in Aurora PostgreSQL. ``` DROP FUNCTION [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...] [ CASCADE | RESTRICT ] ``` For more information, see [DROP FUNCTION](https://www.postgresql.org/docs/current/sql-dropfunction.html) in the PostgreSQL documentation. **Topics** + [ ### Function distribution ](#limitless-function-distribution) + [ ### Function volatility ](#limitless-function-volatility) ### Function distribution When all of the statements of a function are targeted to a single shard, it's beneficial to push the entire function down to the target shard. Then the result is propagated back to the router, rather than unraveling the function at the router itself. Function and stored procedure pushdown capability is useful for customers who want to run their function or stored procedure closer to the data source, that is the shard. To distribute a function, first create the function, then call the `rds_aurora.limitless_distribute_function` procedure to distribute it. This function uses the following syntax: ``` SELECT rds_aurora.limitless_distribute_function('function_prototype', ARRAY['shard_key'], 'collocating_table'); ``` The function takes the following parameters: + `function_prototype` – The function to be distributed. Mention only the input arguments, and not any of the output arguments. If any of the arguments are defined as `OUT` parameters, don't include their type in the arguments of `function_prototype`. + `ARRAY['shard_key']` – The list of function arguments that are identified as the shard key for the function. + `collocating_table` – The sharded table that contains the range of data on the target shard. To identify the shard where to push down this function for running, the system takes the `ARRAY['shard_key']` argument, hashes it, and finds the shard from `collocating_table` that hosts the range containing this hash value. **Restrictions** When you distribute a function or procedure, it only deals with data that's bounded by the shard key range in that shard. In cases where the function or procedure tries to access data from a different shard, the results returned by the distributed function or procedure will be different compared to one that isn't distributed. For example, you create a function containing queries that will touch multiple shards, but then call the `rds_aurora.limitless_distribute_function` procedure to distribute it. When you invoke this function by providing arguments for a shard key, it is likely that the results of running it will be bounded by the values present in that shard. These results are different from ones produced without distributing the function. **Examples** Consider the following function `func` where we have the sharded table `customers` with the shard key `customer_id`. ``` postgres_limitless=> CREATE OR REPLACE FUNCTION func(c_id integer, sc integer) RETURNS int language SQL volatile AS $$ UPDATE customers SET score = sc WHERE customer_id = c_id RETURNING score; $$; ``` Now we distribute this function: ``` SELECT rds_aurora.limitless_distribute_function('func(integer, integer)', ARRAY['c_id'], 'customers'); ``` The following are example query plans. ``` EXPLAIN(costs false, verbose true) SELECT func(27+1,10); QUERY PLAN -------------------------------------------------- Foreign Scan Output: (func((27 + 1), 10)) Remote SQL: SELECT func((27 + 1), 10) AS func Single Shard Optimized (4 rows) ``` ``` EXPLAIN(costs false, verbose true) SELECT * FROM customers,func(customer_id, score) WHERE customer_id=10 AND score=27; QUERY PLAN --------------------------------------------------------------------- Foreign Scan Output: customer_id, name, score, func Remote SQL: SELECT customers.customer_id, customers.name, customers.score, func.func FROM public.customers, LATERAL func(customers.customer_id, customers.score) func(func) WHERE ((customers.customer_id = 10) AND (customers.score = 27)) Single Shard Optimized (10 rows) ``` The following example shows a procedure with `IN` and `OUT` parameters as arguments. ``` CREATE OR REPLACE FUNCTION get_data(OUT id INTEGER, IN arg_id INT) AS $$ BEGIN SELECT customer_id, INTO id FROM customer WHERE customer_id = arg_id; END; $$ LANGUAGE plpgsql; ``` The following example distributes the procedure using only `IN` parameters. ``` EXPLAIN(costs false, verbose true) SELECT * FROM get_data(1); QUERY PLAN ----------------------------------- Foreign Scan Output: id Remote SQL: SELECT customer_id FROM get_data(1) get_data(id) Single Shard Optimized (6 rows) ``` ### Function volatility You can determine whether a function is immutable, stable, or volatile by checking the `provolatile` value in the [pg\$1proc](https://www.postgresql.org/docs/current/catalog-pg-proc.html) view. The `provolatile` value indicates whether the function's result depends only on its input arguments, or is affected by outside factors. The value is one of the following: + `i` – Immutable functions, which always deliver the same result for the same inputs + `s` – Stable functions, whose results (for fixed inputs) don't change within a scan + `v` – Volatile functions, whose results might change at any time. Also use `v` for functions with side effects, so that calls to them can't be optimized away. The following examples show volatile functions. ``` SELECT proname, provolatile FROM pg_proc WHERE proname='pg_sleep'; proname | provolatile ----------+------------- pg_sleep | v (1 row) SELECT proname, provolatile FROM pg_proc WHERE proname='uuid_generate_v4'; proname | provolatile ------------------+------------- uuid_generate_v4 | v (1 row) SELECT proname, provolatile FROM pg_proc WHERE proname='nextval'; proname | provolatile ---------+------------- nextval | v (1 row) ``` Changing the volatility of an existing function isn't supported in Aurora PostgreSQL Limitless Database. This applies to both `ALTER FUNCTION` and `CREATE OR REPLACE FUNCTION` commands, as shown in the following examples. ``` -- Create an immutable function CREATE FUNCTION immutable_func1(name text) RETURNS text language plpgsql AS $$ BEGIN RETURN name; END; $$IMMUTABLE; -- Altering the volatility throws an error ALTER FUNCTION immutable_func1 STABLE; -- Replacing the function with altered volatility throws an error CREATE OR REPLACE FUNCTION immutable_func1(name text) RETURNS text language plpgsql AS $$ BEGIN RETURN name; END; $$VOLATILE; ``` We highly recommend that you assign the correct volatilities to functions. For example, if your function uses `SELECT` from multiple tables or references database objects, don't set it as `IMMUTABLE`. If the table contents ever change, the immutability is broken. Aurora PostgreSQL allows `SELECT` inside immutable functions, but the results might be incorrect. Aurora PostgreSQL Limitless Database can return both errors and incorrect results. For more information on function volatility, see [Function volatility categories](https://www.postgresql.org/docs/current/xfunc-volatility.html) in the PostgreSQL documentation. ## Sequences Named sequences are database objects that generate unique numbers in ascending or descending order. `CREATE SEQUENCE` creates a new sequence number generator. Sequence values are guaranteed to be unique. When you create a named sequence in Aurora PostgreSQL Limitless Database, a distributed sequence object is created. Then Aurora PostgreSQL Limitless Database distributes non-overlapping chunks of sequence values across all Distributed Transaction Routers (routers). Chunks are represented as local sequence objects on routers; therefore, sequence operations such as `nextval` and `currval` are run locally. Routers operate independently, and request new chunks from the distributed sequence when needed. For more information on sequences, see [CREATE SEQUENCE](https://www.postgresql.org/docs/current/sql-createsequence.html) in the PostgreSQL documentation. **Topics** + [ ### Requesting a new chunk ](#limitless-reference.DDL-limitations.Sequences.request-chunk) + [ ### Limitations ](#limitless-reference.DDL-limitations.Sequences.limitations) + [ ### Unsupported options ](#limitless-reference.DDL-limitations.Sequences.unsupported) + [ ### Examples ](#limitless-reference.DDL-limitations.Sequences.examples) + [ ### Sequence views ](#limitless-reference.DDL-limitations.Sequences.views) + [ ### Troubleshooting sequence issues ](#limitless-reference.DDL-limitations.Sequences.troubleshooting) ### Requesting a new chunk You configure the sizes of chunks allocated on routers by using the `rds_aurora.limitless_sequence_chunk_size` parameter. The default value is `250000`. Each router initially owns two chunks: active and reserved. Active chunks are used to configure local sequence objects (setting `minvalue` and `maxvalue`), and reserved chunks are stored in an internal catalog table. When an active chunk reaches the minimum or maximum value, it's replaced by the reserved chunk. To do that, `ALTER SEQUENCE` is used internally, meaning that `AccessExclusiveLock` is acquired. Background workers run every 10 seconds on router nodes to scan sequences for used reserved chunks. If a used chunk is found, the worker requests a new chunk from the distributed sequence. Make sure to set the chunk size large enough that the background workers have enough time to request new ones. Remote requests never happen in the context of user sessions, which means that you can't request a new sequence directly. ### Limitations The following limitations apply to sequences in Aurora PostgreSQL Limitless Database: + The `pg_sequence` catalog, `pg_sequences` function, and `SELECT * FROM sequence_name` statement all show only the local sequence state, not the distributed state. + Sequence values are guaranteed to be unique, and are guaranteed to be monotonic within a session. But they can be out of order with `nextval` statements run in other sessions, if those sessions are connected to other routers. + Make sure that the sequence size (number of available values) is large enough to be distributed across all routers. Use the `rds_aurora.limitless_sequence_chunk_size` parameter to configure the `chunk_size`. (Each router has two chunks.) + The `CACHE` option is supported, but the cache must be smaller than `chunk_size`. ### Unsupported options The following options aren't supported for sequences in Aurora PostgreSQL Limitless Database. **Sequence manipulation functions** The `setval` function isn't supported. For more information, see [Sequence Manipulation Functions](https://www.postgresql.org/docs/current/functions-sequence.html) in the PostgreSQL documentation. **CREATE SEQUENCE** The following options aren't supported. ``` CREATE [{ TEMPORARY | TEMP} | UNLOGGED] SEQUENCE [[ NO ] CYCLE] ``` For more information, see [CREATE SEQUENCE](https://www.postgresql.org/docs/current/sql-createsequence.html) in the PostgreSQL documentation. **ALTER SEQUENCE** The following options aren't supported. ``` ALTER SEQUENCE [[ NO ] CYCLE] ``` For more information, see [ALTER SEQUENCE](https://www.postgresql.org/docs/current/sql-altersequence.html) in the PostgreSQL documentation. **ALTER TABLE** The `ALTER TABLE` command isn't supported for sequences. ### Examples **CREATE/DROP SEQUENCE** ``` postgres_limitless=> CREATE SEQUENCE s; CREATE SEQUENCE postgres_limitless=> SELECT nextval('s'); nextval --------- 1 (1 row) postgres_limitless=> SELECT * FROM pg_sequence WHERE seqrelid='s'::regclass; seqrelid | seqtypid | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle ----------+----------+----------+--------------+--------+--------+----------+---------- 16960 | 20 | 1 | 1 | 10000 | 1 | 1 | f (1 row) % connect to another router postgres_limitless=> SELECT nextval('s'); nextval --------- 10001 (1 row) postgres_limitless=> SELECT * FROM pg_sequence WHERE seqrelid='s'::regclass; seqrelid | seqtypid | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle ----------+----------+----------+--------------+--------+--------+----------+---------- 16959 | 20 | 10001 | 1 | 20000 | 10001 | 1 | f (1 row) postgres_limitless=> DROP SEQUENCE s; DROP SEQUENCE ``` **ALTER SEQUENCE** ``` postgres_limitless=> CREATE SEQUENCE s; CREATE SEQUENCE postgres_limitless=> ALTER SEQUENCE s RESTART 500; ALTER SEQUENCE postgres_limitless=> SELECT nextval('s'); nextval --------- 500 (1 row) postgres_limitless=> SELECT currval('s'); currval --------- 500 (1 row) ``` **Sequence manipulation functions** ``` postgres=# CREATE TABLE t(a bigint primary key, b bigint); CREATE TABLE postgres=# CREATE SEQUENCE s minvalue 0 START 0; CREATE SEQUENCE postgres=# INSERT INTO t VALUES (nextval('s'), currval('s')); INSERT 0 1 postgres=# INSERT INTO t VALUES (nextval('s'), currval('s')); INSERT 0 1 postgres=# SELECT * FROM t; a | b ---+--- 0 | 0 1 | 1 (2 rows) postgres=# ALTER SEQUENCE s RESTART 10000; ALTER SEQUENCE postgres=# INSERT INTO t VALUES (nextval('s'), currval('s')); INSERT 0 1 postgres=# SELECT * FROM t; a | b -------+------- 0 | 0 1 | 1 10000 | 10000 (3 rows) ``` ### Sequence views Aurora PostgreSQL Limitless Database provides the following views for sequences. **rds\$1aurora.limitless\$1distributed\$1sequence** This view shows a distributed sequence state and configuration. The `minvalue`, `maxvalue`, `start`, `inc`, and `cache` columns have the same meaning as in the [pg\$1sequences](https://www.postgresql.org/docs/current/view-pg-sequences.html) view, and show the options with which the sequence was created. The `lastval` column shows the latest allocated or reserved value in a distributed sequence object. This doesn't mean that the value was already used, as routers keep sequence chunks locally. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_distributed_sequence WHERE sequence_name='test_serial_b_seq'; schema_name | sequence_name | lastval | minvalue | maxvalue | start | inc | cache -------------+-------------------+---------+----------+------------+-------+-----+------- public | test_serial_b_seq | 1250000 | 1 | 2147483647 | 1 | 1 | 1 (1 row) ``` **rds\$1aurora.limitless\$1sequence\$1metadata** This view shows distributed sequence metadata and aggregates sequence metadata from cluster nodes. It uses the following columns: + `subcluster_id` – The cluster node ID that owns a chunk. + Active chunk – A chunk of a sequence that is being used (`active_minvalue`, `active_maxvalue`). + Reserved chunk – The local chunk that will be used next (`reserved_minvalue`, `reserved_maxvalue`). + `local_last_value` – The last observed value from a local sequence. + `chunk_size` – The size of a chunk, as configured on creation. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_sequence_metadata WHERE sequence_name='test_serial_b_seq' order by subcluster_id; subcluster_id | sequence_name | schema_name | active_minvalue | active_maxvalue | reserved_minvalue | reserved_maxvalue | chunk_size | chunk_state | local_last_value ---------------+-------------------+-------------+-----------------+-----------------+-------------------+-------------------+------------+-------------+------------------ 1 | test_serial_b_seq | public | 500001 | 750000 | 1000001 | 1250000 | 250000 | 1 | 550010 2 | test_serial_b_seq | public | 250001 | 500000 | 750001 | 1000000 | 250000 | 1 | (2 rows) ``` ### Troubleshooting sequence issues The following issues can occur with sequences. **Chunk size not large enough** If the chunk size isn't set large enough and the transaction rate is high, the background workers might not have enough time to request new chunks before the active chunks are exhausted. This can lead to contention and wait events such as `LIMITLESS:AuroraLimitlessSequenceReplace`, `LWLock:LockManager` , `Lockrelation`, and `LWlock:bufferscontent`. Increase the value of the `rds_aurora.limitless_sequence_chunk_size` parameter. **Sequence cache set too high** In PostgreSQL, sequence caching happens at the session level. Each session allocates successive sequence values during one access to the sequence object, and increases the sequence object's `last_value` accordingly. Then, the next uses of `nextval` within that session simply return the pre-allocated values without touching the sequence object. Any numbers allocated but not used within a session are lost when that session ends, resulting in "holes" in the sequence. This can consume the sequence\$1chunk quickly and lead to to contention and wait events such as `LIMITLESS:AuroraLimitlessSequenceReplace`, `LWLock:LockManager` , `Lockrelation`, and `LWlock:bufferscontent`. Reduce the sequence cache setting. The following figure shows wait events caused by sequence issues. ![\[Wait events caused by sequence issues.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_sequence_waits.png) # Supported and unsupported Data Manipulation Language (DML) and query processing SQL commands The following table lists the DML commands that are supported and not supported by Aurora PostgreSQL Limitless Database, with references to limitations or more information. | Command | Supported? | Limitations or more information | | --- | --- | --- | | ABORT | Yes | None | | ANALYZE | Yes | [ANALYZE](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.ANALYZE) | | BEGIN | Yes | None | | CALL | Yes | None | | CHECKPOINT | Yes | None | | CLOSE | Yes | None | | CLUSTER | Yes | [CLUSTER](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.CLUSTER) | | COMMIT | Yes | None | | COMMIT PREPARED | No | Not applicable | | COPY | Yes | None | | DEALLOCATE | Yes | None | | DECLARE | Yes | None | | DELETE | Yes | None | | DISCARD | Yes | None | | DO | Yes | None | | END | Yes | None | | EXECUTE | Yes | None | | EXPLAIN | Yes | [EXPLAIN](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.EXPLAIN) | | FETCH | Yes | None | | IMPORT FOREIGN SCHEMA | No | Not applicable | | INSERT | Yes | [INSERT](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.INSERT) | | LISTEN | No | Not applicable | | LOCK | Yes | None | | MERGE | No | Not applicable | | MOVE | Yes | None | | NOTIFY | No | Not applicable | | OPEN | Yes | None | | PREPARE | Yes | None | | PREPARE TRANSACTION | No | Not applicable | | REFRESH MATERIALIZED VIEW | No | Not applicable | | REINDEX | No | Not applicable | | RELEASE SAVEPOINT | Yes | None | | ROLLBACK | Yes | None | | ROLLBACK PREPARED | No | Not applicable | | ROLLBACK TO SAVEPOINT | Yes | None | | SAVEPOINT | Yes | None | | SELECT | Yes | None | | SELECT INTO | Yes | None | | SHOW | Yes | None | | START TRANSACTION | Yes | None | | UNLISTEN | No | None | | UPDATE | Yes | [UPDATE](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.UPDATE) | | UPDATE … WHERE CURRENT OF | No | Not applicable | | VACUUM | Yes | [VACUUM](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.VACUUM) | | VALUES | Yes | None | # DML limitations and other information for Aurora PostgreSQL Limitless Database The following topics describe limitations or provide more information for DML and query processing SQL commands in Aurora PostgreSQL Limitless Database. **Topics** + [ ## ANALYZE ](#limitless-reference.DML-limitations.ANALYZE) + [ ## CLUSTER ](#limitless-reference.DML-limitations.CLUSTER) + [ ## EXPLAIN ](#limitless-reference.DML-limitations.EXPLAIN) + [ ## INSERT ](#limitless-reference.DML-limitations.INSERT) + [ ## UPDATE ](#limitless-reference.DML-limitations.UPDATE) + [ ## VACUUM ](#limitless-reference.DML-limitations.VACUUM) ## ANALYZE The `ANALYZE` command collects statistics about the contents of tables in the database. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries. For more information, see [ANALYZE](https://www.postgresql.org/docs/current/sql-analyze.html) in the PostgreSQL documentation. In Aurora PostgreSQL Limitless Database, the `ANALYZE` command collects table statistics across all routers and shards when it runs. To prevent the calculation of statistics on every router during `ANALYZE` runs, table statistics are calculated on one of the routers and then copied to peer routers. ## CLUSTER The `CLUSTER` command physically reorders a table based on an index. The index must already have been defined on the table. In Aurora PostgreSQL Limitless Database, the clustering is local to the part of the index that's present on each shard. For more information, see [CLUSTER](https://www.postgresql.org/docs/current/sql-cluster.html) in the PostgreSQL documentation. ## EXPLAIN You use the following parameter to configure the output from the `EXPLAIN` command: + `rds_aurora.limitless_explain_options` – What to include in the `EXPLAIN` output. The default value is `single_shard_optimization`: whether plans are single-shard optimized is shown, but shard plans aren't included. In this example, the `EXPLAIN` output doesn't show plans from shards. ``` postgres_limitless=> EXPLAIN SELECT * FROM employees where id =25; QUERY PLAN ------------------------------------------------------ Foreign Scan (cost=100.00..101.00 rows=100 width=0) Single Shard Optimized (2 rows) ``` Now we set the `rds_aurora.limitless_explain_options` to include `shard_plans` and `single_shard_optimization`. We can view the execution plans of statements on both routers and shards. In addition, we disable the `enable_seqscan` parameter to enforce that index scan is used on the shard layer. ``` postgres_limitless=> SET rds_aurora.limitless_explain_options = shard_plans, single_shard_optimization; SET postgres_limitless=> SET enable_seqscan = OFF; SET postgres_limitless=> EXPLAIN SELECT * FROM employees WHERE id = 25; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Foreign Scan (cost=100.00..101.00 rows=100 width=0) Remote Plans from Shard postgres_s4: Index Scan using employees_ts00287_id_idx on employees_ts00287 employees_fs00003 (cost=0.14..8.16 rows=1 width=15) Index Cond: (id = 25) Single Shard Optimized (5 rows) ``` For more information on the `EXPLAIN` command, see [EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html) in the PostgreSQL documentation. ## INSERT Most `INSERT` commands are supported in Aurora PostgreSQL Limitless Database. PostgreSQL doesn't have an explicit `UPSERT` command, but it does support `INSERT ... ON CONFLICT` statements. `INSERT ... ON CONFLICT` isn't supported if the conflict action has a subquery or a mutable function: ``` -- RANDOM is a mutable function. INSERT INTO sharded_table VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET other_id = RANDOM(); ERROR: Aurora Limitless Tables doesn't support pushdown-unsafe functions with DO UPDATE clauses. ``` For more information on the `INSERT` command, see [INSERT](https://www.postgresql.org/docs/current/sql-insert.html) in the PostgreSQL documentation. ## UPDATE Updating the shard key isn't supported. For example, you have a sharded table called `customers`, with a shard key `customer_id`. The following DML statements cause errors: ``` postgres_limitless=> UPDATE customers SET customer_id = 11 WHERE customer_id =1; ERROR: Shard key column update is not supported postgres_limitless=> UPDATE customers SET customer_id = 11 WHERE customer_name='abc'; ERROR: Shard key column update is not supported ``` To update a shard key, first you have to `DELETE` the row with the shard key, then `INSERT` a new row with the updated shard key value. For more information on the `UPDATE` command, see [Updating data](https://www.postgresql.org/docs/current/dml-update.html) in the PostgreSQL documentation. ## VACUUM You can perform vacuuming on both sharded and reference tables. The following `VACUUM` functions are fully supported in Aurora PostgreSQL Limitless Database: + VACUUM + [ANALYZE](#limitless-reference.DML-limitations.ANALYZE) + DISABLE\$1PAGE\$1SKIPPING + FREEZE + FULL + INDEX\$1CLEANUP + PARALLEL + PROCESS\$1TOAST + TRUNCATE + VERBOSE `VACUUM` on Aurora PostgreSQL Limitless Database has the following limitations: + The [pg\$1visibility\$1map](https://www.postgresql.org/docs/current/pgvisibility.html) extension isn't supported. + Checking for unused indexes with the [pg\$1stat\$1all\$1indexes](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW) view isn't supported. + Consolidated views for [pg\$1stat\$1user\$1indexes](https://www.postgresql.org/docs/current/monitoring-stats.html), [pg\$1class](https://www.postgresql.org/docs/current/catalog-pg-class.html), and [pg\$1stats](https://www.postgresql.org/docs/current/view-pg-stats.html) aren't implemented. For more information on the `VACUUM` command, see [VACUUM](https://www.postgresql.org/docs/current/sql-vacuum.html) in the PostgreSQL documentation. For more information on how vacuuming works in Aurora PostgreSQL Limitless Database, see [Reclaiming storage space by vacuuming](limitless-vacuum.md). # Variables in Aurora PostgreSQL Limitless Database You can use the following variables to configure Aurora PostgreSQL Limitless Database. **rds\$1aurora.limitless\$1active\$1shard\$1key** Sets a single shard key while querying the database, causing all `SELECT` and DML queries to be appended with the shard key as a constant predicate. For more information, see [Setting an active shard key](limitless-query.single-shard.md#limitless-query.single-shard.active). **rds\$1aurora.limitless\$1create\$1table\$1collocate\$1with** Set this variable to a specific table name to collocate newly created tables with that table. For more information, see [Creating limitless tables by using variables](limitless-creating-config.md). **rds\$1aurora.limitless\$1create\$1table\$1mode** Sets the table creation mode. For more information, see [Creating limitless tables by using variables](limitless-creating-config.md). **rds\$1aurora.limitless\$1create\$1table\$1shard\$1key** Set this variable to an array of column names to use as shard keys. For more information, see [Creating limitless tables by using variables](limitless-creating-config.md). **rds\$1aurora.limitless\$1explain\$1options** What to include in the `EXPLAIN` output. For more information, see [EXPLAIN](limitless-reference.DML-limitations.md#limitless-reference.DML-limitations.EXPLAIN). # DB cluster parameters in Aurora PostgreSQL Limitless Database You can use the following DB cluster parameters to configure Aurora PostgreSQL Limitless Database. **rds\$1aurora.limitless\$1adaptive\$1fetch\$1size** Enhances batch prefetching. When set to `true`, this parameter allows a self-adjusting (adaptive) fetch size for prefetching. When set to `false`, the fetch size is constant. **rds\$1aurora.limitless\$1auto\$1scale\$1options** Sets the options available for adding routers or splitting shards in a DB shard group. The value can be `add_router`, `split_shard`, or both. For more information, see [Adding a router to a DB shard group](limitless-add-router.md) and [Splitting a shard in a DB shard group](limitless-shard-split.md). **rds\$1aurora.limitless\$1distributed\$1deadlock\$1timeout** The amount of time to wait on a lock before checking whether there is a distributed deadlock condition, in milliseconds. The default is `1000` (1 second). For more information, see [Distributed deadlocks in Aurora PostgreSQL Limitless Database](limitless-query.deadlocks.md). **rds\$1aurora.limitless\$1enable\$1auto\$1scale** Enables the adding of routers and splitting of shards in a DB shard group. For more information, see [Adding a router to a DB shard group](limitless-add-router.md) and [Splitting a shard in a DB shard group](limitless-shard-split.md). **rds\$1aurora.limitless\$1finalize\$1split\$1shard\$1mode** Determines how system-initiated shard splits are finalized. For more information, see [Splitting a shard in a DB shard group](limitless-shard-split.md). **rds\$1aurora.limitless\$1maximum\$1adaptive\$1fetch\$1size** Sets the upper limit for the adaptive fetch size. The range is `1`–`INT_MAX`. The default is `1000`.