Migrating from PostgreSQL to Aurora DSQL

Use CREATE INDEX ASYNC instead of CREATE INDEX for non-blocking index creation.

Benefit: Zero-downtime index creation on large tables.

Use DELETE FROM table_name instead of TRUNCATE.

Alternative: For complete table recreation, use DROP TABLE followed by CREATE TABLE.

Aurora DSQL is fully managed, so configuration is handled automatically based on workload patterns. Use the AWS Management Console or API to manage cluster settings.

Benefit: No need for database tuning or parameter management.

Aurora DSQL supports table relationships and JOIN operations. For referential integrity, implement validation in your application layer. This design aligns with modern distributed database patterns where application-layer validation provides more flexibility and avoids performance bottlenecks from cascading operations.

Pattern: Implement referential integrity checks in your application layer using consistent naming conventions, validation logic, and transaction boundaries. Many high-scale applications prefer this approach for better control over error handling and performance.

Use CTEs, subqueries, or regular tables with cleanup logic instead of temporary tables.

Alternative: Create tables with session-specific names and clean them up in your application.

Aurora DSQL provides one built-in database named postgres per cluster.

Migration tip: If your application uses multiple databases, create separate Aurora DSQL clusters for logical separation, or use schemas within a single cluster.

For temporary data handling, you SHOULD use common table expressions (CTEs) and subqueries, which provide flexible alternatives for complex queries.

Alternative: Use CTEs with WITH clauses for temporary result sets, or regular tables with unique naming for session-specific data.

Aurora DSQL eliminates tablespaces and manual storage management. Storage automatically scales and optimizes based on your data patterns.

Benefit: No need to monitor disk space, plan storage allocation, or manage tablespace configurations.

For trigger-like functionality, implement event-driven logic in your application layer.

Migration strategy: Move trigger logic to application code, use event-driven architectures with AWS services like EventBridge, or implement audit trails using application logging.

Aurora DSQL supports SQL-based functions but not procedural languages like PL/pgSQL.

Alternative: Use SQL functions for data transformations, or move complex logic to your application layer or AWS Lambda functions.

Aurora DSQL uses optimistic concurrency control (OCC), a lock-free approach that differs from traditional database locking mechanisms. Instead of acquiring locks that block other transactions, Aurora DSQL allows transactions to proceed without blocking and detects conflicts at commit time. This eliminates deadlocks and prevents slow transactions from blocking other operations.

Key difference: When conflicts occur, Aurora DSQL returns a serialization error rather than making transactions wait for locks. This requires applications to implement retry logic, similar to handling lock timeouts in traditional databases, but conflicts are resolved immediately rather than causing blocking waits.

Design pattern: Implement idempotent transaction logic with retry mechanisms. Design schemas to minimize contention by using random primary keys and spreading updates across your key range. For details, see Concurrency control in Aurora DSQL.

Aurora DSQL supports foreign key relationships between tables, including JOIN operations. For referential integrity, implement validation in your application layer. While enforcing referential integrity can be valuable, cascading operations (like cascading deletes) can create unexpected performance issues—for example, deleting an order with 1,000 line items becomes a 1,001-row transaction. Many customers avoid foreign key constraints for this reason.

Design pattern: Implement referential integrity checks in your application layer, use eventual consistency patterns, or leverage AWS services for data validation.

Aurora DSQL automatically manages storage optimization, statistics collection, and performance tuning. Traditional maintenance commands like VACUUM are handled by the system.

Benefit: Eliminates the need for database maintenance windows, vacuum scheduling, and system parameter tuning.

Aurora DSQL automatically partitions and distributes your data based on access patterns. Use UUIDs or application-generated IDs for optimal distribution.

Migration tip: Remove manual partitioning logic and let Aurora DSQL handle data distribution. Use UUIDs or application-generated IDs for optimal distribution. If your application requires sequential identifiers, see Sequences and identity columns.