#!/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"