Your AI Feature Doesn't Need Pinecone. It Needs pgvector.

A search bar feels like an Elasticsearch problem the moment someone asks for "did you mean...?" or relevance ranking. So the data gets duplicated into a second cluster, a sync job gets built to keep it current, and now there are two sources of truth for the same rows.

pgvector puts the embedding in the same row as everything else about the thing it describes, so a hybrid semantic + relational query — "find documents like this one, but only ones this specific user wrote" — is just... a query.

The setup

Install the extension, add a vector(n) column, and index it with HNSW for fast approximate nearest-neighbor search.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  author_id   bigint REFERENCES users(id),
  content     text,
  embedding   vector(1536)
);

CREATE INDEX idx_documents_embedding
  ON documents USING hnsw (embedding vector_cosine_ops);

HNSW (Hierarchical Navigable Small World) builds a multi-layered graph over your vectors — think of it as a high-dimensional skip list — so approximate nearest-neighbor search stays fast as the table grows.

The query that used to need two databases

Here's the actual RAG query, one round trip:

SELECT content FROM documents
WHERE author_id = 42
ORDER BY embedding <=> '[0.012, -0.045, 0.031, ...]'::vector
LIMIT 5;

Semantic ranking and a relational filter, one transaction, one system to keep consistent.

The gotcha nobody mentions

With an approximate index like HNSW, Postgres fetches the nearest candidates first, then applies the WHERE clause after. If author_id = 42 is a narrow slice of a large table, you can get back fewer than 5 rows — not an error, just a quietly short result.

The fix, if you're on pgvector 0.8 or newer:

SET hnsw.iterative_scan = 'strict_order';

This keeps scanning until the filter is actually satisfied instead of settling for whatever the first pass turned up. Worth checking your version — SELECT extversion FROM pg_extension WHERE extname = 'vector'; — since some package managers (looking at you, plain apt) ship versions old enough not to have this option yet.

Why this beats a dedicated vector database for most teams

• ▹Embeddings and the rows they describe are written in the same transaction — they can never drift out of sync.
• ▹Hybrid search is a single query instead of an application-layer join between two stores.
• ▹One fewer vendor, one fewer bill. No managed-vector cluster to size, patch, and monitor.

Where Pinecone still wins

Billion-vector scale, with dedicated horizontally-sharded ANN infrastructure and managed elastic scaling. If you're not there yet, you're paying for infrastructure you don't need.