◉ AI Models/2026-03-26Advanced

Taking Rork × Supabase pgvector Semantic Search to Production: Index Choice, Incremental Re-embedding, and Hybrid Retrieval

How to run semantic search in a Rork app for real. Measure HNSW against IVFFlat, cut embedding API calls with content hashing, and fuse vector search with full-text search using RRF.

rork⁵⁸ supabase³ pgvector³ semantic-search vector-embeddings AI²⁹ recommendation advanced⁷

✦ Premium Article

The day I shipped a search box in one of my calming-wallpaper apps, a log entry showed a user typing "something to look at when I can't sleep." The result count was zero. The database held hundreds of images tagged "stillness," "moonlight," and "restful sleep" — and not a single character matched.

Search had to reach for meaning, not letters. That was the moment I reached for pgvector.

This article builds semantic search and AI recommendations into a Rork mobile app with Supabase pgvector, at a scale an indie developer actually operates: low six figures of rows, a small Supabase instance, one person on call. If you need a refresher on Supabase auth and real-time features, see the Rork × Supabase Auth & Real-Time Implementation Guide.

Here is the architecture:

The user enters a natural-language search query
A Supabase Edge Function converts the query to a vector via the OpenAI Embeddings API
pgvector runs a cosine similarity search against the database
Results come back to the Rork app with relevance scores

Getting that far is the easy half. What bites you in production is the second half of this article: which index you choose, and when you decide to rebuild your embeddings.

Prerequisites and Setup

What You'll Need

A Rork Max account with an active project
A Supabase project (pgvector is available on the Free plan)
An OpenAI API key for the Embeddings API
Node.js 18+ for local Edge Function testing

What Are Vector Embeddings?

Vector embeddings transform data like text or images into high-dimensional numerical arrays. OpenAI's text-embedding-3-small model, for instance, converts any text into a 1536-dimensional array of floating-point numbers.

Texts with similar meanings end up close together in vector space. "Walking the dog" and "Taking my pet for a stroll" use different words but produce vectors that are nearly identical in direction. This is the foundation that makes semantic search possible.

Setting Up Supabase pgvector

Enabling the Extension

Run the following SQL in the Supabase Dashboard SQL Editor:

-- Enable the pgvector extension
create extension if not exists vector with schema extensions;
 
-- Products table (semantic search target)
create table products (
  id bigserial primary key,
  name text not null,
  description text not null,
  category text not null,
  price numeric(10,2) not null,
  image_url text,
  -- 1536-dimensional vector column (OpenAI text-embedding-3-small)
  embedding vector(1536),
  created_at timestamptz default now()
);
 
-- IVFFlat index for cosine similarity search
-- Suitable for medium-scale data (up to ~1M rows)
create index on products
  using ivfflat (embedding vector_cosine_ops)
  with (lists = 100);
 
-- Enable Row Level Security
alter table products enable row level security;
 
-- Authenticated users can view all products
create policy "Products are viewable by authenticated users"
  on products for select
  to authenticated
  using (true);

Creating the Search RPC Function

This PostgreSQL function is the core of our search pipeline. It accepts a query vector and returns products ranked by cosine similarity.

-- Semantic search function
-- query_embedding: the vectorized search query
-- match_threshold: similarity cutoff (0-1, higher = stricter)
-- match_count: maximum results to return
create or replace function match_products(
  query_embedding vector(1536),
  match_threshold float default 0.7,
  match_count int default 10
)
returns table (
  id bigint,
  name text,
  description text,
  category text,
  price numeric,
  image_url text,
  similarity float
)
language plpgsql
as $$
begin
  return query
  select
    products.id,
    products.name,
    products.description,
    products.category,
    products.price,
    products.image_url,
    -- Calculate cosine similarity (1 - cosine distance)
    1 - (products.embedding <=> query_embedding) as similarity
  from products
  where 1 - (products.embedding <=> query_embedding) > match_threshold
  order by products.embedding <=> query_embedding
  limit match_count;
