AWS Introduces Foreign Key Constraints in Aurora DSQL

AWS recently announced that Aurora DSQL now supports foreign key constraints, allowing applications to enforce referential integrity directly in the database. The supported capabilities include CASCADE, SET NULL and other referential actions, according to the announcement by InfoQ's Renato Losio. The addition addresses what users had explicitly described as an adoption blocker.

Aurora DSQL is a serverless, distributed, PostgreSQL-compatible SQL database designed for highly available, scalable applications. According to the documentation, it enforces referential integrity through snapshot verification during transactions and conflict detection at commit time. Foreign key relationships are checked against a consistent transaction snapshot without locking tables, permitting concurrent operations, and implicit KEY SHARE checks at commit detect conflicting changes. Transactions that would violate a constraint are rejected with a serialization error.

Because concurrent conflicts produce transaction failures rather than waits, applications should implement retry logic. For heavily referenced rows, AWS suggests avoiding frequently changing key columns, keeping referenced keys stable and moving changing values to non-key columns to reduce transaction conflicts.

Marc Brooker, VP and Distinguished Engineer at AWS, said the service uses the Adjudicator and PostgreSQL's KEY SHARE mechanism to detect changes to relevant rows at commit time without blocking concurrent reads, citing no blocking, no change in scaling for non-FKC reads, and that FKC readers never cause other readers to abort.

Luc van Donkersgoed, principal engineer at Nederlandse Spoorwegen and creator of AWS News Feed, commented on LinkedIn that the change closes the biggest gap between DSQL and standard Postgres, making DSQL more viable for brownfield environments. Marc Bowes, senior principal engineer at AWS, had previously acknowledged in a thread that foreign keys were coming.

Reaction has been largely positive, though a Reddit user questioned whether the feature slows things down given DSQL's architecture. AWS notes that all DML operations on referenced or referencing tables incur additional reads to maintain referential integrity and that customers should benchmark workloads before adding a foreign key constraint.

AWS also recently added CloudWatch Database Insights for per-statement, cluster-level performance monitoring and troubleshooting in DSQL.