-
-
Save fabiolimace/c1a23b1c0b1e8b5c112718163a47670d to your computer and use it in GitHub Desktop.
UUID benchmark driver script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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()" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment