MongoDB or PostgreSQL for Multi-Tenant SaaS: Choose by Access Pattern

“MongoDB or PostgreSQL?” is too early a question. First write down what one tenant reads and writes, which facts must change together, how a large tenant grows, and what an operator must recover at 03:00. Those answers determine the data boundary. The product choice follows.

Consider a project-management SaaS with organizations, users, projects, tasks, comments, invoices, and an audit trail. Its dashboard reads projects with counts. Billing must not orphan invoice lines. Enterprise customers may demand regional placement and restoration of one tenant. Search and cohort reporting cut across tenants. “CRUD application” hides every consequential requirement in that description.

This field note compares PostgreSQL and MongoDB for those access patterns. It does not crown a universal winner. Both can serve multi-tenant systems; both can be modeled into expensive failure modes.

Start with a workload dossier

Write the ten highest-volume and ten highest-value operations before choosing a schema. Include their filters, sort order, result size, write frequency, consistency boundary, and expected growth. A useful entry is specific:

Operation: list unresolved tasks for one organization and project
Filter: organization_id = ?, project_id = ?, status = "open"
Sort: due_at ASC, id ASC
Page: 50 rows, keyset cursor
Invariant: assignee belongs to organization
Growth: 8 million tasks for the largest tenant in three years
SLO: p95 under 180 ms

Also record four constraints that benchmarks routinely omit:

• Invariants: uniqueness, referential integrity, balance equations, and the exact records that must commit or abort together.
• Isolation: whether application filtering is enough or whether regulatory, contractual, or blast-radius requirements demand a harder boundary.
• Lifecycle: schema changes, archival, tenant deletion, legal hold, tenant-level export, and recovery.
• Operations: the team’s ability to tune queries, run migrations, operate partitions or shards, and rehearse restores.

The result should be an evidence packet, not “our data looks like JSON” or “SQL is safer.”

Topology is a separate decision

The same product can support different tenancy topologies. Product and topology must be evaluated independently.

MongoDB namespaces are databases and collections rather than PostgreSQL schemas, but the trade-off is similar: more namespaces make tenant-specific restore and placement clearer while multiplying fleet work. A collection per tenant can also create large catalog and index-management overhead, so do not treat it as a free middle tier.

Isolation has several layers. A database boundary does not replace application authorization. A row or document filter does not cap CPU, connections, storage, or cache consumed by one tenant. Define isolation for confidentiality, integrity, availability, encryption keys, geography, recovery, and operator access separately.

Shared models make the tenant key structural

In a shared model, tenant_id is not metadata. It participates in identity, joins or references, indexes, authorization, and often partition or shard routing.

For PostgreSQL, a task table might use a composite relationship that makes a cross-tenant reference impossible:

CREATE TABLE projects (
  tenant_id uuid NOT NULL,
  project_id uuid NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (tenant_id, project_id)
);

CREATE TABLE tasks (
  tenant_id uuid NOT NULL,
  project_id uuid NOT NULL,
  task_id uuid NOT NULL,
  status text NOT NULL CHECK (status IN ('open', 'done')),
  due_at timestamptz,
  PRIMARY KEY (tenant_id, task_id),
  FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects (tenant_id, project_id)
);

CREATE INDEX tasks_open_queue
  ON tasks (tenant_id, project_id, due_at, task_id)
  WHERE status = 'open';

The composite foreign key enforces tenant agreement at the database boundary. The partial index follows the example operation. PostgreSQL B-tree multicolumn indexes are most efficient when leading columns are constrained, although PostgreSQL 17 can sometimes use skip scan for constraints on later columns. Do not rely on that possibility as a substitute for tenant-qualified query design.

In MongoDB, the corresponding index could be:

db.tasks.createIndex({
  tenantId: 1,
  projectId: 1,
  status: 1,
  dueAt: 1,
  _id: 1
})

MongoDB compound indexes support queries on their prefix fields. Omitting tenantId forfeits the leading prefix, and may also turn a routed sharded query into scatter-gather work. Field order should follow equality, sort, and range behavior for the real query. For either database, inspect execution plans with representative tenant cardinalities. A plan that works for a median tenant can fail for the one tenant holding half the data.

Global uniqueness deserves an explicit decision. Is a username globally unique, unique inside a tenant, or merely unique among active records? In PostgreSQL, encode the intended key or partial unique index. In a sharded MongoDB collection, a unique index must contain the full shard key as a prefix; that can make “globally unique email” an application or registry concern rather than a collection constraint.

Choose the consistency boundary

PostgreSQL makes relationships first-class. Foreign keys, unique constraints, checks, exclusion constraints, and transactions can protect invariants across tables. This is valuable when the domain contains many-to-many relationships or when several access paths must agree on one fact. Constraints still require care: PostgreSQL does not automatically create an index on the referencing columns of a foreign key, and a poorly staged constraint can make a migration operationally expensive.

