Pre-Conditions

• The pgvector extension MUST be enabled in the target database.
• A table with a vector column and an HNSW index MUST already exist.
• The same table (or a joined table) MUST have a tsvector column with a GIN index.
• The pgvector-hnsw-index-selection skill MUST be installed and verified. See skills/pgvector-hnsw-index-selection.md for setup instructions.

Execution Phases

Phase 1 — Validate Schema

1. Run SELECT extname FROM pg_extension WHERE extname = 'vector'; — HALT if empty.
2. Confirm the target table has both a vector column and a tsvector column.
3. Confirm an HNSW index exists on the vector column and a GIN index exists on the tsvector column.

Phase 2 — Create and Deploy the RPC

Replace your_table, embedding_col, and tsvector_col with the actual table and column names from the validated schema, then execute the following:

CREATE OR REPLACE FUNCTION hybrid_search(
  query_embedding vector,
  query_text      text,
  match_count     int  DEFAULT 20,
  k               int  DEFAULT 60   -- RRF constant; 60 is the standard default
)
RETURNS TABLE(
  id             bigint,
  combined_score float,
  semantic_score float,
  fulltext_score float
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  WITH
  -- Semantic CTE: rank rows by cosine distance using pgvector
  semantic AS (
    SELECT
      t.id,
      ROW_NUMBER() OVER (ORDER BY t.embedding_col <=> query_embedding) AS rank
    FROM your_table t
    ORDER BY t.embedding_col <=> query_embedding
    LIMIT match_count
  ),
  -- Full-text CTE: rank rows by tsvector match quality
  fulltext AS (
    SELECT
      t.id,
      ROW_NUMBER() OVER (
        ORDER BY ts_rank_cd(t.tsvector_col, plainto_tsquery(query_text)) DESC
      ) AS rank
    FROM your_table t
    WHERE t.tsvector_col @@ plainto_tsquery(query_text)
    ORDER BY ts_rank_cd(t.tsvector_col, plainto_tsquery(query_text)) DESC
    LIMIT match_count
  )
  -- RRF fusion: FULL OUTER JOIN so rows from either CTE are retained;
  -- COALESCE(..., 0.0) gives a score of 0 for the missing component
  SELECT
    COALESCE(s.id, f.id)                              AS id,
    (COALESCE(1.0 / (k + s.rank), 0.0) +
     COALESCE(1.0 / (k + f.rank), 0.0))              AS combined_score,
    COALESCE(1.0 / (k + s.rank), 0.0)                AS semantic_score,
    COALESCE(1.0 / (k + f.rank), 0.0)                AS fulltext_score
  FROM semantic s
  FULL OUTER JOIN fulltext f ON s.id = f.id
  ORDER BY combined_score DESC;
END;
$$;

GRANT EXECUTE ON FUNCTION hybrid_search(vector, text, int, int) TO authenticated;

Phase 3 — Verification Report

1. Call the RPC via SELECT * FROM hybrid_search(...) with a test embedding and test text.
2. Confirm the result set returns rows with non-zero combined_score.
3. Verify that a row appearing in both CTEs has a higher combined_score than a row appearing in only one.
4. Run EXPLAIN (ANALYZE, FORMAT JSON) on the RPC call — HALT if a sequential scan appears on the vector or tsvector column.
5. Report: row count, top-3 scores, index usage confirmed (yes/no).

Tuning & Edge Cases

• k default (60): The value 60 is the standard RRF default and works well in most cases. Lower values amplify rank differences; higher values flatten them.
• NULL rank handling: COALESCE(..., 0.0) ensures rows that appear in only one CTE receive a score of 0 for the missing component — they are still returned, just ranked lower than dual-match rows.
• Advanced tuning: For domain-specific weighting (e.g. boosting semantic over full-text), multiply each RRF component by a weight constant before summing in the combined_score expression.