AWS RDS and Aurora skill for Claude Code

A managed database is set up well when the engine and deployment type were chosen from measured load, failover has been tested and the app reconnects on its own, engine settings live in a custom parameter group under version control, upgrades go through blue/green with a rehearsed switchover, and nobody is paying for I/O, idle readers, or Extended Support by accident.

First, inspect what exists

aws rds describe-db-instances --query 'DBInstances[].{id:DBInstanceIdentifier,engine:Engine,ver:EngineVersion,class:DBInstanceClass,multiAZ:MultiAZ,storage:StorageType,gb:AllocatedStorage,pg:DBParameterGroups[0].DBParameterGroupName,cluster:DBClusterIdentifier}' --output table
aws rds describe-db-clusters --query 'DBClusters[].{id:DBClusterIdentifier,engine:Engine,ver:EngineVersion,storage:StorageType,serverless:ServerlessV2ScalingConfiguration,members:length(DBClusterMembers),insights:DatabaseInsightsMode}' --output table
aws rds describe-db-engine-versions --engine aurora-postgresql --default-only \
  --query 'DBEngineVersions[].EngineVersion'
aws rds describe-pending-maintenance-actions

Then pull two weeks of CloudWatch for the writer: CPUUtilization, FreeableMemory, DatabaseConnections, ReadIOPS/WriteIOPS (RDS) or VolumeReadIOPs/VolumeWriteIOPs (Aurora), ReplicaLag / AuroraReplicaLag, DBLoad (Database Insights), and for Serverless v2 ServerlessDatabaseCapacity and ACUUtilization. Size from these numbers, not from the current instance class.

Choose the deployment

Situation	Choose
Small or steady workload, cost first, needs every community extension or engine feature	RDS for PostgreSQL/MySQL, gp3, Multi-AZ instance in production
Production needing readable standbys and faster failover on RDS	RDS Multi-AZ DB cluster (writer + 2 readable standbys, semi-synchronous; limited instance classes)
High read scale (up to 15 replicas), fast failover, storage that grows on its own, clones	Aurora provisioned
Spiky or unpredictable load, dev/test, many small databases	Aurora Serverless v2 (0 or 0.5 to 256 ACU)
Steady high utilization 24/7	Provisioned (RDS or Aurora) with Reserved Instances; Serverless v2 costs more at constant high load
Multi-Region DR with low RPO	Aurora Global Database
I/O-heavy Aurora where I/O is 25%+ of the Aurora bill	Aurora I/O-Optimized storage (see Cost)

Facts that drive the choice:

• Aurora storage is six copies across three AZs regardless of instance count; a single writer with no reader still has durable storage but no fast compute failover target.
• RDS Multi-AZ instance standby is not readable; the Multi-AZ DB cluster’s standbys are.
• RDS gp3 baseline is 3,000 IOPS and 125 MiB/s below 400 GiB, and 12,000 IOPS and 500 MiB/s at 400 GiB and above (PostgreSQL, MySQL, MariaDB), with extra IOPS and throughput purchasable only above that threshold.
• Aurora cluster volumes grow automatically, up to 256 TiB on recent engine versions.

Size the instance

• OLTP: memory-optimized classes (db.r*), current-generation Graviton where the engine supports it. Size memory so the hot working set fits in the buffer cache: a falling BufferCacheHitRatio (Aurora) or rising read IOPS with flat traffic means it does not.
• Avoid burstable db.t* classes in production; when CPU credits run out, latency collapses with no config change.
• DBLoad (average active sessions) consistently above the vCPU count means sessions are queuing: break it down by wait event before buying a bigger instance. Lock waits and I/O waits are not fixed by more CPU.
• Leave headroom: target sustained CPU under roughly 60-70% on the writer so failover to an equal-size reader and traffic spikes fit.
• Readers that are failover targets should be the same class as the writer.

Aurora Serverless v2

aws rds modify-db-cluster --db-cluster-identifier app-prod \
  --serverless-v2-scaling-configuration MinCapacity=4,MaxCapacity=64 --apply-immediately

• 1 ACU is roughly 2 GiB of memory with matching CPU and network; scaling is in steps as small as 0.5 ACU and happens without dropping connections.
• Set MinCapacity high enough to hold the working set in memory; a minimum of 0.5 empties the buffer cache at night and the morning traffic hits storage.
• MinCapacity=0 (auto-pause) fits dev/test and rarely used databases on supported versions; the first connection after a pause waits for resume. Not for production APIs.
• Readers in promotion tiers 0-1 scale with the writer (failover-ready); tiers 2-15 scale independently (reporting readers).
• max_connections is derived from MaxCapacity, not current capacity.
• Total idle cost is roughly (instances) x MinCapacity ACU-hours around the clock.

High availability and read scaling

