UUID benchmark driver script

run_test_uuid.sh

#!/bin/bash

INIT_TABLE_ROWS=20000000
PGBENCH_TRANSACTIONS_PER_CLIENT=100000
PGBENCH_CLIENTS=10

run_test() {
  echo "$(date +%y%m%d.%H%M) STARTING TEST $1"

  pg_stop
  pg_start

  /usr/bin/time psql -Xc "
  create table records (
     id $1 primary key
    ,foreign_table_id $2
    ,data bigint
  );
  "

  /usr/bin/time psql -Xc "create index on records(data);"

  /usr/bin/time psql -Xc "insert into records(data) select random()*$INIT_TABLE_ROWS from generate_series(1, $INIT_TABLE_ROWS) s;"

  /usr/bin/time psql -Xc "vacuum analyze records;"
  /usr/bin/time psql -Xc "checkpoint;"
  date

  echo "
    \set search_data random(1,$INIT_TABLE_ROWS)
    insert into records(data) values(random()*$INIT_TABLE_ROWS);
    select * from records where data=:search_data;
  " >txn.sql
  pgbench --no-vacuum --transactions=$PGBENCH_TRANSACTIONS_PER_CLIENT --client=$PGBENCH_CLIENTS --file=txn.sql --progress=5 --report-per-command

  echo "$(date +%y%m%d.%H%M) FINISH TEST $1"
}

run_test "bigint generated by default as identity (cache 20)"   "bigint default random()*$INIT_TABLE_ROWS"
run_test "text default gen_random_uuid()"   "text default gen_random_uuid()"
run_test "uuid default gen_random_uuid()"   "uuid default gen_random_uuid()"
run_test "uuid default uuidv7()"   "uuid default uuidv7()"

run_test "bigint generated by default as identity (cache 20)"   "bigint default random()*$INIT_TABLE_ROWS"
run_test "text default gen_random_uuid()"   "text default gen_random_uuid()"
run_test "uuid default gen_random_uuid()"   "uuid default gen_random_uuid()"
run_test "uuid default uuidv7()"   "uuid default uuidv7()"

run_test "bigint generated by default as identity (cache 20)"   "bigint default random()*$INIT_TABLE_ROWS"
run_test "text default gen_random_uuid()"   "text default gen_random_uuid()"
run_test "uuid default gen_random_uuid()"   "uuid default gen_random_uuid()"
run_test "uuid default uuidv7()"   "uuid default uuidv7()"