Confession: I spent last Sunday porting a RAG side project off Pinecone. Not because Pinecone was bad. It was actually fine. Query latency was solid, the client library barely got in my way, and I’d never had a single support ticket. I just got tired of paying for a separate database to hold 40,000 rows of chunked docs when Postgres was sitting right there, humming along.
I moved everything into pgvector in about three hours. My monthly infra bill dropped by $27. My deploy script lost twenty lines. My mental model of the app compressed from “two databases that need to stay in sync” to “one database with a weird column type.”
I’m not going to tell you pgvector is the best vector database for every workload. It isn’t. But it’s the one I reach for first now, and I think that shift is worth writing down. Here’s what changed for me: the setup, the index picks I stopped agonizing over, the hybrid search query that finally made me stop caring about Elasticsearch for this stuff, and the two failure modes that will absolutely burn you if you go in blind.
What pgvector actually is (in one paragraph)
pgvector is a Postgres extension that adds a vector column type and a few distance operators (<-> for L2, <=> for cosine, <#> for inner product). It supports two ANN index types: IVFFlat (older, faster to build, worse recall at high dimensions) and HNSW (newer, slower to build, much better recall). That’s it. There’s no separate service, no separate client SDK, no separate auth. If you can run SELECT, you can query a vector column.
I mention this because a lot of the “should I use a vector DB” content online treats pgvector as the compromise option. The thing you pick when you want to save money but accept worse performance. That framing was maybe fair in 2023. In 2026, with HNSW landed and a healthy set of production stories from teams running billions of embeddings on it, that framing is out of date.
The setup I actually use
Here’s the old way. The “two databases” pattern, roughly:
# app.py - the two-DB setup I ripped out
import psycopg
from pinecone import Pinecone
pg = psycopg.connect(POSTGRES_URL)
pc = Pinecone(api_key=PINECONE_KEY)
index = pc.Index("docs-prod")
def store_chunk(chunk_id: str, doc_id: str, text: str, embedding: list[float]):
with pg.cursor() as cur:
cur.execute(
"INSERT INTO chunks (id, doc_id, text) VALUES (%s, %s, %s)",
(chunk_id, doc_id, text),
)
index.upsert([(chunk_id, embedding, {"doc_id": doc_id})])
def search(query_vec: list[float], k: int = 5):
hits = index.query(vector=query_vec, top_k=k, include_metadata=True)
ids = [h["id"] for h in hits["matches"]]
with pg.cursor() as cur:
cur.execute("SELECT id, text FROM chunks WHERE id = ANY(%s)", (ids,))
return cur.fetchall()
Two writes per chunk. Two systems to keep in sync. One extra network hop on every query, plus a lookup back into Postgres to hydrate the text I actually wanted.
Here’s the pgvector version:
# app.py - one Postgres, one query
import psycopg
pg = psycopg.connect(POSTGRES_URL)
def store_chunk(chunk_id: str, doc_id: str, text: str, embedding: list[float]):
with pg.cursor() as cur:
cur.execute(
"INSERT INTO chunks (id, doc_id, text, embedding) "
"VALUES (%s, %s, %s, %s)",
(chunk_id, doc_id, text, embedding),
)
def search(query_vec: list[float], k: int = 5):
with pg.cursor() as cur:
cur.execute(
"SELECT id, text FROM chunks "
"ORDER BY embedding <=> %s LIMIT %s",
(query_vec, k),
)
return cur.fetchall()
The schema is boring, which is the point:
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
id TEXT PRIMARY KEY,
doc_id TEXT NOT NULL,
text TEXT NOT NULL,
embedding vector(1536) NOT NULL
);
CREATE INDEX chunks_embedding_hnsw
ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
If you already have Postgres in your stack, you already have a vector database. You just have to install the extension and pick an operator class.
HNSW vs IVFFlat: the index pick I stopped agonizing over
For a while I would sit and stare at this decision. I read three papers, benchmarked both against my own data, then read the papers again. Here is the boring answer I landed on: for anything under ten million rows, use HNSW. Set m = 16 and ef_construction = 64. Move on with your life.
HNSW gives you way better recall at reasonable query latency, and the build time (which was the main knock against it in early pgvector versions) is fine for the row counts most side projects and even most SaaS products have. IVFFlat still wins if you’re above a hundred million rows and want a small index footprint, or if you’re rebuilding the index every few hours on a machine that can’t afford the CPU. Neither of those is where I live.
The one knob worth tuning at query time is hnsw.ef_search. Default is 40. If you care about recall, bump it to 80 or 100 per session:
SET LOCAL hnsw.ef_search = 100;
SELECT id, text FROM chunks ORDER BY embedding <=> $1 LIMIT 10;
There’s a real cost. Queries get slower. But you get a lever without rebuilding the index, which matters when your recall on the ground turns out to be worse than the benchmarks promised.
Hybrid search: FTS and vectors in one query
This is the thing that finally made me stop reaching for Elasticsearch on side projects. Postgres has full-text search that’s better than most people give it credit for, and you can combine it with vector similarity in a single query using reciprocal rank fusion. I wrote a longer post on when to reach for Postgres full-text search over Elasticsearch, and the punchline applies here too: for most workloads, the answer is “just use Postgres.”
A simple hybrid ranker looks like this:
WITH fts AS (
SELECT id,
ts_rank_cd(text_tsv, plainto_tsquery('english', $1)) AS score
FROM chunks
WHERE text_tsv @@ plainto_tsquery('english', $1)
ORDER BY score DESC LIMIT 50
),
vec AS (
SELECT id, 1 - (embedding <=> $2) AS score
FROM chunks
ORDER BY embedding <=> $2 LIMIT 50
),
ranked_fts AS (
SELECT id, row_number() OVER (ORDER BY score DESC) AS r FROM fts
),
ranked_vec AS (
SELECT id, row_number() OVER (ORDER BY score DESC) AS r FROM vec
)
SELECT id,
COALESCE(1.0 / (60 + rf.r), 0)
+ COALESCE(1.0 / (60 + rv.r), 0) AS score
FROM ranked_fts rf
FULL OUTER JOIN ranked_vec rv USING (id)
ORDER BY score DESC LIMIT 10;
It’s not the most beautiful SQL I’ve ever written. It also runs fine on a small database and gives me results that beat pure semantic search on any query with a specific noun in it. “What did the Q3 report say about ARR” is a question FTS crushes. “What’s the vibe of the customer support tickets” is one vectors crush. Reciprocal rank fusion lets both matter.
Where I still reach for a dedicated vector database
I want to be honest here because I’ve watched too many “just use Postgres” posts skip this part. Pinecone, Qdrant, and Weaviate exist for reasons, and those reasons don’t evaporate because HNSW landed in Postgres.
I still reach for something dedicated when I have hundreds of millions of vectors and I care about p99 latency more than anything else. Postgres will do this, but the ops story gets harder. Vacuum tuning, autovacuum lag, replica sync all become full-time problems, and purpose-built systems ship with sharding assumptions that fit this shape from day one.
I also reach for a dedicated store when I need first-class metadata filtering on high-cardinality dimensions with sub-100ms tail latency. pgvector can do filtered ANN via pre-filtering, but the recall/perf tradeoff is real above a certain scale. The Timescale team’s pgvectorscale extension helps here, and I’ve had good luck with it, but I don’t reach for it on every project.
And finally: org shape matters. If a data team runs Pinecone and an app team runs their own Postgres, forcing them to merge is more organizational work than infra saving.
For anything smaller (the “under 20 million vectors, embedding a knowledge base or product catalog or docs” size), pgvector is enough. That’s the vast majority of RAG side projects, internal tools, and even small SaaS features I’ve seen ship this year.
The two failure modes that will burn you
First: forgetting to set maintenance_work_mem before building the HNSW index. The default is 64MB, which will make the build take literal hours on a few-million-row table. You’ll assume pgvector is slow when in fact you starved it of RAM. Bump it to at least 2GB for the session that builds the index, then reset:
SET maintenance_work_mem = '2GB';
CREATE INDEX chunks_embedding_hnsw
ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
RESET maintenance_work_mem;
Second: thinking the ANN index will “just work” for filtered queries. If you write WHERE user_id = $1 ORDER BY embedding <=> $2 LIMIT 10, Postgres has to decide between using the HNSW index (which doesn’t know about your filter) or scanning by user_id and then sorting. On selective filters it often picks wrong. The workaround I use is either partial indexes (one HNSW index per tenant, works up to a few hundred tenants) or upgrading to pgvectorscale, which added proper filtered ANN. Run EXPLAIN on real data before you assume it’s fast.
Try one thing this week
If you already have Postgres in production and you’ve been putting off a vector database, install pgvector on a staging copy, dump 10,000 real rows in, and run one query. Not a benchmark. Just one real query with your real data. The whole thing takes less than an hour, and either you’ll find out it’s plenty fast for your workload (which is the common outcome), or you’ll have a concrete number to justify the dedicated vector DB in your next planning meeting.
I keep a running list of these “just try it for an hour” experiments on my work page. This one belongs near the top, right next to the SSE port I did last month. Both are the same kind of trade: fewer moving parts, one bill instead of two, and a stack that fits in your head at 2am when something breaks.
