Pgvector Advanced

pgvector Advanced

Extension Setup

Requires pgvector 0.7+ for halfvec, binary quantization, and sparsevec.

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion FROM pg_extension WHERE extname = 'vector';  -- expect >= 0.7.0

CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- tsvector / tsquery are built-in

Data Types (pgvector 0.7+)

CREATE TABLE chunks (
  id bigserial PRIMARY KEY,
  tenant_id uuid NOT NULL,
  content text NOT NULL,
  -- Full precision for reranking
  embedding vector(1536),
  -- Half precision for fast ANN
  embedding_half halfvec(1536),
  -- Binary for ultra-fast filter
  embedding_bit bit(1536),
  content_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
  metadata jsonb DEFAULT '{}',
  created_at timestamptz DEFAULT now()
);

HNSW vs IVFFlat

HNSW Tuning

-- m: max connections per layer (default 16). Higher = better recall, more memory.
-- ef_construction: build-time search width (default 64). Higher = better index, slower build.
CREATE INDEX chunks_hnsw_idx
ON chunks USING hnsw (embedding_half halfvec_cosine_ops)
WITH (m = 16, ef_construction = 200);

-- ef_search: query-time search width (default 40). Higher = better recall, slower query.
SET hnsw.ef_search = 100;

-- Parallel builds (pgvector 0.6+)
SET max_parallel_maintenance_workers = 7;
SET maintenance_work_mem = '8GB';
CREATE INDEX CONCURRENTLY ...;

IVFFlat Tuning

-- lists: number of partitions. Rule of thumb: rows/1000 up to 1M, then sqrt(rows).
CREATE INDEX chunks_ivf_idx
ON chunks USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);

-- probes: partitions scanned at query time. Higher = better recall, slower.
SET ivfflat.probes = 10;

Build IVFFlat AFTER inserting representative data — centroids are computed once.

Binary Quantization

Use bit columns with Hamming distance for ultra-fast prefilter, then rescore with full-precision vectors.

-- Populate bit column at insert
INSERT INTO chunks (content, embedding, embedding_half, embedding_bit)
VALUES (
  $1,
  $2::vector,
  $2::vector::halfvec(1536),
  binary_quantize($2::vector)::bit(1536)
);

CREATE INDEX chunks_bit_idx
ON chunks USING hnsw (embedding_bit bit_hamming_ops)
WITH (m = 16, ef_construction = 100);

Rescoring Query Pattern

WITH candidates AS (
  SELECT id, content, embedding
  FROM chunks
  WHERE tenant_id = $1
  ORDER BY embedding_bit <~> binary_quantize($2::vector)::bit(1536)
  LIMIT 100
)
SELECT id, content, 1 - (embedding <=> $2::vector) AS score
FROM candidates
ORDER BY embedding <=> $2::vector
LIMIT 10;

Latency drop: often 5-10x vs full float32 HNSW, with recall@10 recovered by rescoring.

Hybrid Search (vector + BM25-like + trigram)

-- tsvector (BM25-ish via ts_rank_cd)
CREATE INDEX chunks_tsv_idx ON chunks USING gin (content_tsv);
-- trigram (fuzzy keyword)
CREATE INDEX chunks_trgm_idx ON chunks USING gin (content gin_trgm_ops);

WITH
  vec AS (
    SELECT id, 1 - (embedding <=> $1::vector) AS vec_score
    FROM chunks
    WHERE tenant_id = $2
    ORDER BY embedding <=> $1::vector
    LIMIT 50
  ),
  lex AS (
    SELECT id, ts_rank_cd(content_tsv, plainto_tsquery('english', $3)) AS lex_score
    FROM chunks
    WHERE tenant_id = $2
      AND content_tsv @@ plainto_tsquery('english', $3)
    LIMIT 50
  )
SELECT c.id, c.content,
       COALESCE(vec.vec_score, 0) * 0.6 + COALESCE(lex.lex_score, 0) * 0.4 AS score
FROM chunks c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN lex ON lex.id = c.id
WHERE (vec.id IS NOT NULL OR lex.id IS NOT NULL)
ORDER BY score DESC
LIMIT 10;

Reciprocal Rank Fusion (RRF)

