Executive summary
Amazon Aurora DSQL now lets you add foreign key constraints to new and existing tables, in every Region where Aurora DSQL runs. Until this week the migration guidance was the opposite: keep referential integrity in application code. That advice is now withdrawn, and the database will enforce it for you.
The interesting part is not that foreign keys arrived. It is how they are enforced. Aurora DSQL has no locks. It could not implement a foreign key the way PostgreSQL does, so it did something else: verify the constraint against the transaction's start-time snapshot, then adjudicate at commit using an implicit KEY SHARE dependency on the referenced row.
That choice has a consequence the announcement does not mention. Adding a foreign key changes the conflict behaviour of transactions that never touch each other's rows directly. A child insert and a parent delete, running concurrently and touching two different tables, are now a conflict — one of them will fail at commit with SQLSTATE 40001. On a schema with hot parent rows, turning on referential integrity can raise your serialization-failure rate without a single query changing.
What changed
Aurora DSQL supports the NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT referential actions. It also supports the MATCH FULL and MATCH SIMPLE match types, and deferrable foreign key constraints — a more complete set than the announcement's summary suggests, and enough that most existing PostgreSQL schemas will port without editing their DDL.
Constraints can be added to existing tables, not only at creation. Availability is stated plainly: this feature is available in all AWS Regions where Aurora DSQL is available, with no separate opt-in and no pricing note, which means it is priced as ordinary read and write activity rather than as a feature.
Aurora DSQL's migration guidance previously told you to implement referential integrity in application code, on the reasoning that it avoids the performance cost of cascading operations. Anyone who designed a DSQL schema against that advice built validation into their service layer. That code is not wrong now — but it is no longer the only option, and the trade-off it was avoiding has changed shape rather than disappeared.
Architecture
The mechanism is two-step, and both steps matter for reasoning about behaviour under load.
Snapshot verification. Every transaction runs against a consistent snapshot taken at its start. When you insert a referencing row, DSQL reads the referenced table at that snapshot to confirm the key exists. When you delete a referenced key, it reads the referencing table to confirm no orphans would result. Crucially, AWS is explicit that because this verification reads from the transaction start-time snapshot instead of taking a lock, other transactions can continue to modify both tables in parallel.
Commit-time resolution. Snapshot verification proves the constraint held when the transaction started — not that it still holds. A concurrent transaction can delete the parent after you checked it. To settle that, Aurora DSQL implicitly applies the KEY SHARE clause to referenced rows, detecting whether a concurrent change invalidated your snapshot. If it did, the transaction fails with a serialization error.
Why this widens the conflict surface
DSQL's published conflict matrix is the thing to internalise. SELECT ... FOR KEY SHARE conflicts with INSERT, DELETE, an UPDATE to key columns, or SELECT ... FOR UPDATE on the same row. It does not conflict with an update to non-key columns.
Because a foreign key applies that clause implicitly to every referenced row, inserting a child row now creates a commit-time dependency on the parent. Two transactions that write to entirely different tables can now fail against each other. Before the constraint existed, they could not.
| Concurrent operations on the parent row | Child insert conflicts? | Why |
|---|---|---|
DELETE the parent |
Yes | The child's implicit KEY SHARE depends on the row existing |
UPDATE a key column |
Yes | The referenced key itself moved |
UPDATE a non-key column |
No | The child only depends on the key columns staying stable |
| Another child insert on the same parent | No | Two KEY SHARE readers do not conflict with each other |
Business value
The value is real and mostly about deleted code. Referential integrity implemented in a service layer is a class of bug that is easy to write and hard to test: a check-then-insert in application code is not atomic, so under concurrency it admits exactly the orphan rows it was written to prevent. Pushing the constraint into the database makes the guarantee unconditional rather than best-effort.
It also removes a migration blocker. A team porting an existing PostgreSQL schema previously had to strip foreign keys and reimplement them, which is both work and a source of behavioural drift between the old system and the new. Most schemas can now move with their DDL intact, and standard ORM tooling that emits REFERENCES clauses works without the workarounds that DSQL guides previously described.
Security considerations
Referential integrity is a data-correctness control rather than an access control, but two points are worth noting.
A cascading delete is an authorised deletion of rows the caller may never have named. ON DELETE CASCADE means a principal with delete permission on the parent table effectively has delete permission on every referencing row, transitively. DSQL manages permissions through schema-level grants, so a grant that looks narrow at the table level can be considerably wider once cascades are in place. Model the blast radius of a cascade as part of the permission, not separately from it.
Second, constraint violations are informative. An error revealing that a referenced key does not exist tells the caller something about data they may not be permitted to read. Where a table is multi-tenant and the foreign key crosses a tenant boundary, prefer returning a generic failure from the application layer over surfacing the database's message verbatim.
Cost considerations
There is no price for the feature; there is a price for what it does. AWS states it directly: all DML operations on referenced or referencing tables incur extra reads to guarantee referential integrity, and advises benchmarking the workload before adding a constraint.
On a serverless, consumption-priced database, extra reads are not a footnote — they are the bill. Every insert into a child table now also reads the parent table. Every delete from a parent reads the children. A schema with several foreign keys per table multiplies this, and the multiplication lands on the write path, which is usually the hot path.
The second cost is retries. A transaction that fails with a serialization error consumed resources and produced nothing, and then runs again. A workload with hot parent rows can pay for the same logical write several times.
Operational considerations
Three DSQL limits interact with foreign keys in ways worth planning for.
The 3,000-row transaction cap. A transaction can modify up to 3,000 rows, regardless of the number of secondary indexes, and that limit applies to all DML. A cascading delete performs deletes on child rows inside the same transaction, so a parent row with a wide fan-out is a candidate for hitting the cap. AWS does not spell out the interaction, so treat this as something to test on your own schema rather than assume in either direction — delete a parent with more children than the cap and see what happens, before a customer does.
One DDL statement per transaction, and DDL separate from DML. Adding constraints to an existing schema is therefore a sequence of individual statements, not one migration transaction. There is no all-or-nothing schema change here, so a migration that fails halfway leaves some constraints applied and some not. Write the migration to be resumable.
Schema changes can fail live sessions. Any operation that modifies the schema catalog can produce OC001 — including ALTER TABLE. Adding a foreign key to a busy table can therefore surface serialization errors in sessions doing nothing but ordinary reads and writes, because their cached catalog went stale. This is safe and retryable, but it is visible, and it belongs in the change plan rather than in the incident channel.
Tradeoffs
| Approach | Works well when | Breaks down when |
|---|---|---|
| Database foreign keys | Correctness matters more than peak write throughput; parent rows are stable; fan-out per parent is bounded | Hot parent rows whose key columns change; very wide cascades; write paths already close to a latency budget |
| Application-level integrity | Extreme write throughput; deliberate tolerance for eventual repair; relationships that are advisory rather than strict | Concurrency makes check-then-write non-atomic; every new caller must reimplement the rule correctly |
| Both — constraint plus app validation | You want friendly error messages and a hard backstop; the app validates for UX, the database guarantees | Duplicated rules drift apart; the extra reads are paid regardless of what the app already checked |
Implementation guidance
Add constraints where the parent is stable and the fan-out is bounded, and benchmark before committing to them on a hot path.
-- DDL and DML need separate transactions, and only one DDL each.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_product
FOREIGN KEY (product_id) REFERENCES products (product_id)
ON DELETE RESTRICT;
-- RESTRICT, not CASCADE, until you have measured the fan-out
-- against the 3,000-row limit on your own data.
On the client side, the retry is no longer optional. AWS notes that the logic resembles standard PostgreSQL deadlock handling, but that OCC requires your applications to exercise this logic more frequently. Both OC000 and OC001 arrive as SQLSTATE 40001 and both are safe to retry.
import random, time
import psycopg
def run_with_retry(conn_factory, work, attempts=5):
for attempt in range(attempts):
try:
with conn_factory() as conn, conn.transaction():
return work(conn)
except psycopg.errors.SerializationFailure:
# OC000 (row conflict) and OC001 (stale catalog) both land here.
if attempt == attempts - 1:
raise
time.sleep((2 ** attempt) * 0.05 + random.uniform(0, 0.05))The transaction body must be idempotent for this to be safe. That is the same requirement DSQL already placed on every write, so a correctly built DSQL application should need no change — but a schema newly acquiring foreign keys will exercise the path far more often than before.
Best practices
Keep referenced keys stable. This is AWS's own guidance and it is the highest-leverage design decision here. Key columns are those in a unique, non-partial, non-expression index; everything else is a non-key column, and a concurrent update to a non-key column does not conflict. So if a parent row carries a value that changes often, move it off the referenced key. A hot parent row whose key never moves will not reject its children.
Prefer RESTRICT to CASCADE as the default. Cascades are convenient and they hide unbounded work inside an innocuous statement — work that runs against a hard 3,000-row transaction limit and an authorisation model that does not see it. Use cascade where the fan-out is small and known.
Spread the key range. DSQL's standing advice — random primary keys, avoid contention on single keys — matters more once foreign keys exist, because the constraint concentrates commit-time dependencies onto parent rows.
Benchmark on the write path, not the read path. The extra reads land on inserts, updates and deletes. A read-heavy benchmark will show almost nothing and tell you almost nothing.
Who should adopt this
Adopt now: teams migrating an existing PostgreSQL schema to Aurora DSQL, who were facing the work of stripping and reimplementing constraints. The migration blocker is gone and the schema can move mostly intact.
Adopt selectively: teams already running on DSQL with application-level integrity. The application code works; the case for change is removing a class of concurrency bug, not fixing a live problem. Add constraints to the relationships where an orphan would be a correctness incident, and leave the rest.
Benchmark first: anyone with hot parent rows — a tenants table, a popular product, a status lookup referenced by every row in the system. This is exactly the shape where the implicit KEY SHARE turns independent writes into conflicting ones, and where the serialization rate can move sharply.
Key takeaways
- Aurora DSQL now supports foreign keys on new and existing tables, in all Regions where DSQL runs, with a fuller set of referential actions and match types than the announcement implies.
- Enforcement is lock-free: snapshot verification during the transaction, then commit-time adjudication via an implicit
KEY SHAREon referenced rows. - The cost is not latency but failed commits. A child insert now conflicts with a concurrent parent delete or key-column update, and the loser fails with
SQLSTATE 40001. - An update to a non-key column on the parent does not conflict — which makes "keep the referenced key stable" the main design lever.
- All DML on referenced or referencing tables incurs extra reads. On a consumption-priced database that is a direct bill increase on the write path, and AWS explicitly says to benchmark first.
- Cascading deletes run inside the 3,000-row transaction limit. Test a wide parent on your own schema before trusting
ON DELETE CASCADEin production.
Comments