Launch Workspace — "Just Use Postgres" Week 3 (Article 3 / Week 4)

Dev.to title: Your AI Feature Doesn't Need Pinecone. It Needs pgvector.
Slug: /blog/replace-pinecone-with-pgvector
Canonical URL: https://omenabyte.com/blog/replace-pinecone-with-pgvector
dev.to tags: postgres, ai, vectordatabase, tutorial
Cover: https://omenabyte.com/blog/just-use-postgres-cover.png
Series: The Field Manual Series
Medium topics: PostgreSQL, Databases, SQL, Web Development, Software Engineering

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

The moment a project needs semantic search or RAG, the instinct is to bolt on a dedicated vector database. It works fine right up until you need a semantic match and a relational filter at the same time — "find documents like this one, but only ones this specific user wrote" — and now you're querying two separate systems and stitching results back together over a network call.

pgvector puts the embedding in the same row as everything else about the thing it describes, so that query is just... a query.

The setup

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

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 round trip, one transaction, one system to keep consistent.

The gotcha nobody mentions

Here's something worth knowing before you ship this: 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 (semantic + relational) is a single query instead of an application-layer join.
• One fewer vendor, one fewer bill.

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.

This is one of eight infrastructure swaps in Just Use Postgres, a 24-page field manual on replacing MongoDB, Redis, Elasticsearch, Pinecone, and more with the database you're probably already running. Every recipe in it — including this one — was run against a live Postgres instance with pgvector before it went in the book.

👉 Get the field manual — launch price $14 instead of walking into a $50/month managed-vector invoice

🐳 Or take the $24 bundle with the full docker-compose up starter repo — all 8 modules as tested, runnable migrations + seed data: https://4693433176360.gumroad.com/

Read this on omenabyte.com → https://omenabyte.com/blog/replace-pinecone-with-pgvector