end;
$$;

The <=> operator computes cosine distance in pgvector. By subtracting from 1, we get cosine similarity — a value closer to 1 means the items are more semantically related.

✦

Thank you for reading this far.

Continue Reading

What follows includes implementation code, benchmarks, and practical content we hope you'll find useful. This site runs without ads — server and development costs are supported entirely by members like you. If it's been helpful, we'd be truly grateful for your support.

WHAT YOU'LL LEARN

✦A measured comparison of HNSW and IVFFlat on recall@10 and p95 latency, with selection criteria by data size

✦An incremental re-embedding design using content_hash that cuts Embeddings API calls by over 90%

✦A SQL implementation of hybrid search that fuses vector and full-text results with RRF

Secure payment via Stripe · Cancel anytime

✦

Unlock This Article

Get full access to the rest of this article. Buy once, read anytime. This site is ad-free — your support goes directly toward keeping it running.

Unlock all articles with Membership →

Building the Vectorization Pipeline with Edge Functions

Vectorizing Search Queries

This Supabase Edge Function receives a text query, converts it to a vector using OpenAI, and runs a semantic search against pgvector.

// supabase/functions/semantic-search/index.ts
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";
 
// Retrieved from environment variables
const OPENAI_API_KEY = Deno.env.get("OPENAI_API_KEY")!;
const SUPABASE_URL = Deno.env.get("SUPABASE_URL")!;
const SUPABASE_SERVICE_ROLE_KEY = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;
 
serve(async (req) => {
  try {
    const { query, threshold = 0.7, limit = 10 } = await req.json();
 
    if (!query || typeof query !== "string") {
      return new Response(
        JSON.stringify({ error: "query is required" }),
        { status: 400, headers: { "Content-Type": "application/json" } }
      );
    }
 
    // Step 1: Vectorize the query with OpenAI Embeddings API
    const embeddingResponse = await fetch(
      "https://api.openai.com/v1/embeddings",
      {
        method: "POST",
        headers: {
          Authorization: `Bearer ${OPENAI_API_KEY}`,
          "Content-Type": "application/json",
        },
        body: JSON.stringify({
          model: "text-embedding-3-small",
          input: query,
        }),
      }
    );
 
    const embeddingData = await embeddingResponse.json();
    const queryEmbedding = embeddingData.data[0].embedding;
    // Expected output: array of 1536 floating-point numbers
 
    // Step 2: Semantic search via Supabase pgvector
    const supabase = createClient(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY);
 
    const { data: results, error } = await supabase.rpc("match_products", {
      query_embedding: queryEmbedding,
      match_threshold: threshold,
      match_count: limit,
    });
 
    if (error) throw error;
 
    return new Response(
      JSON.stringify({ results, query }),
      { status: 200, headers: { "Content-Type": "application/json" } }
    );
  } catch (err) {
    return new Response(
      JSON.stringify({ error: err.message }),
      { status: 500, headers: { "Content-Type": "application/json" } }
    );
  }
});

Batch Vectorization of Product Data

You'll also need a function to generate embeddings for existing product data.

// supabase/functions/generate-embeddings/index.ts
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";
 
const OPENAI_API_KEY = Deno.env.get("OPENAI_API_KEY")!;
const SUPABASE_URL = Deno.env.get("SUPABASE_URL")!;
const SUPABASE_SERVICE_ROLE_KEY = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;
 
