"""SQL-эксперимент запускается только в отдельном research Compose project.""" from datetime import datetime, timedelta, timezone import hashlib import json import os import secrets import statistics import sys import time from uuid import UUID import psycopg from psycopg.conninfo import make_conninfo from psycopg.rows import dict_row from src import db, migrate, passwords from src.config import secret SIZES = [70000, 20000, 9000, 1000] START = datetime(2026, 1, 1, tzinfo=timezone.utc) QUERY = """SELECT * FROM messages WHERE chat_id=%s AND (created_at, id)>(%s, %s) ORDER BY created_at, id LIMIT %s""" def identifier(value): return UUID(hashlib.md5(value.encode(), usedforsecurity=False).hexdigest()) def connect(restored=False): url = secret("DATABASE_URL") if restored: url = make_conninfo(url, host="postgres-restore") return psycopg.connect(url, autocommit=True, row_factory=dict_row, options="-c timezone=UTC -c statement_timeout=120000") def snapshot(conn): counts = {} for table in ("users", "chats", "chat_members", "messages", "schema_migrations"): # Имена таблиц — фиксированный список выше, не входные данные клиента. counts[table] = conn.execute("SELECT count(*) AS n FROM " + table).fetchone()["n"] digest = hashlib.sha256() for row in conn.execute("SELECT * FROM messages ORDER BY id"): digest.update((json.dumps(row, default=str, sort_keys=True, ensure_ascii=False) + "\n").encode()) samples = [] offset = 0 for size in SIZES: for number in (offset + 1, offset + size // 2, offset + size): row = conn.execute("SELECT * FROM messages WHERE id=%s", (identifier(f"message:{number}"),)).fetchone() samples.append(json.loads(json.dumps(row, default=str, sort_keys=True, ensure_ascii=False))) offset += size return {"counts": counts, "messages_sha256": digest.hexdigest(), "samples": samples} def benchmark(): db.pool.open(wait=True, timeout=15) try: migrate.apply() finally: db.pool.close() results, plans = [], [] with connect() as conn: assert conn.execute("SELECT count(*) AS n FROM users").fetchone()["n"] == 0, "Research requires an empty database" encoded = passwords.hash_password(secrets.token_urlsafe(24)) for n in range(1, 11): conn.execute("INSERT INTO users(id,username,display_name,password_hash) VALUES(%s,%s,%s,%s)", (identifier(f"user:{n}"), f"research_{n}", "Synthetic research user", encoded)) for n in range(4): chat = identifier(f"chat:{n}") conn.execute("INSERT INTO chats(id,title) VALUES(%s,%s)", (chat, f"Research chat {n}")) for user in (1, 2): conn.execute("INSERT INTO chat_members(chat_id,user_id) VALUES(%s,%s)", (chat, identifier(f"user:{user}"))) variants = [("chat_only", "chat_id"), ("history", "chat_id, created_at, id")] for repeat in range(3): order = variants if repeat % 2 == 0 else variants[::-1] for variant, columns in order: conn.execute("TRUNCATE messages") conn.execute("DROP INDEX IF EXISTS messages_history_idx") conn.execute("DROP INDEX IF EXISTS messages_chat_idx") index = "messages_history_idx" if variant == "history" else "messages_chat_idx" conn.execute("CREATE INDEX " + index + " ON messages (" + columns + ")") lsn = conn.execute("SELECT pg_current_wal_insert_lsn() AS lsn").fetchone()["lsn"] started = time.perf_counter() conn.execute("""INSERT INTO messages(id,chat_id,sender_id,text,created_at) SELECT md5('message:'||n)::uuid, md5('chat:'||(CASE WHEN n<=70000 THEN 0 WHEN n<=90000 THEN 1 WHEN n<=99000 THEN 2 ELSE 3 END))::uuid, md5('user:'||(1+n%2))::uuid, 'Synthetic message '||n||' '||repeat('x',80), timestamptz '2026-01-01 00:00:00+00' + n*interval '1 microsecond' FROM generate_series(1,100000) AS n""") insert_ms = (time.perf_counter() - started) * 1000 wal = conn.execute("SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), %s) AS bytes", (lsn,)).fetchone()["bytes"] conn.execute("VACUUM (ANALYZE) messages") index_bytes = conn.execute("SELECT pg_relation_size(%s::regclass) AS bytes", (index,)).fetchone()["bytes"] result = {"variant": variant, "repeat": repeat + 1, "insert_100000_ms": insert_ms, "wal_bytes": int(wal), "index_bytes": index_bytes, "queries": []} offset = 0 for chat, size in enumerate(SIZES): for fraction in (0.1, 0.5, 0.9): number = offset + int(size * fraction) params = (identifier(f"chat:{chat}"), START + timedelta(microseconds=number), identifier(f"message:{number}"), 51) conn.execute(QUERY, params).fetchall() # один прогрев именно этой страницы times = [] for _ in range(5): started = time.perf_counter() rows = conn.execute(QUERY, params).fetchall() times.append((time.perf_counter() - started) * 1000) assert len(rows) == 51 plan = conn.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + QUERY, params).fetchone()["QUERY PLAN"][0] result["queries"].append({"chat_size": size, "position": fraction, "latency_ms": times, "explain_execution_ms": plan["Execution Time"]}) if repeat == 0: plans.append({"variant": variant, "chat_size": size, "position": fraction, "plan": plan}) offset += size results.append(result) # Последняя итерация заканчивается production-индексом history. assert conn.execute("SELECT to_regclass('messages_history_idx') AS name").fetchone()["name"] baseline = snapshot(conn) assert baseline["counts"]["messages"] == 100000 summary = {} for variant, _ in variants: trials = [row for row in results if row["variant"] == variant] latencies = sorted(t for row in trials for q in row["queries"] for t in q["latency_ms"]) summary[variant] = { "insert_100000_median_ms": statistics.median(row["insert_100000_ms"] for row in trials), "index_bytes": trials[-1]["index_bytes"], "wal_bytes_median": statistics.median(row["wal_bytes"] for row in trials), "queries": len(latencies), "errors": 0, "concurrency": 1, "p50_ms": statistics.median(latencies), "p95_ms": latencies[int(0.95 * (len(latencies) - 1))], "p99_ms": latencies[int(0.99 * (len(latencies) - 1))], "successful_sql_rps": len(latencies) / (sum(latencies) / 1000), } return {"postgres": conn.execute("SELECT version() AS version").fetchone()["version"], "messages": 100000, "chat_sizes": SIZES, "repeats": 3, "warmup_per_case": 1, "measurements_per_case": 5, "page_size": 51, "query": QUERY, "summary": summary, "trials": results, "plans": plans, "snapshot": baseline} def after_backup(): with connect() as conn: message_id = identifier("after-backup") conn.execute("INSERT INTO messages(id,chat_id,sender_id,text) VALUES(%s,%s,%s,%s)", (message_id, identifier("chat:0"), identifier("user:1"), "Committed AFTER backup")) return {"id": str(message_id), "source_messages": conn.execute("SELECT count(*) AS n FROM messages").fetchone()["n"]} def verify(): expected = json.load(sys.stdin) with connect(restored=True) as restored, connect() as source: actual = snapshot(restored) assert actual == expected, "Restored data differs from the snapshot" marker = (identifier("after-backup"),) assert restored.execute("SELECT 1 FROM messages WHERE id=%s", marker).fetchone() is None assert source.execute("SELECT 1 FROM messages WHERE id=%s", marker).fetchone() is not None return {"counts": actual["counts"], "messages_sha256": actual["messages_sha256"], "samples_verified": len(actual["samples"]), "all_messages_match": True, "after_backup_message_missing_in_restore": True, "source_messages": source.execute("SELECT count(*) AS n FROM messages").fetchone()["n"]} if __name__ == "__main__": if os.environ.get("LAB2_RESEARCH") != "1": raise SystemExit("Use ./scripts/research.sh: an isolated research database is required") operation = sys.argv[1] if len(sys.argv) > 1 else "benchmark" result = {"benchmark": benchmark, "after-backup": after_backup, "verify": verify}[operation]() print(json.dumps(result, ensure_ascii=False, default=str))