2 Amazon RDS in Practice

Match the database to the workload

Amazon RDS is a managed service for running relational database engines. AWS provisions and operates much of the underlying infrastructure, but customers remain responsible for schema design, query quality, access controls, and workload sizing. Supported engines are Db2, MariaDB, Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.

Choose a database based on the application's workload and access patterns rather than starting with a preferred AWS service. Relational data, SQL, transactions, joins, compatibility requirements, licensing, required features, and team experience all inform the choice.

Choose a database and engine

Choose RDS when an application needs a relational model, SQL, transactions, joins, and a supported engine. Select an engine version compatible with the application and its required features.

Amazon Aurora is a separate RDS-compatible service with its own architecture and engine choices. Consider it when an AWS-built, MySQL- or PostgreSQL-compatible relational database and its cluster-oriented capabilities fit the workload; compare its features, limits, and cost with standard RDS engines.

Consider DynamoDB for key-value or document-style access patterns that need managed, elastic NoSQL capacity, rather than for arbitrary relational joins. Consider Redshift for analytical warehousing and large-scale reporting, rather than as an application's transactional database.

After selecting an engine, choose an instance class and storage to match measured CPU, memory, throughput, and I/O needs. Test engine-parameter changes in a nonproduction environment before applying them in production.

Choose an availability design

Select a deployment based on availability, read scaling, recovery objectives, and cost. A Single-AZ DB instance runs in one Availability Zone and has no standby in another zone, so it can suit development, testing, or workloads with lower availability needs.

A Multi-AZ DB instance maintains a standby in another Availability Zone for high availability and failover; that standby does not serve application reads. A Multi-AZ DB cluster has a writer and two reader instances across three Availability Zones, providing high availability with additional read capacity. Supported engines and configurations vary.

A Read replica can support read scaling or a replica in another Region. It uses asynchronous replication, so it can lag. It is not a substitute for a synchronous high-availability standby.

Design the application to reconnect after a Multi-AZ failover, use the appropriate endpoint, and send only suitable read traffic to replicas. Test failover and replica-lag behavior rather than assuming these events will be invisible to the application.

Secure network and database access

Create the DB instance in a VPC and normally keep production instances private. Place it in a DB subnet group spanning Availability Zones as required by the chosen deployment. Configure security-group rules to permit database traffic only from the application or approved administrative sources; do not open a database port broadly to the internet.

Use database users and privileges in the selected engine to control access to data. Use IAM policies to control who can manage RDS resources. Store and rotate credentials securely, for example with AWS Secrets Manager. Enable encryption at rest when creating the DB instance, and configure clients to use TLS for connections in transit. Check engine-specific support and settings before relying on a particular authentication or encryption feature.

Connect to the database

After creation, retrieve the DB endpoint, port, database name, and authentication details from the RDS configuration. Connect with a client compatible with the selected engine. For example, a PostgreSQL client can connect with:

A connection failure is often a network or authentication issue, not a SQL issue. Check that the instance is available, the endpoint and port are correct, the client can reach the VPC, the security group permits traffic from its source, and the credentials and TLS settings match the database configuration.

Improve query performance

Use standard SQL supported by the selected engine. Design tables and indexes around the application's access patterns, and inspect the query plan when a query is slow. An index can speed up reads, but it consumes storage and can increase the cost of inserts, updates, and deletes. Add indexes to address observed workloads rather than speculatively.

For example, in PostgreSQL, an index on frequently filtered columns can help retrieve recent orders for one customer:

Check the query plan and actual workload to confirm whether the index helps. Also review connection counts, transaction duration, lock waits, and connection-pool behavior. A larger DB instance will not fix every query or application-design problem.

Back up and rehearse recovery

Automated backups support point-in-time recovery within the configured retention period. For DB instances, the retention period can be up to 35 days; a value of zero disables automated backups. Choose a retention period that matches recovery requirements, and confirm the latest restorable time when needed.

Manual DB snapshots provide restore points before risky changes or for longer-term retention. Restoring from a snapshot creates a new DB instance. Recovery therefore also requires checking the restored database and directing the application to its endpoint.

For production, document and rehearse recovery, including who can restore, how clients switch endpoints, and how data integrity is validated. A backup is not proven useful until a restore has been tested.

Monitor and respond

Use Amazon CloudWatch metrics and alarms to track resource use and availability. Choose metrics and alarm thresholds that reflect the workload. Common signals include CPU use, freeable memory, storage capacity, database connections, read and write activity, and latency. Review database logs for engine errors and slow queries.

Use Enhanced Monitoring when operating-system-level detail is useful. Use Performance Insights, where supported, to investigate database load, top SQL, and wait events. These tools complement CloudWatch: they provide more detailed views of resource or database activity, while alarms notify operators when defined conditions occur.

Include backup status, replica lag where relevant, and RDS events such as maintenance or failover in routine operations monitoring.