serve(async (req) => {
  const supabase = createClient(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY);
 
  // Fetch products without embeddings (batch size: 50)
  const { data: products, error } = await supabase
    .from("products")
    .select("id, name, description, category")
    .is("embedding", null)
    .limit(50);
 
  if (error) throw error;
  if (!products || products.length === 0) {
    return new Response(JSON.stringify({ message: "No products to process" }));
  }
 
  let processed = 0;
 
  for (const product of products) {
    // Combine name, description, and category for richer embeddings
    const textToEmbed = `${product.name}. ${product.description}. Category: ${product.category}`;
 
    const embeddingResponse = await fetch(
      "https://api.openai.com/v1/embeddings",
      {
        method: "POST",
        headers: {
          Authorization: `Bearer ${OPENAI_API_KEY}`,
          "Content-Type": "application/json",
        },
        body: JSON.stringify({
          model: "text-embedding-3-small",
          input: textToEmbed,
        }),
      }
    );
 
    const embeddingData = await embeddingResponse.json();
    const embedding = embeddingData.data[0].embedding;
 
    // Save the embedding vector to the products table
    await supabase
      .from("products")
      .update({ embedding })
      .eq("id", product.id);
 
    processed++;
  }
 
  return new Response(
    JSON.stringify({ processed, total: products.length }),
    { headers: { "Content-Type": "application/json" } }
  );
});
// Expected output: { "processed": 50, "total": 50 }

Integrating with Your Rork App

Semantic Search Component

Here's how to build a search screen in your Rork project. The key insight is that users type natural language directly — no special query syntax needed — and the Edge Function handles the semantic matching behind the scenes.

// app/search.tsx — Main search screen
import React, { useState, useCallback } from "react";
import {
  View, TextInput, FlatList, Text, Image,
  StyleSheet, ActivityIndicator, TouchableOpacity,
} from "react-native";
import { supabase } from "@/lib/supabase";
 
interface Product {
  id: number;
  name: string;
  description: string;
  category: string;
  price: number;
  image_url: string | null;
  similarity: number;
}
 
export default function SemanticSearchScreen() {
  const [query, setQuery] = useState("");
  const [results, setResults] = useState<Product[]>([]);
  const [loading, setLoading] = useState(false);
 
  const handleSearch = useCallback(async () => {
    if (!query.trim()) return;
    setLoading(true);
 
    try {
      // Call the Supabase Edge Function
      const { data, error } = await supabase.functions.invoke(
        "semantic-search",
        { body: { query, threshold: 0.65, limit: 20 } }
      );
 
      if (error) throw error;
      setResults(data.results || []);
    } catch (err) {
      console.error("Search failed:", err);
    } finally {
      setLoading(false);
    }
  }, [query]);
 
  const renderProduct = ({ item }: { item: Product }) => (
    <TouchableOpacity style={styles.card}>
      {item.image_url && (
        <Image source={{ uri: item.image_url }} style={styles.image} />
      )}
      <View style={styles.info}>
        <Text style={styles.name}>{item.name}</Text>
        <Text style={styles.description} numberOfLines={2}>
          {item.description}
        </Text>
        <View style={styles.meta}>
          <Text style={styles.price}>${item.price.toFixed(2)}</Text>
          {/* Display similarity score as a percentage */}
          <Text style={styles.similarity}>
            {Math.round(item.similarity * 100)}% match
          </Text>
        </View>
      </View>
    </TouchableOpacity>
  );
 
  return (
    <View style={styles.container}>
      <TextInput
        style={styles.searchInput}
        placeholder="What are you looking for? (e.g., waterproof running shoes)"
        value={query}
        onChangeText={setQuery}
        onSubmitEditing={handleSearch}
        returnKeyType="search"
      />
      {loading ? (
        <ActivityIndicator size="large" style={styles.loader} />
      ) : (
        <FlatList
          data={results}
          keyExtractor={(item) => item.id.toString()}
          renderItem={renderProduct}
          ListEmptyComponent={
            query ? (
              <Text style={styles.empty}>
                No matching products found
              </Text>
            ) : null
          }
        />
      )}
    </View>
  );
}
 
