Cut p95 latency on the search endpoint

I profiled the endpoint's last 24 hours of traffic first. Two things stand out: the tag filter forces a sequential scan on documents, and the service issues one extra query per result to load tags (an N+1).

Here is the order I'd tackle them in:

1. Add a composite index that matches the filter and the sort.
2. Load tags for the whole page in one query instead of one per row.
3. Cache the facet counts for 30 seconds, since they change slowly.
4. Re-run the load test and compare p95, not the average.

The index is the biggest win. A GIN index on the tag array plus a b-tree for ordering brought the query plan from a sequential scan to an index scan in similar systems 1:

CREATE INDEX CONCURRENTLY documents_tags_gin
  ON documents USING gin (tags);

CREATE INDEX CONCURRENTLY documents_workspace_updated
  ON documents (workspace_id, updated_at DESC);

Use CONCURRENTLY so writes aren't blocked while it builds. For the N+1, batch the lookup with WHERE document_id = ANY($1) 2. Want me to rewrite the data-loading function too?

Here is a version that fetches the page of documents and all of their tags in two queries total, regardless of page size:

export async function loadResults(workspaceId: string, tag: string, limit = 20) {
  const { rows: docs } = await pool.query(
    `SELECT id, title, updated_at FROM documents
     WHERE workspace_id = $1 AND tags @> ARRAY[$2]
     ORDER BY updated_at DESC LIMIT $3`,
    [workspaceId, tag, limit],
  );
  const { rows: tags } = await pool.query(
    "SELECT document_id, tag FROM document_tags WHERE document_id = ANY($1)",
    [docs.map((d) => d.id)],
  );
  return docs.map((d) => ({
    ...d,
    tags: tags.filter((t) => t.document_id === d.id).map((t) => t.tag),
  }));
}

Two notes: @> is what lets the GIN index kick in, and for pages larger than a few hundred rows you'd want to group the tags into a Map instead of filtering per document.