Monitoring Postgres Queries In Nodejs Application Level

Hi All,

In this blog, I'm going to describe how to monitor postgres queries with execution time in postgres.
We have two popular modules pg(node-postgres) and pg-promise (PostgreSQL client) for node.js. Lets start with the pg(node-postgres).
 

pg(node-postgres) is the non-blocking postgres client for nodejs. To install pg module in our nodejs application, use npm install pg, for more click here.

To monitor postgresSQL in pg module, here is a module appmetrics which provides Machine and runtime environment information, Process and system CPU, Process and system memory usage, Event loop latency information, MongoDB queries made by the application, PostgreSQL queries made by the application etc. 

For postgres

 var appmetrics = require('appmetrics');

var monitor = appmetrics.monitor();

monitor.on('postgres', function(response) {
   console.log("SQL Query: "+response.query);
   console.log("Duration: "+response.duration); // This will print execution time(duration) of query. 
});
 

Note: In code, we must require appmetrics before requiring pg module.

Now lets start monitoring pg-promise, pg module provides pg-monitor module.

To use pg-monitor,first install it

npm install pg-monitor

Then use the below code

 const monitor = require('pg-monitor');

var pgOptions = {
	pgNative: true
};

const options = {
	pgNative: true,
	query: e => {
		/* do some of your own processing, if needed */

		//monitor.query(e);  monitor the event;
	},
	error: (err, e) => {
		/* do some of your own processing, if needed */
		//monitor.error(err, e);  monitor the event;
	}
};

monitor.attach(options); // attach to all events at once;

 

But the problem with this module is that it monitors all types of queries but shows duration only for transactional queries. So, this is how we can monitor the PostgresSQL in application level. 

Thanks