DEV Community

Guilherme Dalla Rosa
Guilherme Dalla Rosa

Posted on

Aurora DSQL now supports foreign keys. What else?

#serverless #aws #database

AWS recently announced that Amazon Aurora DSQL now supports foreign key constraints. If you've been following DSQL since the preview, you know why this one matters.

Aurora DSQL is the serverless, distributed database from AWS that speaks PostgreSQL: no instances to size, scaling to zero when idle, and multi-region clusters where you write to any region and read a consistent answer from all of them. Instead of locking rows, it detects conflicts at commit time and asks the loser to retry. I covered all of that in my talk What DSQL? Rethinking SQL for the Serverless, Distributed Age at AWS Community Summit Manchester, and one of the limitations I highlighted there was the lack of foreign keys, a potential blocker for some use cases.

The syntax is the one you already know from PostgreSQL: a column-level REFERENCES or a table-level FOREIGN KEY, with the same match types, the same five referential actions and the same deferrable options, so the DDL you wrote for PostgreSQL should run as it is:

CREATE TABLE customers (
  id uuid PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE orders (
  id uuid PRIMARY KEY,
  customer_id uuid NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  total numeric(18,2) NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

The one DSQL-specific detail is adding a constraint to a table that already has rows. Where PostgreSQL would scan the table there and then, DSQL has you add the constraint as NOT VALID and validate the existing data afterwards, as an asynchronous job you can follow in the sys.jobs system view, the same way you follow an asynchronous index build:

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
Enter fullscreen mode Exit fullscreen mode

What changes in practice is who enforces the relationship: from now on the database refuses the write, and a schema you carry over from a PostgreSQL project needs fewer exceptions. One caveat: the foreign key documentation is clear that every write to a referenced or referencing table costs extra reads. A CASCADE also counts toward the 3,000-row limit of a transaction, so a delete that fans out into thousands of child rows still has to be chunked.

To be fair, not everyone wants foreign key constraints in the first place. PlanetScale's guide to operating without foreign key constraints explains why they don't recommend them. Constraints mean more locking under high concurrency, column types you can no longer change, more complex schema refactors and rules that are hard to maintain once data is split across servers.

Their advice is to keep the relationships in your model and enforce them in the application, which is what most of us had been doing on DynamoDB anyway. DSQL's implementation avoids the locking part of that list, and the rest turns into the extra reads and the row limit above. Nice to have the option, and still worth measuring before you turn it on everywhere.

Then, last month at AWS Community Day Singapore, I watched Yuuki Yamashita's talk Distributed Transactions Under Fire: Building a Zero-Oversell Flash Sale Platform with Amazon Aurora DSQL. The problem is familiar to anyone in e-commerce: a limited drop lasts 30 seconds, race conditions cause oversells and provisioning for that peak means paying for it all month. So he built a flash sale on Lambda and DSQL where two buyers race for the last item and the first to commit wins.

His demo put 100 concurrent buyers against 10 items and came out with 10 orders and zero oversells. The honest part of the talk was what broke on the way. Connections cached across Lambda invocations outlived the 15-minute IAM token, and the retries themselves tripled the load until backoff with jitter and a cap of three attempts turned the storm into a clean sold-out. It's the kind of real-world lesson I look for, and it left me wondering what else had improved in the DSQL world since my talk. So here's what I found.

What else has improved

Let's start with the one I was hoping for when I first looked at DSQL: change data capture (CDC). DynamoDB Streams has spoilt me: the database propagates every change to a stream, and the rest of the event-driven architecture hangs off it without the application having to publish anything. With a traditional relational database you end up building the transactional outbox pattern instead, writing the event in the same transaction as the data, polling the outbox table and pushing it to the bus, and keeping the two in step.

CDC now does that for you: inserts, updates and deletes go to Kinesis Data Streams as change events, and from there to Lambda, or to S3, Redshift and OpenSearch through Firehose. In my opinion this is as big an announcement as the foreign keys, because it makes the case for DSQL as a replacement for DynamoDB in event-driven applications, with SQL on top. One caveat: delivery is at least once, so the consumer still has to deduplicate and order the records, and the stream is billed in DPUs by the volume it captures. If you want to try it, Vijay Karumajji's getting started guide walks through the setup, from the Kinesis stream and the IAM role to the first events.

The rest of the engine changes closed several other items on my slide, and the release notes are the place to follow them:

  • Multi-region is no longer a US-only story. Multi-region clusters run in 16 regions across three region sets, with Frankfurt, Ireland, London, Paris, Spain and Stockholm on the European side, and single-region clusters are available in 20 regions. A cluster still has to stay inside one region set.
  • Identity columns and sequences are in, with a cache you have to set explicitly and values that can arrive out of order across sessions.
  • JSON and JSONB are supported, another item from the slide.
  • Schema changes got easier: DROP COLUMN, constraints added as NOT VALID and validated later, indexes on expressions and extended statistics are all in.
  • Clusters now create in seconds instead of minutes, and storage goes up to 256 TiB. What hasn't moved is the other half of the slide, and the migration guide is the honest place to read it. No triggers, no PL/pgSQL, no temporary tables, no extensions (so no pgvector and no PostGIS), one database per cluster, and 3,000 rows and 5 minutes per transaction. Those aren't gaps waiting to be filled, they're the design, and the examples below are mostly about building around them.

What the community learned

The lesson that repeats across every story I read is that retries are a design decision, not an error handler. Marc Bowes, who works on DSQL, shows in avoid hot keys why a single counter row that every transaction updates is the classic mistake, and why appending rows and summing them is the shape that scales. Fernando Azevedo's field notes on multi-region add the other rule of thumb: every commit in a multi-region cluster pays the round trip between the regions, so an abort rate creeping up is a design smell before it's a database problem. For the bigger picture, Marc Brooker's DSQL: Simplifying Architectures makes the case for an active-active setup with no failover logic and no leader election. The team also published the paper, for anyone who wants the full story of how it works.

Use cases and examples

A few references worth keeping, from talks and production stories to sample apps:

Top comments (0)