Why all node library use postgres.js instead of node-postgres ?

[cesco69]

TL;TR: node-postgres/pg seems fast!

I noticed that every node library into TechEmpower/FrameworkBenchmarks use postgres.js

I've made a small benchmark to compare node-postgres/pg against others that claim to be the fastest: Postgres.js and Bun.SQL.

Inspired by some online benchmarks, here’s the first test:

package.json

{
  "name": "pg-bench",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "bench": "bun --expose-gc index.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "bun": "^1.2.2",
    "mitata": "^1.0.34",
    "pg": "^8.13.3",
    "postgres": "^3.4.5"
  },
  "devDependencies": {
    "@types/bun": "latest"
  },
  "peerDependencies": {
    "typescript": "^5.0.0"
  }
}

index.js

import { SQL } from "bun";
import { Pool } from "pg"; // node-postgres
import postgres from 'postgres'
import { run, bench } from 'mitata';

const bun = new SQL({
    max: 4, 
    hostname: "LOCALHOST",
    port: 5432,
    database: "DB",
    username: "USER",
    password: "PWD",
});

const pg = new Pool({
    max: 4,
    host: 'LOCALHOST',
    port: 5432,
    database: 'DB',
    user: 'USER',
    password: 'PWD',
});

const sql = postgres({
    max: 4,
    host: 'LOCALHOST',
    port: 5432,
    database: 'DB',
    user: 'USER',
    password: 'PWD',
})

bench('bun', async () => {
    await bun`SELECT * FROM information_schema.tables LIMIT 100`;
});

bench('postgres.js', async () => {
    await sql`SELECT * FROM information_schema.tables LIMIT 100`;
});

bench('pg', async () => {
    await pg.query({ text: `SELECT * FROM information_schema.tables LIMIT 100` });
});

await run({
    format: 'mitata',
    colors: true,
    throw: true
});

run with

bun --expose-gc index.js

And here are the results:

At first glance, node-postgres seems very slow, but that’s not actually the case. The reason is that Bun and Postgres.js automatically cache prepared statements by default, whereas node-postgres requires setting the name parameter to enable prepared statement caching. If we update the benchmark like this:

bench('pg', async () => {
    await pg.query({ text: `SELECT * FROM information_schema.tables LIMIT 100`, name: 'foo' });
});

pg actually becomes faster than postgres.js:

pg is still slower than Bun.SQL, but that’s mainly because Bun is written in Zig! Maybe compared to pg-native, Bun would lose, but it's not possible to run this benchmark since pg-native only works on Node.js.

I've also run a benchmark with more test cases:

bench('bun: base', async () => {
    await bun`SELECT * FROM information_schema.tables LIMIT 100`;
});
bench('postgres.js: base', async () => {
    await sql`SELECT * FROM information_schema.tables LIMIT 100`;
});
bench('pg: base', async () => {
    await pg.query({ text: `SELECT * FROM information_schema.tables LIMIT 100`, name: 'foo' });
});


bench('bun: random param', async () => {
    await bun`SELECT *, ${Math.random()} as RND FROM information_schema.tables LIMIT 100`;
});
bench('postgres.js: random param', async () => {
    await sql`SELECT *, ${Math.random()} as RND FROM information_schema.tables LIMIT 100`;
});
bench('pg: random param', async () => {
    await pg.query({ text: 'SELECT *, $1 as RND FROM information_schema.tables LIMIT 100', values: [Math.random()], name: 'foo2'});
});


bench('bun: parallel', async () => {
    await Promise.all([
        bun`SELECT *, 1 as i FROM information_schema.tables LIMIT 100`,
        bun`SELECT *, 2 as i FROM information_schema.tables LIMIT 100`,
    ]);
});
bench('postgres.js: parallel', async () => {
    await Promise.all([
        sql`SELECT *, 1 as i FROM information_schema.tables LIMIT 100`,
        sql`SELECT *, 2 as i FROM information_schema.tables LIMIT 100`,
    ]);
});
bench('pg: parallel', async () => {
    await Promise.all([
        pg.query({ text: 'SELECT *, 1 as i FROM information_schema.tables LIMIT 100', name: 'foo3' }),
        pg.query({ text: 'SELECT *, 2 as i FROM information_schema.tables LIMIT 100', name: 'foo4' })
    ]);
});

node-postgres seem very fast

In the "parallel" bench the gap with Bun increases because Bun has the "pipeline mode" active (but also Postgres.js has it)

[joanhey]

This is an open benchmark, be free to open a PR.

Thank you !