Choosing Between a JSON Column and a Document Database

"Should we use Postgres JSONB or MongoDB?" is asked as a performance question and is almost never one. Both can do an indexed lookup on 200,000 documents in about a millisecond. The real differences are in what you are giving up and taking on: joins, constraints and transactions on one side, a document model with its own query language, replication and scaling story on the other. This article puts numbers on the parts that can be measured, lists the factors that decide in practice, and ends with the cases where the answer is neither.

What a JSON column keeps

When a JSON column lives in a relational database, the document is one column in a row, and everything else stays as it was:

• Joins. An order document can still be joined to the customers table on a promoted customer_id. The comparison of the options for that is in working with JSONB in PostgreSQL.

• Constraints. Foreign keys, NOT NULL, CHECK and unique indexes apply to the columns around the document, and to extracted fields through generated columns (PostgreSQL's generated column with a foreign key accepted and enforced one).

• Transactions across tables. Insert an order document and update stock in one transaction, with the isolation levels you already know.

• Tooling and people. Backups, replication, monitoring, migrations, SQL skills and reporting tools already exist.

• Partial adoption. A single column is a small commitment you can reverse.

What it does not give you is a natural way to treat the document as the unit: you update a field by rewriting the document, query nested fields through functions and expression indexes, and keep the shape rules in CHECK constraints or application code.

What a document database gives

MongoDB treats the document as the row. The things that follow:

• Nested fields are first-class. {"specs.dims.w": 20} is an ordinary filter, and an index on it is declared directly. There is no function to remember, and no expression-matching rule like SQLite's.

• Partial updates. $set, $inc, $push and friends change fields in place. In my run, update_many({"brand": "Acme"}, {"$set": {"specs.color": "pink", "specs.origin.city": "Lyon"}}) modified 40,000 documents in 904 ms and created the missing origin parent without ceremony (the relational databases leave a missing parent untouched, or create it, depending on the engine, as the articles on updating a JSONB key and adding and removing keys show).

• Arrays as a native index target. A multikey index on tags made "has tag" a seek.

• Horizontal scale. Sharding and replica sets are the product's design center. A relational JSON column scales as the database does.

• An aggregation pipeline designed for nested data.

What the numbers say

The collection of 200,000 documents:

data size           35.8 MB     (average document 187 bytes)
index: sku          2.3 MB
index: tags         1.8 MB      (multikey)
index: brand+price  3.2 MB

Queries from pymongo, best of three, client round trip included:

find by sku, no index                 36.4 ms     (collection scan)
count brand + in_stock, no index      46.6 ms
count nested specs.dims.w = 20        46.6 ms
count tag gift, no index              46.2 ms
group by brand, avg price             46.0 ms
find by sku, index                     0.9 ms
count tag gift, multikey index         5.5 ms     (33,333 matches)
brand Acme and price > 490, compound   0.5 ms     (831 matches)

Without an index, every query was a scan of about 36 to 47 ms. With the right index, a selective lookup took under a millisecond. The relational engines tell the same story with different constants. PostgreSQL scanned the 200,000 documents in 17 ms using parallel workers, and answered the SKU containment lookup in 0.03 ms of execution time through a GIN index (indexing JSONB with GIN). MySQL needed a generated column to reach 0.017 ms (indexing a MySQL JSON field), and SQLite 0.17 ms through an expression index (indexing JSON fields in SQLite). The figures are not comparable to the Mongo ones: server-side times against client-side times, different machines, no tuning. The conclusion that holds is that with the right index, any of them is fast; without one, all of them scan. Speed is not the reason to choose.

Size is a little different. MongoDB's 35.8 MB was close to the JSON text size (30.7 MB for the same documents as text) because BSON keeps field names in every document, as the relational JSON types do. PostgreSQL's JSONB table was 59 MB with its overhead, and MySQL's 48 MB. All of them lost to typed columns (11.8 MB in SQLite, 17 MB in PostgreSQL). Compression and storage engine settings change the picture; in all cases, repeating key names in every document costs space.

Schema freedom is the same freedom

Both stores accept whatever you give them. I added one deliberately wrong document to the Mongo collection, with sku: 5, brand: ["x"] and price: "free", and it went in beside 200,000 normal ones. A later price > 450 count returned 20,182 and a $type: "string" check found the one string price. A PostgreSQL JSONB column behaves the same way until a CHECK constraint says otherwise (the constraints section of the PostgreSQL guide), and MongoDB has its own validation: a $jsonSchema validator on the collection. Both systems can enforce shape and neither does by default. A flexible schema is a decision to enforce shape in code instead, and the cost shows up as bugs found in reports.

What decides in practice

1. Is the document the whole model or one part? A catalog of products with free-form attributes next to orders, customers and stock is one part: the relational engine has the other parts and the joins. A system whose only entity is the document (a content store, a configuration service, a log of events) can use a document database naturally.

2. How many fields do you query? If queries touch three fields, a JSON column with generated columns or expression indexes is fine. If they touch thirty, each needing a path index, the document database's direct indexing is easier to live with, or the data should be columns.

3. Do you need joins? Mongo has $lookup, which works and is not the model's strength. If you join often, you want a relational engine.

4. Do you need cross-record constraints and transactions? MongoDB supports multi-document transactions, and they have costs and limits. A relational database has done this for decades.

5. Do you need to scale writes across machines? This is the clearest case for a document database or a distributed store. A single PostgreSQL or MySQL server goes a long way, and the question is whether you need to go further than one server can.

6. Who runs it? A team that already operates PostgreSQL well adds a JSONB column for the price of a migration. Adding a second database technology adds an on-call burden, backup and restore procedures, upgrades and monitoring. That cost is the usual deciding factor, and it is rarely in the benchmarks.

7. What do other tools expect? Reporting and BI tools speak SQL. Exports from a document database to SQL are a project.

A quick scoring: if you answered "part of the model", "few fields", "yes joins", "yes constraints", "no multi-machine scale" and "we run Postgres", the answer is a JSON column and it is not close. The opposite answers point to a document database. Mixed answers point to the JSON column first, since it is the smaller step and the easier to reverse.

Scenarios

A product catalog with varying attributes per category. JSON column. Fixed columns for id, sku, name, price and category; the attributes in JSONB or JSON; a generated column or two for the attributes people filter by.

User settings and preferences. JSON column (storing settings as JSON in SQLite shows the pattern). A document database for this alone would be a lot of machinery.

Event or log ingestion at high volume. Neither is ideal. A columnar store, as in storing JSON in ClickHouse, or a file format with a warehouse, handles it better; the JSON type there stored 200,000 documents in a third of the space of a string column and filtered ten times faster.

A content management system where each page is a document. A document database is natural; a JSON column also works if pages relate to users, permissions and workflows.

A key-value workload with huge scale and a known access pattern. A key-value store with documents as values, such as DynamoDB (maps and lists as JSON documents), with the access paths designed up front.

Moving between them

Starting with a JSON column and moving out is a bounded job if you kept the access paths in top-level columns: export the documents (as JSON Lines, see JSON Lines for data pipelines), keep the ids, and rewrite the queries. Moving from a document database to a relational one is a larger job if the documents grew organically: the fields have to be discovered, typed and sometimes split into child tables. Querying JSON files with DuckDB is a good first step for that, because it shows the real shape of what is in the collection.

When neither is the right answer

The data is tabular with a stable schema. Typed columns win on size, speed and constraints in every engine measured here.

You mainly analyze it. Export it to a columnar store or Parquet; JSON in a transactional store is slow for scans.

You only pass it through. Store the payloads as files or text, with an index table.

You have not measured the problem. Both options are fine at 200,000 documents. Pick the one your team can operate, add the index you need, and revisit if the data or the load grows by two orders of magnitude.

The vendors document the options in the MongoDB data modeling guide and the PostgreSQL manual on JSON types. A document can be inspected in the browser with the JSON formatter before you decide where it lives.