AWS aurora-dsql documentation change
Summary
Updated documentation about primary key implementation in Aurora DSQL with expanded technical details. Added sections about data structure/storage and guidelines for key selection. Clarified performance implications of key choices and system limitations.
Security assessment
Changes focus on performance optimization, data distribution, and schema design best practices. While they mention security-adjacent concepts like unique keys and partitioning, there's no direct reference to vulnerabilities, access control, encryption, or security incidents. The guidance about avoiding monotonically increasing keys addresses performance bottlenecks rather than security risks.
Diff
diff --git a/aurora-dsql/latest/userguide/working-with-primary-keys.md b/aurora-dsql/latest/userguide/working-with-primary-keys.md index 145a69f72..6aa309b2c 100644 --- a//aurora-dsql/latest/userguide/working-with-primary-keys.md +++ b//aurora-dsql/latest/userguide/working-with-primary-keys.md @@ -4,0 +5,2 @@ +Data structure and storageGuidelines for choosing a primary key + @@ -9 +11,5 @@ Amazon Aurora DSQL is provided as a Preview service. To learn more, see [Betas a -In Aurora DSQL, defining a primary key for your table is similar to the `CLUSTER` operation in PostgreSQL or a clustered index in other database systems. Aurora DSQL applies an INCLUDE statement that references all columns, which creates a table organized by an index. This structure makes it so that any lookup against an Aurora DSQL primary key can access all column values associated with the key, and the data is always ordered according to the primary key. Unlike the `CLUSTER` operation, Aurora DSQL always maintains the order of this index-organized table. +In Aurora DSQL, a _primary key_ is a feature that organizes table data. It's similar to the `CLUSTER` operation in PostgreSQL or a clustered index in other databases. When you define a primary key, Aurora DSQL creates an index that includes all columns in the table. The primary key structure in Aurora DSQL ensures efficient data access and management. + +## Data structure and storage + +When you define a primary key, Aurora DSQL stores table data in primary key order. This index-organized structure allows a primary key lookup to retrieve all column values directly, instead of following a pointer to the data as in a traditional B-tree index. Unlike the `CLUSTER` operation in PostgreSQL, which reorganizes data only once, Aurora DSQL maintains this order automatically and continuously. This approach improves the performance of queries that rely on primary key access. @@ -11 +17 @@ In Aurora DSQL, defining a primary key for your table is similar to the `CLUSTER -Aurora DSQL uses this main concept to organize distributed data management. Aurora DSQL uses the primary key to construct a cluster-wide unique key that's assigned to each row in each table or index. Aurora DSQL uses this key to automatically partition storage. This partition key plays a central role in Aurora DSQL automatic scaling and concurrency control mechanisms. +Aurora DSQL also uses the primary key to generate a cluster-wide unique key for each row in tables and indexes. This unique key is not only used for indexing, but also underpins distributed data management. It enables automatic partitioning of data across multiple nodes, supporting scalable storage and high concurrency. As a result, the primary key structure helps Aurora DSQL scale automatically and manage concurrent workloads efficiently. @@ -13 +19 @@ Aurora DSQL uses this main concept to organize distributed data management. Auro -Consider the following when you choose a primary key. +## Guidelines for choosing a primary key @@ -15 +21 @@ Consider the following when you choose a primary key. - * It's a best practice to define a primary key when you create a table in Aurora DSQL. This key becomes part of a cluster-wide key that is used to partition data in your cluster. This is an important component in the mechanism that Aurora DSQL uses to to automatically scale write throughput for your cluster. If you don't assign a primary key, Aurora DSQL assigns a synthetic hidden ID. +When choosing and using a primary key in Aurora DSQL, consider the following guidelines: @@ -17 +23 @@ Consider the following when you choose a primary key. - * Once you create a table, you can't change the primary key, and you can't add a new primary key later. + * Define a primary key when you create a table. You can't change this key or add a new primary key later. The primary key becomes part of the cluster-wide key used for data partitioning and automatic scaling of write throughput. If you don't specify a primary key, Aurora DSQL assigns a synthetic hidden ID. @@ -19 +25 @@ Consider the following when you choose a primary key. - * For tables with high write volumes, avoid using monotonically increasing integers as primary keys, which can lead to weaker performance. Randomness in primary keys ensures even distribution of new writes across storage partitions. Instead, using monotonically increasing integers as primary keys can lead to all new inserts being directed to a single partition, which creates a bottleneck. + * For tables with high write volumes, avoid using monotonically increasing integers as primary keys. This can lead to performance issues by directing all new inserts to a single partition. Instead, use primary keys with random distribution to ensure even distribution of writes across storage partitions. @@ -21 +27 @@ Consider the following when you choose a primary key. - * If your table doesn't change very often or is read-only, you can use an ascending key, even if it is a dense key. Doing so is fine because there you don't need a high level of performance for loading data into the key. + * For tables that change infrequently or are read-only, you can use an ascending key. Examples of ascending keys are timestamps or sequence numbers. A dense key has many closely spaced or duplicate values. You can use an ascending key even if it is dense because write performance is less critical. @@ -23 +29 @@ Consider the following when you choose a primary key. - * Generally speaking, if doing a full scan of the table doesn't meet your performance needs, choose a primary key that represents your most common join and lookup key when you query the table. + * If a full table scan doesn't meet your performance requirements, choose a more efficient access method. In most cases, this means using a primary key that matches your most common join and lookup key in queries. @@ -25 +31 @@ Consider the following when you choose a primary key. - * The maximum combined size of a column that you can use in a primary key is 1 kibibyte. For more information, see [ Database limits in Aurora DSQL ](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/CHAP_quotas.html#SECTION_database-limits) and [Supported data types in Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-supported-data-types). + * The maximum combined size of columns in a primary key is 1 kibibyte. For more information, see [Database limits in Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/CHAP_quotas.html#SECTION_database-limits) and [Supported data types in Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-supported-data-types). @@ -27 +33 @@ Consider the following when you choose a primary key. - * The maximum number of columns that you can include in a primary key or a secondary index is 8. For more information, see [Database limits in Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/CHAP_quotas.html#SECTION_database-limits) and [Supported data types in Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-supported-data-types). + * You can include up to 8 columns in a primary key or a secondary index. For more information, see [Database limits in Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/CHAP_quotas.html#SECTION_database-limits) and [Supported data types in Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-supported-data-types). @@ -38 +44 @@ To use the Amazon Web Services Documentation, Javascript must be enabled. Please -Data definition language +DDL and distributed transactions @@ -40 +46 @@ Data definition language -Async indexes +Asynchronous indexes