I know nothing about Mongo, but one thing you won't get with Postgres is indexing inequality operations on arbitrary JSON fields. (You can accelerate equality and membership on arbitrary JSON fields using GIN indexes, and you can index inequality on fields known a priori with expression indexes, but not both.)
Also note that locking is at least at a per-row granularity, there is no way to contend only part of a JSON structure. Again, not sure what Mongo does here.
Likewise, updates are at a per-row granularity. This means that – assuming your JSON is indexed – you lose the benefit of HOT updates (https://github.com/postgres/postgres/blob/master/src/backend...), which can result in a lot of vacuuming and indexing overhead if you have an update-heavy workload. Best practice here would be to place indexed and unindexed parts of the JSON objects in different columns, to regain HOT update optimization for updates to unindexed values.
Re:Ask HN: Posgresql JSON or MongoDB?
by maerF0x0 on Tuesday May 09, 2017 @10:19PM
(#14305477)
Mongo seems to lock on various granularities. Sounds like collection ("table") is common and with WiredTiger engine (default iirc) it goes to document locking ("row").
I know nothing about Mongo, but one thing you won't get with Postgres is indexing inequality operations on arbitrary JSON fields. (You can accelerate equality and membership on arbitrary JSON fields using GIN indexes, and you can index inequality on fields known a priori with expression indexes, but not both.)
Also note that locking is at least at a per-row granularity, there is no way to contend only part of a JSON structure. Again, not sure what Mongo does here.
Likewise, updates are at a per-row granularity. This means that – assuming your JSON is indexed – you lose the benefit of HOT updates (https://github.com/postgres/postgres/blob/master/src/backend...), which can result in a lot of vacuuming and indexing overhead if you have an update-heavy workload. Best practice here would be to place indexed and unindexed parts of the JSON objects in different columns, to regain HOT update optimization for updates to unindexed values.