#!/usr/bin/env python3 """Трек 2 (часть A): план запроса истории на ≥100 000 сообщений с НЕРАВНОМЕРНЫМИ чатами. Гипотеза H1: составной индекс (chat_id, created_at, id) даёт страницу истории за O(размер страницы) для любого чата и любой глубины, а индекс только по chat_id или «глобальный» (created_at, id) — нет; покрывающий вариант (INCLUDE) читает меньше страниц, но платит за это размером индекса и стоимостью записи. Гипотеза H2: каждый дополнительный индекс заметно удорожает вставку, и цена растёт с шириной индекса. Запуск (ОТДЕЛЬНАЯ БД эксперимента, не рабочая!): DATABASE_URL_FILE=/path/to/url python scripts/track2_indexes.py --out evidence/track2-indexes.json """ import argparse import json import os import platform import statistics import sys import time import uuid from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parents[1])) import psycopg # noqa: E402 from app import config, migrate # noqa: E402 # Неравномерные размеры: 1 гигант, 2 больших, 10 средних, 50 малых, 500 «почти пустых» = ровно 100 000. SIZES = [50000, 20000, 10000] + [1000] * 10 + [100] * 50 + [10] * 500 CONFIGS = [ ("C0_pk_only", None), ("C1_chat_id", "CREATE INDEX hist_idx ON messages (chat_id)"), ("C2_chat_created_id", "CREATE INDEX hist_idx ON messages (chat_id, created_at, id)"), ("C3_covering_include", "CREATE INDEX hist_idx ON messages (chat_id, created_at, id) INCLUDE (sender_id, text)"), ("C4_global_created_id", "CREATE INDEX hist_idx ON messages (created_at, id)"), ] COLS = "id, chat_id, sender_id, text, created_at" PAGE = 50 READ_RUNS, WARMUP = 300, 20 SINGLE_INSERTS = 2000 BULK_REPEATS = 3 def pct(values, p): values = sorted(values) return values[max(0, min(len(values) - 1, int(round(p * len(values) + 0.5)) - 1))] def seed_source(conn): """Один раз генерируем данные в messages_src: тексты разной длины (10–400 символов), время по возрастанию в чате.""" conn.execute("TRUNCATE messages, chat_members, chats, sessions, users CASCADE") conn.execute("DROP TABLE IF EXISTS messages_src") user = uuid.uuid4() conn.execute("INSERT INTO users (id, username, display_name, password_hash) VALUES (%s,'track2_user','x','x')", (user,)) chats = [uuid.uuid4() for _ in SIZES] for cid in chats: conn.execute("INSERT INTO chats (id, title, created_by) VALUES (%s,'t',%s)", (cid, user)) conn.execute("INSERT INTO chat_members (chat_id, user_id) VALUES (%s,%s)", (cid, user)) conn.execute("""CREATE TABLE messages_src AS SELECT gen_random_uuid() AS id, c.chat_id, %s::uuid AS sender_id, left(repeat('сообщение ', 60), 10 + (random() * 390)::int) AS text, timestamptz '2026-01-01 00:00:00+00' + (g.n * 7 + c.idx) * interval '1 second' + (random() * 1000) * interval '1 microsecond' AS created_at FROM (SELECT unnest(%s::uuid[]) AS chat_id, unnest(%s::int[]) AS size, generate_series(1, %s) AS idx) c CROSS JOIN LATERAL generate_series(1, c.size) AS g(n)""", (user, chats, SIZES, len(SIZES))) conn.execute("ANALYZE messages_src") return user, chats def reset_history_index(conn, ddl): conn.execute("DROP INDEX IF EXISTS messages_chat_created_id_idx") conn.execute("DROP INDEX IF EXISTS hist_idx") if ddl: conn.execute(ddl) def measure_writes(conn, ddl, chats): """Стоимость записи: (1) пакетная загрузка 100 000 строк, (2) 2000 одиночных INSERT с коммитом (как работает API).""" bulk = [] for _ in range(BULK_REPEATS): conn.execute("TRUNCATE messages") reset_history_index(conn, ddl) t0 = time.perf_counter() conn.execute("INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT * FROM messages_src") bulk.append(time.perf_counter() - t0) conn.execute("VACUUM (ANALYZE) messages") # Одиночные вставки: чаты выбираются пропорционально размеру (больше сообщений — чаще пишут), autocommit = COMMIT на каждую. import random rnd = random.Random(42) sender = conn.execute("SELECT sender_id FROM messages LIMIT 1").fetchone()[0] singles = [] for _ in range(SINGLE_INSERTS): cid = rnd.choices(chats, weights=SIZES)[0] t0 = time.perf_counter() conn.execute("INSERT INTO messages (id, chat_id, sender_id, text) VALUES (%s,%s,%s,%s)", (uuid.uuid4(), cid, sender, "одиночная вставка " + "x" * rnd.randint(10, 300))) singles.append(time.perf_counter() - t0) conn.execute("VACUUM (ANALYZE) messages") size = lambda rel: conn.execute("SELECT pg_relation_size(%s)", (rel,)).fetchone()[0] return { "bulk_insert_100k_seconds": {"runs": [round(x, 3) for x in bulk], "median": round(statistics.median(bulk), 3)}, "single_insert_ms": {"n": len(singles), "mean": round(1000 * statistics.mean(singles), 3), "p50": round(1000 * pct(singles, .50), 3), "p95": round(1000 * pct(singles, .95), 3), "p99": round(1000 * pct(singles, .99), 3)}, "history_index_bytes": size("hist_idx") if ddl else 0, "table_bytes": size("messages"), "pk_index_bytes": size("messages_pkey"), } def plan_summary(conn, sql, params): plan = conn.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql, params).fetchone()[0][0] nodes, stack = [], [plan["Plan"]] while stack: node = stack.pop() nodes.append(node["Node Type"]) stack.extend(node.get("Plans", [])) root = plan["Plan"] return {"nodes": nodes[::-1], "execution_ms": round(plan["Execution Time"], 3), "shared_buffers": root.get("Shared Hit Blocks", 0) + root.get("Shared Read Blocks", 0), "rows": root["Actual Rows"]} def measure_reads(conn, queries): out = {} for name, (sql, params) in queries.items(): for _ in range(WARMUP): conn.execute(sql, params).fetchall() times = [] for _ in range(READ_RUNS): t0 = time.perf_counter() conn.execute(sql, params).fetchall() times.append(time.perf_counter() - t0) out[name] = {"p50_ms": round(1000 * pct(times, .50), 3), "p95_ms": round(1000 * pct(times, .95), 3), "plan": plan_summary(conn, sql, params)} return out def main(): ap = argparse.ArgumentParser() ap.add_argument("--out", default="evidence/track2-indexes.json") ap.add_argument("--reverse", action="store_true", help="обратный порядок конфигураций: проверка, что результат не зависит от порядка/прогрева") args = ap.parse_args() migrate.apply_all(config.DATABASE_URL) with psycopg.connect(config.DATABASE_URL, autocommit=True) as conn: user, chats = seed_source(conn) total = conn.execute("SELECT count(*) FROM messages_src").fetchone()[0] big, small, mid = chats[0], chats[-1], chats[3] # 50 000 / 10 / 1 000 сообщений deep_ts, deep_id = conn.execute("SELECT created_at, id FROM messages_src WHERE chat_id=%s ORDER BY created_at, id OFFSET 40000 LIMIT 1", (big,)).fetchone() mid_ts, mid_id = conn.execute("SELECT created_at, id FROM messages_src WHERE chat_id=%s ORDER BY created_at, id OFFSET 500 LIMIT 1", (mid,)).fetchone() first = "SELECT %s FROM messages WHERE chat_id=%%s ORDER BY created_at, id LIMIT %%s" % COLS keyset = "SELECT %s FROM messages WHERE chat_id=%%s AND (created_at, id) > (%%s, %%s) ORDER BY created_at, id LIMIT %%s" % COLS offset = "SELECT %s FROM messages WHERE chat_id=%%s ORDER BY created_at, id OFFSET 40000 LIMIT %%s" % COLS queries = { "Q1_first_page_giant_50k": (first, (big, PAGE + 1)), "Q2_deep_keyset_giant_at_40k": (keyset, (big, deep_ts, deep_id, PAGE + 1)), "Q3_first_page_tiny_10": (first, (small, PAGE + 1)), "Q4_keyset_mid_1k_at_500": (keyset, (mid, mid_ts, mid_id, PAGE + 1)), "Q5_OFFSET_40000_giant": (offset, (big, PAGE + 1)), } env = { "postgres": conn.execute("SHOW server_version").fetchone()[0], "shared_buffers": conn.execute("SHOW shared_buffers").fetchone()[0], "work_mem": conn.execute("SHOW work_mem").fetchone()[0], "fsync": conn.execute("SHOW fsync").fetchone()[0], "synchronous_commit": conn.execute("SHOW synchronous_commit").fetchone()[0], "cpus": os.cpu_count(), "python": platform.python_version(), "platform": platform.platform(), } results = {"environment": env, "dataset": {"messages": total, "chats": len(SIZES), "chat_size_distribution": {"50000": 1, "20000": 1, "10000": 1, "1000": 10, "100": 50, "10": 500}, "text_length_chars": "10..400 (равномерно)", "page_size": PAGE, "read_runs": READ_RUNS, "warmup": WARMUP, "single_inserts": SINGLE_INSERTS, "bulk_repeats": BULK_REPEATS, "order": "reverse" if args.reverse else "forward"}, "configs": {}} for name, ddl in (reversed(CONFIGS) if args.reverse else CONFIGS): print("== %s ==" % name, flush=True) w = measure_writes(conn, ddl, chats) r = measure_reads(conn, queries) results["configs"][name] = {"ddl": ddl, "writes": w, "reads": r} print(" bulk %.2fs, single p50 %.3f ms, index %.1f MiB; Q1 p50 %.3f ms, Q2 p50 %.3f ms, Q3 p50 %.3f ms" % ( w["bulk_insert_100k_seconds"]["median"], w["single_insert_ms"]["p50"], w["history_index_bytes"] / 2**20, r["Q1_first_page_giant_50k"]["p50_ms"], r["Q2_deep_keyset_giant_at_40k"]["p50_ms"], r["Q3_first_page_tiny_10"]["p50_ms"]), flush=True) Path(args.out).parent.mkdir(parents=True, exist_ok=True) Path(args.out).write_text(json.dumps(results, ensure_ascii=False, indent=2) + "\n") print("saved", args.out) if __name__ == "__main__": main()