forked from mtusi2026/mtusi2026lab2
192 lines
7.4 KiB
Bash
Executable File
192 lines
7.4 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
# Track 2: compare the two supporting indexes on one skewed dataset.
|
|
# Exactly one of chat_members_user_idx / messages_chat_created_id_idx is
|
|
# present in the two comparison states; both are restored at the end.
|
|
set -euo pipefail
|
|
cd "$(dirname "$0")/.."
|
|
|
|
set -a
|
|
. ./.env
|
|
set +a
|
|
|
|
DB=messenger_research
|
|
SIZE=${SIZE:-300000}
|
|
CHAT_COUNT=${CHAT_COUNT:-5000}
|
|
USER_COUNT=${USER_COUNT:-2000}
|
|
RESULTS=research/results/indexes.md
|
|
COMPOSE=(docker compose --env-file .env)
|
|
ZERO=00000000-0000-0000-0000-000000000000
|
|
mkdir -p research/results
|
|
|
|
psqlq() {
|
|
"${COMPOSE[@]}" exec -T postgres psql -X -q -A -t \
|
|
-U "$POSTGRES_USER" -d "$DB" -c "$1"
|
|
}
|
|
|
|
psqlf() {
|
|
"${COMPOSE[@]}" exec -T postgres psql -X -q \
|
|
-U "$POSTGRES_USER" -d "$DB" -v ON_ERROR_STOP=1 "$@"
|
|
}
|
|
|
|
echo "Rebuilding $DB at $SIZE messages..."
|
|
"${COMPOSE[@]}" exec -T postgres psql -X -q -U "$POSTGRES_USER" -d postgres \
|
|
-v ON_ERROR_STOP=1 \
|
|
-c "DROP DATABASE IF EXISTS $DB WITH (FORCE);" \
|
|
-c "CREATE DATABASE $DB;" >/dev/null
|
|
psqlf -f - < app/internal/database/migrations/0001_init.up.sql >/dev/null
|
|
psqlq "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
|
|
psqlf -v target_messages="$SIZE" -v chat_count="$CHAT_COUNT" \
|
|
-v user_count="$USER_COUNT" -f - < research/seed.sql >/dev/null
|
|
|
|
# All three queries are built from the API's access patterns:
|
|
# user_chats - list the chats of one user
|
|
# chat_messages - keyset page of one chat
|
|
# user_feed - messages from all of one user's chats, keyset page
|
|
# The feed cursor is recent so this is a realistic page, not a full-history
|
|
# sort; the query still joins every message to the user's memberships.
|
|
Q_USER_CHATS="SELECT c.id, c.title, c.created_at
|
|
FROM chats c
|
|
JOIN chat_members cm ON cm.chat_id = c.id
|
|
WHERE cm.user_id = research_user_uuid(1)
|
|
ORDER BY c.created_at, c.id
|
|
LIMIT 50;"
|
|
|
|
Q_CHAT_MESSAGES="SELECT id, chat_id, sender_id, text, created_at
|
|
FROM messages
|
|
WHERE chat_id = research_chat_uuid(1)
|
|
AND (created_at, id) > ('2026-02-15T00:00:00Z'::timestamptz, '$ZERO'::uuid)
|
|
ORDER BY created_at, id
|
|
LIMIT 51;"
|
|
|
|
Q_USER_FEED="SELECT m.id, m.chat_id, m.sender_id, m.text, m.created_at
|
|
FROM messages m
|
|
JOIN chat_members cm ON cm.chat_id = m.chat_id
|
|
WHERE cm.user_id = research_user_uuid(1)
|
|
AND (m.created_at, m.id) > ('2026-03-01T00:00:00Z'::timestamptz, '$ZERO'::uuid)
|
|
ORDER BY m.created_at, m.id
|
|
LIMIT 50;"
|
|
|
|
ensure_both() {
|
|
psqlq "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
|
|
}
|
|
|
|
messages_only() {
|
|
psqlq "CREATE INDEX IF NOT EXISTS messages_chat_created_id_idx ON messages (chat_id, created_at, id);
|
|
DROP INDEX IF EXISTS chat_members_user_idx;" >/dev/null
|
|
}
|
|
|
|
membership_only() {
|
|
psqlq "CREATE INDEX IF NOT EXISTS chat_members_user_idx ON chat_members (user_id, chat_id);
|
|
DROP INDEX IF EXISTS messages_chat_created_id_idx;" >/dev/null
|
|
}
|
|
|
|
# AGENT: the comparison is only meaningful if exactly one index is present;
|
|
# a leftover second index would hide the regression this experiment measures.
|
|
assert_single() {
|
|
local want_membership=$1 want_messages=$2
|
|
local count has_membership has_messages
|
|
count=$(psqlq "SELECT count(*) FROM pg_indexes
|
|
WHERE indexname IN ('chat_members_user_idx', 'messages_chat_created_id_idx');")
|
|
has_membership=$(psqlq "SELECT to_regclass('chat_members_user_idx') IS NOT NULL;")
|
|
has_messages=$(psqlq "SELECT to_regclass('messages_chat_created_id_idx') IS NOT NULL;")
|
|
if [ "$count" != "1" ] || [ "$has_membership" != "$want_membership" ] || [ "$has_messages" != "$want_messages" ]; then
|
|
echo "index state is not controlled: count=$count membership=$has_membership messages=$has_messages" >&2
|
|
exit 1
|
|
fi
|
|
}
|
|
|
|
assert_both() {
|
|
local count
|
|
count=$(psqlq "SELECT count(*) FROM pg_indexes
|
|
WHERE indexname IN ('chat_members_user_idx', 'messages_chat_created_id_idx');")
|
|
[ "$count" = "2" ] || { echo "both indexes were not restored" >&2; exit 1; }
|
|
}
|
|
|
|
bench_query() {
|
|
local label=$1 sql=$2
|
|
psqlq "EXPLAIN (ANALYZE, BUFFERS) $sql" >/dev/null
|
|
local times=()
|
|
local i t
|
|
for i in 1 2 3 4 5; do
|
|
t=$(psqlq "EXPLAIN (ANALYZE, BUFFERS) $sql" |
|
|
sed -n 's/.*Execution Time: \([0-9.]*\) ms/\1/p')
|
|
times+=("$t")
|
|
done
|
|
local median plan
|
|
median=$(printf '%s\n' "${times[@]}" | sort -n |
|
|
awk '{a[NR]=$1} END {print a[int((NR + 1) / 2)]}')
|
|
plan=$(psqlq "EXPLAIN (ANALYZE, BUFFERS) $sql" |
|
|
grep -oE '(Index Only Scan|Index Scan|Seq Scan|Bitmap Heap Scan|Sort|Hash Join|Nested Loop|Merge Join)' |
|
|
awk '{if ($0 != prev) print; prev = $0}' |
|
|
paste -sd '>' -)
|
|
printf '| %s | %s | %s |\n' "$label" "$median" "$plan"
|
|
}
|
|
|
|
bench_write() {
|
|
psqlq "DROP TABLE IF EXISTS research_write_bench;
|
|
CREATE TABLE research_write_bench (
|
|
id uuid PRIMARY KEY,
|
|
chat_id uuid NOT NULL,
|
|
sender_id uuid NOT NULL,
|
|
text text NOT NULL,
|
|
created_at timestamptz NOT NULL
|
|
);" >/dev/null
|
|
if [ "$1" = "yes" ]; then
|
|
psqlq "CREATE INDEX research_write_bench_idx
|
|
ON research_write_bench (chat_id, created_at, id);" >/dev/null
|
|
fi
|
|
"${COMPOSE[@]}" cp research/write.sql postgres:/tmp/research_write.sql >/dev/null
|
|
local tps
|
|
tps=$("${COMPOSE[@]}" exec -T postgres pgbench -n -c 4 -j 2 -T 10 \
|
|
-U "$POSTGRES_USER" -d "$DB" -f /tmp/research_write.sql 2>&1 |
|
|
sed -n 's/^tps = \([0-9.]*\).*/\1/p')
|
|
psqlq "DROP TABLE research_write_bench;" >/dev/null
|
|
echo "$tps"
|
|
}
|
|
|
|
{
|
|
echo "# Index experiment"
|
|
echo
|
|
echo "Dataset: $SIZE messages, $CHAT_COUNT chats, $USER_COUNT users."
|
|
echo "Median of 5 warm-cache runs; plan is the sequence of access nodes."
|
|
echo
|
|
echo "| state | query | median ms | plan |"
|
|
echo "| --- | --- | ---: | --- |"
|
|
|
|
ensure_both
|
|
assert_both
|
|
bench_query "both | user_chats" "$Q_USER_CHATS"
|
|
bench_query "both | chat_messages" "$Q_CHAT_MESSAGES"
|
|
bench_query "both | user_feed" "$Q_USER_FEED"
|
|
write_with=$(bench_write yes)
|
|
|
|
messages_only
|
|
assert_single f t
|
|
bench_query "messages_only | user_chats" "$Q_USER_CHATS"
|
|
bench_query "messages_only | chat_messages" "$Q_CHAT_MESSAGES"
|
|
bench_query "messages_only | user_feed" "$Q_USER_FEED"
|
|
|
|
membership_only
|
|
assert_single t f
|
|
bench_query "membership_only | user_chats" "$Q_USER_CHATS"
|
|
bench_query "membership_only | chat_messages" "$Q_CHAT_MESSAGES"
|
|
bench_query "membership_only | user_feed" "$Q_USER_FEED"
|
|
write_without=$(bench_write no)
|
|
|
|
ensure_both
|
|
assert_both
|
|
|
|
echo
|
|
echo "Index sizes after restoring both:"
|
|
psqlq "SELECT 'chat_members_user_idx=' || pg_size_pretty(pg_relation_size('chat_members_user_idx')) ||
|
|
', messages_chat_created_id_idx=' || pg_size_pretty(pg_relation_size('messages_chat_created_id_idx')) ||
|
|
', messages_table=' || pg_size_pretty(pg_total_relation_size('messages'));"
|
|
echo
|
|
echo "pgbench message-like inserts, 4 clients, 10 s: with messages index ${write_with} tps; without ${write_without} tps."
|
|
echo "The research DB is left with both indexes."
|
|
} | tee "$RESULTS"
|
|
|
|
echo "Wrote $RESULTS"
|