AWS RDS — Managed Relational Databases

Why it matters

Databases are typically the most expensive part of a cloud footprint and the highest risk from operational mistakes. RDS reduces that risk substantially by encoding good practices as the default.

It does that by taking work away from you, and the work it takes away comes bundled with capabilities it also takes away. Both halves of that trade are specific and enumerable, and almost every unpleasant surprise with RDS is someone meeting the second half for the first time during a migration or an incident.

The managed-database bargain, stated precisely

RDS is not a database. It is an operating model wrapped around five engines you could run yourself: PostgreSQL, MySQL, MariaDB, Oracle and SQL Server. The engine binaries are stock. What AWS supplies is the automation around them, and what you sign away is the ability to reach underneath that automation.

What AWS takes over: instance provisioning and replacement, the block volume beneath the data directory, engine installation and minor-version patching, the backup schedule and the transaction-log archive, standby replication and failover orchestration, and the endpoint DNS record your clients resolve. Those are precisely the tasks that produce most self-managed database outages: a backup nobody verified, a patch applied to the wrong node, a failover script nobody tested.

What you give up is narrower than "control" but much sharper: no operating-system login, no superuser role inside the engine, no arbitrary extensions, no arbitrary parameters, and no say in when a forced version upgrade lands. Each of those is a specific tool that stops working rather than an abstract loss of flexibility, and the rest of this article is largely about which tools and what you do instead.

The mental model that holds up: RDS is a control plane that owns the lifecycle of a database instance, plus an engine you rent read-write access to through the network port only. If a task requires touching the box, RDS will not do it for you and neither can you.

No superuser, no shell - and the tooling that stops working

On RDS for PostgreSQL your master user is granted rds_superuser, a role that looks powerful and is deliberately not SUPERUSER. On RDS for MySQL the master user does not hold SUPER. This single constraint accounts for most of the friction when a working self-hosted database is lifted into RDS.

On PostgreSQL, concretely: you can only CREATE EXTENSION for extensions AWS has vetted and shipped with the engine build, and anything requiring shared_preload_libraries - pg_stat_statements, auto_explain, pg_cron, pglogical - must additionally be listed in that parameter, which is static. An extension you compiled yourself has nowhere to live. COPY ... FROM PROGRAM is blocked because it would run shell commands as the engine user. The server-side file-reading functions are off limits for the same reason. And you cannot ALTER SYSTEM: configuration changes go through the parameter group, not through the engine.

On MySQL, the absence of SUPER means SET GLOBAL is refused for many variables and FLUSH TABLES WITH READ LOCK is unavailable - which is why mysqldump --master-data fails and why Percona XtraBackup cannot be pointed at an RDS instance at all. Trigger and view definers need attention during import, because you cannot create objects owned by a definer you are not.

The absent shell kills a whole class of tooling in one stroke. pgBackRest, Barman, WAL-G and XtraBackup all want the filesystem. perf, strace and pg_test_fsync all want the host. Reading the engine log means calling the log-download API or exporting to CloudWatch Logs rather than tailing a file. Any runbook step that begins "ssh to the database host" needs rewriting before the migration, not during the first incident after it.

The escape hatch, when a vendor agent genuinely must run on the host, is RDS Custom for Oracle and SQL Server, which hands back OS access at the cost of handing back some of the automation. Below that it is a database on EC2 and you own everything again.

-- What can this instance actually load?
SELECT name, default_version, installed_version
  FROM pg_available_extensions
 ORDER BY name;

-- Preload libraries are a static parameter. This value is set in the
-- DB parameter group, and changing it does nothing until a reboot.
SHOW shared_preload_libraries;

-- Confirm what you are not:
SELECT rolname, rolsuper, rolbypassrls
  FROM pg_roles
 WHERE rolname = current_user;
-- rolsuper = false, and no parameter will change that.

Parameter groups, option groups, and the reboot you did not plan

Engine configuration lives in a DB parameter group, a named object attached to the instance. The default group AWS creates for you is read-only - you cannot edit it - so the first thing every real deployment does is create a custom group, associate it, and reboot, because changing which group is attached is itself a change that requires a reboot.

Within a group every parameter has an apply type of dynamic or static. Dynamic parameters (work_mem, log_min_duration_statement, max_standby_streaming_delay) reach the running engine within a minute or so of the modify call. Static parameters (shared_preload_libraries, max_connections, wal_level) move the instance to a pending-reboot apply status and change nothing at all until you reboot it. There is no way to promote a static parameter to dynamic, and nothing in the console stops you from setting one and walking away convinced it took effect. Reading ParameterApplyStatus after every modify is the discipline that prevents the confusion.

