Unified Search Engine Technical Specifications

How PostgreSQL full-text search works:

```sql -- 1. Parse the query into a tsquery: -- "wireless headphones" → to_tsquery('english', 'wireless & headphones')

-- 2. Full-text rank with column weights: -- ts_rank(columns, tsquery) — higher = more relevant

SELECT id, title, ts_rank( setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', description), 'B') || setweight(to_tsvector('english', body), 'C'), query ) AS rank, ts_headline( 'english', COALESCE(title, '') || ' ' || COALESCE(description, ''), query, 'MaxWords=30, MinWords=15, StartSel=<em>, StopSel=</em>' ) AS highlight, ts_rank_cd( setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', description), 'B'), query ) AS proximity_rank FROM products, to_tsquery('english', 'wireless & headphones') query WHERE to_tsvector('english', title || ' ' || COALESCE(description, '')) @@ query ORDER BY rank DESC, proximity_rank DESC LIMIT 20;

-- Column weight system: -- A (title): 3x weight — most important -- B (description): 2x weight — secondary -- C (body): 1x weight — tertiary ```

ts_headline highlighting:

```typescript // Input: "wireless headphones noise cancelling" // Output HTML: // "...premium <em>wireless</em> <em>headphones</em> // with advanced <em>noise</em> <em>cancelling</em> technology..."

// ts_headline options: { MaxWords: 30, // Max words in highlight snippet MinWords: 15, // Min words (pad with context) StartSel: "<em>", // Highlight start tag StopSel: "</em>", // Highlight end tag MaxFragments: 3, // Max separate highlight fragments FragmentDelimiter: " ... ", // Fragment separator } ```

Typo tolerance:

``` "seach" → matches "search" "nke" → matches "nike"

How it works: 1. Break the query into short letter fragments (trigrams) 2. Find rows sharing those fragments — a fast, index-friendly first pass 3. Score close matches by edit distance, scaled to word length so short words aren't over-matched 4. Rank exact matches above close matches ```

Embedding generation:

1. Text is truncated to the model's max token window (8192 tokens for text-embedding-3-small) 2. The embedding provider returns a vector (1536 dimensions for text-embedding-3-small, $0.02/1M tokens; 3072 dimensions for text-embedding-3-large, $0.13/1M tokens) 3. The vector is stored alongside the record it describes

```sql -- Vector storage in PostgreSQL: CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE product_embeddings ( id uuid DEFAULT gen_random_uuid(), product_id uuid NOT NULL, embedding vector(1536), -- 1536 dims for text-embedding-3-small created_at timestamptz DEFAULT now() );

-- HNSW index (faster queries, more memory): CREATE INDEX ON product_embeddings USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); ```

Vector similarity search:

```sql -- 1. Embed the query with your chosen provider -- 2. Run a cosine similarity search against the stored vectors

SELECT p.id, p.name AS title, p.description, 1 - (e.embedding <=> $1::vector) AS similarity -- <=> is the cosine distance operator in pgvector -- 1 - distance = cosine similarity (0-1 scale) FROM products p JOIN product_embeddings e ON p.id = e.product_id WHERE 1 - (e.embedding <=> $1::vector) >= 0.7 -- similarity threshold ORDER BY e.embedding <=> $1::vector LIMIT 20; ```

Index types:

```typescript // IVFFlat — faster build, slightly slower query // Good for: frequently updated data CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

// HNSW — faster query, slower build, more memory // Good for: read-heavy, rarely updated CREATE INDEX ON products USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64, ef_search = 40);

// m: number of connections per node (8-64, default 16) // ef_construction: build quality (32-512, default 64) — higher = better results, slower build // ef_search: query quality (16-500, default 40) — higher = better recall, slower query ```

RRF fusion algorithm:

``` 1. Run the full-text search — returns a ranked list, e.g. [A, B, C, D] 2. Run the semantic search — returns a ranked list, e.g. [A, E, F, B] 3. Fuse the two rankings: For each document, sum 1 / (k + rank) across every list it appears in (k = 60, the standard RRF constant; rank = 1-indexed position) 4. Sort by the combined score

Worked example (k=60, equal weight to both engines): A: 1/61 + 1/61 = 0.0164 (rank 1 in both → top result) B: 1/62 + 1/64 = 0.0159 (rank 2 in both) E: 0 + 1/62 = 0.0081 (rank 2 in semantic only) C: 1/63 + 0 = 0.0079 (rank 3 in full-text only) F: 0 + 1/63 = 0.0079 (rank 3 in semantic only) D: 1/64 + 0 = 0.0078 (rank 4 in full-text only)

Final order: A > B > E > C > F > D ```

Why RRF instead of score averaging:

``` Score averaging problem: → Full-text scores: 0-1 (ts_rank normalized) → Vector scores: 0-1 (cosine similarity) → But scale might differ in practice

RRF advantage: → Only uses ranks, not raw scores → Rank 1 always contributes the same regardless of engine → More robust to score scale differences → Google's approach for combining ranking signals ```

Setup: Turning on Search enables the engine, registers your collections for indexing, and starts logging query telemetry — no additional configuration required. Every query, its mode, its result count, and its latency are captured automatically the moment the module is enabled.

Telemetry pipeline: Query logging runs asynchronously off the request path, with automatic retry and backoff, so analytics collection never adds latency to a live search. Every query fires an internal event the moment it completes; a background worker picks it up and writes the record — the customer-facing search response never waits on it.

What gets tracked per query: the search text (normalized), which mode was used (fulltext / semantic / hybrid), how many results came back, how long it took, and which user ran it.

Analytics queries (illustrative — the same shape powers the built-in dashboard):

```sql -- Top queries by volume: SELECT query, COUNT(*) AS count FROM search_queries GROUP BY query ORDER BY count DESC LIMIT 20;

-- No-results queries (opportunity for content): SELECT query, COUNT(*) AS count FROM search_queries WHERE result_count = 0 GROUP BY query ORDER BY count DESC;

-- Average timing by mode: SELECT mode, AVG(timing) AS avg_timing FROM search_queries GROUP BY mode; ```