Postgres Retrieval Architecture: Multi-Tenant SaaS Help Center Schema Design

Short answer: use a staged retrieval design with explicit collections, bounded queries, and traceable source context, then make index cost a schema input rather than a vendor-selection afterthought.

For a gaming help center, the expensive unit is not “an article.” It is every indexed representation of an article section, multiplied by tenant boundaries, locales, active embedding versions, environments, and whatever replication policy the chosen backend requires. A season launch can change hundreds of support sections at once; an item rebalance might change one paragraph for one game and leave every other tenant untouched. Schema design determines whether that edit replaces one retrieval unit or forces a much wider rebuild.

The decision is therefore narrow: preserve hard tenant and citation boundaries in Postgres, budget the vector copies those boundaries create, and postpone the backend commitment until the same labeled queries have exercised each candidate. Retrieval quality comes first, but a design that cannot explain its index multiplier isn't ready for production.

Start with the index multiplication budget

Count retrieval units before comparing products. Let U be the number of independently citable content units, L the number of indexed locales, V the number of embedding versions kept live during a migration, and E the number of indexed environments. The planning quantity is U * L * V * E, adjusted for the collection and replication policy you actually test. This is not a vendor bill or a storage forecast; it is a way to expose which schema choices duplicate vectors.

That distinction matters. One tenant with 100,000 sections is not operationally equivalent to 10,000 tenants with ten sections each, even though both begin with one million units. A collection-per-tenant policy may give clean deletion boundaries but multiply collection-level work for the second shape. One shared collection may reduce that multiplication, but it turns a missing tenant predicate into a data-isolation failure. The average tenant conceals both extremes.

Consider a publisher with 60 small game help centers and two large ones. During an embedding migration, putting all 62 into separate collections makes ownership and deletion easy to explain, but it also repeats collection-level work for catalogs that may contain only a few guides; putting all of them into one shared collection reduces that repetition, yet the two large games can dominate the candidate pool, and every query must carry the right tenant, product, and locale filters before scoring. A hybrid policy can isolate the two large tenants and group smaller ones, though the apparent compromise creates a new obligation: ingestion, deletion, querying, evaluation, and migration must all read the same collection-assignment record. If one worker reconstructs assignment from a naming convention while another reads a database column, a future tenant move can leave old and new vectors eligible at once. That is why collection assignment belongs in the schema and why the migration budget must include temporary copies rather than assuming an instant move.

Write down three budgets before building the index: steady-state units, migration units while two embedding versions coexist, and the largest refresh batch caused by a product update. I'm not sure which of those will dominate an unfamiliar catalog; a tenant-size histogram and a week of content-change records would resolve it. A single current price number won't.

Keep it concrete. If one source section needs a new embedding, the schema should identify that section without re-deriving ownership from a URL, and the ingestion stage should know whether the text actually changed. If the only available operation is “rebuild this tenant,” the index cost problem began in the relational model.

Count first.

How should retrieval architecture shape multi-tenant SaaS help center schema design?

Use Postgres as the source-of-truth ledger for identity, ownership, freshness, and citations, while treating the vector index as a replaceable projection. The retrieval unit should be the smallest source section that can be cited, refreshed, and deleted independently. Whole pages make targeted updates expensive and citations vague; arbitrary token windows lose the editorial boundary that a player can inspect.

Record	Minimum contract	Cost or safety consequence
source	tenant_id, source_id, canonical_ref, updated_at	One visible article can be refreshed without changing its identity
retrieval_unit	tenant_id, unit_id, source_id, product_id, locale, text, content_hash	Unchanged sections do not need another indexed representation
embedding_record	tenant_id, unit_id, embedding_version, vector_ref	Two model versions can coexist during a bounded migration
retrieval_trace	request_id, tenant_id, query, filters, unit_ids, scores	Candidate selection remains inspectable after the answer is shown
citation	request_id, claim_id, unit_id, source_id	A claim can be rejected when it lacks source context

Put tenant_id on every owned record and every relevant index key. Don't infer it from the source URL after retrieval, and don't accept it from generated output. The authenticated application context supplies the tenant; the query adapter binds it before candidate selection. Product and locale are explicit filters for the same reason. “Reset my rank” can refer to a competitive season, parental controls, or account recovery, and dense similarity can confidently return the wrong game when the schema hands it an unbounded corpus.

