simple_triggers_perf_test.sh

#!/bin/bash

SCALE=100
LOOPS=3
DURATION=7200
PGBENCH_SEED=2018

for loop in $(seq 1 ${LOOPS}) ; do

echo "doing loop $loop ..."

##################
# without triggers

echo "dropping existing tables..."
psql -c "drop table if exists pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers cascade;"

echo "pgbench -i -s $SCALE &>/dev/null"
pgbench -i -s $SCALE &>/dev/null
psql -c "checkpoint" && sleep 60

for x in accounts branches tellers ; do
 	psql -c "ALTER TABLE pgbench_${x} ADD COLUMN last_modified_on timestamptz, ADD COLUMN last_modified_by text;"
done

echo "running without triggers for $DURATION s..."
pgbench -T $DURATION --random-seed=$PGBENCH_SEED &> run_${loop}_no_trg.log


##################
# plpgsql triggers

echo "dropping existing tables..."
psql -c "drop table if exists pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers cascade;"

echo "pgbench -i -s $SCALE &>/dev/null"
pgbench -i -s $SCALE &>/dev/null

psql -c "checkpoint" && sleep 60

TRG_FUNC=$(cat <<HERE
CREATE OR REPLACE FUNCTION trg_last_modified_audit() RETURNS TRIGGER
AS 
\$\$
BEGIN
	IF NEW.last_modified_by IS NULL THEN
		NEW.last_modified_by = session_user;
	END IF;
	IF NEW.last_modified_on IS NULL THEN
		NEW.last_modified_on = current_timestamp;
	END IF;
	RETURN NEW;
end;
\$\$ LANGUAGE plpgsql;
HERE
)

psql -c "$TRG_FUNC"

for x in accounts branches tellers ; do
 	psql -c "ALTER TABLE pgbench_${x} ADD COLUMN last_modified_on timestamptz, ADD COLUMN last_modified_by text;"
	psql -c "CREATE TRIGGER audit BEFORE UPDATE ON pgbench_${x} FOR EACH ROW EXECUTE PROCEDURE trg_last_modified_audit();"
done

echo "running WITH triggers for $DURATION s..."
pgbench -T $DURATION --random-seed=$PGBENCH_SEED &> run_${loop}_plpgsql_trg.log

done