Store message text as UTF-8 bytes and migrate existing rows without loss. Cover HTTP round trips, restart persistence, Unicode limits and idempotent retries. Adapt direct database tools to the binary storage format.
129 lines
6.4 KiB
Python
129 lines
6.4 KiB
Python
"""Reproducible 100k-row history-index and insert-cost experiment."""
|
|
|
|
import csv
|
|
import json
|
|
import secrets
|
|
import sys
|
|
from datetime import datetime, timezone
|
|
from time import perf_counter
|
|
from uuid import uuid4
|
|
|
|
import psycopg
|
|
from psycopg.rows import dict_row
|
|
|
|
from src.db import database_url
|
|
from src.store import Store
|
|
|
|
|
|
HISTORY = "CREATE INDEX messages_history_idx ON messages (chat_id, created_at, id)"
|
|
COVERING = (
|
|
"CREATE INDEX messages_history_covering_idx ON messages "
|
|
"(chat_id, created_at, id) INCLUDE (sender_id, text)"
|
|
)
|
|
PROJECTION = "SELECT id,chat_id,sender_id,text,created_at FROM messages WHERE chat_id=%s"
|
|
|
|
|
|
def explain(conn, query, params):
|
|
result = conn.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + query, params).fetchone()["QUERY PLAN"][0]
|
|
plan = result["Plan"]
|
|
names = []
|
|
|
|
def visit(node):
|
|
names.append(node.get("Index Name", node["Node Type"]))
|
|
for child in node.get("Plans", []):
|
|
visit(child)
|
|
|
|
visit(plan)
|
|
return result, " > ".join(names)
|
|
|
|
|
|
def main():
|
|
store = Store()
|
|
user = store.register("track2_" + uuid4().hex[:16], "Synthetic benchmark", secrets.token_urlsafe(18))
|
|
sizes = (("hot", 80_000), ("medium", 15_000), ("cold", 5_000))
|
|
chats = [(name, size, store.create_chat(user["id"], "Track 2 " + name, [user["id"]])["id"])
|
|
for name, size in sizes]
|
|
write_chat = store.create_chat(user["id"], "Track 2 writes", [user["id"]])["id"]
|
|
with psycopg.connect(database_url(), autocommit=True, row_factory=dict_row) as conn:
|
|
conn.execute("SET statement_timeout=0")
|
|
base = datetime.now(timezone.utc)
|
|
for name, size, chat_id in chats:
|
|
conn.execute(
|
|
"INSERT INTO messages (id,chat_id,sender_id,text,created_at) "
|
|
"SELECT gen_random_uuid(),%s,%s,convert_to(%s || n, 'UTF8'),%s::timestamptz + n * interval '1 microsecond' "
|
|
"FROM generate_series(1,%s) n",
|
|
(chat_id, user["id"], name + "-synthetic-", base, size),
|
|
)
|
|
conn.execute("VACUUM (ANALYZE) messages")
|
|
total = sum(conn.execute("SELECT count(*) AS n FROM messages WHERE chat_id=%s", (chat_id,))
|
|
.fetchone()["n"] for _, _, chat_id in chats)
|
|
assert total == 100_000
|
|
hot_id = chats[0][2]
|
|
cursor = conn.execute(
|
|
"SELECT created_at,id FROM messages WHERE chat_id=%s "
|
|
"ORDER BY created_at,id OFFSET 71999 LIMIT 1", (hot_id,)
|
|
).fetchone()
|
|
queries = (
|
|
("first", PROJECTION + " ORDER BY created_at,id LIMIT 51", (hot_id,)),
|
|
("deep", PROJECTION + " AND (created_at,id)>(%s,%s) "
|
|
"ORDER BY created_at,id LIMIT 51", (hot_id, cursor["created_at"], cursor["id"])),
|
|
)
|
|
writer = csv.writer(sys.stdout, lineterminator="\n")
|
|
writer.writerow(("variant", "index", "operation", "trial", "dataset_rows", "execution_ms",
|
|
"shared_hit", "shared_read", "shared_dirtied", "plan", "rows_inserted"))
|
|
print(f"UTC: {datetime.now(timezone.utc).isoformat()}", file=sys.stderr)
|
|
print(f"PostgreSQL: {conn.execute('SHOW server_version').fetchone()['server_version']}", file=sys.stderr)
|
|
print("Synthetic chat sizes: hot=80000, medium=15000, cold=5000; fixed 51-row pages; deep cursor=72000", file=sys.stderr)
|
|
try:
|
|
conn.execute("DROP INDEX IF EXISTS messages_history_covering_idx")
|
|
conn.execute(HISTORY.replace("CREATE INDEX", "CREATE INDEX IF NOT EXISTS", 1))
|
|
for variant, index in (("A", "messages_history_idx"),
|
|
("B", "messages_history_covering_idx")):
|
|
if variant == "B":
|
|
conn.execute("DROP INDEX messages_history_idx")
|
|
conn.execute(COVERING)
|
|
conn.execute("VACUUM (ANALYZE) messages")
|
|
print(f"Variant {variant}: {index}", file=sys.stderr)
|
|
for label, query, params in queries:
|
|
expected = [row["id"] for row in conn.execute(query, params).fetchall()]
|
|
assert len(expected) == 51
|
|
for _ in range(3):
|
|
explain(conn, query, params)
|
|
for trial in range(1, 6):
|
|
result, plan = explain(conn, query, params)
|
|
actual = [row["id"] for row in conn.execute(query, params).fetchall()]
|
|
assert actual == expected
|
|
node = result["Plan"]
|
|
writer.writerow((variant, index, label, trial, total, result["Execution Time"],
|
|
node.get("Shared Hit Blocks", 0), node.get("Shared Read Blocks", 0),
|
|
node.get("Shared Dirtied Blocks", 0), plan, 0))
|
|
if trial == 1:
|
|
print(json.dumps({"variant": variant, "operation": label, "plan": node},
|
|
default=str), file=sys.stderr)
|
|
# Same 500-row write statement on a separate synthetic chat under each index.
|
|
# Five committed repetitions include index maintenance and commit time.
|
|
for trial in range(1, 6):
|
|
start = perf_counter()
|
|
with conn.transaction():
|
|
conn.execute(
|
|
"INSERT INTO messages (id,chat_id,sender_id,text,created_at) "
|
|
"SELECT gen_random_uuid(),%s,%s,convert_to('write-synthetic-' || n, 'UTF8'),"
|
|
"clock_timestamp() + n * interval '1 microsecond' "
|
|
"FROM generate_series(1,500) n",
|
|
(write_chat, user["id"]),
|
|
)
|
|
elapsed = (perf_counter() - start) * 1000
|
|
writer.writerow((variant, index, "write500", trial, total, round(elapsed, 3),
|
|
"", "", "", "INSERT 500 + COMMIT", 500))
|
|
finally:
|
|
conn.execute("DROP INDEX IF EXISTS messages_history_covering_idx")
|
|
conn.execute(HISTORY.replace("CREATE INDEX", "CREATE INDEX IF NOT EXISTS", 1))
|
|
assert conn.execute(
|
|
"SELECT count(*) AS n FROM messages WHERE chat_id=%s", (write_chat,)
|
|
).fetchone()["n"] == 5000
|
|
print("Original history index restored; committed write rows=5000", file=sys.stderr)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|