RDS PostgreSQL in production: a AWS guide for developers

Moving from development to RDS PostgreSQL in production feels like crossing a bridge—it looks similar from a distance, but the load-bearing details matter enormously. I've watched well-architected applications collapse under traffic because the database wasn't configured for real-world access patterns. This article walks through the four non-negotiable configurations that separate a database that survives 9 PM from one that thrives at 9 AM after a viral feature launch.

Instance sizing — Memory-optimized instances for most production databases

Choosing the right instance class is the first decision that compounds over months. I used to pick db.t3.medium because it was cheap, watched the CPU spike to 80% under modest load, then scrambled to resize during business hours. The cheaper instance classes like burstable T-series are fine for staging or light production workloads, but they're not designed for sustained traffic.

For production, memory-optimized instances—the R-series or R6i—should be your baseline. Why? Because PostgreSQL caches the working set in shared buffers and OS page cache. If your dataset or hot data fits in RAM, every query becomes CPU-bound instead of I/O-bound. A db.r6i.xlarge (32 GB RAM, 4 vCPU) costs roughly 2.5x a T-series equivalent, but it'll handle 10x the throughput for read-heavy workloads.

Here's the real calculation: if your database is 500 GB but only 100 GB is actively queried, a memory-optimized instance with 256 GB of memory means almost everything lives in cache. Compare that to a burstable instance with 4 GB—every miss means a disk read, which adds 5–10 ms of latency. Multiply that across thousands of queries per second, and the T-series instance isn't just slower; it's actively hurting user experience.

One project I worked on ran an e-commerce platform with a 50 GB user and order dataset. Orders from the past 7 days (roughly 10 GB) were hot. We started with db.t3.xlarge. Peak traffic caused CPU saturation, and the application timeout frequently. Moving to db.r6i.large (16 GB) took 20 minutes and solved the problem entirely—no code changes.

A practical rule: allocate memory equal to your active dataset plus 20% headroom. If you don't know your active dataset size, set up a test workload first.

Storage — gp3 with autoscaling enabled

Storage is where budget concerns often override engineering judgment, and it's a mistake. Older configurations defaulted to gp2, which ties IOPS to volume size (3 IOPS per GB, capped at 16,000). A 100 GB gp2 volume gives you only 300 IOPS—laughable for production.

gp3 decouples IOPS from size. You can have a 50 GB volume with 16,000 IOPS if you need it. The cost difference is negligible (roughly $0.10 per provisioned IOPS per month), but the performance difference is massive. For RDS PostgreSQL, I always provision gp3 with 3,000 IOPS at minimum. For write-heavy workloads (trading systems, event streaming), 6,000–12,000 IOPS is standard.

But static provisioning fails when your traffic grows. Enable autoscaling from day one. RDS can automatically expand your volume up to 64 TB (for most accounts). Configure a threshold—I use 80% of allocated storage—and let AWS handle growth.

Here's a scenario: a CRM system starts with 500 GB storage. Without autoscaling, someone wakes up at 3 AM to an alert that storage is full. With autoscaling, the volume expands silently, and you address it in the morning. The cost difference is zero; you pay for what you use either way.

To enable autoscaling via the AWS CLI:

aws rds modify-db-instance \
  --db-instance-identifier my-postgres-db \
  --storage-autoscaling-enabled \
  --max-allocated-storage 1000 \
  --apply-immediately

This allows the volume to grow up to 1,000 GB automatically. Monitor your growth over time and adjust max-allocated-storage accordingly. Also monitor CloudWatch metrics—if autoscaling kicks in frequently, it's a sign your baseline is too small.

Parameter groups — Tune connections, logging and statement timeouts

The default PostgreSQL parameter group is optimized for nothing. It's a blank slate. Tuning three parameters will handle 80% of production issues.

max_connections: Default is 100. In a microservices architecture with 10 services, each maintaining 20 connection pools, you'll exhaust this immediately. I set this to 500 for most production databases, then monitor actual connection count in CloudWatch. Formula: (number of applications) × (connections per pool) + 50 buffer.

-- Check current connections
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

statement_timeout: Prevents runaway queries from hanging forever. Default is unlimited (dangerous). Set this to something reasonable—5000 ms (5 seconds) is my baseline, but adjust based on your report generation or ETL workload. Long-running backups aren't affected because they don't go through the connection pool.

log_min_duration_statement: Logs queries slower than a threshold. I set this to 1000 ms in production. Combined with Performance Insights (covered next), this creates a complete audit trail of problematic queries.

To update parameters, create or modify a parameter group in AWS and associate it with your instance (requires a restart). Here's the pattern for critical changes:

aws rds modify-db-parameter-group \
  --db-parameter-group-name my-postgres-params \
  --parameters "ParameterName=max_connections,ParameterValue=500,ApplyMethod=immediate"

Note: Some changes are immediate; others require a restart. Check the documentation before applying.

Performance Insights — Find slow queries and wait events

Performance Insights is the hidden gem most teams don't enable. It gives you a real-time view of where your database is spending time—CPU, locks, I/O, or other wait events. This transforms debugging from guesswork to data-driven decisions.

Enable it during instance creation or modification (requires a restart on existing instances, but it's worth it):

aws rds modify-db-instance \
  --db-instance-identifier my-postgres-db \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \
  --apply-immediately

Once enabled, the AWS Console gives you a timeline showing load over time. Each bar is segmented by wait event—if it's red (CPU), your queries are CPU-bound; if it's blue (lock), you have contention; if it's purple (I/O), you're disk-bound.

Real example: a ride-hailing system I worked on started experiencing 100 ms query latency spikes every 15 minutes. The application team blamed slow code. Performance Insights showed a clear pattern: spikes coincided with lock waits on the drivers table. Root cause? A batch job updating driver status every 15 minutes without proper indexing. Adding a partial index and adjusting the update strategy (batching smaller chunks) eliminated the locks and reduced latency to 10 ms.

Performance Insights also integrates with RDS Recommendations. AWS analyzes your workload and suggests missing indexes, parameter tuning, or schema changes. It's not always right, but it's a useful starting point.

The drill: every week, spend 10 minutes reviewing Performance Insights. Look for patterns. If you see consistent lock waits, investigate your application code. If you see I/O waits, consider adding more memory or increasing IOPS.

Common mistakes

• Starting with burstable instances: They work fine for staging, but production needs sustained performance. Plan to move to memory-optimized from the start.
• Static storage allocation: Turning off autoscaling to "save money" costs far more when someone scrambles to resize at 2 AM.
• Ignoring log_min_duration_statement: You can't optimize what you can't see. Enable slow query logging immediately.
• Not monitoring Performance Insights: It's like having a health monitor that nobody checks. Make it part of your weekly ritual.
• Tuning parameters without testing: Changes like statement_timeout can break long-running backups. Test in staging first.
• Forgetting about connection pooling on the application side: RDS handles per-instance limits, but your applications need to pool efficiently (PgBouncer, Hikari for Java, etc.).

Key takeaways

• Use memory-optimized instances (R-series) for production; burstable T-series is not suitable for sustained traffic.
• Provision gp3 storage with 3,000+ IOPS and enable autoscaling to avoid storage emergencies.
• Tune max_connections, statement_timeout, and log_min_duration_statement in a custom parameter group from day one.
• Enable Performance Insights and review it weekly to identify slow queries, lock contention, and I/O bottlenecks.
• Test all parameter changes in staging before applying to production.
• Combine RDS monitoring with application-level connection pooling for the complete picture.