When building a RAG system, the first question is usually 'which vector database?' Pinecone, Weaviate, Qdrant, and Chroma are all purpose-built options. Deeppin chose pgvector — PostgreSQL's vector extension. Not because pgvector is technically superior, but because it fits the current constraints best.

§ 01Part 1 — Co-location: the most underrated advantage

Deeppin's business data (sessions, threads, messages) lives in Supabase PostgreSQL. If vectors were in a separate Pinecone instance, RAG retrieval would require: query Pinecone for similar chunk_ids → round-trip back to PostgreSQL for metadata and filtering. Two network hops, plus data consistency concerns between two systems.

With pgvector, vector search and relational filtering happen in the same query:

SELECT c.content, c.filename,
       1 - (c.embedding <=> query_vector) AS similarity
FROM   attachment_chunks c
JOIN   attachments a ON a.id = c.attachment_id
WHERE  a.session_id = $session_id    -- relational filter
  AND  1 - (c.embedding <=> $vec) > 0.3
ORDER  BY c.embedding <=> $vec
LIMIT  8;

This single DB request performs vector similarity, session isolation, threshold filtering, ordering, and limit — a native combination impossible to replicate in a single request to a dedicated vector database.

§ 02Part 2 — RLS: row-level security at the vector layer

Supabase's Row Level Security applies directly to vector tables. User A's embeddings are completely invisible to User B — no application-layer filtering needed:

ALTER TABLE attachment_chunks ENABLE ROW LEVEL SECURITY;

CREATE POLICY "user_session_isolation"
  ON attachment_chunks
  USING (
    attachment_id IN (
      SELECT id FROM attachments
      WHERE session_id IN (
        SELECT id FROM sessions WHERE user_id = auth.uid()
      )
    )
  );

Dedicated vector databases typically lack native row-level permissions, requiring namespaces or metadata filters to simulate it — error-prone and costly to maintain.

§ 03Part 3 — Cost and operations

• Pinecone free tier: 1 index, 1M vectors, paid beyond that
• Weaviate Cloud free tier: limited quota, self-hosting requires extra machines
• pgvector in Supabase: included in PostgreSQL free tier, 500MB storage, zero extra cost
• One fewer service: every additional service adds operational burden, failure points, and monitoring targets

§ 04Part 4 — Performance: pgvector in practice

pgvector performs well at small scale (<1M vectors). Deeppin's vector tables (attachment_chunks and conversation_memories) hold tens to hundreds of thousands of vectors. With HNSW indexing, similarity queries typically complete in 10–50ms.

-- HNSW index (pgvector 0.5+)
CREATE INDEX ON attachment_chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Postgres auto-selects the index
EXPLAIN ANALYZE
SELECT * FROM attachment_chunks
ORDER BY embedding <=> '[...]'
LIMIT 8;

§ 05Part 5 — When to migrate

• Vector count exceeds 10M: HNSW index memory and query latency become bottlenecks
• Multi-modal vectors needed (images, audio): dedicated databases have better support
• Real-time index updates at high write throughput: pgvector HNSW degrades under heavy write load
• Vector search dominates total query volume: purpose-built optimizations become worthwhile

Deeppin is nowhere near any of these thresholds. 'Migrate when it actually becomes a bottleneck' is the correct engineering decision — premature optimization is the root of all evil.