You don’t need Pinecone to store embeddings. If you already run Postgres, pgvector adds vector search as a column type and an index — nothing new to operate.

Install it

Most managed Postgres providers (RDS, Supabase, Neon, Cloud SQL) ship pgvector as an available extension already. Enable it:

CREATE EXTENSION IF NOT EXISTS vector;

Self-hosting? It’s a standard Postgres extension — apt install postgresql-16-pgvector on Debian/Ubuntu, or build from source if your distro doesn’t package it.

Schema

Add a vector column sized to your embedding model’s output. OpenAI’s text-embedding-3-small is 1536 dimensions; a lot of open models use 768 or 1024. Check your model, don’t guess:

ALTER TABLE documents ADD COLUMN embedding vector(1536);

Index it

Without an index, pgvector does an exact nearest-neighbor scan — fine for a few thousand rows, too slow past that. Two index types matter:

• HNSW — better recall, better query speed, slower to build and more memory at build time. The default choice as of pgvector 0.5+.
• IVFFlat — faster to build, needs a lists parameter tuned to your row count, slightly worse recall.

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Use vector_cosine_ops for embeddings from OpenAI or most sentence-transformer models — they’re trained for cosine similarity. Check your model’s docs before assuming; some are trained for dot product (vector_ip_ops) instead, and using the wrong operator silently gives you worse-than-random-looking results without erroring.

Querying

SELECT id, title, embedding <=> '[0.012, -0.045, ...]' AS distance
FROM documents
ORDER BY embedding <=> '[0.012, -0.045, ...]'
LIMIT 5;

<=> is cosine distance (lower is more similar). <-> is Euclidean, <#> is negative inner product. Match the operator to the index op class you built, or the index won’t be used and every query falls back to a full scan.

The part people get wrong

HNSW index build time and memory scale with row count and dimension count together. A 1536-dim column with a few million rows can take a genuinely long time to build and use several GB of RAM during the build. If you’re bulk-loading historical data, load the rows first, then create the index — building an HNSW index incrementally row-by-row on insert is much slower than building it once over the finished table.

Also set ef_search per query if recall matters more than speed for a particular endpoint:

SET hnsw.ef_search = 100;

Higher values search more of the graph and improve recall at the cost of latency. Default is 40; bump it for anything where a missed result is worse than a slower response.

When to actually reach for a dedicated vector DB instead

• Tens of millions of vectors with high query volume, where a dedicated engine’s sharding and memory management outperforms Postgres.
• You need multi-tenant isolation at a scale where row-level security in Postgres becomes the bottleneck.
• Your team is already running Postgres at its ceiling for unrelated reasons and can’t absorb more load.

Below that scale, pgvector in your existing database is less infrastructure, one fewer network hop between your app and your vectors, and lets you JOIN embeddings against your actual relational data in a single query — something a standalone vector DB can’t do at all. Start here and move only when you have a specific number that says you must.