DSQL SQL Dialect: How Amazon Aurora DSQL differs from single-instance PostgreSQL

Amazon Aurora DSQL is based on open source PostgreSQL (today v16) but is also a distributed database. This means that there are some differences in supported features and behavior. Understanding the differences can help you save time and take advantage of the unique capabilities of Amazon Aurora DSQL.

Knowing exactly where Amazon Aurora DSQL aligns with standard PostgreSQL and where it diverges helps you to reduce risk and design schemas that perform well from day one. You might find that most existing PostgreSQL applications work with minimal changes.

This post is for database architects, developers, and DBAs who must evaluate Amazon Aurora DSQL or work with PostgreSQL workloads on a distributed database.

What’s the same

The short answer: most of it. Today, Amazon Aurora DSQL uses standard PostgreSQL v16 and the v3.2+ wire protocol. Standard PostgreSQL clients, drivers, and ORMs: psql, pgjdbc, psycopg, Django, ActiveRecord, Hibernate, connect and work as expected. Your SQL queries, assuming they use supported features, return identical results: same NULL handling, sort order, numeric precision, and string behavior.

The core SQL surface area is intact:

• Standard DML: SELECT (with joins, subqueries, aggregations), INSERT, UPDATE, DELETE
• DDL: CREATE TABLE, ALTER TABLE, CREATE VIEW, DROP VIEW
• Transaction control: BEGIN, COMMIT, ROLLBACK
• Core data types: numeric types, character types, date/time types, Boolean, bytea

Some PostgreSQL features aren’t available in Amazon Aurora DSQL. For the complete list of supported and unsupported features, see the Aurora DSQL and PostgreSQL Compatibility documentation. The feature set evolves as Amazon Aurora DSQL adds support.

The key point: if your application uses standard PostgreSQL SQL for transactional workloads, the dialect compatibility is high. The differences that matter are architectural, not syntactic.

What’s different and why

The dialect differences in Amazon Aurora DSQL stem from its distributed, shared-nothing architecture. Understanding the architecture explains the differences, which makes them predictable rather than surprising.

Storage is ordered by primary key

This is the most fundamental difference, and it shapes everything else.

In PostgreSQL, tables use a heap: rows are stored in unordered pages with no relationship to the primary key. You can run CLUSTER to reorder a table by an index once, but PostgreSQL doesn’t maintain that order as data changes.

In Amazon Aurora DSQL, data is stored and maintained in primary key order. Think of it as a table that’s continuously Clustered. This applies to both tables and secondary indexes (which are ordered by their key columns).

What this means for the SQL that you write:

• Range scans on the primary key are physically sequential reads. Queries that filter on primary key ranges are inherently efficient.
• Primary key choice has a much larger impact than in PostgreSQL. In a heap, a bad primary key is compensated by indexes. In Amazon Aurora DSQL, the primary key is the physical layout.
• Sequential or monotonically increasing keys (auto-increment, timestamps) create hot spots in a distributed system. Randomly distributed keys (UUIDs) spread load. The same principle applies to secondary indexes. Their key order determines physical layout and scan efficiency.

Not all operations push down to storage

Amazon Aurora DSQL separates compute and storage. This is one of the keys to its ability to automatically scale and deliver a fully serverless operation. When a query runs, the critical performance question is: can the filter or operation be evaluated at the storage layer, or does the compute layer have to fetch rows and evaluate them locally?

This affects the dialect in two concrete ways:

Index key type restrictions. Not every PostgreSQL data type can be used as the key of an index in Amazon Aurora DSQL. If you try to create an index on an unsupported key type, you will get an error. Check the current documentation for the supported set as this evolves over time.

Operation push down. Straightforward equality and range comparisons on supported types generally push down to storage. Complex expressions, function calls, or operations on types that the storage layer doesn’t natively handle are evaluated at the compute layer after fetching rows. This doesn’t change your SQL syntax but alters performance characteristics. Use EXPLAIN to identify filters that aren’t being pushed down.

