Repository: brianc/node-postgres (language: javascript)

Issue:
nodejs v18.17.1 / v20.9.0 - pg ^8.11.0 - slow select query 
I am using the following query to retrieve users from database, and before this one i just perform a count(*) to retrieve the total number of users.
```
SELECT coalesce(json_agg(_0_users),'[]') as users 
        FROM (
          SELECT row_to_json((
            SELECT _0_users
            FROM ( SELECT _0_users.id,_0_users.email,_0_users.password,_0_users.name,_0_users.last_name,_0_users.created_at,_0_users.verified,_0_users.blacklist,_0_users.profile_image,_0_users.role_id,_0_users.country_id,_0_users.city,_0_users.phone,_0_users.address,_0_users.postal_code,_0_users.is_business,_0_users.business_name,_0_users.business_address,_0_users.business_country_id,_0_users.vat,_0_users.parent_id) _0_users )) _0_users
            FROM (
              SELECT  _0_users.id,_0_users.email,_0_users.password,_0_users.name,_0_users.last_name,_0_users.created_at,_0_users.verified,_0_users.blacklist,_0_users.profile_image,_0_users.role_id,_0_users.country_id,_0_users.city,_0_users.phone,_0_users.address,_0_users.postal_code,_0_users.is_business,_0_users.business_name,_0_users.business_address,_0_users.business_country_id,_0_users.vat,_0_users.parent_id FROM public.users _0_users
       ) _0_users
       ) _0_users
```
The execution with nodejs v18.17.1 / v20.9.0 - pg ^8.11.0  results to something between 60 and 92 ms for both count and the query mentioned above, but the same process with nodejs v16.20.2  results to 12ms for both queries (average).
Are there any issues with node-postgres and nodejs versions > 16? Am I missing something?
       


Discussion / hints:
Cannot reproduce it on different machines,except windows 11 for unknown reasons
Very strange! Sounds like more of an issue w/ something internal to node on v18/20 on windows. Please lmk if you track down what it is!
Found this when troubleshooting my ORM problems. 

Indeed, for me on Windows 11 and with Postgres running in a Docker container in WSL, basic queries on node 16 take like 2ms, and on node 22 - 40ms. Specifically, I get these results when running Kysely and Drizzle ORM with pg driver. 

