Files

113 lines
5.1 KiB
Bash
Executable File

#!/usr/bin/env bash
# Track 2: dump/restore timing at 100k, 200k and 300k messages.
# The schema comes from the repository migration; both indexes are present
# for every dump. The research DB is left at 300k with both indexes.
set -euo pipefail
cd "$(dirname "$0")/.."
set -a
. ./.env
set +a
DB=messenger_research
RESTORE_DB=messenger_research_restore
DUMP=/tmp/research_recovery.dump
RESULTS=research/results/recovery.csv
COMPOSE=(docker compose --env-file .env)
mkdir -p research/results
psql_db() {
local db=$1 sql=$2
"${COMPOSE[@]}" exec -T postgres psql -X -q -A -t \
-U "$POSTGRES_USER" -d "$db" -c "$sql"
}
psql_admin() {
"${COMPOSE[@]}" exec -T postgres psql -X -q -A -t \
-U "$POSTGRES_USER" -d postgres -v ON_ERROR_STOP=1 -c "$1"
}
measure() {
local command=$1
# AGENT: /usr/bin/time runs inside the container so the client-side
# docker exec overhead is not counted as backup or restore time.
if ! "${COMPOSE[@]}" exec -T postgres sh -c \
"/usr/bin/time -f '%e' -o /tmp/research_time $command >/dev/null 2>&1"; then
echo "timed command failed: $command" >&2
exit 1
fi
"${COMPOSE[@]}" exec -T postgres cat /tmp/research_time
}
ensure_both_indexes() {
psql_db "$DB" "CREATE INDEX IF NOT EXISTS chat_members_user_idx
ON chat_members (user_id, chat_id);
CREATE INDEX IF NOT EXISTS messages_chat_created_id_idx
ON messages (chat_id, created_at, id);" >/dev/null
}
printf 'messages,dump_s,restore_s,dump_bytes,db_bytes,table_bytes,index_bytes\n' > "$RESULTS"
for n in 100000 200000 300000; do
echo "Rebuilding $DB at $n messages..."
psql_admin "DROP DATABASE IF EXISTS $RESTORE_DB WITH (FORCE);" >/dev/null
psql_admin "DROP DATABASE IF EXISTS $DB WITH (FORCE);" >/dev/null
psql_admin "CREATE DATABASE $DB;" >/dev/null
"${COMPOSE[@]}" exec -T postgres psql -X -q -U "$POSTGRES_USER" -d "$DB" \
-v ON_ERROR_STOP=1 -f - < app/internal/database/migrations/0001_init.up.sql >/dev/null
psql_db "$DB" "CREATE TABLE IF NOT EXISTS schema_migrations
(version bigint NOT NULL PRIMARY KEY, dirty boolean NOT NULL);
INSERT INTO schema_migrations VALUES (1, false)
ON CONFLICT DO NOTHING;" >/dev/null
"${COMPOSE[@]}" exec -T postgres psql -X -q -U "$POSTGRES_USER" -d "$DB" \
-v ON_ERROR_STOP=1 -v target_messages="$n" -v chat_count=5000 \
-v user_count=2000 -f - < research/seed.sql >/dev/null
ensure_both_indexes
db_bytes=$(psql_db "$DB" "SELECT pg_database_size(current_database());")
table_bytes=$(psql_db "$DB" "SELECT pg_total_relation_size('messages');")
index_bytes=$(psql_db "$DB" "SELECT pg_relation_size('chat_members_user_idx') +
pg_relation_size('messages_chat_created_id_idx');")
dump_s=$(measure "pg_dump -U $POSTGRES_USER -d $DB -Fc -f $DUMP")
dump_bytes=$("${COMPOSE[@]}" exec -T postgres stat -c %s "$DUMP")
psql_admin "CREATE DATABASE $RESTORE_DB;" >/dev/null
restore_s=$(measure "pg_restore -U $POSTGRES_USER -d $RESTORE_DB --no-owner --no-privileges $DUMP")
src_count=$(psql_db "$DB" "SELECT count(*) FROM messages;")
dst_count=$(psql_db "$RESTORE_DB" "SELECT count(*) FROM messages;")
src_members=$(psql_db "$DB" "SELECT count(*) FROM chat_members;")
dst_members=$(psql_db "$RESTORE_DB" "SELECT count(*) FROM chat_members;")
# AGENT: one aggregate fingerprint covers every message row, so the check
# is not limited to whichever row happened to be newest.
src_fingerprint=$(psql_db "$DB" "SELECT md5(string_agg(
id::text || '|' || chat_id::text || '|' || sender_id::text || '|' ||
text || '|' || created_at::text, E'\n' ORDER BY id)) FROM messages;")
dst_fingerprint=$(psql_db "$RESTORE_DB" "SELECT md5(string_agg(
id::text || '|' || chat_id::text || '|' || sender_id::text || '|' ||
text || '|' || created_at::text, E'\n' ORDER BY id)) FROM messages;")
dst_indexes=$(psql_db "$RESTORE_DB" "SELECT count(*) FROM pg_indexes
WHERE indexname IN ('chat_members_user_idx',
'messages_chat_created_id_idx');")
if [ "$src_count" != "$dst_count" ] || [ "$src_members" != "$dst_members" ] ||
[ "$src_fingerprint" != "$dst_fingerprint" ] || [ "$dst_indexes" != "2" ]; then
echo "restore verification failed at $n rows" >&2
exit 1
fi
printf '%s,%s,%s,%s,%s,%s,%s\n' \
"$n" "$dump_s" "$restore_s" "$dump_bytes" "$db_bytes" "$table_bytes" "$index_bytes" \
>> "$RESULTS"
echo " dump ${dump_s}s, restore ${restore_s}s, ${dump_bytes} bytes; verified"
psql_admin "DROP DATABASE $RESTORE_DB WITH (FORCE);" >/dev/null
"${COMPOSE[@]}" exec -T postgres rm -f "$DUMP"
done
ensure_both_indexes
echo
echo "Recovery results (CSV: messages,dump_s,restore_s,dump_bytes,db_bytes,table_bytes,index_bytes):"
cat "$RESULTS"
echo
echo "Final research DB: $(psql_db "$DB" "SELECT count(*) FROM messages;") messages, both indexes present."