Files
mtusi2026lab2/scripts/track2_experiment.py
Самохвалов Андрей Ремович 3862765327 fix: preserve NUL characters in persisted messages
Store message text as UTF-8 bytes and migrate existing rows without loss.
Cover HTTP round trips, restart persistence, Unicode limits and idempotent retries.
Adapt direct database tools to the binary storage format.
2026-11-07 09:53:39 +03:00

129 lines
6.4 KiB
Python

"""Reproducible 100k-row history-index and insert-cost experiment."""
import csv
import json
import secrets
import sys
from datetime import datetime, timezone
from time import perf_counter
from uuid import uuid4
import psycopg
from psycopg.rows import dict_row
from src.db import database_url
from src.store import Store
HISTORY = "CREATE INDEX messages_history_idx ON messages (chat_id, created_at, id)"
COVERING = (
"CREATE INDEX messages_history_covering_idx ON messages "
"(chat_id, created_at, id) INCLUDE (sender_id, text)"
)
PROJECTION = "SELECT id,chat_id,sender_id,text,created_at FROM messages WHERE chat_id=%s"
def explain(conn, query, params):
result = conn.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + query, params).fetchone()["QUERY PLAN"][0]
plan = result["Plan"]
names = []
def visit(node):
names.append(node.get("Index Name", node["Node Type"]))
for child in node.get("Plans", []):
visit(child)
visit(plan)
return result, " > ".join(names)
def main():
store = Store()
user = store.register("track2_" + uuid4().hex[:16], "Synthetic benchmark", secrets.token_urlsafe(18))
sizes = (("hot", 80_000), ("medium", 15_000), ("cold", 5_000))
chats = [(name, size, store.create_chat(user["id"], "Track 2 " + name, [user["id"]])["id"])
for name, size in sizes]
write_chat = store.create_chat(user["id"], "Track 2 writes", [user["id"]])["id"]
with psycopg.connect(database_url(), autocommit=True, row_factory=dict_row) as conn:
conn.execute("SET statement_timeout=0")
base = datetime.now(timezone.utc)
for name, size, chat_id in chats:
conn.execute(
"INSERT INTO messages (id,chat_id,sender_id,text,created_at) "
"SELECT gen_random_uuid(),%s,%s,convert_to(%s || n, 'UTF8'),%s::timestamptz + n * interval '1 microsecond' "
"FROM generate_series(1,%s) n",
(chat_id, user["id"], name + "-synthetic-", base, size),
)
conn.execute("VACUUM (ANALYZE) messages")
total = sum(conn.execute("SELECT count(*) AS n FROM messages WHERE chat_id=%s", (chat_id,))
.fetchone()["n"] for _, _, chat_id in chats)
assert total == 100_000
hot_id = chats[0][2]
cursor = conn.execute(
"SELECT created_at,id FROM messages WHERE chat_id=%s "
"ORDER BY created_at,id OFFSET 71999 LIMIT 1", (hot_id,)
).fetchone()
queries = (
("first", PROJECTION + " ORDER BY created_at,id LIMIT 51", (hot_id,)),
("deep", PROJECTION + " AND (created_at,id)>(%s,%s) "
"ORDER BY created_at,id LIMIT 51", (hot_id, cursor["created_at"], cursor["id"])),
)
writer = csv.writer(sys.stdout, lineterminator="\n")
writer.writerow(("variant", "index", "operation", "trial", "dataset_rows", "execution_ms",
"shared_hit", "shared_read", "shared_dirtied", "plan", "rows_inserted"))
print(f"UTC: {datetime.now(timezone.utc).isoformat()}", file=sys.stderr)
print(f"PostgreSQL: {conn.execute('SHOW server_version').fetchone()['server_version']}", file=sys.stderr)
print("Synthetic chat sizes: hot=80000, medium=15000, cold=5000; fixed 51-row pages; deep cursor=72000", file=sys.stderr)
try:
conn.execute("DROP INDEX IF EXISTS messages_history_covering_idx")
conn.execute(HISTORY.replace("CREATE INDEX", "CREATE INDEX IF NOT EXISTS", 1))
for variant, index in (("A", "messages_history_idx"),
("B", "messages_history_covering_idx")):
if variant == "B":
conn.execute("DROP INDEX messages_history_idx")
conn.execute(COVERING)
conn.execute("VACUUM (ANALYZE) messages")
print(f"Variant {variant}: {index}", file=sys.stderr)
for label, query, params in queries:
expected = [row["id"] for row in conn.execute(query, params).fetchall()]
assert len(expected) == 51
for _ in range(3):
explain(conn, query, params)
for trial in range(1, 6):
result, plan = explain(conn, query, params)
actual = [row["id"] for row in conn.execute(query, params).fetchall()]
assert actual == expected
node = result["Plan"]
writer.writerow((variant, index, label, trial, total, result["Execution Time"],
node.get("Shared Hit Blocks", 0), node.get("Shared Read Blocks", 0),
node.get("Shared Dirtied Blocks", 0), plan, 0))
if trial == 1:
print(json.dumps({"variant": variant, "operation": label, "plan": node},
default=str), file=sys.stderr)
# Same 500-row write statement on a separate synthetic chat under each index.
# Five committed repetitions include index maintenance and commit time.
for trial in range(1, 6):
start = perf_counter()
with conn.transaction():
conn.execute(
"INSERT INTO messages (id,chat_id,sender_id,text,created_at) "
"SELECT gen_random_uuid(),%s,%s,convert_to('write-synthetic-' || n, 'UTF8'),"
"clock_timestamp() + n * interval '1 microsecond' "
"FROM generate_series(1,500) n",
(write_chat, user["id"]),
)
elapsed = (perf_counter() - start) * 1000
writer.writerow((variant, index, "write500", trial, total, round(elapsed, 3),
"", "", "", "INSERT 500 + COMMIT", 500))
finally:
conn.execute("DROP INDEX IF EXISTS messages_history_covering_idx")
conn.execute(HISTORY.replace("CREATE INDEX", "CREATE INDEX IF NOT EXISTS", 1))
assert conn.execute(
"SELECT count(*) AS n FROM messages WHERE chat_id=%s", (write_chat,)
).fetchone()["n"] == 5000
print("Original history index restored; committed write rows=5000", file=sys.stderr)
if __name__ == "__main__":
main()