A client's content library had outgrown keyword search; users wanted results based on meaning, not exact word matches, a search for "budget-friendly family trip ideas" should surface an article titled "affordable vacations with kids" even though the two share almost no words in common. Rather than standing up a dedicated vector database, we added the pgvector extension to their existing Postgres instance, which turned out to be both the pragmatic choice and, once we'd actually built with it, the technically better fit for how this client's data was structured.
Why we didn't reach for a dedicated vector database
The obvious-seeming path for semantic search is a purpose-built vector database, and there are genuinely good reasons teams choose that path: purpose-built systems can offer better performance at very large scale and richer built-in tooling for embedding management. But adopting a whole new category of database introduces a new operational surface, a new backup and monitoring story, a new set of failure modes for the team to learn, and a new place for data to get out of sync with the source of truth still living in Postgres. For a client whose content library, while large enough to need semantic search, wasn't at a scale where Postgres itself would become the bottleneck, that operational cost didn't buy them anything they actually needed.
Keeping vector search in the same database as the rest of the application data meant queries could combine semantic similarity with normal relational filters in a single query, which a separate vector database would have made far more awkward. A real search on this client's site isn't just "find similar content," it's "find similar content that's published, in the right content category, and not already shown to this user this week." Expressing that as one SQL query with pgvector's similarity operator sitting alongside ordinary WHERE clauses was straightforward. Doing the equivalent across two separate databases would have meant either querying the vector database for a wide candidate set and then filtering in application code, discarding a lot of the database's own query optimization in the process, or maintaining duplicate relational metadata inside the vector database itself, which reintroduces exactly the sync problem we were trying to avoid.
The hard part: tuning the index
Index tuning for approximate nearest-neighbor search took real experimentation to balance query speed against result accuracy for this client's specific data volume. pgvector supports a couple of different indexing approaches, and the tradeoffs between them aren't obvious from documentation alone, they depend heavily on the actual size and shape of the dataset being indexed. We ran the same evaluation set of realistic search queries against several index configurations, measuring both query latency and how well the returned results actually matched what a human reviewer judged as relevant, before settling on a configuration that hit the client's latency budget without a noticeable drop in result quality.
- Combining semantic similarity search with ordinary relational filters in a single query was simpler with pgvector than it would have been across two separate databases.
- Approximate nearest-neighbor index tuning required real experimentation against realistic query patterns, not just following default settings.
- Re-embedding content on every meaningful edit, rather than only on creation, kept search results from silently going stale.
- Query latency and result relevance had to be measured together, since optimizing for one in isolation degraded the other.
One detail that surprised us: the default index parameters that worked well in pgvector's own documentation examples performed noticeably worse on this client's actual content, which skewed toward much longer articles than the shorter reference documents most tuning guides implicitly assume. Getting a configuration that actually fit this client's content required generating a realistic embedding set from their real library early in the project, rather than assuming published benchmarks would transfer directly.
Keeping search results from going stale
A part of this project that's easy to overlook in the excitement of getting semantic search working the first time is the ongoing discipline of keeping embeddings current as content changes. Every time an article gets meaningfully edited, its embedding needs to be regenerated, or search results start silently drifting out of sync with what the article actually says. We built this as an automatic step triggered by the client's existing publish workflow, so an editor doesn't need to remember to do anything differently, but getting the triggering logic right, specifically distinguishing a meaningful content edit from a trivial metadata change that shouldn't trigger an expensive re-embedding, took a few iterations to get right without either missing real updates or re-embedding far more often than necessary.
Who this approach actually fits
For teams already running Postgres, pgvector is a lower-friction path to semantic search than standing up dedicated vector infrastructure, provided the scale doesn't outgrow what a single Postgres instance can comfortably handle. That caveat matters and we say it directly to every client considering this path: pgvector is genuinely well-suited to the workload this client had, but it's not a universal answer, and a team operating at a scale where vector search itself, independent of everything else Postgres does, is the dominant workload should seriously evaluate dedicated vector infrastructure instead. The right call depends on how central vector search is to the whole system versus how much it's one capability living alongside everything else a conventional relational database was already doing well.
A part of this project that doesn't get discussed as often as the database side is the pipeline that actually generates embeddings from content in the first place, and getting that pipeline right mattered just as much as the pgvector configuration. Embedding generation happens outside the database, calling an external model to convert a piece of text into its vector representation, and that step introduces its own set of operational considerations: rate limits on the embedding API, cost per embedding at this client's content volume, and the question of what to do when the embedding step fails partway through a large backfill of existing content.
We built the pipeline to be resumable and idempotent from the start, tracking which content had already been successfully embedded so a failed run could pick back up without redoing already-completed work or, worse, generating duplicate embeddings for content that had already been processed. That sounds like an obvious requirement in hindsight, but our first draft of the pipeline didn't have it, and we only added it after a rate-limit error partway through the initial backfill made us redo several hours of work that a resumable design would have preserved.
We also learned that evaluating search quality isn't something you do once during initial tuning and then stop thinking about. As the client's content library grew and as their audience's actual search behavior diverged somewhat from the queries we'd used for initial evaluation, we found result quality drifting slightly in ways that weren't obvious from system metrics like latency, which stayed perfectly healthy throughout. We set up a lightweight recurring evaluation process, sampling a rotating set of real user queries and having a human reviewer periodically judge whether the top results were genuinely relevant, specifically because latency and error rate monitoring, the metrics we already had strong tooling for, would never have caught a slow degradation in relevance on their own. That evaluation practice has become a permanent, low-overhead part of how we maintain this system rather than a one-time tuning exercise we did during the initial build and considered finished.
One refinement that improved result quality meaningfully was adding a lightweight re-ranking step after the initial similarity search, rather than trusting pgvector's raw distance ordering as the final answer. Pure vector similarity captures semantic closeness well, but it doesn't inherently know about business logic that should also influence ranking, more recently published content being generally preferable when relevance is otherwise similar, for instance, or certain content categories the client wanted lightly favored in search results for editorial reasons.
We kept that re-ranking logic in the application layer, deliberately, rather than trying to bake it into the database query itself, since business ranking preferences are exactly the kind of thing that changes over time as a client's priorities shift, and it was easier to iterate on a re-ranking function in application code than to keep revising the underlying SQL query every time the client wanted to adjust how strongly recency or category should factor into result ordering. That separation, letting pgvector answer the pure semantic similarity question and handling business-specific ranking preferences as a distinct step afterward, gave us a cleaner system to reason about and adjust than trying to encode everything into a single, increasingly complex query.