pg-adapter-bench.js

// This benchmark performs two tests:
// 1. 10000 queries with single connection
// 2. 10000 queries in parallel with pool size = 10

// Results on my machine

// Single connection:
// 4.39s pg-adapter
// 6.55s pg
// 6.45s pg-native

// Parallel queries:
// 3.64s pg-adapter
// 6.54s pg
// 6.98s pg-native

// My machine:
// Linux 5.10.15-1-MANJARO
// Processors: 8 × Intel® Core™ i7-10510U CPU @ 1.80GHz
// 15,5 GiB of RAM

// Lib versions:
// pg-adapter 1.2.0
// pg 8.5.1
// pg-native 3.0.0

const { Adapter: PgAdapter } = require('pg-adapter')
const { Pool, Client } = require('pg')
const Native = require('pg').native

const config = {
  user: 'postgres',
  host: 'localhost',
  database: 'testdb',
  port: 5432,
}

const sql = 'SELECT * FROM users'

const measure = async (name, fn, times = 1) => {
  const start = process.hrtime()
  for (let i = 0; i < times; i++) await fn()
  const time = process.hrtime(start)
  console.log(`${name}: ${time[0] + time[1] / 1e9}s`)
}

const singleConnection = async () => {
  console.log('-- Single Connection --')
  const pgAdapter = new PgAdapter({
    ...config,
    pool: 1,
    log: false,
  })
  const pg = new Client({ ...config, max: 1 })
  const pgNative = new Native.Client({ ...config, max: 1 })

  const times = 10000

  await pgAdapter.connect()
  await measure('pg-adapter', () => pgAdapter.query(sql), times)
  await pgAdapter.close()

  await pg.connect()
  await measure('pg', () => pg.query(sql), times)
  await pg.end()

  await pgNative.connect()
  await measure('pg-native', () => pgNative.query(sql), times)
  await pgNative.end()
}

const parallelQueries = async () => {
  console.log('-- Parallel Queries --')
  const pgAdapter = new PgAdapter({
    ...config,
    pool: 10,
    log: false,
  })
  const pgPool = new Pool({ ...config, max: 10 })
  const nativePool = new Native.Pool({ ...config, max: 10 })

  const queries = 10000

  await pgAdapter.connect()
  await measure('pg-adapter', async () => {
    const promises = []
    for (let i = 0; i < queries; i++) promises.push(pgAdapter.query(sql))
    await Promise.all(promises)
  })
  await pgAdapter.close()

  await measure('pg', async () => {
    const promises = []
    const pool = async () => {
      const client = await pgPool.connect()
      const result = await client.query(sql)
      client.release()
      return result
    }
    for (let i = 0; i < queries; i++) promises.push(pool())
    await Promise.all(promises)
  })
  await pgPool.end()

  await measure('pg-native', async () => {
    const promises = []
    const nativePoolFn = async () => {
      const client = await nativePool.connect()
      const result = await client.query(sql)
      client.release()
      return result
    }
    for (let i = 0; i < queries; i++) promises.push(nativePoolFn())
    await Promise.all(promises)
  })
  await nativePool.end()
}

const prepare = async () => {
  const pgAdapter = new PgAdapter({
    ...config,
    pool: 1,
  })

  await pgAdapter.exec`
    CREATE OR REPLACE FUNCTION random_string(length integer) RETURNS text AS
    $$
    DECLARE
      chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
      result text := '';
      i integer := 0;
    BEGIN
      IF length < 0 THEN
        raise exception 'Given length cannot be less than 0';
      END IF;
      FOR i in 1..length LOOP
        result := result || chars[1+random()*(array_length(chars, 1)-1)];
      END LOOP;
      return result;
    END;
    $$ language plpgsql;
  `

  try {
    await pgAdapter.exec`
        CREATE TABLE IF NOT EXISTS users
        (
            id         serial PRIMARY KEY,
            first_name text,
            last_name  text,
            email      text,
            about      text,
            birthdate  date,
            created_at timestamp,
            updated_at timestamp
        );
    `
  } catch (err) {
    //noop
  }

  if ((await pgAdapter.value('SELECT count(*) FROM users')) === 0) {
    await pgAdapter.exec`
      INSERT INTO users(first_name, last_name, email, about, birthdate, created_at, updated_at)
      SELECT
        random_string(15),
        random_string(15),
        random_string(15),
        random_string(100),
        now() - ' 1 year '::INTERVAL * round(random() * 100),
        now(),
        now()
      FROM generate_series(1, 100) AS g(id)
    `
  }

  await pgAdapter.close()
}

const main = async () => {
  await prepare()
  await singleConnection()
  await parallelQueries()
}

main()