Search in Your SaaS 
Postgres, OpenSearch, or a Vector Store 

Search is one of the few features where the implementation decision is routinely made before the problem is understood. A ticket says 'add search to the customer list', someone remembers that Elasticsearch is what you use for search, and six weeks later the team owns a cluster, an indexing pipeline, a reindexing runbook, and a class of bug where the search results and the database disagree. Meanwhile the actual requirement — find a customer by name or email, from a table of forty thousand rows — would have been a Postgres index and an afternoon. The opposite mistake is now equally common: a team that genuinely needs relevance-ranked text search reaches instead for embeddings and a vector database, because semantic search is the current default answer, and discovers that their users mostly search for exact invoice numbers, which is the one thing semantic search is bad at.

Three different problems, all called search

The first is lookup: the user knows what they want and is typing an identifier or a name to navigate to it. Relevance barely matters — what matters is prefix matching, typo tolerance at the margins, speed, and returning the right row first. The second is filtered retrieval: the user is narrowing a large set by attributes, dates, status, and a text fragment, and expects faceted counts and stable pagination. This looks like search but is mostly querying, and the hard parts are index selection and pagination over a moving dataset. The third is discovery: the user has an imprecise information need expressed in natural language, doesn't know the exact wording of what they're looking for, and expects the system to bridge vocabulary — this is where relevance ranking and semantic matching earn their cost. Most SaaS products need the first two, believe they need the third, and would be well served by being honest about the ratio. Instrumenting your existing search box for a fortnight settles the argument: log the queries, and the distribution of identifier lookups versus natural-language phrases will tell you which system you're actually building.

Postgres goes considerably further than most teams assume

For lookup and filtered retrieval, and for a surprising amount of ranked text search, Postgres is sufficient well past the point at which teams abandon it. Trigram indexes handle fuzzy and substring matching with real index support, which covers the typo-tolerant name lookup that constitutes most in-app search. Full-text search with tsvector columns, weighted fields, and ranking gives you genuine relevance ordering across a few million documents on hardware you're already paying for, with the substantial advantage that the index is transactionally consistent with the data — there is no synchronisation lag, no reindexing job, and no possibility of the search results disagreeing with the record page. The limits are real and worth naming: relevance tuning is coarse compared to a dedicated engine, faceted aggregations over large result sets get expensive, per-language analysis is basic, and heavy search traffic competes with your transactional workload for the same resources unless you push it to a read replica. But those limits arrive later than the folklore suggests, and the operational saving is enormous. The honest heuristic is to stay on Postgres until you can articulate the specific capability you're missing — and 'relevance isn't quite right' is only a real answer once someone has actually tried tuning the weights.

When a dedicated search engine earns its cost

OpenSearch or Elasticsearch becomes the right answer when you need things that are genuinely outside a relational database's remit: sophisticated relevance tuning with per-field boosting and custom scoring, faceted aggregations across tens of millions of documents at interactive latency, rich per-language analysis with stemming and synonyms, complex highlighting, or search over documents that don't map cleanly to your relational schema. What you take on in exchange is a stateful distributed system with its own failure modes, an indexing pipeline that must be kept in step with your primary store, a reindexing procedure that will be needed every time a mapping changes, and a cost line that scales with data volume rather than with query volume. The synchronisation problem is the one that consumes the most engineering time in practice: dual writes drift under partial failure, so the pattern that holds up is to treat the search index as a derived view populated asynchronously from a durable change stream, with a documented and rehearsed full rebuild path. Accept that the index is eventually consistent and design the UI to tolerate it — a record that was just edited appearing with stale text for two seconds is fine, provided the detail page reads from the database rather than from the index.

Semantic search, and the hybrid reality

Embedding-based search solves a specific problem — matching meaning when the user's vocabulary differs from the document's — and it solves it well. It is correspondingly bad at exact matching, which is what a great deal of business search actually is: invoice numbers, SKUs, error codes, surnames. A pure vector search for 'INV-4417' will return semantically similar invoices and may not return that one. This is why virtually every serious production system converges on hybrid: run keyword and vector retrieval in parallel, fuse the results with something like reciprocal rank fusion, and optionally rerank the merged top-N with a cross-encoder, which is usually the single highest-leverage quality improvement available. Note that hybrid is now available without adopting a separate system — Postgres with a vector extension, and OpenSearch with dense vector fields, both support keyword and vector retrieval in one place, which removes the operational argument for a standalone vector database in most application-search cases. Where a dedicated vector store still makes sense is at large embedding volumes with heavy filtering, which is more often a RAG pipeline concern than an in-app search one. The cost model is also different from keyword search in a way that catches teams out: embeddings must be generated for every document at index time and for every query at search time, so a change of embedding model means re-embedding the entire corpus, and your search latency now includes an inference call.

What breaks in multi-tenant search

• Permission filtering — results must be restricted to what this user in this tenant may see, enforced in the query rather than filtered after retrieval, or your top-10 becomes a top-3 with no explanation.
• Post-filtering with vector search — approximate nearest-neighbour retrieval followed by a permission filter can return almost nothing for a small tenant, so filters need to be applied inside the search, not after it.
• Tenant skew — one customer with ten million documents and four hundred with a thousand each will destroy a naive single-index sharding strategy.
• Index freshness expectations — users who just created a record expect to find it immediately, so the acceptable indexing lag is a product decision that must be stated, not discovered.
• Cross-tenant leakage — the highest-severity bug this feature can produce, which means explicit tests that a user from tenant A cannot retrieve tenant B's documents through any query path.
• Deletion and redaction — a deleted record must leave the index promptly, which is both a correctness issue and a data-protection one.
• Cost attribution — search infrastructure is a shared cost that scales with your largest tenants, which matters if your pricing doesn't reflect that.

Relevance is a product problem with an engineering budget

The decision that determines whether search is good is not which engine you chose, and teams that treat relevance as a one-time configuration step end up with a search box users learn to avoid. Relevance is iterative and requires a feedback loop: log queries and what users clicked, watch for queries returning nothing (the single most actionable signal you have, and usually a vocabulary mismatch a synonym list can fix), track how often users click the first result versus scroll, and assemble a small set of judged query-result pairs so you can tell whether a tuning change improved things or merely changed them. Without that last piece every relevance change is guesswork, and someone will eventually revert a genuine improvement because it made their own favourite query worse. The other half is interface: instant results as the user types, clear empty states that suggest what to try instead, an explanation of why a result matched, and — most underrated — good defaults for the case where the user just opens the search box with no query. And there is a common shortcut worth naming, which is that a decent number of 'search' requirements are satisfied by a well-designed filter UI with a fast prefix match, shipped in days rather than months.

How Infiniti Tech Partners approaches search

Our first move on a search engagement is usually to look at the query logs before the architecture, because the distribution of what people actually type resolves the build decision faster than any design discussion — and it frequently rules out the cluster somebody had already budgeted for. Where Postgres is sufficient we say so and implement it there, with the right indexes, weighted ranking, and a read path that doesn't compete with transactional load. Where a dedicated engine is warranted, we build the indexing pipeline as a derived view from a durable change stream with a tested rebuild path, because the synchronisation is where these systems actually fail. For semantic and hybrid retrieval we start with the keyword baseline and add vector retrieval and reranking against a judged evaluation set, so quality claims are measured rather than asserted — the same evaluation discipline we apply to any AI feature. And in every case we test the tenant boundary explicitly, because it's the one bug in this feature that turns into a disclosure.