Files

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"