Monitoring the growth of individual postgres tables

Question

We have an RDS db that contains multiple large postgres tables. Need to monitor how fast the tables grow (how many rows are inserted each day, and total size). We've been doing it manually by running SQL-queries, but want to make an automated report.

As a beginner (who's only used RDS & IAM) I suspect that I should use Lambda to run the SQL, and set up metrics in CloudWatch. Could use some general advice & pointers to get started. In example: Is there a suitable Lambda function blueprint I can use, or would I need to author one from scratch? Should I start by making a new dashboard in CloudWatch?

If someone could suggest a brief step-by-step list of what I'd need to do, that'd be very helpful.

Answer

If you were using Aurora Postgres instead of RDS Postgres, you could have used Database Activity Streams - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/DBActivityStreams.html

It sends all database activity including DDL, DML and DQL commands to a Kinesis Data Stream. You can take a look at this blog to see how to use the Activity Streams data for further processing.

Cloudwatch metrics for RDS does not offer anything like your requirement - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-metrics.html

You can possibly look at building custom metrics if you want to go the Cloudwatch metrics route - https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/publishingMetrics.html

Monitoring the growth of individual postgres tables

相關內容