Postgres JSONB vs MongoDB under load: the numbers, the plans and the one GIN setting

“Just use Postgres, it has JSONB” is easy to say. To check it, I stored the same order documents in a PostgreSQL jsonb column and in a MongoDB collection, put one Go service in front of each, and loaded both with k6 at the same moment. This post has the setup, all result tables, the query plans behind the one surprising result, and the commands to repeat each check yourself.

Everything that produced these numbers is in huseyinbabal/db-wars.

Results

The pass mark for a rate: p95 under 50 ms with under 1% failed requests. The table gives the highest rate it held, in requests per second. Rounds marked 3 ran three times and show the median.

+ means the database still held the top of the tested range, so its real limit is higher.

The single runs are single runs. The swapped-host check moved Postgres from 5,474 to 6,477 on different hardware of the same size, which tells you how much two “identical” droplets can differ. Treat a gap of a few hundred requests per second in a one-run row as noise.

The setup

Two stacks, built the same way, in one DigitalOcean region (lon1):

The app and load machines are four times the database on purpose: the database has to run out first, or you end up measuring the Go service. One extra small machine runs Prometheus and Grafana and listens only on localhost.

Both stacks run every round at the same moment. If the region gets busy at 3 pm, both sides feel it.

Data and indexes

One million orders, each a JSON document of about 600 bytes with a customer, a status and three items. The same document goes into both databases.

CREATE TABLE orders (id bigint PRIMARY KEY, doc jsonb NOT NULL);
CREATE INDEX orders_customer ON orders (((doc->'customer'->>'id')::bigint), id);
CREATE INDEX orders_items ON orders USING gin ((doc->'items') jsonb_path_ops);

db.orders.createIndex({ "customer.id": 1, _id: -1 })
db.orders.createIndex({ "items.product_id": 1 })

MongoDB’s _id index plays the role of the primary key. The product index is where the two engines differ most: Postgres uses a GIN index over the items array, MongoDB a multikey B-tree.

Settings that have to match

Both acknowledge a write only after it is on disk. Leave journal=true out of the MongoDB URI and you are comparing a durable database with one that is not.

Load

k6 uses an arrival-rate executor: it starts a fixed number of requests every second whether earlier ones have returned or not, and raises the rate every 30 seconds. With virtual users instead, a slow database slows the test down with it and the numbers look better than they are.

The request mix per round, from k6/doc.js:

Why Postgres falls over in the shop mix

Every pure round went fine for Postgres, but the mix failed at its very first step in all three runs. During the failure every connection was on the CPU and none waited on a lock (pg_stat_activity grouped by wait_event). perf showed JSONB containment comparisons at the top. The table statistics told the rest:

SELECT seq_scan, seq_tup_read FROM pg_stat_user_tables WHERE relname = 'orders';

 seq_scan | seq_tup_read
----------+--------------
      643 |    205917016

 seq_scan | seq_tup_read
----------+--------------
      725 |    232126112output,

82 sequential scans in 10 seconds, about 320,000 rows each, for product searches that should hit about 30 orders out of a million. The index exists, so why is it skipped?

Step 1: the generic plan

pgx prepares every query. After five executions of a prepared statement, Postgres starts comparing a generic plan (one plan for any parameter) with the average cost of the custom plans it made so far, and switches to the generic one when that looks no worse. You can see the generic plan with EXPLAIN (GENERIC_PLAN), new in PostgreSQL 16:

EXPLAIN (GENERIC_PLAN) SELECT doc FROM orders WHERE doc->'items' @> $1 LIMIT 10;

 Limit  (cost=0.00..97.93 rows=10 width=595)
   ->  Seq Scan on orders  (cost=0.00..97928.54 rows=10000 width=595)
         Filter: ((doc -> 'items'::text) @> $1)output

Without a product id it guesses 10,000 matches, so it expects to find 10 of them early in a scan. Cost 97.93.

Step 2: the custom plan, with and without a pending list

For a real product the planner uses the index:

EXPLAIN SELECT doc FROM orders WHERE doc->'items' @> '[{"product_id": 4242}]' LIMIT 10;

 Limit  (cost=21.87..60.98 rows=10 width=595)
   ->  Bitmap Heap Scan on orders  (cost=21.87..412.95 rows=100 width=595)
         ->  Bitmap Index Scan on orders_items  (cost=0.00..21.84 rows=100 width=0)output,

61 is cheaper than 98, so the custom plan wins. Then the inserts start. A GIN index has fastupdate = on by default: new entries are appended to an unsorted pending list and merged into the index later, by vacuum or when the list reaches gin_pending_list_limit (4 MB). Every search has to read that whole list, and the planner adds it to the index plan’s cost:

SELECT pending_pages, pending_tuples FROM pgstatginindex('orders_items');

 pending_pages | pending_tuples
