pgvector or Pinecone: A reflection on choosing to provide business value

pgvector or Pinecone" : this is not entirely about which one. This is about when and why. If you are contemplating on implementing, you must before it is too later. I've shipped semantic search and recommendations on both, and the honest answer to "which should I use?" is the least satisfying one: it depends on your scale, your team, and how much database you want to run yourself. What follows is how each actually works, how to use them from Python, when to reach for which, and three problems people hit in production once the demo is over.

First, the thing both of them do

An embedding is a list of numbers that captures the meaning of a piece of text (or an image, or audio). Two descriptions that mean similar things end up close together in that number-space; unrelated ones end up far apart. You generate embeddings with a model — OpenAI's text-embedding-3-small (1536 dimensions), Amazon's Titan Text Embeddings V2 (1024), or others — and you store them.

A vector database exists to answer one question quickly: given this query vector, which stored vectors are nearest? That's it. "Find me films like this one," "find the support article that answers this question," "recommend the next thing to watch" — all the same nearest-neighbour lookup underneath.

Throughout, I'll use a small film catalogue as the running example, because searching plot descriptions by meaning is easy to picture:

"A lonely robot wakes up on an abandoned space station."
"Two rival chefs fall in love during a cooking competition."
"A detective hunts a killer through 1940s Los Angeles."

Ask for "space adventure with a machine" and you want the first one back, even though it shares not a single keyword. That's the point of vectors over plain keyword search.

But a real film record is never just a plot. It has a genre, a director, a cast, a release year, maybe a festival run. You almost never search on the plot vector alone — you search "sci-fi like this one, directed by someone, that played a festival." That combination of meaning plus metadata is where the interesting engineering lives, so I'll build the example that way from the start.

Part 1 — pgvector on Aurora PostgreSQL

pgvector is an open-source extension that adds a vector type and nearest-neighbour indexes to PostgreSQL. The pitch is blunt and powerful: if you already run Postgres, you don't need a second database for vectors. Your embeddings live next to the rows they describe — the genre, the director, the cast — under the same transactions, the same backups, the same security model.

Getting it running on Aurora

Recent Aurora PostgreSQL versions (14.x, 15.x, 16.x, 17.x — check the exact minor version) ship pgvector 0.8.x. One thing that trips people up: on Aurora the extension version is tied to the engine version, so if you're stuck on an old pgvector you upgrade the cluster first, then the extension.

-- once per database
CREATE EXTENSION IF NOT EXISTS vector;

-- if you're on an older extension after an engine upgrade
ALTER EXTENSION vector UPDATE;

CREATE TABLE films (
    id           bigserial PRIMARY KEY,
    title        text NOT NULL,
    description  text NOT NULL,
    genre        text,
    director     text,
    cast_list    text[],          -- Postgres arrays are perfect for this
    festival     text,            -- e.g. 'Cannes 2023', or NULL
    release_year int,
    embedding    vector(1024)     -- Titan V2 = 1024 dims
);