Values can be formulas over instance attributes rather than constants. The PostgreSQL default for max_connections is of the form LEAST({DBInstanceClassMemory}/9531392, 5000), so resizing the instance silently resizes the connection ceiling. That is convenient right up until someone pins the parameter to a literal and the instance is later scaled down, at which point you have promised more connections than the memory can support.

Option groups are a separate mechanism for engine features that behave like installable add-ons rather than settings: Oracle options, SQL Server native backup/restore and TDE, the MariaDB audit plugin. They matter mostly on the commercial engines - on PostgreSQL nearly everything you want is an extension governed by the parameter group instead. Both objects are bound to a major engine version, which is one more reason a major upgrade is a project rather than a click: you build new groups to go with it.

# A custom group with one static and one dynamic parameter.
aws rds create-db-parameter-group \
  --db-parameter-group-name app-pg16 \
  --db-parameter-group-family postgres16 \
  --description "app tier"

aws rds modify-db-parameter-group \
  --db-parameter-group-name app-pg16 \
  --parameters \
    "ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements,ApplyMethod=pending-reboot" \
    "ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate"

# The check almost everyone skips:
aws rds describe-db-instances --db-instance-identifier app-prod \
  --query 'DBInstances[0].DBParameterGroups[0].ParameterApplyStatus'
# "pending-reboot"  => nothing you set as static is live yet

The architecture

An RDS instance is an EC2-backed database with managed storage. You choose engine, version, instance size, storage size and type (gp3, io2), and options. RDS launches the DB, applies AWS's baseline configuration, and hands you an endpoint.

Multi-AZ deployment maintains a synchronous standby in another AZ. On primary failure, RDS fails over to the standby with minimal data loss.

Storage: a one-way door with an autoscaler on it

Underneath an RDS instance is an EBS volume, and the volume-type decision - gp3 for nearly everything, io1/io2 when you are buying latency consistency rather than raw throughput - is exactly the decision developed in EBS Volume Types and AWS EBS. That ground is covered there and is not repeated here. What is specific to RDS is the shape the choice takes once a database sits on top.

The gp3 threshold. RDS gp3 storage has a size threshold - 400 GiB on the MySQL, MariaDB and PostgreSQL engines - below which the volume is pinned to the gp3 baseline of 3,000 IOPS and 125 MiB/s and additional IOPS simply cannot be bought at any price. Above the threshold, provisioned IOPS and throughput become adjustable. The practical consequence is that a 200 GiB database needing 8,000 IOPS must either over-allocate storage past the threshold or move to a provisioned-IOPS type. Storage sizing on RDS is not purely a capacity question.

Autoscaling. Setting MaxAllocatedStorage lets RDS grow the volume on its own. It triggers when free space stays below ten percent of allocated for at least five minutes, provided at least six hours have elapsed since the last storage modification, and each increment is the greater of 5 GiB or ten percent of current allocation. Note what those conditions imply: this is a slow safety net, not a burst absorber. A bulk load that consumes forty percent of the remaining volume in ten minutes will reach storage-full long before a second increment can fire, and an instance in storage-full stops accepting writes.

The one-way door. Allocated storage cannot be reduced. There is no shrink operation, no reclaim after a vacuum, no support ticket that helps. If autoscaling took you to 4 TiB because of a runaway audit table, you own 4 TiB until you build a new instance and move the data - a logical dump and restore, or logical replication with a cutover. Treat MaxAllocatedStorage as a budget ceiling you actually intend to pay rather than as a synonym for infinity, and alarm on FreeStorageSpace so a human sees the trend before the autoscaler is the only thing standing between you and a write outage.

After any storage change the volume enters storage optimization, during which further modifications are refused; on large volumes that runs for hours. Resizes are scheduled work, not an incident-response tool.

Multi-AZ is availability, not read scaling

If one sentence here is worth memorising, it is this: in a classic Multi-AZ deployment the standby serves no traffic. Not reads, not reporting queries, not backups you route yourself. It exists to become the primary. Teams provision Multi-AZ believing they have doubled their database and are then surprised twice - once by the bill, which roughly doubles, and once by the load, which does not move at all. This is comfortably the most common misconception about RDS.

Multi-AZ instance - one standby, zero reads

