Range Day Pro

API performance audit

2 min read

API Performance Audit

Idea

Validate query performance across all MVP APIs against a realistic staging snapshot seeded from the 60-70k user production base. Current APIs are functionally complete, but several depend on joins, visibility filters, pagination, replacement/void filtering, org membership, friendships, and event relationships that haven’t been tested at scale.

Motivation

MVP APIs favor straightforward query-time Postgres — no Redis, materialized views, or denormalized read models. That’s acceptable now, but we should validate with production-shaped data before trusting behavior at scale. Leaderboard queries are the most obviously complex (mixed-direction cursors, DISTINCT ON, multiple EXISTS visibility clauses), but every API has potential hotspots.

APIs and Query Areas to Audit

  • Auth/user session lookups (GetMe)
  • Drill list/detail, drill visibility checks
  • Score create/list/detail, including voided/replaced score filtering
  • Event list/detail, roster, event-drill relationships
  • User/org membership lookups
  • Leaderboard drill/friends/org queries (DESC and ASC ranking, pagination with ties, cross-org visibility, large org membership)
  • Dashboard/mobile startup paths that chain several API calls

Data Shape Concerns

  • 60-70k users
  • Large orgs (hundreds of members)
  • Users in multiple orgs
  • Popular drills with many scores
  • Repeated scores per shooter (best-of selection)
  • Voided/replaced scores (NOT EXISTS replacement filter)
  • Events with large rosters
  • Many event-drill relationships
  • Friendship-heavy users
  • Common pagination paths (first page, deep pages, tied values)

Audit Method

  • Run representative API calls against staging
  • Capture p50/p95/p99 latency at the API layer
  • Run EXPLAIN (ANALYZE, BUFFERS) for slow or complex SQL
  • Confirm indexes are used for visibility, pagination, org membership, friendship, event, score, and replacement filters
  • Check for N+1 query patterns in provider/service code
  • Record findings and any proposed indexes/read-model changes before implementing optimizations

Decision Criteria

  • Keep simple query-time Postgres where latency and plans are acceptable
  • Prefer targeted indexes or small query rewrites first
  • Consider denormalized read models, materialized views, background aggregation, or cache tables only when realistic data shows they are needed

Status

Not blocking MVP item completion. Treat as a future staging/performance hardening task once production-shaped data is available in staging.

  • leaderboard-api — most complex query set, uses query-time aggregation by design
  • score-api — voided/replaced score filtering, pagination
  • event-api — roster joins, event-drill relationships
  • user-api — friendship queries, org membership lookups
  • drill-api — visibility checks, org scoping

Graph View

Backlinks