Files
mtusi2026lab2/scripts/track2_experiment.py

95 lines
7.0 KiB
Python

#!/usr/bin/env python3
"""Repeatable track 2 experiment: skewed 100k message history, two indexes, pg_dump/restore.
Run only on the local lab stack after `docker compose up -d`. It adds fixed
synthetic rows to the lab tables and uses disposable benchmark tables and a
separate restore database. Existing application rows and volumes are retained.
"""
import json
import os
import subprocess
import time
import uuid
from pathlib import Path
ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT / "evidence" / "track2-results.json"
DB = os.environ.get("POSTGRES_DB", "messenger")
USER = os.environ.get("POSTGRES_USER", "messenger")
RESTORE_DB = DB + "_restore_check"
def docker(*args, input=None):
return subprocess.run(["docker", "compose", "--env-file", ".env", *args], cwd=ROOT, input=input,
text=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE, check=True).stdout
def sql(statement, database=DB, service="postgres"):
return docker("exec", "-T", service, "psql", "-X", "-v", "ON_ERROR_STOP=1", "-U", USER, "-d", database, "-At", "-c", statement)
def main():
tag = "lab2_2026"
table = "bench_messages_" + tag
# Seed one fixed synthetic user, one 80k-message room and 200 smaller rooms.
# Deterministic UUIDs and ON CONFLICT make reruns idempotent.
sql(f"""
INSERT INTO users(id,username,display_name,password_hash) VALUES (md5('user-{tag}')::uuid,'bench_{tag}','Benchmark user','not-a-login-hash') ON CONFLICT (username) DO NOTHING;
INSERT INTO chats(id,title) SELECT md5('chat-'||g)::uuid, 'Benchmark chat '||g FROM generate_series(0,200) g ON CONFLICT (id) DO NOTHING;
INSERT INTO chat_members(chat_id,user_id) SELECT md5('chat-'||g)::uuid,md5('user-{tag}')::uuid FROM generate_series(0,200) g ON CONFLICT DO NOTHING;
INSERT INTO messages(id,chat_id,sender_id,text,created_at)
SELECT md5('m'||g)::uuid,
CASE WHEN g<=80000 THEN md5('chat-0')::uuid ELSE md5('chat-'||((g-80001)%200+1))::uuid END,
md5('user-{tag}')::uuid,
repeat('x', 40 + (g%160)),
timestamptz '2026-01-01 00:00:00+00' + g * interval '1 millisecond'
FROM generate_series(1,100000) AS g ON CONFLICT (id) DO NOTHING;
DROP TABLE IF EXISTS {table};
CREATE TABLE {table} (LIKE messages INCLUDING DEFAULTS);
INSERT INTO {table}(id,chat_id,sender_id,text,created_at)
SELECT id,chat_id,sender_id,text,created_at FROM messages WHERE sender_id=md5('user-{tag}')::uuid;
ANALYZE {table};
CREATE INDEX {table}_a ON {table}(chat_id,created_at,id);
""")
samples = []
for label, index_sql in [
("A chat,created_at,id", f"CREATE INDEX {table}_a ON {table}(chat_id,created_at,id)"),
("B covering history index", f"CREATE INDEX {table}_b ON {table}(chat_id,created_at DESC,id DESC) INCLUDE(sender_id)")]:
if label.startswith("B"):
sql(f"DROP INDEX IF EXISTS {table}_a; {index_sql}; ANALYZE {table}")
plan = sql(f"EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT id,chat_id,sender_id,text,created_at FROM {table} WHERE chat_id=md5('chat-0')::uuid AND (created_at,id)>(timestamptz '2026-01-01 00:00:40+00',md5('cursor')::uuid) ORDER BY created_at,id LIMIT 50")
parsed = json.loads(plan)[0]["Plan"]
size = sql(f"SELECT pg_size_pretty(pg_relation_size('{table}_{'a' if label.startswith('A') else 'b'}'))")
samples.append({"index":label,"total_runtime_ms":parsed.get("Actual Total Time"),"shared_read_blocks":parsed.get("Shared Read Blocks"),"shared_hit_blocks":parsed.get("Shared Hit Blocks"),"index_size":size.strip(),"plan":parsed})
# Paired write-cost trials start with identical 100k-row tables. Index build
# and seed-copy time are excluded; order alternates to reduce warm-cache bias.
write = []
for trial in range(1, 4):
order = [("A", "a"), ("B", "b")] if trial % 2 else [("B", "b"), ("A", "a")]
for label, idx in order:
target = f"{table}_w{idx}{trial}"
index = (f"CREATE INDEX {target}_idx ON {target}(chat_id,created_at,id)" if idx == "a"
else f"CREATE INDEX {target}_idx ON {target}(chat_id,created_at DESC,id DESC) INCLUDE(sender_id)")
sql(f"CREATE TABLE {target} (LIKE {table} INCLUDING DEFAULTS); INSERT INTO {target} SELECT * FROM {table}; {index}")
write_plan=json.loads(sql(f"EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) INSERT INTO {target}(id,chat_id,sender_id,text,created_at) SELECT md5('write-{tag}-{label}-{trial}-'||g)::uuid, md5('chat-0')::uuid, md5('user-{tag}')::uuid, 'write sample', now()+g*interval '1 microsecond' FROM generate_series(1,10000) g"))[0]
write.append({"trial":trial,"index":label,"rows":10000,"execution_ms":write_plan["Execution Time"],"plan":write_plan["Plan"]})
sql(f"DROP TABLE {target}")
# Restore into a separate PostgreSQL process with its own persistent volume.
docker("--profile", "restore", "up", "-d", "--wait", "postgres_restore")
sql(f"DROP DATABASE IF EXISTS {RESTORE_DB}", database="postgres", service="postgres_restore")
sql(f"CREATE DATABASE {RESTORE_DB} OWNER {USER}", database="postgres", service="postgres_restore")
start=time.monotonic()
dump=subprocess.run(["docker","compose","--env-file",".env","exec","-T","postgres","pg_dump","-Fc","-U",USER,DB],cwd=ROOT,stdout=subprocess.PIPE,stderr=subprocess.PIPE,check=True).stdout
backup_seconds=time.monotonic()-start
start=time.monotonic()
subprocess.run(["docker","compose","--env-file",".env","exec","-T","postgres_restore","pg_restore","-U",USER,"-d",RESTORE_DB,"--no-owner","--no-privileges"],cwd=ROOT,input=dump,stdout=subprocess.PIPE,stderr=subprocess.PIPE,check=True)
restore_seconds=time.monotonic()-start
counts={DB:{name:int(sql(f"SELECT count(*) FROM {name}",database=DB).strip()) for name in ("users","chats","chat_members","messages","sessions")},RESTORE_DB:{name:int(sql(f"SELECT count(*) FROM {name}",database=RESTORE_DB,service="postgres_restore").strip()) for name in ("users","chats","chat_members","messages","sessions")}}
selected=sql(f"SELECT id::text||':'||text FROM messages WHERE sender_id=md5('user-{tag}')::uuid ORDER BY id LIMIT 10")
restored=sql(f"SELECT id::text||':'||text FROM messages WHERE sender_id=md5('user-{tag}')::uuid ORDER BY id LIMIT 10",database=RESTORE_DB,service="postgres_restore")
result={"seed":"100000 messages, fixed seed/tag "+tag,"restore_target":"separate PostgreSQL 16.4 service with its own restore_data volume","index_plans":samples,"write_cost":write,"backup_bytes":len(dump),"backup_seconds":round(backup_seconds,3),"restore_seconds":round(restore_seconds,3),"row_counts":counts,"first_ten_rows_match":selected==restored}
OUT.write_text(json.dumps(result,ensure_ascii=False,indent=2)+"\n",encoding="utf-8")
docker("stop", "postgres_restore")
if counts[DB] != counts[RESTORE_DB] or selected != restored:
raise RuntimeError("Restored row counts or selected messages differ from the source")
print(json.dumps(result,ensure_ascii=False,indent=2))
if __name__=="__main__": main()