---------------+----------------
           124 |           4200output,

 Limit  (cost=599.87..638.98 rows=10 width=595)
   ->  Bitmap Heap Scan on orders  (cost=599.87..990.98 rows=100 width=595)
         ->  Bitmap Index Scan on orders_items  (cost=0.00..599.84 rows=100 width=0)the

639 is far above 98. The plan cache picks the generic Seq Scan, which is only cheap if the guess of 10,000 matches were right. It is wrong by a factor of 300, so each search reads a third of the table. pgstatginindex comes from the pgstattuple extension.

The fix and its price

ALTER INDEX orders_items SET (fastupdate = off);
SELECT gin_clean_pending_list('orders_items');

With no pending list the custom plan stays at 60.98 and the shop mix holds 5,474 requests per second. The price is that every insert now updates the GIN index right away: new orders dropped from 5,964 to 2,970 per second, below MongoDB’s 3,966.

Two other fixes I did not measure: SET plan_cache_mode = force_custom_plan keeps the planner from switching to the generic plan, and pgx can run without prepared statements (QueryExecModeExec or QueryExecModeSimpleProtocol). Both keep fastupdate on and should keep the insert speed, at the cost of planning every query.

MongoDB has no equivalent problem here. Its product index is a normal multikey B-tree, and the plan for the same search is an index scan with exact bounds:

db.orders.find({ "items.product_id": 4242 }).limit(10).explain().queryPlanner.winningPlan

stage: 'LIMIT', inputStage: { stage: 'FETCH', inputStage: {
  stage: 'IXSCAN', indexName: 'items.product_id_1', isMultiKey: true,
  indexBounds: { 'items.product_id': [ '[4242, 4242]' ] } } }output

Why big documents go to MongoDB

With 40 items an order grows to about 3.4 KB of JSON. Postgres compresses it and still keeps it inside the row, about 1.1 KB, with the TOAST table empty. A status update then costs this:

EXPLAIN (ANALYZE, WAL, COSTS OFF)
UPDATE orders SET doc = jsonb_set(doc, '{status}', '"delivered"') WHERE id = 4242;
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'orders';

 Update on orders (actual time=0.551..0.552 rows=0.00 loops=1)
   WAL: records=6 bytes=3542
 ...
 n_tup_upd | n_tup_hot_upd
-----------+---------------
         2 |             0output

Postgres never edits a row in place. The update writes a new version of the whole row, and because every index here is built on an expression over doc, the update cannot be a HOT update, so each index gets a new entry too: six WAL records and 3.5 KB for changing one string. The GIN index on items gets entries for all 40 items again.

MongoDB’s profiler shows the same change touching no index at all:

db.setProfilingLevel(2)
db.orders.updateOne({ _id: 4242 }, { $set: { status: "delivered" } })
db.system.profile.find({ op: "update" }, { keysInserted: 1, keysDeleted: 1, nModified: 1, _id: 0 }).sort({ ts: -1 }).limit(1)

[ { nModified: 1, keysInserted: 0, keysDeleted: 0 } ]output

That is the 1,492 vs 4,479 row in the table. Turn the profiler off again with db.setProfilingLevel(0), level 2 writes every operation to system.profile.

Repeating it

git clone https://github.com/huseyinbabal/db-wars && cd db-wars/postgres-vs-mongodb
cd setup && ./up.sh && ./provision.sh && cd ..      # doctl must be logged in
./round.sh read-1 read 3 1000 1000 12 30s
PG_TUNE="ALTER INDEX orders_items SET (fastupdate = off)" ./round.sh shop-tuned-1 shop 3 400 400 12 30s
./setup/down.sh                                       # deletes every droplet with the tag

The seven droplets cost about $0.71 an hour together (DigitalOcean list prices). Each round reloads one million orders on both stacks before it starts.

What this does not cover

One database machine per side, no replicas, no sharding. MongoDB’s strengths in replica sets and horizontal scaling, and Postgres’ strengths in joins, constraints and transactions across tables, are outside this test. The documents are read and written whole; a workload that queries deep inside documents with ad-hoc filters could rank the two differently.

Within that scope: MongoDB was ahead on reads and on big documents that change in place. Postgres JSONB was ahead on small inserts and updates, and on the mixed workload once the GIN index stopped using its pending list. If you put JSONB with a GIN index under a write-heavy load, check pgstatginindex before you blame JSONB.

Sources

• db-wars: the app, the k6 script and the setup scripts
• PostgreSQL: GIN fast update technique
• PostgreSQL: plan_cache_mode and generic plans
• PostgreSQL: PREPARE, when generic plans are used
• PostgreSQL: pgstattuple (pgstatginindex)
• PostgreSQL: heap-only tuples (HOT)
• MongoDB: database profiler
• MongoDB: multikey indexes
• pgx: QueryExecMode
• k6: ramping arrival rate executor