Postgres vs MongoDB: You May Already Have a Document Store

A second datastore is rarely adopted on the whole of its merits. It arrives for one access pattern — an event payload with unpredictable keys, a catalogue whose attributes differ by product category — and is then operated forever: backed up, upgraded, presented to an auditor.

Feature comparisons miss that asymmetry. The real decision is narrower: does this access pattern justify a second engine — its own licence, audit-logging edition gate and end-of-life calendar — or does the engine already in production hold it?

The question is not which database is better

Two properties settle it, neither of them syntax. Write throughput: can one primary absorb the write rate, now and at the volume the business plans for? Schema variance: does the document shape genuinely differ per record, or is it a mostly-stable structure with optional branches?

Both are answerable in your own estate. The throughput figures circulating for this comparison are vendor benchmarks or unreproducible blog posts, so none appears here — only documented behaviour, licence text and end-of-life dates, which still hold in three years.

One note on versions: PostgreSQL quotations come from the version 18 manual, MongoDB's from the current manual, documenting a 9.0 server; a reader on 8.0 may see different wording.

Click to zoom

What jsonb actually is, and what it is not

jsonb is not a text column with convenience functions bolted on. The manual's JSON types page says json "stores an exact copy of the input text, which processing functions must reparse on each execution", while jsonb is "stored in a decomposed binary format" that makes it "slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed". That is not the only cost: the jsonb input function is also "stricter", disallowing "Unicode escapes for characters that cannot be represented in the database encoding" and rejecting \u0000 — a restriction the migration below runs into. The next sentence: "jsonb also supports indexing, which can be a significant advantage" — and the verdict, "most applications should prefer to store JSON data as jsonb".

The difference from BSON is semantic, not performance. jsonb "does not preserve the order of object keys, and does not keep duplicate object keys" — where keys repeat, "only the last value is kept". An application that signs a canonical serialisation or relies on repeated keys behaves differently here.

The size ceiling needs care: PostgreSQL's limits appendix gives field size as 1 GB — against MongoDB's 16 mebibyte cap, an apparent sixtyfourfold advantage — though not a working size, and the manual says so: "any update acquires a row-level lock on the whole row. Consider limiting JSON documents to a manageable size in order to decrease lock contention among updating transactions."

On the query side Postgres is not limited to operators: the JSON functions page says the SQL/JSON functions JSON_EXISTS(), JSON_QUERY() and JSON_VALUE() "can be used to query JSON documents" — typed values, not text. Mutation is functional, and jsonb_set carries a trap in both halves of one signature: new_value is "added if create_if_missing is true (which is the default) and the item designated by path does not exist", while "all earlier steps in the path must exist, or the target is returned unchanged". A typo raises nothing either way, and the outcome turns on where it sits: misspell an earlier step and the document comes back unchanged; misspell the final step and it silently gains a junk key. Pass create_if_missing = false whenever the key must already exist.

Where MongoDB genuinely earns the slot

Three things, all real. First, horizontal distribution in the server rather than an extension: MongoDB's sharding documentation calls it "a method for distributing data across multiple machines" used "to support deployments with very large data sets and high throughput operations". If one primary cannot absorb the write rate, that is the capability being bought, and it ships in the box.

It is not free; MongoDB's own page gives the topology a floor: "Each shard must be deployed as a replica set", and "Config servers must be deployed as a replica set (CSRS)". A wrong shard key is recoverable, not cheap: resharding requires that "your application can tolerate a period of two seconds where the affected collection blocks writes", and "collections that use queryable encryption are not supported".

Second, change streams — a resumable feed of data changes, the clean primitive for cache invalidation, search indexing and event fan-out. One topology precondition: they "are available for replica sets and sharded clusters", which "must use the WiredTiger storage engine" — a standalone mongod has none. Postgres is not empty-handed: logical decoding with replication slots is in core, and slots "persist independently of the connection using them and are crash-safe". What differs is who operates the consumer: MongoDB ships an application-facing resumable API; Postgres a replication stream needing an output plugin, a consumer in front of it, and the duty that an abandoned slot pins WAL and catalog rows against VACUUM — "if a slot is no longer required it should be dropped". Same question behind picking a local-first sync engine.

Third, per-record schema variance — which none of the MongoDB pages cited here argues for. PostgreSQL's manual, mid-recommendation, adds that "even for applications where maximal flexibility is desired, it is still recommended that JSON documents have a somewhat fixed structure". Read as a limit rather than advice: documents genuinely differing per record sit against the grain of their own engine's documentation.

