A PostgreSQL driver connecting successfully to Aurora DSQL proves protocol compatibility. It does not prove that the application’s transaction semantics, database features, migration scripts, or operational assumptions are compatible.

Situation

RetailCo wants its order-authorization service to accept writes in two AWS Regions. The service owns checkout idempotency, reservation state, and a small order-status ledger. A regional database failover would be simpler if both application regions could read and write one strongly consistent logical database.

The candidate workload has several characteristics that make a distributed database plausible:

  • most transactions insert or update fewer than twenty rows;
  • rows have application-generated UUIDs;
  • transactions normally concern one order or one reservation;
  • cross-order reporting can run outside the synchronous write path;
  • the application already understands idempotency and bounded retries.

The existing PostgreSQL estate also contains patterns that may not migrate:

  • foreign keys and triggers enforce part of the order workflow;
  • PL/pgSQL functions contain business logic;
  • reconciliation jobs use temporary tables and large transactions;
  • schema tools assume several DDL statements and data backfills can share a transaction;
  • connection pools expect long-lived password-authenticated sessions.

RetailCo is a reference architecture, not a claimed customer deployment. The transaction sizes, topology, and migration patterns are scenario inputs. No latency, throughput, availability, conflict-rate, recovery, or cost result in this article is presented as measured fact.

Aurora DSQL changed materially between its May 2025 general availability and this article’s March 31, 2026 cutoff. AWS added identity columns and sequence objects in February 2026, removing one launch-era compatibility gap. The service still needs an exact cutoff because current documentation can describe capabilities that did not exist when an earlier design was made: Aurora DSQL release notes.

The current architecture is not a PostgreSQL primary with replicas. A multi-Region Aurora DSQL database has two peered Regional clusters with writable endpoints and a third witness Region that participates in transaction-log quorum but exposes no query endpoint:

flowchart TD
  AppA[application — Region A] --> EndA[DSQL endpoint — Region A]
  AppB[application — Region B] --> EndB[DSQL endpoint — Region B]
  EndA --> Logical[one strongly consistent logical database]
  EndB --> Logical
  Logical --> Witness[witness Region — encrypted transaction log]
  Logical --> StorageA[distributed storage — Region A]
  Logical --> StorageB[distributed storage — Region B]

AWS documents both endpoints as concurrently readable and writable with strong consistency. The witness stores a limited window of encrypted transaction logs and has no client endpoint. Multi-Region clusters must use Regions from one supported Region set; cross-continent multi-Region clusters are not currently supported: What is Aurora DSQL? and creating a multi-Region cluster.

That architecture directly addresses the single-writer ownership problem described earlier in this series. It does not make an arbitrary PostgreSQL workload distributed without redesign.

The Problem

The phrase PostgreSQL-compatible compresses several different questions into one label:

Compatibility layerWhat to ask
Wire protocolCan the driver connect and exchange PostgreSQL messages?
SQL syntax and typesDoes the application use only the supported subset?
Transaction semanticsDo isolation, conflicts, retries, and limits preserve business invariants?
Database programmabilityAre required triggers, procedural functions, extensions, and constraints available?
OperationsDo authentication, pooling, observability, backup, and schema deployment fit?
PerformanceDoes this transaction and key distribution work under representative concurrency in each Region?

Aurora DSQL uses PostgreSQL’s version 3 wire protocol and core PostgreSQL components including its parser, planner, optimizer, and type system. It is currently based on PostgreSQL 16. AWS describes its transaction isolation as equivalent to PostgreSQL REPEATABLE READ: Aurora DSQL and PostgreSQL.

Underneath that interface, Aurora DSQL uses optimistic concurrency control and a distributed catalog. It does not reproduce the storage, locking, extension, process, configuration, or administration model of a PostgreSQL server.

flowchart TD
  Client[PostgreSQL client or ORM] --> Protocol[PostgreSQL wire protocol]
  Protocol --> Parser[PostgreSQL-compatible SQL surface]
  Parser --> OCC[optimistic concurrency control]
  OCC --> Catalog[distributed schema catalog]
  OCC --> Log[distributed transaction log]
  OCC --> Storage[automatically partitioned storage]
  Storage --> Result[compatible result for supported behavior]

