Is the pg-native recommendation outdated?

[sunaurus]

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the issue has not already been raised

Issue

In the README, there is a section about pg-native support which states the following:

If you want maximum performance you can install pg-native, and pass native: true to the plugin options.

Is this claim of maximum performance still true in 2023? Does it still make sense to recommend pg-native to users?

My own findings:

According to this benchmark, the difference in performance is minimal, and actually js pg beats pg-native in 3 out of 4 tests. I've also heard anecdotal evidence from several engineers that they saw no difference in real life performance when switching between pg and pg-native.

On the other hand, there is a clear downside to using pg-native: setup can be a real pain.

Unless there's some evidence that pg-native is indeed still better, I propose changing the README to reflect the current situation, so users won't think they need pg-native for good performance.

[climba03003]

Is this claim of maximum performance still true in 2023?

Can you run the benchmark to see if the line still capable.
I have no postgres installed machine and setting it up in Windows or via Docker (in Windows) is disaster.

I would like to see a newly run benchmark before any action.

[sunaurus]

My environment is like this:

M1 Max Macbook
node: v18.13.0
db: postgres (PostgreSQL) 14.7 (Homebrew)

[email protected]
[email protected]
[email protected]

The benchmark runs 10,000 queries with each client, and compares the the differences in elapsed time.

On my machine, I consistently see [email protected] being 3-4x faster than everything else, no matter how many times I run the benchmark. Between pg and pg-native it's less clear, on some runs, pg comes ahead by 1.2-1.4x, on other runs, pg-native wins by about the same amount. Crucially, the elapsed time differences between them are <80ms. This could just be considered margin of error for most use cases - 80ms per 10k queries is practically nothing.

For me personally this result makes it clear - I can ignore pg-native and on the off chance that I ever need something faster, I can consider postgres instead of pg.

Meanwhile, I think it's also important to consider this quote from the postgres-benchmarks repo:

NB. In daily usage it is very likely that this difference doesn't matter as much since the time spent by the client library is negligable compared to the query time itself.

In my experience, this is 100% true.

[danilofuchs]

On Windows, the difference is still significant:

Some simple queries using pg:

connect: 0.127ms
nodes: 48.078ms
measurements c1be3833-5e26-46ca-a8cc-62ed4eeee440}: 49.955ms
measurements f345dfbe-3181-47d8-a6b5-2ca721830d9a}: 99.679ms
measurements 8ef8dd90-b061-43f5-b63c-fd97476b18c0}: 149.827ms
measurements 092594db-6d9c-4e28-a2f3-995d5546cfd6}: 199.876ms
measurements 8e1f25f4-9292-4f58-9e4e-6b1d6d10ce6a}: 249.66ms
measurements: 250.141ms

The same queries now with pg-native:

connect: 0.224ms
nodes: 1.03ms
measurements c1be3833-5e26-46ca-a8cc-62ed4eeee440}: 0.675ms
measurements f345dfbe-3181-47d8-a6b5-2ca721830d9a}: 1.363ms
measurements 8ef8dd90-b061-43f5-b63c-fd97476b18c0}: 1.946ms
measurements 092594db-6d9c-4e28-a2f3-995d5546cfd6}: 2.563ms
measurements 8e1f25f4-9292-4f58-9e4e-6b1d6d10ce6a}: 3.349ms
measurements: 3.654ms

Node v18.16.1 (LTS)
Windows 11
pg 8.10.0
pg-native 3.0.1
postgresql 15

[mcollina]

Would you like to send a PR to drop that recommendation?

[StarpTech]

Why not recommend https://github.com/porsager/postgres ? Which does not rely on native modules and claims to be the fastest in town.

[Uzlopak]

Is it API compatible?
Do you have benchmarks supporting the performance claim?

[climba03003]

Is it API compatible?

No, postgres is really out-of-scope here.
And pg-native seems to be better performance in certain machine compared to pg.