const styles = StyleSheet.create({
  container: { flex: 1, backgroundColor: "#f8f9fa" },
  searchInput: {
    margin: 16, padding: 14, backgroundColor: "#fff",
    borderRadius: 12, fontSize: 16, borderWidth: 1, borderColor: "#e0e0e0",
  },
  card: {
    flexDirection: "row", marginHorizontal: 16, marginBottom: 12,
    backgroundColor: "#fff", borderRadius: 12, overflow: "hidden",
    shadowColor: "#000", shadowOpacity: 0.05, shadowRadius: 8,
    elevation: 2,
  },
  image: { width: 100, height: 100 },
  info: { flex: 1, padding: 12 },
  name: { fontSize: 16, fontWeight: "600", marginBottom: 4 },
  description: { fontSize: 13, color: "#666", marginBottom: 8 },
  meta: { flexDirection: "row", justifyContent: "space-between" },
  price: { fontSize: 15, fontWeight: "700", color: "#1a73e8" },
  similarity: { fontSize: 12, color: "#888", alignSelf: "center" },
  loader: { marginTop: 40 },
  empty: { textAlign: "center", color: "#999", marginTop: 40, fontSize: 15 },
});

Building the AI Recommendation Engine

Taking semantic search a step further, let's implement personalized recommendations based on each user's browsing history.

Hybrid Scoring Design

Pure vector similarity is a solid starting point, but combining it with behavioral signals produces significantly better results in practice.

-- User view history table
create table user_views (
  id bigserial primary key,
  user_id uuid references auth.users(id),
  product_id bigint references products(id),
  viewed_at timestamptz default now()
);
 
-- Function to compute a user's preference vector
-- Averages the embeddings of the 20 most recently viewed products
create or replace function get_user_preference_vector(p_user_id uuid)
returns vector(1536)
language plpgsql
as $$
declare
  pref_vector vector(1536);
begin
  select avg(p.embedding)::vector(1536) into pref_vector
  from user_views uv
  join products p on p.id = uv.product_id
  where uv.user_id = p_user_id
    and p.embedding is not null
  order by uv.viewed_at desc
  limit 20;
 
  return pref_vector;
end;
$$;
 
-- Hybrid recommendation function
-- Weights: vector similarity 70% + popularity 20% + recency 10%
create or replace function recommend_products(
  p_user_id uuid,
  p_limit int default 10
)
returns table (
  id bigint,
  name text,
  description text,
  category text,
  price numeric,
  image_url text,
  score float
)
language plpgsql
as $$
declare
  user_vector vector(1536);
begin
  user_vector := get_user_preference_vector(p_user_id);
 
  if user_vector is null then
    -- No browsing history: fall back to popular/recent items
    return query
    select p.id, p.name, p.description, p.category,
           p.price, p.image_url, 0.5::float as score
    from products p
    order by p.created_at desc
    limit p_limit;
    return;
  end if;
 
  return query
  select
    p.id, p.name, p.description, p.category,
    p.price, p.image_url,
    (
      -- Vector similarity (70%)
      0.7 * (1 - (p.embedding <=> user_vector))
      -- Popularity: log-scaled view count (20%)
      + 0.2 * least(log(2 + coalesce(vc.view_count, 0)) / 5, 1)
      -- Recency: bonus for items added within 7 days (10%)
      + 0.1 * case
          when p.created_at > now() - interval '7 days' then 1
          when p.created_at > now() - interval '30 days' then 0.5
          else 0
        end
    ) as score
  from products p
  left join (
    select product_id, count(*) as view_count
    from user_views
    group by product_id
  ) vc on vc.product_id = p.id
  where p.embedding is not null
    -- Exclude already-viewed products
    and p.id not in (
      select product_id from user_views where user_id = p_user_id
    )
  order by score desc
  limit p_limit;
end;
$$;

Recommendations Screen in Rork

// app/recommendations.tsx — Personalized recommendations
import React, { useEffect, useState } from "react";
import { View, FlatList, Text, StyleSheet, RefreshControl } from "react-native";
import { supabase } from "@/lib/supabase";
import { ProductCard } from "@/components/ProductCard";
 
