Amazon RDS running out of storage: emergency steps and storage autoscaling

#Symptoms

• The FreeStorageSpace metric is near zero.
• Instance status storage-full. Writes fail, and the instance may stop accepting connections.
• Application errors such as could not extend file (PostgreSQL) or The table is full / error 28 (MySQL).

#1. Grow the storage now

aws rds describe-db-instances --db-instance-identifier prod-db \
  --query "DBInstances[].{status:DBInstanceStatus,alloc:AllocatedStorage,max:MaxAllocatedStorage,type:StorageType}" --output table

aws rds modify-db-instance --db-instance-identifier prod-db \
  --allocated-storage 600 --apply-immediately

Important

After a storage modification you must wait 6 hours (or until the storage optimization finishes, whichever is longer) before modifying storage again. Grow by a comfortable margin, at least 20%, not just enough to get by.

#2. Turn on storage autoscaling

aws rds modify-db-instance --db-instance-identifier prod-db --max-allocated-storage 1000 --apply-immediately

RDS grows storage automatically when free space falls below 10% for 5 minutes, subject to the same 6-hour rule.

#3. Find what consumed the space

PostgreSQL

SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;               -- inactive slots retain WAL indefinitely

Abandoned logical replication slots (from a DMS task or Debezium connector that was stopped) are a top cause.

MySQL

SELECT table_schema, ROUND(SUM(data_length + index_length)/1024/1024/1024, 2) AS gb
FROM information_schema.tables GROUP BY table_schema ORDER BY gb DESC;
SHOW BINARY LOGS;                         -- large binlog retention?
CALL mysql.rds_show_configuration;        -- 'binlog retention hours'

Other sources: temporary files from large sorts, general or slow logs written to tables, and bloat (see the PostgreSQL bloat post).

#4. Reclaim space

• PostgreSQL: drop unused slots (SELECT pg_drop_replication_slot('name');), vacuum bloated tables, archive old partitions.
• MySQL: reduce binlog retention (CALL mysql.rds_set_configuration('binlog retention hours', 24);), purge old data in batches, then OPTIMIZE TABLE during a quiet period.

Note

RDS storage can't be reduced in place. To shrink, migrate to a new, smaller instance (dump/restore, or a blue/green deployment where supported).

#Prevent it next time

• Alarm on FreeStorageSpace < 15% and on the trend. A steady 2 GB/day growth is predictable.
• Enable storage autoscaling with a sensible maximum on every production instance.
• Monitor OldestReplicationSlotLag (PostgreSQL) and TransactionLogsDiskUsage.