Though there are caveats. 
1. On node 22, Kysely and Drizzle ORM with `postgres-js` driver work as fast as `node-pg` on node 16 does.
2. Mikro-ORM, which uses `node-pg` under the hood (https://github.com/mikro-orm/mikro-orm/blob/master/packages/postgresql/package.json) also works fast
do raw queries executed through this library w/o an orm wrapper take a long time? only on windows w/ node 22? 🤔 
Should've checked it before, I guess.

When raw queries are fired from `node-postgres` directly on node 22, the execution time is actually appropriate.

Kysely and Drizzle ORM are slow on nodes 22, 20, 18, (I only have Windows 11 machines) and are sufficiently fast on 16. (I didn't check the odd-numbered ones)

But since `node-postgres` itself is fast when doing raw sql, I guess the issue elsewhere. Still it's weird that both Kysely and Drizzle display this thing where node 16 is the last version with which they use the driver effectively. And that aligned with the original author's issue
it could have something to do w/ not using a pool or something internally. not sure, but I know it takes orders of magnittude longer to connect & handshake w/ postgres than to do a query (hence we all use connection pools) so maybe they aren't? Really no idea, but I'm glad to know the speed issue is w/ a 3rd party lib and not this one! 😅 
Ok, I actually reproduced the issue on node-postgres. 

The issue is with parameterized queries. The library is fast when it fires just raw strings, but is slow when using parameters like $1, $2, etc. 
Agreed, it is unique to ~~parameterized queries~~ prepared queries.

Isolated repro: https://gist.github.com/codyebberson/513b69b07d841869a319c49d4b25cf05

On Windows 11 

```
C:\Users\Cody\dev\database-perf-test>node index.js
Raw SQL:
Average query time: 0.39 ms
Parameterized
Average query time: 51.35 ms
```

On WSL on same machine:

```
$ node index.js
Raw SQL:
Average query time: 0.18 ms
Parameterized
Average query time: 0.18 ms
```

The repro is very consistently in between these two steps:

1. Final step of the send:
   1. `Client.query`
   2. `Client._pulseQueryQueue`
   3. `Query.submit`
   4. `Query.prepare`
   5. `Query._getRows`
   6. `Connection.sync`
   7. `Connection._send`
2. First step of the receive:
   1. `stream.on('data', ...)` in `packages/pg-protocol/src/index.ts`

When running on Mac, Linux, or WSL, there is essentially zero delay between the final `Connection._send` and `stream.on('data', ...)`, just the time to run the query, usually sub millisecond.

When running on Windows 11, there is a consistent 40-50ms delay between `Connection._send` and `stream.on('data', ...)`.

Another clue:
* When I use the IP address of the WSL guest, the problem goes away (i.e., "172.17.19.157")
* The problem only appears when using "localhost" or "127.0.01"

Things I have tried that do ***not*** fix the issue:

* Disabling ipv6, forcing ipv4
* Use `new net.Socket({ highWaterMark: 64 * 1024 })`
* Use `stream.setNoDelay(true)`
* Use `dns.setDefaultResultOrder('ipv4first')`
* Use `NODE_OPTIONS=--dns-result-order=ipv4first`
* Use `UV_TCP_KEEPALIVE=1`
* `/etc/wsl.conf`
   *  `networkingMode=mirrored`
   * `generateResolvConf=false`
* `/etc/sysctl.conf`
   * `net.ipv4.tcp_sack=0`
   * `net.ipv4.tcp_timestamps=0  # Disable TCP timestamps`
   * `net.ipv4.tcp_wmem=4096 65536 16777216`
   * `net.ipv4.tcp_rmem=4096 65536 16777216`
   * `net.ipv4.tcp_slow_start_after_idle=0`
   * `net.ipv4.tcp_fastopen=3`
   * `net.core.wmem_max=16777216`
   * `net.core.rmem_max=16777216`

~~Probably~~ Tested and confirmed fixed with https://github.com/brianc/node-postgres/pull/3340

Fix patch:
diff --git a/packages/pg/bench.js b/packages/pg/bench.js
index 223b10278..8f966c581 100644
--- a/packages/pg/bench.js
+++ b/packages/pg/bench.js
@@ -47,21 +47,27 @@ const run = async () => {
   for (let i = 0; i < 4; i++) {
     let queries = await bench(client, params, seconds * 1000)
     console.log('')
-    console.log('little queries:', queries)
+    console.log('param queries:', queries)
     console.log('qps', queries / seconds)
-    console.log('on my laptop best so far seen 733 qps')
+    console.log('on my laptop best so far seen 987 qps')
+
+    queries = await bench(client, { ...params, name: 'params' }, seconds * 1000)
+    console.log('')
+    console.log('named queries:', queries)
+    console.log('qps', queries / seconds)
+    console.log('on my laptop best so far seen 937 qps')
 
     console.log('')
     queries = await bench(client, seq, seconds * 1000)
     console.log('sequence queries:', queries)
     console.log('qps', queries / seconds)
-    console.log('on my laptop best so far seen 1309 qps')
+    console.log('on my laptop best so far seen 2725 qps')
 
     console.log('')
     queries = await bench(client, insert, seconds * 1000)
     console.log('insert queries:', queries)
     console.log('qps', queries / seconds)
-    console.log('on my laptop best so far seen 6445 qps')
+    console.log('on my laptop best so far seen 27383 qps')
 
     console.log('')
     console.log('Warming up bytea test')
@@ -75,7 +81,7 @@ const run = async () => {
     const time = Date.now() - start
     console.log('bytea time:', time, 'ms')
     console.log('bytea length:', results.rows[0].data.byteLength, 'bytes')
-    console.log('on my laptop best so far seen 1107ms and 104857600 bytes')
+    console.log('on my laptop best so far seen 1407ms and 104857600 bytes')
     await new Promise((resolve) => setTimeout(resolve, 250))
   }
 
diff --git a/packages/pg/lib/query.js b/packages/pg/lib/query.js
index fbef341bf..06b582f6f 100644
--- a/packages/pg/lib/query.js
+++ b/packages/pg/lib/query.js
@@ -161,7 +161,21 @@ class Query extends EventEmitter {
       return new Error('Query values must be an array')
     }
     if (this.requiresPreparation()) {
-      this.prepare(connection)
+      // If we're using the extended query protocol we fire off several separate commands
+      // to the backend. On some versions of node & some operating system versions
+      // the network stack writes each message separately instead of buffering them together
+      // causing the client & network to send more slowly. Corking & uncorking the stream
+      // allows node to buffer up the messages internally before sending them all off at once.
+      // note: we're checking for existence of cork/uncork because some versions of streams
+      // might not have this (cloudflare?)
+      connection.stream.cork && connection.stream.cork()
+      try {
+        this.prepare(connection)
+      } finally {
+        // while unlikely for this.prepare to throw, if it does & we don't uncork this stream
+        // this client becomes unresponsive, so put in finally block "just in case"
+        connection.stream.uncork && connection.stream.uncork()
+      }
     } else {
       connection.query(this.text)
     }


Test patch: