Primary keys in Aurora DSQL

PDFRSS

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.

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.

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.

• Once you create a table, you can't change the primary key, and you can't add a new primary key later.

• 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.

• 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.

• 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.

• 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 and Supported data types in Aurora DSQL.

• 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 and Supported data types in Aurora DSQL.