Building a Hybrid RAG Pipeline for Meal Search: Postgres, pgvector, and the Retrieval Problem…
Building a Hybrid RAG Pipeline for Meal Search: Postgres, pgvector, and the Retrieval Problem Nobody Talks AboutHow I combined keyword and vector search over a meal_catalog to power meal search and diet planning, and why pure vector similarity wasn’t enough.The problem I was actually solvingI spend…
Building a Hybrid RAG Pipeline for Meal Search: Postgres, pgvector, and the Retrieval Problem Nobody Talks AboutHow I combined keyword and vector search over a meal_catalog to power meal search and diet planning, and why pure vector similarity wasn’t enough.The problem I was actually solvingI spend most of my day writing React and TypeScript for a fintech product, so this project started as something outside of work: a personal RAG app over global meal data, built to power meal search and diet planning.The obvious approach is: embed everything, throw it in a vector DB, do a cosine similarity search, done. I tried that first. It worked, until it didn’t. Pure vector search kept surfacing meals that were semantically close but practically wrong.Someone searching “chicken biryani” would get “chicken pulao” ranked above an actual biryani recipe with a slightly unusual description, because the embedding space treats them as neighbors. For a diet-planning tool where the exact dish, exact ingredients, and exact nutrition numbers matter, “semantically similar” isn’t good enough. I needed exact-term matching and semantic recall, working together.That’s what pushed me toward hybrid search instead of vector-only retrieval, and toward Postgres + pgvector instead of a dedicated vector DB, since I could get full-text search and vector search in the same query engine without stitching two systems together.Why Postgres + pgvector over a dedicated vector DBI considered Pinecone and Weaviate early on. They’re better at pure vector search at scale.But for this project:I already had meal data (ingredients, nutrition, instructions) that needed relational structure, categories, dietary tags, macros as queryable columns, not just blob metadata.Running keyword search (Postgres full-text search via tsvector) and vector search (pgvector) in the same query, against the same table, meant I could combine both signals in one round trip instead of querying two systems and merging results in application code.One less service to run, monitor, and pay for. For a side project, that matters.The tradeoff: pgvector’s ANN indexing (I used IVFFlat) doesn’t scale as gracefully as a purpose-built vector DB once you’re past a few million rows. For my catalog size, that ceiling was nowhere close.The schemaI went with one row per meal: name, ingredients, and nutrition combined into a single chunk for embedding, rather than splitting each meal into separate ingredient/instruction/nutrition chunks.This was a deliberate call: meals are typically retrieved as a whole unit (you want the full dish, not a fragment of its ingredient list), so chunking below the meal level would’ve meant reassembling fragments at query time for no real benefit.CREATE EXTENSION IF NOT EXISTS vector;CREATE TABLE meal_catalog ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, cuisine TEXT, ingredients TEXT[] NOT NULL, instructions TEXT, calories NUMERIC, protein_g NUMERIC, carbs_g NUMERIC, fat_g NUMERIC, dietary_tags TEXT[], -- e.g. {vegan, gluten-free} embedding VECTOR(1536), -- text-embedding-3-small dimension search_vector TSVECTOR, created_at TIMESTAMPTZ DEFAULT now());-- keyword indexCREATE INDEX idx_meal_search_vector ON meal_catalog USING GIN(search_vector);-- vector indexCREATE INDEX idx_meal_embedding ON meal_catalog USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);-- keep tsvector in sync on writeCREATE TRIGGER meal_search_vector_update BEFORE INSERT OR UPDATEON meal_catalog FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(search_vector, 'pg_catalog.english', name, instructions);The dietary_tags and macro columns as real columns (not buried in JSON) turned out to matter a lot later; diet planning needs hard filters (“under 500 calories,” “vegan”) before ranking, not fuzzy semantic matching on them.Ingestion: generating embeddingsStraightforward pipeline in TypeScript, using OpenAI’s text-embedding-3-small. The one thing I got wrong initially: I embedded the raw ingredients array without flattening it into readable text, and retrieval quality was noticeably worse. Embedding models work on natural language, not data structures.import OpenAI from "openai";import { pool } from "./db";const openai = new OpenAI();interface MealInput { name: string; cuisine: string; ingredients: string[]; instructions: string; calories: number; proteinG: number; carbsG: number; fatG: number; dietaryTags: string[];}function buildEmbeddingText(meal: MealInput): string { return [ meal.name, `Cuisine: ${meal.cuisine}`, `Ingredients: ${meal.ingredients.join(", ")}`, `Tags: ${meal.dietaryTags.join(", ")}`, ].join(". ");}export async function ingestMeal(meal: MealInput) { const embeddingInput = buildEmbeddingText(meal); const { data } = await openai.embeddings.create({ model: "text-embedding-3-small", input: embeddingInput, }); const embedding = data[0].embedding; await pool.query( `INSERT INTO meal_catalog (name, cuisine, ingredients, instructions, calories, protein_g, carbs_g, fat_g, dietary_tags, embedding) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`, [ meal.name, meal.cuisine, meal.ingredients, meal.instructions, meal.calories, meal.proteinG, meal.carbsG, meal.fatG, meal.dietaryTags, `[${embedding.join(",")}]`, ] );}The retrieval layer: combining keyword and vector searchThis was the actual hard part. I run two searches, full-text (ts_rank) and vector (cosine distance), and combine them with Reciprocal Rank Fusion (RRF) rather than trying to normalize and average two incompatible scoring scales directly. ts_rank scores and cosine distances live on completely different numeric ranges, so a weighted average between them is unstable; RRF sidesteps that by only caring about rank position, not raw score magnitude.export async function hybridSearch(query: string, limit = 10) { const { data } = await openai.embeddings.create({ model: "text-embedding-3-small", input: query, }); const queryEmbedding = `[${data[0].embedding.join(",")}]`; const result = await pool.query( ` WITH keyword_results AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(search_vector, plainto_tsquery('english', $1)) DESC) AS rank FROM meal_catalog WHERE search_vector @@ plainto_tsquery('english', $1) LIMIT 50 ), vector_results AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY embedding $2::vector) AS rank FROM meal_catalog ORDER BY embedding $2::vector LIMIT 50 ), fused AS ( SELECT COALESCE(k.id, v.id) AS id, (1.0 / (60 + COALESCE(k.rank, 1000))) + (1.0 / (60 + COALESCE(v.rank, 1000))) AS rrf_score FROM keyword_results k FULL OUTER JOIN vector_results v ON k.id = v.id ) SELECT m.*, f.rrf_score FROM fused f JOIN meal_catalog m ON m.id = f.id ORDER BY f.rrf_score DESC LIMIT $3 `, [query, queryEmbedding, limit] ); return result.rows;}The constant 60 in the RRF formula is a standard smoothing value from the original RRF paper; it dampens the influence of top-ranked outliers from either list so neither search method can completely dominate the fused ranking.Feeding retrieval into diet planningMeal search alone is just retrieval. Diet planning is where RAG actually earns its name; the retrieved meals become context for an LLM call that reasons over macro targets, not just topical relevance.export async function generateMealPlan(userQuery: string, macroTargets: MacroTargets) { const candidates = await hybridSearch(userQuery, 20); const context = candidates .map( (m) => `${m.name} (${m.calories} kcal, ${m.protein_g}g protein, ${m.carbs_g}g carbs, ${m.fat_g}g fat) — ${m.dietary_tags.join(", ")}` ) .join("\n"); const prompt = `You are a diet planning assistant. Using ONLY the meals listed below, build a day's meal plan that fits these targets: ${JSON.stringify(macroTargets)}. Do not invent meals not in this list.Available meals:${context}`; const response = await openai.chat.completions.create({ model: "gpt-4o-mini", messages: [{ role: "user", content: prompt }], }); return response.choices[0].message.content;}The “do not invent meals not in this list” instruction earns its keep; without it, the model happily hallucinates plausible-sounding dishes that don’t exist in the catalog, which defeats the entire point of grounding it in retrieval.What I’d do differentlyFilter before you rank, not after. Early on, I ran hybrid search first and filtered dietary tags and calorie limits afterward, which meant good matches sometimes got cut post-ranking, leaving fewer than limit results. Moving hard filters (dietary tags, macro caps) into the WHERE clause of both CTEs — before RRF — fixed this.IVFFlat needs lists tuned to your data size, not left at a default. Too few lists and every query scans nearly the whole table; too many and recall drops. I had to actually benchmark this against my catalog size rather than trusting a default.Chunking at the meal level was the right call for search, but not for instructions. If I extend this to recipe step-by-step guidance later, I’ll likely need a second, finer-grained embedding table for instructions specifically; the current one-chunk-per-meal design trades instruction-level retrieval for simplicity, and that’s fine for search but would need to change for a “how do I make step 3” kind of query.This is one piece of a broader personal project exploring RAG and LLM application patterns outside of my day-to-day frontend work. Happy to go deeper on any part of this: the RRF tuning, the embedding pipeline, or the diet-planning prompt design, in a follow-up.This story is published on Generative AI. Connect with us on LinkedIn and follow Zeniteq to stay in the loop with the latest AI stories.Subscribe to our newsletter and YouTube channel to stay updated with the latest news and updates on generative AI. Let’s shape the future of AI together!Building a Hybrid RAG Pipeline for Meal Search: Postgres, pgvector, and the Retrieval Problem… was originally published in Generative AI on Medium, where people are continuing the conversation by highlighting and responding to this story.Source: Generative AI Pub — Published — Category: Image AI