The classic deployment provisions a second instance in another Availability Zone and replicates synchronously: for PostgreSQL, MySQL, MariaDB and Oracle this happens at the storage layer, so a write is not acknowledged to the client until it is durable in both zones. SQL Server achieves the same guarantee with Database Mirroring or Always On availability groups. Because replication is synchronous, committed-write loss on failover is zero; because the standby is not open for queries, there is no endpoint that reaches it. The cost is write latency - every commit now pays a cross-AZ round trip. That is usually low single-digit milliseconds, and occasionally it is the reason a chatty write path halves in throughput the day Multi-AZ is switched on.

Multi-AZ DB cluster - two standbys that do serve reads

The Multi-AZ DB cluster deployment, offered for MySQL and PostgreSQL, is a genuinely different topology: one writer plus two readable standbys spread across three Availability Zones, with semi-synchronous commit - the writer acknowledges once one of the two standbys confirms rather than waiting for both. That gives you a reader endpoint alongside the writer endpoint, and typically a faster failover than the instance deployment, because the cluster already has a quorum and a promotion path rather than a handoff to a cold node. When someone claims "Multi-AZ gives us read scaling," this is the deployment they have in mind - and it is worth checking which one is actually provisioned before designing around either.

Failover is a CNAME flip, and the clients that ignore it

The instance endpoint - something like app-prod.abc123xyz.us-east-1.rds.amazonaws.com - is a CNAME. A failover fences the old primary, promotes the standby, and repoints that record. RDS emits an event and an EventBridge notification while it happens, and the DNS change itself typically completes inside a minute or two.

Applications nevertheless report failovers lasting five, ten or thirty minutes, and almost none of that duration belongs to RDS. It is caching on the client side, in three stacked layers:

The runtime caches DNS. The JVM in particular caches successful lookups according to networkaddress.cache.ttl, which under the historical security-manager default is -1: cache forever. A long-lived JVM will keep resolving the endpoint to the dead node's address until somebody restarts it. Setting the TTL to five or ten seconds is a one-line fix that most teams discover during their first real failover.

Connection pools hold sockets, not names. Even with correct DNS, fifty established connections to the old primary will not re-resolve until each socket is closed. Without a validation query and a bounded maximum connection lifetime, those sockets sit in the pool failing every checkout.

The resolver adds its own TTL. The OS stub resolver and any local caching daemon each layer on more.

Every fix is client-side and unglamorous: a short DNS TTL in the runtime, connection validation plus a maximum lifetime in the pool, finite driver-level socketTimeout and loginTimeout so a hung socket fails fast instead of parking a thread, TCP keepalives, and jittered reconnect so the freshly promoted primary is not met by every application instance simultaneously. Drivers exist that handle this properly - the AWS JDBC Driver and the MariaDB connector's failover mode both track topology rather than trusting DNS - and adopting one is usually cheaper than tuning four layers by hand.

Then test it. aws rds reboot-db-instance --force-failover is a deliberate failover you can run inside a maintenance window, and it is the only honest way to learn what your stack actually does when the endpoint moves.

Read replicas and the lag you have to design around

A read replica is engine-native asynchronous replication: PostgreSQL streams WAL to a hot standby, MySQL and MariaDB ship row-format binlogs. Replicas get their own endpoints, may run a different instance class and a different parameter group, may live in another region, and may themselves be Multi-AZ. They are a read-scaling and disaster-recovery mechanism, and they are emphatically not backups - a DELETE that lost its WHERE clause replicates faithfully, in well under a second.

The number that matters is ReplicaLag, reported in seconds. It is rarely a network problem. On MySQL the classic cause is apply-side serialisation: one enormous transaction on the writer replays as one long apply on the replica. On PostgreSQL the cause is conflict handling - a long analytical query on the replica blocks WAL application, and you are choosing between max_standby_streaming_delay (let the query finish, let lag grow) and cancellation, which surfaces to users as canceling statement due to conflict with recovery. Turning on hot_standby_feedback stops the cancellations by holding vacuum back on the primary, trading replica stability for bloat upstream. No setting gives you both; pick which side pays.

Because replication is asynchronous, a read issued to a replica immediately after a write on the primary may not see that write. Whether a given query path can tolerate that is a correctness decision made per path, not a configuration knob - and the routing techniques that resolve it are developed in read-replica routing rather than restated here.

Promotion is one-way. promote-read-replica detaches the replica into a standalone writable instance, and there is no un-promote. A cross-region replica promoted during a regional event becomes a new primary that you must then rebuild an entire replication topology around, which is worth rehearsing before you need it.

Backups are snapshots plus logs, and restore always builds a new instance