interface RecommendedProduct {
  id: number;
  name: string;
  description: string;
  category: string;
  price: number;
  image_url: string | null;
  score: number;
}
 
export default function RecommendationsScreen() {
  const [products, setProducts] = useState<RecommendedProduct[]>([]);
  const [refreshing, setRefreshing] = useState(false);
 
  const fetchRecommendations = async () => {
    const { data: { user } } = await supabase.auth.getUser();
    if (!user) return;
 
    const { data, error } = await supabase.rpc("recommend_products", {
      p_user_id: user.id,
      p_limit: 20,
    });
 
    if (!error && data) {
      setProducts(data);
    }
  };
 
  useEffect(() => {
    fetchRecommendations();
  }, []);
 
  const onRefresh = async () => {
    setRefreshing(true);
    await fetchRecommendations();
    setRefreshing(false);
  };
 
  return (
    <View style={styles.container}>
      <Text style={styles.title}>Recommended for You</Text>
      <FlatList
        data={products}
        keyExtractor={(item) => item.id.toString()}
        renderItem={({ item }) => (
          <ProductCard product={item} showScore />
        )}
        refreshControl={
          <RefreshControl refreshing={refreshing} onRefresh={onRefresh} />
        }
        ListEmptyComponent={
          <Text style={styles.empty}>
            Browse some products and we'll start showing personalized recommendations
          </Text>
        }
      />
    </View>
  );
}
 
const styles = StyleSheet.create({
  container: { flex: 1, backgroundColor: "#f8f9fa" },
  title: {
    fontSize: 22, fontWeight: "700", padding: 16, paddingBottom: 8,
  },
  empty: {
    textAlign: "center", color: "#999", marginTop: 60,
    fontSize: 15, paddingHorizontal: 32,
  },
});

Performance Optimization

Choosing the Right Index

pgvector offers two approximate nearest neighbor (ANN) index types, each suited to different scales.

IVFFlat partitions vectors into clusters and narrows the search scope. It works well for datasets in the 100K–1M range. The lists parameter controls the number of clusters — a good rule of thumb is the square root of your total row count.

HNSW (Hierarchical Navigable Small World) uses a graph-based algorithm that achieves higher recall than IVFFlat, though index construction takes longer. For datasets exceeding 1M rows, HNSW is the recommended choice.

-- HNSW index (for large-scale data)
-- m: graph connectivity (higher = better recall but more memory)
-- ef_construction: search width during index building
create index on products
  using hnsw (embedding vector_cosine_ops)
  with (m = 16, ef_construction = 64);

Edge Caching for Frequent Queries

For queries that are searched often, adding a cache layer can significantly reduce latency and API costs. For a deeper dive into AI backend caching strategies, see the How I Cut My Rork App's AI Costs with Cloudflare AI Gateway.

// Generate a normalized cache key from the query
function generateCacheKey(query: string): string {
  return query.toLowerCase().trim().replace(/\s+/g, " ");
}
 
// Cache lookup example using a Supabase table
async function getCachedResult(cacheKey: string) {
  const { data } = await supabase
    .from("search_cache")
    .select("results, cached_at")
    .eq("cache_key", cacheKey)
    .single();
 
  if (data) {
    const age = Date.now() - new Date(data.cached_at).getTime();
    // Return cache if less than 1 hour old
    if (age < 3600000) return data.results;
  }
  return null;
}

Reducing Embedding Dimensions

OpenAI's text-embedding-3-small defaults to 1536 dimensions, but you can reduce this with the dimensions parameter. For many use cases, 512 dimensions provides sufficient accuracy while cutting storage by roughly 67% and improving query speed.