(cast is a reserved word in SQL, hence cast_list — and note it's a native Postgres array, one of those small conveniences you get for free by staying inside the database you already run.)

One instance decision matters more than any other on Aurora: pick a memory-optimised (r-series) instance. The nearest-neighbour index has to sit in RAM to be fast, so a memory-bound instance class isn't a nice-to-have. More on that in the problems section.

Putting films in, from Python

You embed the description with your model of choice, then insert the vector alongside the ordinary metadata. Using psycopg (v3) with the pgvector adapter:

import psycopg
from pgvector.psycopg import register_vector
from openai import OpenAI

ai = OpenAI()
conn = psycopg.connect("postgresql://user:pass@your-aurora-endpoint:5432/app")
register_vector(conn)          # teaches psycopg the vector type

def embed(text: str) -> list[float]:
    resp = ai.embeddings.create(model="text-embedding-3-small", input=text)
    return resp.data[0].embedding

films = [
    {"title": "Solaris Station",
     "description": "A lonely robot wakes up on an abandoned space station.",
     "genre": "Sci-Fi", "director": "Ada Kern",
     "cast_list": ["Lena Ford", "Omar Diaz"],
     "festival": "Venice 2024", "release_year": 2024},
    {"title": "Two Spoons",
     "description": "Two rival chefs fall in love during a cooking competition.",
     "genre": "Romance", "director": "Marco Vidal",
     "cast_list": ["Sofia Reyes", "Tom Blake"],
     "festival": None, "release_year": 2023},
    {"title": "Chinatown Nights",
     "description": "A detective hunts a killer through 1940s Los Angeles.",
     "genre": "Noir", "director": "Ada Kern",
     "cast_list": ["Ruby Hale", "Sam Ono"],
     "festival": "Cannes 2022", "release_year": 2022},
]

with conn.cursor() as cur:
    for f in films:
        cur.execute(
            """INSERT INTO films
               (title, description, genre, director, cast_list,
                festival, release_year, embedding)
               VALUES (%(title)s, %(description)s, %(genre)s, %(director)s,
                       %(cast_list)s, %(festival)s, %(release_year)s, %(emb)s)""",
            {**f, "emb": embed(f["description"])},
        )
conn.commit()

(On Aurora and want to stay inside AWS? Swap the OpenAI call for a Bedrock Titan call — same shape, embedding stays vector(1024).)

Indexing and querying — meaning and metadata

Without an index, pgvector does an exact sequential scan — correct, but it reads every row. Fine for thousands, fatal for millions. For production you want an HNSW index, with a distance operator that matches your embeddings; for text, cosine distance (<=>) is the usual choice.

CREATE INDEX ON films
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

Now the query that shows off pgvector's real advantage — nearest-neighbour search filtered by ordinary columns, in one statement:

def search(query: str, genre: str | None = None,
           festival_only: bool = False, k: int = 5):
    q = embed(query)
    sql = """
        SELECT title, director, festival, embedding <=> %(q)s AS distance
        FROM films
        WHERE (%(genre)s IS NULL OR genre = %(genre)s)
          AND (NOT %(fest)s OR festival IS NOT NULL)
        ORDER BY embedding <=> %(q)s
        LIMIT %(k)s
    """
    with conn.cursor() as cur:
        cur.execute(sql, {"q": q, "genre": genre, "fest": festival_only, "k": k})
        return cur.fetchall()

# "sci-fi like this, that actually played a festival"
for title, director, festival, dist in search(
        "space adventure with a machine", genre="Sci-Fi", festival_only=True):
    print(f"{dist:.3f}  {title} — dir. {director} ({festival})")

That's the superpower: the vector similarity and the WHERE genre = 'Sci-Fi' AND festival IS NOT NULL happen together, in one round trip, against the same table, with real transactions. No syncing a separate metadata store. (Hold that thought — it's also the source of the first production gotcha below.)

When and why to choose pgvector

Choose it when you already run Postgres, your corpus is in the millions rather than hundreds of millions (a well-sized Aurora instance handles roughly 10M vectors comfortably, more with tuning), and you value keeping vectors, metadata and business data in one consistent, backed-up, access-controlled place. For teams with EU-data-residency or compliance constraints, "the vectors never leave our database" is often the line that ends the debate. And there's no second vendor, no second bill, no second thing to page you at 3am.

Part 2 — Pinecone

Pinecone is a fully managed, serverless vector database. You never provision a server, never tune an index, never run a REINDEX. You create an index over an HTTP API, upsert vectors with their metadata, and query them. Pinecone owns the operational surface entirely.

Using it from Python

from pinecone import Pinecone, ServerlessSpec
from openai import OpenAI

ai = OpenAI()
pc = Pinecone(api_key="...")

pc.create_index(
    name="films",
    dimension=1536,                       # must match your embedding model
    metric="cosine",
    spec=ServerlessSpec(cloud="aws", region="us-east-1"),
)
index = pc.Index("films")

def embed(text: str) -> list[float]:
    return ai.embeddings.create(
        model="text-embedding-3-small", input=text
    ).data[0].embedding

index.upsert(vectors=[
    ("solaris", embed("A lonely robot wakes up on an abandoned space station."),
     {"title": "Solaris Station", "genre": "Sci-Fi",
      "director": "Ada Kern", "festival": "Venice 2024"}),
    ("chinatown", embed("A detective hunts a killer through 1940s Los Angeles."),
     {"title": "Chinatown Nights", "genre": "Noir",
      "director": "Ada Kern", "festival": "Cannes 2022"}),
])

