Changing one field in a 100KB document, in Postgres and in MongoDB

The usual argument for keeping documents in Postgres is that jsonb gives you most of MongoDB without running a second database. I mostly agree with it. The part I had never measured is what happens when you change one small field inside a big document, over and over, which is what a view counter or a status flag does all day.

So I measured it. The same documents, the same update, in Postgres and in MongoDB, at three document sizes.

With 1KB documents Postgres was the faster of the two by a wide margin. With 100KB documents the jsonb version had lost 94% of its throughput and MongoDB was ahead of it. Then one extra column put Postgres back in front, by a factor of eight.

The setup

Two managed databases on DigitalOcean in Frankfurt, both the 2 vCPU / 4GB size with a single node: Postgres 17.11 and MongoDB 8.0.26. A 4 vCPU droplet in the same VPC ran the load over the private network, and each database's firewall accepted that droplet and nothing else. Both clusters were online by the time I had finished setting up the droplet, which is most of why this comparison got done at all.

The documents are fake orders: a customer, a status, a views counter, and a list of line items padded out to 1KB, 10KB or 100KB. 20,000 documents at the small size, 2,000 at the large one.

The update adds one to views on a random document. Eight client processes, twenty seconds, five runs per cell. Every run starts from a freshly loaded table, and the three setups take turns so that drift hits them all equally.

In MongoDB:

db.docs.updateOne({_id: id}, {$inc: {views: 1}})

In Postgres with the whole document in one jsonb column:

update docs set doc = jsonb_set(doc, '{views}',
  to_jsonb((doc->>'views')::int + 1)) where id = $1

And in Postgres with the counter pulled out into a real column next to the document:

update docs set views = views + 1 where id = $1

What happened as the documents grew

Updates per second, median of five runs, range in brackets.

document size	Postgres, field in jsonb	Postgres, field in a column	MongoDB $inc
1KB	6,101 (5,333 – 6,409)	5,438 (4,028 – 8,549)	1,672 (1,480 – 2,210)
10KB	2,154 (1,672 – 2,444)	8,027 (5,013 – 8,371)	1,545 (1,299 – 1,708)
100KB	375 (373 – 410)	7,982 (5,101 – 9,534)	984 (970 – 1,145)

Read down the first column. From 1KB to 100KB the jsonb update goes from 6,101 a second to 375. That is sixteen times slower for an update that changes one integer.

MongoDB slows down too, but only by about 40% across the same range.

The column version does not care how big the document is. At the median it even got faster once the documents were large, and I will come back to why.

Tail latency follows the same pattern. At 100KB the 99th percentile was 62ms for jsonb, 48ms for MongoDB and 4ms for the column.

Where the time goes in Postgres

Postgres never edits a row in place. An update writes a new version of the row and leaves the old one for vacuum. For a jsonb column that means a new copy of the entire document, and the write-ahead log shows it:

document size	WAL per update, field in jsonb	WAL per update, field in a column
1KB	1,306 bytes	1,309 bytes
10KB	5,202 bytes	261 bytes
100KB	43,301 bytes	257 bytes

Forty-three kilobytes of log to add one to a number.

The first row is the one worth a second look. At 1KB the separate column buys nothing. Both versions write about 1.3KB per update, because a document that small is stored inline in the row and gets copied along with it either way. Past roughly 2KB Postgres moves the document out to a side table and the row keeps only a pointer. Updating views then copies the pointer and leaves the document where it is. That is the whole trick, and it is why the column version sped up as the documents grew.

Disk tells the same story. I ran each setup for sixty seconds at 100KB and looked again after a minute's rest:

• Field in jsonb: 23,698 updates. The table went from 83MB to 701MB and Postgres wrote 1.1GB of WAL.
• Field in a column: 332,467 updates. The table stayed at 83MB with 86MB of WAL.
• MongoDB: 64,701 updates. The collection went from 74MB to 155MB on disk.

At rest the two engines were close. The 2,000 large documents are 210MB of BSON, and they occupied 74MB in MongoDB and 83MB in Postgres.

Reads inside Postgres have the mirror image of the problem. Fetching doc->'views' from a 100KB document ran at 3,150 a second, because the document has to be fetched and decompressed to find one key. Selecting the column ran at 8,412.

The defaults are not the same defaults

Halfway through I checked what each database does before it tells you a write succeeded, and they differ.

The managed Postgres had synchronous_commit set to off. A commit returns before its log record is flushed to disk. The managed MongoDB uses write concern majority, which waits for the journal. So in the table above Postgres is being asked for less on every write.

I could not line them up exactly, so I ran each one the other way round, again five runs at 100KB:

• Postgres with synchronous_commit = on: 350 a second for jsonb, 3,326 for the column.
• MongoDB with w: 1 and journal: false: 1,507 a second.

So with both waiting for the disk, the column is at 3,326 against MongoDB's 984. With neither waiting, it is 7,982 against 1,507. The ordering at 100KB survives both ways: column, then MongoDB, then jsonb. The 1KB result is the one to distrust, since Postgres led there while doing less work per commit.

You can set synchronous_commit per session or per transaction, which is how I changed it. It is worth knowing which way yours is set before you compare anything.

What I got wrong

I tested the script on my laptop first, with both databases in Docker. There MongoDB did 14,673 updates a second on 100KB documents and looked untouchable. On the managed cluster it did 984. The local container was a bare standalone that acknowledged writes without waiting for anything. I nearly designed the post around that number.

I assumed the two services would ship with equivalent durability. I only looked because Postgres at 1KB seemed too fast.

I also measured reads on both engines and I am not publishing the comparison between them. When I checked afterwards, with eight client processes the droplet had 29% of its CPU idle during a MongoDB read run and 53% during a Postgres one. The Python driver for MongoDB costs more on the client, so that test was partly measuring my load generator. The Postgres-against-Postgres read numbers above use one driver and are fine.

What I would take from it

Small documents: stay in jsonb and stop worrying. At 1KB the update is cheap and a separate column changes nothing.

Large documents with a hot field: move the field out. A separate column took the 100KB case from 375 updates a second to nearly 8,000 and stopped the table growing.

MongoDB handles the awkward case better by default. If you can't predict which fields will be hot, its 40% slowdown is a much gentler failure than a sixteen-fold one.

Check the commit settings before believing a write benchmark. That includes this one.

Caveats

This is one instance size, one region and one morning, with eight clients and a Python load generator. The range on some cells is wide, especially the column version, and I would not read anything into differences smaller than those ranges. Autovacuum was left at its defaults and twenty seconds is not long enough to see it catch up or fall behind. The MongoDB cluster was a single node, so majority meant one member.

The whole thing ran for about an hour and a half on two databases and one droplet and cost roughly 35 cents by my estimate from the hourly prices. The benchmark is about 150 lines of Python, and the three statements above are everything that matters in it.