• Production: Multi-AZ (RDS) or at least one Aurora reader in another AZ, promotion tier 0 or 1.
• Connect writers to the cluster (writer) endpoint and reads to the reader endpoint. The reader endpoint balances per new connection via DNS, not per query; pooled connections stick to one reader.
• Replica lag is real on RDS read replicas (asynchronous). Never read-your-own-write from a replica; route those reads to the writer.
• Keep client DNS caching at 5 seconds or less (JVM networkaddress.cache.ttl) or the app keeps writing to the old writer after failover. AWS Advanced JDBC and Python wrappers detect topology changes faster than DNS.
• Test failover before production, and after big changes. Ask the user before running it on a shared or production database:

aws rds failover-db-cluster --db-cluster-identifier app-prod                   # Aurora, Multi-AZ DB cluster
aws rds reboot-db-instance --db-instance-identifier app-prod --force-failover  # RDS Multi-AZ instance

Measure: time until writes succeed again from the app’s point of view, and whether the pool recovered without a restart.

Parameter groups

• Never edit default groups (you cannot). Create a custom DB parameter group (and a DB cluster parameter group for Aurora and Multi-AZ DB clusters) per engine family; manage it in Terraform or CDK.
• Static parameters need a reboot (pending-reboot); schedule it, or change during a blue/green deployment.
• Baseline PostgreSQL settings worth setting deliberately: log_min_duration_statement (for example 500 ms), idle_in_transaction_session_timeout, statement_timeout per role rather than globally, rds.force_ssl = 1, shared_preload_libraries including pg_stat_statements, and work_mem sized to concurrency.
• MySQL: slow_query_log = 1, long_query_time, require_secure_transport = ON, innodb_print_all_deadlocks = 1.
• Leave memory parameters that RDS derives from the instance class (shared_buffers, innodb_buffer_pool_size) on their formula unless you have measured a reason.

RDS Proxy

Use it for Lambda and other bursty clients that open many short connections, to absorb connection storms, and to shorten failover and blue/green switchover impact. It is not a substitute for an application pool in long-running services.

• Watch DatabaseConnectionsCurrentlySessionPinned. Pinned sessions keep a dedicated database connection and defeat multiplexing.
• PostgreSQL pins on: any SET (including set_config), PREPARE/EXECUTE/ DEALLOCATE, temporary tables, cursors, LISTEN, session-level advisory locks (pg_advisory_lock; the _xact_ variants do not pin), DISCARD ALL, and statements over 16 KB.
• MySQL pins on: prepared statements, temporary tables, LOCK TABLES, GET_LOCK, most user or system variable changes (some tracked variables do not pin; session pinning filters can exempt variable setting).
• Move identical per-connection SET statements into the proxy’s initialization query.
• Disable driver-level server-side prepared statements when connecting through the proxy if pinning stays high. Pool sizing math lives in connection-pooling.

Backups and recovery

• Automated backups with retention of at least 7 days (maximum 35); PITR restores to a new instance or cluster, never in place.
• Manual snapshots persist until deleted; copy them to another Region or account for disaster recovery and ransomware protection (or use AWS Backup with vault lock).
• Aurora clones (restore-db-cluster-to-point-in-time --restore-type copy-on-write) give a production-sized test copy in minutes, billed for changed pages only.
• Restore drills, RPO/RTO design, and logical dumps belong to backup-restore. Run one restore per quarter at minimum and time it.

Upgrades with blue/green deployments

aws rds create-blue-green-deployment --blue-green-deployment-name app-pg17 \
  --source arn:aws:rds:us-east-1:123456789012:cluster:app-prod \
  --target-engine-version 17.6 \
  --target-db-cluster-parameter-group-name app-aurora-pg17
# test green, then (user confirms the window):
aws rds switchover-blue-green-deployment --blue-green-deployment-identifier bgd-abc123 \
  --switchover-timeout 600

Pick the target version from describe-db-engine-versions; the version above is an example. Checklist:

• Green created with the new version and a parameter group built for it; extensions updated and tested on green.
• Application test suite and a replay of production queries run against green (read-only); compare query plans for the top statements.
• PostgreSQL logical-replication limits checked: no DDL or large-object changes on blue during the deployment (replication degrades and you must recreate green), every table has a primary key or REPLICA IDENTITY FULL, materialized views refreshed after switchover, pg_partman disabled on blue, ANALYZE run on green.
• Replica lag near zero; long transactions and DDL stopped on blue.
• IAM database-auth policies list both blue and green resources; IAM roles re-associated after switchover.
• Switchover timeout set (default 300 s, range 30-3,600 s); if exceeded, everything rolls back.
• Old blue kept for at least the PITR window: the new production starts its own PITR history at green creation and cannot restore to earlier times.

Limits to know: not supported with Secrets Manager-managed master passwords, cascading or cross-Region replicas, or CloudFormation-managed resources.

Minor versions: enable auto minor version upgrade on non-critical environments first, then production in a maintenance window.

Monitoring