MongoDB makes the document the native atomic boundary. A single-document write is atomic even when it changes several embedded fields. That is a strong match for a bounded aggregate such as an automation rule with its ordered actions, or a form definition with its fields, when those parts are normally read and changed together.

MongoDB also supports transactions across operations, collections, databases, documents, and shards. PostgreSQL supports multi-statement transactions across tables. The presence of transactions therefore does not settle the choice. Ask how often the model needs them, how much data each transaction touches, and whether the natural write boundary matches a row set or a document. MongoDB’s own guidance warns that distributed transactions cost more than single-document writes and should not replace effective schema design.

Schema validation in MongoDB can reject documents that violate a JSON Schema or query-expression rule. It is useful protection, but it is not a foreign key between arbitrary collections. If task membership must always agree with project membership, embedding can make the relationship local, application code can check it, or a transaction can maintain multiple documents. Each is a different guarantee and failure surface.

Join or embed by change pattern

“PostgreSQL joins; MongoDB embeds” is a starting distinction, not a design.

Embed a value when it is owned by one bounded aggregate, commonly read with the parent, and changed on the same lifecycle. Reference it when it grows without a known bound, is shared by many parents, changes independently, or needs its own query paths.

For the project SaaS:

• Embedding a task’s small, fixed checklist can make a MongoDB document a clean atomic unit.
• Embedding every comment ever written creates an unbounded array. MongoDB documents must remain under 16 MiB, and growing arrays can also degrade index and application behavior. Keep comments in their own collection, or embed a bounded recent subset and reference the history.
• Copying an organization name into every event may optimize immutable audit presentation, but copying the current billing plan into every active record creates a fan-out update and consistency question.
• PostgreSQL joins suit normalized, independently changing entities, but a high-fan-out dashboard can still need a summary table, cache, or asynchronously maintained projection.

Document growth matters before the hard size limit. A hot document with a large array serializes writers onto one atomic unit. A normalized design can spread those writes, but then retrieval and consistency cross records. Choose which cost corresponds to the workload.

JSONB is not MongoDB inside PostgreSQL

PostgreSQL jsonb supports indexing and containment queries, and it is useful for sparse custom fields, external payload snapshots, and attributes whose keys vary by tenant. It does not erase relational design.

Keep identifiers, tenant keys, lifecycle state, timestamps used for retention, and frequently filtered or joined values in typed columns. Put flexible leaves in jsonb when their validation and query needs justify it. PostgreSQL’s documentation notes that updating JSON still locks the whole row, so a large mutable JSON document inherits a document-style contention boundary without MongoDB’s document-oriented distribution model.

Conversely, flexible MongoDB documents need deliberate validation and versioned readers. A field that silently shifts from number to string is schema evolution, even if the server accepts both values. Flexibility moves work; it does not remove it.

Test query evolution, reporting, and search

The first release rarely keeps its first query shapes. Model likely changes:

• a task list gains filters for assignee, label, due date, and custom field;
• a tenant dashboard becomes a cross-tenant product analytics report;
• support needs a global lookup by external ID;
• audit retention becomes region- and plan-dependent;
• a keyword box becomes fuzzy, faceted, or relevance-ranked search.

PostgreSQL is often comfortable when relationships and ad hoc dimensions keep expanding. Its optimizer can combine relations, and it has built-in full-text search with GIN or GiST indexes. MongoDB aggregation and $lookup can answer relational questions, but a design built around embedded read models may need new projections as questions change.

Neither operational database should automatically absorb unlimited analytics. Cross-tenant scans compete with customer traffic, and denormalized events may need semantic cleanup before reporting. For sustained analytical workloads, define change-data capture or export into a warehouse, including tenant deletion and residency behavior.

Built-in text search is also not a blanket search strategy. PostgreSQL text search can be excellent for language-aware matching inside a relational query. MongoDB offers text indexes and, in Atlas, a separately documented Search capability. Requirements such as typo tolerance, facets, highlighting, synonyms, and independent scaling may justify a dedicated search system. Score the required behavior, operational ownership, and consistency lag rather than checking a “full text” box.

Partitioning and sharding encode future routes

PostgreSQL declarative partitioning can prune partitions when predicates match the partition bounds. It can help lifecycle management and very large tables, but it adds constraints: unique or primary-key constraints on a partitioned table must include all partition-key columns in PostgreSQL 17. Partition per tenant is rarely sensible for an unbounded population. Range partitioning by time, hash partitioning by tenant, or a combination should follow measured maintenance and query needs, not aesthetics.

