Massive performance difference between pg and pg-native

[gajus]

I have been trying to get to the bottom of this issue #1952 and along the way I have discovered that the latter issue is not present when using pg-native. However, what I also discovered is that there is massive performance difference between pg and pg-native. Without any change to the workload, and only by changing pg with pg-native, my database CPU usage dropped from an avg. 70% to an avg. 35%.

I suggest that it would be made more clear in the documentation that there is a considerable performance difference between using pg and pg-native. Switching from pg to pg-native will allow us to downgrade our server and save a significant amount of money.

In terms of what changes, as far as I can tell, the biggest difference is how much time is spent in ClientRead state. pg-native compared to pg spends a lot less time in ClientRead state. This appears to imply slow (?) parsing or some other client-side issue handling the response/ sending parameter values.

[gajus]

Here is what a switch from pg-native to pg looks over an hour's use.

[vitaly-t]

This would depend a lot on type, size and frequency of data being exchanged, and even Node.js version. For example, if you are exchanging a lot of binary objects, it may have a significant impact. It would be difficult to generalize for documentation. And for an average use case the performance difference is negligible.

[gajus]

90%+ of the queries are simple primary key lookups returning couple of columns, e.g.

SELECT id FROM country WHERE code_alpha_2 = $1

I would think that this is the case for most applications.

There is no binary data being read/ written.

[vitaly-t]

And what is the average number of rows returned in that test? If it is too many, the JavaScript version may simply be eating more memory due to more native JavaScript serialization calls between garbage collections.

The latter would depend a lot on the Node.js version, and can also be profiled via Node.js parameters.

[gajus]

Looking at the top queries in the pg_stat_statements, most of them return 1 or none rows.

maybeOne and maybeOneFirst are the most used query methods.

Are there any real downsides to using pg-native apart from:

Some of the modules using advanced features of PostgreSQL such as pg-query-stream, pg-cursor,and pg-copy-streams need to operate directly on the binary stream and therefore are incompatible with the native bindings.

?

[vitaly-t]

Interesting. If what you are telling is accurate, then it contradicts great many tests conducted here by many developers, including myself.

There may be something special in how or where your app runs that manifests such a performance discrepancy.

Are there any real downsides to using pg-native apart from...

It can be a real installation pain sometimes 😄 Other than that, NO.

[gajus]

Is there a benchmark suite I can run against my infrastructure? Happy to contribute whatever helps to identify the underlying cause.

It can be a real installation pain sometimes 😄 Other than that, NO.

Wasn't an issue using a standard Docker node image.

[murukesh]

@gajus @brianc @vitaly-t I am experiencing the same problem with node-postgres driver too. Posted it here : #1952 (comment)

[brianc]

I think I might have a fix - would you be willing to try it out by running a branch of this? I'd like to confirm it before releasing if possible. It shouldn't negatively impact things. I noticed about 200 queries per second difference on a small quick benchmark I wrote locally.

The difference being I'm testing against a local postgres instance...my hunch is it might be faster when testing against an instance over a longer/slower network.

[murukesh]

please share the branch

[brianc]

@murukesh #2031

Would you be able to pull that down & see if it helps? I have a couple other ideas too...I'm experimenting w/ stuff over here

Hi,
How did you config pg-native and pg?
Could you Plz, share your configs? @gajus

[tinder-ydong]

Hi,
How did you config pg-native and pg?
Could you Plz, share your configs? @gajus

add pg-native to package.json as a dependency, then add this to your environment variables
https://github.com/brianc/node-postgres/blob/master/packages/pg/lib/index.js#L33