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

[ghost]

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

[jfirebaugh]

We were seeing a lot of time spent in the ClientRead state on pg 7.18.2, but it mostly disappeared after upgrading to 8.5.1. I don't know if there were specific fixes targeted at this -- if so, thanks! In any case, the upgrade seems to have resolved the issue.

[ohjeyong]

For me, it is completely solved by upgrading to 8.5.1

[bfelbo]

Does anyone know if there's still a substantial performance difference between pg and pg-native on the latest version?

[gajus]

OP: No, there is not. We have since switched back to using JavaScript driver and have scaled our program a lot more. pg driver itself was never the bottleneck.

[bfelbo]

That’s really useful to know, thanks!

What made you switch back? Are there any benefits to using the JS driver if you don’t use any of the advanced features?

[gajus]

I cannot recall the specifics, but we did hit several issues with the native driver and debugging them was always a mystery. JavaScript driver has a lot more maintainers and has since released many patches.

[mgagliardo91]

Any update on this?

[valentinoPereira]

Should we continue using the pgNative or switch to the non-native javascript driver? What are the use cases for the existence both of them?

[gajus]

pg-native has not been updated for 2 years. Safe to say it is not in active development.

[brianc]

I'm coming back to work on node-postgres full time in about a month. Been building a startup and now I'm done w/ that & going to spend 3-6 months (at least) just doing open source. I know it doesn't solve the immediate need but hope you find some encouragement in that I intend to update pg-native along with many other things 🔜 . In the mean time....I'm sorry 😢

Also, not sure why the client read issue...seems like it might be gone? Happy to dive into that next week as well.

[rishi-raj-jain]

Does anyone know if there's still a substantial performance difference between pg and pg-native on the latest version?

I still see it. Switching to pg-native just magically improves noticeable perf.

[melroy89]

pg-native has not been updated for 2 years. Safe to say it is not in active development.

Not true.. https://www.npmjs.com/package/pg-native latest update is 1 month ago.