#!/bin/sh # Run the SQL experiment in a fresh disposable container, never the deployed DB. set -eu cd "$(dirname "$0")/.." mkdir -p evidence work=$(mktemp -d) container= cleanup() { if [ -n "$container" ]; then docker rm -f "$container" >/dev/null; fi rm -rf "$work" } trap cleanup EXIT container=$(docker run -d --label purpose=lab2-research --memory=512m --cpus=1 -e POSTGRES_HOST_AUTH_METHOD=trust postgres:17.6-alpine) for attempt in $(seq 1 60); do if docker exec "$container" pg_isready -U postgres >/dev/null 2>&1; then break; fi sleep 1 done docker exec "$container" createdb -U postgres messenger docker exec -i "$container" psql -X -v ON_ERROR_STOP=1 -U postgres -d messenger < internal/store/postgres/migrations/001_initial.sql > /dev/null docker cp student-tests/plans.sql "$container:/tmp/plans.sql" >/dev/null { date -u '+UTC: %Y-%m-%dT%H:%M:%SZ' echo 'Hypothesis: composite index improves late-page reads, with additional write cost.' echo '100000 rows, distribution 90/9/1%; three reads and 1000-row rollback writes per index.' echo 'Limits: 1 CPU, 512MiB; single local Docker instance; no concurrent writes or PITR.' docker inspect --format 'Image: {{.Image}}' "$container" git ls-files --cached --others --exclude-standard cmd internal student-tests/research.sql student-tests/plans.sql scripts/research.sh | sort | while read -r file; do shasum -a 256 "$file"; done docker exec -i "$container" psql -XAt -v ON_ERROR_STOP=1 -U postgres -d messenger < student-tests/research.sql } > evidence/sql-research-helm.txt # Use the actual schema, all table contents, and a fresh target database. query="SELECT count(*),md5(string_agg(id||':'||chat_id||':'||sender_id||':'||text||':'||created_at,',' ORDER BY id)) FROM messages" docker exec "$container" psql -XAt -U postgres -d messenger -c "$query" > "$work/before" { time docker exec "$container" pg_dump -U postgres -d messenger --no-owner --no-acl > "$work/dump.sql"; } 2>> evidence/sql-research-helm.txt docker exec "$container" createdb -U postgres restored { time docker exec -i "$container" psql -X -v ON_ERROR_STOP=1 -U postgres -d restored < "$work/dump.sql" > /dev/null; } 2>> evidence/sql-research-helm.txt docker exec "$container" psql -XAt -U postgres -d restored -c "$query" > "$work/after" diff -u "$work/before" "$work/after" { echo 'PASS: restore row count and checksum match' cat "$work/after" wc -c < "$work/dump.sql" docker exec "$container" psql -X -U postgres -d restored -c "SELECT id,text FROM messages WHERE id IN(md5('1')::uuid,md5('50000')::uuid,md5('100000')::uuid) ORDER BY id" } >> evidence/sql-research-helm.txt echo 'PASS: evidence/sql-research-helm.txt'