How to configure multiple RDS instance (100+) alerts in cloud watch for CPU utilization, Free Storage space etc?

We have done exactly this using terraform for instances created or not created via terraform. We also use terraform to perform the configuration of all of our pager duty environment.

The best you can do is script alarm creation by looping through each RDS instance and then create each alarm while naming each alarm accordingly.

The alarm action should be to sent to SNS topic. Of which PagerDuty is subscribed via https

I would write a bash script or use IAC and loop through each instance and create the alarm.

Write it once over perhaps 5-10 lines of code for this to create all your alarms.

The SNS payload is sent to the PagerDuty service where the details of which instance triggered the alarm is visible in the incident.

Is it a one-off or are you creating new RDS instances regularly?

As a one-off, you could simply loop on existing metrics in the RDS namespace and create alarms on them. You could do that from the CLI or a Lambda, using the list metrics API to listmetrics in the RDS namespace and putmetricalarm to create the alarms.

If you create RDS instances regularly:

• if you create them from infrastructure as code, e.g. Terraform or CloudFormation, is there a reason that prevents you from creating the alarms from the same stacks?
• if you don't control the creation, you can listen to events that indicate new RDS instances creation and trigger a lambda on those events, implement the alarm creation in the lambda