Working with identity columns and sequences in Aurora DSQL

This article explains how to use PostgreSQL-compatible identity columns and sequences in Aurora DSQL for generating unique identifiers in distributed database environments.

• Identity columns provide streamlined syntax for auto-incrementing IDs with configurable cache settings
• Standalone sequences offer advanced control for sharing across tables or generating IDs outside INSERT operations
• Cache size selection balances throughput (larger cache) against sequential ordering (CACHE 1)
• GENERATED ALWAYS prevents user overrides; GENERATED BY DEFAULT allows explicit values
• Gaps in sequences are expected with high cache values due to concurrent allocation and transaction rollbacks
• UUIDs recommended for primary keys; integer sequences better for human-readable display IDs
• Hybrid approach combines UUID primary keys with cached sequence display numbers
• Use CACHE >= 65536 for high-volume workloads; CACHE 1 for strict sequential ordering requirements
• Monitor sequences via pg_sequences system view and information_schema.columns
• No additional charges for sequences or identity columns; 5,000 sequence limit per cluster

Aurora DSQL now supports distributed identity columns and sequences optimized for high-concurrency workloads, with flexible configuration options for different application requirements.