One capability assumed onto this list is absent. Multi-document transactions exist on both sides, but on MongoDB they cost topology — "MongoDB standalone deployments do not support transactions", and "your deployment must be a multiple node replica set" — and time: by default "a transaction must have a runtime of less than one minute", tunable via transactionLifetimeLimitSeconds. MongoDB's transactions page adds that "a distributed transaction incurs a greater performance cost over single document writes" and "should not be a replacement for effective schema design".

The write path nobody benchmarks: update amplification

The mechanic deciding whether a jsonb column is cheap in production is in no matrix. The routine vacuuming chapter notes that "an UPDATE or DELETE of a row does not immediately remove the old version of the row", so every document edit leaves a dead tuple for VACUUM. Cheapness turns on heap-only tuple eligibility.

The HOT updates page gives two conditions, both of which must hold: "The update does not modify any columns referenced by the table's indexes, not including summarizing indexes", and "There is sufficient free space on the page containing the old row for the updated row". Then "New index entries are not needed to represent updated rows".

Measurable, too: the same page notes you "can increase the likelihood of sufficient page space for HOT updates by decreasing a table's fillfactor", and "pg_stat_all_tables allows monitoring of the occurrence of HOT and non-HOT updates" — a ratio read off your own update mix.

-- Baseline the HOT / non-HOT counters for this table. One prerequisite:
-- n_tup_newpage_upd needs PostgreSQL 16 or later, which added the column
-- to pg_stat_*_tables -- the 16 release notes record statistics on "the
-- occurrence of updated rows moving to new pages":
-- https://www.postgresql.org/docs/release/16.0/
-- On 15, drop it from both SELECTs below and read
-- (n_tup_upd - n_tup_hot_upd) on its own -- the headline ratio survives.
SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
  FROM pg_stat_all_tables
 WHERE relname = 'catalogue_item';

-- ANALYZE EXECUTES both statements below: these rows are really updated.
-- Run the pair against a scratch copy of the table carrying real rows,
-- never production. The DDL below creates the table empty, and against an
-- empty table both UPDATEs match nothing and every counter stays at zero.
--
-- (a) Touch a key the GIN index covers. The index references the doc
--     column, so this update modifies an indexed column: the first HOT
--     condition fails and new index entries have to be written.
EXPLAIN (ANALYZE, BUFFERS)
UPDATE catalogue_item
   SET doc = jsonb_set(doc, '{attributes,certified}', 'false'::jsonb),
       updated_at = now()
 WHERE item_id = 1;

-- (b) Touch only a column no index references. With free space on the
--     page, this one can stay heap-only.
EXPLAIN (ANALYZE, BUFFERS)
UPDATE catalogue_item
   SET updated_at = now()
 WHERE item_id = 1;

-- Re-read the counters -- but not instantly, or the measurement looks
-- like it failed. A process flushes stats "just before going idle, but
-- not more frequently than once per PGSTAT_MIN_INTERVAL milliseconds
-- (1 second ...)", and "in the default configuration" the values a
-- transaction accesses stay cached until it ends (stats_fetch_consistency
-- governs this). So read from a new session, a moment later.
--
-- (n_tup_upd - n_tup_hot_upd) is the count of
-- updates that had to write new index entries; n_tup_newpage_upd is the
-- subset that could not even stay on the original page.
SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
  FROM pg_stat_all_tables
 WHERE relname = 'catalogue_item';

Two design consequences follow. Promote frequently-updated fields into real columns, so routine writes touch nothing the GIN index references. And keep documents small: the row-level lock is taken on the whole row however small the change.

Failure modes, side by side

Start with the symmetry nobody mentions: neither engine escapes a single write primary without sharding. MongoDB's replica set primary page: "The primary is the only member in the replica set that receives write operations". PostgreSQL's hot standby chapter: standby connections are "strictly read-only; not even temporary tables may be written". Replicas buy read capacity and durability, never write capacity.

They diverge on what is shipped versus what you assemble. PostgreSQL is explicit that failover is not its job: it "does not provide the system software required to identify a failure on the primary and notify the standby database server", noting that many exist. Price that gap — an operator or cluster manager fills it, the shape of running Postgres on Kubernetes with CloudNativePG. MongoDB ships that component: a secondary calls for an election to select a new primary "and automatically resume normal operations".

