Performance: pg VS postgres.js VS Bun.SQL

[cesco69]

TL;TR: pg seems fast!

Hi, I've made a small benchmark to compare this library 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";
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, pg 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 pg 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 Rust! 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' })
    ]);
});

pg 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)

[spaiz]

Actually, I'm benchmarking few days 3 packages in production on real data, with non-trivial raw sql:

1. Prisma

2. node-postgres

3. Postgres.js

4. Prisma still slowest, but not too much. They did a good progress in performance improvements.

5. node-postgresq vs Postgresql.js - actually I don't really see any difference in performance.

I follow not only the number of Postman benchmark, but also from logs of execution time of each query, and also from tracing of DataDog.

The only question now is - either I want to deal with $q, $2 (I hate it) or use ${var} (which I love, but I kind of not fun of Postgresql.js API :/

We use Prisma in general... but I must have max performance...

[cesco69]

@spaiz if you want use literal string as query try https://www.npmjs.com/package/sql-template-tag

import sql from "sql-template-tag";
import pg from "pg";

const id = 10;

pg.query( sql`SELECT * FROM books WHERE id = ${id}` );

[spaiz]

@cesco69 Yeah, spent half a day for that. It uses esm, I'm using commonjs on node 20... at the end, finally successfully adopted it. Needed to upgrade to nodejs 22 which supports commonjs and esm modules to be used together.

[brianc]

Thanks for the repor on this! I really appreciate it. This is why it's always good to be suspect of benchmarks and do your own! While its tempting to publish these results in some official capacity I'm not sure about that yet...but I'll keep it in mind & if so, I'll tag ya on the PR to review the docs. :)

[nikelborm]

Also did those benchmarks account fot heating the functions? v8 does some optimizations and so first N runs of a certain function can be slower than the same amount of runs right after. So it seems best to either run every test as individual process, or let all of them warmup in the beginning, and then actually measure their perfomance.

[nigrosimone]

Postgres.js claim to be the "Fastest" and link a this benchmark https://github.com/porsager/postgres-benchmarks#results , last update of 4 years ago!!!! Also the benchmark is totally wrong because Postgres.js use prepared statements by default, whereas pg requires setting the name parameter AND don't expose the GC of node, this can also cause random slowdown due to random execution of the GC!

While its tempting to publish these results in some official capacity I'm not sure about that yet...

it would be more honest if Postgres.js claim to be "Faster" and not "Fastest"

Side note: "prepared statements by default" It looks like a feature to win in benchmarks,
in the real world, with dynamic queries, connections being closed and reopened, it can only be a big performance loss