The critical architecture question is therefore not:

Does Aurora DSQL speak PostgreSQL?

It is:

Can RetailCo express and verify its order invariants using Aurora DSQL’s supported transaction model without importing assumptions from conventional PostgreSQL?

Evaluate the Transaction Shape Before the Product Name

RetailCo should evaluate Aurora DSQL in five gates: transaction semantics, key distribution, feature compatibility, operational lifecycle, and measured workload behavior. A failure at an early gate should stop the migration before a benchmark creates false confidence.

Gate 1 — design for abort and retry

Aurora DSQL uses optimistic concurrency control. Transactions proceed without traditional blocking locks, and conflicting work is evaluated at commit. A data conflict returns PostgreSQL SQLSTATE 40001 with OCC code OC000. A stale distributed catalog can return the same SQLSTATE with code OC001: Aurora DSQL concurrency control.

Deadlock-free does not mean conflict-free. It means a conflict becomes an aborted transaction instead of a lock wait or deadlock cycle.

The retry boundary must be the complete transaction:

flowchart TD
  Start[start transaction attempt] --> Read[read required state]
  Read --> Validate[validate business invariant]
  Validate --> Write[write all database changes]
  Write --> Commit[attempt commit]
  Commit --> Success{commit succeeded}
  Success -->|yes| Publish[return durable success]
  Success -->|no| Classify{retryable 40001}
  Classify -->|yes| Backoff[bounded backoff with jitter]
  Backoff --> Start
  Classify -->|no| Fail[fail and preserve diagnostics]

The second attempt must open a new transaction and repeat every database read. Retrying only COMMIT would reuse decisions made from the losing snapshot.

The retry implementation also needs:

  • a maximum attempt count and total time budget;
  • exponential or decorrelated backoff with jitter;
  • metrics split by OC000, OC001, exhausted retries, and operation;
  • an idempotency key for the business command;
  • no irreversible external side effect before the database commit;
  • safe handling when the client loses the connection and cannot tell whether commit succeeded.

For checkout, RetailCo should store the idempotency key and the order transition in the same transaction. A retry then re-reads that record and returns the already committed result rather than charging, reserving, or publishing twice.

Gate 2 — distinguish snapshot isolation from serializable execution

Aurora DSQL provides strong consistency and ACID transactions at a fixed isolation level equivalent to PostgreSQL REPEATABLE READ. Those are important guarantees. They do not justify silently labeling every business invariant serializable.

Snapshot isolation gives a transaction a stable snapshot and detects conflicting writes. A multi-row invariant can still require a separate correctness design when concurrent transactions read different rows and write disjoint rows. The safe approach is to test the exact invariant rather than infer behavior from the words ACID or strong consistency.

RetailCo should include a write-skew test:

Invariant: at least one fraud-reviewer assignment remains active

Transaction A:
  reads reviewer A and reviewer B
  sees both active
  disables reviewer A

Transaction B:
  reads reviewer A and reviewer B
  sees both active
  disables reviewer B

The lab must determine whether both can commit for the installed service behavior. If they can, RetailCo must move the invariant onto a common write key, use a unique or checkable representation that forces a conflict, or place the decision behind one application owner. This article does not claim the lab result.

Aurora DSQL now supports a narrow SELECT ... FOR UPDATE form: equality predicates on every primary-key column, against a single table. Range, IN, OR, and joined forms are rejected. That is not a drop-in replacement for every PostgreSQL locking query: supported SQL features.

Gate 3 — distribute keys and avoid hot ownership

Aurora DSQL physically organizes data by primary key and uses that key in its cluster-wide row identity and automatic partitioning. AWS recommends random primary keys for high-write tables and warns that monotonically increasing integers can direct new inserts toward one partition: Aurora DSQL primary keys.

Use an explicit, distributed key even though Aurora DSQL can synthesize a hidden ID for a table without a declared primary key:

