/aurora-dsql

DSQL Horizontal Scaling Guide

Part of DSQL Development Guide.

Horizontal Scaling: Best Practice

Aurora DSQL is designed for massive horizontal scale without latency degradation.

Note on default limits in this file. Each "verify via the AWS MCP Server's aws___search_documentation" reference below assumes the AWS MCP Server is connected. When it isn't, fall back to the DSQL documentation directly, or the defaults table in SKILL.md.

Connection Strategy

• PREFER more concurrent connections with smaller batches - Higher concurrency typically yields better throughput
• SHOULD implement connection pooling - Reuse connections to minimize token overhead; respect 10,000 max per cluster (verify via the AWS MCP Server's aws___search_documentation if available, or the DSQL documentation: aurora dsql connection limits)
• PREFER initial pool size 10-50 per instance - Generate fresh IAM auth tokens in pool hooks (e.g., BeforeConnect) for 15-minute expiration (verify via the AWS MCP Server's aws___search_documentation if available, or the DSQL documentation: aurora dsql authentication token)
• MUST set pool max-lifetime under 60 minutes - DSQL closes connections at the 60-minute cap. Set max-lifetime to ~50 minutes so connections recycle ahead of that and the application never observes a server-initiated close mid-query. Examples: HikariCP maxLifetime: 3000000 (50 min), psycopg-pool max_lifetime=3000, node-postgres pool — pair idleTimeoutMillis with a separate lifetime guard.
• SHOULD retry internal errors with new connection - Internal errors are retryable, but SHOULD use a new connection from the pool
• SHOULD implement backoff with jitter - Avoid thundering herd; scale pools gradually

Batch Size Optimization

• PREFER batches of 500-1,000 rows - Balance throughput and transaction limits (defaults: 3,000 rows, 10 MiB, 5 minutes max — verify via the AWS MCP Server's aws___search_documentation if available, or the DSQL documentation: aurora dsql transaction limits)
• SHOULD process batches concurrently - Use multiple connections; consider multiple threads for bulk loading
• Smaller batches reduce lock contention, enable better concurrency, fail faster, distribute load evenly

AVOID Hot Keys

Hot keys (frequently accessed rows) create bottlenecks. For detailed analysis, see "How to avoid hot keys in Aurora DSQL".

Key strategies:

• PREFER UUIDs for primary keys - UUIDs are the recommended default identifier because they avoid coordination; use gen_random_uuid() for distributed writes
  • Sequences and IDENTITY columns are available when compact, human-readable integer identifiers are needed (e.g., account numbers, reference IDs). CACHE must be specified explicitly as either 1 or >= 65536. See Choosing Identifier Types
  • ALWAYS use GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY for auto-incrementing columns (SERIAL is not supported)
• SHOULD avoid aggregate update patterns - Year-to-date totals and running counters create hot keys via read-modify-write
  • RECOMMENDED: Compute aggregates via queries - Calculate totals with SELECT when needed; eventual consistency often acceptable
• Accept contention only for genuine constraints - Inventory management and account balances justify contention; sequential numbering and visit tracking are better served by coordination-free approaches

Choosing Identifier Types

Aurora DSQL supports both UUID-based identifiers and integer values generated using sequences or IDENTITY columns.

• UUIDs can be generated without coordination and are recommended as the default identifier type, especially for primary keys where scalability is important and strict ordering is not required
• Sequences and IDENTITY columns generate compact integer values convenient for human-readable identifiers, reporting, and external interfaces. When numeric identifiers are preferred, we recommend using a sequence or IDENTITY column in combination with UUID-based primary keys
• ALWAYS use GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY for auto-incrementing columns (SERIAL is not supported)

Choosing a CACHE Size

REQUIRED: Specify CACHE explicitly when creating sequences or identity columns. Supported values are 1 or >= 65536 (verify via the AWS MCP Server's aws___search_documentation if available, or the DSQL documentation: aurora dsql sequence cache).

• CACHE >= 65536 — suited for high-frequency identifier generation, many concurrent sessions, and workloads that tolerate gaps and ordering effects (e.g., IoT/telemetry ingestion, job run IDs, internal order numbers)
• CACHE = 1 — suited for low allocation rates where identifiers should follow allocation order more closely and minimizing gaps matters more than throughput (e.g., account numbers, reference numbers)