Building scalable applications on Amazon Aurora DSQL

This article provides practical guidance for designing scalable applications on Amazon Aurora DSQL, covering primary key selection, schema design, transaction strategies, and multi-Region optimization while maintaining ACID compliance.

• Aurora DSQL uses optimistic concurrency control (OCC) where transactions run without locks and are validated at commit, requiring applications to handle retries on conflicts
• Use UUIDs for primary keys in high-scale workloads; use identity columns with CACHE 1 for ordered IDs or CACHE ≥65536 for high-throughput distributed allocation
• Separate hot (frequently updated) and cold (rarely updated) data into different tables to reduce OCC conflicts and improve throughput
• Append-only tables for logs and events have zero OCC conflicts since inserts into different rows don't conflict
• Keep transactions short, move external work outside transaction boundaries, and batch large operations within the 3,000 mutated row limit per transaction
• Implement error classification to retry OCC conflicts and connection failures but never retry constraint violations
• Use the expand-contract pattern for schema migrations: add column, deploy dual-write code, switch reads, then drop old column
• Monitor OCC conflict rate, commit latency, connection pool utilization, and retry exhaustion to assess application health

Designing applications for Aurora DSQL requires accounting for distributed architecture constraints, particularly minimizing hot key contention through schema design and implementing resilient transaction handling with proper retry logic.