CODEWITHSUNDEEP
amazon-web-servicesamazon-rdsamazon-cloudwatch
Cong Wang
Cong Wang

Reputation: 93

create custom AWS cloudwatch metric with ID from Postgres table

I have an interesting problem I need to resolve. I have a table A in Postgres. This table is treated like a queue which has a set of tasks. ID is incremental id in Postgres.

I want to have a metric to contain current processed position (ID) and the max number of ID. Those two numbers are accumulating every second.

Is there an efficient way to do it ?

The easiest way on top of my head is to execute this SQL query every 10 seconds (varies):

select blablah from table then limit 1 order by asc

to get smallest id and use the same approach to get largest id.

But this command is expensive. Is there any better way to do this ?

Upvotes: 1

Views: 519

Answers (1)

John Hanley
John Hanley

Reputation: 81356

When you insert a new record into the table, return the record ID. When you extract a record do the same. You could cache this in memory, a file, a different DB table, etc. Then run a scheduled task to post these values to CloudWatch as a custom metric.

Example (very simple) SQL statement to return the ID when inserting new records:

INSERT INTO table (name) OUTPUT Inserted.ID VALUES('bob');

Upvotes: 1

Related Questions