CREATE SCHEMA orders;

CREATE TABLE orders.order_state (
    order_id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id         uuid NOT NULL,
    idempotency_key   varchar(200) NOT NULL,
    order_status      varchar(40) NOT NULL,
    total_amount      numeric(18,2) NOT NULL,
    updated_at        timestamptz NOT NULL DEFAULT current_timestamp
);

CREATE UNIQUE INDEX ASYNC order_state_tenant_idempotency_uq
ON orders.order_state (tenant_id, idempotency_key);

An explicit primary key improves application reasoning, query access, idempotency, and the identity carried in outbox events and reconciliation records.

Sequences and identity columns are supported as of February 2026. That removes an earlier compatibility gap, but it does not make an ascending key ideal for a high-volume insert path. AWS notes that sequence cache size 1 can increase coordination overhead in a distributed system: Aurora DSQL sequences.

The more important hot-key problem is update contention. A single row holding a global inventory count, account balance, or daily sequence remains one write-conflict domain even when the database has enormous distributed capacity.

For inventory, prefer ownership units that can change independently:

global product counter
        becomes
inventory by product, location, and reservation bucket

That redesign is valid only if the business can reconcile those units without violating its sellable-quantity invariant. Sharding a counter is an application correctness decision, not merely a performance technique.

Gate 4 — fit inside the transaction contract

Current Aurora DSQL database limits include:

LimitCurrent documented valueArchitectural effect
Maximum transaction duration5 minutesLong workflows and migrations must be decomposed
Rows mutated in a transaction block3,000Bulk settlement and backfills need bounded batches
Modified data in one write transaction10 MiBRow count alone is not a safe batch limit
Query memory base amount128 MiB per transactionLarge joins and sorts require workload tests
Databases per cluster1Database-per-service requires multiple clusters or schemas
Schemas per database10Tenant-per-schema and database consolidation need review
Tables per database1,000High-object-count designs need an inventory
Connection duration60 minutesPools must recycle connections proactively

These are service limits, not tuning parameters. AWS marks the transaction duration, row mutation, modified-data, and database-count limits as non-adjustable: Aurora DSQL quotas and limits.

RetailCo’s twenty-row authorization transaction fits comfortably in shape. Its 20,000-row settlement does not. The settlement must become restartable batches with progress checkpoints and idempotent reconciliation—or remain on another database.

Batching is not automatically equivalent to one atomic settlement. If the business rule requires all 20,000 changes to commit or none, splitting the transaction changes semantics. RetailCo must redesign the workflow around an explicit state machine, staging records, and a final visibility decision, or reject Aurora DSQL for that path.

Gate 5 — inventory PostgreSQL features, not only SQL statements

AWS’s migration guide identifies several current differences:

  • one built-in database named postgres per cluster;
  • no temporary tables;
  • referential integrity validation moves to the application layer;
  • trigger-like logic moves to the application or event-driven processing;
  • SQL-language functions are supported, but procedural languages such as PL/pgSQL are not;
  • system configuration and tablespaces are service-managed;
  • the transaction isolation level is fixed at REPEATABLE READ;
  • DDL and DML must use separate transactions;
  • one transaction can contain only one DDL statement.

See Migrating from PostgreSQL to Aurora DSQL and DDL and distributed transactions.

The current supported-SQL list includes CREATE FUNCTION with LANGUAGE SQL; it does not include CREATE TRIGGER, procedural functions, or CREATE EXTENSION. The current system-catalog matrix marks pg_trigger, pg_event_trigger, pg_extension, foreign-data-wrapper catalogs, publications, subscriptions, and partition metadata as unavailable: Aurora DSQL system tables.

This creates a migration boundary:

flowchart TD
  Existing[existing PostgreSQL application] --> Scan[scan SQL schema ORM and operations]
  Scan --> Direct[supported SQL and types]
  Scan --> Rewrite[triggers PLpgSQL temporary tables and FK logic]
  Scan --> Reject[required extension or transaction semantic cannot move]
  Direct --> Lab[compatibility and concurrency lab]
  Rewrite --> Lab
  Lab --> Decide{all invariants proven}
  Decide -->|yes| Migrate[migrate bounded service]
  Decide -->|no| Alternative[retain PostgreSQL or choose another scale path]

