One backfill grew our RDS storage to 2 TB, and the bill stayed after cleanup

In January, the analytics team ran a backfill on our production PostgreSQL RDS instance: they recomputed two years of order aggregates into temporary tables, then swapped them in. The job ran for three days. Storage autoscaling kept up and nobody was paged. In March, the cost review showed the instance's storage line had gone from about 46 USD to about 230 USD per month and stayed there.

$ aws rds describe-db-instances --db-instance-identifier orders-prod \
    --query 'DBInstances[0].[AllocatedStorage,MaxAllocatedStorage,StorageType]'
[2048, 4000, "gp3"]

The database itself was about 310 GB:

SELECT pg_size_pretty(sum(pg_database_size(datname))) FROM pg_database;
-- 312 GB

Reproducing it

The CloudWatch FreeStorageSpace metric for January showed the sawtooth pattern of storage autoscaling: free space dropping to about 10 percent, then a jump as allocated storage grew, repeated five times during the backfill. After the temporary tables were dropped, free space rose to 1.7 TB and stayed there.

What we ruled out

• Bloat that VACUUM could fix. The database size was genuinely 312 GB; the unused space was outside the database, in allocated storage.
• Snapshots. Backup storage is billed separately and was within the free allowance.

Root cause

RDS storage autoscaling increases allocated storage when free space runs low, up to MaxAllocatedStorage. It never decreases it, and allocated storage on an existing RDS instance cannot be reduced in place by any modify call. Our backfill wrote temporary tables and a large amount of WAL, which pushed usage to around 1.8 TB at peak. After cleanup, the space was free inside the volume but still allocated and billed. MaxAllocatedStorage was set to 4,000 GB, ten times our normal size, so nothing stopped the growth.

What is happening under the hood

RDS charges for allocated storage, not used storage. Autoscaling triggers when free space is below 10 percent of allocated, the low-space condition lasts at least five minutes, and at least six hours have passed since the last storage modification (the details are in the RDS documentation). Each increase is the larger of a fixed amount, 10 percent of current allocation, or predicted growth.

To get back to a smaller allocation you need a new storage volume: create a new instance with smaller storage and move the data, by dump and restore, logical replication, or a managed blue/green deployment. Some engines and versions support storage shrink through RDS Blue/Green Deployments, which create a green copy with smaller storage and switch over with a short interruption. Check the documentation for your engine and version.

Environment

Minimal reproduction

aws rds modify-db-instance --db-instance-identifier test-db --allocated-storage 100 --apply-immediately
# rejected with an InvalidParameterCombination error: the new value must not be lower than the current allocation

Any value lower than the current allocation is rejected; only increases are accepted.

The fix

1. Shrink with a blue/green deployment. For our engine version this was supported, so we created a green environment with 500 GB of storage:

aws rds create-blue-green-deployment \
  --blue-green-deployment-name orders-shrink \
  --source arn:aws:rds:eu-west-1:111122223333:db:orders-prod \
  --target-allocated-storage 500

The green instance replicated from blue with logical replication. We checked replication lag, ran read-only smoke tests against the green endpoint, then switched over during a low-traffic window. Switchover took about a minute of write unavailability, which we had announced.

If blue/green storage shrink had not been available, the fallback plan was a new instance with pg_dump/pg_restore for the initial copy plus logical replication to catch up, and a DNS-based cutover.

2. Cap autoscaling at a sensible headroom.

aws rds modify-db-instance --db-instance-identifier orders-prod --max-allocated-storage 800

800 GB is about 2.5 times normal data size. A runaway job now hits the ceiling and alerts instead of growing silently.

3. Run backfills elsewhere. Heavy one-off jobs now run against a restored snapshot or a dedicated reporting replica, and results are loaded back with a controlled, batched process.

Alternatives and trade-offs

• Accept the cost. 180 USD a month is small; the operational risk of a migration may not be worth it for small databases. For ours, the cost was small but the precedent mattered, and we also wanted to lower storage for faster snapshots.
• Keep the space for growth. If you expect to grow into it within months, shrinking is wasted effort.
• Aurora, where storage is billed by usage and shrinks automatically as data is deleted. A larger decision than a storage fix.

How to test the fix

We rehearsed the blue/green shrink on a restored snapshot first, measured switchover time, and verified application reconnection behaviour with our connection pooler.

Confirming in production

• Allocated storage 500 GB after switchover; storage cost back to about 58 USD per month.
• No data loss: row counts and checksums on key tables matched between blue and green before switchover.
• Snapshot and restore times dropped because of the smaller volume.

Regression guard

• MaxAllocatedStorage set to 2 to 3 times data size on every instance, enforced by a Terraform policy.
• An alert on storage autoscaling events (RDS event category low storage and modification events).
• A runbook for backfills that requires a capacity estimate and a target that is not the production primary.

Security implications

The blue/green deployment copies all data to a new instance. We confirmed encryption at rest with the same KMS key, the same parameter group with TLS enforced, the same security groups, and that the old blue instance was deleted after the retention period so an extra copy of production data does not linger.

Prevention checklist

• Remember that RDS allocated storage only grows.
• Set MaxAllocatedStorage with reasonable headroom, not unlimited.
• Run heavy one-off jobs away from the production primary.
• Alert on storage autoscaling events.
• Plan shrinks as migrations with rehearsals.

References

• Amazon RDS storage autoscaling — https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIOPS.Autoscaling.html
• Working with RDS DB instance storage — https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIOPS.ModifyingExisting.html
• Amazon RDS Blue/Green Deployments — https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html
• Amazon RDS pricing — https://aws.amazon.com/rds/postgresql/pricing/