// Reduce to 512 dimensions (~67% storage savings)
const embeddingResponse = await fetch(
  "https://api.openai.com/v1/embeddings",
  {
    method: "POST",
    headers: {
      Authorization: `Bearer ${OPENAI_API_KEY}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      model: "text-embedding-3-small",
      input: query,
      dimensions: 512,  // Default is 1536
    }),
  }
);
// Expected output: array of 512 floating-point numbers

Remember to update both the table definition and index if you change the dimension count.

Choosing Between HNSW and IVFFlat by Measurement

The section above lists the index types. In practice the hard question isn't "which one" — it's "can I prove I didn't lose accuracy after adding it?" Approximate nearest neighbor search is, as the name says, approximate. The moment you build an index, you accept that some correct results will be missed.

So build a ground truth set first. An exhaustive scan with indexes disabled is, by definition, the correct answer.

-- 1) Build ground truth (exhaustive scan, no index)
set enable_indexscan = off;
set enable_bitmapscan = off;
 
create table eval_ground_truth as
select
  q.id as query_id,
  array_agg(p.id order by p.embedding <=> q.embedding) as top10
from eval_queries q
cross join lateral (
  select id, embedding from products
  order by embedding <=> q.embedding
  limit 10
) p
group by q.id;
 
reset enable_indexscan;
reset enable_bitmapscan;

With ground truth in hand, compare the indexed results against it and compute recall@10.

-- 2) Compute recall@10 for the indexed path
create or replace function eval_recall_at_10()
returns numeric language plpgsql as $$
declare
  hit_total int := 0;
  q record;
  approx bigint[];
begin
  for q in select query_id, top10, (select embedding from eval_queries where id = query_id) as emb
           from eval_ground_truth loop
    select array_agg(id order by embedding <=> q.emb)
      into approx
      from (select id, embedding from products order by embedding <=> q.emb limit 10) s;
 
    -- Add the size of the intersection between true top-10 and approximate top-10
    hit_total := hit_total + (
      select count(*) from unnest(approx) a where a = any(q.top10)
    );
  end loop;
 
  -- Divide by (queries × 10) to get recall@10 in the 0.0–1.0 range
  return hit_total::numeric / (select count(*) * 10 from eval_ground_truth);
end;
$$;

Here are the numbers from my own setup: a Supabase Small instance, 120,000 products, 512-dimension embeddings, 200 evaluation queries. The p95 column is query time as observed from the Edge Function.

Configuration	recall@10	p95 latency	Build time	Index size
No index (exhaustive scan)	1.000	412 ms	—	—
IVFFlat lists=346, probes=10	0.912	18 ms	42 s	~260 MB
IVFFlat lists=346, probes=40	0.971	51 ms	42 s	~260 MB
HNSW m=16, ef_search=40	0.983	9 ms	6 min 20 s	~380 MB
HNSW m=16, ef_search=100	0.995	14 ms	6 min 20 s	~380 MB

Three things stand out.

First, IVFFlat defaults are not accurate enough. The default probes value is 1. Even at probes=10, recall@10 sits at 0.912 — roughly one of every ten correct results never surfaces. When search "feels worse" right after you add an index, it usually isn't slower. It's wrong.

Second, HNSW lets you trade accuracy for speed per session. Because ef_search is a query-time parameter, you can move it without rebuilding anything.

-- Precision matters (an explicit user search)
set local hnsw.ef_search = 100;
 
-- Speed matters (a recommendation strip on a list screen)
set local hnsw.ef_search = 24;

Third, HNSW charges you in build time and memory. Six minutes for 120k rows here — and if maintenance_work_mem is too small, the build spills to disk and stretches into tens of minutes. Raise it before you build on a small instance.

set maintenance_work_mem = '512MB';
create index concurrently products_embedding_hnsw
  on products using hnsw (embedding vector_cosine_ops)
  with (m = 16, ef_construction = 64);

concurrently builds without taking a write lock on the table. When you're adding an index to an app that's already live, make that your default.

My own rule: HNSW when writes are infrequent and search quality is the product. IVFFlat (with probes pinned at 20 or higher) when tens of thousands of rows churn daily and rebuild cost dominates. For indie product and content search, it lands on HNSW almost every time.

Incremental Re-embedding: Cutting Embeddings API Calls by Over 90%

The first thing production teaches you about semantic search isn't about accuracy. It's about the invoice.

If a one-character edit to a description triggers a full re-embedding job, your cost scales with row count instead of with change. At 120,000 rows and roughly 180 tokens each, one full pass is about 21.6M tokens — around $0.43 at text-embedding-3-small pricing of $0.02 per 1M tokens. Run it nightly and that's about $13 a month. The money was never the problem. Vectorizing 120,000 unchanged rows every night was.

The fix is boring: hash the exact string you embed, store it, and only rebuild rows whose hash moved.

-- digest() ships with pgcrypto
create extension if not exists pgcrypto with schema extensions;
 
alter table products
  add column content_hash text,
  add column embedded_at timestamptz;
 
-- Define the embedding input in exactly one place
-- (if the app and the database disagree on this string, you get silent drift)
create or replace function products_embedding_input(p products)
returns text language sql immutable as $$
  select p.name || E'\n' || p.category || E'\n' || p.description;
$$;
 
-- Invalidate the embedding whenever the input changes
create or replace function products_invalidate_embedding()
returns trigger language plpgsql as $$
begin
  new.content_hash := encode(digest(products_embedding_input(new), 'sha256'), 'hex');
  if new.content_hash is distinct from old.content_hash then
    new.embedding := null;      -- re-enters the backfill queue
    new.embedded_at := null;
  end if;
  return new;
end;
$$;
 
create trigger trg_products_invalidate_embedding
  before insert or update on products
  for each row execute function products_invalidate_embedding();

Your worker now just asks for rows where embedding is null. No separate queue table.

// supabase/functions/backfill-embeddings/index.ts
const BATCH = 50; // stays inside the Edge Function timeout
 
async function backfillOnce(): Promise<number> {
  const { data: rows } = await supabase
    .from("products")
    .select("id, name, category, description")
    .is("embedding", null)
    .limit(BATCH);
 
  if (!rows?.length) return 0;
 
  // One request, many inputs — the Embeddings API accepts arrays
  const inputs = rows.map((r) => `${r.name}\n${r.category}\n${r.description}`);
  const res = await fetch("https://api.openai.com/v1/embeddings", {
    method: "POST",
    headers: {
      Authorization: `Bearer ${Deno.env.get("OPENAI_API_KEY")}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      model: "text-embedding-3-small",
      input: inputs,
      dimensions: 512,
    }),
  });
  const { data } = await res.json();
 
  await Promise.all(
    rows.map((r, i) =>
      supabase
        .from("products")
        .update({ embedding: data[i].embedding, embedded_at: new Date().toISOString() })
        .eq("id", r.id)
        // If the row changed while we were embedding, don't overwrite it
        .is("embedding", null)
    )
  );
  return rows.length;
}

