Step 3 — Design tables, indexes, and pools from query budgets

More indexes or a larger pool is not automatically an optimization. Measure query shape, data distribution, write cost, and the total connection budget together.

1. Write the user path and columns needed for list, search, and detail views.
2. Measure representative queries with EXPLAIN (ANALYZE, BUFFERS).
3. Consider partial/composite indexes for repeated language, kind, and published filters.
4. Match array/trigram operators to GIN indexes.
5. Compare before and after on staging data.

Warragon's Codingstairs lists use PostSummary instead of moving full bodies over the wire, and keyword arrays use a GIN-friendly @> condition. The ILIKE '%term%' search over titles, descriptions, slugs, and published lesson bodies uses pg_trgm GIN indexes, with the same definitions added idempotently to existing databases by the Admin migration. DMDDKSL RAG exposes only processed = TRUE files and adds partial indexes for vector/HIRA joins. A fast incomplete search copy is still incorrect.

Admin's five PostgreSQL pools, Python, Java Hikari, and Next.js TypeORM all consume database capacity. Set explicit connection-wait limits, but do not impose a global query timeout on long crawler or backup work without a route-specific policy. Verify the aggregate against staging and production max_connections and pooler limits.

Practice

For one slow screen, record columns, filters, ordering, expected rows, actual plan, and the post-change plan. Check write and backup cost after adding an index.

Read next

• Idempotent migrations and recovery
• Expose completed files only in RAG
• Codingstairs search with pg_trgm
• Next step