# same "sci-fi that played a festival" idea, via a metadata filter
res = index.query(
    vector=embed("space adventure with a machine"),
    top_k=5, include_metadata=True,
    filter={"genre": {"$eq": "Sci-Fi"}, "festival": {"$exists": True}},
)
for m in res["matches"]:
    print(f"{m['score']:.3f}  {m['metadata']['title']}")

Two things worth knowing. Pinecone now offers hosted embeddings (its Inference API), so you can hand it raw text and skip the separate OpenAI call — one fewer key to rotate. And namespaces partition an index cheaply, which makes per-tenant isolation trivial: one namespace per customer, queries never cross the boundary.

When and why to choose Pinecone

Choose it when you don't want to run a database at all, when you're at very large scale (100M to billions of vectors), when traffic is bursty and serverless scale-to-zero saves money overnight, or when you want multi-region low latency and hybrid (keyword + vector) search out of the box. For a small team that wants to ship an AI feature this week and never think about index memory, that zero-ops promise is worth paying for.

The honest decision

The truth most vendor posts bury: for a large middle ground, either is fine. Under a few million vectors at moderate query volume, both serve sub-100ms results and neither choice will make or break you. In that zone, decide on team grounds, not benchmarks — do you want one less system to operate (Pinecone) or one less vendor and full SQL integration (pgvector)?

It only gets sharp at the edges:

• Already on Postgres, data-residency matters, millions not billions → pgvector.
• No appetite to run a database, huge or bursty scale, want it managed → Pinecone.
• Cost-sensitive at steady, predictable volume → self-hosted pgvector usually wins on raw price; Pinecone wins once you price in the engineering hours to operate it. Value your team's time honestly — the "cheaper" option that eats a week of tuning often isn't.

Three problems people actually hit

This is the part the tutorials skip. None of these mean the tool is bad — they're the potholes you want to know about before you're driving over them at speed.

1. Your filtered search comes back nearly empty — and it's lying to you (pgvector). You ask for the 10 sci-fi films that played a festival, and get 3 back. First instinct: the catalogue's broken. It isn't. Older pgvector finds the 10 nearest films first, then throws away the ones that don't match your filter — so seven perfectly good matches, sitting slightly further out in the embedding space, never get considered. It's the single most common "why is this empty?" moment for anyone building filtered search. The fix shipped in pgvector 0.8 as iterative scans, which keep pulling from the index until they've genuinely got enough rows that pass your filter. If you're on an older version, this alone is worth the upgrade.

2. It flew in the demo, then fell off a cliff in production (pgvector on Aurora). Ten thousand films on your laptop: instant. Load the real catalogue and suddenly queries drag and index builds take hours. Almost always the same cause — the nearest-neighbour index has outgrown available memory, and the moment it can't sit in RAM, everything slows to disk speed. The fix is boring and effective: run on a memory-optimised (r-series) Aurora instance sized so the index fits in memory, and give index builds enough headroom to finish. If the index genuinely can't fit on one machine, that's your signal you've outgrown a single instance — time for pgvectorscale or a dedicated engine.

3. The Pinecone bill arrives at 3–5× the estimate (Pinecone). You modelled cost as a read-heavy search workload — occasional writes, lots of queries — and the calculator gave a comfortable number. Then you shipped AI agents that write to memory on every step, "capacity fees" quietly switched on under sustained load, and finance sent you a confused email. The fix is architectural, not magical: batch and deduplicate writes instead of upserting on every loop, be deliberate about what actually needs to be a stored vector, and model cost against your real write frequency — not the read-heavy default everyone assumes. And keep the plan's monthly minimum in mind before you commit.

The takeaway

pgvector and Pinecone aren't really rivals so much as answers to different questions. pgvector asks, "you already have Postgres — why add a system?" Pinecone asks, "you want vectors — why run a system at all?" Both are correct, for different teams on different days. Start from what you already operate, be honest about the scale you're actually at rather than the scale you fantasise about, and treat your engineers' time as the real currency. The rest is tuning — and now you know where the sharp edges are before you hit them.