Node.js pg Pool slow on second execute

Question

I've run into a strange issue that I can't seem to get right.

I'm using pg pooling in a Node.js app and it runs really quickly on the first execute and then on the second one it is dramatically slower (30/50secs).

I've read through the documentation on the GitHub page https://github.com/brianc/node-postgres/wiki/Example

Which seems to require the use of a global variable in order to not create multiple pools which I've tried to do as follows.

I have my main server file bellow

var express = require("express");
var path = require("path");
var app = express();
var port = 3000;

app.use("/public", express.static(process.cwd() + "/public"));

app.set("views", process.cwd() + "/public/views");
app.set("view engine", "ejs");

var login_route = require("./public/logic/login/login_route.js");
app.use(login_route);

app.listen(port, function() {

   var message = "Server Started: Port " + port;
   console.log(message);

});

Which is using a route file which does the database query

var express = require("express");
var router = express.Router();

var pg = require("pg");

var credentials = {
   host: "127.0.0.1",
   port: "5432",
   database: "altaltalt",
   user: "postgres",
   password: "postgres",
   max: 100,
   idleTimeoutMillis: 30000
};

var pool = new pg.Pool(credentials);

// this bit of code is executed
// when a button is clicked
router.get("/exec", function(req, res) {

   pool.connect (
      function(error, client, done) {

         if(error) {
            console.log("connection failed");
         }

         client.query(

            "select * from dummytable",
             function(error, result) {

               done();

               if(error) {
                  console.log(error);
               }

               alert(result.rows);

             }

         );

      }
   );

});

module.exports = router;

When I make a get request to that route it executes really quickly once the button is pressed but any time it is pressed after that is when it becomes terribly slow.

Am I missing something in the code? Is there an additional step to handle multiple requests?

In the past I had similar problems, I got crazy using this library (node-postgres). Then I discover [pg-promise][1] which does all the stuff of closing clients for you and is based in promises. My life changed. [1]: http://github.com/vitaly-t/pg-promise — Jose Hermosilla Rodrigo, Apr 11 '17 at 13:20
@Trent moving into [pg-promise](https://github.com/vitaly-t/pg-promise) will get rid you of all the headache with connection pools and their management. — vitaly-t, Apr 11 '17 at 15:46

score 1 · Accepted Answer · answered Apr 11 '17 at 13:27

1

alert() doesn't exist in Node. Also, when you accept an HTTP request, you should send back a response. Lastly, if you want to run just one query, you can use pool.query().

All together:

router.get('/exec', function(req, res) {
  pool.query('select * from dummytable', function(error, result) {
    if (error) {
      console.log('query failed');
      return res.sendStatus(500);
    }
    res.send(result.rows);
  });
});

And using pool.connect():

router.get('/exec', function(req, res) {
  pool.connect(function(error, client, done) {
    if (error) {
      console.log('connection failed');
      return res.sendStatus(500);
    }

    client.query('select * from dummytable', function(error, result) {
      done(error);
      if (error) {
        console.log('query failed');
        return res.sendStatus(500);
      }
      res.send(result.rows);
    });
  });
});

answered Apr 11 '17 at 13:27

robertklep

198,204
35
394
381

Hello @robertklep, I'm using node-webpack which is why I'm using alerts. Also I'm not sure why but as soon as I added the res.send(result.rows) bit at the end there it started working fine, not 100% sure why that fixed it – TheLovelySausage Apr 11 '17 at 14:02
1

@Trent `alert()` is client-side (browser) code, but your code is meant to run server-side (Node). – robertklep Apr 11 '17 at 14:03
I only use alert for debugging, I remove it once the bugs are fixed – TheLovelySausage Apr 11 '17 at 14:07
1

@Trent is it your intention that this code is going to be running inside a browser? – robertklep Apr 11 '17 at 14:08
It's going to run inside node-webkit which I believe uses a built in browser on a local machine so I think that sort of qualifies yes – TheLovelySausage Apr 11 '17 at 14:10
1

@Trent fair enough, if `node-webkit` actually defines `alert()` then I guess it's okay to use. – robertklep Apr 11 '17 at 14:11
node-webkit allows alert but I never leave alerts in the code because they seem very intrusive – TheLovelySausage Apr 11 '17 at 14:12
On a side note which is a bit more relative to your answer, are there any cons to using pool.query over pool.connect? – TheLovelySausage Apr 11 '17 at 14:14
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/141440/discussion-between-trent-and-robertklep). – TheLovelySausage Apr 11 '17 at 14:14

Node.js pg Pool slow on second execute

1 Answers1