nodejs v18.17.1 / v20.9.0 - pg ^8.11.0 - slow select query

[Dimitris-Tzilopoylos]

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?

[Dimitris-Tzilopoylos]

Cannot reproduce it on different machines,except windows 11 for unknown reasons

[brianc]

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!

[vladkrasn]

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

[brianc]

do raw queries executed through this library w/o an orm wrapper take a long time? only on windows w/ node 22? 🤔

[vladkrasn]

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

[brianc]

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! 😅

[vladkrasn]

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.

[codyebberson]

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

[codyebberson]

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', ...).

[codyebberson]

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"

[codyebberson]

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

[codyebberson]

Probably Tested and confirmed fixed with #3340