MongoDB's walls under load are hard and documented: the limits reference states "The maximum BSON document size is 16 mebibytes" and that "MongoDB supports no more than 100 levels of nesting for BSON documents". Anything larger goes to GridFS — an application change, not a configuration one. Postgres's wall is softer and arrives earlier: lock contention on wide rows, VACUUM falling behind. Soft walls are worse: they degrade instead of refusing, and get misdiagnosed as "the database is slow".

The distribution answer is asymmetric in a way procurement cares about. MongoDB's sharding is in the server, under the server's licence; PostgreSQL's is an extension, and the LICENSE file in the Citus repository is the GNU Affero General Public License v3 — a copyleft boundary core does not carry, so a sharded Postgres future means a differently-licensed component.

Concrete config: indexes, queries, and their MongoDB equivalents

The design that works is a hybrid, not a schemaless table: identity, tenancy and lifecycle become real columns with real constraints, only the genuinely variable remainder lives in the document, and fillfactor is lowered to buy the page space the second HOT condition needs.

CREATE TABLE catalogue_item (
    item_id     bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   uuid        NOT NULL,
    sku         text        NOT NULL,
    status      text        NOT NULL
                CONSTRAINT catalogue_item_status_ck
                CHECK (status IN ('draft', 'active', 'withdrawn')),
    updated_at  timestamptz NOT NULL DEFAULT now(),
    doc         jsonb       NOT NULL
                CONSTRAINT catalogue_item_doc_is_object
                CHECK (jsonb_typeof(doc) = 'object'),
    CONSTRAINT catalogue_item_tenant_sku_uq UNIQUE (tenant_id, sku)
)
-- Leave room on each page so an in-place update can stay on it.
WITH (fillfactor = 85);

-- Containment and jsonpath over the whole document. jsonb_path_ops is
-- usually much smaller than the default jsonb_ops and searches more
-- specifically -- but it does NOT support the key-exists operators
-- (? ?| ?&). Only @>, @? and @@.
CREATE INDEX catalogue_item_doc_gin
    ON catalogue_item USING gin (doc jsonb_path_ops);

-- The one hot key, promoted to a targeted expression index: smaller and
-- faster to search than the whole-column index, and able to serve btree
-- predicates the GIN index cannot.
CREATE INDEX catalogue_item_doc_brand
    ON catalogue_item ((doc ->> 'brand'))
    WHERE status = 'active';

-- The relational side of the join in statement 4 of the next block. In a
-- real schema this is a table you already own; create it here so the query
-- block runs against nothing but what this article supplies.
CREATE TABLE supplier_lead_time (
    component_code text    PRIMARY KEY,
    lead_time_days integer NOT NULL
);

The operator-class choice has consequences, and the manual gives direction without magnitude: a jsonb_path_ops index "is usually much smaller than a jsonb_ops index over the same data, and the specificity of searches is better". The cost is capability — it "does not support the key-exists operators". The same page adds that "targeted expression indexes are likely to be smaller and faster to search than a simple index".

-- 1. Containment. The @> predicate is what the jsonb_path_ops GIN index
--    serves; tenant_id is separately covered by the UNIQUE (tenant_id,
--    sku) btree, so the planner may pick either or BitmapAnd both.
SELECT item_id, sku
  FROM catalogue_item
 WHERE tenant_id = '00000000-0000-0000-0000-000000000001'
   AND doc @> '{"attributes": {"certified": true}}';

-- 2. A jsonpath predicate -- and the trap in this section. For @? and @@
--    the manual says a GIN index "extracts clauses of the form
--    accessors_chain == constant out of the jsonpath pattern". A range
--    filter contains no such clause, so nothing is extracted. The
--    consequence -- a reading, not the manual's wording -- is that the
--    GIN index cannot accelerate this predicate at all.
SELECT item_id, sku
  FROM catalogue_item
 WHERE doc @? '$.dimensions.weight_kg ? (@ > 25)';