• Database Insights replaced the Performance Insights console (Performance Insights console end of life: July 31, 2026; the API continues). Standard mode is on by default. Advanced mode (execution plans, lock analysis, fleet views) requires 465-day retention and is billed per vCPU or ACU: aws rds modify-db-cluster --db-cluster-identifier app-prod --database-insights-mode advanced --enable-performance-insights --performance-insights-retention-period 465.
• Export PostgreSQL/MySQL logs to CloudWatch Logs; alarm on FreeStorageSpace (RDS), FreeableMemory, DatabaseConnections against max_connections, replica lag, and DiskQueueDepth.
• Query-level tuning from pg_stat_statements or the slow log goes to sql-query-tuning.

IAM database authentication

-- PostgreSQL
CREATE USER app_user; GRANT rds_iam TO app_user;
-- MySQL
CREATE USER 'app_user' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';

aws rds generate-db-auth-token --hostname app-prod.cluster-xyz.us-east-1.rds.amazonaws.com \
  --port 5432 --username app_user --region us-east-1

• Tokens are valid for 15 minutes and are needed only to open a connection; pools must generate a fresh token per new connection (driver plugins do this).
• Requires TLS. Budget 300-1,000 MiB of extra instance memory; avoid on tiny instances.
• A PostgreSQL user granted rds_iam can no longer log in with a password.
• High new-connection rates: put RDS Proxy in front and use IAM auth to the proxy.
• Token generation is not logged in CloudTrail; audit through IAM policy design.

Cost traps

Trap	Check
Aurora Standard with heavy I/O	I/O share of Aurora spend; run scripts/aurora_storage_cost.py
Running an engine past end of standard support	Extended Support is billed per vCPU-hour automatically; check the release calendar and plan upgrades
New instances silently enrolling in Extended Support	--engine-lifecycle-support open-source-rds-extended-support-disabled on create (fails instead of enrolling)
Serverless v2 minimum too high on idle clusters, or too many readers	ServerlessDatabaseCapacity vs MinCapacity; reader count per cluster
Idle or oversized readers and dev clusters running 24/7	Stop dev clusters, delete unused readers, Serverless v2 with auto-pause for dev
gp2 or io1 on RDS	gp3 is cheaper for most workloads; io2 for sustained high IOPS
Manual snapshots never deleted, long backup retention	Snapshot inventory and age
Cross-AZ and cross-Region data transfer	App and database in the same AZ for chatty traffic; replica placement
Database Insights Advanced on every dev database	Use Advanced only where execution plans are needed
Steady production on On-Demand	Reserved Instances (size-flexible within a family)

python3 scripts/aurora_storage_cost.py --compute 3200 --storage 450 --io 1900
python3 scripts/aurora_storage_cost.py --compute 3200 --storage 450 --io 1900 \
  --compute-ratio <io_opt_instance_price/standard> --storage-ratio <io_opt_storage_price/standard>

Feed it one month of Cost Explorer spend for the cluster grouped by usage type, and the price ratios from the Aurora pricing page for your Region and instance class. It prints the I/O share, the AWS 25% rule verdict, the projected I/O-Optimized bill, and the break-even I/O spend. Standard to I/O-Optimized can be switched once every 30 days; switching back is allowed any time (NVMe-based instances restart when switching).

Hard rules

• Destructive or disruptive actions need explicit user confirmation, naming the resource: deleting an instance or cluster (always keep a final snapshot), failover, blue/green switchover, --apply-immediately modifications that reboot, restoring over a name in use, deleting snapshots.
• Never put credentials in code or Terraform state outputs; use Secrets Manager or IAM auth.
• Encryption at rest must be chosen at creation (or via snapshot copy or blue/green with a new KMS key); plan it up front.
• Public accessibility off; databases in private subnets; security groups allow the app only.

Verification

1. Configuration diff: instance class, Multi-AZ, parameter group, backup retention, deletion protection, encryption, and Insights mode match the plan (describe-db-instances / describe-db-clusters).
2. Failover test: app recovers writes within the agreed time without restarts.
3. Load test or production replay on the new size or version: p95 latency and DBLoad within targets.
4. Cost: next month’s bill by usage type matches the projection; I/O and Extended Support lines are expected.

Output

**Database:** app-prod, Aurora PostgreSQL 17, I/O-Optimized, writer db.r8g.2xlarge + 1 reader (tier 0, other AZ)
**Why:** I/O was 34% of Aurora spend; projected saving 7%/month (aurora_storage_cost.py)
**HA:** failover tested 2026-10-20, writes resumed in 31 s, pools recovered
**Params:** app-aurora-pg17 (log_min_duration_statement=500ms, idle_in_transaction_session_timeout=60s)
**Upgrade:** blue/green from 15.x, switchover 600 s timeout, old cluster kept 14 days for PITR
**Monitoring:** Database Insights Standard; alarms on memory, connections, replica lag