Why repeat .is("embedding", null) on the update? Because the round trip to the embeddings API takes 300–900 ms in my measurements, and a row can be edited inside that window. If the trigger nulls the embedding and you then write the stale vector back, the content and its vector are permanently out of sync. That one line closes the window.

Once this was live, my nightly re-embedding set settled at 0.6–1.4% of the table — roughly 700 to 1,700 rows out of 120,000. API calls dropped by two orders of magnitude, and the monthly bill went from about $13 to about $0.20.

For the permission side of this (never exposing service_role to the client, updating embeddings while RLS stays on), Rork Max × Supabase Edge Functions & Row Level Security gets concrete.

Catching What Vectors Miss: Hybrid Search with RRF

Vector search has one blunt weakness: proper nouns, model numbers, and coined words.

Search for "Xperia 1 VII" and a vector index will happily return "Xperia 5 V" and "Galaxy S25," because those are semantically close. The user wanted exactly one device. In my own app, exact-title lookups started losing to semantic neighbors — a strange kind of regression where the search got smarter and less useful.

The answer is to run both searches and fuse their rankings. Not a weighted sum of scores — a rank-based fusion, RRF (Reciprocal Rank Fusion). Cosine distance and ts_rank live on different scales, so normalizing and adding them turns into weight-tuning that never ends.

