0

I tried to create a system which changes the number of replicas in Postgres based on system metrics - latency, I/O operations, read/write frequency and CPU usage. I know that those metrics are available in pg_stat_* tables (for example pg_stat_statement or pg_stat_replication).

At the beginning I had a plan to periodically check those metrics (by querying them) and then based on same thresholds make a decision to add/remove a replica (or even replicas). But then I found out that I can use triggers for the purpose and do it "asynchronously" (instead of polling).

But as far as I see those "metrics" tables in Postgres (and probably in other databases too) are views and because of that I cannot create a trigger on them. Am I right? And if so - what do you recommend to solve this issue? Go back to my solution with polling data from pg_stat?

Improve this question
0

1 Answer 1

Reset to default
0

Right, these are views based on functions, so you cannot define triggers on them. But then defining a trigger on a catalog table is unsupported and a bad idea to begin with: for one, it will probably break pg_upgrade.

So I recommend polling the statistics views at regular intervals.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.