Driver for PostgreSQL on Node.js

As you know, the speed of any caravan is bounded above by the speed of the slowest camel. In the programming world, we now and then encounter this principle, developing a complex, multi-component system of interacting modules. By optimizing our internal algorithms, we are faced with the limitations of camel drivers that provide us with an interface to third-party services: databases, message queue managers, etc.

Unfortunately, in the node.js community, the current situation is such that the overwhelming majority of drivers for common services have a number of significant flaws that prevent applications from achieving well-deserved heights of efficiency and stability. You have probably heard all these horrific stories about the fact that “node.js is flowing”, it is “toy”, not intended for use in real high-loaded environment. However, as we have seen from our own experience, wisely written software for the node brilliantly copes with all the ordeals of harsh combat realities. And here we come to the main question: what interferes with the average node.js driver normal operation? A separate article can be devoted to a full-scale review of these obstacles, however, now we can single out the most typical signs of a problem product:
')

1. sluggishness (lack of consideration of v8 features)
2. incorrect processing of incoming chunks (lack of accounting for the effects of tcp segmentation)
3. memory leaks (event handlers and their timely cleaning)
4. lack of a faylover and other useful buns.

One of the first we needed was a driver for the PosgreSQL database. Having experimented with the available solutions, we came to the conclusion that it would be wiser to write our own driver. Later, faced with the need to interact more with a large number of services, we inevitably returned to the same solution.

So, we wrote our multithreaded PosgreSQL driver on c +, which implemented the failover mechanism. When the connection to the server breaks, we no longer think about whether requests will be lost. Our driver stores them in RAM and resumes requests when the connection is restored.

When designing the interface, we were guided by the principle “nothing superfluous and only in business”.
In order to execute the query, you must first initialize the connection pool. This is done very simply using the pg.init function, to which you need to transfer the number of simultaneous connections and connection settings:

pg.init(20, { 'user': 'postgres', 'dbname': 'postgres', 'hostaddr': '127.0.0.1', 'password': '123' }); 

Then you can execute a query and process the result:

 var query = "SELECT 1 AS value"; pg.exec(query, function(table) { console.log('Result table:', table); }, console.error); 

You can also execute a pre-prepared query:

 var preparedQuery = "SELECT $word1 AS word1, $word2 AS word2"; pg.execPrepared(preparedQuery, { 'word1': 'hello', 'word2': 'world' }, function(table) { console.log('Result table:', table); }, console.error); 

After all the necessary actions have been completed, you can break the connection using the driver by simply calling the pg.destroy () function;
As you can see, everything is very simple and transparent. You can, without spending a lot of time getting acquainted with the driver interface, to start developing.

We also wrote speed comparison tests and compared our driver with one of the most popular ones, namely node-postgres. We made requests for data packets of 1 byte and 1 KB and compared the elapsed time and memory. The results below are presented on a semi-log scale:


As can be seen from the graph, after 100,000 requests, the waiting time for the result is rapidly increasing. 500,000 queries have to wait more than 25 minutes! Our driver processes such a number of requests in half a minute.

As can be seen from the graph below, when the number of requests is 10,000 - 100,000, the difference in processing time is significant.


The situation is similar with a data packet of 1K. The results below are presented on a semi-log scale.




We also compared the memory consumption results. As can be seen from the graphs, here our driver also wins. Further, all graphs are presented on a semi-log scale.




As a result, we developed a simple, fast driver with the ability to failover.
You can install it using npm by running the command in the console:

 npm install livetex-node-pg 

and connecting it in your application:

 var pg = require('livetex-node-pg'); 

You can also create a project on github, play around, suggest your changes:
https://github.com/LiveTex/Node-Pg