-- pgvector 拡張を有効化create extension if not exists vector with schema extensions;-- 商品テーブル（セマンティック検索対象）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次元のベクトルカラム（OpenAI text-embedding-3-small 対応） embedding vector(1536), created_at timestamptz default now());-- コサイン類似度検索用のインデックス-- ivfflat は中規模データ（〜100万件）に適したインデックスタイプcreate index on products using ivfflat (embedding vector_cosine_ops) with (lists = 100);-- RLS（行レベルセキュリティ）を有効化alter table products enable row level security;-- 認証済みユーザーは全商品を閲覧可能create policy "Products are viewable by authenticated users" on products for select to authenticated using (true);
-- ユーザー閲覧履歴テーブル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());-- ユーザーの閲覧傾向ベクトルを計算する関数-- 直近20件の閲覧商品ベクトルの平均を「ユーザー嗜好ベクトル」とするcreate or replace function get_user_preference_vector(p_user_id uuid)returns vector(1536)language plpgsqlas $$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;$$;-- ハイブリッドレコメンデーション関数-- ベクトル類似度 70% + 人気度 20% + 新着度 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 plpgsqlas $$declare user_vector vector(1536);begin user_vector := get_user_preference_vector(p_user_id); if user_vector is null then -- 閲覧履歴がない場合は人気商品を返す 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, ( -- ベクトル類似度（70%） 0.7 * (1 - (p.embedding <=> user_vector)) -- 人気度：閲覧数の対数スケール（20%） + 0.2 * least(log(2 + coalesce(vc.view_count, 0)) / 5, 1) -- 新着度：7日以内は加点（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 -- 既に閲覧した商品は除外 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;$$;
-- 1) 正解集合をつくる（インデックスを使わない全件走査）set enable_indexscan = off;set enable_bitmapscan = off;create table eval_ground_truth asselect q.id as query_id, array_agg(p.id order by p.embedding <=> q.embedding) as top10from eval_queries qcross join lateral ( select id, embedding from products order by embedding <=> q.embedding limit 10) pgroup by q.id;reset enable_indexscan;reset enable_bitmapscan;
正解集合ができたら、インデックス経由の検索結果と突き合わせて recall@10 を測ります。
-- 2) インデックス経由の結果と突き合わせて recall@10 を算出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; -- 正解 top10 と近似 top10 の積集合の要素数を足し込む hit_total := hit_total + ( select count(*) from unnest(approx) a where a = any(q.top10) ); end loop; -- クエリ数 × 10 で割ると recall@10（0.0〜1.0） return hit_total::numeric / (select count(*) * 10 from eval_ground_truth);end;$$;
私自身の環境（Supabase Small インスタンス・商品 12 万件・512 次元・評価クエリ 200 本）で測った値が次の表です。p95 は Edge Function から見たクエリ時間です。
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);
-- digest() は pgcrypto に含まれますcreate extension if not exists pgcrypto with schema extensions;alter table products add column content_hash text, add column embedded_at timestamptz;-- 埋め込みの入力となる文字列を一箇所で定義する（アプリ側と定義がズレると事故になる）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;$$;-- 入力が変わったら embedding を無効化する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; -- 再埋め込みキューに乗る 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();
ワーカー側は「embedding is null の行を取りに行く」だけになります。キューテーブルを別に持つ必要はありません。
-- 全文検索用のカラムとインデックスalter table products add column fts tsvector generated always as (to_tsvector('simple', name || ' ' || description)) stored;create index products_fts_idx on products using gin (fts);-- ハイブリッド検索（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('simple', query_text)) desc) as rank from products p where p.fts @@ plainto_tsquery('simple', query_text) limit match_count * 2)select p.id, p.name, -- 片側にしか出てこない候補も coalesce で拾う coalesce(1.0 / (rrf_k + s.rank), 0.0) + coalesce(1.0 / (rrf_k + k.rank), 0.0) as scorefrom products pleft join semantic s on s.id = p.idleft join keyword k on k.id = p.idwhere s.id is not null or k.id is not nullorder by score desclimit match_count;$$;
ERROR: column "embedding" is of type vector but expression is of type text が出る場合は、ベクトルデータが文字列として送信されています。Supabase JavaScript クライアントでは、配列を直接渡せば自動で vector 型にキャストされます。
Edge Function のタイムアウト
OpenAI API の応答が遅い場合、Edge Function がタイムアウトすることがあります。バッチ処理では、1 回の呼び出しで処理する件数を 50 件以内に抑え、必要に応じて複数回呼び出す設計にしてください。
インデックスの再構築
データを大量に追加・削除した後は、IVFFlat インデックスの精度が低下する場合があります。定期的に REINDEX INDEX を実行してください。
-- インデックスの再構築reindex index products_embedding_idx;
まとめ
pgvector を動かすところまでは、SQL 数行と Edge Function 一本で辿り着けます。難しいのはその先、近似で取りこぼしていないかを自分で測り続けることでした。