Data & Infrastructure
Supabase pgvector Guide: Vector Search, HNSW Indexing & Cosine Distance in PostgreSQL
Build enterprise semantic search directly inside PostgreSQL using Supabase pgvector, HNSW indexing, and cosine distance operators.
Why a Single PostgreSQL Database Is All You Need
Using external vector databases (Pinecone, Qdrant) introduces data synchronization overhead, network hops, and separate billing.
Supabase pgvector keeps relational user data, permissions, and vector embeddings in a single ACID-compliant PostgreSQL database.
-- Enable pgvector extension
create extension if not exists vector;
-- Create documents table with 1536-dim embedding column
create table documents (
id bigserial primary key,
content text not null,
metadata jsonb default '{}'::jsonb,
embedding vector(1536)
);
-- Create HNSW index for ultra-fast cosine similarity search
create index on documents using hnsw (embedding vector_cosine_ops);
-- Similarity search function
create or replace function match_documents(
query_embedding vector(1536),
match_threshold float,
match_count int
)
returns table (id bigint, content text, similarity float)
language sql stable
as $$
select id, content, 1 - (documents.embedding <=> query_embedding) as similarity
from documents
where 1 - (documents.embedding <=> query_embedding) > match_threshold
order by documents.embedding <=> query_embedding
limit match_count;
$$;Related Technical Guides
Deepen your understanding with these closely related production architectures and tutorials:
Web Scraping Data Pipelines: Schema Validation, De-duplication & DB Loading
Design resilient ETL scraping pipelines with Pydantic validation, hash-based de-duplication, and idempotent PostgreSQL upserts.
Hybrid Search with BM25 & Vector pgvector: Reciprocal Rank Fusion (RRF)
Combine the precision of full-text BM25 keyword matching with dense semantic embeddings using Reciprocal Rank Fusion (RRF).
RAG Chunking Strategies: Fixed Size, Semantic Chunking & Markdown Hierarchy
Master document chunking: character splitting, semantic boundary detection, and table/header-aware recursive splitting.