-- Full-text column and index
alter table products add column fts tsvector
  generated always as (to_tsvector('english', name || ' ' || description)) stored;
create index products_fts_idx on products using gin (fts);
 
-- Hybrid search (RRF, k = 60)
create or replace function hybrid_search(
  query_text text,
  query_embedding vector(512),
  match_count int default 20,
  rrf_k int default 60
)
returns table (id bigint, name text, score numeric)
language sql stable as $$
with semantic as (
  select p.id, row_number() over (order by p.embedding <=> query_embedding) as rank
  from products p
  order by p.embedding <=> query_embedding
  limit match_count * 2
),
keyword as (
  select p.id, row_number() over (order by ts_rank_cd(p.fts, plainto_tsquery('english', query_text)) desc) as rank
  from products p
  where p.fts @@ plainto_tsquery('english', query_text)
  limit match_count * 2
)
select
  p.id,
  p.name,
  -- coalesce keeps candidates that appear on only one side
  coalesce(1.0 / (rrf_k + s.rank), 0.0) + coalesce(1.0 / (rrf_k + k.rank), 0.0) as score
from products p
left join semantic s on s.id = p.id
left join keyword  k on k.id = p.id
where s.id is not null or k.id is not null
order by score desc
limit match_count;
$$;

rrf_k = 60 is the value used widely in the information retrieval literature; it's a smoothing term that keeps the top rank from dominating. Lower it and first place carries more weight; raise it and the ranks flatten out. I've left it at 60 and never wanted it elsewhere.

After shipping this, I re-ran my 200 evaluation queries. Across the 38 that contained an exact model number or title, the share where the intended item landed in the top three rose from 61% (vector only) to 95% (hybrid). Accuracy on ordinary natural-language queries was unchanged. You don't have to pick a side. You add them.

One caveat if your content isn't English: to_tsvector relies on a dictionary and won't segment languages like Japanese or Thai. Where pg_bigm or PGroonga is available, use it; on managed Supabase, a pragmatic fallback is a separate 2–3 character n-gram column matched with ILIKE. This is highly environment-dependent — measure on your own data before committing.

Common Errors and Fixes

pgvector Type Mismatch

If you see ERROR: column "embedding" is of type vector but expression is of type text, the vector data is being sent as a string. The Supabase JavaScript client automatically casts arrays to the vector type when you pass them directly — make sure you're not wrapping the array in JSON.stringify.

Edge Function Timeouts

When the OpenAI API is slow, Edge Functions can time out. For batch processing, keep each invocation under 50 items and design your workflow to make multiple calls as needed.

Rebuilding Stale Indexes

After significant data changes (bulk inserts or deletes), IVFFlat index accuracy can degrade. Run a periodic reindex to maintain search quality.

-- Rebuild the index
reindex index products_embedding_idx;

Summary

Getting pgvector to run takes a few lines of SQL and one Edge Function. The hard part comes after: continuously proving to yourself that the approximation isn't dropping results.

Measure recall@10 whenever you add an index. Rebuild embeddings only for rows whose content hash moved. Let full-text search cover the proper nouns your vectors blur together, and fuse the two by rank. Put those three in from the start and the design survives a tenfold increase in data.

For your next step, write just twenty evaluation queries and measure recall@10 against the search you're running in production today. The number will tell you where to start.

When you're ready to feed those results into an AI chat as retrieval context, RAG with Rork — Building Knowledge-Powered AI Chat into Your Mobile App is the next map.

I'm still measuring and correcting my own setup as I go. Thank you for reading.

Thank You for Reading

Rork Lab is ad-free, supported entirely by members like you. We publish practical guides daily with implementation code, benchmarks, and production-ready patterns. If you've found it useful, we'd love to have you on board.