Covering indexes matter more here. Because of the compute/storage separation, a query that can be answered entirely from an index (without fetching the base table row) avoids an extra round trip to storage. Use INCLUDE columns aggressively:

-- Covers queries that filter on customer_id and return order_total, status

CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (order_total, status);

Optimistic concurrency control

PostgreSQL uses lock-based concurrency: transactions acquire row-level locks and block each other on conflict. Amazon Aurora DSQL uses optimistic concurrency control (OCC): transactions execute without locks and are checked for conflicts at commit time.

This doesn’t change your SQL syntax, but it changes the dialect’s behavior:

• No deadlocks. Transactions don’t block each other.
• Serialization errors on conflict. When two transactions modify the same data, the one with the earlier commit timestamp wins. The other receives SQLSTATE 40001. Your application must retry the entire transaction.
• Read-only transactions are conflict-free. They have zero commit latency and aren’t rejected.
• Isolation level is equivalent to PostgreSQL Repeatable Read. You don’t choose an isolation level, this is what you get.

The practical dialect difference: SELECT … FOR UPDATE is syntactically supported, but it doesn’t block. Instead, conflicts are detected at commit time and result in an OCC serialization error. If your PostgreSQL application relies on SELECT … FOR UPDATE to serialize access to contested rows, expect those paths to surface as retry-able conflicts rather than blocking waits. Design for low contention and use idempotent transactions, that can be retried without additional business logic / new decisions.

Asynchronous DDL

In PostgreSQL, DDL is synchronous: when CREATE TABLE returns, the table exists. In Amazon Aurora DSQL, some DDL is synchronous and some is not. Straightforward operations like CREATE TABLE return synchronously, but DDL that requires background work such as [All CREATE INDEX operations must be CREATE INDEX ASYNC].

Dialect constraints that follow from this:

• Only one DDL statement per transaction.
• DDL and DML cannot be mixed in the same transaction.
• For asynchronous DDL, you must verify DDL completion (select * from sys.jobs; or wait for the job_id to finish) before depending on the schema change.

Reads and writes continue uninterrupted during DDL operations.

Authentication is IAM, not passwords

Amazon Aurora DSQL replaces the PostgreSQL pg_hba.conf and username/password authentication with AWS Identity and Access Management (IAM). You connect using short-lived tokens generated using the AWS SDK. This doesn’t change the SQL dialect itself, but it changes every connection string and authentication flow in your application. For more information, see the IAM authentication guide. For comprehensive access control patterns and security best practices, see Securing Amazon Aurora DSQL: Access Control Best Practices.

Unsupported features that affect the dialect

Some PostgreSQL features aren’t available in Amazon Aurora DSQL. Rather than list everything (always consult the Aurora DSQL and PostgreSQL Compatibility documentation), here are the categories that most commonly affect the way SQL is written:

These are the items most likely to require SQL changes during migration. The full list evolves as features are added, so we recommend that you verify against the current documentation.

Conclusion

Amazon Aurora DSQL uses a PostgreSQL parser, planner, and type system, so the SQL dialect is largely compatible. This post explores the architectural differences that matter and how they shape the way you write and think about SQL in a distributed database: key-ordered storage, optimistic concurrency control, asynchronous DDL, and IAM authentication.

The focus is on understanding why Amazon Aurora DSQL behaves differently. Primary keys determine physical layout and write distribution, indexes are key-ordered structures that benefit from covering columns, transactions are optimistic and conflict-free for reads, and operations have limits designed for consistent performance in a distributed system. We recommend that you keep an eye on this service. Amazon Aurora DSQL is closing feature gaps (PG Sequences just launched!) and the differences between Amazon Aurora DSQL and standard PostgreSQL will continue to shrink.

Create your first Aurora DSQL cluster today and experience distributed PostgreSQL with active-active writes across multiple AWS Regions. Amazon Aurora DSQL offers a free tier with 100,000 DPUs per month and 1GB Storage.

About the authors