DEV Community: Phani Saripalli

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

Phani Saripalli — Sat, 11 Jul 2026 06:45:11 +0000

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.

AI Bought Us Time. We Spent It on More Work.

Phani Saripalli — Sat, 11 Jul 2026 05:41:34 +0000

Every engineering leader I've spoken with over the last two years seemed to be making a similar quiet bet. The hope was that by putting effective AI tooling in front of a capable team, the grind would lift. Boilerplate would write itself, test scaffolding would appear, and that two-hour detour into an unfamiliar API would vanish. In my experience, those predictions largely held true. The grind did lift.

Then, we did something with the time we regained, and looking back, it seems most of us did it without really thinking. We asked for more.

It wasn't malicious. I didn't see anyone stand up and announce that the team was now expected to carry a heavier load. It seemed to happen by omission. A sprint that previously held six tickets quietly started holding eight, simply because eight now fit. The demo that would have taken a week landed in three days, so a three-day cadence became the new expectation. The speed became our baseline. And once that speed was the baseline, it stopped feeling like a gift and started to feel like a floor we were constantly bracing against.

That, as I've come to see it, is the trap. It wasn't that AI created burnout; it was that the way we reinvested its dividend may have accelerated it.

I include myself in this. Early on, I treated those reclaimed hours as pure capacity — free, high-quality capacity that saved us from hiring. So, I filled it. It took me longer than I'd like to admit to notice that a team shipping more wasn't necessarily a team doing better. The people around me seemed to grow quieter and flatter — careful in the way people become when they are running hard just to stay in the same place.

My takeaway from this period is a simple reframe: efficiency is not the same as throughput. We allowed those two words to become synonyms in our planning, and I believe it cost us. A team that ships more tickets is not necessarily more efficient if a significant portion of that output is code nobody fully understands, resting on debt that may cost more to remediate later than it saved to write initially. I've found that true efficiency often looks more like a team that understands the systems it owns, makes deliberate choices about what not to build, and doesn't burn itself out in the process. That is harder to track on a burndown chart, but I believe it is worth far more.

The risk, as I saw it, was that the baseline reset without a conscious decision. That strikes me as the classic signature of poorly managed change: the most consequential shift in how a team functions arrives as a default, and by the time you notice, it has already become culture. I've learned that a leader's role is to make that choice out loud — to decide, deliberately, where the saved time goes. Because it is going to be spent either way.

In my view, there are four key areas where we should be directing this dividend:

Thinking time. I realised the truly scarce resource in engineering was never typing speed. It was having the uninterrupted space to understand a problem before committing to a solution. AI writes code quickly, but it doesn't tell you what to build, and it will often confidently help you build the wrong thing. The best engineering decisions — the ones that remove the need for work entirely — only seem to emerge when someone has room to think. Filling every reclaimed hour with another ticket often results in the faster delivery of things that perhaps shouldn't have been built.
Depth over breadth. I've found it more effective to give people the room to truly know the systems they are responsible for, rather than skating across ten they only half-understand. AI makes it trivial to ship into a codebase you don't grasp, but that only lasts until it breaks at 2:00 AM and a human has to hold it together. I think reclaimed time should be used to buy comprehension, which is often what turns a potential crisis into a manageable incident.
Craft, and the broken ladder. Juniors historically learned by doing the unglamorous work that AI now absorbs. That work was rarely just output; it was an apprenticeship. If we remove that work, we may be quietly removing the rungs people need to climb. If we don't consciously reinvest that time into how engineers grow, we risk building a team that can prompt fluently but struggles to reason. Growth, I've learned, is no longer a guaranteed by-product of the work; it has to be designed back in.
Recovery. Not everything of value is output. A portion of that time should simply be for rest — the necessary input that keeps the other three goals sustainable. Speed as a permanent default, without a trough after the peak, is not a high-performance culture in my eyes. It is a burnout schedule with better branding.

I recognise the tension here; dodging it would feel naive. Businesses pay for speed, and they expect a return. I am not arguing for slack as a reward or comfort as a core company value. I am arguing that reinvesting the dividend in your people is a high-yield return. A team that understands its systems tends to ship faster over any horizon longer than a quarter. A team that isn't exhausted tends to keep its best people. In my experience, judgment, depth, and retention are not "soft" concepts — they are the assets that make next year more effective than this one. This isn't a wellbeing argument; it's a strategy argument that happens to be humane.

Innovation follows a similar pattern. True breakthroughs rarely come from a team running at full capacity. They come from the margins — the afternoon someone follows an interesting hunch, or the tangent that eventually becomes next year's product. Margin is the first thing a throughput-focused culture kills, yet it is the last thing it can manufacture on demand. If you want a team that invents, you have to leave them the room to do so.

Ultimately, this is the work of leadership. When AI hands your team time back — and it will continue to do so — you have to decide what happens to that time. You can let the baseline creep up in the dark until your best engineers are exhausted, or you can spend the dividend on the people themselves.

The tools got faster. The real question I've been asking myself is whether I am paying enough attention to where the time actually goes.