# Aurora DSQL and PostgreSQL
Aurora DSQL is a PostgreSQL-compatible, distributed relational database designed for transactional workloads. Aurora DSQL uses core PostgreSQL components such as the parser, planner, optimizer, and type system.
The Aurora DSQL design ensures that all supported PostgreSQL syntax provides compatible behavior and yields identical query results. For example, Aurora DSQL provides type conversions, arithmetic operations, and numerical precision and scale that are identical to PostgreSQL. Any deviations are documented.
Aurora DSQL also introduces advanced capabilities such as optimistic concurrency control and distributed schema management. With these features, you can use the familiar tooling of PostgreSQL while benefiting from the performance and scalability of a modern, cloud-native, distributed applications.
## PostgreSQL compatibility highlights
Aurora DSQL is currently based on PostgreSQL version 16. Key highlights include the following:
**Wire protocol**
Aurora DSQL uses the standard PostgreSQL v3 wire protocol. This enables integration with standard PostgreSQL clients, drivers, and tools. For example, Aurora DSQL is compatible with `psql`, `pgjdbc`, and `psycopg`.
**SQL syntax**
Aurora DSQL supports a wide range of standard PostgreSQL expressions and functions commonly used in transactional workloads. Supported SQL expressions yield identical results to PostgreSQL, including the following:
+ Handling of nulls
+ Sort order behavior
+ Scale and precision for numeric operations
+ Equivalence for string operations
For more information, see [SQL feature compatibility in Aurora DSQL](working-with-postgresql-compatibility.md).
**Transaction management**
Aurora DSQL preserves the primary characteristics of PostgreSQL, such as ACID transactions and an isolation level equivalent to PostgreSQL Repeatable Read. For more information, see [Concurrency control in Aurora DSQL](working-with-concurrency-control.md).
## Distributed architecture benefits
The distributed, shared-nothing design of Aurora DSQL provides performance and scalability benefits beyond traditional single-node databases. Key capabilities include the following:
**Optimistic Concurrency Control (OCC)**
Aurora DSQL uses an optimistic concurrency control model. This lock-free approach prevents transactions from blocking one another, eliminates deadlocks, and enables high-throughput parallel execution. These features make Aurora DSQL particularly valuable for applications requiring consistent performance at scale. For more example, see [Concurrency control in Aurora DSQL](working-with-concurrency-control.md).
**Asynchronous DDL operations**
Aurora DSQL runs DDL operations asynchronously, which allows uninterrupted reads and writes during schema changes. Its distributed architecture allows Aurora DSQL to perform the following actions:
+ Run DDL operations as background tasks, minimizing disruption.
+ Coordinate catalog changes as strongly consistent distributed transactions. This ensures atomic visibility across all nodes, even during failures or concurrent operations.
+ Operate in a fully distributed, leaderless manner across multiple Availability Zones with decoupled compute and storage layers.
For more on using the EXPLAIN command in PostgreSQL, see [DDL and distributed transactions in Aurora DSQL](working-with-ddl.md).
# SQL feature compatibility in Aurora DSQL
In the following sections, learn about Aurora DSQL support for PostgreSQL data types and SQL commands.
**Topics**
+ [Supported data types in Aurora DSQL](working-with-postgresql-compatibility-supported-data-types.md)
+ [Supported SQL for Aurora DSQL](working-with-postgresql-compatibility-supported-sql-features.md)
+ [Supported subsets of SQL commands in Aurora DSQL](working-with-postgresql-compatibility-supported-sql-subsets.md)
+ [Migrating from PostgreSQL to Aurora DSQL](working-with-postgresql-compatibility-migration-guide.md)
# Supported data types in Aurora DSQL
Aurora DSQL supports a subset of the common PostgreSQL types.
**Topics**
+ [Numeric data types](#numeric-data-types)
+ [Character data types](#character-data-types)
+ [Date and time data types](#date-time-data-types)
+ [Miscellaneous data types](#miscellaneous-data-types)
+ [Query runtime data types](#working-with-postgresql-compatibility-query-runtime)
## Numeric data types
Aurora DSQL supports the following PostgreSQL numeric data types.
| Name | Aliases | Range and precision | Storage size | Index support |
| --- | --- | --- | --- | --- |
| smallint | int2 | -32768 to \$132767 | 2 bytes | Yes |
| `integer` | `int`, `int4` | -2147483648 to \$12147483647 | 4 bytes | Yes |
| `bigint` | `int8` | -9223372036854775808 to \$19223372036854775807 | 8 bytes | Yes |
| `real` | `float4` | 6 decimal digits precision | 4 bytes | Yes |
| `double precision` | `float8` | 15 decimal digits precision | 8 bytes | Yes |
| `numeric` [ `(`*p*, *s*`)` ] | `decimal` [ `(`*p*, *s*`)` ] `dec`[ `(`*p*,*s*`)`] | Exact numeric of selectable precision. The maximum precision is 38 and the maximum scale is 37.1 The default is `numeric (18,6)`. | 8 bytes \$1 2 bytes per precision digit. Maximum size is 27 bytes. | Yes |
1 – If you don't explicitly specify a size when you run `CREATE TABLE` or `ALTER TABLE ADD COLUMN`, Aurora DSQL enforces the defaults. Aurora DSQL applies limits when you run `INSERT` or `UPDATE` statements.
## Character data types
Aurora DSQL supports the following PostgreSQL character data types.
| Name | Aliases | Description | Aurora DSQL limit | Storage size | Index support |
| --- | --- | --- | --- | --- | --- |
| `character` [ `(`*n*`)` ] | `char` [ `(`*n*`)` ] | Fixed-length character string | 4096 bytes1 | Variable up to 4100 bytes | Yes |
| `character varying` [ `(`*n*`)` ] | `varchar` [ `(`*n*`)` ] | Variable-length character string | 65535 bytes1 | Variable up to 65539 bytes | Yes |
| `bpchar` [ `(`*n*`)` ] | | If fixed length, this is an alias for `char`. If variable length, this is an alias for `varchar`, where trailing spaces are semantically insignificant. | 4096 bytes1 | Variable up to 4100 bytes | Yes |
| `text` | | Variable-length character string | 1 MiB1 | Variable up to 1 MiB | Yes |
1 – If you don't explicitly specify a size when you run `CREATE TABLE` or `ALTER TABLE ADD COLUMN`, then Aurora DSQL enforces the defaults. Aurora DSQL applies limits when you run `INSERT` or `UPDATE` statements.
## Date and time data types
Aurora DSQL supports the following PostgreSQL date and time data types.
| Name | Aliases | Description | Range | Resolution | Storage size | Index support |
| --- | --- | --- | --- | --- | --- | --- |
| `date` | | Calendar date (year, month, day) | 4713 BC – 5874897 AD | 1 day | 4 bytes | Yes |
| `time` [ `(`*p*`)` ] [ `without time zone` ] | `timestamp` | Time of day, with no time zone | 0 – 1 | 1 microsecond | 8 bytes | Yes |
| `time` [ `(`*p*`)` ] `with time zone` | `timetz` | time of day, including time zone | 00:00:00\$11559 – 24:00:00 –1559 | 1 microsecond | 12 bytes | No |
| `timestamp` [ `(`*p*`)` ] [ `without time zone` ] | | Date and time, with no time zone | 4713 BC – 294276 AD | 1 microsecond | 8 bytes | Yes |
| `timestamp` [ `(`*p*`)` ] `with time zone` | `timestamptz` | Date and time, including time zone | 4713 BC – 294276 AD | 1 microsecond | 8 bytes | Yes |
| `interval` [ `fields` ] [ `(`*p*`)` ] | | Time span | -178000000 years – 178000000 years | 1 microsecond | 16 bytes | No |
## Miscellaneous data types
Aurora DSQL supports the following miscellaneous PostgreSQL data types.
| Name | Aliases | Description | Aurora DSQL limit | Storage size | Index support |
| --- | --- | --- | --- | --- | --- |
| `boolean` | `bool` | Logical Boolean (true/false) | | 1 byte | Yes |
| `bytea` | | Binary data ("byte array") | 1 MiB1 | Variable up to 1 MiB limit | No |
| `UUID` | | Universally unique identifier | | 16 bytes | Yes |
1 – If you don't explicitly specify a size when you run `CREATE TABLE` or `ALTER TABLE ADD COLUMN`, then Aurora DSQL enforces the defaults. Aurora DSQL applies limits when you run `INSERT` or `UPDATE` statements.
## Query runtime data types
Query runtime data types are internal data types used at query execution time. These types are distinct from the PostgreSQL-compatible types like `varchar` and `integer` that you define in your schema. Instead, these types are runtime representations that Aurora DSQL uses when processing a query.
The following data types are supported only during query runtime:
**Array type**
Aurora DSQL supports arrays of the supported data types. For example, you can have an array of integers. The function `string_to_array` splits a string into a PostgreSQL-style array with the comma delimiter (`,`) as shown in the following example. You can use arrays in expressions, function outputs, or temporary computations during query execution.
```
SELECT string_to_array('1,2', ',');
```
The function returns a response similar to the following:
```
string_to_array
-----------------
{1,2}
(1 row)
```
****inet type****
The data type represents IPv4, IPv6 host addresses, and their subnets. This type is useful when parsing logs, filtering on IP subnets, or doing network calculations within a query. For more information, see [inet in the PostgreSQL documentation](https://www.PostgreSQL.org/docs/16/datatype-net-types.html#DATATYPE-INET).
**JSON runtime functions**
Aurora DSQL supports JSON and JSONB as runtime data types for query processing. Store JSON data as `text` columns and cast to JSON during query execution to use PostgreSQL JSON functions and operators.
Aurora DSQL supports most PostgreSQL JSON functions from [section 9.1.6 JSON Functions and Operators](https://www.postgresql.org/docs/current/functions-json.html) with identical behavior.
Functions that return JSON or JSONB types may require additional casting to `text` for proper display.
```
SELECT json_build_array(1, 2, 'foo', 4, 5)::text;
```
The function returns a response similar to the following:
```
json_build_array
---------------------
[1, 2, "foo", 4, 5]
(1 row)
```
# Supported SQL for Aurora DSQL
Aurora DSQL supports a wide range of core PostgreSQL SQL features. In the following sections, you can learn about general PostgreSQL expression support. This list is not exhaustive.
## `SELECT` command
Aurora DSQL supports the following clauses of the `SELECT` command.
| Primary clause | Supported clauses |
| --- | --- |
| `FROM` | |
| `GROUP BY` | `ALL`, `DISTINCT` |
| `ORDER BY` | `ASC`, `DESC`, `NULLS` |
| `LIMIT` | |
| `DISTINCT` | |
| `HAVING` | |
| `USING` | |
| `WITH` (common table expressions) | |
| `INNER JOIN` | `ON` |
| `OUTER JOIN` | `LEFT`, `RIGHT`, `FULL`, `ON` |
| `CROSS JOIN` | `ON` |
| `UNION` | `ALL` |
| `INTERSECT` | `ALL` |
| `EXCEPT` | `ALL` |
| `OVER` | `RANK ()`, `PARTITION BY` |
| `FOR UPDATE` | |
## Data Definition Language (DDL)
Aurora DSQL supports the following PostgreSQL DDL commands.
| Command | Primary Clause | Supported Clauses |
| --- | --- | --- |
| `CREATE` | `TABLE` | For information about the supported syntax of the `CREATE TABLE` command, see [`CREATE TABLE`](create-table-syntax-support.md). |
| `ALTER` | `TABLE` | For information about the supported syntax of the `ALTER TABLE` command, see [`ALTER TABLE`](alter-table-syntax-support.md). |
| `DROP` | `TABLE` | |
| `CREATE` | `[UNIQUE] INDEX ASYNC` | You can use this command with the following parameters: `ON`, `NULLS FIRST`, `NULLS LAST`. For information about the supported syntax of the `CREATE INDEX ASYNC` command, see [Asynchronous indexes in Aurora DSQL](working-with-create-index-async.md). |
| `DROP` | `INDEX` | |
| `CREATE` | `VIEW` | For more information about the supported syntax of the `CREATE VIEW` command, see [`CREATE VIEW`](create-view.md). |
| ALTER | VIEW | For information about the supported syntax of the `ALTER VIEW` command, see [`ALTER VIEW`](alter-view-syntax-support.md). |
| DROP | VIEW | For information about the supported syntax of the DROP VIEW command, see [`DROP VIEW`](drop-view-overview.md). |
| `CREATE` | `SEQUENCE` | For information about the supported syntax of the `CREATE SEQUENCE` command, see [`CREATE SEQUENCE`](create-sequence-syntax-support.md). |
| `ALTER` | `SEQUENCE` | For information about the supported syntax of the `ALTER SEQUENCE` command, see [`ALTER SEQUENCE`](alter-sequence-syntax-support.md). |
| `DROP` | `SEQUENCE` | For information about the supported syntax of the `DROP SEQUENCE` command, see [`DROP SEQUENCE`](drop-sequence-syntax-support.md). |
| `CREATE` | `ROLE`, `WITH` | |
| `CREATE` | `FUNCTION` | `LANGUAGE SQL` |
| `CREATE` | `DOMAIN` | |
## Data Manipulation Language (DML)
Aurora DSQL supports the following PostgreSQL DML commands.
| Command | Primary clause | Supported clauses |
| --- | --- | --- |
| `INSERT` | `INTO` | `VALUES`SELECT |
| `UPDATE` | `SET` | `WHERE (SELECT)` `FROM, WITH` |
| DELETE | FROM | USING, WHERE |
## Data Control Language (DCL)
Aurora DSQL supports the following PostgreSQL DCL commands.
| Command | Supported clauses |
| --- | --- |
| `GRANT` | `ON`, `TO` |
| `REVOKE` | `ON`, `FROM`, `CASCADE`, `RESTRICT` |
## Transaction Control Language (TCL)
Aurora DSQL supports the following PostgreSQL TCL commands.
| Command | Supported clauses | Alias |
| --- | --- | --- |
| `COMMIT` | [`WORK` \$1 `TRANSACTION`] [`AND NO CHAIN`] | `END` |
| `BEGIN` | [`WORK` \$1 `TRANSACTION`] [`ISOLATION LEVEL REPEATABLE READ`] [`READ WRITE` \$1 `READ ONLY`] | |
| `START TRANSACTION` | [`ISOLATION LEVEL REPEATABLE READ`] [`READ WRITE` \$1 `READ ONLY`] | |
| `ROLLBACK` | [`WORK` \$1 `TRANSACTION`] [`AND NO CHAIN`] | `ABORT` |
## Utility commands
Aurora DSQL supports the following PostgreSQL utility commands:
+ `EXPLAIN`
+ `ANALYZE` (relation name only)
# Supported subsets of SQL commands in Aurora DSQL
This section provides detailed information about supported SQL commands, focusing on commands with extensive parameter sets and subcommands. For example, CREATE TABLE in PostgreSQL offers many clauses and parameters, a subset of which are supported by Aurora DSQL. This section describes the supported subsets of common SQL commands using familiar PostgreSQL syntax elements that Aurora DSQL supports.
**Topics**
+ [`CREATE TABLE`](create-table-syntax-support.md)
+ [`ALTER TABLE`](alter-table-syntax-support.md)
+ [`CREATE SEQUENCE`](create-sequence-syntax-support.md)
+ [`ALTER SEQUENCE`](alter-sequence-syntax-support.md)
+ [`DROP SEQUENCE`](drop-sequence-syntax-support.md)
+ [`CREATE VIEW`](create-view.md)
+ [`ALTER VIEW`](alter-view-syntax-support.md)
+ [`DROP VIEW`](drop-view-overview.md)
# `CREATE TABLE`
`CREATE TABLE` defines a new table.
```
CREATE TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression )|
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options ) |
UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
PRIMARY KEY index_parameters |
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
and like_option is:
{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | ALL }
index_parameters in UNIQUE, and PRIMARY KEY constraints are:
[ INCLUDE ( column_name [, ... ] ) ]
```
## Identity columns
**Note**
When using identity columns, the cache value should be carefully considered. For more information, see the Important callout on the [`CREATE SEQUENCE`](create-sequence-syntax-support.md) page.
For guidance on how best to use identity columns based on workload patterns, see [Working with sequences and identity columns](sequences-identity-columns-working-with.md).
The `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options )` clause creates the column as an *identity column*. It will have an implicit sequence attached to it and in newly-inserted rows the column will automatically have values from the sequence assigned to it. Such a column is implicitly `NOT NULL`.
The clauses `ALWAYS` and `BY DEFAULT` determine how explicitly user-specified values are handled in `INSERT` and `UPDATE` commands.
In an `INSERT` command, if `ALWAYS` is selected, a user-specified value is only accepted if the `INSERT` statement specifies `OVERRIDING SYSTEM VALUE`. If `BY DEFAULT` is selected, then the user-specified value takes precedence.
In an `UPDATE` command, if `ALWAYS` is selected, any update of the column to any value other than `DEFAULT` will be rejected. If `BY DEFAULT` is selected, the column can be updated normally. (There is no `OVERRIDING` clause for the `UPDATE` command.)
The *sequence\$1options* clause can be used to override the parameters of the sequence. The available options include those shown for [`CREATE SEQUENCE`](create-sequence-syntax-support.md), plus `SEQUENCE NAME name`. Without `SEQUENCE NAME`, the system chooses an unused name for the sequence.
# `ALTER TABLE`
`ALTER TABLE` changes the definition of a table.
```
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
SET SCHEMA new_schema
where action is one of:
ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type
ADD table_constraint_using_index
ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
and table_constraint_using_index is:
[ CONSTRAINT constraint_name ]
UNIQUE USING INDEX index_name
```
## Identity column actions
**`SET GENERATED { ALWAYS | BY DEFAULT }` / `SET sequence_option` / `RESTART`**
These forms change whether a column is an identity column or change the generation attribute of an existing identity column. See [`CREATE TABLE`](create-table-syntax-support.md) for details. Like `SET DEFAULT`, these forms only affect the behavior of subsequent `INSERT` and `UPDATE` commands; they do not cause rows already in the table to change.
The *sequence\$1option* is an option supported by [`ALTER SEQUENCE`](alter-sequence-syntax-support.md) such as `INCREMENT BY`. These forms alter the sequence that underlies an existing identity column.
**`DROP IDENTITY [ IF EXISTS ]`**
This form removes the identity property from a column. If `DROP IDENTITY IF EXISTS` is specified and the column is not an identity column, no error is thrown. In this case a notice is issued instead.
## Add constraint actions
**`ADD table_constraint_using_index`**
This form adds a new `UNIQUE` constraint to a table based on an existing unique index. All the columns of the index will be included in the constraint.
The index must be in a `VALID` state; adding a unique constraint using an index while the index is currently building is not supported.
If a constraint name is provided then the index will be renamed to match the constraint name. Otherwise the constraint will be named the same as the index.
After this command is executed, the index is "owned" by the constraint, in the same way as if the index had been built by a regular `CREATE UNIQUE INDEX ASYNC` command. In particular, dropping the constraint will make the index disappear too.
# `CREATE SEQUENCE`
`CREATE SEQUENCE` — define a new sequence generator.
**Important**
In PostgreSQL, specifying `CACHE` is optional and defaults to 1. In a distributed system such as Amazon Aurora DSQL, sequence operations involve coordination, and a cache size of 1 can increase coordination overhead under high concurrency. While larger cache values allow sequence numbers to be served from locally preallocated ranges, improving throughput, unused reserved values can be lost, making gaps and ordering effects more visible. Because applications differ in their sensitivity to allocation ordering versus throughput, Amazon Aurora DSQL requires `CACHE` to be specified explicitly and currently supports `CACHE = 1` or `CACHE >= 65536`, providing a clear distinction between allocation behavior that is closer to strictly sequential generation and allocation optimized for highly concurrent workloads.
When `CACHE >= 65536`, sequence values remain guaranteed to be unique but might not be generated in strict increasing order across sessions, and gaps can occur, particularly when cached values are not fully consumed. These characteristics are consistent with PostgreSQL semantics for cached sequences under concurrent use, where both systems guarantee distinct values but do not guarantee strictly sequential ordering across sessions.
Within a single client session, sequence values may not always appear strictly increasing, particularly outside explicit transactions. This behavior is similar to PostgreSQL deployments that use connection pooling. Allocation behavior closer to a single-session PostgreSQL environment can be achieved by using `CACHE = 1` or by obtaining sequence values within explicit transactions.
With `CACHE = 1`, sequence allocation follows PostgreSQL's non-cached sequence behavior.
For guidance on how best to use sequences based on workload patterns, see [Working with sequences and identity columns](sequences-identity-columns-working-with.md).
## Supported syntax
```
CREATE SEQUENCE [ IF NOT EXISTS ] name CACHE cache
[ AS data_type ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ [ NO ] CYCLE ]
[ START [ WITH ] start ]
[ OWNED BY { table_name.column_name | NONE } ]
where data_type is BIGINT
and cache = 1 or cache >= 65536
```
## Description
`CREATE SEQUENCE` creates a new sequence number generator. This involves creating and initializing a new special single-row table with the name *name*. The generator will be owned by the user issuing the command.
If a schema name is given then the sequence is created in the specified schema. Otherwise it is created in the current schema. The sequence name must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema.
After a sequence is created, you use the functions `nextval`, `currval`, and `setval` to operate on the sequence. These functions are documented in [Sequence manipulation functions](sequence-functions-syntax-support.md).
Although you cannot update a sequence directly, you can use a query like:
```
SELECT * FROM name;
```
to examine some of the parameters and current state of a sequence. In particular, the `last_value` field of the sequence shows the last value allocated by any session. (Of course, this value might be obsolete by the time it is printed, if other sessions are actively doing `nextval` calls.) Other parameters such as *increment* and *maxvalue* can be observed in the `pg_sequences` view.
## Parameters
**`IF NOT EXISTS`**
Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the sequence that would have been created — it might not even be a sequence.
***name***
The name (optionally schema-qualified) of the sequence to be created.
***data\$1type***
The optional clause `AS data_type` specifies the data type of the sequence. Valid types are `bigint`. `bigint` is the default. The data type determines the default minimum and maximum values of the sequence.
***increment***
The optional clause `INCREMENT BY increment` specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1.
***minvalue* / `NO MINVALUE`**
The optional clause `MINVALUE minvalue` determines the minimum value a sequence can generate. If this clause is not supplied or `NO MINVALUE` is specified, then defaults will be used. The default for an ascending sequence is 1. The default for a descending sequence is the minimum value of the data type.
***maxvalue* / `NO MAXVALUE`**
The optional clause `MAXVALUE maxvalue` determines the maximum value for the sequence. If this clause is not supplied or `NO MAXVALUE` is specified, then default values will be used. The default for an ascending sequence is the maximum value of the data type. The default for a descending sequence is -1.
**`CYCLE` / `NO CYCLE`**
The `CYCLE` option allows the sequence to wrap around when the *maxvalue* or *minvalue* has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the *minvalue* or *maxvalue*, respectively.
If `NO CYCLE` is specified, any calls to `nextval` after the sequence has reached its maximum value will return an error. If neither `CYCLE` or `NO CYCLE` are specified, `NO CYCLE` is the default.
***start***
The optional clause `START WITH start` allows the sequence to begin anywhere. The default starting value is *minvalue* for ascending sequences and *maxvalue* for descending ones.
***cache***
The clause `CACHE cache` specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The acceptable values for `CACHE` in Aurora DSQL are 1 or any number >= 65536. The minimum value is 1 (only one value can be generated at a time, meaning no cache).
**`OWNED BY table_name.column_name` / `OWNED BY NONE`**
The `OWNED BY` option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. The specified table must have the same owner and be in the same schema as the sequence. `OWNED BY NONE`, the default, specifies that there is no such association.
## Notes
Use [`DROP SEQUENCE`](drop-sequence-syntax-support.md) to remove a sequence.
Sequences are based on `bigint` arithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808 to 9223372036854775807).
Because `nextval` and `setval` calls are never rolled back, sequence objects cannot be used if "gapless" assignment of sequence numbers is needed.
Each session will allocate and cache successive sequence values during one access to the sequence object and increase the sequence object's `last_value` accordingly. Then, the next *cache*-1 uses of `nextval` within that session simply return the preallocated values without touching the sequence object. So, any numbers allocated but not used within a session will be lost when that session ends, resulting in "holes" in the sequence.
Furthermore, although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a *cache* setting of 10, session A might reserve values 1..10 and return `nextval`=1, then session B might reserve values 11..20 and return `nextval`=11 before session A has generated `nextval`=2. Thus, with a *cache* setting of one it is safe to assume that `nextval` values are generated sequentially; with a *cache* setting greater than one you should only assume that the `nextval` values are all distinct, not that they are generated purely sequentially. Also, `last_value` will reflect the latest value reserved by any session, whether or not it has yet been returned by `nextval`.
Another consideration is that a `setval` executed on such a sequence will not be noticed by other sessions until they have used up any preallocated values they have cached.
## Examples
Create an ascending sequence called `serial`, starting at 101:
```
CREATE SEQUENCE serial CACHE 65536 START 101;
```
Select the next number from this sequence:
```
SELECT nextval('serial');
nextval
---------
101
```
Select the next number from this sequence:
```
SELECT nextval('serial');
nextval
---------
102
```
Use this sequence in an `INSERT` command:
```
INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
```
Reset the sequence to a specific value using `setval`:
```
SELECT setval('serial', 200);
SELECT nextval('serial');
nextval
---------
201
```
## Compatibility
`CREATE SEQUENCE` conforms to the SQL standard, with the following exceptions:
+ Obtaining the next value is done using the `nextval()` function instead of the standard's `NEXT VALUE FOR` expression.
+ The `OWNED BY` clause is a PostgreSQL extension.
# `ALTER SEQUENCE`
`ALTER SEQUENCE` — change the definition of a sequence generator.
**Important**
When using sequences, the cache value should be carefully considered. For more information, see the Important callout on the [`CREATE SEQUENCE`](create-sequence-syntax-support.md) page.
For guidance on how best to use sequences based on workload patterns, see [Working with sequences and identity columns](sequences-identity-columns-working-with.md).
## Supported syntax
```
ALTER SEQUENCE [ IF EXISTS ] name
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ [ NO ] CYCLE ]
[ START [ WITH ] start ]
[ RESTART [ [ WITH ] restart ] ]
[ CACHE cache ]
[ OWNED BY { table_name.column_name | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema
where cache is 1 or cache >= 65536
```
## Description
`ALTER SEQUENCE` changes the parameters of an existing sequence generator. Any parameters not specifically set in the `ALTER SEQUENCE` command retain their prior settings.
You must own the sequence to use `ALTER SEQUENCE`. To change a sequence's schema, you must also have `CREATE` privilege on the new schema. To alter the owner, you must be able to `SET ROLE` to the new owning role, and that role must have `CREATE` privilege on the sequence's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the sequence. However, a superuser can alter ownership of any sequence anyway.)
## Parameters
***name***
The name (optionally schema-qualified) of a sequence to be altered.
**`IF EXISTS`**
Do not throw an error if the sequence does not exist. A notice is issued in this case.
***increment***
The clause `INCREMENT BY increment` is optional. A positive value will make an ascending sequence, a negative one a descending sequence. If unspecified, the old increment value will be maintained.
***minvalue* / `NO MINVALUE`**
The optional clause `MINVALUE minvalue` determines the minimum value a sequence can generate. If `NO MINVALUE` is specified, the defaults of 1 and the minimum value of the data type for ascending and descending sequences, respectively, will be used. If neither option is specified, the current minimum value will be maintained.
***maxvalue* / `NO MAXVALUE`**
The optional clause `MAXVALUE maxvalue` determines the maximum value for the sequence. If `NO MAXVALUE` is specified, the defaults of the maximum value of the data type and -1 for ascending and descending sequences, respectively, will be used. If neither option is specified, the current maximum value will be maintained.
**`CYCLE`**
The optional `CYCLE` key word can be used to enable the sequence to wrap around when the *maxvalue* or *minvalue* has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the *minvalue* or *maxvalue*, respectively.
**`NO CYCLE`**
If the optional `NO CYCLE` key word is specified, any calls to `nextval` after the sequence has reached its maximum value will return an error. If neither `CYCLE` or `NO CYCLE` are specified, the old cycle behavior will be maintained.
***start***
The optional clause `START WITH start` changes the recorded start value of the sequence. This has no effect on the current sequence value; it simply sets the value that future `ALTER SEQUENCE RESTART` commands will use.
***restart***
The optional clause `RESTART [ WITH restart ]` changes the current value of the sequence. This is similar to calling the `setval` function with `is_called` = `false`: the specified value will be returned by the next call of `nextval`. Writing `RESTART` with no *restart* value is equivalent to supplying the start value that was recorded by `CREATE SEQUENCE` or last set by `ALTER SEQUENCE START WITH`.
In contrast to a `setval` call, a `RESTART` operation on a sequence is transactional and blocks concurrent transactions from obtaining numbers from the same sequence. If that's not the desired mode of operation, `setval` should be used.
***cache***
The clause `CACHE cache` enables sequence numbers to be preallocated and stored in memory for faster access. The value must be either 1 or some value >= 65536. If unspecified, the old cache value will be maintained. For more information about cache behavior, see the guidance under [`CREATE SEQUENCE`](create-sequence-syntax-support.md).
**`OWNED BY table_name.column_name` / `OWNED BY NONE`**
The `OWNED BY` option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. If specified, this association replaces any previously specified association for the sequence. The specified table must have the same owner and be in the same schema as the sequence. Specifying `OWNED BY NONE` removes any existing association, making the sequence "free-standing".
***new\$1owner***
The user name of the new owner of the sequence.
***new\$1name***
The new name for the sequence.
***new\$1schema***
The new schema for the sequence.
## Notes
`ALTER SEQUENCE` will not immediately affect `nextval` results in backends, other than the current one, that have preallocated (cached) sequence values. They will use up all cached values prior to noticing the changed sequence generation parameters. The current backend will be affected immediately.
`ALTER SEQUENCE` does not affect the `currval` status for the sequence.
`ALTER SEQUENCE` may cause other transactions to OCC.
For historical reasons, `ALTER TABLE` can be used with sequences too; but the only variants of `ALTER TABLE` that are allowed with sequences are equivalent to the forms shown above.
## Examples
Restart a sequence called `serial`, at 105:
```
ALTER SEQUENCE serial RESTART WITH 105;
```
## Compatibility
`ALTER SEQUENCE` conforms to the SQL standard, except for the `AS`, `START WITH`, `OWNED BY`, `OWNER TO`, `RENAME TO`, and `SET SCHEMA` clauses, which are PostgreSQL extensions.
# `DROP SEQUENCE`
`DROP SEQUENCE` — remove a sequence.
## Supported syntax
```
DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```
## Description
`DROP SEQUENCE` removes sequence number generators. A sequence can only be dropped by its owner or a superuser.
## Parameters
**`IF EXISTS`**
Do not throw an error if the sequence does not exist. A notice is issued in this case.
***name***
The name (optionally schema-qualified) of a sequence.
**`CASCADE`**
Automatically drop objects that depend on the sequence, and in turn all objects that depend on those objects.
**`RESTRICT`**
Refuse to drop the sequence if any objects depend on it. This is the default.
## Examples
To remove the sequence `seq`:
```
DROP SEQUENCE seq;
```
## Compatibility
`DROP SEQUENCE` conforms to the SQL standard, except that the standard only allows one sequence to be dropped per command, and apart from the `IF EXISTS` option, which is a PostgreSQL extension.
# `CREATE VIEW`
`CREATE VIEW` defines a new persistent view. Aurora DSQL does not support temporary views; only permanent views are supported.
## Supported syntax
```
CREATE [ OR REPLACE ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS query
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
```
## Description
`CREATE VIEW` defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.
`CREATE or REPLACE VIEW` is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be different.
If a schema name is given, such as `CREATE VIEW myschema.myview ...`) then the view is created in the specified schema. Otherwise, it is created in the current schema.
The name of the view must be distinct from the name of any other relation (table, index, view) in the same schema.
## Parameters
`CREATE VIEW` supports various parameters to control the behavior of automatically updatable views.
**`RECURSIVE`**
Creates a recursive view. The syntax: `CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...;` is equivalent to `CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;`.
A view column name list must be specified for a recursive view.
**`name`**
The name of the view to be created, which may be optionally schema-qualified. A column name list must be specified for a recursive view.
**`column_name`**
An optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.
**`WITH ( view_option_name [= view_option_value] [, ... ] )`**
This clause specifies optional parameters for a view; the following parameters are supported.
+ `check_option (enum)` — This parameter may be either `local` or `cascaded`, and is equivalent to specifying `WITH [ CASCADED | LOCAL ] CHECK OPTION`.
+ `security_barrier (boolean)`—This should be used if the view is intended to provide row-level security. Aurora DSQL does not currently support row-level security, but this option will still force the view's `WHERE` conditions (and any conditions using operators which are marked as `LEAKPROOF`) to be evaluated first.
+ `security_invoker (boolean)`—This option causes the underlying base relations to be checked against the privileges of the user of the view rather than the view owner. See the notes below for full details.
All of the above options can be changed on existing views using `ALTER VIEW`.
**`query`**
A `SELECT` or `VALUES` command which will provide the columns and rows of the view.
**`WITH [ CASCADED | LOCAL ] CHECK OPTION`**
This option controls the behavior of automatically updatable views. When this option is specified, `INSERT` and `UPDATE` commands on the view will be checked to ensure that new rows satisfy the view-defining condition (that is, the new rows are checked to ensure that they are visible through the view). If they are not, the update will be rejected. If the `CHECK OPTION` is not specified, `INSERT` and `UPDATE` commands on the view are allowed to create rows that are not visible through the view.
`LOCAL`—New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the `CHECK OPTION`).
`CASCADED`—New rows are checked against the conditions of the view and all underlying base views. If the `CHECK OPTION` is specified, and neither `LOCAL` nor `CASCADED` are specified, then `CASCADED` is assumed.
The `CHECK OPTION` may not be used with `RECURSIVE` views. The `CHECK OPTION` is only supported on views that are automatically updatable.
## Notes
Use the `DROP VIEW` statement to drop views.
The names and data types of the view's columns should be carefully considered. For example, CREATE VIEW vista AS SELECT 'Hello World'; is not recommended because the column name defaults to `?column?;`. Also, the column data type defaults to `text`, which might not be what you wanted.
A better approach is to explicitly specify the column name and data type, such as: `CREATE VIEW vista AS SELECT text 'Hello World' AS hello;`.
By default, access to the underlying base relations referenced in the view is determined by the permissions of the view owner. In some cases, this can be used to provide secure but restricted access to the underlying tables. However, not all views are secure against tampering.
+ If the view has the `security_invoker` property set to true, access to the underlying base relations is determined by the permissions of the user executing the query, rather than the view owner. Thus, the user of a security invoker view must have the relevant permissions on the view and its underlying base relations.
+ If any of the underlying base relations is a security invoker view, it will be treated as if it had been accessed directly from the original query. Thus, a security invoker view will always check its underlying base relations using the permissions of the current user, even if it is accessed from a view without the `security_invoker` property.
+ Functions called in the view are treated the same as if they had been called directly from the query using the view. Therefore, the user of a view must have permissions to call all functions used by the view. Functions in the view are executed with the privileges of the user executing the query or the function owner, depending on whether the functions are defined as `SECURITY INVOKER` or `SECURITY DEFINER`.
+ The user creating or replacing a view must have `USAGE` privileges on any schemas referred to in the view query, in order to look up the referenced objects in those schemas.
+ When `CREATE OR REPLACE VIEW` is used on an existing view, only the view's defining `SELECT` rule, plus any `WITH ( ... )` parameters and its `CHECK OPTION` are changed. Other view properties, including ownership, permissions, and non-SELECT rules, remain unchanged. You must own the view to replace it (this includes being a member of the owning role).
## Updatable views
Simple views are automatically updatable: the system will allow `INSERT`, `UPDATE`, and `DELETE` statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:
+ The view must have exactly one entry in its `FROM` list, which must be a table or another updatable view.
+ The view definition must not contain `WITH`, `DISTINCT`, `GROUP BY`, `HAVING`, `LIMIT`, or `OFFSET` clauses at the top level.
+ The view definition must not contain set operations (`UNION`, `INTERSECT`, or `EXCEPT`) at the top level.
+ The view's select list must not contain any aggregates, window functions, or set-returning functions.
An automatically updatable view may contain a mix of updatable and non-updatable columns. A column is updatable if it's a simple reference to an updatable column of the underlying base relation. Otherwise, the column is read-only, and an error occurs if an `INSERT` or `UPDATE` statement attempts to assign a value to it.
A more complex view that doesn't satisfy all these conditions is read-only by default: the system doesn't allow an insert, update, or delete on the view.
**Note**
The user performing the insert, update, or delete on the view must have the corresponding insert, update, or delete privilege on the view. By default, the view's owner must have the relevant privileges on the underlying base relations, while the user performing the update doesn't need any permissions on the underlying base relations. However, if the view has security\$1invoker set to true, the user performing the update, rather than the view owner, must have the relevant privileges on the underlying base relations.
## Examples
To create a view consisting of all comedy films.
```
CREATE VIEW comedies AS
SELECT *
FROM films
WHERE kind = 'Comedy';
```
Create a view with `LOCAL CHECK OPTION`.
```
CREATE VIEW pg_comedies AS
SELECT *
FROM comedies
WHERE classification = 'PG'
WITH CASCADED CHECK OPTION;
```
Create a recursive view.
```
CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
VALUES (1)
UNION ALL
SELECT n+1 FROM nums_1_100 WHERE n < 100;
```
## Compatibility
`CREATE OR REPLACE VIEW` is a PostgreSQL language extension. The `WITH ( ... )` clause is an extension as well, as are security barrier views and security invoker views. Aurora DSQL supports these language extensions.
# `ALTER VIEW`
The `ALTER VIEW` statement allows changing various properties of an existing view, and Aurora DSQL supports all the PostgreSQL syntax for this command.
## Supported syntax
```
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )
```
## Description
`ALTER VIEW` changes various auxiliary properties of a view. (If you want to modify the view's defining query, use `CREATE OR REPLACE VIEW`.) You must own the view to use `ALTER VIEW`. To change a view's schema, you must also have `CREATE` privilege on the new schema. To alter the owner, you must be able to `SET ROLE` to the new owning role, and that role must have `CREATE` privilege on the view's schema.
## Parameters
**`name`**
The name (optionally schema-qualified) of an existing view.
**`column_name`**
Name of an existing column, or new name for an existing column.
**`IF EXISTS`**
Don't throw an error if the view doesn't exist. A notice is issued in this case.
**`SET/DROP DEFAULT`**
These forms set or remove the default value for a column. The default value for a view column is substituted into any `INSERT` or `UPDATE` command where the target is the view.
**`new_owner`**
The user name of the new owner of the view.
**`new_name`**
The new name for the view.
**`new_schema`**
The new schema for the view.
**`SET ( view_option_name [= view_option_value] [, ... ] )`**
Sets a view option. The following are supported options:
+ `check_option (enum)` - Changes the check option of the view. The value must be `local` or `cascaded`.
+ `security_barrier (boolean)` - Changes the security-barrier property of the view.
+ `security_invoker (boolean)` - Changes the security-invoker property of the view.
**`RESET ( view_option_name [, ... ] )`**
Resets a view option to its default value.
## Examples
Renaming the view `foo` to `bar`:
```
ALTER VIEW foo RENAME TO bar;
```
Attaching a default column value to an updatable view:
```
CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1); -- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2); -- ts will receive the current time
```
## Compatibility
`ALTER VIEW` is a PostgreSQL extension of the SQL standard that Aurora DSQL supports.
# `DROP VIEW`
The `DROP VIEW` statement removes an existing view. Aurora DSQL supports the full PostgreSQL syntax for this command.
## Supported syntax
```
DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```
## Description
`DROP VIEW` drops an existing view. To execute this command you must be the owner of the view.
## Parameters
**`IF EXISTS`**
Don't throw an error if the view doesn't exist. A notice is issued in this case.
**`name`**
The name (optionally schema-qualified) of the view to remove.
**`CASCADE`**
Automatically drop objects that depend on the view (such as other views), and in turn all objects that depend on those objects.
**`RESTRICT`**
Refuse to drop the view if any objects depend on it. This is the default.
## Examples
```
DROP VIEW kinds;
```
## Compatibility
This command conforms to the SQL standard, except that the standard only allows one view to be dropped per command, and apart from the `IF EXISTS` option, which is a PostgreSQL extension that Aurora DSQL supports.
# Migrating from PostgreSQL to Aurora DSQL
Aurora DSQL is designed to be [PostgreSQL compatible](working-with-postgresql-compatibility.md), supporting core relational features such as ACID transactions, secondary indexes, joins, and standard DML operations. Most existing PostgreSQL applications can migrate to Aurora DSQL with minimal changes.
This section provides practical guidance for migrating your application to Aurora DSQL, including framework compatibility, migration patterns, and architectural considerations.
## Framework and ORM compatibility
Aurora DSQL uses the standard PostgreSQL wire protocol, ensuring compatibility with PostgreSQL drivers and frameworks. Most popular ORMs work with Aurora DSQL with minimal or no changes. See [Aurora DSQL adapters and dialects](aws-sdks.md#aurora-dsql-adapters) for reference implementations and available ORM integrations.
## Common migration patterns
When migrating from PostgreSQL to Aurora DSQL, some features work differently or have alternative syntax. This section provides guidance on common migration scenarios.
### DDL operation alternatives
Aurora DSQL provides modern alternatives to traditional PostgreSQL DDL operations:
**Index creation**
Use `CREATE INDEX ASYNC` instead of `CREATE INDEX` for non-blocking index creation.
**Benefit:** Zero-downtime index creation on large tables.
**Data removal**
Use `DELETE FROM table_name` instead of `TRUNCATE`.
**Alternative:** For complete table recreation, use `DROP TABLE` followed by `CREATE TABLE`.
**System configuration**
Aurora DSQL is fully managed, so configuration is handled automatically based on workload patterns. Use the AWS Management Console or API to manage cluster settings.
**Benefit:** No need for database tuning or parameter management.
### Schema design patterns
Adapt these common PostgreSQL patterns for Aurora DSQL compatibility:
**Referential integrity patterns**
Aurora DSQL supports table relationships and `JOIN` operations. For referential integrity, implement validation in your application layer. This design aligns with modern distributed database patterns where application-layer validation provides more flexibility and avoids performance bottlenecks from cascading operations.
**Pattern:** Implement referential integrity checks in your application layer using consistent naming conventions, validation logic, and transaction boundaries. Many high-scale applications prefer this approach for better control over error handling and performance.
**Temporary data handling**
Use CTEs, subqueries, or regular tables with cleanup logic instead of temporary tables.
**Alternative:** Create tables with session-specific names and clean them up in your application.
## Understanding architectural differences
Aurora DSQL's distributed, serverless architecture intentionally differs from traditional PostgreSQL in several areas. These differences enable Aurora DSQL's key benefits of simplicity and scale.
### Simplified database model
**Single database per cluster**
Aurora DSQL provides one built-in database named `postgres` per cluster.
**Migration tip:** If your application uses multiple databases, create separate Aurora DSQL clusters for logical separation, or use schemas within a single cluster.
**No temporary tables**
For temporary data handling, you SHOULD use common table expressions (CTEs) and subqueries, which provide flexible alternatives for complex queries.
**Alternative:** Use CTEs with `WITH` clauses for temporary result sets, or regular tables with unique naming for session-specific data.
**Automatic storage management**
Aurora DSQL eliminates tablespaces and manual storage management. Storage automatically scales and optimizes based on your data patterns.
**Benefit:** No need to monitor disk space, plan storage allocation, or manage tablespace configurations.
### Modern application patterns
Aurora DSQL encourages modern application development patterns that improve maintainability and performance:
**Application-level logic instead of database triggers**
For trigger-like functionality, implement event-driven logic in your application layer.
**Migration strategy:** Move trigger logic to application code, use event-driven architectures with AWS services like EventBridge, or implement audit trails using application logging.
**SQL functions for data processing**
Aurora DSQL supports SQL-based functions but not procedural languages like PL/pgSQL.
**Alternative:** Use SQL functions for data transformations, or move complex logic to your application layer or AWS Lambda functions.
**Optimistic concurrency control instead of pessimistic locking**
Aurora DSQL uses optimistic concurrency control (OCC), a lock-free approach that differs from traditional database locking mechanisms. Instead of acquiring locks that block other transactions, Aurora DSQL allows transactions to proceed without blocking and detects conflicts at commit time. This eliminates deadlocks and prevents slow transactions from blocking other operations.
**Key difference:** When conflicts occur, Aurora DSQL returns a serialization error rather than making transactions wait for locks. This requires applications to implement retry logic, similar to handling lock timeouts in traditional databases, but conflicts are resolved immediately rather than causing blocking waits.
**Design pattern:** Implement idempotent transaction logic with retry mechanisms. Design schemas to minimize contention by using random primary keys and spreading updates across your key range. For details, see [Concurrency control in Aurora DSQL](working-with-concurrency-control.md).
**Relationships and referential integrity**
Aurora DSQL supports foreign key relationships between tables, including ` JOIN ` operations. For referential integrity, implement validation in your application layer. While enforcing referential integrity can be valuable, cascading operations (like cascading deletes) can create unexpected performance issues—for example, deleting an order with 1,000 line items becomes a 1,001-row transaction. Many customers avoid foreign key constraints for this reason.
**Design pattern:** Implement referential integrity checks in your application layer, use eventual consistency patterns, or leverage AWS services for data validation.
### Operational simplifications
Aurora DSQL eliminates many traditional database maintenance tasks, reducing operational overhead:
**No manual maintenance required**
Aurora DSQL automatically manages storage optimization, statistics collection, and performance tuning. Traditional maintenance commands like `VACUUM` are handled by the system.
**Benefit:** Eliminates the need for database maintenance windows, vacuum scheduling, and system parameter tuning.
**Automatic partitioning and scaling**
Aurora DSQL automatically partitions and distributes your data based on access patterns. Use UUIDs or application-generated IDs for optimal distribution.
**Migration tip:** Remove manual partitioning logic and let Aurora DSQL handle data distribution. Use UUIDs or application-generated IDs for optimal distribution. If your application requires sequential identifiers, see [Sequences and identity columns](sequences-identity-columns.md).
# Agentic migration with AI tools
AI coding agents can accelerate your migration to Aurora DSQL by analyzing schemas, transforming code, and executing DDL migrations with built-in safety checks.
## Using Kiro for migration
Coding agents such as [Kiro](https://kiro.dev/) can help you analyze and migrate your PostgreSQL code to Aurora DSQL:
+ **Schema analysis:** Upload your existing schema files and ask Kiro to identify potential compatibility issues and suggest alternatives
+ **Code transformation:** Provide your application code and ask Kiro to help refactor trigger logic, replace sequences with UUIDs, or modify transaction patterns
+ **Migration planning:** Ask Kiro to create a step-by-step migration plan based on your specific application architecture
+ **DDL migrations:** Execute schema modifications using the table recreation pattern with built-in safety checks and user verification
**Example prompts:**
```
"Analyze this PostgreSQL schema for DSQL compatibility and suggest alternatives for any unsupported features"
"Help me refactor this trigger function into application-level logic for DSQL migration"
"Create a migration checklist for moving my Django application from PostgreSQL to DSQL"
"Drop the legacy_status column from the orders table"
"Change the price column from VARCHAR to DECIMAL in the products table"
```
## DDL migration with table recreation
When using AI agents with the Aurora DSQL MCP server, certain ALTER TABLE operations use a *table recreation pattern* that safely migrates your data. The agent handles the complexity while keeping you informed at each step.
The following operations use the table recreation pattern:
| Operation | Approach |
| --- | --- |
| DROP COLUMN | Exclude column from new table |
| ALTER COLUMN TYPE | Cast data type during migration |
| ALTER COLUMN SET/DROP NOT NULL | Change constraint in new table definition |
| ALTER COLUMN SET/DROP DEFAULT | Define default in new table definition |
| ADD/DROP CONSTRAINT | Include or remove constraint in new table |
| MODIFY PRIMARY KEY | Define new PK with uniqueness validation |
| Split/Merge columns | Use SPLIT\$1PART, SUBSTRING, or CONCAT |
The following ALTER TABLE operations are supported directly without table recreation:
+ `ALTER TABLE ... RENAME COLUMN` – Rename a column
+ `ALTER TABLE ... RENAME TO` – Rename a table
+ `ALTER TABLE ... ADD COLUMN` – Add a new column
**Safety features:** When executing DDL migrations, AI agents present the migration plan, verify data compatibility, confirm row counts, and request explicit approval before any destructive operations like DROP TABLE.
**Batched migrations:** For tables exceeding 3,000 rows, the agent automatically batches the migration in increments of 500-1,000 rows to stay within transaction limits.
## Aurora DSQL MCP server
The Aurora DSQL Model Context Protocol (MCP) server allows AI assistants to connect directly to your Aurora DSQL cluster and search Aurora DSQL documentation. This enables the AI to:
+ Analyze your existing schema and suggest migration changes
+ Execute DDL migrations with the table recreation pattern
+ Test queries and verify compatibility during migration
+ Provide accurate, up-to-date guidance based on the latest Aurora DSQL documentation
To use the Aurora DSQL MCP server with AI assistants, see the setup instructions for the [ Aurora DSQL MCP server](SECTION_aurora-dsql-mcp-server.md).
## Aurora DSQL considerations for PostgreSQL compatibility
Aurora DSQL has feature support differences from self-managed PostgreSQL that enable its distributed architecture, serverless operation, and automatic scaling. Most applications work within these differences without modification.
For general considerations, see [Considerations for working with Amazon Aurora DSQL](considerations.md). For quotas and limits, see [Cluster quotas and database limits in Amazon Aurora DSQL](CHAP_quotas.md).
+ Aurora DSQL uses a single built-in database named `postgres` per cluster. For logical separation, create separate Aurora DSQL clusters or use schemas within a single cluster.
+ The `postgres` database uses UTF-8 character encoding, which provides broad international character support.
+ The database uses the `C` collation only.
+ Aurora DSQL uses `UTC` as the system timezone. Postgres stores all timezone-aware dates and times internally in UTC. You can set the `TimeZone` configuration parameter to convert how it is displayed to the client and serve as the default for client input that the server will use to convert to UTC internally.
+ The transaction isolation level is fixed at PostgreSQL `Repeatable Read`.
+ Transactions have the following constraints:
+ DDL and DML operations require separate transactions
+ A transaction can include only 1 DDL statement
+ A transaction can modify up to 3,000 rows, regardless of the number of secondary indexes
+ The 3,000-row limit applies to all DML statements (`INSERT`, `UPDATE`, `DELETE`)
+ Database connections time out after 1 hour.
+ Aurora DSQL manages permissions through schema-level grants. Admin users create schemas using `CREATE SCHEMA` and grant access using `GRANT USAGE ON SCHEMA`. Admin users manage objects in the public schema, while non-admin users create objects in user-created schemas for clear ownership boundaries. For more information, see [Authorizing database roles to use SQL in your database](using-database-and-iam-roles.md#using-database-and-iam-roles-custom-database-roles-sql).
## Need help with migration?
If you encounter features that are critical for your migration but not currently supported in Aurora DSQL, see [Providing feedback on Amazon Aurora DSQL](providing-feedback.md) for information on how to share feedback with AWS.
# Concurrency control in Aurora DSQL
Concurrency allows multiple sessions to access and modify data simultaneously without compromising data integrity and consistency. Aurora DSQL provides [ PostgreSQL compatibility](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility.html) while implementing a modern, lock-free concurrency control mechanism. It maintains full ACID compliance through snapshot isolation, ensuring data consistency and reliability.
A key advantage of Aurora DSQL is its lock-free architecture, which eliminates common database performance bottlenecks. Aurora DSQL prevents slow transactions from blocking other operations and eliminates the risk of deadlocks. This approach makes Aurora DSQL particularly valuable for high-throughput applications where performance and scalability are critical.
## Concurrency control responses
Aurora DSQL uses optimistic concurrency control (OCC), which works differently from traditional lock-based systems. Instead of using locks, OCC evaluates conflicts at commit time. When Aurora DSQL detects a conflict, it returns a PostgreSQL serialization failure with SQLSTATE code `40001`. The response message includes an OCC code that identifies the type of conflict:
**OC000 — Data conflict**
Two transactions attempted to modify the same row. The transaction with the earliest commit time succeeds, and the conflicting transaction receives the OC000 response:
```
ERROR: mutation conflicts with another transaction, retry as needed (OC000) (SQLSTATE 40001)
```
**OC001 — Schema conflict**
The session's cached schema catalog is out of date. When Aurora DSQL detects that the catalog version has changed since the session loaded its cache, and the transaction can't safely rebase to the current version, the transaction receives the OC001 response:
```
ERROR: schema has been updated by another transaction, please retry: (OC001) (SQLSTATE 40001)
```
Any operation that modifies the schema catalog can cause an OC001 response, including DDL statements such as `CREATE TABLE` and `ALTER TABLE`, as well as `GRANT` and `REVOKE` statements. For more information, see [DDL and distributed transactions in Aurora DSQL](working-with-ddl.md).
Design your applications to implement retry logic to handle these responses. The ideal design pattern is idempotent, enabling transaction retry as a first recourse whenever possible. The recommended logic is similar to the abort and retry logic in a standard PostgreSQL lock timeout or deadlock situation. However, OCC requires your applications to exercise this logic more frequently.
## Guidelines for optimizing transaction performance
To optimize performance, minimize high contention on single keys or small key ranges. To achieve this goal, design your schema to spread updates over your cluster key range by using the following guidelines:
+ Choose a random primary key for your tables.
+ Avoid patterns that increase contention on single keys. This approach ensures optimal performance even as transaction volume grows.
# DDL and distributed transactions in Aurora DSQL
Data definition language (DDL) behaves differently in Aurora DSQL from PostgreSQL. Aurora DSQL features a Multi-AZ distributed and shared-nothing database layer built on top of multi-tenant compute and storage fleets. Because no single primary database node or leader exists, the database catalog is distributed. Thus, Aurora DSQL manages DDL schema changes as distributed transactions.
Specifically, DDL behaves differently in Aurora DSQL as follows:
**Concurrency control responses**
Because the database catalog is distributed, Aurora DSQL manages DDL schema changes as distributed transactions that update the catalog version. Sessions that have a cached copy of the catalog at an earlier version can receive a concurrency control response with SQLSTATE code `40001` and OCC code `OC001` when they next interact with storage.
For example, consider the following sequence of actions:
1. In session 1, a user adds a column to the table `mytable`. This updates the catalog version.
1. In session 2, a user attempts to insert a row into `mytable`. This session still has the previous catalog version cached.
Aurora DSQL returns `SQL Error [40001]: ERROR: schema has been updated by another transaction, please retry: (OC001)`.
An OC001 response can also occur when the schema change has already completed before the affected transaction starts. Aurora DSQL query processors discover catalog changes reactively during query execution, so a session that has been idle might still be operating with a stale catalog version. On retry, the session refreshes its catalog cache and the transaction typically succeeds.
**DDL and DML in the same transaction**
Transactions in Aurora DSQL can contain only one DDL statement and can't have both DDL and DML statements. This restriction means that you can't create a table and insert data into the same table within the same transaction. For example, Aurora DSQL supports the following sequential transactions.
```
BEGIN;
CREATE TABLE mytable (ID_col integer);
COMMIT;
BEGIN;
INSERT into FOO VALUES (1);
COMMIT;
```
Aurora DSQL doesn't support the following transaction, which includes both `CREATE` and `INSERT` statements.
```
BEGIN;
CREATE TABLE FOO (ID_col integer);
INSERT into FOO VALUES (1);
COMMIT;
```
**Asynchronous DDL**
In standard PostgreSQL, DDL operations such as `CREATE INDEX` lock the affected table, making it unavailable for reads and writes from other sessions. In Aurora DSQL, these DDL statements run asynchronously using a background manager. Access to the affected table isn't blocked. Thus, DDL on large tables can run without downtime or performance impact. For more information about the asynchronous job manager in Aurora DSQL, see [Asynchronous indexes in Aurora DSQL](working-with-create-index-async.md).
# Primary keys in Aurora DSQL
In Aurora DSQL, a primary key is a feature that physically 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.
Aurora DSQL also uses the primary key to generate a cluster-wide unique key for each row in tables and indexes. This unique key 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.
## Guidelines for choosing a primary key
When choosing and using a primary key in Aurora DSQL, consider the following guidelines:
+ 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.
+ 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.
+ 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.
+ 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.
+ 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).
+ 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).
# Sequences and identity columns
Sequences and identity columns generate integer values and are useful when compact or human-readable identifiers are needed. These values involve allocation and caching behavior described in the [`CREATE SEQUENCE`](create-sequence-syntax-support.md) documentation.
**Topics**
+ [Sequence manipulation functions](sequence-functions-syntax-support.md)
+ [Identity columns](sequences-identity-columns-overview.md)
+ [Working with sequences and identity columns](sequences-identity-columns-working-with.md)
# Sequence manipulation functions
This section describes functions for operating on sequence objects, also called sequence generators or just sequences. Sequence objects are special single-row tables created with [`CREATE SEQUENCE`](create-sequence-syntax-support.md). Sequence objects are commonly used to generate unique identifiers for rows of a table. The sequence functions provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.
**Important**
When using sequences, the cache value should be carefully considered. For more information, see the Important callout on the [`CREATE SEQUENCE`](create-sequence-syntax-support.md) page.
For guidance on how best to use sequences based on workload patterns, see [Working with sequences and identity columns](sequences-identity-columns-working-with.md).
| Function | Description |
| --- | --- |
| nextval ( regclass ) → bigint | Advances the sequence object to its next value and returns that value. This is done atomically: even if multiple sessions run nextval concurrently, each will safely receive a distinct sequence value. If the sequence object has been created with default parameters, successive nextval calls will return increasing values beginning with 1. Other behaviors can be obtained by using appropriate parameters in the [`CREATE SEQUENCE`](create-sequence-syntax-support.md) command. This function requires USAGE or UPDATE privilege on the sequence. |
| setval ( regclass, bigint [, boolean ] ) → bigint | Sets the sequence object's current value, and optionally its is\$1called flag. The two-parameter form sets the sequence's last\$1value field to the specified value and sets its is\$1called field to true, meaning that the next nextval will advance the sequence before returning a value. The value that will be reported by currval is also set to the specified value. In the three-parameter form, is\$1called can be set to either true or false. true has the same effect as the two-parameter form. If it's set to false, the next nextval will return exactly the specified value, and sequence advancement commences with the following nextval. Furthermore, the value reported by currval isn't changed here. For example: SELECT setval('myseq', 42); -- Next nextval will return 43
SELECT setval('myseq', 42, true); -- Same as above
SELECT setval('myseq', 42, false); -- Next nextval will return 42 The result returned by setval is just the value of its second argument. This function requires UPDATE privilege on the sequence. |
| currval ( regclass ) → bigint | Returns the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have run nextval because the current session did. This function requires USAGE or SELECT privilege on the sequence. |
| lastval () → bigint | Returns the value most recently returned by nextval in the current transaction. This function is identical to currval, except that instead of taking the sequence name as an argument it refers to whichever sequence nextval was most recently applied to in the current transaction. It's an error to call lastval if nextval hasn't yet been called in the current transaction. This function requires USAGE or SELECT privilege on the last used sequence. |
**Warning**
The value obtained by `nextval` isn't reclaimed for re-use if the calling transaction later aborts. This means that transaction aborts or database crashes can result in gaps in the sequence of assigned values. That can happen without a transaction abort, too. For example, an `INSERT` with an `ON CONFLICT` clause will compute the to-be-inserted tuple, including doing any required `nextval` calls, before detecting any conflict that would cause it to follow the `ON CONFLICT` rule instead. Thus, Aurora DSQL's sequence objects *can't be used to obtain "gapless" sequences*.
Likewise, sequence state changes made by `setval` are immediately visible to other transactions, and aren't undone if the calling transaction rolls back.
The sequence to be operated on by a sequence function is specified by a `regclass` argument, which is simply the OID of the sequence in the `pg_class` system catalog. You don't have to look up the OID by hand, however, because the `regclass` data type's input converter will do the work for you. See the PostgreSQL documentation on [Object Identifier Types](https://www.postgresql.org/docs/current/datatype-oid.html) for details.
# Identity columns
**Important**
When using identity columns, the cache value should be carefully considered. For more information, see the Important callout on the [`CREATE SEQUENCE`](create-sequence-syntax-support.md) page.
For guidance on how best to use identity columns based on workload patterns, see [Working with sequences and identity columns](sequences-identity-columns-working-with.md).
An identity column is a special column that's generated automatically from an implicit sequence. It can be used to generate key values. To create an identity column, use the `GENERATED ... AS IDENTITY` clause in [`CREATE TABLE`](create-table-syntax-support.md), for example:
```
CREATE TABLE people (
id bigint GENERATED ALWAYS AS IDENTITY (CACHE 70000),
...
);
```
or alternatively:
```
CREATE TABLE people (
id bigint GENERATED BY DEFAULT AS IDENTITY (CACHE 70000),
...
);
```
See [`CREATE TABLE`](create-table-syntax-support.md) for more details.
If an `INSERT` command is executed on the table with the identity column and no value is explicitly specified for the identity column, then a value generated by the implicit sequence is inserted. For example, with the preceding definitions and assuming additional appropriate columns, writing:
```
INSERT INTO people (name, address) VALUES ('A', 'foo');
INSERT INTO people (name, address) VALUES ('B', 'bar');
```
would generate values for the `id` column starting at 1 and result in the following table data:
```
id | name | address
----+------+---------
1 | A | foo
2 | B | bar
```
Alternatively, the keyword `DEFAULT` can be specified in place of a value to explicitly request the sequence-generated value:
```
INSERT INTO people (id, name, address) VALUES (DEFAULT, 'C', 'baz');
```
Similarly, the keyword `DEFAULT` can be used in `UPDATE` commands.
Thus, in many ways, an identity column behaves like a column with a default value.
The clauses `ALWAYS` and `BY DEFAULT` in the column definition determine how explicitly user-specified values are handled in `INSERT` and `UPDATE` commands. In an `INSERT` command, if `ALWAYS` is selected, a user-specified value is only accepted if the `INSERT` statement specifies `OVERRIDING SYSTEM VALUE`. If `BY DEFAULT` is selected, then the user-specified value takes precedence. Thus, using `BY DEFAULT` results in a behavior more similar to default values, where the default value can be overridden by an explicit value, whereas `ALWAYS` provides some more protection against accidentally inserting an explicit value.
The data type of an identity column must be one of the data types supported by sequences. (See [`CREATE SEQUENCE`](create-sequence-syntax-support.md).) The properties of the associated sequence might be specified when creating an identity column (see [`CREATE TABLE`](create-table-syntax-support.md)) or changed afterwards (see [`ALTER TABLE`](alter-table-syntax-support.md)).
An identity column is automatically marked as `NOT NULL`. An identity column, however, doesn't guarantee uniqueness. (A sequence normally returns unique values, but a sequence could be reset, or values could be inserted manually into the identity column, as discussed previously.) Uniqueness would need to be enforced using a `PRIMARY KEY` or `UNIQUE` constraint.
# Working with sequences and identity columns
This section helps you understand how best to use sequences and identity columns based on workload patterns.
**Important**
See the Important callout on the [`CREATE SEQUENCE`](create-sequence-syntax-support.md) page for more details on allocation and caching behavior.
## Choosing identifier types
Amazon Aurora DSQL supports both UUID-based identifiers and integer values generated using sequences or identity columns. These options differ in how values are allocated and how they scale under load.
UUID values can be generated without coordination and are well suited to workloads where identifiers are created frequently or across many sessions. Because Amazon Aurora DSQL is designed for distributed operation, avoiding coordination is often beneficial. For this reason, UUIDs are recommended as the default identifier type, especially for primary keys in workloads where scalability is important and strict ordering of identifiers isn't required.
Sequences and identity columns generate compact integer values that are convenient for human-readable identifiers, reporting, and external interfaces. When numeric identifiers are preferred for usability or integration reasons, consider using a sequence or identity column in combination with UUID-based identifiers. When integer sequence or identity values are required, choosing an appropriate cache size becomes an important part of workload design. See the following section for guidance on choosing a cache size.
## Choosing a cache size
Selecting an appropriate cache value is an important part of using sequences and identity columns effectively. The cache setting determines how identifier allocation behaves under load, influencing both system throughput and how closely values reflect allocation order.
**A larger cache size of `CACHE >= 65536` is well suited when:**
+ Identifiers are generated at high frequency
+ Many sessions insert concurrently
+ The workload can tolerate gaps and visible ordering effects
For example, high-volume event ingestion workloads (such as IoT or telemetry), as well as operational identifiers like job run IDs, support case references, or internal order numbers typically benefit from larger cache sizes, where identifiers are generated frequently and strict ordering isn't required.
**A cache size of 1 is better aligned when:**
+ Allocation rates are relatively low
+ Identifiers are expected to follow allocation order more closely over time
+ Minimizing gaps is more important than maximum throughput
Workloads such as assigning account or reference numbers, where identifiers are generated less often and closer ordering is desirable, are better aligned with a cache size of 1.
# Asynchronous indexes in Aurora DSQL
The `CREATE INDEX ASYNC` command creates an index on one or more columns of a specified table. This command is an asynchronous DDL operation that doesn't block other transactions. When you run `CREATE INDEX ASYNC`, Aurora DSQL immediately returns a `job_id`.
You can monitor the status of this asynchronous job using the `sys.jobs` system view. While the index creation job is in progress, you can use these procedures and commands:
**`sys.wait_for_job(job_id)'your_index_creation_job_id'`**
Blocks the current session until the specified job completes or fails. Returns a Boolean value indicating success or failure.
**`DROP INDEX`**
Cancels an in-progress index build job.
When the asynchronous index creation completes, Aurora DSQL updates the system catalog to mark the index as active.
Note that concurrent transactions accessing objects in the same namespace during this update might encounter concurrency errors.
When Aurora DSQL finishes an asynchronous index task, it updates the system catalog to show that the index is active. If other transactions reference the objects in the same namespace at this time, you might see a concurrency error.
## Syntax
`CREATE INDEX ASYNC` uses the following syntax.
```
CREATE [ UNIQUE ] INDEX ASYNC [ IF NOT EXISTS ] name ON table_name
( { column_name } [ NULLS { FIRST | LAST } ] )
[ INCLUDE ( column_name [, ...] ) ]
[ NULLS [ NOT ] DISTINCT ]
```
## Parameters
**`UNIQUE`**
Indicates to Aurora DSQL to check for duplicate values in the table when it creates the index and each time you add data. If you specify this parameter, insert and update operations that would result in duplicate entries generate an error.
**`IF NOT EXISTS`**
Indicates that Aurora DSQL shouldn't throw an exception if an index with the same name already exists. In this situation, Aurora DSQL doesn't create the new index. Note that the index you're trying to create could have a very different structure from the index that exists. If you specify this parameter, the index name is required.
**`name`**
The name of the index. You can't include the name of your schema in this parameter.
Aurora DSQL creates the index in the same schema as its parent table. The name of the index must be distinct from the name of any other object, such as a table or index, in the schema.
If you don't specify a name, Aurora DSQL generates a name automatically based on the name of the parent table and indexed column. For example, if you run `CREATE INDEX ASYNC on table1 (col1, col2)`, Aurora DSQL automatically names the index `table1_col1_col2_idx`.
**`NULLS FIRST | LAST`**
The sort order of null and non-null columns. `FIRST` indicates that Aurora DSQL should sort null columns before non-null columns. `LAST` indicates that Aurora DSQL should sort null columns after non-null columns.
**`INCLUDE`**
A list of columns to include in the index as non-key columns. You can't use a non-key column in an index scan search qualification. Aurora DSQL ignores the column in terms of uniqueness for an index.
**`NULLS DISTINCT | NULLS NOT DISTINCT`**
Specifies whether Aurora DSQL should consider null values as distinct in a unique index. The default is `DISTINCT`, meaning that a unique index can contain multiple null values in a column. `NOT DISTINCT` indicates that an index can't contain multiple null values in a column.
## Usage notes
Consider the following guidelines:
+ The `CREATE INDEX ASYNC` command doesn't introduce locks. It also doesn't affect the base table that Aurora DSQL uses to create the index.
+ During schema migration operations, the `sys.wait_for_job(job_id)'your_index_creation_job_id'` procedure is useful. It ensures that subsequent DDL and DML operations target the newly created index.
+ Every time Aurora DSQL runs a new asynchronous task, it checks the `sys.jobs` view and deletes tasks that have a status of `completed` or `failed` for more than 30 minutes. Thus, `sys.jobs` primarily shows in-progress tasks and doesn’t contain information about old tasks.
+ If Aurora DSQL fails to build an asynchronous index, the index stays `INVALID`. For unique indexes, DML operations are subject to uniqueness constraints until you drop the index. We recommend that you drop invalid indexes and recreate them.
## Creating an index: example
The following example demonstrates how to create a schema, a table, and then an index.
1. Create a table named `test.departments`.
```
CREATE SCHEMA test;
CREATE TABLE test.departments (name varchar(255) primary key NOT null,
manager varchar(255),
size varchar(4));
```
1. Insert a row into the table.
```
INSERT INTO test.departments VALUES ('Human Resources', 'John Doe', '10')
```
1. Create an asynchronous index.
```
CREATE INDEX ASYNC test_index on test.departments(name, manager, size);
```
The `CREATE INDEX` command returns a job ID, as shown below.
```
job_id
--------------------------
jh2gbtx4mzhgfkbimtgwn5j45y
```
The `job_id` indicates that Aurora DSQL has submitted a new job to create the index. You can use the procedure `sys.wait_for_job(job_id)'your_index_creation_job_id'` to block other work on the session until the job finishes or times out.
## Querying the status of index creation: example
Query the `sys.jobs` system view to check the creation status of your index, as shown in the following example.
```
SELECT * FROM sys.jobs where job_id = 'wqhu6ewifze5xitg3umt24h5ua';
```
Aurora DSQL returns a response similar to the following.
```
job_id | status | details | job_type | class_id | object_id | object_name | start_time | update_time
----------------------------+-----------+---------+-------------+----------+-----------+-------------------+------------------------+------------------------
wqhu6ewifze5xitg3umt24h5ua | completed | | INDEX_BUILD | 1259 | 26433 | public.nt2_c1_idx | 2025-09-25 22:07:31+00 | 2025-09-25 22:07:46+00
```
The status column can be one of the following values.
| Status | Description |
| --- | --- |
| submitted | The task is submitted, but Aurora DSQL hasn't started to process it yet. |
| processing | Aurora DSQL is processing the task. |
| failed | The task failed. See the details column for more information. If Aurora DSQL failed to build the index, Aurora DSQL doesn't automatically remove the index definition. You must manually remove the index with the DROP INDEX command. |
| completed | Aurora DSQL has completed the task successfully. |
You can also query the state of the index via the catalog tables `pg_index`and `pg_class`. Specifically, the attributes `indisvalid` and `indisimmediate` can tell you what state your index is in. While Aurora DSQL creates your index, it has an initial status of `INVALID`. The `indisvalid` flag for the index returns `FALSE` or `f`, which indicates that the index isn't valid. If the flag returns `TRUE` or `t`, the index is ready.
```
SELECT relname AS index_name, indisvalid as is_valid, pg_get_indexdef(indexrelid) AS index_definition
from pg_index, pg_class
WHERE pg_class.oid = indexrelid AND indrelid = 'test.departments'::regclass;
```
```
index_name | is_valid | index_definition
------------------+----------+-------------------------------------------------------------------------------------------------------------------
department_pkey | t | CREATE UNIQUE INDEX department_pkey ON test.departments USING btree_index (title) INCLUDE (name, manager, size)
test_index1 | t | CREATE INDEX test_index1 ON test.departments USING btree_index (name, manager, size)
```
## Unique index build failures
If your asynchronous unique index build job shows a failed state with the detail `Found duplicate key while validating index for UCVs`, this indicates that a unique index could not be built due to uniqueness constraint violations.
**To resolve unique index build failures**
1. Remove any rows in your primary table that have duplicate entries for the keys specified in your unique secondary index.
1. Drop the failed index.
1. Issue a new create index command.
## Detecting uniqueness violations in primary tables
The following SQL query helps you identify duplicate values in a specified column of your table. This is particularly useful when you need to enforce uniqueness on a column that isn't currently set as a primary key or doesn't have a unique constraint, such as email addresses in a user table.
The examples below demonstrate how to create a sample users table, populate it with test data containing known duplicates, and then run the detection query.
** Define table schema **
```
-- Drop the table if it exists
DROP TABLE IF EXISTS users;
-- Create the users table with a simple integer primary key
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
email VARCHAR(255),
first_name VARCHAR(100),
last_name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
**Insert sample data that includes sets of duplicate email addresses**
```
-- Insert sample data with explicit IDs
INSERT INTO users (user_id, email, first_name, last_name) VALUES
(1, 'john.doe@example.com', 'John', 'Doe'),
(2, 'jane.smith@example.com', 'Jane', 'Smith'),
(3, 'john.doe@example.com', 'Johnny', 'Doe'),
(4, 'alice.wong@example.com', 'Alice', 'Wong'),
(5, 'bob.jones@example.com', 'Bob', 'Jones'),
(6, 'alice.wong@example.com', 'Alicia', 'Wong'),
(7, 'bob.jones@example.com', 'Robert', 'Jones');
```
** Run duplicate detection query **
```
-- Query to find duplicates
WITH duplicates AS (
SELECT email, COUNT(*) as duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
)
SELECT u.*, d.duplicate_count
FROM users u
INNER JOIN duplicates d ON u.email = d.email
ORDER BY u.email, u.user_id;
```
** View all records with duplicate email addresses **
```
user_id | email | first_name | last_name | created_at | duplicate_count
---------+------------------------+------------+-----------+----------------------------+-----------------
4 | akua.mansa@example.com | Akua | Mansa | 2025-05-21 20:55:53.714432 | 2
6 | akua.mansa@example.com | Akua | Mansa | 2025-05-21 20:55:53.714432 | 2
1 | john.doe@example.com | John | Doe | 2025-05-21 20:55:53.714432 | 2
3 | john.doe@example.com | Johnny | Doe | 2025-05-21 20:55:53.714432 | 2
(4 rows)
```
**If we were to try the index creation statement now, it would fail: **
```
postgres=> CREATE UNIQUE INDEX ASYNC idx_users_email ON users(email);
job_id
----------------------------
ve32upmjz5dgdknpbleeca5tri
(1 row)
postgres=> select * from sys.jobs;
job_id | status | details | job_type | class_id | object_id | object_name | start_time | update_time
----------------------------+-----------+-----------------------------------------------------+-------------+----------+-----------+------------------------+------------------------+------------------------
qpn6aqlkijgmzilyidcpwrpova | completed | | DROP | 1259 | 26384 | | 2025-05-20 00:47:10+00 | 2025-05-20 00:47:32+00
ve32upmjz5dgdknpbleeca5tri | failed | Found duplicate key while validating index for UCVs | INDEX_BUILD | 1259 | 26396 | public.idx_users_email | 2025-05-20 00:49:49+00 | 2025-05-20 00:49:56+00
(2 rows)
```
# System tables and commands in Aurora DSQL
See the following sections to learn about the supported system tables and catalogs in Aurora DSQL as well as useful queries for fetching information about the system, such as the version.
## System tables
Aurora DSQL is compatible with PostgreSQL, so many [ system catalog tables](https://www.PostgreSQL.org/docs/current/catalogs-overview.html) and [ views](https://www.PostgreSQL.org/docs/current/views.html) from PostgreSQL also exist in Aurora DSQL.
### Important PostgreSQL catalog tables and views
The following table describes the most common tables and views you might use in Aurora DSQL.
| Name | Description |
| --- | --- |
| `pg_namespace` | Information on all schemas |
| `pg_tables` | Information on the all tables |
| `pg_attribute` | Information on all attributes |
| `pg_views` | Information on (pre-)defined views |
| `pg_class` | Describes all tables, column, indices, and similar objects |
| `pg_stats` | A view on the planner statistics |
| `pg_user` | Information on users |
| `pg_roles` | Information on users and groups |
| `pg_indexes` | Lists all indexes |
| `pg_constraint` | Lists constraints on tables |
### Supported and unsupported catalog tables
The following table indicates which tables are supported and unsupported in Aurora DSQL.
| Name | Applicable to Aurora DSQL |
| --- | --- |
| `pg_aggregate` | No |
| `pg_am` | Yes |
| `pg_amop` | No |
| `pg_amproc` | No |
| `pg_attrdef` | Yes |
| `pg_attribute` | Yes |
| `pg_authid` | No (use `pg_roles`) |
| `pg_auth_members` | Yes |
| `pg_cast` | Yes |
| `pg_class` | Yes |
| `pg_collation` | Yes |
| `pg_constraint` | Yes |
| `pg_conversion` | No |
| `pg_database` | No |
| `pg_db_role_setting` | Yes |
| `pg_default_acl` | Yes |
| `pg_depend` | Yes |
| `pg_description` | Yes |
| `pg_enum` | No |
| `pg_event_trigger` | No |
| `pg_extension` | No |
| `pg_foreign_data_wrapper` | No |
| `pg_foreign_server` | No |
| `pg_foreign_table` | No |
| `pg_index` | Yes |
| `pg_inherits` | Yes |
| `pg_init_privs` | No |
| `pg_language` | No |
| `pg_largeobject` | No |
| `pg_largeobject_metadata` | Yes |
| `pg_namespace` | Yes |
| `pg_opclass` | No |
| `pg_operator` | Yes |
| `pg_opfamily` | No |
| `pg_parameter_acl` | Yes |
| `pg_partitioned_table` | No |
| `pg_policy` | No |
| `pg_proc` | No |
| `pg_publication` | No |
| `pg_publication_namespace` | No |
| `pg_publication_rel` | No |
| `pg_range` | Yes |
| `pg_replication_origin` | No |
| `pg_rewrite` | No |
| `pg_seclabel` | No |
| `pg_sequence` | No |
| `pg_shdepend` | Yes |
| `pg_shdescription` | Yes |
| `pg_shseclabel` | No |
| `pg_statistic` | Yes |
| `pg_statistic_ext` | No |
| `pg_statistic_ext_data` | No |
| `pg_subscription` | No |
| `pg_subscription_rel` | No |
| `pg_tablespace` | No |
| `pg_transform` | No |
| `pg_trigger` | No |
| `pg_ts_config` | Yes |
| `pg_ts_config_map` | Yes |
| `pg_ts_dict` | Yes |
| `pg_ts_parser` | Yes |
| `pg_ts_template` | Yes |
| `pg_type` | Yes |
| `pg_user_mapping` | No |
### Supported and unsupported system views
The following table indicates which views are supported and unsupported in Aurora DSQL.
| Name | Applicable to Aurora DSQL |
| --- | --- |
| `pg_available_extensions` | No |
| `pg_available_extension_versions` | No |
| `pg_backend_memory_contexts` | Yes |
| `pg_config` | No |
| `pg_cursors` | No |
| `pg_file_settings` | No |
| `pg_group` | Yes |
| `pg_hba_file_rules` | No |
| `pg_ident_file_mappings` | No |
| `pg_indexes` | Yes |
| `pg_locks` | No |
| `pg_matviews` | No |
| `pg_policies` | No |
| `pg_prepared_statements` | No |
| `pg_prepared_xacts` | No |
| `pg_publication_tables` | No |
| `pg_replication_origin_status` | No |
| `pg_replication_slots` | No |
| `pg_roles` | Yes |
| `pg_rules` | No |
| `pg_seclabels` | No |
| `pg_sequences` | No |
| `pg_settings` | Yes |
| `pg_shadow` | Yes |
| `pg_shmem_allocations` | Yes |
| `pg_stats` | Yes |
| `pg_stats_ext` | No |
| `pg_stats_ext_exprs` | No |
| `pg_tables` | Yes |
| `pg_timezone_abbrevs` | Yes |
| `pg_timezone_names` | Yes |
| `pg_user` | Yes |
| `pg_user_mappings` | No |
| `pg_views` | Yes |
| `pg_stat_activity` | No |
| `pg_stat_replication` | No |
| `pg_stat_replication_slots` | No |
| `pg_stat_wal_receiver` | No |
| `pg_stat_recovery_prefetch` | No |
| `pg_stat_subscription` | No |
| `pg_stat_subscription_stats` | No |
| `pg_stat_ssl` | Yes |
| `pg_stat_gssapi` | No |
| `pg_stat_archiver` | No |
| `pg_stat_io` | No |
| `pg_stat_bgwriter` | No |
| `pg_stat_wal` | No |
| `pg_stat_database` | No |
| `pg_stat_database_conflicts` | No |
| `pg_stat_all_tables` | No |
| `pg_stat_all_indexes` | No |
| `pg_statio_all_tables` | No |
| `pg_statio_all_indexes` | No |
| `pg_statio_all_sequences` | No |
| `pg_stat_slru` | No |
| `pg_statio_user_tables` | No |
| `pg_statio_user_sequences` | No |
| `pg_stat_user_functions` | No |
| `pg_stat_user_indexes` | No |
| `pg_stat_progress_analyze` | No |
| `pg_stat_progress_basebackup` | No |
| `pg_stat_progress_cluster` | No |
| `pg_stat_progress_create_index` | No |
| `pg_stat_progress_vacuum` | No |
| `pg_stat_sys_indexes` | No |
| `pg_stat_sys_tables` | No |
| `pg_stat_xact_all_tables` | No |
| `pg_stat_xact_sys_tables` | No |
| `pg_stat_xact_user_functions` | No |
| `pg_stat_xact_user_tables` | No |
| `pg_statio_sys_indexes` | No |
| `pg_statio_sys_sequences` | No |
| `pg_statio_sys_tables` | No |
| `pg_statio_user_indexes` | No |
### The sys.jobs view
`sys.jobs` provides status information about asynchronous jobs. For example, after you [create an asynchronous index](working-with-create-index-async.md), Aurora DSQL returns a `job_uuid`. You can use this `job_uuid` with `sys.jobs` to look up the status of the job.
```
SELECT * FROM sys.jobs;
```
Aurora DSQL returns a response similar to the following.
```
job_id | status | details | job_type | class_id | object_id | object_name | start_time | update_time
----------------------------+-----------+---------+-------------+----------+-----------+-------------------+------------------------+------------------------
wqhu6ewifze5xitg3umt24h5ua | completed | | INDEX_BUILD | 1259 | 26433 | public.nt2_c1_idx | 2025-09-25 22:07:31+00 | 2025-09-25 22:07:46+00
kkngzf33dndl3daacxehpx5eba | completed | | ANALYZE | 1259 | 26419 | public.nt | 2025-09-25 21:57:05+00 | 2025-09-25 21:57:27+00
fyopxjb6ovdn7po6lrkj63cyea | completed | | DROP | 1259 | 26422 | | 2025-09-25 22:05:57+00 | 2025-09-25 22:06:03+00
```
The following table describes the columns in the `sys.jobs` view.
**sys.jobs view columns**
| Column | Type | Description |
| --- | --- | --- |
| job\$1id | text | A base-32 UUID representing the job. |
| status | text | The current status of the job. Possible values are submitted, processing, completed, and failed. For more information, see [sys.jobs status values](#dsql-sys-jobs-status-values). |
| details | text | Any relevant details about the job. If the job fails, a detailed reason is provided. |
| job\$1type | text | The type of asynchronous job. Possible values are: INDEX\$1BUILD – an asynchronous index build. ANALYZE – a system-submitted auto-analyze job. DROP – removes physical data after a DROP TABLE or DROP INDEX operation. |
| class\$1id | oid | The OID of the catalog table which contains the object. |
| object\$1id | oid | The OID of the object. |
| object\$1name | text | The fully qualified name of the object. DROP jobs cannot reference already dropped objects. If a referenced object has already been dropped, the object\$1name may be NULL. |
| start\$1time | timestamp with time zone | The timestamp at which the job was submitted. |
| update\$1time | timestamp with time zone | The timestamp at which the job row was last updated. |
**sys.jobs status values**
| Status | Description |
| --- | --- |
| submitted | The task is submitted, but Aurora DSQL hasn't started to process it yet. |
| processing | Aurora DSQL is processing the task. |
| failed | The task failed. See the details column for more information. |
| completed | Aurora DSQL has completed the task successfully. |
### The sys.iam\$1pg\$1role\$1mappings view
The view `sys.iam_pg_role_mappings` provides information about the permissions granted to IAM users. For example, if `DQSLDBConnect` is an IAM role that gives Aurora DSQL access to non-admins and a user named `testuser` is granted the `DQSLDBConnect` role and corresponding permissions, you can query the `sys.iam_pg_role_mappings` view to see which users are granted which permissions.
```
SELECT * FROM sys.iam_pg_role_mappings;
```
## Useful system metadata queries
Use these queries to get table statistics and system metadata without performing expensive operations like full table scans.
### Get estimated row count for a table
To get the approximate count of rows in a table without performing a full table scan, use the following query:
```
SELECT reltuples FROM pg_class WHERE relname = 'table_name';
```
The command returns output similar to the following:
```
reltuples
--------------
9.993836e+08
```
This approach is more efficient than `SELECT COUNT(*)` for large tables in Aurora DSQL.
### Get current Aurora DSQL major version
To get the current major version of the Aurora DSQL cluster, use the following query:
```
SELECT * FROM sys.dsql_major_version();
```
The command returns output similar to the following:
```
dsql_major_version
--------------------
1
```
This returns the major version the SQL connection is on in Aurora DSQL.
### Get current PostgreSQL version
To get the current PostgreSQL version of the Aurora DSQL cluster, use the following query:
```
SHOW server_version;
```
The command returns output similar to the following:
```
server_version
----------------
16.13
```
This returns the PostgreSQL version the SQL connection is on in Aurora DSQL.
## The `ANALYZE` command
The `ANALYZE `command collects statistics about the contents of tables in the database and stores the results in the `pg_stats` system view. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.
In Aurora DSQL, you can't run the `ANALYZE` command within an explicit transaction. `ANALYZE` isn't subject to the database transaction timeout limit.
To reduce the need for manual intervention and keep statistics consistently up to date, Aurora DSQL automatically runs `ANALYZE` as a background process. This background job is triggered automatically based on the observed rate of change in the table. It is linked to the number of rows (tuples) that have been inserted, updated, or deleted since the last analyze.
`ANALYZE` runs asynchronously in the background and its activity can be monitored in the system view sys.jobs with the following query:
```
SELECT * FROM sys.jobs WHERE job_type = 'ANALYZE';
```
**Key considerations**
**Note**
`ANALYZE` jobs are billed like other asynchronous jobs in Aurora DSQL. When you modify a table, this may indirectly trigger an automatic background statistics collection job, which can result in metering charges due to the associated system-level activity.
Background `ANALYZE`jobs, triggered automatically, collect the same types of statistics as a manual `ANALYZE` and apply them by default to user tables. System and catalog tables are excluded from this automated process.
# Working with Aurora DSQL EXPLAIN plans
Aurora DSQL uses a similar EXPLAIN plan structure to PostgreSQL, but with key additions that reflect its distributed architecture and execution model.
In this documentation, we'll provide an overview of Aurora DSQL EXPLAIN plans, highlighting the similarities and differences compared to PostgreSQL. We'll cover the various types of scan operations available in Aurora DSQL and help you understand the cost of running your queries.
## PostgreSQL VS Aurora DSQL EXPLAIN plans
Aurora DSQL is built on top of the PostgreSQL database and shares most plan structures with PostgreSQL, but has key architectural differences that affect query execution and optimization:
| Feature | PostgreSQL | Aurora DSQL |
| --- | --- | --- |
| Data Storage | Heap Storage | No heap, all rows are indexed by a unique identifier |
| Primary Key | Primary key index is separate from table data | Primary key index is the table with all extra columns as INCLUDE columns |
| Secondary Indexes | Standard secondary indexes | Work the same as PostgreSQL, with ability to include non-key columns |
| Filtering Capabilities | Index Condition, Heap Filter | Index Condition, Storage Filter, Query Processor Filter |
| Scan Types | Sequential Scan, Index Scan, Index Only Scan | Full Scan, Index Only Scan, Index Scan |
| Query Execution | Local to the Database | Distributed (compute and storage are separate) |
Aurora DSQL stores table data directly in primary key order rather than in a separate heap. Each row is identified by a unique key, typically the primary key, which allows the database to optimize lookups more efficiently. The architectural difference explains why Aurora DSQL often uses Index Only Scans in cases where PostgreSQL might choose a sequential scan.
Another key distinction is that Aurora DSQL separates compute from storage, enabling filters to be applied earlier in the execution path to reduce data movement and improve performance.
For more using EXPLAIN plans with PostgreSQL, see the [PostgreSQL EXPLAIN documentation](https://www.postgresql.org/docs/current/using-explain.html).
## Key elements in Aurora DSQL EXPLAIN plans
Aurora DSQL EXPLAIN plans provide detailed information about how queries are executed, including where filtering occurs and which columns are retrieved from storage. Understanding this output helps you optimize query performance.
Index Cond
Conditions used to navigate the index. Most efficient filtering that reduces data scanned. In Aurora DSQL, index conditions can be applied at multiple layers of the execution plan.
Projections
Columns retrieved from storage. Fewer projections mean better performance.
Storage Filter
Conditions applied at storage level. More efficient than query processor filters.
Query Processor Filter
Conditions applied at the query processor level. Requires transferring all data before filtering, which results in higher data movement and processing overhead.
## Filters in Aurora DSQL
Aurora DSQL separates compute from storage, which means that the point where filters are applied during query execution has a significant impact on performance. Filters applied before large volumes of data are transferred reduce latency and improve efficiency. The earlier a filter is applied, the less data needs to be processed, moved, and scanned, resulting in faster queries.
Aurora DSQL can apply filters at multiple stages in the query path. Understanding these stages is key to interpreting query plans and optimizing performance.
| Level | Filter Type | Description |
| --- | --- | --- |
| 1 | Index Condition | Applied while scanning the index. Limits how much data is read from storage and reduces the data sent to the compute layer. |
| 2 | Storage Filter | Applied after data is read from storage but before it’s sent to compute. An example here is a filter on an include column of an index. Reduces data transfer but not the amount read. |
| 3 | Query Processor Filter | Applied after data reaches the compute layer. All data must be transferred first, which increases latency and cost. Currently, Aurora DSQL cannot perform all filtering and projection operations on storage, so some queries might be forced to fall back to this type of filtering. |
# Reading Aurora DSQL EXPLAIN plans
Understanding how to read EXPLAIN plans is key to optimizing query performance. In this section, we’ll walk through real examples of Aurora DSQL query plans, show how different scan types behave, explain where filters are applied, and highlight opportunities for optimization.
## Sample tables used in these examples
The examples below reference two tables: `transaction` and `account`.
The `transaction` table does not have a primary key, which causes Aurora DSQL to perform full table scans when querying it.
The `account` table has an index on `customer_id`. This index includes `balance` and `status` as covering columns, which allows certain queries to be satisfied directly from the index without reading from the base table. However, the index does not include `created_at`, so queries that reference this column require additional table access.
```
CREATE TABLE transaction (
account_id uuid,
transaction_date timestamp,
description text
);
CREATE TABLE account (
customer_id uuid,
balance numeric,
status varchar,
created_at timestamp
);
CREATE INDEX ASYNC idx1 ON account (customer_id) INCLUDE (balance, status);
```
## Full Scan example
Aurora DSQL has both Sequential Scans, which is functionally identical to PostgreSQL, as well as Full Scans. The only difference between these two are that Full Scans can utilize extra filtering on storage. Due to this, it is almost always selected above Sequential Scans. Due to the similarity, we will only cover examples of the more interesting Full Scans.
Full Scans will mostly be used on tables with no primary key. Because Aurora DSQL primary keys are by default full covering indexes, Aurora DSQL will most likely use Index Only Scans on the primary key in many situations where PostgreSQL would use a Sequential Scan. As with most other databases, a table with no indexes on it will scale badly.
```
EXPLAIN SELECT account_id FROM transaction WHERE transaction_date > '2025-01-01' AND description LIKE '%external%';
```
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Full Scan (btree-table) on transaction (cost=125100.05..177933.38 rows=33333 width=16)
Filter: (description ~~ '%external%'::text)
-> Storage Scan on transaction (cost=12510.05..17793.38 rows=66666 width=16)
Projections: account_id, description
Filters: (transaction_date > '2025-01-01 00:00:00'::timestamp without time zone)
-> B-Tree Scan on transaction (cost=12510.05..17793.38 rows=100000 width=30)
```
This plan shows two filters applied at different stages. The `transaction_date > '2025-01-01'` condition is applied at the storage layer, reducing how much data is returned. The `description LIKE '%external%'` condition is applied later in the query processor, after data is transferred, making it less efficient. Pushing more selective filters into the storage or index layers generally improves performance.
## Index Only Scan example
Index Only Scans are the most optimal scan types in Aurora DSQL as they result in the fewest round trips to the storage layer and can do the most filtering. But just because you see Index Only Scan does not mean that you have the best plan. Because of all the different levels of filtering that can happen, it is essential to still pay attention to the different places filtering can happen.
```
EXPLAIN SELECT balance FROM account
WHERE customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'
AND balance > 100
AND status = 'pending';
```
```
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using idx1 on account (cost=725.05..1025.08 rows=8 width=18)
Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid)
Filter: (balance > '100'::numeric)
-> Storage Scan on idx1 (cost=12510.05..17793.38 rows=9 width=16)
Projections: balance
Filters: ((status)::text = 'pending'::text)
-> B-Tree Scan on idx1 (cost=12510.05..17793.38 rows=10 width=30)
Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid)
```
In this plan, the index condition, `customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'`), is evaluated first during the index scan, which is the most efficient stage because it limits how much data is read from storage. The storage filter, `status = 'pending'`, is applied after data is read but before it’s sent to the compute layer, reducing the amount of data transferred. Finally, the query processor filter, `balance > 100`, runs last, after the data has been moved, making it the least efficient. Of these, the index condition provides the greatest performance because it directly controls how much data is scanned.
## Index Scan example
Index Scans are similar to Index Only Scans, except they have the extra step of having to call into the base table. Because Aurora DSQL can specify storage filters, it is able to do so on both the index call as well as the lookup call.
To make this clear, Aurora DSQL presents the plan as two nodes. This way, you can clearly see how much adding an include column will help in terms of rows returned from storage.
```
EXPLAIN SELECT balance FROM account
WHERE customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'
AND balance > 100
AND status = 'pending'
AND created_at > '2025-01-01';
```
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Index Scan using idx1 on account (cost=728.18..1132.20 rows=3 width=18)
Filter: (balance > '100'::numeric)
Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid)
-> Storage Scan on idx1 (cost=12510.05..17793.38 rows=8 width=16)
Projections: balance
Filters: ((status)::text = 'pending'::text)
-> B-Tree Scan on account (cost=12510.05..17793.38 rows=10 width=30)
Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid)
-> Storage Lookup on account (cost=12510.05..17793.38 rows=4 width=16)
Filters: (created_at > '2025-01-01 00:00:00'::timestamp without time zone)
-> B-Tree Lookup on transaction (cost=12510.05..17793.38 rows=8 width=30)
```
This plan shows how filtering happens across multiple stages:
+ The index condition on `customer_id ` filters data early.
+ The storage filter on `status` further narrows results before they’re sent to compute.
+ The query processor filter on `balance` is applied later, after transfer.
+ The lookup filter on `created_at` is evaluated when fetching additional columns from the base table.
Adding frequently used columns as `INCLUDE` fields can often eliminate this lookup and improve performance.
## Best Practices
+ **Align filters with indexed columns** to push filtering earlier.
+ **Use INCLUDE columns** to allow Index-Only Scans and avoid lookups.
+ **Validate row estimates** when investigating performance issues. Aurora DSQL manages statistics automatically by running `ANALYZE` in the background based on data change rates. If estimates appear inaccurate, you can run `ANALYZE` manually to refresh statistics immediately.
+ **Avoid unindexed queries** on large tables to prevent expensive Full Scans.
# Understanding DPUs in EXPLAIN ANALYZE
Aurora DSQL provides **statement-level** Distributed Processing Unit (DPU) information in `EXPLAIN ANALYZE VERBOSE` plan output, giving you deeper visibility into query cost during development. This section explains what DPUs are and how to interpret them in the `EXPLAIN ANALYZE VERBOSE` output.
## What is a DPU?
A Distributed Processing Unit (DPU) is the normalized measure of work done by Aurora DSQL. It is composed of:
+ **ComputeDPU** – Time spent executing SQL queries
+ **ReadDPU** – Resources used to read data from storage
+ **WriteDPU** - Resources used to write data to storage
+ **MultiRegionWriteDPU** – Resources used to replicate writes to peered clusters in multi-Region configurations.
## DPU usage in EXPLAIN ANALYZE VERBOSE
Aurora DSQL extends `EXPLAIN ANALYZE VERBOSE` to include a statement-level DPU usage estimate to the end of the output. This provides immediate visibility into query cost, helping you identify workload cost drivers, tune query performance, and better forecast resource usage.
The following examples show how to interpret the statement-level DPU estimates included in EXPLAIN ANALYZE VERBOSE output.
### Example 1: SELECT Query
```
EXPLAIN ANALYZE VERBOSE SELECT * FROM test_table;
```
```
QUERY PLAN
----------------------------------------------------
Index Only Scan using test_table_pkey on public.test_table (cost=125100.05..171100.05 rows=1000000 width=36) (actual time=2.973..4.482 rows=120 loops=1)
Output: id, context
-> Storage Scan on test_table_pkey (cost=125100.05..171100.05 rows=1000000 width=36) (actual rows=120 loops=1)
Projections: id, context
-> B-Tree Scan on test_table_pkey (cost=125100.05..171100.05 rows=1000000 width=36) (actual rows=120 loops=1)
Query Identifier: qymgw1m77maoe
Planning Time: 11.415 ms
Execution Time: 4.528 ms
Statement DPU Estimate:
Compute: 0.01607 DPU
Read: 0.04312 DPU
Write: 0.00000 DPU
Total: 0.05919 DPU
```
In this example, the SELECT statement performs an index-only scan, so most of the cost comes from Read DPU (0.04312), representing the data retrieved from storage and Compute DPU (0.01607), which reflects the compute resources used to process and return the results. There is no Write DPU since the query doesn't modify data. The total DPU (0.05919) is the sum of Compute \$1 Read \$1 Write.
### Example 2: INSERT Query
```
EXPLAIN ANALYZE VERBOSE INSERT INTO test_table VALUES (1, 'name1'), (2, 'name2'), (3, 'name3');
```
```
QUERY PLAN
----------------------------------------------------
Insert on public.test_table (cost=0.00..0.04 rows=0 width=0) (actual time=0.055..0.056 rows=0 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=122) (actual time=0.003..0.008 rows=3 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
Query Identifier: jtkjkexhjotbo
Planning Time: 0.068 ms
Execution Time: 0.543 ms
Statement DPU Estimate:
Compute: 0.01550 DPU
Read: 0.00307 DPU (Transaction minimum: 0.00375)
Write: 0.01875 DPU (Transaction minimum: 0.05000)
Total: 0.03732 DPU
```
This statement primarily performs writes, so most of the cost is associated with Write DPU. The Compute DPU (0.01550) represents the work done to process and insert the values. The Read DPU (0.00307) reflects minor system reads (for catalog lookups or index checks).
Notice the Transaction minimums shown next to Read and Write DPUs. These indicate the baseline per-transaction costs that apply *only when the operation includes reads or writes*. They do not mean that every transaction automatically incurs a 0.00375 Read DPU or 0.05 Write DPU charge. Instead, these minimums are applied at the transaction level during cost aggregation and only if reads or writes occur within that transaction. Because of this difference in scope, statement-level estimates in `EXPLAIN ANALYZE VERBOSE` may not exactly match the transaction-level metrics reported in CloudWatch or billing data.
## Using DPU Information for Optimization
Per-statement DPU estimates give you a powerful way to optimize queries beyond just execution time. Common use cases include:
+ **Cost Awareness:** Understand how expensive a query is relative to others.
+ **Schema Optimization:** Compare the impact of indexes or schema changes on both performance and resource efficiency.
+ **Budget Planning:** Estimate workload cost based on observed DPU usage.
+ **Query Comparison:** Evaluate alternative query approaches by their relative DPU consumption.
## Interpreting DPU Information
Keep the following best practices in mind when using DPU data from `EXPLAIN ANALYZE VERBOSE`:
+ **Use it directionally:** Treat the reported DPU as a way to understand the *relative* cost of a query rather than an exact match with CloudWatch metrics or billing data. Differences are expected because `EXPLAIN ANALYZE VERBOSE` reports statement-level cost, while CloudWatch aggregates transaction-level activity. CloudWatch also includes background operations (such as ANALYZE or compactions) and transaction overhead (`BEGIN`/`COMMIT`) that `EXPLAIN ANALYZE VERBOSE` intentionally excludes.
+ **DPU variability across runs is normal** in distributed systems and does not indicate errors. Factors such as caching, execution plan changes, concurrency, or shifts in data distribution can all cause the same query to consume different resources from one run to the next.
+ **Batch small operations:** If your workload issues many small statements, consider batching them into larger operations (not to exceed 10MB). This reduces rounding overhead and produces more meaningful cost estimates.
+ **Use for tuning, not billing:** DPU data in `EXPLAIN ANALYZE VERBOSE` is designed for cost awareness, query tuning, and optimization. It is not a billing-grade metric. Always rely on CloudWatch metrics or monthly billing reports for authoritative cost and usage data.