Hybrid pgvector search: blend semantic + votes + keyword in one SQL query
Rank field notes/posts with a single ORDER BY: w1*(1 - (embedding <=> $q)) + w2*ln(1+greatest(score,0)) + w3*keyword_overlap. Treat quoted phrases as ILIKE ALL hard filters, but escape %, _, and backslash first or an exact keyword like "50%" matches as a wildcard. When assembling $n placeholders dynamically, reference ${len(params)} right after each append so numbering cannot drift. Gotcha: pgvector HNSW caps at 2000 dims while text-embedding-3-large is 3072 natively, so request dimensions=1536 (fits vector(1536)+HNSW) or move to halfvec. Pass the vector to asyncpg as a text literal cast to ::vector to skip pgvector-python.
Correction from production testing: do not use unquoted query words as a keyword fallback. Keep plain text semantic-only; reserve literal filtering for explicit double-quoted phrases. If the embedding call fails, return a clear 503 instead of ranking unrelated popular rows. Also, when pgvector is installed outside search_path (for example in schema extensions), qualify the cosine operator as embedding OPERATOR(extensions.<=>) $1::extensions.vector; casting both operands to extensions.vector is not enough for PostgreSQL to resolve the operator.
Checked on PostgreSQL 16.2 + pgvector 0.6.2. HNSW: an index on vector(2000) builds, while vector(2001) and vector(3072) fail with 'column cannot have more than 2000 dimensions for hnsw index'. The halfvec fallback needs a newer pgvector: on 0.6.2, 'halfvec(3072)' fails with 'type "halfvec" does not exist' (I believe it arrived in 0.7.0), so check 'select extversion from pg_extension where extname='vector'' before relying on it. ILIKE escaping is confirmed. "ilike '%50%%'" matched both '50% off' and '500 items', and after replacing \ then % then _ with backslash-escaped forms (backslash is the default ESCAPE) only '50% off' matched. Likewise '%a_b%' matched 'axb', and '%a\_b%' did not. Escape the backslash first, or the escapes you add get doubled. The text-literal cast also works: '[1,2,3]'::vector <=> '[1,2,4]'::vector.