Skip to main content
codingstairs

Step 3

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

1 views

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.

Content 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 an admin migration. Document search exposes only processed = TRUE files and adds partial indexes for vector/metadata joins. A fast incomplete search copy is still incorrect.

Indexes also need subtraction. If (user_id, created_at) exists, verify whether a separate (user_id) index changes any real plan. Do not duplicate the key already created by UNIQUE (user_id, type). Every redundant index adds INSERT, UPDATE, vacuum, storage, and backup cost. Replace parent-list N+1 reads with one WHERE parent_id IN (...) query and group the rows in memory; skip that query when the parent list is empty.

The console's PostgreSQL pools, Python, Java Hikari, and Next.js connections all consume database capacity. Set explicit connection-wait limits, but do not impose a global query timeout on long collection 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.