commit 7b32df42f3d0a5393ed63c6802ffb9ce9000fee7
parent 2df5315bdbd11a0926e069605c4395c8a831bac6
Author: Silas Brack <silasbrack@gmail.com>
Date: Sat, 27 Jun 2026 22:45:48 +0200
perf: use composite primary keys for vote and story_category
Replace surrogate integer PKs with structured composite PKs on tables
where the surrogate id was never referenced:
- vote: PRIMARY KEY (user_id, target_type, target_id), WITHOUT ROWID
Eliminates the separate UNIQUE index — one B-tree instead of two.
- story_category: PRIMARY KEY (news_item_id, category_id), WITHOUT ROWID
Also adds a uniqueness guarantee that was previously missing.
At 1M votes this halves DB size (59 → 26 MB) and improves vote write
throughput ~12-16% at steady state.
Also adds warmup phase and minimum request count to bench.sh to avoid
cold-cache measurement artifacts at low concurrency.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
3 files changed, 88 insertions(+), 0 deletions(-)
diff --git a/bench.sh b/bench.sh
@@ -60,8 +60,12 @@ SELECT 'comments: ' || COUNT(*) FROM comment;
SELECT 'users: ' || COUNT(*) FROM user;
"
+MIN_REQUESTS=200
+
run() {
local label=$1 url=$2 c=$3 n=${4:-$((c * 50))} method=${5:-GET} extra="${6:-}"
+ # Ensure enough requests for stable measurement
+ if (( n < MIN_REQUESTS )); then n=$MIN_REQUESTS; fi
local result
result=$($HEY -n "$n" -c "$c" -m "$method" $extra "$url" 2>&1)
local rps=$(echo "$result" | grep "Requests/sec" | awk '{print $2}')
@@ -70,6 +74,12 @@ run() {
printf "%-20s c=%-4s %10s rps avg=%ss p99=%ss\n" "$label" "$c" "$rps" "$avg" "$p99"
}
+# Warmup: prime page caches and mmap regions before measuring
+echo ""
+echo "Warming up..."
+$HEY -n 500 -c 50 "$BASE/" > /dev/null 2>&1
+$HEY -n 200 -c 10 -m POST -H "Cookie:$COOKIE" "$BASE/vote/story/1" > /dev/null 2>&1
+
echo ""
echo "=== READS ==="
for c in 10 100 500; do run "homepage" "$BASE/" $c; done
diff --git a/migrations/0007_structured_pks.sql b/migrations/0007_structured_pks.sql
@@ -0,0 +1,77 @@
+-- Replace surrogate primary keys with structured/composite primary keys
+-- on junction/association tables where the surrogate id is never referenced.
+--
+-- vote: PK becomes (user_id, target_type, target_id) — eliminates the
+-- separate UNIQUE index and the unused autoincrement id.
+-- story_category: PK becomes (news_item_id, category_id) — also adds
+-- a uniqueness guarantee that was previously missing.
+
+PRAGMA foreign_keys=OFF;
+BEGIN IMMEDIATE;
+
+-- Drop vote triggers (they reference the old table)
+DROP TRIGGER IF EXISTS vote_insert_story;
+DROP TRIGGER IF EXISTS vote_delete_story;
+DROP TRIGGER IF EXISTS vote_insert_comment;
+DROP TRIGGER IF EXISTS vote_delete_comment;
+
+-- Drop vote index (the composite PK replaces it)
+DROP INDEX IF EXISTS idx_vote_user_target;
+
+-- Recreate vote with composite PK
+ALTER TABLE vote RENAME TO vote_old;
+CREATE TABLE vote (
+ user_id INTEGER NOT NULL REFERENCES user(id),
+ target_type INTEGER NOT NULL,
+ target_id INTEGER NOT NULL,
+ created_at INTEGER NOT NULL,
+ PRIMARY KEY (user_id, target_type, target_id)
+) STRICT, WITHOUT ROWID;
+INSERT INTO vote (user_id, target_type, target_id, created_at)
+SELECT user_id, target_type, target_id, created_at FROM vote_old;
+DROP TABLE vote_old;
+
+-- Drop story_category indices
+DROP INDEX IF EXISTS idx_story_category_news_item;
+DROP INDEX IF EXISTS idx_story_category_category;
+
+-- Recreate story_category with composite PK
+ALTER TABLE story_category RENAME TO story_category_old;
+CREATE TABLE story_category (
+ news_item_id INTEGER NOT NULL REFERENCES story(id),
+ category_id INTEGER NOT NULL REFERENCES category(id),
+ PRIMARY KEY (news_item_id, category_id)
+) STRICT, WITHOUT ROWID;
+INSERT OR IGNORE INTO story_category (news_item_id, category_id)
+SELECT news_item_id, category_id FROM story_category_old;
+DROP TABLE story_category_old;
+
+-- Recreate index for looking up stories by category
+-- (PK covers news_item_id lookups, but category_id lookups need this)
+CREATE INDEX idx_story_category_category ON story_category(category_id);
+
+-- Recreate vote triggers
+CREATE TRIGGER vote_insert_story AFTER INSERT ON vote WHEN new.target_type = 1 BEGIN
+ UPDATE story SET score = score + 1 WHERE id = new.target_id;
+ UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM story WHERE id = new.target_id);
+END;
+
+CREATE TRIGGER vote_delete_story AFTER DELETE ON vote WHEN old.target_type = 1 BEGIN
+ UPDATE story SET score = score - 1 WHERE id = old.target_id;
+ UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM story WHERE id = old.target_id);
+END;
+
+CREATE TRIGGER vote_insert_comment AFTER INSERT ON vote WHEN new.target_type = 2 BEGIN
+ UPDATE comment SET score = score + 1 WHERE id = new.target_id;
+ UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM comment WHERE id = new.target_id);
+END;
+
+CREATE TRIGGER vote_delete_comment AFTER DELETE ON vote WHEN old.target_type = 2 BEGIN
+ UPDATE comment SET score = score - 1 WHERE id = old.target_id;
+ UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id);
+END;
+
+UPDATE migration_version SET version = 7;
+COMMIT;
+
+PRAGMA foreign_keys=ON;
diff --git a/src/migrations.rs b/src/migrations.rs
@@ -7,6 +7,7 @@ const MIGRATIONS: &[&str] = &[
include_str!("../migrations/0004_sessions.sql"),
include_str!("../migrations/0005_epoch_timestamps.sql"),
include_str!("../migrations/0006_fts_vocab.sql"),
+ include_str!("../migrations/0007_structured_pks.sql"),
];
pub fn run(conn: &Connection) -> Result<(), String> {
