database-query-optimization

Replace per-row queries inside loops with a single query using inArray over the collected IDs, then group results in memory with a Map keyed by the foreign key. The preloadUserInterests function is the canonical batch-preload example in this codebase.

Check the per-request dbCount, dbMs, and dbMax metrics tracked by the db proxy wrapper. A high dbCount relative to the response payload signals N+1 or redundant queries, and Prometheus histograms at /api/metrics show latency trends.

Use a composite index when queries filter on multiple columns, leading with the most selective or equality-filtered column. Use a partial index for status columns with skewed distributions, such as indexing only rows where match_status equals pending.

No, Drizzle does not auto-create indexes for foreign keys in PostgreSQL. You should add indexes manually for foreign key columns, especially on high-volume tables like eventAttendance which currently lacks indexes entirely.

Skip this guidance for schema design, migrations, or backfills, which belong to migration safety workflows. It also does not apply to trivial single-row lookups, seed data, fixtures, or test-only queries where performance is irrelevant.