Files
HellsTomatoandCopilot 189847708f Добавить исследовательский трек уровня 5
Добавить сравнение планов PostgreSQL и проверку backup-восстановления.

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-10-21 15:07:43 +03:00

146 lines
5.8 KiB
Python

#!/usr/bin/env python3
"""Repeatable level-5 track: query plans, write cost and PostgreSQL restore."""
import csv
import io
import json
import os
import subprocess
import tempfile
import time
import uuid
COMPOSE = ['docker', 'compose']
RESTORE = 'mtusi2026lab2-track2-restore'
def run(command, data=None, check=True):
result = subprocess.run(command, input=data, capture_output=True, check=check)
return result.stdout.decode('utf-8', errors='replace')
def psql(sql, database='messenger'):
return run(COMPOSE + ['exec', '-T', 'postgres', 'psql', '-U', 'messenger',
'-d', database, '-At', '-c', sql]).strip()
def explain(sql):
output = psql(sql)
return json.loads(output[output.index('['):])[0]
def copy_messages(rows):
data = io.StringIO(newline='')
writer = csv.writer(data, lineterminator='\n')
writer.writerows(rows)
run(COMPOSE + ['exec', '-T', 'postgres', 'psql', '-U', 'messenger',
'-d', 'messenger', '-c',
'COPY messages(id, chat_id, sender_id, text, created_at) FROM STDIN WITH (FORMAT csv)'],
data.getvalue().encode())
def restore_psql(sql):
return run(['docker', 'exec', '-i', RESTORE, 'psql', '-U', 'postgres',
'-d', 'messenger', '-At', '-c', sql]).strip()
def main():
suffix = uuid.uuid4().hex[:10]
user_id = str(uuid.uuid4())
chat_id = str(uuid.uuid4())
write_chat_id = str(uuid.uuid4())
setup = f"""
INSERT INTO users(id, username, display_name, password_hash, created_at)
VALUES ('{user_id}', 'track2_{suffix}', 'Track 2', 'not-used', now());
INSERT INTO chats(id, title, created_at)
VALUES ('{chat_id}', 'Track 2 large chat', now()),
('{write_chat_id}', 'Track 2 write benchmark', now());
INSERT INTO chat_members(chat_id, user_id)
VALUES ('{chat_id}', '{user_id}'), ('{write_chat_id}', '{user_id}');
"""
psql(setup)
print('Generating 100000 messages with a skewed chat distribution...')
csv_data = io.StringIO(newline='')
writer = csv.writer(csv_data, lineterminator='\n')
for number in range(100_000):
message_chat = chat_id if number < 90_000 else write_chat_id
writer.writerow([str(uuid.uuid4()), message_chat, user_id,
'track2 message ' + str(number),
f'2026-01-01 00:00:{number % 60:02d}.000000+00'])
run(COMPOSE + ['exec', '-T', 'postgres', 'psql', '-U', 'messenger',
'-d', 'messenger', '-c',
'COPY messages(id, chat_id, sender_id, text, created_at) FROM STDIN WITH (FORMAT csv)'],
csv_data.getvalue().encode())
query = ("SELECT id, chat_id, sender_id, text, created_at FROM messages "
f"WHERE chat_id = '{chat_id}' ORDER BY created_at, id LIMIT 50")
indexed = explain('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' + query)
without_index = explain(
"SET enable_indexscan=off; SET enable_bitmapscan=off; "
"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + query)
print('Indexed plan:')
print(json.dumps(indexed, ensure_ascii=False))
print('Sequential-scan comparison:')
print(json.dumps(without_index, ensure_ascii=False))
rows = [(str(uuid.uuid4()), write_chat_id, user_id,
'write benchmark ' + str(number), '2026-02-01 00:00:00+00')
for number in range(1_000)]
started = time.perf_counter()
copy_messages(rows)
with_index_ms = (time.perf_counter() - started) * 1000
psql('DROP INDEX messages_history')
rows = [(str(uuid.uuid4()), write_chat_id, user_id,
'write without index ' + str(number), '2026-02-02 00:00:00+00')
for number in range(1_000)]
started = time.perf_counter()
copy_messages(rows)
without_index_ms = (time.perf_counter() - started) * 1000
psql('CREATE INDEX messages_history ON messages(chat_id, created_at, id)')
print(f'1000 inserts with index: {with_index_ms:.1f} ms')
print(f'1000 inserts without index: {without_index_ms:.1f} ms')
backup_path = os.path.join(tempfile.gettempdir(), 'mtusi2026lab2-track2.sql')
backup = run(COMPOSE + ['exec', '-T', 'postgres', 'pg_dump', '-U',
'messenger', '-d', 'messenger'])
with open(backup_path, 'w', encoding='utf-8', newline='') as file:
file.write(backup)
before = psql('SELECT count(*) FROM messages')
selected_id = psql(f"SELECT id FROM messages WHERE chat_id='{chat_id}' ORDER BY id LIMIT 1")
try:
run(['docker', 'rm', '-f', RESTORE], check=False)
run(['docker', 'run', '-d', '--name', RESTORE, '-e',
'POSTGRES_PASSWORD=restore', '-e', 'POSTGRES_DB=messenger',
'postgres:16.4'])
for _ in range(30):
try:
run(['docker', 'exec', RESTORE, 'pg_isready', '-U', 'postgres'])
break
except subprocess.CalledProcessError:
time.sleep(1)
restore_start = time.perf_counter()
run(['docker', 'exec', '-i', RESTORE, 'psql', '-U', 'postgres',
'-d', 'messenger'], backup.encode())
restore_seconds = time.perf_counter() - restore_start
after = restore_psql('SELECT count(*) FROM messages')
restored_id = restore_psql(
f"SELECT id FROM messages WHERE id='{selected_id}'")
print(f'Backup file: {backup_path}')
print(f'Messages before backup: {before}')
print(f'Messages after restore: {after}')
print(f'Selected message restored: {selected_id == restored_id}')
print(f'Restore time: {restore_seconds:.2f} s')
if before != after or selected_id != restored_id:
raise SystemExit('FAIL: restored data does not match source')
finally:
run(['docker', 'rm', '-f', RESTORE], check=False)
print('PASS: track 2 query-plan and backup/restore evidence')
if __name__ == '__main__':
main()