Files
mtusi2026lab2/student-tests/track5_query_restore.py

345 lines
14 KiB
Python

#!/usr/bin/env python3
"""Reproducible 100k-message plan/index/write-cost and backup-restore experiment."""
import json
import os
import platform
import secrets
import statistics
import subprocess
import tempfile
import time
from datetime import datetime, timezone
from pathlib import Path
ROOT = Path(__file__).resolve().parents[1]
EVIDENCE = ROOT / "evidence" / "track5_query_restore.json"
MARKDOWN = ROOT / "evidence" / "track5_query_restore.md"
COMPOSE = ["docker", "compose", "--env-file", ".env"]
def run_container(*args, input_text=None, stdin=None, stdout=None, check=True):
result = subprocess.run(
[*COMPOSE, "exec", "-T", "postgres", *args],
cwd=ROOT,
input=input_text,
stdin=stdin,
stdout=stdout if stdout is not None else subprocess.PIPE,
stderr=subprocess.PIPE,
text=input_text is not None,
check=False,
)
if check and result.returncode:
detail = result.stderr.decode("utf-8", "replace") if isinstance(result.stderr, bytes) else result.stderr
raise RuntimeError("PostgreSQL benchmark command failed: " + detail[-1000:].strip())
return result
def psql(database, sql):
result = run_container(
"sh",
"-c",
'psql -X -qAt -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$1"',
"track5",
database,
input_text=sql,
)
return result.stdout.strip()
def stdout_text(result):
if isinstance(result.stdout, bytes):
return result.stdout.decode("utf-8", "replace").strip()
return result.stdout.strip()
def admin(command, database):
return run_container(
"sh",
"-c",
f'{command} -U "$POSTGRES_USER" "$1"',
"track5",
database,
check=command != "dropdb",
)
def plan(database, table, query):
raw = psql(database, f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {query};")
document = json.loads(raw)[0]
root = document["Plan"]
return {
"execution_ms": document["Execution Time"],
"planning_ms": document["Planning Time"],
"actual_rows": root.get("Actual Rows"),
"shared_hit_blocks": root.get("Shared Hit Blocks", 0),
"shared_read_blocks": root.get("Shared Read Blocks", 0),
"plan": root,
}
def container_metadata():
container_id = subprocess.run(
[*COMPOSE, "ps", "-q", "postgres"],
cwd=ROOT,
check=True,
stdout=subprocess.PIPE,
text=True,
).stdout.strip()
inspected = subprocess.run(
["docker", "inspect", "--format", "{{.HostConfig.Memory}} {{.HostConfig.NanoCpus}}", container_id],
check=True,
stdout=subprocess.PIPE,
text=True,
).stdout.strip()
memory, nano_cpus = (int(value) for value in inspected.split())
try:
host_memory = int(
subprocess.run(
["sysctl", "-n", "hw.memsize"],
check=True,
stdout=subprocess.PIPE,
text=True,
).stdout.strip()
)
except (OSError, subprocess.CalledProcessError, ValueError):
host_memory = os.sysconf("SC_PAGE_SIZE") * os.sysconf("SC_PHYS_PAGES")
pg_version = stdout_text(run_container(
"sh",
"-c",
'psql -X -qAt -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT current_setting(\'server_version\')"',
"track5",
))
return {
"host_platform": platform.platform(),
"host_architecture": platform.machine(),
"host_logical_cpus": os.cpu_count(),
"host_memory_bytes": host_memory,
"postgres_image": "postgres:16.4-alpine",
"postgres_version": pg_version,
"postgres_container_memory_limit_bytes": memory,
"postgres_container_cpu_limit_nanos": nano_cpus,
"warmup": "initial data load followed by ANALYZE; plan query is executed 5 times",
}
def dataset_sql():
return """
CREATE TABLE messages_chat_only (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
chat_id integer NOT NULL,
sender_id integer NOT NULL,
text varchar(200) NOT NULL,
created_at timestamptz NOT NULL
);
CREATE TABLE messages_composite (LIKE messages_chat_only INCLUDING ALL);
INSERT INTO messages_chat_only (chat_id, sender_id, text, created_at)
SELECT CASE WHEN n <= 80000 THEN 1 ELSE 2 + ((n - 80001) % 100) END,
1 + (n % 3),
'synthetic-message-' || lpad(n::text, 6, '0'),
timestamptz '2025-01-01 00:00:00+00' + n * interval '1 second'
FROM generate_series(1, 100000) AS n;
INSERT INTO messages_composite (chat_id, sender_id, text, created_at)
SELECT chat_id, sender_id, text, created_at FROM messages_chat_only ORDER BY id;
CREATE INDEX messages_chat_only_idx ON messages_chat_only (chat_id);
CREATE INDEX messages_chat_history_idx ON messages_composite (chat_id, created_at, id);
ANALYZE messages_chat_only;
ANALYZE messages_composite;
"""
def history_query(table):
return (
f"SELECT id, created_at, text FROM {table} "
"WHERE chat_id = 2 "
"AND (created_at, id) > (timestamptz '2025-01-01 23:36:40+00', 0) "
"ORDER BY created_at, id LIMIT 25"
)
def write_samples(database, table):
sql = (
"BEGIN; "
"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) "
f"INSERT INTO {table} (chat_id, sender_id, text, created_at) "
"SELECT 2 + (n % 100), 1, 'synthetic-write-' || n::text, "
"timestamptz '2025-01-01 00:00:00+00' + n * interval '1 second' "
"FROM generate_series(100001, 102000) AS n; "
"ROLLBACK;"
)
samples = []
for _ in range(5):
raw = psql(database, sql)
document = json.loads(raw)[0]
samples.append(
{
"execution_ms": document["Execution Time"],
"planning_ms": document["Planning Time"],
"shared_write_blocks": document["Plan"].get("Shared Written Blocks", 0),
}
)
return {
"samples": samples,
"median_execution_ms": statistics.median(item["execution_ms"] for item in samples),
"median_shared_write_blocks": statistics.median(item["shared_write_blocks"] for item in samples),
"rows_per_sample": 2000,
"rolled_back_after_each_measurement": True,
}
def render_markdown(result):
small = result["query_plans"]
writes = result["write_cost"]
restore = result["backup_restore"]
return f"""# Lab2 level-5 track 2 — query plans and recovery
- Run UTC: {result['run_utc']}
- Host: {result['environment']['host_platform']} ({result['environment']['host_architecture']}), logical CPUs: {result['environment']['host_logical_cpus']}
- Host physical memory: {result['environment']['host_memory_bytes']} bytes.
- PostgreSQL: {result['environment']['postgres_version']} in `{result['environment']['postgres_image']}`; container memory limit: {result['environment']['postgres_container_memory_limit_bytes']} bytes; CPU limit: {result['environment']['postgres_container_cpu_limit_nanos']} nanocores (0 means unlimited).
- Dataset: 100,000 synthetic rows in each comparison table; one chat has 80,000 rows and 100 small chats share the remaining 20,000. Only generated text is used.
- Query: keyset page from chat 2, identical SELECT and cursor for both indexes; each plan was executed five times after ANALYZE.
| Index | Median execution (ms) | Shared hits | Shared reads | Rows |
|---|---:|---:|---:|---:|
| `(chat_id)` | {statistics.median(item['execution_ms'] for item in small['chat_id_only']['samples']):.3f} | {small['chat_id_only']['samples'][0]['shared_hit_blocks']} | {small['chat_id_only']['samples'][0]['shared_read_blocks']} | {small['chat_id_only']['samples'][0]['actual_rows']} |
| `(chat_id, created_at, id)` | {statistics.median(item['execution_ms'] for item in small['composite_history']['samples']):.3f} | {small['composite_history']['samples'][0]['shared_hit_blocks']} | {small['composite_history']['samples'][0]['shared_read_blocks']} | {small['composite_history']['samples'][0]['actual_rows']} |
| Index | Median time for 2,000 inserted rows (ms) | Median shared write blocks |
|---|---:|---:|
| `(chat_id)` | {writes['chat_id_only']['median_execution_ms']:.3f} | {writes['chat_id_only']['median_shared_write_blocks']} |
| `(chat_id, created_at, id)` | {writes['composite_history']['median_execution_ms']:.3f} | {writes['composite_history']['median_shared_write_blocks']} |
Each write-cost query was measured five times inside a transaction and rolled back. This isolates execution and index-maintenance work but does not measure durable commit/fsync latency.
Backup/restore: custom-format dump {restore['dump_bytes']} bytes; dump {restore['dump_seconds']:.3f}s; restore into a separately created empty database {restore['restore_seconds']:.3f}s. Restored row counts and deterministic checksums for the selected messages were verified: **PASS**.
Raw JSON plans, samples, environment and restore checksums are in `track5_query_restore.json`. This single-host synthetic run is a reproducible comparison, not a production capacity or HA claim.
"""
def main():
tag = secrets.token_hex(5)
source = "lab2_track5_source_" + tag
target = "lab2_track5_restore_" + tag
created = []
try:
for database in (source, target):
admin("createdb", database)
created.append(database)
psql(source, dataset_sql())
select_chat = "SELECT id, chat_id, sender_id, text, created_at FROM {table} WHERE chat_id IN (1, 2) ORDER BY chat_id, created_at, id"
plans = {
"chat_id_only": [
plan(source, "messages_chat_only", history_query("messages_chat_only"))
for _ in range(5)
],
"composite_history": [
plan(source, "messages_composite", history_query("messages_composite"))
for _ in range(5)
],
}
writes = {
"chat_id_only": write_samples(source, "messages_chat_only"),
"composite_history": write_samples(source, "messages_composite"),
}
selected_hash = {}
for table in ("messages_chat_only", "messages_composite"):
selected_hash[table] = psql(
source,
"SELECT md5(string_agg(md5(row_to_json(r)::text), '' ORDER BY r.chat_id, r.created_at, r.id)) "
f"FROM ({select_chat.format(table=table)}) AS r;",
)
with tempfile.NamedTemporaryFile(prefix="lab2-track5-", suffix=".dump") as dump_file:
started = time.monotonic()
dump = run_container(
"sh",
"-c",
'pg_dump -U "$POSTGRES_USER" -Fc "$1"',
"track5",
source,
stdout=dump_file,
)
if dump.returncode:
raise RuntimeError("PostgreSQL benchmark backup failed")
dump_seconds = time.monotonic() - started
dump_file.flush()
dump_size = os.fstat(dump_file.fileno()).st_size
started = time.monotonic()
dump_file.seek(0)
restore = run_container(
"sh",
"-c",
'pg_restore -U "$POSTGRES_USER" --exit-on-error -d "$1"',
"track5",
target,
stdin=dump_file,
stdout=subprocess.DEVNULL,
)
if restore.returncode:
raise RuntimeError("PostgreSQL benchmark restore failed")
restore_seconds = time.monotonic() - started
restored_counts = {
table: int(psql(target, f"SELECT count(*) FROM {table};"))
for table in ("messages_chat_only", "messages_composite")
}
restored_hashes = {
table: psql(
target,
"SELECT md5(string_agg(md5(row_to_json(r)::text), '' ORDER BY r.chat_id, r.created_at, r.id)) "
f"FROM ({select_chat.format(table=table)}) AS r;",
)
for table in ("messages_chat_only", "messages_composite")
}
if any(count != 100000 for count in restored_counts.values()):
raise RuntimeError("Restored message counts do not match the 100,000-row source tables")
if restored_hashes != selected_hash:
raise RuntimeError("Restored selected-message checksums differ from the source")
env = container_metadata()
env["postgres_memory_setting"] = psql(source, "SHOW shared_buffers;")
env["postgres_work_mem"] = psql(source, "SHOW work_mem;")
result = {
"run_utc": datetime.now(timezone.utc).isoformat(),
"environment": env,
"method": {
"rows_per_index_variant": 100000,
"distribution": "80,000 messages in one chat; 20,000 distributed evenly across 100 chats",
"warmup": "ANALYZE followed by five executed EXPLAIN ANALYZE samples per query",
"query": history_query("messages"),
},
"query_plans": {
"chat_id_only": {"index": "(chat_id)", "samples": plans["chat_id_only"]},
"composite_history": {
"index": "(chat_id, created_at, id)",
"samples": plans["composite_history"],
},
},
"write_cost": writes,
"backup_restore": {
"format": "pg_dump custom format",
"dump_bytes": dump_size,
"dump_seconds": dump_seconds,
"restore_seconds": restore_seconds,
"restored_counts": restored_counts,
"source_selected_checksums": selected_hash,
"restored_selected_checksums": restored_hashes,
"verification": "PASS",
},
}
EVIDENCE.write_text(json.dumps(result, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
MARKDOWN.write_text(render_markdown(result), encoding="utf-8")
print("PASS: level-5 track 2; evidence written to evidence/track5_query_restore.md")
finally:
for database in reversed(created):
admin("dropdb", database)
if __name__ == "__main__":
try:
main()
except (OSError, subprocess.SubprocessError, ValueError, KeyError, RuntimeError) as exc:
print("FAIL:", str(exc))
raise SystemExit(1)