Foreign keys deserve special attention. Replacing a constraint with “the application checks first” can introduce a time-of-check-to-time-of-use race unless the validation and dependent write share a transaction and concurrent deletion is controlled. It also means every writer, migration tool, and repair script must obey the same rule. For relationships where orphan prevention is non-negotiable, encode the ownership differently, create a repairable asynchronous invariant with monitoring, or use a database that enforces the constraint.

Design the RetailCo Service for DSQL

Aurora DSQL is a better fit for a deliberately bounded service than for RetailCo’s 40-TB PostgreSQL monolith.

Keep the synchronous transaction small and self-contained

One order-authorization transaction should contain only database changes required to decide and record the authorization:

BEGIN;

INSERT INTO orders.idempotency (
    tenant_id,
    idempotency_key,
    order_id,
    result_code
)
VALUES ($1, $2, $3, 'PENDING')
ON CONFLICT (tenant_id, idempotency_key) DO NOTHING;

-- Read the idempotency row and current order state.
-- Validate the transition inside this transaction.

UPDATE orders.order_state
SET order_status = $4,
    updated_at = current_timestamp
WHERE order_id = $3
  AND order_status = $5;

INSERT INTO orders.outbox (
    event_id,
    order_id,
    event_type,
    event_payload,
    created_at
)
VALUES ($6, $3, $7, $8, current_timestamp);

COMMIT;

This is illustrative SQL, not a complete application implementation. At the March 2026 cutoff, the outbox payload must use a supported scalar representation such as text or normalized columns rather than assuming unsupported JSON types. The schema must include matching unique keys, and the application must check affected-row counts. A payment call must not occur between BEGIN and COMMIT; external side effects need their own idempotent workflow.

Do not assume a native CDC stream exists

At this article’s March 31, 2026 cutoff, Aurora DSQL did not provide a native Kinesis CDC capability. A design that needs committed row changes must therefore treat change propagation as an application responsibility rather than quietly assuming PostgreSQL logical decoding or a native database stream is available.

RetailCo can write an explicit outbox row in the same bounded order transaction, then let an application worker claim and publish pending rows idempotently. That worker needs retries, deduplication keys, visible delivery state, and reconciliation. The outbox keeps the business change and publication intent atomic, but it does not make email, payment, inventory, or search side effects exactly once.

Treat schema deployment as asynchronous distributed work

Aurora DSQL distributes its schema catalog. A completed DDL change can cause another session with a stale catalog to receive OC001; retry normally refreshes the catalog.

CREATE INDEX ASYNC returns a job ID rather than waiting for an index to finish. The index is not ready until the job completes and pg_index.indisvalid becomes true. A failed index can remain invalid and needs to be dropped and recreated. Catalog activation can itself cause concurrency errors for sessions using the namespace: Aurora DSQL asynchronous indexes.

The deployment pipeline should therefore:

  1. run one DDL statement in its own transaction;
  2. capture asynchronous job IDs;
  3. wait for completion and verify catalog state;
  4. tolerate OC001 in application sessions;
  5. deploy code using an expand-and-contract compatibility window;
  6. run any data backfill as bounded DML batches;
  7. remove old schema only after old application versions are gone.

Do not translate a PostgreSQL migration file containing ten statements inside BEGIN and COMMIT into ten unobserved Aurora DSQL autocommits. That changes failure and rollback behavior.

Make IAM authentication part of pool design

Aurora DSQL uses signed IAM tokens instead of persistent database passwords. A token authenticates a new connection; an established session can remain active after that token expires. The database connection itself has a maximum duration of one hour: Aurora DSQL client access and authentication tokens.

Use a supported connector or pool hook that generates a fresh token when opening a connection. Configure pool lifetime below 3,600 seconds and add jitter so the fleet does not replace every connection simultaneously. AWS’s Python connector example uses a 3,300-second maximum lifetime for this reason: Aurora DSQL Connector for Python.

