Multi-tenancy and comparison with PostgreSQL + pgvector
Practice this questionOverview
- Multi-tenancy supports several organizations while preventing cross-organization access.
- MongoDB often fits flexible document records; PostgreSQL provides relational constraints, joins, transactions, and optional row-level security with pgvector.
- Both require explicit permission design and workload tuning.
Why this matters
Client-facing AI products may serve separate insurers, departments, or businesses.
An everyday example
Shared office infrastructure must still keep each company's records private.
A technical example
Compound keys and scoped queries include tenant ID; SQL schemas may use tenant-aware relationships and row policies.
How it works
- Choose isolation model
- Enforce tenant in services/indexes/cache keys
- Add object permissions
- Test adversarial requests
- Monitor and revoke access consistently.
Architecture and flow
Verified tenant/user → scoped data + search + memory + cache → authorized results.
The insurance assistant example
Two insurers can both have a policy called “Motor Plus” without sharing retrieved clauses or customer data.
When to use it
Shared infrastructure with tested policy boundaries; stronger physical separation where required.
When to avoid it
Do not assume database-per-tenant removes the need for secure routing, caching, or provider controls.
Interview answer
“I choose MongoDB or PostgreSQL around data relationships, integrity, operational fit, and retrieval performance, with tenant isolation enforced across every derived store.”
Benefits, trade-offs, and production considerations
- PostgreSQL row policies require correct roles and connection handling; MongoDB tenant filters require consistent application enforcement.
- Neither product prevents cross-tenant prompt, cache, checkpoint, or trace leaks automatically.
| Dimension | MongoDB Vector Search | PostgreSQL + pgvector |
|---|---|---|
| Data fit | Flexible document/chunk metadata and existing MERN records | Relational data and SQL-heavy business constraints |
| Integrity | Design appropriate constraints and transactional boundaries | Relational constraints and transaction-oriented operations |
| Access controls | Service-enforced tenant/object rules and supported search filters | Service rules plus carefully configured row policies where useful |
| Retrieval operations | Search index lifecycle and deployment configuration | Extension/index tuning, filtered ANN behavior, resource contention |
| Choice | Prefer when existing MongoDB operations and benchmarks fit | Prefer when existing SQL relationships and benchmarks fit |