AWS Aurora DSQL Adds Foreign Key Constraints with Referential Actions

Foreign Key Support Introduced

AWS has announced that Aurora DSQL now supports foreign key constraints. This feature allows applications to enforce referential integrity directly within the database, including support for referential actions such as CASCADE and SET NULL. This update addresses a long-standing request from users who identified the absence of foreign key constraints as a barrier to adopting Aurora DSQL.

Mechanism of Enforcement

Aurora DSQL, a serverless, distributed, PostgreSQL-compatible SQL database, enforces referential integrity through snapshot verification during transactions and conflict detection at commit time. The service checks foreign key relationships against a consistent transaction snapshot without locking tables, which allows concurrent operations. Implicit KEY SHARE checks are used at commit to detect conflicting changes, and transactions violating a constraint are rejected with a serialization error.

Application Considerations

Applications using Aurora DSQL with foreign key constraints should implement retry logic, as concurrent conflicts can lead to transaction failures. For heavily referenced rows, AWS advises against frequently changing key columns. Instead, keeping referenced keys stable and moving changing values to non-key columns can help reduce transaction conflicts.

Technical Details and Impact

Marc Brooker, VP and Distinguished Engineer at AWS, noted that Aurora DSQL uses the Adjudicator and PostgreSQL's KEY SHARE mechanism to detect changes to relevant rows at commit time without blocking concurrent reads. This approach aims to prevent blocking, maintain scaling for non-FKC reads, and ensure FKC readers do not cause other readers to abort, providing good FKC scaling for well-distributed workloads.

Luc van Donkersgoed, principal engineer at Nederlandse Spoorwegen, commented that the lack of foreign key support was the biggest gap between DSQL and 'normal' Postgres, hindering many migrations. This change makes DSQL more viable for existing brownfield environments.

Addressing User Feedback

The absence of foreign key constraints was a frequently cited issue in discussions regarding Aurora DSQL's adoption, particularly since its announcement at re:Invent 2024. Marc Bowes, senior principal engineer at AWS, had previously acknowledged user feedback, stating that foreign keys were in development.