What it actually is
Amazon Aurora DSQL is a distributed SQL database that speaks the PostgreSQL wire protocol. You do not pick an instance size, a replica count, or a failover target. There is no primary. Every connection lands on a compute layer based on PostgreSQL 16, and every transaction is checked for conflicts at commit time by a separate transaction layer before it is made durable across three Availability Zones.
That last sentence is the whole product. Classic PostgreSQL, RDS, and Aurora PostgreSQL protect you with locks: the second writer waits for the first. DSQL uses optimistic concurrency control. Nobody waits. Two transactions that touch the same row both run to completion, and the one that commits last is told it lost. There are no lock waits and no deadlocks, and in exchange your application owns a retry loop that a normal Postgres app barely needs.
If you remember one thing: DSQL trades blocking for retrying. A workload that spreads writes across many keys gets scale and 99.99% single-Region availability (99.999% multi-Region) with no operations. A workload that hammers one row gets a stream of serialization errors. The rest of this article is about knowing which one you have.
The model
The user guide names five multi-tenant components, each redundant across three AZs: relay and connectivity, compute and databases, the transaction log with concurrency control and isolation, storage, and a control plane. You never see them, but they explain the behavior. Compute is stateless Postgres. Durability and conflict detection live below it. That is why a cluster can sit idle at zero cost and why connections are cheap to lose.
The contract is snapshot isolation with strong consistency. A transaction reads a consistent snapshot, and at commit the system checks whether any row it wrote (or locked with FOR UPDATE) was changed by a transaction that committed after the snapshot. If so, you get SQLSTATE 40001 with one of two codes. OC000 is a data conflict: two transactions modified the same row. OC001 is a schema conflict: your session's cached catalog is stale because someone ran DDL, including GRANT and REVOKE.
Two details matter. First, SELECT ... FOR UPDATE does not lock anything. It is checked at commit time, so it turns a read into a declared write for conflict purposes. Second, an UPDATE that touches only non-key columns does not conflict with a FOR KEY SHARE read, but it does conflict with another update of the same row. Key columns here means members of a unique, non-partial, non-expression index.
Multi-Region clusters add a peered second cluster and a witness Region. Both endpoints accept concurrent reads and writes against one logical database with synchronous replication. The witness stores encrypted transaction log data and hosts no endpoints. Region sets are continental: you can pair within North America, within Europe, or within a set of Asia Pacific Regions, not across continents.
What AWS operates: everything below the SQL surface, including scaling, patching, replication, and recovery. What you operate: schema design for low contention, the IAM-to-database-role mapping, retry logic, and cost watching.
When to use it, when not to
Need | DSQL | Aurora PostgreSQL | DynamoDB |
|---|---|---|---|
Relational SQL, joins, ACID across rows | Yes | Yes | No |
Writes in more than one Region at once | Yes, active-active | Global Database, one writer Region | Global tables, last-writer-wins |
Operations: instances, failover, patching | None | You size and tune | None |
Concurrency model | Optimistic, retry on 40001 | Locks, waits, deadlocks | Per-item conditional writes |
PostgreSQL feature coverage | Subset | Nearly full | Not SQL |
Hot single-row counters | Poor | Fine | Fine with care |
Pick DSQL when you want relational semantics, you have a multi-Region availability requirement or a spiky workload you do not want to size, and your writes naturally fan out across keys: orders, sessions, per-tenant records. Pick Aurora PostgreSQL when you need the full Postgres surface, long transactions, or heavy contention on a few rows. Pick DynamoDB when your access patterns are known key lookups and you want single-digit-millisecond reads with no SQL tax.
Several gaps have closed this year. Foreign keys arrived in August 2026, partial indexes in October 2026, and JSON and JSONB with compression earlier in the year. Check the PostgreSQL compatibility page before you design around memory: DSQL still has no temporary tables, no PL/pgSQL, no triggers as a recommended pattern, one database per cluster, no TRUNCATE, and a fixed C collation. DDL and DML must run in separate transactions, and each transaction can hold one DDL statement.
What it costs
You pay for two things. Compute, I/O, and write work are metered together as Distributed Processing Units. The pricing page examples use $8 per million DPUs in US East (Ohio), and storage at $0.33 per GB-month. The monthly free tier is 100,000 DPUs and 1 GB of storage.
Three things surprise people. First, retried transactions are not free: a transaction that fails with OC000 still consumed DPUs, so a contended workload pays for its own conflicts. Second, multi-Region writes replicate to the peer and incur additional DPUs equal to the originating write cost, billed in the originating Region, while storage is billed in each Region holding a replica. The witness Region costs nothing. Third, queries that scan can burn DPUs fast, and the docs point to covering indexes and index-only scans as the lever to pull. Use EXPLAIN ANALYZE VERBOSE to see where a query spends.
Inter-AZ replication is free, and data transfer in is free. Backups are billed through AWS Backup. Following along with this article costs a few cents at most, and likely nothing inside the free tier.
The limits that bite
The quotas page is short and worth reading in full. These are the ones that change designs.
A write transaction can modify at most 10 MiB of data and at most 3,000 rows, and the row limit applies across all DML statements regardless of how many secondary indexes you have. A bulk load must be chunked. Transaction time is capped at 5 minutes. A connection lives at most 60 minutes, so a pool must recycle connections. A row is capped at 2 MiB, a primary key at 1 KiB across at most 8 columns, and a table can have 24 indexes.
Cluster-level limits: 10,000 connections per cluster, a connection rate of 100 per second with a burst of 1,000, 20 single-Region and 5 multi-Region clusters per account per Region, and 10 TiB of storage by default (up to 256 TiB with an approved increase). A database holds at most 1,000 tables and 10 schemas, and a cluster holds exactly one database named postgres.
The limit that is not on that page is behavioral. Any DDL, and any GRANT or REVOKE, can make other sessions' next transaction fail with OC001 until their catalog cache refreshes. Schema changes during a deploy are not free.
Build it
We will create a single-Region cluster, connect with an IAM token, then run two experiments: a deliberate two-writer conflict, and a contention benchmark comparing one hot row against many rows.
Prerequisites. Python 3.10 or newer, the AWS CLI configured with credentials, and a Region where DSQL is available (we use us-east-1). Install the libraries:
python -m venv .venv && source .venv/bin/activate
pip install "boto3>=1.40" "psycopg[binary]>=3.2"
export AWS_REGION=us-east-1
Check that your boto3 has the dsql client with python -c "import boto3; print(boto3.client('dsql', region_name='us-east-1'))". If it raises UnknownServiceError, upgrade boto3.
IAM. The identity running this needs these actions. Scope the resource to the cluster ARN once you have one, or use * for a throwaway sandbox account:
{
"Version": "2012-10-17",
"Statement": [{
"Effect": "Allow",
"Action": [
"dsql:CreateCluster",
"dsql:GetCluster",
"dsql:ListClusters",
"dsql:UpdateCluster",
"dsql:DeleteCluster",
"dsql:DbConnectAdmin"
],
"Resource": "*"
}]
}
dsql:DbConnectAdmin is what lets you connect as the admin database role. Application roles use dsql:DbConnect instead, mapped to a database role with AWS IAM GRANT.
Step 1: Create the cluster
We create the cluster with deletion protection off, so cleanup is one call. The API returns the cluster identifier and an endpoint; we poll until the status is ACTIVE.
# create_cluster.py
import time
import boto3
REGION = "us-east-1"
dsql = boto3.client("dsql", region_name=REGION)
resp = dsql.create_cluster(deletionProtectionEnabled=False)
cluster_id = resp["identifier"]
print("created", cluster_id, resp["status"])
while True:
c = dsql.get_cluster(identifier=cluster_id)
print("status:", c["status"])
if c["status"] == "ACTIVE":
break
time.sleep(5)
endpoint = f"{cluster_id}.dsql.{REGION}.on.aws"
with open(".cluster", "w") as f:
f.write(f"{cluster_id}\n{endpoint}\n")
print("endpoint:", endpoint)
Run it with python create_cluster.py. A cluster typically becomes active in well under a minute. The endpoint follows the pattern <cluster-id>.dsql.<region>.on.aws, which is also the host name you pass to the token generator. The .cluster file saves both values so later scripts and the cleanup step can read them.
Step 2: Connect with an IAM token
There are no database passwords. The password is a presigned token, valid for 15 minutes by default (ExpiresIn=900), generated locally by signing with your current IAM credentials. Token generation does not contact AWS, so it succeeds even when your credentials are wrong. The connection attempt is what fails. A token is used only to authenticate, so an established connection survives the token expiring. SSL is required.
# db.py
import boto3
import psycopg
def load():
cluster_id, endpoint = open(".cluster").read().split()
return cluster_id, endpoint
def connect(region="us-east-1", autocommit=True):
_, endpoint = load()
client = boto3.client("dsql", region_name=region)
token = client.generate_db_connect_admin_auth_token(endpoint, region)
return psycopg.connect(
host=endpoint, port=5432, dbname="postgres", user="admin",
password=token, sslmode="require", autocommit=autocommit,
)
if __name__ == "__main__":
with connect() as conn:
print(conn.execute("SELECT version()").fetchone()[0])
Run python db.py. You should see a PostgreSQL 16 version string. This is also your smoke test: if it prints, IAM, the endpoint, and TLS are all fine. AWS now ships Go, Python, and Node.js connectors that generate tokens automatically, and they are the better choice for a real service. We use plain boto3 here so every moving part is visible.
Step 3: Create the schema, one DDL per transaction
DSQL requires DDL and DML in separate transactions, and one DDL statement per transaction. With autocommit=True, each statement is its own transaction, which satisfies that. Primary keys are UUIDs on purpose: random keys spread writes across the key range, which the docs list as the first performance tip.
# setup.py
from db import connect
with connect() as conn:
conn.execute("CREATE SCHEMA IF NOT EXISTS lab")
conn.execute("""
CREATE TABLE IF NOT EXISTS lab.counters (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
n BIGINT NOT NULL DEFAULT 0
)""")
conn.execute("CREATE INDEX ASYNC IF NOT EXISTS counters_name ON lab.counters (name)")
for name in ["hot"] + [f"k{i}" for i in range(8)]:
conn.execute("INSERT INTO lab.counters (name) VALUES (%s)", (name,))
print(conn.execute("SELECT name, n FROM lab.counters ORDER BY name").fetchall())
CREATE INDEX ASYNC is how DSQL builds indexes without blocking writes: it returns immediately and builds in the background, and queries keep working while it finishes. Plain CREATE INDEX is the Postgres habit to unlearn. If you re-run setup, the INSERT loop adds duplicate names, so run it once, or drop the table first.
Step 4: Cause a conflict on purpose
Two transactions read the same row, both increment it, and both try to commit. In Postgres the second would block until the first finished. In DSQL both proceed, and the loser fails at COMMIT. A threading.Barrier makes the race deterministic.
# conflict.py
import threading
import psycopg
from db import connect
barrier = threading.Barrier(2)
results = {}
def worker(label):
with connect(autocommit=False) as conn:
try:
conn.execute("UPDATE lab.counters SET n = n + 1 WHERE name = 'hot'")
barrier.wait() # both have written before either commits
conn.commit()
results[label] = "committed"
except psycopg.errors.SerializationFailure as e:
conn.rollback()
results[label] = f"FAILED 40001: {e}".strip()
ts = [threading.Thread(target=worker, args=(x,)) for x in "AB"]
[t.start() for t in ts]; [t.join() for t in ts]
print(results)
Expected output is one committed and one FAILED 40001 carrying change conflicts with another transaction (OC000). Which thread wins varies by run. The important observation is that neither thread waited: the loser did all of its work and discovered the conflict only at commit. That wasted work is the cost you pay for never blocking.
Step 5: Own the retry loop
The documented recovery is to retry the entire transaction, with the work written to be safe to re-run in full. Never retry a single statement. This wrapper opens a fresh transaction per attempt and backs off exponentially:
# retry.py
import time
import psycopg
MAX_ATTEMPTS = 8
retries = 0 # crude global counter for the benchmark below
def run_with_retry(conn, work):
global retries
for attempt in range(1, MAX_ATTEMPTS + 1):
try:
with conn.transaction():
return work(conn)
except psycopg.errors.SerializationFailure: # SQLSTATE 40001
retries += 1
if attempt == MAX_ATTEMPTS:
raise
time.sleep(min(0.02 * 2 ** attempt, 1.0))
Here the connection is opened with autocommit=True and conn.transaction() wraps each attempt in BEGIN ... COMMIT. The commit happens when the context manager exits, so the SerializationFailure surfaces inside the try. Treat OC001 the same way: it also arrives as 40001, and a retry works after the session's catalog refreshes.
Step 6: Hot row versus spread rows
Now measure the thing. Eight threads each perform 25 increments, first all against the single hot row, then each against its own row (k0 to k7).
# bench.py
import threading, time
import retry
from retry import run_with_retry
from db import connect
def run(target_for):
retry.retries = 0
def worker(i):
with connect() as conn:
for _ in range(25):
run_with_retry(conn, lambda c: c.execute(
"UPDATE lab.counters SET n = n + 1 WHERE name = %s",
(target_for(i),)))
ts = [threading.Thread(target=worker, args=(i,)) for i in range(8)]
t0 = time.time()
[t.start() for t in ts]; [t.join() for t in ts]
return time.time() - t0, retry.retries
for label, fn in [("hot row ", lambda i: "hot"), ("spread ", lambda i: f"k{i}")]:
secs, r = run(fn)
print(f"{label} 200 increments in {secs:5.1f}s with {r} retries")
On a cold run you will see a large retry count and a long wall time for the hot row, and zero or near-zero retries for the spread case. Exact numbers depend on your network distance to the Region, so run it from the same Region if you can. The shape is stable: contention on one key turns concurrency into serial retry storms, and distinct keys scale cleanly. If your product needs a global counter, shard it across N rows and sum on read.
Verify it works
The tutorial succeeded when all of these are true. python db.py printed a Postgres 16 version string. python conflict.py printed one committed and one FAILED 40001 mentioning OC000. And this check shows the counters add up, proving retries did not lose or double-apply increments:
python - <<'PY'
from db import connect
with connect() as conn:
rows = conn.execute("SELECT name, n FROM lab.counters ORDER BY name").fetchall()
print(rows)
print("total:", sum(n for _, n in rows))
PY
If you ran the conflict test once and the benchmark once, hot should read 201 (one winning increment from conflict.py plus 200 from the benchmark), each k0 to k7 should read 25, and the total should be 401. A lower number means a retry loop that committed work twice or dropped it, which is the first bug to look for in any retry code you write.
When it breaks
OC000 on every request. You are updating the same row or key range from many writers. Spread the keys, shard the counter, or batch the updates in the application. Retries are the safety net, not the fix.
OC001 after a deploy. A migration or a GRANT changed the catalog while other sessions held a stale cache. Retry the transaction, keep schema changes infrequent, and do not run DDL on a hot path. If an object looks missing right after you created it elsewhere, reconnect or re-run SET search_path.
PAM authentication failed or access denied on connect. The token is expired, was generated for the wrong host name, or your identity lacks dsql:DbConnectAdmin. Regenerate the token and confirm the hostname you signed is the endpoint you connect to. A token with stray whitespace also fails.
Error 54000 on a bulk insert. You crossed the 3,000-row or 10 MiB per-transaction limit. Chunk the load into batches of a few hundred rows, each in its own retried transaction.
A job connects then dies after an hour. The 60-minute connection cap. Recycle connections in your pool before they hit it, and expect brief reconnects during rare recovery events.
NetworkUnreachable on a dual-stack host. The IPv4-only endpoint was throttled and the client fell back to IPv6. Pin the client to IPv4.
Cleanup
Delete the cluster. We turned deletion protection off at creation, so this is one call. If you left it on, update_cluster with deletionProtectionEnabled=False first.
# cleanup.py
import boto3
REGION = "us-east-1"
cluster_id = open(".cluster").read().split()[0]
dsql = boto3.client("dsql", region_name=REGION)
print(dsql.delete_cluster(identifier=cluster_id)["status"])
print([c["identifier"] for c in dsql.list_clusters().get("clusters", [])])
The first line prints DELETING, and the cluster list should no longer contain your identifier once deletion completes. Storage calculations can lag after a drop, so give the console a few minutes before you worry about the storage line. An idle DSQL cluster costs almost nothing, but a forgotten one is still a resource you do not need. Confirm in the Billing console the next day that DPU and storage usage are flat.
Where to take it next
Easiest: add a custom database role with CREATE ROLE app WITH LOGIN and AWS IAM GRANT app TO '<role-arn>', then connect as it using generate_db_connect_auth_token and dsql:DbConnect. That is the production auth pattern, and the admin role should stay out of application code.
Next: add a partial index (CREATE INDEX ASYNC ... WHERE status = 'open') on a table with a small active set and a large history, and compare the DPU cost of a covering index scan against a full scan using EXPLAIN ANALYZE VERBOSE.
Hardest: peer a second cluster in another Region within the same Region set, with a witness Region, run the benchmark against both endpoints at once, and watch how cross-Region conflicts show up. The question to answer is whether your retry loop is idempotent enough to survive a second writer that is a whole Region away.