Automated backups are two mechanisms working together. Once a day, during the backup window, RDS takes a storage-level snapshot of the volume. Continuously - on the order of every five minutes - it archives the engine's transaction logs to S3. Point-in-time recovery is the snapshot plus log replay, which is why the realistic RPO is measured in minutes rather than in the twenty-four hours a daily snapshot alone would imply, and why LatestRestorableTime trails the present by a few minutes instead of being "now".

Retention runs from 0 to 35 days, and 0 means automated backups are off - a defensible setting for a scratch instance and an unrecoverable one for anything else. Manual snapshots sit outside retention entirely: they live until you delete them, they can be copied across regions and shared with other accounts, and they are the right tool for "checkpoint before the migration".

Two semantics catch people repeatedly.

Restore builds a new instance. Always. There is no in-place rollback of a production database. restore-db-instance-to-point-in-time provisions a fresh instance with a new endpoint, so the recovery procedure necessarily includes an application cutover - repoint the application, or rename both instances. Budget for that inside your RTO, along with the fact that a snapshot-backed volume is hydrated lazily from S3, so the restored instance is slow on first touch of cold blocks until initialisation finishes.

Deleting the instance deletes its automated backups. Manual snapshots survive; automated ones do not, apart from a final snapshot if you explicitly ask for one at delete time. Nearly every "we deleted the wrong environment and lost the data" story reduces to this one rule. Enable deletion protection, and take a manual snapshot before anything irreversible.

One last detail that only ever surfaces under time pressure: an encrypted snapshot inherits its KMS key, and sharing one with another account requires a customer-managed key, because the AWS-managed aws/rds key cannot be shared. Discovering that while trying to hand a snapshot to a partner team during an incident is a bad time to discover it.

The maintenance window and the upgrades you do not control

Every instance carries a weekly 30-minute maintenance window, and what lands in it is AWS's decision at least as much as yours. AutoMinorVersionUpgrade, on by default, means minor engine versions are applied during that window with a restart. OS and hypervisor patching happens there too - and on a Multi-AZ deployment AWS patches the standby first, fails over, then patches the former primary. Routine maintenance therefore triggers a real failover, which means the client-side behaviour described above is exercised in production whether or not you scheduled a test.

Major versions are not optional forever. Each engine version has an end-of-standard-support date, and past it AWS will upgrade you. RDS Extended Support buys additional time on some engines at a premium, but it is a deferral, not an exemption. The version calendar belongs on the roadmap as an item with a deadline, because the alternative is letting AWS pick the date.

The other forced event is CA certificate rotation. RDS presents a server certificate chained to an AWS certificate-authority bundle, and those bundles expire. When one does, every client that verifies the server certificate must have the replacement bundle in its trust store before the instance rotates, or TLS handshakes fail across the whole fleet at once. These rotations are scheduled and announced well in advance, and they remain one of the most reliable ways to take an avoidable outage, because the actual work lives in a hundred application images rather than in the console.

Blue/green deployments for the upgrade you cannot take downtime for

A major version upgrade applied in place is a long write outage: the engine stops, the upgrade runs, statistics are rebuilt, and you learn how long it took only afterwards. RDS blue/green deployments exist to convert that into a switchover measured in seconds.

The mechanism: RDS creates a green environment - a copy of the blue instance and its replicas - and keeps it synchronised from blue using logical replication. Because green is a separate environment, you can do things to it that you would never do to production: upgrade the major version, change the parameter group, change the instance class, add an index, and then test against green's own endpoints for as long as you like. When you switch over, RDS blocks writes on blue, waits for green to drain its replication lag, runs health checks, and renames the endpoints so green takes over the production names. If lag does not drain inside the timeout the switchover aborts and blue is still blue, which is the property that makes the whole thing safe to attempt.

The constraints follow from the word logical. Logical replication does not carry DDL, so schema changes during the sync window need care and coordination. Tables without a primary key or a suitable replica identity are a problem. Some data types and engine features do not replicate. And green is a real, separately billed environment for as long as it exists, so the cost model is "roughly double for a day", not "free". None of that diminishes its value: for a major upgrade on a database that cannot be down for twenty minutes, this is the mechanism that exists, and building it yourself out of replication and a cutover script is strictly more work with strictly fewer guardrails than the generic blue/green pattern would suggest.

Connections, max_connections, and why a pooler matters

PostgreSQL forks a backend process per connection and MySQL allocates per-thread buffers, so connections consume memory whether or not they are executing anything. RDS reflects that by deriving max_connections from instance memory rather than shipping a constant - which is why a small burstable class and a large memory-optimised class have wildly different ceilings, and why scaling an instance down can quietly drop you below your application's steady-state demand.

