Skip to content

Instantly share code, notes, and snippets.

@kmoppel

kmoppel/timescale_vs_native_analytics.sh

Created August 18, 2024 22:57
Show Gist options
  • Save kmoppel/3f5ad9101cf15ad0678482460ad650db to your computer and use it in GitHub Desktop.
Save kmoppel/3f5ad9101cf15ad0678482460ad650db to your computer and use it in GitHub Desktop.
Download ZIP
Raw
timescale_vs_native_analytics.sh
#!/bin/bash
set -exuo pipefail
export PGPORT=5432
export PGDATABASE=postgres
export PGHOST=/var/run/postgresql
export PGUSER=postgres
ROWS=2e8
TEST_QUERY_PG="select count(*) from (select bid, avg(abalance) from pgbench_accounts group by bid) x;"
TEST_QUERY_TS="select count(*) from (select bid, avg(abalance) from pgbench_accounts_ts group by bid) x;"
TRANSACTIONS=20
psql -Xc "create extension if not exists timescaledb" -c "create extension if not exists pg_stat_statements"
psql -Xc "select pg_stat_statements_reset()"
pgbench -iq -I dt
psql -Xc "alter table pgbench_accounts add created_on timestamptz not null;"
SQL_INS=$(cat <<- EOF
insert into pgbench_accounts(aid,bid,abalance,filler,created_on)
select aid, (aid - 1) / 100000 + 1, aid % 100000, '', '2024-01-01'::date + aid * '1millisecond'::interval
from generate_series(1, $ROWS) as aid;
EOF
)
echo "$SQL_INS" | psql
psql -Xc "vacuum analyze pgbench_accounts;"
psql -Xc "\dt+ pgbench_accounts"
echo -e "\n\n\n*** POSTGRES NATIVE ***\n\n\n"
echo "$TEST_QUERY_PG" | pgbench -n -f- -t $TRANSACTIONS
echo -e "\n\n\n*** TIMESCALE COMPRESSED ***\n\n\n"
psql -Xc "drop table if exists pgbench_accounts_ts"
psql -Xc "create table pgbench_accounts_ts (like pgbench_accounts);"
psql -Xc "SELECT create_hypertable('pgbench_accounts_ts', by_range('created_on', INTERVAL '1 hour'));"
psql -Xc "insert into pgbench_accounts_ts select * from pgbench_accounts"
psql -Xc "alter table pgbench_accounts_ts set ( timescaledb.compress ) ;"
psql -qXAt -c "select format(\$\$ SELECT compress_chunk('%s'); \$\$, c) from show_chunks( 'pgbench_accounts_ts' ) c" | psql
psql -Xc "select pg_size_pretty(hypertable_size('pgbench_accounts_ts')) hypertable_size;"
echo "$TEST_QUERY_TS" | pgbench -n -f- -t $TRANSACTIONS
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment