Добавить сравнение планов PostgreSQL и проверку backup-восстановления. Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
146 lines
5.8 KiB
Python
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()
|