A script for measuring the performance of sql queries with the generation of a report in the form of a MarkDown file.

pgbench.sh

#!/usr/bin/env bash

if [ -z "$PGHOST" ]; then
    export PGHOST=localhost
fi

if [ -z "$PGPORT" ]; then
    export PGPORT=5432
fi

if [ -z "$PGUSER" ]; then
    export PGUSER=postgres
fi

if [ -z "$PGPASSWORD" ]; then
    export PGPASSWORD=postgres
fi

if [ -z "$PGDATABASE" ]; then
    export PGDATABASE=postgres
fi

if [ -z "$PGB_FLAGS" ]; then
    export PGB_FLAGS='--time 30 --client 10 --jobs 10 --no-vacuum'
fi

if [ -z "$PGB_COMMENT" ]; then
    export PGB_COMMENT=''
fi

if [ -z "$PGB_SUITE_DIR" ]; then
    export PGB_SUITE_DIR='suite'
fi

if [ -z "$PGB_NO_VACUUM" ]; then
    # Vacuum be skipped if empty PGB_NO_VACUUM.
    export PGB_NO_VACUUM=''
fi

readonly PGHOST PGPORT PGUSER PGPASSWORD PGDATABASE PGB_FLAGS PGB_COMMENT PGB_SUITE_DIR PGB_NO_VACUUM

__HEADER() {
    echo "# The benchmark process has been started: $(date)"
    echo
    echo "_It is most convenient to view this report in the Markdown editor._"
    echo
    echo "**Host from which the launch benchmark:**"
    echo
    uname -a
    echo
    echo "**Comment:**"
    echo
    echo "${PGB_COMMENT}"
    echo
}

__PRESET() {
    echo "## Dump info about aggregated statistics by database:"
    echo
    echo '```'
    psql --echo-queries --host $PGHOST --port $PGPORT --username $PGUSER <<EndOfMessage
SET search_path TO device_inventory,devices,public;
--;
SELECT COUNT(*)
FROM devices;
EndOfMessage
    echo '```'
    echo
}

__MAIN() {
    echo "## Starting the benchmark process"

    local BENCHMARK_NUMBER=1

    for SQL_FILE in "$PGB_SUITE_DIR"/*.sql; do
        echo
        echo "### Starting the benchmark #${BENCHMARK_NUMBER}"
        echo
        echo "#### ${BENCHMARK_NUMBER}.1 Start the vacuum cleaning:"
        local COMMAND="vacuumdb --host $PGHOST --port $PGPORT --username $PGUSER --analyze $PGDATABASE"
        echo
        echo '```'
        echo "${COMMAND}"
        echo '```'
        echo
        echo '```'
        [ -z "$PGB_NO_VACUUM" ] && ${COMMAND} 2>&1 || echo "Vacuum skipped because set env PGB_NO_VACUUM=${PGB_NO_VACUUM}"
        echo '```'
        echo
        echo "#### ${BENCHMARK_NUMBER}.2 The following SQL query was complete from a file: '$SQL_FILE'"
        echo
        echo '```'
        cat "$SQL_FILE"
        echo '```'
        echo
        echo "#### ${BENCHMARK_NUMBER}.3 To run the benchmark, the following command was used:"
        echo
        local COMMAND="pgbench --host $PGHOST --port $PGPORT --username $PGUSER $PGB_FLAGS -f $SQL_FILE"
        echo
        echo '```'
        echo "${COMMAND}"
        echo '```'
        echo "#### ${BENCHMARK_NUMBER}.4 Results the benchmark:"
        echo
        echo '```'
        $COMMAND 2>&1
        echo '```'
        echo
        ((BENCHMARK_NUMBER++))
    done
}

__DUMP() {
    echo
    echo "## This benchmark generated by this source code:"
    echo
    echo '```'
    cat "$0"
    echo '```'
}

__AVERAGE() {
    local AVERAGES_FILE=$(mktemp)
    local TRANSACTIONS_FILE=$(mktemp)
    readonly AVERAGES_FILE TRANSACTIONS_FILE
    cat "$1" | grep 'latency average = ' >"$AVERAGES_FILE"
    ls "$PGB_SUITE_DIR" >"$TRANSACTIONS_FILE"
    echo
    echo "## A quick summary of the benchmark results:"
    echo
    echo '```'
    paste "$AVERAGES_FILE" "$TRANSACTIONS_FILE"
    echo '```'
    echo
}

__REPORT() {
    local REPORT="pgbench-report-$(date -u '+%Y%m%dT%H%M%S%z').md"
    local MAIN_FILE="$(mktemp)"
    readonly MAIN_FILE REPORT

    __MAIN | tee "$MAIN_FILE" 1>&2

    __HEADER >"$REPORT"
    __AVERAGE "$MAIN_FILE" >>"$REPORT"
    __PRESET >>"$REPORT"
    cat "$MAIN_FILE" >>"$REPORT"
    __DUMP >>"$REPORT"
}

__REPORT