MuseHub — Database Query Runbook

Run these EXPLAIN ANALYZE checks before each production deployment and after any schema or index change. No sequential scans on tables expected to hold more than ~10 k rows.

How to run

# Connect to the production DB:
psql $DATABASE_URL

# For each query below, prepend EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT):
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...

Look for:

• Seq Scan on large tables — add an index if present
• Rows Removed by Filter >> Rows — index selectivity is poor
• actual time= values > 100 ms — investigate or add a covering index

Top-10 highest-traffic queries

Q1 — Repo lookup by URL slug (every page load)

SELECT * FROM musehub_repos
WHERE owner = 'gabriel' AND slug = 'neo-soul'
  AND deleted_at IS NULL;

Expected plan: Index Scan using uq_musehub_repos_owner_slug

Q2 — List repos for a user (dashboard)

SELECT * FROM musehub_repos
WHERE owner = 'gabriel' AND visibility = 'public'
  AND deleted_at IS NULL
ORDER BY pushed_at DESC
LIMIT 20;

Expected plan: Index Scan using ix_musehub_repos_owner_visibility

Q3 — HEAD commit for a branch (every push/fetch)

SELECT * FROM musehub_commits
WHERE repo_id = '<repo_uuid>' AND branch = 'main'
ORDER BY timestamp DESC
LIMIT 1;

Expected plan: Index Scan using ix_musehub_commits_repo_branch

Q4 — Open issue list (issue tracker)

SELECT * FROM musehub_issues
WHERE repo_id = '<repo_uuid>' AND state = 'open'
ORDER BY created_at DESC
LIMIT 25;

Expected plan: Index Scan using ix_musehub_issues_repo_state

Q5 — Issue by number (direct URL)

SELECT * FROM musehub_issues
WHERE repo_id = '<repo_uuid>' AND number = 42;

Expected plan: Index Scan using ix_musehub_issues_repo_number

Q6 — Open proposals (Proposal list)

SELECT * FROM musehub_proposals
WHERE repo_id = '<repo_uuid>' AND state = 'open'
ORDER BY created_at DESC
LIMIT 25;

Expected plan: Index Scan using ix_musehub_proposals_repo_state

Q7 — Intel result lookup (intel endpoints)

SELECT * FROM musehub_intel_results
WHERE repo_id = '<repo_uuid>' AND intel_type = 'code.symbol_history';

Expected plan: Index Scan using ix_musehub_intel_results_repo_type

SLOW QUERY threshold: 100 ms. Alert if actual time exceeds threshold.

Q8 — Per-repo storage quota sum (every push)

SELECT COALESCE(SUM(o.size_bytes), 0)
FROM musehub_objects o
JOIN musehub_object_refs r ON r.object_id = o.object_id
WHERE r.repo_id = '<repo_uuid>' AND o.deleted_at IS NULL;

Expected plan: Index Scan using pk_musehub_object_refs (repo_id prefix) + Index Scan using musehub_objects pkey

Q9 — Recent commit feed (repo home page)

SELECT * FROM musehub_commits
WHERE repo_id = '<repo_uuid>'
ORDER BY timestamp DESC
LIMIT 10;

Expected plan: Index Scan using ix_musehub_commits_repo_timestamp

Q10 — Per-user storage sum (MCP push quota)

SELECT COALESCE(SUM(o.size_bytes), 0)
FROM musehub_objects o
JOIN musehub_object_refs ref ON ref.object_id = o.object_id
JOIN musehub_repos      r   ON r.repo_id = ref.repo_id
WHERE r.owner_user_id = '<user_uuid>'
  AND r.deleted_at IS NULL;

Expected plan: Index Scan using ix_musehub_repos_owner_user_id + Index Scan using pk_musehub_object_refs + Index Scan using musehub_objects pkey

Slow query log

Queries exceeding SLOW_QUERY_THRESHOLD_MS (default 100 ms) are logged at WARNING level by the SQLAlchemy after_cursor_execute listener in musehub/db/database.py.

To monitor in production:

# Tail the app log for SLOW QUERY entries:
journalctl -u musehub -f | grep "SLOW QUERY"

# Or set log_min_duration_statement in Postgres for server-side capture:
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Alerts should be wired to any line matching SLOW QUERY in the application log, or to pg_stat_statements entries with mean_exec_time > 100.