Serverless callers break the model outright. Lambda scales by creating concurrent execution environments, each with its own connection and no awareness of the others; a thousand concurrent invocations is an attempt at a thousand connections arriving over a few seconds. The database does not queue them, it refuses them, and FATAL: sorry, too many clients already is the entire outage.

RDS Proxy sits between callers and instance, holding a warm pool of database connections and multiplexing many client connections onto few server ones. Two behaviours are worth knowing before you deploy it. First, it integrates with Secrets Manager and IAM authentication, so callers present an IAM identity instead of a password. Second - and this is the gotcha - the proxy pins a client to a dedicated backend connection whenever the session does something that cannot safely be shared: setting session variables, creating temporary tables, certain prepared-statement patterns. A pinned connection is no longer multiplexed, and an application that pins on every request ends up with a proxy that costs money and pools nothing. The CloudWatch metric DatabaseConnectionsCurrentlySessionPinned is what tells you, and it is the first thing to check before concluding the proxy did not help.

The proxy also holds client connections open across a failover and reconnects on the far side, which shortens the perceived interruption considerably - often the strongest argument for it even in workloads that do not need pooling for capacity reasons. General pool-sizing theory belongs to database connection pooling; what is specific to RDS is that the ceiling is a formula over instance memory, and the multiplexer in front of it has a pinning failure mode you must measure.

Encryption at rest is a create-time decision

You choose encryption when you create the instance, and you cannot change your mind afterwards. There is no modify call that turns storage encryption on for a running instance, and there is no call that turns it off either. An unencrypted instance stays unencrypted for its entire life.

The migration path is a detour rather than a setting: take a snapshot, copy the snapshot while specifying a KMS key - the copy operation is where encryption is introduced - restore a new instance from the encrypted copy, and cut over. That is a full restore plus an application cutover, which is why "we will turn encryption on later" is in practice a decision to schedule a migration later.

The consequences propagate outward. Read replicas of an encrypted instance must themselves be encrypted. A cross-region replica or a cross-region snapshot copy needs a KMS key in the destination region, with a key policy that permits the operation. Restores and blue/green environments inherit the same key requirements. The safe default is to create every instance encrypted with a customer-managed key from the beginning; the cost is negligible and it removes an entire category of future migration. Envelope encryption and key hierarchy themselves are treated in Cloud KMS.

Seeing inside a box you cannot log into

Giving up shell access costs you top, iostat and the log directory, so RDS offers three replacements that are easy to conflate. CloudWatch metrics are the hypervisor's view at one-minute granularity: CPU, FreeableMemory, FreeStorageSpace, ReadIOPS, DatabaseConnections, ReplicaLag. Enhanced Monitoring runs an agent inside the instance and reports genuine OS-level metrics - per-process memory, load average, per-device disk statistics - at granularities down to a second, which is the only way to observe a spike shorter than a CloudWatch period. Performance Insights is the database's own view: load expressed as average active sessions, broken down by wait event, SQL statement and user.

The rule of thumb: CloudWatch tells you the instance is unhealthy, Enhanced Monitoring tells you which resource is exhausted, and Performance Insights tells you which query did it. Turning all three on before an incident costs very little and is the closest substitute available for the shell you gave up.

When RDS is the wrong choice

Extreme write throughput. RDS scales writes vertically and only vertically: one writer, one instance class, one ceiling. When you reach the largest class and the write path is still saturated, the next move is architectural - partitioning, sharding, or moving the hot table into a different store - and nothing in RDS helps you make it. If your growth curve predicts hitting that wall, design the partitioning strategy while you still have headroom, not from the biggest box.

A genuine need for OS-level control. A compiled extension, a vendor agent that must run on the host, a specific kernel or filesystem tunable, a physical backup tool your compliance process names by product: none of these are RDS workloads. RDS Custom returns host access for Oracle and SQL Server; below that the honest answer is a database on EC2, with the operational burden that implies.

A workload that wants Aurora's storage architecture. Aurora rewrites the storage layer rather than just the automation around a stock engine, and for workloads dominated by read fan-out, fast crash recovery, or clone-and-test cycles it is the better fit. Its storage design is developed in Aurora storage architecture and the product-level comparison belongs with the Aurora deep dives; the boundary here is only this - choose RDS over Aurora because you want the stock engine's exact behaviour and a simpler cost model, not because nobody evaluated Aurora.

And the non-reason: RDS is not the wrong choice merely because it is "less flexible". For the large majority of relational workloads the automation is worth more than the flexibility, and teams that do move off it almost always move because of one specific blocked capability they can name in a sentence.