Plan recovery even when infrastructure is serverless

Aurora DSQL integrates with AWS Backup for full-cluster backups, schedules, retention, cross-account and cross-Region copies, and Vault Lock. A restore creates a new cluster rather than overwriting the source. Restoring a multi-Region cluster requires an identical backup copy in every target cluster Region before the restore begins: Aurora DSQL backup and restore.

RetailCo still needs to test:

  • restoration into a network-isolated environment;
  • IAM and database-role reconstruction;
  • application connection to the new endpoint;
  • outbox relay restart, duplicate-delivery handling, and downstream reconciliation;
  • DNS or configuration cutover;
  • validation of orders, reservations, and idempotency state;
  • the observed recovery time and recovery point.

Serverless removes server restoration work. It does not prove application recovery.

In Practice

RetailCo should run a rejection-oriented lab before a migration pilot. The goal is to find incompatible invariants and workload shapes early, not to produce an attractive peak-TPS chart.

Test 1 — compatibility inventory

Extract the current schema, migration history, SQL statements, ORM-generated SQL, and operational scripts. Classify every object or command:

StatusMeaning
Supported directlyCurrent AWS documentation and the lab both accept it
Rewrite requiredA documented alternative exists, but semantics must be re-proven
Service boundary changeLogic moves to another component or database
Blocking incompatibilityNo acceptable replacement preserves the requirement

Include triggers, functions, extensions, foreign keys, temporary tables, sequences, TRUNCATE, lock clauses, isolation settings, schema count, table count, index count, and driver initialization SQL.

Result to verify: every production path has an owner and disposition. This article claims no completed inventory.

Test 2 — transaction correctness under concurrency

Run at least these cases from both writable Regions:

  1. independent UUID-key inserts;
  2. concurrent updates to the same order row;
  3. concurrent inserts using the same idempotency key;
  4. the write-skew invariant described earlier;
  5. a transaction that approaches each row, byte, and duration limit;
  6. a connection loss immediately before or after commit acknowledgment;
  7. application retry exhaustion under sustained contention.

Record success, OC000, OC001, other SQLSTATEs, retry count, commit latency, and invariant violations. AWS exposes CloudWatch metrics including OccConflicts, QueryTimeouts, CommitLatency, transaction counts, bytes read and written, compute time, storage size, and DPU use: Monitoring Aurora DSQL.

Result to verify: retries preserve correctness and remain within the application’s latency and error budgets. No conflict rate or latency result is claimed here.

Test 3 — schema change under application load

Run one supported ALTER TABLE, an asynchronous non-unique index, and an asynchronous unique index while old and new application versions issue transactions.

Verify:

  • OC001 is retried safely;
  • the application does not use the index before it becomes valid;
  • a duplicate-data failure leaves an invalid index that the runbook removes;
  • the deployment can resume after interruption;
  • no migration assumes transactional rollback across multiple DDL statements.

Test 4 — operational failure and recovery

Exercise connection recycling, expired token replacement, one Regional application path becoming unavailable, outbox relay interruption and redelivery, an impaired downstream destination, and AWS Backup restoration to a new cluster.

For a multi-Region test, measure commit behavior from both Regions before and during each supported failure injection. Do not infer the application’s availability from the service’s published availability target.

Test 5 — compare the correct alternatives

Use the same schema subset and command mix against:

  • standard Aurora PostgreSQL;
  • Aurora PostgreSQL Limitless Database if its feature set fits;
  • Aurora DSQL;
  • application-sharded Aurora clusters if tenant routing is acceptable.

The earlier Aurora write-scaling article explains how those architectures distribute ownership differently. The comparison should include compatibility work, application retries, multi-Region commit latency, conflict behavior, backup restoration, observability, and operational ownership—not only throughput.

Where It Breaks

