Inserts records and runs select queries on a PostgreSQL database to compare the performance of two alternate approaches.

psql_performance.sh

#!/bin/bash

# Amount of inserts and selects
AMT_INSERTS=10000
AMT_SELECTS=1000

# Executes the given statements and prints out the elapsed time
execute_statements() {
  local statements=("$@")
  local tmp=$(mktemp /tmp/psql-performance.XXXXXX)

  printf -- '%s;\n' "${statements[@]}" > $tmp

  local start=$(date +%s.%3N)
  psql -U postgres test -f $tmp > /dev/null
  local end=$(date +%s.%3N)
  rm $tmp

  local elapsed=$(echo "scale=3; $end - $start" | bc)
  local avg=$(echo "scale=6; $elapsed / ${#statements[@]}" | bc)
  echo "Time elapsed: ${elapsed}"
  echo "Average query time: ${avg}"
}

progress() {
  local current=$1
  local total=$2

  local progress=$(( 100 * $current / $total ))
  echo -ne "${current} / ${total} (${progress}%)\r"
}

echo "Creating database 'test' with a table 'performance'"
psql -U postgres -c "CREATE DATABASE test"
psql -U postgres test -c "CREATE TABLE performance(id serial PRIMARY KEY, settings JSONB, some_toggle BOOLEAN)"
psql -U postgres test -c "CREATE INDEX some_toggle_index ON performance (some_toggle)"

boolvals=(TRUE FALSE)

echo "Creating insert statements..."
inserts=()
for i in $(seq 1 $AMT_INSERTS); do
  progress $i $AMT_INSERTS

  bool=${boolvals[$RANDOM % 2]}
  toggle=$(($RANDOM % 2))
  inserts+=("INSERT INTO performance (settings, some_toggle) VALUES ('{\"test_setting\": ${toggle}}', ${bool});")
done
echo ""

echo "Inserting the records to the database..."
execute_statements "${inserts[@]}"

echo "Generating select statements for the JSONB column..."
selects=()
for i in $(seq 1 $AMT_SELECTS); do
  progress $i $AMT_SELECTS

  toggle=$(($RANDOM % 2))
  selects+=("SELECT * FROM performance WHERE (settings->'test_setting')::int = ${toggle} ORDER BY RANDOM()")
done
echo ""

echo "Running the database selects => JSONB"
execute_statements "${selects[@]}"

echo "Generating select statements for the BOOLEAN column..."
selects=()
for i in $(seq 1 $AMT_SELECTS); do
  progress $i $AMT_SELECTS

  bool=${boolvals[$RANDOM % 2]}
  selects+=("SELECT * FROM performance WHERE some_toggle = ${bool} ORDER BY RANDOM()")
done
echo ""

echo "Running the database selects => BOOLEAN"
execute_statements "${selects[@]}"

echo "Dropping database 'test' with a table 'performance'"
psql -U postgres test -c "DROP INDEX some_toggle_index"
psql -U postgres test -c "DROP TABLE performance"
psql -U postgres -c "DROP DATABASE test"