Small benchmark tool for db drop, create, insert and select *

node-postgres-benchmark.js

const { Pool } = require('pg')
const debug = require('debug')('a')

const defaultParams = {
  database: 'postgres',
  user: 'myuser',
  password: 'mysecretpassword',
  host: 'localhost',
  port: 5432
}

const pool = new Pool(defaultParams)
const avgDrop = []
const avgCreate = []
const avgInsert = []
const avgSelect = []
const avgRowCmp = []

function setData (length, width) {
  let data = []
  let col

  for (let i = 0; i < width; i++) {
    col = []
    for (let j = 0; j < length; j++) {
      col.push('' + i)
    }
    data.push(col)
  }

  return data
}

function test (size) {
  let nowCreate
  let nowInsert
  let nowCmp
  let nowSelect

  const values = setData(size, 10)

  let columnDefinitions = ''
  let columnNames = ''
  let columnValuesPlaceholders = ''

  for (let i = 0; i < values.length; i++) {
    columnDefinitions += `Col${i + 1} text, `
    columnNames += `Col${i + 1}, `
    columnValuesPlaceholders += `$${i + 1}::text[], `
  }
  columnDefinitions = columnDefinitions.slice(0, -2)
  columnNames = columnNames.slice(0, -2)
  columnValuesPlaceholders = columnValuesPlaceholders.slice(0, -2)

  const nowDrop = Date.now()
  return pool.query(`DROP TABLE IF EXISTS yolo`)
  .catch((err) => { console.error(err) })
  .then(() => {
    avgDrop.push(Date.now() - nowDrop)
  })
  .then(() => {
    nowCreate = Date.now()
    return pool.query(`CREATE TABLE yolo (${columnDefinitions})`)
  })
  .then(() => {
    avgCreate.push(Date.now() - nowCreate)

    // Use arrays: https://github.com/brianc/node-postgres/issues/957#issuecomment-295583050
    nowInsert = Date.now()
    return pool.query({
      text: `INSERT INTO yolo (${columnNames}) SELECT * FROM UNNEST (${columnValuesPlaceholders})`,
      values
    })
  })
  .then(() => {
    avgInsert.push(Date.now() - nowInsert)

    nowSelect = Date.now()
    return pool.query({text: `SELECT * FROM yolo`, rowMode: 'array'})
  })
  .then(({rows}) => {
    avgSelect.push(Date.now() - nowSelect)

    nowCmp = Date.now()

    if (rows.length !== values[0].length) {
      console.log('different length', rows.length, values[0].length)
      return false
    }
    // compare rows
    for (let i = 0; i < rows.length; i++) {
      for (let j = 0; j < rows[i].length; j++) {
        if (rows[i][j] !== values[j][i]) {
          return false
        }
      }
    }
    return true
  })
  .then((ok) => {
    if (!ok) console.log('not ok')
    avgRowCmp.push(Date.now() - nowCmp)
    /*
    debug(`${size} rows: drop ${avgDrop[avgDrop.length - 1]}ms \
| create ${avgCreate[avgCreate.length - 1]}ms \
| insert ${avgInsert[avgInsert.length - 1]}ms \
| select ${avgSelect[avgSelect.length - 1]}ms \
| compare ${avgRowCmp[avgRowCmp.length - 1]}ms`)
    */
  })
}

let promise = Promise.resolve()

function stats () {
  debug('avgDrop', avgDrop.reduce((a, b) => a + b) / avgDrop.length)
  debug('avgCreate', avgCreate.reduce((a, b) => a + b) / avgCreate.length)
  debug('avgInsert', avgInsert.reduce((a, b) => a + b) / avgInsert.length)
  debug('avgSelect', avgSelect.reduce((a, b) => a + b) / avgSelect.length)
  debug('avgRowCmp', avgRowCmp.reduce((a, b) => a + b) / avgRowCmp.length)
  debug('done')
}

for (let i = 1; i < 1000; i += 10) {
  const j = i
  promise = promise.then(() => test(j))
}

promise.then(() => stats())

for (let i = 1000; i < 10000; i += 100) {
  const j = i
  promise = promise.then(() => test(j))
}

promise.then(() => stats())

for (let i = 10000; i < 100000; i += 10000) {
  const j = i
  promise = promise.then(() => test(j))
}

promise.then(() => stats())

for (let i = 100000; i < 1000000; i += 100000) {
  const j = i
  promise = promise.then(() => test(j))
}

promise.then(() => stats())