Files
mtusi2026lab2/student-tests/index_study.py

119 lines
5.5 KiB
Python

"""Runs inside the API image, against a separate synthetic research database."""
import datetime as dt
import hashlib
import json
import os
from pathlib import Path
import statistics
import sys
import time
import uuid
import psycopg
from psycopg import sql
from psycopg.conninfo import make_conninfo
ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT / 'src'))
from postgres import setting
def snapshot(conn):
counts = {}
for table in ('users', 'chats', 'chat_members', 'messages', 'sessions'):
counts[table] = conn.execute(sql.SQL('SELECT count(*) FROM {}').format(sql.Identifier(table))).fetchone()[0]
digest = hashlib.sha256()
selected = []
with conn.cursor(name='checksum') as cur:
cur.execute('SELECT row_to_json(m)::text FROM messages m ORDER BY id')
for i, (row,) in enumerate(cur):
digest.update(row.encode() + b'\n')
if i in (0, 1, 50000, 99998, 99999):
selected.append(json.loads(row))
return dict(counts=counts, messages_sha256=digest.hexdigest(), selected_messages=selected)
def main():
url = setting('DATABASE_URL')
if '--verify' in sys.argv:
with psycopg.connect(make_conninfo(url, dbname=os.environ['RESEARCH_DATABASE'], host=os.environ['RESEARCH_HOST'])) as conn:
print(json.dumps(snapshot(conn)))
return
database = 'research_' + uuid.uuid4().hex[:12]
with psycopg.connect(url, autocommit=True) as admin:
admin.execute(sql.SQL('CREATE DATABASE {}').format(sql.Identifier(database)))
conn = psycopg.connect(make_conninfo(url, dbname=database))
for migration in sorted((ROOT / 'migrations').glob('*.sql')):
conn.execute(migration.read_text())
user = uuid.uuid4()
conn.execute('INSERT INTO users (id, username, display_name, password_hash) VALUES (%s, %s, %s, %s)', (user, 'research_user', b'Synthetic', 'not-an-authenticatable-password'))
sizes = [70000, 20000, 5000, 2000, 1000, 800, 500, 300, 200, 200]
chats = [uuid.uuid4() for _ in sizes]
base = dt.datetime(2026, 1, 1, tzinfo=dt.timezone.utc)
for chat in chats:
conn.execute('INSERT INTO chats (id, title) VALUES (%s, %s)', (chat, b'Synthetic'))
conn.execute('INSERT INTO chat_members (chat_id, user_id) VALUES (%s, %s)', (chat, user))
with conn.cursor().copy('COPY messages (id, chat_id, sender_id, text, created_at) FROM STDIN') as copy:
for chat, count in zip(chats, sizes):
for i in range(count):
first = chat == chats[0] and i == 0
mid = uuid.UUID(int=0) if first else uuid.uuid4()
content = b'A\x00B' + b'x' * 61 if first else b'x' * 64
copy.write_row((mid, chat, user, content, base + dt.timedelta(microseconds=i)))
conn.commit()
results = []
query = '''
SELECT
id
, chat_id
, sender_id
, text
, created_at
FROM messages
WHERE 1=1
AND chat_id = %s
AND (created_at, id) > (%s, %s)
ORDER BY created_at, id
LIMIT 51
'''
for variant, columns in (('chat_only', 'chat_id'), ('composite', 'chat_id, created_at, id')):
conn.execute('DROP INDEX messages_history')
# The two SQL fragments are constants, never request input.
conn.execute('CREATE INDEX messages_history ON messages (' + columns + ')')
conn.execute('ANALYZE messages')
conn.commit()
reads = []
for position in (0, 1, 9):
params = (chats[position], base + dt.timedelta(microseconds=sizes[position] * 4 // 5), uuid.UUID(int=0))
conn.execute(query, params).fetchall() # Warm the same page before both variants.
plans = [conn.execute('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' + query, params).fetchone()[0][0] for _ in range(5)]
reads.append(dict(chat_messages=sizes[position], median_execution_ms=statistics.median(v['Execution Time'] for v in plans), plans=plans))
conn.commit()
writes = []
for repeat in range(3):
ids = [uuid.uuid4() for _ in range(1000)]
before = conn.execute('SELECT pg_current_wal_insert_lsn()').fetchone()[0]
conn.commit()
started = time.perf_counter()
with conn.cursor().copy('COPY messages (id, chat_id, sender_id, text, created_at) FROM STDIN') as copy:
for i, mid in enumerate(ids):
copy.write_row((mid, chats[0], user, b'write-cost', base + dt.timedelta(seconds=1, microseconds=i)))
conn.commit()
elapsed = (time.perf_counter() - started) * 1000
wal = conn.execute('SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), %s::pg_lsn)', (before,)).fetchone()[0]
writes.append(dict(rows=1000, commit_ms=elapsed, wal_bytes=int(wal)))
conn.execute('DELETE FROM messages WHERE 1=1 AND id = ANY(%s::uuid[])', (ids,))
conn.commit()
index_size = conn.execute("SELECT pg_relation_size('messages_history')").fetchone()[0]
results.append(dict(variant=variant, index_bytes=index_size, reads=reads, writes=writes))
captured = snapshot(conn)
assert captured['counts']['messages'] == 100000
assert captured['selected_messages'][0]['text'] == '\\x' + (b'A\x00B' + b'x' * 61).hex()
version = conn.execute('SELECT version()').fetchone()[0]
conn.close()
print(json.dumps(dict(database=database, postgresql=version, sizes=sizes, query=query, repeats=5, results=results, snapshot=captured)))
if __name__ == '__main__':
main()