MongoDB distributes a sharded collection by its shard key. The key controls distribution and query routing. A range key that grows monotonically can concentrate inserts; hashing spreads values but weakens range locality. A tenant-first compound key can target tenant queries but a very large tenant may remain concentrated unless the key adds a distribution dimension. MongoDB 9.0 supports resharding, but that does not make an early key consequence-free.

The shared rule is simple: put the route used by dominant requests into the physical design, then test hot tenants and non-routed administrative queries. Partitioning is not MongoDB sharding, and PostgreSQL replicas do not by themselves distribute writes.

Row-level security is a guardrail with bypasses

PostgreSQL row-level security can restrict which rows a role may read or modify. When enabled with no applicable policy, access is default-deny. It can provide defense in depth for a shared-schema model:

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_tasks ON tasks
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

This example is incomplete until connection-pool behavior is designed. Set the tenant context transaction-locally, reset it reliably, and test prepared statements, background jobs, migrations, and support tooling. Table owners normally bypass row security, as do roles with BYPASSRLS; referential-integrity checks also bypass it. Force RLS where appropriate, keep the application role non-owner and non-bypass, and test policies as authorization code.

MongoDB authorization is generally expressed at database and collection resources, so shared-collection tenant filtering remains an application design concern. Atlas features and deployment-specific controls can add layers, but a query missing tenantId should be treated as a security defect in a shared collection. Centralize query construction and test negative cross-tenant cases.

Recovery and residency can decide before query syntax

A full-cluster point-in-time restore is different from recovering one tenant. In shared tables or collections, tenant restore usually means restoring a copy to an isolated environment, extracting a consistent tenant dataset, reconciling global references and writes since the recovery point, then importing it. That is an application recovery workflow, not a database checkbox.

Database-per-tenant makes the recovery unit closer to the contract, although control-plane records, identity, and shared billing data can still cross the boundary. PostgreSQL logical dumps can select tables, not arbitrary consistent tenant predicates across a live relational graph. MongoDB mongorestore can include namespaces, which helps when a tenant owns a namespace, not when many tenants share collections.

Test both restore time and reconciliation. Record the recovery point objective, recovery time objective, encryption keys, version compatibility, and how writes accepted after the restored point are handled.

Residency has the same shape. A region column does not prove data stayed in a region: backups, replicas, search indexes, analytics exports, logs, and support access count. Dedicated databases or region-specific clusters can make placement auditable, at the price of routing and fleet complexity. Map every derived copy before promising residency.

Migration and team costs are part of the model

PostgreSQL schema changes are explicit and can enforce a clean target state, but large-table DDL and backfills need staged compatibility. Use expand, migrate, contract patterns and bounded lock waits, as detailed in the related PostgreSQL migration field note.

MongoDB allows old and new document shapes to coexist, which can make rolling application deployment easier. It also means readers, writers, indexes, and validators must handle the transition until old documents are migrated. A lazy read repair spreads cost across traffic; an offline or batched migration makes progress measurable. Neither approach is free.

Cost comparisons should include more than storage price:

• instances, replicas, backups, data transfer, and search or analytics services;
• connection proxies, observability, and staging environments;
• engineer time for schema design, index review, migrations, sharding or partitioning, restore drills, and incident response;
• overprovisioning required for the largest tenant and isolation tier;
• the cost of a missing invariant, cross-tenant leak, or unrecoverable tenant.

Prices change by vendor, region, commitment, and date, so calculate them from a dated workload model rather than copying a headline figure. Team maturity is a real constraint. A theoretically elegant sharded document design is a poor choice for a team that cannot operate its routing and recovery; the same is true of a highly partitioned relational fleet.

Decision matrix for concrete workload shapes

“Both” is not an escape hatch. It adds replication lag, dual-write failure, backup coordination, access control in two systems, and more on-call surface. Use two databases only when two durable workload boundaries repay that cost.

A decision record worth approving

Before committing, require a short record containing:

1. The workload dossier and three-year cardinality ranges, including the largest tenant rather than only the average.
2. The chosen tenant topology and its confidentiality, availability, recovery, and residency boundaries.
3. Schema sketches for two important aggregates and two cross-entity invariants.
4. Execution plans for dominant and adversarial query shapes on representative distributions.
5. A schema-evolution rehearsal and a tenant restore rehearsal.
6. The partition or shard trigger, proposed key, and evidence that hot routes include it.
7. A dated cost model and the named team that owns migrations, backups, query tuning, and incidents.

Choose PostgreSQL when relational invariants, evolving joins, and analytical query flexibility dominate. Choose MongoDB when bounded document aggregates and their access locality dominate, especially when the distribution key is clear. Choose either with a harder tenant topology when recovery, residency, or blast radius dominates. The defensible answer is the one whose failure boundaries match the actual SaaS, not the database category on its homepage.