node-postgres bench query slow

[zhang-wenchao]

Version: Deno 2.0.0

https://github.com/brianc/node-postgres/issues/2225

After extensive discussions, a more important library, hoping to improve the priority to start investigating the cause.

[bartlomieju]

Pasting a link to reproducer that is slow: https://github.com/brianc/node-postgres/issues/2225#issuecomment-2408316215

[bartlomieju]

@zhang-wenchao just to clarify - how many rows does your Users table contain and I assume you are using a remote database and not one on localhost, is that correct?

[zhang-wenchao]

I did encounter this problem. Whether it is local or remote, Deno has a magnitude of performance gaps than Node and Bun.Wait, I submit a Benchmark.

[bartlomieju]

FYI I can see difference on localhost with a table with 1 row. Just trying to get more info to get the same results as you to dig more.

[kamikazechaser]

I was able to reporoduce @morpig 's simple e2e benchmark with an updated query that doesn't rely on a seeded db. Environment is Linux.

import { Buffer } from "node:buffer";

import pg from 'pg'
const { Client } = pg
const client = new Client({
    connectionString: 'postgresql://postgres:postgres@127.0.0.1:5432/postgres'
})
await client.connect()

async function fetch() {
    const startPerf = performance.now();
    const res = await client.query(`select
        $1::int as int,
        $2 as string,
        $3::timestamp with time zone as timestamp,
        $4 as null,
        $5::bool as boolean,
        $6::bytea as bytea,
        $7::jsonb as json
      `, [
        1337,
        'wat',
        new Date().toISOString(),
        null,
        false,
        Buffer.from('awesome'),
        JSON.stringify([{ some: 'json' }, { array: 'object' }])
    ])
    console.log(`${new Date()}: ${Math.round(performance.now() - startPerf)}ms`)
}

setInterval(() => {
    fetch()
}, 1000)

[bartlomieju]

Thanks for a hint @kamikazechaser, but for me all three runtimes take 2ms to execute the query - tried both localhost and 127.0.0.1 to see if there's any difference.

@zhang-wenchao did you have a chance to do the benchmark?

[kamikazechaser]

@bartlomieju I did around 10 runs, all consistent with the timings above. What OS/environment are you on?

[bartlomieju]

I'm on macOS. @littledivy says that they can reproduce on linux.