Files
mtusi2026lab2/research/query_plan.py
T

372 lines
14 KiB
Python

#!/usr/bin/env python3
"""Isolated SQL experiment and logical restore; never resets the application DB."""
import argparse
import hashlib
import json
import platform
import statistics
import subprocess
import tempfile
import threading
import time
from datetime import UTC, datetime, timedelta
from pathlib import Path
from uuid import UUID, uuid4
import psycopg
from argon2 import PasswordHasher
from psycopg import sql
from psycopg.conninfo import make_conninfo
from messenger.migrate import migrate
ROOT = Path(__file__).resolve().parents[1]
SIZES = [90_000, 5_000, 2_000, 1_000, 500, 500, 250, 250, 250, 250]
CHAT_IDS = [UUID(int=100 + i) for i in range(10)]
USER_IDS = [UUID(int=1), UUID(int=2)]
START = datetime(2026, 10, 17, tzinfo=UTC)
QUERY = (
"SELECT id,chat_id,sender_id,text,created_at FROM messages WHERE chat_id=%s "
"AND (created_at,id) > (%s,%s) ORDER BY created_at,id LIMIT 51"
)
def docker(*args, **kwargs):
return subprocess.run(
["docker", "compose", *args], cwd=ROOT, check=True, capture_output=True, **kwargs
)
def percentiles(values):
ordered = sorted(values)
def p(fraction):
index = (len(ordered) - 1) * fraction
lower = int(index)
return ordered[lower] + (ordered[min(lower + 1, len(ordered) - 1)] - ordered[lower]) * (
index - lower
)
return {"p50_ms": p(0.5), "p95_ms": p(0.95), "p99_ms": p(0.99)}
def initialize(conn):
encoded = PasswordHasher().hash("Synthetic-research-password-only")
for i, user_id in enumerate(USER_IDS):
conn.execute(
"INSERT INTO users (id,username,display_name,password_hash) VALUES (%s,%s,%s,%s)",
(user_id, "research_" + str(i), "Research", encoded),
)
for chat_id in CHAT_IDS:
conn.execute("INSERT INTO chats (id,title) VALUES (%s,'Synthetic history')", (chat_id,))
for position, user_id in enumerate(USER_IDS):
conn.execute(
"INSERT INTO chat_members (chat_id,user_id,position) VALUES (%s,%s,%s)",
(chat_id, user_id, position),
)
def seed(conn, variant):
conn.execute("TRUNCATE messages")
conn.execute("DROP INDEX IF EXISTS messages_history")
conn.execute("DROP INDEX IF EXISTS messages_by_chat")
if variant == "single":
conn.execute("CREATE INDEX messages_by_chat ON messages (chat_id)")
else:
conn.execute("CREATE INDEX messages_history ON messages (chat_id,created_at,id)")
with conn.transaction():
for chat_id, size in zip(CHAT_IDS, SIZES, strict=True):
conn.execute(
"INSERT INTO messages (id,chat_id,sender_id,text,created_at) "
"SELECT md5(%s || ':' || g::text)::uuid,%s,%s,repeat('m',128),"
"%s::timestamptz + g * interval '1 microsecond' FROM generate_series(1,%s) g",
(str(chat_id), chat_id, USER_IDS[0], START, size),
)
conn.execute("VACUUM (ANALYZE) messages")
def parameters(i):
chat_index = 8 if i % 4 == 0 else 0
boundary = int(SIZES[chat_index] * 0.9) if i % 2 == 0 else 0
return CHAT_IDS[chat_index], START + timedelta(microseconds=boundary), UUID(int=0)
def resource_samples(stop, samples, container):
while not stop.is_set():
try:
result = subprocess.run(
["docker", "stats", "--no-stream", "--format", "{{json .}}", container],
capture_output=True,
text=True,
timeout=5,
check=True,
)
raw = json.loads(result.stdout)
samples.append(
{
"at": datetime.now(UTC).isoformat(),
"cpu": raw["CPUPerc"],
"memory": raw["MemUsage"],
}
)
except OSError, subprocess.SubprocessError, ValueError, KeyError:
samples.append({"error": "resource_sample_unavailable"})
stop.wait(0.25)
def measure(conn, variant, repeat, queries, batches, container):
seed(conn, variant)
plans = {}
for name, params in [
("large_first", parameters(1)),
("large_deep", parameters(2)),
("small_deep", parameters(0)),
]:
plans[name] = conn.execute(
"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + QUERY, params
).fetchone()[0]
for i in range(20):
conn.execute(QUERY, parameters(i)).fetchall()
stop, samples = threading.Event(), []
sampler = threading.Thread(
target=resource_samples, args=(stop, samples, container), daemon=True
)
sampler.start()
try:
latency = []
started = time.perf_counter()
for i in range(queries):
before = time.perf_counter()
rows = conn.execute(QUERY, parameters(i)).fetchall()
latency.append((time.perf_counter() - before) * 1000)
assert len(rows) > 0
elapsed = time.perf_counter() - started
reads = {
"successful_queries": queries,
"errors": 0,
"elapsed_seconds": elapsed,
"queries_per_second": queries / elapsed,
**percentiles(latency),
"raw_latency_ms": latency,
}
before_wal = conn.execute("SELECT pg_current_wal_lsn()").fetchone()[0]
latency = []
started = time.perf_counter()
for batch in range(batches):
records = [
(uuid4(), CHAT_IDS[0], USER_IDS[0], f"write:{batch}:{i}") for i in range(100)
]
before = time.perf_counter()
with conn.transaction(), conn.cursor() as cur:
cur.executemany(
"INSERT INTO messages (id,chat_id,sender_id,text) VALUES (%s,%s,%s,%s)", records
)
latency.append((time.perf_counter() - before) * 1000)
elapsed = time.perf_counter() - started
wal = conn.execute(
"SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),%s::pg_lsn)", (before_wal,)
).fetchone()[0]
writes = {
"committed_batches": batches,
"rows_per_batch": 100,
"errors": 0,
"elapsed_seconds": elapsed,
"rows_per_second": batches * 100 / elapsed,
"wal_bytes_cluster_delta": int(wal),
**percentiles(latency),
"raw_batch_latency_ms": latency,
}
finally:
stop.set()
sampler.join(timeout=6)
name = "messages_by_chat" if variant == "single" else "messages_history"
index_bytes = conn.execute("SELECT pg_relation_size(%s::regclass)", (name,)).fetchone()[0]
return {
"index": variant,
"repeat": repeat,
"initial_messages": sum(SIZES),
"plans": plans,
"reads": reads,
"writes": writes,
"index_bytes_after_writes": index_bytes,
"postgres_resource_samples": samples,
}
def fingerprint(conn):
counts = {
table: conn.execute(
sql.SQL("SELECT count(*) FROM {}").format(sql.Identifier(table))
).fetchone()[0]
for table in ("users", "chats", "chat_members", "messages", "sessions", "schema_migrations")
}
digest = hashlib.sha256()
with conn.transaction(), conn.cursor(name="fingerprint") as cur:
cur.execute("SELECT id,chat_id,sender_id,text,created_at FROM messages ORDER BY id")
for row in cur:
digest.update((json.dumps(row, default=str, ensure_ascii=False) + "\n").encode())
selected = conn.execute(
"SELECT id,chat_id,sender_id,text,created_at FROM messages ORDER BY id LIMIT 8"
).fetchall()
return {
"counts": counts,
"all_messages_sha256": digest.hexdigest(),
"selected_messages": [[str(value) for value in row] for row in selected],
}
def restore_check(conn, admin, source, restored, config):
token_hash = hashlib.sha256(uuid4().bytes + uuid4().bytes).digest()
conn.execute(
"INSERT INTO sessions (token_hash,user_id,expires_at) "
"VALUES (%s,%s,clock_timestamp()+interval '1 hour')",
(token_hash, USER_IDS[0]),
)
before = fingerprint(conn)
with tempfile.TemporaryDirectory(prefix="lab2-backup-") as temporary:
archive = Path(temporary) / "snapshot.dump"
started = time.perf_counter()
dump = docker(
"exec",
"-T",
"postgres",
"pg_dump",
"-U",
"messenger",
"-d",
source,
"--format=custom",
"--no-owner",
"--no-acl",
)
archive.write_bytes(dump.stdout)
dump_seconds = time.perf_counter() - started
# Deliberately committed AFTER the backup; logical restore must not contain it.
late_id = uuid4()
conn.execute(
"INSERT INTO messages (id,chat_id,sender_id,text) VALUES (%s,%s,%s,'after snapshot')",
(late_id, CHAT_IDS[0], USER_IDS[0]),
)
started = time.perf_counter()
admin.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier(restored)))
docker(
"exec",
"-T",
"postgres",
"pg_restore",
"-U",
"messenger",
"-d",
restored,
"--no-owner",
"--no-acl",
input=archive.read_bytes(),
)
restore_seconds = time.perf_counter() - started
with psycopg.connect(make_conninfo(config, dbname=restored), autocommit=True) as target:
after = fingerprint(target)
assert before == after, "Restored snapshot differs"
assert (
target.execute("SELECT count(*) FROM messages WHERE id=%s", (late_id,)).fetchone()[
0
]
== 0
)
return {
"snapshot": before,
"restored": after,
"equal": before == after,
"dump_seconds": dump_seconds,
"restore_database_seconds": restore_seconds,
"archive_bytes": archive.stat().st_size,
"archive_sha256": hashlib.sha256(archive.read_bytes()).hexdigest(),
"post_backup_commits_absent_after_restore": 1,
"archive_retained": False,
}
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument("--output", type=Path, default=ROOT / "evidence/query-plan.json")
parser.add_argument("--queries", type=int, default=200)
parser.add_argument("--write-batches", type=int, default=20)
args = parser.parse_args()
if args.output.exists():
raise SystemExit("Choose a new output path; existing evidence will not be overwritten")
password = (ROOT / "secrets/postgres_password").read_text().strip()
config = make_conninfo(
host="127.0.0.1",
port=55432,
dbname="messenger",
user="messenger",
password=password,
options="-c timezone=UTC",
)
source, restored = "research_" + uuid4().hex, "restored_" + uuid4().hex
container = docker("ps", "-q", "postgres", text=True).stdout.strip()
result = {
"started_at": datetime.now(UTC).isoformat(),
"scope": "SQL, not HTTP latency",
"initial_chat_sizes": SIZES,
"concurrency": 1,
"warmup_queries_per_trial": 20,
"client_platform": platform.machine(),
"trials": [],
}
with psycopg.connect(config, autocommit=True) as admin:
admin.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier(source)))
try:
dsn = make_conninfo(config, dbname=source)
migrate(dsn, ROOT / "migrations")
with psycopg.connect(dsn, autocommit=True) as conn:
initialize(conn)
result["postgres_version"] = conn.execute("SELECT version()").fetchone()[0]
result["durability"] = {
name: conn.execute(sql.SQL("SHOW {}").format(sql.Identifier(name))).fetchone()[
0
]
for name in ("fsync", "synchronous_commit", "full_page_writes")
}
for repeat in range(3):
for variant in (
("single", "composite") if repeat % 2 == 0 else ("composite", "single")
):
trial = measure(
conn, variant, repeat + 1, args.queries, args.write_batches, container
)
result["trials"].append(trial)
print("Completed " + variant + " repeat " + str(repeat + 1), flush=True)
result["backup_restore"] = restore_check(conn, admin, source, restored, config)
result["finished_at"] = datetime.now(UTC).isoformat()
result["median_by_index"] = {
variant: {
"read_queries_per_second": statistics.median(
t["reads"]["queries_per_second"]
for t in result["trials"]
if t["index"] == variant
),
"read_p95_ms": statistics.median(
t["reads"]["p95_ms"] for t in result["trials"] if t["index"] == variant
),
"write_rows_per_second": statistics.median(
t["writes"]["rows_per_second"]
for t in result["trials"]
if t["index"] == variant
),
}
for variant in ("single", "composite")
}
args.output.parent.mkdir(parents=True, exist_ok=True)
args.output.write_text(json.dumps(result, ensure_ascii=False, indent=2) + "\n")
print("PASS: index comparison and backup restore verified")
finally:
for name in (restored, source):
admin.execute(
sql.SQL("DROP DATABASE IF EXISTS {} WITH (FORCE)").format(sql.Identifier(name))
)
if __name__ == "__main__":
main()