-- 2b. A range predicate needs its own targeted expression index. One
--     precondition: every weight_kg present must cast cleanly, or the
--     build fails on the first value that does not.
CREATE INDEX catalogue_item_doc_weight
    ON catalogue_item (((doc #>> '{dimensions,weight_kg}')::numeric));

SELECT item_id, sku
  FROM catalogue_item
 WHERE (doc #>> '{dimensions,weight_kg}')::numeric > 25;

-- 3. Extract a typed SQL value rather than text, with a default on miss.
--    PostgreSQL 17 or later only: the 17 release notes add the SQL/JSON
--    query functions JSON_EXISTS(), JSON_QUERY() and JSON_VALUE() --
--    https://www.postgresql.org/docs/release/17.0/ -- and JSON_TABLE() in
--    the same release. On 14 through 16 use
--    (doc #>> '{pricing,list_cents}')::integer with COALESCE.
SELECT item_id,
       JSON_VALUE(doc, '$.pricing.list_cents' RETURNING integer
                       DEFAULT 0 ON EMPTY) AS list_cents
  FROM catalogue_item
 WHERE status = 'active';

-- 4. Unnest an embedded array into rows and join it against a relational
--    table in one statement. No second round trip, no application-side
--    join, one transaction, one backup, one audit trail. The
--    supplier_lead_time table is created at the end of the DDL block
--    above -- in a real schema it is one you already have.
--    PostgreSQL 17 or later only as well: JSON_TABLE() landed in the same
--    release. On 14 through 16, jsonb_array_elements() does this job with
--    a per-key cast instead of a COLUMNS list. LATERAL is written out
--    rather than left implicit: the rows JSON_TABLE produces are joined
--    laterally to the row that generated them either way, and being
--    explicit keeps the reference to i unambiguous once a second JOIN
--    follows in the same tree.
SELECT i.sku, c.component_code, c.quantity, s.lead_time_days
  FROM catalogue_item AS i
  CROSS JOIN LATERAL
       JSON_TABLE(i.doc, '$.bill_of_materials[*]'
         COLUMNS (
           component_code text    PATH '$.code',
           quantity       integer PATH '$.qty'
         )) AS c
  JOIN supplier_lead_time AS s ON s.component_code = c.component_code
 WHERE i.status = 'active';

-- 5. And the targeted expression index earns its keep here: equality on
--    the one promoted key, inside the same partial predicate the index
--    was built with. Outside that predicate it cannot be used.
SELECT item_id, sku
  FROM catalogue_item
 WHERE status = 'active'
   AND doc ->> 'brand' = 'northwind';

The MongoDB equivalents are differently shaped, and the shape is the argument. Both engines offer a catch-all option for keys nobody anticipated — a whole-column jsonb GIN index on one side, a wildcard index on $** on the other — but the documented postures are not symmetric. MongoDB's manual says to "only use wildcard indexes when the fields you want to index are unknown or may change" because they "don't perform as well as targeted indexes on specific fields". PostgreSQL's cautions nothing: it prices the option — such an index "will store copies of every key and value in the jdoc column", the manual's example column — and calls it "far more flexible (since it supports queries about any key)". What differs is context, not capability: a wildcard index is scoped to one collection, while the GIN index sits on a table that can also be joined and constrained relationally.

// Placeholders -- substitute your own. tenantId is written here as a BSON
// UUID (Binary subtype 4). Do not assume its export shape: the Extended
// JSON specification's conversion table emits subtype-4 binary as
// {"$binary": {"base64": ..., "subType": "04"}} and introduces
// {"$uuid": ...} under its parsing rules as a more readable alternative,
// while the manual's Extended JSON reference shows the uuid row as
// {"$uuid": ...} in canonical and relaxed modes alike. Read the first
// line of your own export; handle both downstream.
const tenantUuid = UUID("00000000-0000-0000-0000-000000000001");
const itemId = ObjectId("000000000000000000000001");

// Targeted compound index on tenantId plus one embedded path. The
// index-everything alternative is a wildcard index --
// db.catalogueItem.createIndex({ "$**": 1 }) -- which the manual advises
// against unless the fields are unknown or may change.
db.catalogueItem.createIndex(
  { tenantId: 1, "attributes.certified": 1 },
  { name: "tenant_certified" },
);

// The matching read, with a projection standing in for the column list.
db.catalogueItem
  .find({ tenantId: tenantUuid, "attributes.certified": true }, { _id: 1, sku: 1 })
  .limit(50);

// One document, one atomic write -- the same guarantee a single-row jsonb
// UPDATE gives, and the reason neither side needs a transaction here.
db.catalogueItem.updateOne(
  { _id: itemId },
  { $set: { "attributes.certified": false, updatedAt: new Date() } },
);

// Two collections, one transaction. REQUIRES a multiple-node replica set:
// standalone deployments do not support transactions at all, and the
// default runtime limit is one minute.
const session = db.getMongo().startSession();
session.startTransaction();
try {
  const items = session.getDatabase("catalogue").catalogueItem;
  const audit = session.getDatabase("catalogue").auditTrail;
  items.updateOne({ _id: itemId }, { $set: { status: "withdrawn" } });
  audit.insertOne({ itemId: itemId, action: "withdraw", at: new Date() });
  session.commitTransaction();
} catch (err) {
  session.abortTransaction();
  throw err;
} finally {
  session.endSession();
}

The compliance surface: licence posture and what an auditor is shown

Audit logging first: that is what an auditor asks, and the answer is an edition, not a setting. MongoDB's auditing tutorial states that "MongoDB Enterprise supports auditing of various operations" and that "A complete auditing solution must involve all mongod server and mongos router processes" — every process, not the cluster as an abstraction. Managed, it reads "MongoDB Atlas supports auditing for all M10 and larger clusters".

On Postgres the equivalent is an extension, and pgaudit says why: statement logging via log_statement = all "is acceptable for monitoring and other usages but does not provide the level of detail generally required for an audit". Its LICENSE file says the code "is released under the PostgreSQL licence". No edition to buy, so audit capability is not a commercial gate; which facility is richer is a separate question for your control framework.

Now the licence. MongoDB's community-edition licensing page places the server under "MongoDB, Inc.’s Server Side Public License (for all versions released after October 16, 2018, including patch fixes for prior versions)". PostgreSQL's own licence page says the project "is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses". On the Postgres side copyleft moved rather than vanished: core permissive, pgaudit under the PostgreSQL licence, DocumentDB MIT, Citus AGPL-3.0. Licence posture is a per-component question — as when a search engine re-licenses underneath you.

Auditing is not the only gate. MongoDB's own compatibility table marks *automatic* encryption for Queryable Encryption and CSFLE "Yes" on Atlas and Enterprise Advanced and "No" on Community Edition, which gets explicit encryption only, driven from application code. The Enterprise Advanced minimums are 7.0 and 4.2; Atlas reads 7.0 and "All supported MongoDB versions" respectively. Both also need a replica set or sharded cluster, "but not a standalone instance". Self-hosted Community keeps the primitive and loses the automation.

The exit ramp runs one way

Optionality is worth having only once rehearsed, and this one is cheap. FerretDB is "a proxy that converts MongoDB 5.0+ wire protocol queries to SQL and uses PostgreSQL with DocumentDB extension as a database engine". The DocumentDB extension under it is "open-sourced under the most permissive MIT license" and "introduces the BSON data type to PostgreSQL". An existing driver points at Postgres untouched, making compatibility a measurement against your query set.

# docker-compose.yml -- exit-ramp rehearsal, not a production topology.
# Point an existing driver at
# mongodb://username:CHANGE_ME_BEFORE_USE@127.0.0.1:27017/ and run your own
# query set. FerretDB authenticates against the PostgreSQL backend, so the
# MongoDB URI carries the POSTGRES_USER / POSTGRES_PASSWORD set below.
services:
  postgres:
    # Pre-packaged PostgreSQL carrying the DocumentDB extension. Pin the
    # full image tag, as FerretDB's own Docker page recommends.
    image: ghcr.io/ferretdb/postgres-documentdb:17-0.107.0-ferretdb-2.7.0
    restart: on-failure
    environment:
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=CHANGE_ME_BEFORE_USE
      - POSTGRES_DB=postgres
    volumes:
      - ./data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U username -d postgres"]
      interval: 10s
      timeout: 5s
      retries: 12

  ferretdb:
    image: ghcr.io/ferretdb/ferretdb:2.7.0
    restart: on-failure
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      - FERRETDB_POSTGRESQL_URL=postgres://username:CHANGE_ME_BEFORE_USE@postgres:5432/postgres
    ports:
      # Bound to loopback on purpose: this rig holds a copy of real data.
      - "127.0.0.1:27017:27017"

networks:
  default:
    name: ferretdb

Read the project's own caution first. FerretDB says it "is compatible with MongoDB drivers and can be used as a direct replacement for MongoDB 5.0+", and then: "before moving to production, please verify if it is suitable for your application". Not a drop-in guarantee, but a way to find out in an afternoon which queries fail.

The bulk route makes the real cost visible: deciding which fields stop being document keys and become constrained columns. mongoexport emits MongoDB Extended JSON v2 — --jsonFormat defaults to relaxed — so identifiers and dates arrive as {"$oid": …} and {"$date": …} wrappers to unwrap deliberately.

#!/usr/bin/env bash
set -euo pipefail

MONGO_URI="mongodb://localhost:27017/catalogue"
PG_URI="postgresql://localhost:5432/catalogue"
EXPORT_FILE="catalogue_item.ndjson"

# 1. Export one collection as newline-delimited Extended JSON v2.
mongoexport \
  --uri="$MONGO_URI" \
  --collection=catalogueItem \
  --type=json \
  --out="$EXPORT_FILE"

# 2. Land it untouched in a staging table. No schema decisions yet.
#    Quote and delimiter are control characters that cannot occur in the
#    export, so each line arrives as one jsonb value, unmangled.
#    Expect this to abort on documents jsonb refuses but json accepts:
#    an embedded \u0000 in a BSON string. ON_ERROR_STOP=1 makes that
#    loud, not silent -- fix
#    the offending documents at the source and re-export.
psql "$PG_URI" -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE IF NOT EXISTS stg_catalogue_item (doc jsonb NOT NULL);
TRUNCATE stg_catalogue_item;
SQL

psql "$PG_URI" -v ON_ERROR_STOP=1 -c \
  "\copy stg_catalogue_item (doc) FROM '$EXPORT_FILE' WITH (FORMAT csv, DELIMITER E'\x1f', QUOTE E'\x1e')"

# 3. The actual migration: decide what stops being a document, and unwrap
#    the Extended JSON that relaxed mode left behind. Only the wrappers
#    named below are unwrapped. $oid, and any $date or $numberLong further
#    down the document, survive into jsonb verbatim and need their own
#    deliberate pass -- a decision, not an oversight: enumerate them
#    against your real schema before trusting this on production data.
#    Rows the filter holds back -- an unknown status, or no 'sku' key at
#    all -- stay in staging and are counted by the two reconciliation
#    SELECTs below, not guessed at. _id is dropped on purpose: the target
#    assigns its own item_id identity, so any cross-collection reference
#    to a MongoDB _id needs a mapping pass of its own.
psql "$PG_URI" -v ON_ERROR_STOP=1 <<'SQL'
WITH staged AS (
  SELECT doc,
         doc - 'tenantId' - 'sku' - 'status' - '_id' AS body
    FROM stg_catalogue_item
   WHERE doc ? 'sku'
     -- status is CHECK-constrained on the target. A collection value the
     -- constraint does not know would abort the whole INSERT, so filter
     -- it out here and report it below rather than inventing a mapping.
     AND coalesce(doc ->> 'status', 'draft')
           IN ('draft', 'active', 'withdrawn')
), inserted AS (
INSERT INTO catalogue_item (tenant_id, sku, status, doc)
SELECT
       -- tenantId arrives as a plain string, or -- if it was a BSON UUID
       -- -- as {"$binary": {"base64": ..., "subType": "04"}}, the shape the
       -- Extended JSON specification's conversion table defines, or as
       -- {"$uuid": "..."}, the shape the manual's Extended JSON reference
       -- shows. All three occur; take whichever the export actually wrote
       -- rather than asserting one. Both UUID forms cast cleanly: the
       -- unhyphenated 32 hex characters from base64, and the canonical
       -- hyphenated text from $uuid.
       coalesce(
         encode(decode(doc #>> '{tenantId,$binary,base64}', 'base64'), 'hex'),
         doc #>> '{tenantId,$uuid}',
         doc #>> '{tenantId}'
       )::uuid,
       doc ->> 'sku',
       coalesce(doc ->> 'status', 'draft'),
       -- createdAt arrives as {"$date": ...} -- but in two shapes. Relaxed
       -- mode writes an ISO-8601 string for dates in the years 1970 to 9999
       -- inclusive; outside that range, and always in canonical mode, it
       -- nests {"$date": {"$numberLong": "<millis>"}}. Test the nested form
       -- FIRST: on that shape #>> returns the inner object's JSON text and
       -- the timestamptz cast errors. Leave the document alone if absent.
       CASE
         WHEN doc #>> '{createdAt,$date,$numberLong}' IS NOT NULL
           THEN jsonb_set(body, '{createdAt}',
                  to_jsonb(to_timestamp(
                    (doc #>> '{createdAt,$date,$numberLong}')::bigint
                    / 1000.0)))
         WHEN doc #>> '{createdAt,$date}' IS NOT NULL
           THEN jsonb_set(body, '{createdAt}',
                  to_jsonb((doc #>> '{createdAt,$date}')::timestamptz))
         ELSE body
       END
  FROM staged
ON CONFLICT (tenant_id, sku) DO NOTHING
RETURNING 1
)
-- Reconciliation, part one. RETURNING counts only rows actually written,
-- so eligible minus inserted is exactly the duplicate (tenant_id, sku)
-- pairs that ON CONFLICT DO NOTHING dropped without raising anything.
SELECT (SELECT count(*) FROM staged)   AS eligible,
       (SELECT count(*) FROM inserted) AS inserted,
       (SELECT count(*) FROM staged)
         - (SELECT count(*) FROM inserted) AS dropped_as_duplicate;

-- Reconciliation, part two: everything the staging filter held back,
-- grouped by reason. Documents carrying no 'sku' key fail that filter too,
-- so they are reported here rather than disappearing between the counts --
-- eligible plus these totals equals the staging row count.
SELECT CASE
         WHEN NOT (doc ? 'sku') THEN 'missing sku'
         ELSE 'unknown status: '
                || coalesce(doc ->> 'status', 'draft')
       END AS held_back_reason,
       count(*) AS documents
  FROM stg_catalogue_item
 WHERE NOT (doc ? 'sku')
    OR coalesce(doc ->> 'status', 'draft')
         NOT IN ('draft', 'active', 'withdrawn')
 GROUP BY 1
 ORDER BY 2 DESC;
SQL

echo "probe complete: reconcile eligible / inserted / held-back counts before trusting this"

Now the asymmetry. There is no equivalent wire-protocol shim in the other direction: nothing lets a Postgres application speak to MongoDB unchanged: that move is a rewrite of the data-access layer, not a proxy. The optionality is real but directional, and the discipline that makes schema migrations reviewable and reversible applies here too.

Click to zoom

The long game: operating two datastores for a decade

The recurring cost of a second engine is mostly an upgrade cost, countable from published dates. MongoDB's lifecycle schedules page says its schedules "specify the end-of-life date for each" release, and those dates are not uniform: 9.0, released September 2026, ends 31 October 2031; 8.3, released May 2026, ends 31 October 2029; 8.0, released October 2024, also ends 31 October 2029; and 8.2, released September 2025, ended 31 July 2026 — already past, as of this writing.

Read the four together; any one in isolation misleads. Windows run from under a year to roughly five — a property of the release, not a constant. MongoDB's versioning page scopes its own rule: "Starting with MongoDB 8.2", Major Releases arrive "every two years" with "a five-year lifecycle" (its examples are 7.0 and 8.0), while a minor like 8.2 trades that for "early access to new features" and "more frequent upgrades" — and once a newer minor lands, "MongoDB does not continue patching the previous minor release". The scoping matters: a fifth row, 7.0, ends 31 August 2027 — four years after its August 2023 release, not five, because it predates that policy. These are Server dates: the table sits under MongoDB Enterprise Advanced, sub-heading MongoDB Server, with no Community schedule published. Two Atlas tables sit above it; only one runs a shorter clock: Atlas Dedicated (Major Release only) lists majors alone on the Server dates — 9.0, 8.0, 7.0 and earlier — so Atlas majors run no tighter a window than self-managed, while Atlas Auto Upgrade tracks the minor train, ending 8.0 on 20 June 2025 rather than 31 October 2029 and giving 8.3 as "TBD, no sooner than October 2026".

PostgreSQL's versioning policy is the other model: the project "supports a major version for 5 years after its initial release. After this, a final minor version will be released and the software will then be unsupported (end-of-life)", with fixes "released at least once every three months". Flat, uniform, knowable years ahead. Over a decade what matters is less which policy is generous than which calendar goes on a roadmap without re-reading a table every year.

The sovereign version of this decision is not "avoid MongoDB": make the second datastore earn its place against the two measurable tests, and if it does, adopt it on purpose, with the sharded topology floor, audit edition and per-release end-of-life lookup priced in. Otherwise the engine already running holds the workload on one backup, one audit trail, one upgrade calendar. Ask it of any specialist store — the argument behind a dedicated vector database versus pgvector, and behind choosing Postgres over SQL Server on licence and topology, not feature lists.