AWS RDS removes the operational burden of database server management — OS patching, hardware provisioning, and storage scaling are handled by AWS — but it does not remove the responsibility of tuning the database engine itself. An out-of-the-box RDS instance using default parameter groups, the wrong instance class, and untuned storage can deliver a fraction of the performance your workload is capable of, at a higher cost than a properly configured smaller instance. This guide covers every layer of RDS performance optimisation: choosing the right instance class for your workload profile, tuning the PostgreSQL parameter group to match your instance's memory and I/O characteristics, configuring read replicas effectively, selecting and sizing storage between GP3 and io1, setting up CloudWatch and Performance Insights for genuine observability, reducing cost without sacrificing throughput, and deciding whether Aurora PostgreSQL offers meaningful gains for your specific use case. The examples focus on PostgreSQL on RDS, but the storage, monitoring, and cost principles apply equally to MySQL on RDS.
RDS Performance Fundamentals
RDS performance is determined by four interacting variables: compute (vCPUs and memory), storage (type, provisioned IOPS, and throughput), network bandwidth between the database and your application tier, and the PostgreSQL engine configuration applied through parameter groups. Poor performance on RDS almost always traces back to one of these four layers — and the most common mistake is tuning engine parameters without first confirming that the instance class and storage tier are appropriate for the workload. A db.t3.medium with a perfectly tuned parameter group will still be saturated by a workload that genuinely requires a db.r7g.2xlarge. Conversely, an oversized instance with default parameter group settings leaves substantial performance on the table. The correct starting point for any RDS performance investigation is AWS Performance Insights: determine whether database time is dominated by CPU, I/O wait, lock contention, or query execution — then target the layer that is actually the constraint rather than optimising a layer that is not.
Instance Type Selection Guide
RDS instance types fall into distinct families with meaningfully different performance profiles, and choosing the wrong family is one of the most expensive mistakes to make — correcting it requires a Multi-AZ failover maintenance window. The db.t class (t3, t4g) instances are burstable: they accumulate CPU credits during low-usage periods and spend them during traffic spikes. They suit development environments and light production workloads but will CPU-throttle under sustained load when credit balances are exhausted, producing severe latency spikes that can be difficult to correlate with the root cause. The db.m class (m6g, m7g, m6i) instances provide balanced compute and memory at moderate cost, suitable for general-purpose OLTP workloads with predictable traffic. The db.r class (r6g, r7g, r8g) instances are memory-optimised with significantly more RAM per vCPU — critical for PostgreSQL, where the ratio of shared_buffers to your active working set determines whether queries are served from memory or disk. For any database whose working data set exceeds 8GB, start with a db.r instance class and size based on memory requirements, not estimated CPU.
-- Assess cache hit ratio to determine if instance RAM is sufficient
-- Target: > 99% on a correctly sized memory-optimised instance
SELECT
round(
sum(heap_blks_hit)::numeric /
NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2
) AS cache_hit_pct
FROM pg_statio_user_tables;
-- Check current parameter values applied to this RDS instance
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
'shared_buffers', 'work_mem',
'effective_cache_size', 'max_connections'
);Parameter Group Tuning: Memory Settings
AWS RDS does not allow direct access to postgresql.conf — all engine configuration is managed through RDS parameter groups, applied either immediately for dynamic parameters or after a scheduled reboot for static ones. The three most impactful memory parameters are shared_buffers, effective_cache_size, and work_mem. shared_buffers controls how much instance RAM PostgreSQL reserves for its internal buffer pool: the standard starting value is 25% of total instance memory. RDS parameter groups support the formula syntax {DBInstanceClassMemory/32768} to set shared_buffers proportionally across instance sizes, avoiding hard-coded values that become wrong after a resize. effective_cache_size does not allocate memory — it signals to the query planner how much memory is available for caching including the OS page cache, influencing whether the planner prefers index scans or sequential scans. Set it to 75% of total instance memory. work_mem controls memory allocated per sort or hash operation per active query: on high-connection instances, setting it too aggressively can exhaust available RAM. A safe baseline is total RAM divided by max_connections divided by 4, tuned upward for workloads using connection pooling.
-- RDS Parameter Group values for a db.r7g.2xlarge (64 GB RAM)
-- Apply via AWS Console, CLI, or Terraform aws_db_parameter_group resource
-- shared_buffers: 25% of RAM — static parameter, requires reboot
-- Using RDS formula (scales automatically if instance class changes):
-- {DBInstanceClassMemory/32768} → sets in 8KB pages = 25% of RAM
-- For a fixed 64 GB instance, equivalent explicit value:
shared_buffers = 16384000 -- KB (≈ 16 GB)
-- effective_cache_size: 75% of RAM — dynamic, no reboot needed
effective_cache_size = 49152000 -- KB (≈ 48 GB)
-- work_mem: per sort/hash op — dynamic
-- 64 GB / 200 connections / 4 = ~80 MB per operation
work_mem = 81920 -- KB
-- Apply a single parameter change via AWS CLI
aws rds modify-db-parameter-group \
--db-parameter-group-name my-postgres15-params \
--parameters "ParameterName=work_mem,ParameterValue=81920,ApplyMethod=immediate"Parameter Group Tuning: Checkpoint and WAL Settings
Beyond memory settings, checkpoint and WAL parameters have significant impact on write throughput and I/O consistency on RDS. max_wal_size controls how much WAL can accumulate before a forced checkpoint. The default of 1GB is conservative — on write-heavy RDS instances with adequate IOPS, increasing max_wal_size to 4GB or 8GB reduces checkpoint frequency and smooths the burst I/O that each checkpoint generates. checkpoint_completion_target tells PostgreSQL to spread checkpoint writes across a fraction of the checkpoint interval rather than flushing everything immediately at the checkpoint trigger — setting it to 0.9 produces the most consistent I/O pattern and avoids the periodic latency spikes that tight checkpoints cause. wal_buffers controls shared memory used for WAL data before it is written to disk — increasing it to 64MB from the default reduces WAL write latency on write-intensive workloads. Finally, random_page_cost should always be lowered to 1.1 on RDS, since all current RDS storage types use SSD — this correction steers the planner to correctly prefer index scans over sequential scans for smaller result sets.
-- Parameter Group: checkpoint and WAL tuning for write-heavy workloads
-- All parameters below are dynamic (no reboot required) unless noted
-- Reduce forced checkpoint frequency on write-heavy instances
max_wal_size = 4096 -- MB (4 GB); note: static on some minor versions
-- Spread checkpoint I/O evenly to eliminate periodic latency spikes
checkpoint_completion_target = 0.9 -- dynamic
-- Increase WAL buffer memory (default is often 4MB — too small)
wal_buffers = 65536 -- KB (64 MB); static, requires reboot
-- Correct random_page_cost for SSD storage (default 4.0 assumes HDD)
random_page_cost = 1.1 -- dynamic
-- Apply multiple parameters at once
aws rds modify-db-parameter-group \
--db-parameter-group-name my-postgres15-params \
--parameters \
"ParameterName=checkpoint_completion_target,ParameterValue=0.9,ApplyMethod=immediate" \
"ParameterName=random_page_cost,ParameterValue=1.1,ApplyMethod=immediate"Read Replica Configuration
Read replicas are RDS's primary mechanism for scaling read throughput beyond what a single instance can serve. A read replica maintains a continuously synchronised copy of the primary database using PostgreSQL streaming replication — any query that does not require writes can be directed to a replica, multiplying read capacity by the number of replicas in the pool. RDS PostgreSQL supports up to 15 read replicas per primary, each deployable in any Availability Zone within the region or in a separate AWS region for cross-region read scaling and disaster recovery. Effective read replica usage requires routing at the application or connection pooler layer: write queries must go to the primary endpoint, read queries to replica endpoints. AWS RDS Proxy provides automatic read/write splitting but adds network hop latency; PgBouncer on an EC2 instance in the same VPC provides equivalent routing with lower overhead for high-throughput workloads. Always monitor replication lag via the ReplicaLag CloudWatch metric — sustained lag above your staleness tolerance indicates replicas are undersized, overloaded with read traffic, or receiving excessive replication volume from a write-heavy primary.
# Create a read replica in a separate AZ for read scaling
aws rds create-db-instance-read-replica \
--db-instance-identifier my-db-replica-1 \
--source-db-instance-identifier my-db-primary \
--db-instance-class db.r7g.xlarge \
--availability-zone us-east-1b \
--publicly-accessible false
# CloudWatch alarm: alert when replica lag exceeds 30 seconds
aws cloudwatch put-metric-alarm \
--alarm-name rds-replica-lag-high \
--metric-name ReplicaLag \
--namespace AWS/RDS \
--statistic Maximum \
--period 60 \
--threshold 30 \
--comparison-operator GreaterThanThreshold \
--dimensions Name=DBInstanceIdentifier,Value=my-db-replica-1 \
--evaluation-periods 3 \
--alarm-actions arn:aws:sns:us-east-1:123456789012:ops-alertsStorage Optimization: GP3 vs io1 vs io2
Storage is one of the most frequently misconfigured RDS components, and it directly determines I/O throughput and latency for all database operations. RDS offers three production-relevant storage types: gp2, gp3, and io1/io2. gp2, the original general-purpose SSD, delivered IOPS that scaled with volume size — 3 IOPS per GB, capped at 16,000 — meaning teams often had to over-provision storage to obtain adequate IOPS. gp3 decouples IOPS from volume size: it provides a baseline of 3,000 IOPS and 125 MB/s throughput regardless of volume size, with independently provisionable IOPS up to 16,000 and throughput up to 1,000 MB/s. Migrating from gp2 to gp3 delivers equivalent or better performance at approximately 20% lower cost — it is one of the highest-return, lowest-risk RDS optimisations available, executable online with no downtime. io1 and io2 Block Express are reserved for workloads genuinely requiring more than 16,000 IOPS — typically high-write OLTP systems with consistent sub-millisecond latency requirements — where io2 Block Express supports up to 256,000 IOPS with improved durability guarantees over io1.
# Migrate from gp2 to gp3 storage online — no downtime required
aws rds modify-db-instance \
--db-instance-identifier my-db-primary \
--storage-type gp3 \
--iops 6000 \
--storage-throughput 500 \
--apply-immediately
# Verify the change is applied
aws rds describe-db-instances \
--db-instance-identifier my-db-primary \
--query 'DBInstances[0].{StorageType:StorageType,IOPS:Iops,Throughput:StorageThroughput,SizeGB:AllocatedStorage}'
# Storage type decision guide:
# gp3: baseline 3K IOPS, independent scaling — right choice for most workloads
# io1/io2: > 16K IOPS, sub-ms latency SLAs — high-write OLTP only
# io2 Block Express: up to 256K IOPS, higher durability — largest production systemsCloudWatch Monitoring Setup
Effective RDS performance management requires visibility into the right metrics at the right alarm thresholds. CloudWatch provides RDS metrics at one-minute granularity at no additional cost, covering the most critical performance dimensions. The metrics requiring active alarms on every production instance are: CPUUtilization — alert at 80% sustained over 15 minutes, which on non-burstable instances signals the need for a larger class or query optimisation; DatabaseConnections — alert at 80% of max_connections, since exhausting connections cascades immediately to application-level timeout errors; ReadLatency and WriteLatency — alert when the average exceeds your latency SLA threshold, typically 20ms for OLTP workloads; FreeStorageSpace — alert at 20% remaining to allow time for a planned storage increase before RDS autoscaling triggers; and ReplicaLag for all replica instances. CloudWatch RDS metrics operate at the instance level and do not reveal which specific queries are consuming resources — that query-level visibility requires Performance Insights, covered in the next section.
# Create essential CloudWatch alarms for a production RDS instance
# CPUUtilization: alert at 80% average over 15 minutes
aws cloudwatch put-metric-alarm \
--alarm-name rds-cpu-high \
--metric-name CPUUtilization \
--namespace AWS/RDS \
--statistic Average \
--period 300 \
--threshold 80 \
--comparison-operator GreaterThanThreshold \
--dimensions Name=DBInstanceIdentifier,Value=my-db-primary \
--evaluation-periods 3 \
--alarm-actions arn:aws:sns:us-east-1:123456789012:ops-alerts
# Key metrics checklist:
# CPUUtilization → alert > 80% for 15 min (sustained, not burst)
# DatabaseConnections → alert > 80% of max_connections
# ReadLatency → alert > 0.020 seconds average (20 ms)
# WriteLatency → alert > 0.020 seconds average
# FreeStorageSpace → alert < 20% of allocated storage
# ReplicaLag → alert > 30 seconds on all replicasPerformance Insights and Enhanced Monitoring
CloudWatch metrics tell you that your RDS instance is under stress; Performance Insights and Enhanced Monitoring tell you exactly why. Performance Insights provides query-level visibility into database load: it shows which SQL statements consume the most database time, measured in Active Sessions, broken down by wait event type — CPU, I/O, lock contention, or client wait. This directly answers the diagnostic question 'what is the database waiting for right now?' without requiring direct database access. Performance Insights is free for a 7-day retention window and is worth enabling on every production instance. Enhanced Monitoring provides OS-level metrics at up to one-second granularity: CPU usage by individual process, memory breakdown, disk I/O by device, and network throughput. Standard CloudWatch RDS metrics are aggregated at the hypervisor level and can miss OS-level resource contention — Enhanced Monitoring operates inside the OS itself and captures what CloudWatch cannot. Enable Enhanced Monitoring at 15-second intervals on production instances and combine it with Performance Insights query data when diagnosing latency spikes or unexplained throughput degradation.
Cost Optimization Strategies
RDS is a significant line item in most cloud infrastructure budgets, and cost optimisation and performance optimisation are not mutually exclusive — the most common RDS waste patterns are also performance problems. Oversized instances running at consistently low utilisation are the most prevalent source of waste: an instance running below 15% average CPU with consistently high free memory is a candidate for downsizing to a smaller class, typically saving 30-50% on compute costs. Reserved Instances provide 30-45% savings over On-Demand pricing on a 1-year commitment for steady-state production databases, with up to 60% on a 3-year commitment — the break-even on a Reserved Instance versus On-Demand for a continuously running production database is typically under six months. Migrating from gp2 to gp3 storage reduces storage costs by approximately 20% with no performance trade-off. Audit read replica count quarterly: replicas provisioned during peak traffic events and left running at low utilisation can be shut down or downsized. Enable RDS storage autoscaling with a defined ceiling to prevent unexpected runaway storage growth from incremental accumulation.
# Identify downsizing candidates: check 7-day average CPU utilisation
aws cloudwatch get-metric-statistics \
--metric-name CPUUtilization \
--namespace AWS/RDS \
--dimensions Name=DBInstanceIdentifier,Value=my-db-primary \
--start-time $(date -u -d '7 days ago' +%Y-%m-%dT%H:%M:%SZ) \
--end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
--period 3600 \
--statistics Average Maximum \
--query 'sort_by(Datapoints,&Timestamp)[].[Timestamp,Average,Maximum]' \
--output table
# Enable storage autoscaling with a ceiling to prevent runaway growth
aws rds modify-db-instance \
--db-instance-identifier my-db-primary \
--enable-storage-autoscaling \
--max-allocated-storage 2000Aurora Migration Considerations
Amazon Aurora PostgreSQL-Compatible is not simply RDS PostgreSQL on faster hardware — it is a fundamentally different storage architecture that produces different performance characteristics for specific workload types. Aurora replaces PostgreSQL's standard storage layer with a distributed, fault-tolerant storage system that eliminates full fsync overhead on writes: Aurora acknowledges a write after two of six storage nodes confirm receipt, without waiting for a single-device disk flush. This produces 2-3x write throughput improvement over equivalent RDS PostgreSQL on I/O-bound workloads. Aurora additionally offers Aurora Global Database for cross-region active-passive failover with sub-second replication lag, Aurora Serverless v2 for variable-load databases that auto-scale compute between defined minimum and maximum Aurora Capacity Unit boundaries, and storage that auto-scales from 10GB to 128TB with no manual provisioning. Migration from RDS PostgreSQL to Aurora PostgreSQL is typically executed via snapshot restore — Aurora's PostgreSQL wire-protocol compatibility means no application code changes are required — but validate your specific PostgreSQL version compatibility, as Aurora tracks community releases with a minor version lag of several months.
# Migrate RDS PostgreSQL to Aurora PostgreSQL via snapshot restore
# Step 1: create a final snapshot of the source RDS instance
aws rds create-db-snapshot \
--db-instance-identifier my-rds-postgres \
--db-snapshot-identifier aurora-migration-$(date +%Y%m%d)
# Step 2: restore snapshot to a new Aurora PostgreSQL cluster
aws rds restore-db-cluster-from-snapshot \
--db-cluster-identifier my-aurora-cluster \
--snapshot-identifier aurora-migration-20260218 \
--engine aurora-postgresql \
--engine-version 15.4
# Step 3: add a primary instance to the cluster
aws rds create-db-instance \
--db-instance-identifier my-aurora-primary \
--db-cluster-identifier my-aurora-cluster \
--db-instance-class db.r7g.2xlarge \
--engine aurora-postgresqlAWS RDS delivers substantial operational simplicity, but performance on RDS is still an engineering discipline. Begin every optimisation effort by identifying the actual constraint — compute, storage, network, or engine configuration — using Performance Insights and CloudWatch before making changes. Select the instance class based on working set size and memory requirements rather than CPU estimates, and prefer db.r-class instances for any database with a meaningful working set. Configure parameter groups with memory values matched to your specific instance size, lower random_page_cost to 1.1 for SSD storage, and tune checkpoint and WAL parameters for your write volume. Migrate from gp2 to gp3 storage as an immediate, zero-downtime cost and performance improvement. Implement CloudWatch alarms on CPU, connection count, latency, and storage as a baseline, then enable Performance Insights for query-level visibility. Review instance utilisation and replica count quarterly to eliminate waste. Treat Aurora as a genuine architectural upgrade for I/O-bound workloads where its storage architecture unlocks throughput that RDS PostgreSQL cannot match.
Need Expert Database Guidance?
Book a free 30-minute diagnostic call. Whether you are debugging slow queries, evaluating databases, or planning a migration — we will give you specific, actionable recommendations, not generic advice.
BOOK FREE DIAGNOSTIC