WITH
  vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) AS rnk
    FROM chunks WHERE tenant_id = $2
    ORDER BY embedding <=> $1::vector LIMIT 50
  ),
  lex AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(content_tsv,
             plainto_tsquery('english', $3)) DESC) AS rnk
    FROM chunks WHERE tenant_id = $2
      AND content_tsv @@ plainto_tsquery('english', $3)
    LIMIT 50
  )
SELECT id, SUM(1.0 / (60 + rnk)) AS rrf
FROM (
  SELECT id, rnk FROM vec
  UNION ALL
  SELECT id, rnk FROM lex
) x
GROUP BY id
ORDER BY rrf DESC
LIMIT 10;

Multi-Tenant Patterns

Partitioning by tenant_id (best for large SaaS)

CREATE TABLE chunks (
  id bigserial,
  tenant_id uuid NOT NULL,
  embedding halfvec(1536),
  content text,
  PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);

CREATE TABLE chunks_p0 PARTITION OF chunks FOR VALUES WITH (MODULUS 16, REMAINDER 0);
-- ... p1..p15

Build HNSW index per partition — smaller indexes, better cache locality.

Row-level security (RLS)

ALTER TABLE chunks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON chunks
USING (tenant_id = current_setting('app.tenant_id')::uuid);

Index Build at Scale

-- Before bulk load
SET maintenance_work_mem = '16GB';
SET max_parallel_maintenance_workers = 7;

-- Load data first, THEN create index (10-50x faster than inserting into indexed table)
TRUNCATE chunks;
COPY chunks(tenant_id, content, embedding) FROM '/tmp/embeddings.csv' CSV;
CREATE INDEX CONCURRENTLY chunks_hnsw_idx
  ON chunks USING hnsw (embedding halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 200);
ANALYZE chunks;

Replication and HA

• pgvector indexes replicate via standard streaming/physical replication.
• vector types are BINARY — logical replication works but requires matching extension versions on both ends.
• Indexes on replicas rebuild on promotion; check WAL sender / receiver lag during bulk reindex.
• Read replicas: pin embedding model version in app; mismatched indexes cause silent recall loss.

Supabase-Specific Patterns

-- Supabase exposes vector search via RPC. Typical pattern:
CREATE OR REPLACE FUNCTION match_chunks(
  query_embedding halfvec(1536),
  match_threshold float,
  match_count int,
  p_tenant_id uuid
)
RETURNS TABLE (id bigint, content text, similarity float)
LANGUAGE sql STABLE
AS $$
  SELECT c.id, c.content, 1 - (c.embedding_half <=> query_embedding) AS similarity
  FROM chunks c
  WHERE c.tenant_id = p_tenant_id
    AND 1 - (c.embedding_half <=> query_embedding) > match_threshold
  ORDER BY c.embedding_half <=> query_embedding
  LIMIT match_count;
$$;

Supabase HNSW gotchas:

• ef_search is per-session; set via SET LOCAL hnsw.ef_search inside the RPC.
• SECURITY DEFINER functions lose RLS; keep SECURITY INVOKER and pass tenant_id.

Node.js Client

import pg from 'pg';
import pgvector from 'pgvector/pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
await pgvector.registerTypes(pool);

export async function search(tenantId: string, qVec: number[], k = 10) {
  const { rows } = await pool.query(
    `SET LOCAL hnsw.ef_search = 100;
     SELECT id, content, 1 - (embedding_half <=> $1::halfvec) AS score
     FROM chunks
     WHERE tenant_id = $2
     ORDER BY embedding_half <=> $1::halfvec
     LIMIT $3`,
    [pgvector.toSql(qVec), tenantId, k]
  );
  return rows;
}

Anti-Patterns

Production Checklist

• pgvector version >= 0.7 confirmed
• halfvec used for index column, full vector for rescoring
• HNSW params (m, ef_construction) tuned and recorded
• ef_search set per query for target recall
• Tenant filter present in every query (RLS or explicit)
• Hybrid search with tsvector + pg_trgm + vector
• maintenance_work_mem and parallel workers sized for build
• Monitoring: query latency p95, index bloat, replica lag
• Backup/restore tested with vector columns