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.