nodejs + postgresql way too slow

Question

I have this piece of code:

var pg = require('pg');
var QueryStream = require('pg-query-stream');
var constr = 'postgres://devel:[email protected]/tcc';
var JSONStream = require('JSONStream');
var http = require('http');

pg.connect(constr, function(err, client, done) {
    if (err) {
        console.log('Erro ao conectar cliente.', err);
        process.exit(1);
    }

    sql = 'SELECT \
          pessoa.cod, \
          pessoa.nome, \
          pessoa.nasc, \
          cidade.nome AS cidade \
          FROM pessoa, cidade \
          WHERE cidade.cod IN (1, 2, 3);';

    http.createServer(function (req, resp) {
        resp.writeHead(200, { 'Content-Type': 'text/html; Charset=UTF-8' });
        var query = new QueryStream(sql);
        var stream = client.query(query);

        //stream.on('data', console.log);
        stream.on('end', function() {
            //done();
            resp.end()
        });
        stream.pipe(JSONStream.stringify()).pipe(resp);
    }).listen(8080, 'localhost');
});

When I run apache bench on it, it get only about four requests per second. If I run the same query in php/apache or java/tomcat I get ten times faster results. The database has 1000 rows. If I limit the query to about ten rows, then node is double faster than php/java.

What am I doing wrong?

EDIT: Some time ago I opened an issue here: https://github.com/brianc/node-postgres/issues/653

I'm providing this link because I posted there some other variations on the code I have tried. Even with comments and hints so far, I have not been able to get a descent speed.

Are all of your clients sharing the same connection to the database? If that is the case, your requests may be serialized. I am not sure this code properly utilizes the connection pool. — Brandon
– Brandon, Commented Oct 1, 2014 at 1:57
@Brandon, yes, they are. I also tried this example from node-postgres documentation, but the results were about the same. — Fernando Basso
– Fernando Basso, Commented Oct 1, 2014 at 11:38
@FernandoBasso, first of all, i don't really use any kind of sql database, but from what i read about querystream, it only keeps a low number of rows in memory. so i think the bottleneck is querystream, you could try doing it without querystream — freeforall tousez
– freeforall tousez, Commented Nov 8, 2014 at 21:41
Yes, you should do a benchmark without streaming because with PHP/apache you do not stream the query result I guess. — XCS
– XCS, Commented Nov 12, 2014 at 23:13
Just out of curiosity - you are not joining the 2 tables. Is that intentional? — Jayadevan
– Jayadevan, Commented Nov 14, 2014 at 13:47

dnozay · Accepted Answer · 2017-06-13 16:05:32Z

6

pg-query-stream uses cursors.
it uses cursors (bold for emphasis).
you can read the code and change batchSize to better fit your needs.

For those who don't know what cursors are, in short they are a trade-off for keeping memory footprint small and not reading a whole table in memory. But if you get 100 rows at a time when you have 1000 results, that's 1000 / 100 round-trips; so probably 10x slower than a solution not using cursors.

If you know how many rows you need, add a limit to your query, and change the number of rows returned each time to minimize number of roundtrips.

edited Jun 13, 2017 at 16:05

answered Nov 14, 2014 at 7:32

dnozay

24.4k6 gold badges85 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dnozay Over a year ago

@giladmayani, are you saying that part of the answer is rude? Can you please detail what part? I will fix it.

gilad905 Over a year ago

I find the part where you repeat "it uses cursors" 2 extra times to be rude, sounds sarcastic.

dnozay Over a year ago

You are mistaking emphasis for rudeness / sarcasm; but I tweaked the answer.

Witold Szczerba · Accepted Answer · 2016-01-24 22:41:19Z

1

As far as I can tell from this code, you create a single one connection to the PostgreSQL and everything gets queued through it.

The pg module allows for this, it's described here: https://github.com/brianc/node-postgres/wiki/Queryqueue

If you want a real performance, the for each HTTP request you should fetch the connection from the pool, use it, release it and make 101% sure you always release (e.g. proper exception handling) or your server will die once the pool gets completely exhausted.

Once you are there you can tweak the connection pool parameters and measure performance.

answered Jan 24, 2016 at 22:41

Witold Szczerba

1216 bronze badges

Comments

Stephen Punwasi · Accepted Answer · 2014-11-08 20:50:38Z

0

Looks like you're waiting for the server to be created before the request gets relayed. Try moving http.createServer outside of the call. If you only want to use the http server in the request, you should try making the calls async.

answered Nov 8, 2014 at 20:50

Stephen Punwasi

4764 silver badges14 bronze badges

3 Comments

freeforall tousez Over a year ago

nodejs is asynchronous iirc, also, moving the http.createServer outside the pg.connect would break it cause it use the variable name client which is gotten from pg.connect

Stephen Punwasi Over a year ago

@freeforalltousez It's async by default, but code inside of a call needs to use the async package. As of right now the code reads 1) Connect to pg, 2) query, 3) create server, pipe stream.

freeforall tousez Over a year ago

and here i thought the code reads 1) connect to pg, 2) create server and listen to port, 3) query when client (browser) connect to server, pipe stream

huwence · Accepted Answer · 2014-11-13 15:21:28Z

0

Maybe you should set http.agent.maxSockets value, try this:

var http = require('http');
http.agent.maxSockets = {{number}};

default maxSockets is 5

answered Nov 13, 2014 at 15:21

huwence

3261 silver badge8 bronze badges

Collectives™ on Stack Overflow

nodejs + postgresql way too slow

4 Answers 4

3 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related