Workload assumptionWhat DSQL actually requiresFailure if ignoredRecommendation
PostgreSQL driver means drop-in migrationSupported protocol, SQL, and behavior onlyRuntime SQL and semantic failuresInventory and replay real application SQL
ACID means serializableFixed snapshot isolation equivalent to Repeatable ReadMulti-row invariant can remain unprovenRun write-skew tests and redesign ownership if needed
Lock-free means contention-freeOCC aborts conflicting transactions at commitRetry storms and tail-latency growthSpread keys, bound retries, monitor OccConflicts
More Regions remove latencySynchronous strong consistency coordinates writesCommit latency may exceed request budgetBenchmark from every application Region
Sequence support makes ascending keys idealAscending writes can concentrate one partitionInsert hotspot or coordination costPrefer random keys on high-write tables
Application validation replaces every foreign keyAll writers must preserve the invariant under concurrencyOrphans and inconsistent repair pathsRedesign ownership or use enforced constraints elsewhere
An outbox makes downstream delivery exactly onceThe database makes the row and publication intent atomic, not the external side effectDuplicate or stale downstream stateDeduplicate, record delivery state, and reconcile
Serverless removes database operationsAuthentication, retries, schema jobs, backup, recovery, and cost remainOperational gaps move into the applicationOwn runbooks and observability explicitly
A large transaction can be split harmlesslyBatching changes the atomicity boundaryPartial business operation becomes visibleUse a state machine or retain another database
Every PostgreSQL tool will workExtensions, catalogs, parameters, and commands differMigration or operations fail lateTest each required tool and initialization query

When I would not use Aurora DSQL

I would not choose Aurora DSQL as a lift-and-shift target for a PostgreSQL monolith that depends on triggers, PL/pgSQL, extensions, foreign-key enforcement, temporary tables, large atomic batches, several databases, or extensive server-level observability and tuning.

I would also reject it for a critical invariant that requires serializable behavior until a concurrency lab proves the exact design, or for a workload dominated by a few hot rows whose retry rate violates the service objective.

Aurora DSQL is also not the current answer for a cross-continent active-active database because AWS limits multi-Region clusters to supported within-set combinations.

For those workloads, standard Aurora PostgreSQL may provide the required compatibility while retaining a single writer. Aurora PostgreSQL Limitless can distribute selected PostgreSQL tables under its own constraints. Citus can provide explicit shard-key control with higher operational responsibility. Application sharding can preserve conventional PostgreSQL inside each ownership boundary.

What to Do Next

  • Problem: PostgreSQL protocol compatibility can hide incompatible concurrency, transaction, schema, and operational assumptions.
  • Solution: Evaluate the service by transaction shape: small bounded writes, random keys, tolerable contention, idempotent retries, supported SQL, application-owned integrity, and a tested multi-Region latency budget.
  • Proof: Replay production SQL, run same-key and write-skew concurrency tests, exercise schema changes, force retry exhaustion, verify outbox redelivery and reconciliation, and restore an AWS Backup recovery point into a new cluster.
  • Action: Pilot one new or well-bounded service before attempting a monolith migration. Keep every empirical claim marked unverified until the lab preserves business invariants under representative concurrency and failure.

For RetailCo’s order-authorization service, Aurora DSQL is worth a pilot because the core write path is small, keyed by UUID, idempotent, and potentially benefits from two active Regional endpoints. The reconciliation batch, database triggers, procedural logic, and foreign-key-dependent workflows should not move unchanged. They need redesign or a different database boundary.

The scenario verdict is:

Good fit:
  new distributed OLTP services with small transactions and application-owned retries

Poor fit:
  PostgreSQL monoliths with deep database programmability or large atomic batches

Main advantage:
  managed active-active writes with strong consistency and automatic distribution

Main limitation:
  a focused PostgreSQL surface and a different concurrency contract

Operational complexity:
  low for servers, medium to high for application correctness and migrations

Migration complexity:
  low for a new bounded service, high for an existing PostgreSQL-centric system

The conclusion is not that Aurora DSQL is deficient because it is not conventional PostgreSQL. Its distributed behavior is the reason it can offer a different scale and availability model. The engineering mistake is to adopt that model while pretending the differences stop at the connection string.