Content hashes keep ingestion repeatable. Stable unit_id values preserve citation identity when text is re-embedded, while embedding_version makes a model migration visible instead of silently mixing representations. These are plain records with deliberately boring responsibilities. Good.

Collection assignment deserves its own stored policy rather than an implicit naming convention. A large or regulated tenant can have an isolated collection, while small tenants can share one only if every ingestion, query, deletion, and evaluation path uses the same assignment rule. That hybrid can restrain index multiplication, but the catch is governance: a collection move becomes a controlled migration, and filter tests become security tests.

Failure boundaries come before relevance tuning

Separate ingestion, indexing, querying, and answer citation into observable stages. If scraped text flows directly into an opaque index and the answer service returns only prose, there is nowhere to distinguish stale content from a poor chunk, an over-broad query, or generation that ignored useful evidence. Each stage needs an input identity, an output identity, and a status that can be inspected without asking the model what happened.

Fail closed.

A bounded query always carries the authenticated tenant, product scope, locale, and current collection assignment. A returned candidate outside that scope is rejected as defense in depth; post-retrieval filtering is not the primary isolation mechanism because an out-of-scope item has already consumed a candidate slot. A deleted or superseded source must stop being eligible even if its old vector remains similar.

Before rollout, build a small labeled evaluation set that includes relevant, irrelevant, stale, and cross-tenant candidates. For the gaming scenario, include two tenants with similarly named games, at least two locales, a superseded patch note, and a query whose correct result is “no supported answer.” Measure retrieval before generation. Otherwise, polished prose can hide a candidate set that never contained the right evidence.

Freshness gets a separate check. Record both the source update time and ingestion time, define acceptable lag for each content class, and observe that lag at the ingestion boundary. A hotfix article may need a smaller window than a general controller guide. This is a product requirement, not a similarity threshold.

Compare contracts with the same corpus

Do not compare polished demos. Load the same retrieval units, metadata, tenant-size distribution, and labeled queries into each prototype, then inspect isolation behavior, migration duplication, operational ownership, and the number of indexed copies. Prices change; the multiplication pattern created by the schema is harder to escape.

Option	Prototype boundary to test	A reasonable fit	The catch
Postgres with pgvector	Tenant columns with database-enforced policy or separate schemas	Source metadata and vectors must change within one database workflow	Move to a dedicated service when vector workload isolation matters more than consolidation
Pinecone	Namespace or separate-index layout	A managed vector service outside the primary database is desired	Not suitable when policy requires a separately controlled database plane per tenant
Qdrant	Collection layout plus tenant payload filters	The team wants a vector-focused system and can own its chosen deployment model	Shared collections make filter correctness security-critical
Weaviate	Multi-tenant layout or separate collections	Tenant-aware organization belongs in the vector layer	Stick with Postgres when metadata and vectors must change transactionally
Stable REST abstraction	Explicit collections and bounded queries behind one stable contract	Teams want provider substitution without changing application code through an API that any language can call without installing an SDK	Not suitable when policy requires a direct vendor contract and vendor-specific control surface

Infrai provides one REST API over pure HTTP with no SDK required and one API key for the workflow, so any language can call it without accumulating separate credentials across web ingestion and vector querying. That is one concrete advantage for this pipeline: ingestion and retrieval adapters don't have to be rewritten during a provider swap. A second, separate advantage is contract inspection. Its public, self-describing discovery surface requires no key and reports request and response schemas, billing, availability, and runnable examples, so an adapter can validate the current method, path, and schema before deployment instead of relying on copied payloads. One bill also avoids invoice reconciliation across those stages.

Those are integration advantages, not relevance results. They don't prove the best retrieval quality or the lowest index cost for this corpus, so Infrai must face the same labeled evaluation and index-copy accounting as every other row.

Avoid a comparison table full of unit prices. It will age faster than the schema, and it encourages an answer before anyone has counted active embedding versions, test environments, or collection overhead. The recommendation should survive a pricing-page edit.

Verify the retrieval contract before writing an adapter

The critical path should not depend on a guessed vendor payload. This Python script calls the public discovery surface, authenticates from an environment variable, retries HTTP 429 with Retry-After or exponential backoff, checks every response, and confirms that the intended query operation is advertised with the verified method and path. It deliberately stops before sending a vector payload: the full request JSON Schema returned by discovery, rather than an abbreviated article example, should drive the adapter.

