Files
2026-10-16 19:49:48 +03:00

187 lines
10 KiB
Python

#!/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()