PostgreSQL or MongoDB for versioned documents?
Choose between PostgreSQL and MongoDB by testing the document workflow and its invariants, not by asking whether the payload contains JSON. PostgreSQL can combine relational constraints with JSONB; MongoDB can model document aggregates and supports transactions. Revision ownership, query shape, and atomic approval requirements determine the useful comparison.
Inside this article 5 sections +
A document workflow is more than a collection of JSON bodies. It includes identities, revisions, permissions, approval decisions, reporting queries, and history. PostgreSQL and MongoDB can both store the content, so the useful comparison is how each model preserves those relationships under real operations.
Consider supplier compliance documents. A supplier submits a revision, a reviewer approves that exact revision, and account users later need to know which version was effective on a particular date. New drafts must not silently alter the approved document. The model should make that invariant visible before performance testing begins.
Define the revision contract once
Give each logical document a stable tenant-scoped identity. Give each revision an immutable identity and a schema version describing its content. Approval refers to a revision, not to whichever content happens to be current when somebody reads the document. Corrections create new revisions; they do not rewrite the meaning of an old approval.
Separate draft state, submitted revisions, and the approved pointer. Decide whether submission freezes the body, whether reviewers can amend it, and whether approval needs an effective interval. A single status field on a mutable document cannot explain all these cases reliably. The same contract should govern both database prototypes.
Illustrative logical records shared by both designs:
Document: tenant_id, document_id, current_revision, approved_revision
Revision: tenant_id, document_id, revision_id, schema_version, body
Approval: tenant_id, document_id, revision_id, actor_id, decided_at
Invariant: an approval names an existing immutable revision.
Concurrent approval: compare the expected document revision
before changing the approved pointer.
The approval event also needs a stable operation identity if clients may retry. A duplicate request should return the existing decision or a defined conflict, rather than producing two contradictory audit entries. Keep actor authorization current even when the referenced revision is immutable.
Build two honest storage models
In PostgreSQL, a documents table can own the current and approved pointers, a revisions table can store immutable content in JSONB, and approvals can reference the chosen revision. Use relational columns for fields that participate in identity, constraints, joins, or common filters. JSONB can hold variable document sections without forcing every searchable property into an unstructured blob.
In MongoDB, a document aggregate can hold current metadata and bounded current-state content. Large or unbounded revision history often belongs in a separate collection rather than an ever-growing embedded array. That introduces cross-document coordination for some operations; MongoDB supports transactions, but the model should account for their scope and operating cost.
Neither approach should get an unfair shortcut. Comparing normalized PostgreSQL history with a MongoDB model that simply overwrites old content does not compare the same product. Conversely, forcing every MongoDB field into a separate collection defeats the aggregate model before the experiment begins. Preserve equivalent business behavior while using sensible designs for each database.
| Operation | PostgreSQL prototype | MongoDB prototype |
|---|---|---|
| Load approved revision | Document pointer joined to immutable revision | Document pointer followed to revision record |
| Approve expected revision | Conditional update and related writes in transaction | Conditional update; transaction if related records must commit together |
| Filter pending documents | Indexed metadata and selected JSONB fields | Indexes matching declared metadata and document queries |
| Retain growing history | Append revision rows with retention policy | Append revision documents with retention policy |
Test the approval race before throughput
Have two reviewers approve against the same expected document state while a supplier submits another revision. The implementation should make an explicit choice about which transitions can coexist. A conditional update that includes the expected revision can detect a stale decision. The caller must inspect whether the update matched; issuing the command is not evidence that approval succeeded.
If approval also creates an audit record and a notification intent, decide which writes must commit atomically. In PostgreSQL, those records can share a transaction. In MongoDB, either place the invariant within one suitable document or use a transaction for the participating records. Avoid assuming that an individual atomic update makes all surrounding writes atomic.
Repeat after losing the response to a successful commit. Verify that the API can recover the existing outcome using the operation identity. Then test a reviewer whose access was revoked between opening the screen and submitting approval. Storage consistency and current authorization are separate requirements.
Compare the queries the product will actually run
Use at least four queries: fetch the currently approved document, list pending reviews by account and age, find documents containing a selected field value, and report historical approval activity across sites. Declare filters, sorting, pagination, result size, and permission scope. Run with representative document sizes and uneven tenant populations.
Inspect query plans and index size, not just elapsed time. A fast query backed by several large indexes may increase write work and storage significantly. A flexible field search can require different index strategies from an ordered account listing. Record the cost of adding a new document type and its first reporting query.
Include schema evolution in the workload. Read an old revision after the application adopts a new content schema, and render it without silently applying new defaults that change its historical meaning. An explicit versioned reader or projection may be necessary in either database. Document storage does not remove schema ownership; it changes where that ownership is enforced.
Choose using operational evidence
Compare restore procedures, migration tooling, transaction diagnostics, team familiarity, and how operators investigate a disputed approval. A design that performs adequately and exposes its invariants clearly can be more valuable than a marginally faster prototype that nobody can repair confidently.
Keep the decision record tied to the tested workload and reconsider it when those assumptions change. The resumable import guide develops revision-safe ingestion, and API idempotency covers repeated commands. Application implementation should make the document lifecycle understandable independently of the chosen storage engine.