Postgres against Mongo for multi-tenant systems

The argument people have

Flexible schemas against rigid ones. Document shape against normalisation. Developer velocity against correctness. All of it is downstream of a question nobody asks first.

Does anything in this system have to add up?

If the answer is yes, the choice is made, and the rest of the discussion is decoration.

Why money settles it

Multi-tenant business software almost always has money in it. Invoices, balances, payments, credits, allocations.

Money has invariants that span records. A payment allocated across three invoices must allocate exactly the payment amount. A balance must equal the charges minus the settlements. A refund cannot exceed what was paid. None of those are properties of a single document, which means enforcing them is either the database’s job or your application’s job, forever, in every code path, including the ones written under time pressure by somebody who did not know the rule.

A relational database does that with transactions across rows, foreign keys, check constraints, and unique constraints. Those are not bureaucracy. In a system with money, the constraints are the product.

Document stores have improved considerably here and can do multi document transactions. The difference is that in Postgres the guarantee is the default and in a document store it is something you opt into and can forget to.

The multi-tenant angle

Tenant isolation is the one invariant you can never get wrong, because getting it wrong means one customer sees another customer’s data, which is not a bug but an incident with legal consequences.

Postgres gives structural help. Row level security enforces the tenant predicate in the database, so a forgotten filter in one query is not a breach. Separate schemas or separate databases per tenant give harder boundaries where the requirement justifies the operational cost.

In a document store the tenant filter is application discipline, on every single query, forever. That works right up until the one query somebody wrote in a hurry.

When a document store is the right answer

It genuinely is, sometimes, and the criteria are specific.

The documents really are documents: content, event payloads, submissions, logs, where the shape varies legitimately per record and there is no useful common schema.

No cross entity invariant. Nothing has to balance against anything else.

The access pattern is fetch by id and return the whole thing, rather than aggregate across many.

Write volume is high and the write is naturally one document, which is where the operational simplicity really pays.

Those are real systems. They are just less common than the number of projects using a document store would suggest.

The middle path most people miss

Postgres has JSON columns with indexing and query support. You can store a genuinely variable payload in a column while keeping foreign keys and constraints on the parts that must not vary.

That is usually the honest shape of the requirement: most of the data has structure, some of it does not. Choosing a document store for the whole system because ten percent of it is irregular is letting the exception design the system.

The verdict

Postgres for anything with money, ownership or accountability in it, which is most business software and all multi-tenant business software. A document store where documents are genuinely documents and nothing has to balance.

And if you are unsure, pick Postgres, because the cost of choosing it early is close to zero and the cost of migrating to it later, after the invariants have already been violated for a year, is a project nobody has budgeted for.