Files
mtusi2026lab2/student-tests/track2.py
2026-10-23 16:31:42 +03:00

406 lines
16 KiB
Python

#!/usr/bin/env python3
"""Track 2: two history plans, write cost, backup into a second Postgres volume."""
import json
import os
import platform
import re
import subprocess
import time
import uuid
from pathlib import Path
ROOT = Path(__file__).resolve().parents[1]
EVIDENCE = ROOT / "evidence"
def env_value(name):
for line in (ROOT / ".env").read_text(encoding="utf-8").splitlines():
if line.startswith(name + "="):
return line.split("=", 1)[1].strip()
raise SystemExit("missing " + name)
def run(args, input_bytes=None, check=True):
completed = subprocess.run(
args,
cwd=ROOT,
input=input_bytes,
capture_output=True,
)
if check and completed.returncode != 0:
raise SystemExit(completed.stderr.decode("utf-8", "replace")[-2000:])
return completed
def compose(args, input_bytes=None, check=True):
return run(["docker", "compose", "--env-file", ".env", *args], input_bytes, check)
def psql(service, database, sql, check=True, tuples=False):
command = ["exec", "-T", service, "psql", "-v", "ON_ERROR_STOP=1", "-X", "-q",
"-U", env_value("POSTGRES_USER"), "-d", database]
if tuples:
command.extend(["-A", "-t", "-F", "|"])
completed = compose(command, sql.encode("utf-8"), check)
text = completed.stdout.decode("utf-8", "replace")
err = completed.stderr.decode("utf-8", "replace")
return text, err
def psql_rows(service, database, sql):
text, _ = psql(service, database, sql, tuples=True)
rows = []
for line in text.splitlines():
line = line.strip()
if line:
rows.append(line.split("|"))
return rows
def wait_service(service):
for _ in range(40):
probe = compose(
["exec", "-T", service, "pg_isready", "-U", env_value("POSTGRES_USER"), "-d", env_value("POSTGRES_DB")],
check=False,
)
if probe.returncode == 0:
return
time.sleep(1)
raise SystemExit(service + " did not become ready")
def recreate(service):
user = env_value("POSTGRES_USER")
base = env_value("POSTGRES_DB")
psql(service, base, "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'track2' AND pid <> pg_backend_pid();", check=False)
psql(service, base, "DROP DATABASE IF EXISTS track2;")
psql(service, base, "CREATE DATABASE track2;")
def apply_schema():
for name in ("001_tables.sql", "002_message_history_index.sql"):
body = (ROOT / "migrations" / name).read_bytes()
psql("postgres", "track2", body.decode("utf-8"))
def seed():
alice = "11111111-1111-4111-8111-111111111111"
bob = "22222222-2222-4222-8222-222222222222"
sizes = [70000, 20000, 8000, 1500, 400, 80, 15, 5] + [1] * 40
chats = []
statements = [
"""
INSERT INTO users (id, username, display_name, password_hash, created_at) VALUES
('%s', 'track_alice', 'Алиса', 'not-a-login-hash', timestamptz '2026-01-01+00'),
('%s', 'track_bob', 'Боб', 'not-a-login-hash', timestamptz '2026-01-01+00');
""" % (alice, bob)
]
for size in sizes:
chat_id = str(uuid.uuid4())
chats.append({"id": chat_id, "messages": size})
statements.append(
"INSERT INTO chats (id, title, created_at) VALUES ('%s', 'chat-%d', timestamptz '2026-01-01+00');" % (chat_id, size)
)
statements.append(
"""
INSERT INTO chat_members (chat_id, user_id, position) VALUES
('%s', '%s', 0),
('%s', '%s', 1);
""" % (chat_id, alice, chat_id, bob)
)
if size:
statements.append(
"""
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT gen_random_uuid(), '%s'::uuid, '%s'::uuid, 'm' || g,
timestamptz '2026-01-01+00' + (g * interval '1 millisecond')
FROM generate_series(1, %d) AS g;
""" % (chat_id, alice, size)
)
for _ in range(25):
chat_id = str(uuid.uuid4())
chats.append({"id": chat_id, "messages": 0})
statements.append(
"INSERT INTO chats (id, title, created_at) VALUES ('%s', 'empty', timestamptz '2026-01-01+00');" % chat_id
)
statements.append(
"""
INSERT INTO chat_members (chat_id, user_id, position) VALUES
('%s', '%s', 0),
('%s', '%s', 1);
""" % (chat_id, alice, chat_id, bob)
)
psql("postgres", "track2", "\n".join(statements))
psql("postgres", "track2", "ANALYZE users, chats, chat_members, messages;")
return alice, chats
def explain(sql):
text, _ = psql("postgres", "track2", "EXPLAIN (ANALYZE, BUFFERS)\n" + sql)
return text
def metric(plan):
execution = re.search(r"Execution Time: ([0-9.]+) ms", plan)
buffers = re.search(r"^Buffers: (.+)$", plan, re.M)
shared_hit = re.search(r"shared hit=(\d+)", plan)
shared_read = re.search(r"shared read=(\d+)", plan)
return {
"execution_ms": float(execution.group(1)) if execution else None,
"buffers": buffers.group(1).strip() if buffers else "",
"shared_hit": int(shared_hit.group(1)) if shared_hit else 0,
"shared_read": int(shared_read.group(1)) if shared_read else 0,
"uses_history_index": "messages_history_idx" in plan,
"node": plan_node(plan),
}
def plan_node(plan):
if "Index Only Scan" in plan:
return "Index Only Scan"
if "Bitmap" in plan:
return "Bitmap"
if "Index Scan" in plan:
return "Index Scan"
if "Seq Scan" in plan:
return "Seq Scan"
return "other"
def keyset_sql(table, chat_id, created_at, message_id, limit):
return """
SELECT id::text, chat_id::text, sender_id::text, text, created_at
FROM %s
WHERE chat_id = '%s'::uuid
AND (created_at, id) > (timestamptz '%s+00', '%s'::uuid)
ORDER BY created_at, id
LIMIT %d
""" % (table, chat_id, created_at, message_id, limit)
def cursor_at(chat_id, offset):
rows = psql_rows("postgres", "track2", """
SELECT to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.US'), id::text
FROM messages
WHERE chat_id = '%s'::uuid
ORDER BY created_at, id
OFFSET %d LIMIT 1;
""" % (chat_id, offset))
if not rows:
raise SystemExit("cursor row missing")
return rows[0][0].strip(), rows[0][1].strip()
def write_cost(chat_id, sender_id):
sql = """
CREATE TABLE messages_pk (
id uuid PRIMARY KEY,
chat_id uuid NOT NULL,
sender_id uuid NOT NULL,
text text NOT NULL,
created_at timestamptz NOT NULL
);
CREATE TABLE messages_idx (
id uuid PRIMARY KEY,
chat_id uuid NOT NULL,
sender_id uuid NOT NULL,
text text NOT NULL,
created_at timestamptz NOT NULL
);
CREATE INDEX messages_idx_history ON messages_idx (chat_id, created_at, id);
CREATE TEMP TABLE seed AS
SELECT gen_random_uuid() AS id,
'%s'::uuid AS chat_id,
'%s'::uuid AS sender_id,
'w' || g AS text,
timestamptz '2026-03-01+00' + (g * interval '1 millisecond') AS created_at
FROM generate_series(1, 50000) AS g;
DO $$
DECLARE started timestamptz;
BEGIN
started := clock_timestamp();
INSERT INTO messages_pk SELECT * FROM seed;
RAISE NOTICE 'pk_ms=%%', round(extract(epoch FROM clock_timestamp() - started) * 1000);
started := clock_timestamp();
INSERT INTO messages_idx SELECT * FROM seed;
RAISE NOTICE 'idx_ms=%%', round(extract(epoch FROM clock_timestamp() - started) * 1000);
END $$;
DROP TABLE messages_pk, messages_idx;
""" % (chat_id, sender_id)
_, err = psql("postgres", "track2", sql)
pk = re.search(r"pk_ms=([0-9.]+)", err)
idx = re.search(r"idx_ms=([0-9.]+)", err)
if not pk or not idx:
raise SystemExit("write timings missing:\n" + err[-1500:])
return {"rows": 50000, "primary_key_ms": float(pk.group(1)), "primary_key_and_history_index_ms": float(idx.group(1))}
def counts(service):
rows = psql_rows(service, "track2", """
SELECT 'users', count(*)::text FROM users
UNION ALL SELECT 'chats', count(*)::text FROM chats
UNION ALL SELECT 'chat_members', count(*)::text FROM chat_members
UNION ALL SELECT 'messages', count(*)::text FROM messages;
""")
return {name: int(value) for name, value in rows}
def sample(service, chat_id):
return psql_rows(service, "track2", """
SELECT id::text || '|' || text || '|' || to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"')
FROM messages
WHERE chat_id = '%s'::uuid
ORDER BY created_at, id
LIMIT 3;
""" % chat_id)
def memory_gib():
if os.name != "nt":
return None
import ctypes
class Status(ctypes.Structure):
_fields_ = [
("dwLength", ctypes.c_ulong),
("dwMemoryLoad", ctypes.c_ulong),
("ullTotalPhys", ctypes.c_ulonglong),
("ullAvailPhys", ctypes.c_ulonglong),
("ullTotalPageFile", ctypes.c_ulonglong),
("ullAvailPageFile", ctypes.c_ulonglong),
("ullTotalVirtual", ctypes.c_ulonglong),
("ullAvailVirtual", ctypes.c_ulonglong),
("ullAvailExtendedVirtual", ctypes.c_ulonglong),
]
status = Status()
status.dwLength = ctypes.sizeof(Status)
if not ctypes.windll.kernel32.GlobalMemoryStatusEx(ctypes.byref(status)):
return None
return round(status.ullTotalPhys / 1024**3, 1)
def main():
EVIDENCE.mkdir(exist_ok=True)
compose(["--profile", "restore", "up", "-d", "postgres-restore"])
wait_service("postgres")
wait_service("postgres-restore")
recreate("postgres")
recreate("postgres-restore")
apply_schema()
sender, chats = seed()
populated = [item for item in chats if item["messages"] >= 10000]
big = max(chats, key=lambda item: item["messages"])
medium = populated[-1]
created_at, message_id = cursor_at(big["id"], 60000)
plans = {
"keyset_deep_history_index": explain(keyset_sql("messages", big["id"], created_at, message_id, 50)),
"offset_deep_same_chat": explain("""
SELECT id::text, chat_id::text, sender_id::text, text, created_at
FROM messages
WHERE chat_id = '%s'::uuid
ORDER BY created_at, id
OFFSET 60000 LIMIT 50
""" % big["id"]),
"keyset_first_page_at_least_10000": explain("""
SELECT id::text, chat_id::text, sender_id::text, text, created_at
FROM messages
WHERE chat_id = '%s'::uuid
ORDER BY created_at, id
LIMIT 50
""" % medium["id"]),
}
psql("postgres", "track2", """
CREATE TABLE messages_time AS TABLE messages;
CREATE INDEX messages_time_created ON messages_time (created_at);
ANALYZE messages_time;
""")
plans["keyset_deep_created_at_index_only"] = explain(keyset_sql("messages_time", big["id"], created_at, message_id, 50))
psql("postgres", "track2", "DROP TABLE messages_time;")
writes = write_cost(big["id"], sender)
before = counts("postgres")
chosen = sample("postgres", big["id"])
settings = psql_rows("postgres", "track2", """
SELECT current_setting('server_version')
UNION ALL SELECT current_setting('synchronous_commit')
UNION ALL SELECT current_setting('fsync');
""")
flat_settings = [row[0] for row in settings]
redis_cfg = compose(
["exec", "-T", "redis", "sh", "-c",
'redis-cli --no-auth-warning -a "$REDIS_PASSWORD" CONFIG GET appendonly; redis-cli --no-auth-warning -a "$REDIS_PASSWORD" CONFIG GET appendfsync']
).stdout.decode("utf-8", "replace")
started = time.perf_counter()
dump = compose(["exec", "-T", "postgres", "pg_dump", "-U", env_value("POSTGRES_USER"), "-d", "track2", "-Fc", "--no-owner", "--no-acl"]).stdout
dump_seconds = round(time.perf_counter() - started, 3)
extra_id = str(uuid.uuid4())
psql("postgres", "track2", """
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
VALUES ('%s', '%s', '%s', 'after-dump', timestamptz '2026-06-01+00');
""" % (extra_id, big["id"], sender))
started = time.perf_counter()
restored = compose(
["exec", "-T", "postgres-restore", "pg_restore", "-U", env_value("POSTGRES_USER"), "-d", "track2", "--no-owner", "--exit-on-error"],
dump,
)
restore_seconds = round(time.perf_counter() - started, 3)
if restored.returncode != 0:
raise SystemExit(restored.stderr.decode("utf-8", "replace")[-2000:])
after_source = counts("postgres")
after_restore = counts("postgres-restore")
restored_sample = sample("postgres-restore", big["id"])
extra_in_restore = psql_rows("postgres-restore", "track2", "SELECT count(*)::text FROM messages WHERE id = '%s'::uuid;" % extra_id)
summary = {
"track": 2,
"hypothesis": "Keyset on (chat_id, created_at, id) reads about one page even deep in a large chat. OFFSET on the same index walks the skipped prefix. An index on created_at alone is also selective at a timestamp that only the largest chat has reached, so it is not a substitute for bounding the walk by chat_id. The extra history index increases insert time. A custom-format dump restored into a second Postgres volume matches the snapshot and omits a row committed after pg_dump started.",
"hardware": {
"platform": platform.platform(),
"cpu_count": os.cpu_count(),
"ram_gib": memory_gib(),
"api_limit": "2 CPU, 768 MiB",
},
"postgres": {
"image": "postgres:16.10-alpine",
"version": flat_settings[0] if flat_settings else "",
"synchronous_commit": flat_settings[1] if len(flat_settings) > 1 else "",
"fsync": flat_settings[2] if len(flat_settings) > 2 else "",
},
"redis_persistence": redis_cfg.strip(),
"distribution": {
"messages": before["messages"],
"chats": before["chats"],
"largest_chat": big["messages"],
"empty_chats": sum(1 for item in chats if item["messages"] == 0),
"sizes": sorted({item["messages"] for item in chats}, reverse=True),
},
"queries": {name: metric(plan) for name, plan in plans.items()},
"write": writes,
"backup": {
"dump_seconds": dump_seconds,
"restore_seconds": restore_seconds,
"dump_bytes": len(dump),
"counts_before_dump": before,
"counts_source_after_extra_commit": after_source,
"counts_restored": after_restore,
"sample_match": chosen == restored_sample,
"extra_row_in_restore": int(extra_in_restore[0][0]) if extra_in_restore else None,
},
}
(EVIDENCE / "track2.json").write_text(json.dumps(summary, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
chunks = []
for name, plan in plans.items():
chunks.append("===== " + name + " =====\n" + plan.strip() + "\n")
(EVIDENCE / "track2-explain.txt").write_text("\n".join(chunks), encoding="utf-8")
print(json.dumps({"messages": before["messages"], "queries": summary["queries"], "write": writes, "backup": summary["backup"]}, ensure_ascii=False, indent=2))
if not summary["backup"]["sample_match"]:
raise SystemExit("restored sample differs")
if summary["backup"]["extra_row_in_restore"] != 0:
raise SystemExit("row committed after the dump was restored")
if before["messages"] < 100000:
raise SystemExit("not enough messages")
print("PASS: track2")
if __name__ == "__main__":
main()