2

This may sound exotic, by I would like to programmatically know if it is a 'good moment' to execute a heavy-write PL/PGSQL function in a server. By 'good moment' I mean pondering some direct or calculated indicator of the load level, concurrency level, or any other magnitude of the PostgreSQL server.

I am aware there is a number of advanced applications specialized in performance tracking out there, like https://www.datadoghq.com. But I just want a simple internal KPI that alters or delays the execution of these heavy-write procedures until a 'better moment' comes.

Some of these procedures purge tables, some make average/sum calculations over millions of rows, some check remote tables, etc. They may wait for minutes or hours for a 'better moment' when the concurrent user pressure comes down.

Any idea?

coterobarros
  • 941
  • 1
  • 16
  • 25

1 Answers1

3

You can see how many other sessions are active by something like:

select count(*) from pg_stat_activity where state='active';

But you have to be a superuser, or have the pg_monitor role, or else state will be NULL for sessions of other users. If that bothers you, you could write a function with SECURITY DEFINER to allow access to this info. (You should probably be putting this into its own function anyway, which means there is no reason that it needs to be implemented in plpgsql unless that is the only language available to you.)

You can also invoke any arbitrary OS operations by using a suitably privileged pl language. That includes from plpgsql, by abusing COPY...FROM PROGRAM.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • More at https://stackoverflow.com/questions/47336725/measuring-load-per-database-in-postgres-using-active-processes-in-pg-stat-acti – coterobarros Aug 16 '20 at 18:18