forked from mtusi2026/mtusi2026lab2
236 lines
8.4 KiB
Go
236 lines
8.4 KiB
Go
package main
|
|
|
|
import (
|
|
"context"
|
|
"encoding/json"
|
|
"flag"
|
|
"fmt"
|
|
"os"
|
|
"runtime"
|
|
"sort"
|
|
"strconv"
|
|
"strings"
|
|
"time"
|
|
|
|
"github.com/google/uuid"
|
|
"github.com/jackc/pgx/v5"
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
)
|
|
|
|
const experimentSchema = "lab2_experiment"
|
|
|
|
type report struct {
|
|
GeneratedAt string `json:"generated_at"`
|
|
GoVersion string `json:"go_version"`
|
|
Platform string `json:"platform"`
|
|
CPUs int `json:"cpus"`
|
|
Messages int `json:"messages"`
|
|
Distribution []int `json:"chat_distribution"`
|
|
SeedDuration float64 `json:"seed_duration_seconds"`
|
|
Plans []planResult `json:"plans"`
|
|
Writes []writeResult `json:"writes"`
|
|
}
|
|
|
|
type planResult struct {
|
|
Name string `json:"name"`
|
|
DDL string `json:"ddl"`
|
|
Plan json.RawMessage `json:"plan"`
|
|
}
|
|
|
|
type writeResult struct {
|
|
Name string `json:"name"`
|
|
Rows int `json:"rows"`
|
|
SamplesSeconds []float64 `json:"samples_seconds"`
|
|
MedianSeconds float64 `json:"median_seconds"`
|
|
RowsPerSec float64 `json:"rows_per_second"`
|
|
IndexBytes int64 `json:"index_bytes"`
|
|
}
|
|
|
|
func main() {
|
|
if err := run(); err != nil {
|
|
fmt.Fprintln(os.Stderr, err)
|
|
os.Exit(1)
|
|
}
|
|
}
|
|
|
|
func run() error {
|
|
databaseURL := flag.String("database-url", os.Getenv("DATABASE_URL"), "PostgreSQL URL")
|
|
output := flag.String("output", "evidence/lab2-track2.json", "result JSON path")
|
|
messages := flag.Int("messages", 100000, "number of messages")
|
|
writeRows := flag.Int("write-rows", 10000, "rows per write test")
|
|
flag.Parse()
|
|
if *messages < 100000 {
|
|
return fmt.Errorf("messages must be at least 100000")
|
|
}
|
|
if *writeRows < 1000 {
|
|
return fmt.Errorf("write-rows must be at least 1000")
|
|
}
|
|
|
|
if strings.TrimSpace(*databaseURL) == "" {
|
|
return fmt.Errorf("database URL is required")
|
|
}
|
|
pool, err := pgxpool.New(context.Background(), strings.TrimSpace(*databaseURL))
|
|
if err != nil {
|
|
return fmt.Errorf("open database: %w", err)
|
|
}
|
|
defer pool.Close()
|
|
|
|
distribution := messageDistribution(*messages, 20)
|
|
seedStarted := time.Now()
|
|
chatIDs, err := seed(context.Background(), pool, distribution)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
result := report{
|
|
GeneratedAt: time.Now().UTC().Format(time.RFC3339),
|
|
GoVersion: runtime.Version(), Platform: runtime.GOOS + "/" + runtime.GOARCH,
|
|
CPUs: runtime.NumCPU(), Messages: *messages, Distribution: distribution,
|
|
SeedDuration: time.Since(seedStarted).Seconds(),
|
|
}
|
|
result.Plans, err = comparePlans(context.Background(), pool, chatIDs[len(chatIDs)-1], distribution[len(distribution)-1])
|
|
if err != nil {
|
|
return err
|
|
}
|
|
result.Writes, err = compareWrites(context.Background(), pool, *writeRows)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
payload, err := json.MarshalIndent(result, "", " ")
|
|
if err != nil {
|
|
return fmt.Errorf("encode report: %w", err)
|
|
}
|
|
if err := os.WriteFile(*output, append(payload, '\n'), 0o644); err != nil {
|
|
return fmt.Errorf("write report: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func messageDistribution(total, chats int) []int {
|
|
distribution := make([]int, chats)
|
|
distribution[0] = total * 60 / 100
|
|
distribution[1] = total * 20 / 100
|
|
distribution[2] = total * 10 / 100
|
|
remaining := total - distribution[0] - distribution[1] - distribution[2]
|
|
for i := 3; i < chats; i++ {
|
|
distribution[i] = remaining / (chats - 3)
|
|
}
|
|
for assigned := sum(distribution); assigned < total; assigned++ {
|
|
distribution[chats-1]++
|
|
}
|
|
return distribution
|
|
}
|
|
|
|
func sum(values []int) int {
|
|
var result int
|
|
for _, value := range values {
|
|
result += value
|
|
}
|
|
return result
|
|
}
|
|
|
|
func seed(ctx context.Context, pool *pgxpool.Pool, distribution []int) ([]uuid.UUID, error) {
|
|
if _, err := pool.Exec(ctx, `DROP SCHEMA IF EXISTS `+experimentSchema+` CASCADE; CREATE SCHEMA `+experimentSchema+`; CREATE TABLE `+experimentSchema+`.messages (id uuid PRIMARY KEY, chat_id uuid NOT NULL, created_at timestamptz NOT NULL, text text NOT NULL)`); err != nil {
|
|
return nil, fmt.Errorf("create experiment table: %w", err)
|
|
}
|
|
chatIDs := make([]uuid.UUID, len(distribution))
|
|
for i := range chatIDs {
|
|
chatIDs[i] = uuid.NewSHA1(uuid.NameSpaceOID, []byte("chat-"+strconv.Itoa(i)))
|
|
}
|
|
base := time.Date(2026, time.January, 1, 0, 0, 0, 0, time.UTC)
|
|
rows := make([][]any, 0, sum(distribution))
|
|
sequence := 0
|
|
for chatIndex, count := range distribution {
|
|
for i := range count {
|
|
id := uuid.NewSHA1(chatIDs[chatIndex], []byte(strconv.Itoa(i)))
|
|
text := strings.Repeat("m", 32+(i%16)*32)
|
|
sequence = i*len(distribution) + chatIndex
|
|
rows = append(rows, []any{id, chatIDs[chatIndex], base.Add(time.Duration(sequence) * time.Microsecond), text})
|
|
}
|
|
}
|
|
if _, err := pool.CopyFrom(ctx, pgx.Identifier{experimentSchema, "messages"}, []string{"id", "chat_id", "created_at", "text"}, pgx.CopyFromRows(rows)); err != nil {
|
|
return nil, fmt.Errorf("seed messages: %w", err)
|
|
}
|
|
return chatIDs, nil
|
|
}
|
|
|
|
func comparePlans(ctx context.Context, pool *pgxpool.Pool, chatID uuid.UUID, count int) ([]planResult, error) {
|
|
base := time.Date(2026, time.January, 1, 0, 0, 0, 0, time.UTC)
|
|
middle := count / 2
|
|
cursorTime := base.Add(time.Duration(middle*20+19) * time.Microsecond)
|
|
cursorID := uuid.NewSHA1(chatID, []byte(strconv.Itoa(middle)))
|
|
candidates := []struct {
|
|
name string
|
|
ddl string
|
|
}{
|
|
{"history_composite", `CREATE INDEX experiment_history_idx ON ` + experimentSchema + `.messages (chat_id, created_at, id)`},
|
|
{"global_time", `CREATE INDEX experiment_history_idx ON ` + experimentSchema + `.messages (created_at, id)`},
|
|
}
|
|
results := make([]planResult, 0, len(candidates))
|
|
for _, candidate := range candidates {
|
|
if _, err := pool.Exec(ctx, `DROP INDEX IF EXISTS `+experimentSchema+`.experiment_history_idx; `+candidate.ddl+`; ANALYZE `+experimentSchema+`.messages`); err != nil {
|
|
return nil, fmt.Errorf("prepare %s index: %w", candidate.name, err)
|
|
}
|
|
const explain = `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
|
|
SELECT id, chat_id, created_at, text
|
|
FROM lab2_experiment.messages
|
|
WHERE chat_id = $1 AND (created_at, id) > ($2, $3)
|
|
ORDER BY created_at, id
|
|
LIMIT 100`
|
|
var raw []byte
|
|
if err := pool.QueryRow(ctx, explain, chatID, cursorTime, cursorID).Scan(&raw); err != nil {
|
|
return nil, fmt.Errorf("explain %s: %w", candidate.name, err)
|
|
}
|
|
results = append(results, planResult{Name: candidate.name, DDL: candidate.ddl, Plan: json.RawMessage(raw)})
|
|
}
|
|
return results, nil
|
|
}
|
|
|
|
func compareWrites(ctx context.Context, pool *pgxpool.Pool, rows int) ([]writeResult, error) {
|
|
const repetitions = 5
|
|
candidates := []struct {
|
|
name string
|
|
ddl string
|
|
}{
|
|
{"no_secondary_index", ""},
|
|
{"history_composite", `CREATE INDEX write_history_idx ON ` + experimentSchema + `.write_messages (chat_id, created_at, id)`},
|
|
{"global_time", `CREATE INDEX write_history_idx ON ` + experimentSchema + `.write_messages (created_at, id)`},
|
|
}
|
|
results := make([]writeResult, 0, len(candidates))
|
|
for _, candidate := range candidates {
|
|
samples := make([]float64, 0, repetitions)
|
|
var indexBytes int64
|
|
for repetition := range repetitions {
|
|
if _, err := pool.Exec(ctx, `DROP TABLE IF EXISTS `+experimentSchema+`.write_messages; CREATE TABLE `+experimentSchema+`.write_messages (id uuid PRIMARY KEY, chat_id uuid NOT NULL, created_at timestamptz NOT NULL, text text NOT NULL); `+candidate.ddl); err != nil {
|
|
return nil, fmt.Errorf("prepare write test %s: %w", candidate.name, err)
|
|
}
|
|
values := make([][]any, rows)
|
|
base := time.Date(2026, time.January, 1, 0, 0, repetition, 0, time.UTC)
|
|
for i := range rows {
|
|
values[i] = []any{
|
|
uuid.NewSHA1(uuid.NameSpaceOID, []byte(strconv.Itoa(i))),
|
|
uuid.NewSHA1(uuid.NameSpaceURL, []byte(strconv.Itoa(i%20))),
|
|
base.Add(time.Duration(i) * time.Microsecond),
|
|
"message",
|
|
}
|
|
}
|
|
started := time.Now()
|
|
if _, err := pool.CopyFrom(ctx, pgx.Identifier{experimentSchema, "write_messages"}, []string{"id", "chat_id", "created_at", "text"}, pgx.CopyFromRows(values)); err != nil {
|
|
return nil, fmt.Errorf("write test %s: %w", candidate.name, err)
|
|
}
|
|
samples = append(samples, time.Since(started).Seconds())
|
|
if candidate.ddl != "" {
|
|
if err := pool.QueryRow(ctx, `SELECT pg_relation_size($1::regclass)`, experimentSchema+".write_history_idx").Scan(&indexBytes); err != nil {
|
|
return nil, fmt.Errorf("read %s index size: %w", candidate.name, err)
|
|
}
|
|
}
|
|
}
|
|
sort.Float64s(samples)
|
|
median := samples[len(samples)/2]
|
|
results = append(results, writeResult{
|
|
Name: candidate.name, Rows: rows, SamplesSeconds: samples,
|
|
MedianSeconds: median, RowsPerSec: float64(rows) / median, IndexBytes: indexBytes,
|
|
})
|
|
}
|
|
return results, nil
|
|
}
|