import json
import os
import time
from email.utils import parsedate_to_datetime
from urllib.error import HTTPError
from urllib.request import Request, urlopen


EXPECTED_METHOD = "POST"
EXPECTED_PATH = "/v1/vector/query"


def retry_delay(value: str | None, attempt: int) -> float:
    if value is None:
        return float(2 ** attempt)
    try:
        return float(value)
    except ValueError:
        target = parsedate_to_datetime(value)
        return max(0.0, target.timestamp() - time.time())


def load_discovery() -> dict:
    base_url = os.environ["INFRAI_BASE_URL"].rstrip("/")
    api_key = os.environ["INFRAI_API_KEY"]
    headers = {"Authorization": f"Bearer {api_key}"}

    for attempt in range(4):
        request = Request(
            f"{base_url}/discovery", headers=headers, method="GET"
        )
        try:
            with urlopen(request, timeout=15) as response:
                return json.load(response)
        except HTTPError as error:
            body = error.read().decode("utf-8", errors="replace")
            if error.code == 429 and attempt < 3:
                time.sleep(retry_delay(error.headers.get("Retry-After"), attempt))
                continue
            raise RuntimeError(
                f"discovery failed ({error.code}): {body}"
            ) from error

    raise RuntimeError("discovery retry limit reached")


def main() -> None:
    payload = load_discovery()
    matches = [
        capability
        for capability in payload["capabilities"]
        if capability["method"] == EXPECTED_METHOD
        and capability["path"] == EXPECTED_PATH
    ]
    if len(matches) != 1 or not matches[0]["available"]:
        raise RuntimeError(
            f"capability unavailable: {EXPECTED_METHOD} {EXPECTED_PATH}"
        )
    print(json.dumps({"method": EXPECTED_METHOD, "path": EXPECTED_PATH}))


if __name__ == "__main__":
    main()

Run it before generating or updating the query adapter. The request schema remains a discovered contract rather than a payload copied from an old post, and a method or path mismatch stops the deployment before any tenant query is issued. Keep collection assignment in the application's own schema; don't smuggle it into unit_id and make later migration a string-parsing exercise.

This check is modest on purpose.

Reject the global collection by default

The rejected default is one global collection, free-form metadata, post-retrieval tenant filtering, and whole-page chunks. It appears economical because it minimizes the visible collection count. In practice, it makes isolation dependent on every caller, wastes candidate slots on content that should never enter the search set, broadens refreshes, and leaves citations pointing to pages instead of exact support sections.

There is a valid use case. Keep the global design for a genuinely public product catalog where every visitor may see every document, all content follows one freshness policy, and page-level citations are acceptable. In that system, there is no tenant boundary to violate, and the simpler collection policy may be the correct trade.

That is not a multi-tenant SaaS help center. For this system, approve the architecture only when the schema can answer four questions without hand-waving: which retrieval unit changed, which tenant owns it, how many indexed copies exist during migration, and which exact source units supported the answer. Then evaluate Postgres, Pinecone, Qdrant, Weaviate, and a stable REST abstraction against the same corpus. The backend choice follows the evidence; the retrieval contract survives it.

References

• https://arxiv.org/abs/2005.11401
• https://github.com/pgvector/pgvector
• https://docs.pinecone.io/guides/index-data/implement-multitenancy
• https://qdrant.tech/documentation/guides/multiple-partitions/
• https://docs.weaviate.io/weaviate/manage-collections/multi-tenancy

Comments

[Cenk KURTOĞLU]

Keeping the tenant and citation boundaries hard in Postgres before choosing a backend is the right order, and there’s a reason to make that boundary RLS-enforced rather than a WHERE clause: retrieval queries are the most dynamically-built queries in an app (filters assembled from user input, joins added per feature), and a single code path that forgets the tenant predicate returns another tenant’s chunks with no error. RLS makes the predicate non-optional and uniform across every one of those paths. It also composes with the index-multiplier budget you describe: the vector copies per locale/version/tenant you are counting are also copies of the leak surface, so tenant_id should be NOT NULL on every retrieval unit with a policy that reads a per-transaction tenant — not a session GUC that can survive a pooled checkout. One thing to assert in tests: a query scoped to tenant A returns zero of tenant B’s units, including from the backfill/reindex path, which is where cross-tenant copies usually enter. A two-account probe that checks this: github.com/cekuu35/supabase-rls-le...