Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: Posgresql JSON or MongoDB?

news.ycombinator.com

Post reply on HN
Print view View original

1–10 of 26 posts

Ask HN: Posgresql JSON or MongoDB?

#1

Post by ssijak »

I am building some app where part of it will have pretty much unstructured and changing data which I would like to dump as JSON. As a primary DB I am using PostgreSQL. I have never used JSON field type, but have used MongoDB before. I don't need anything fancy, 99% of the time it will be just storing and getting the data and querying by some fields of the JSON and it is of a scale that one instance of PostgreSQL could handle, nothing epic.

My question is, is PostgreSQL JSON field comparable to using MongoDB? Are there any limits or stumbling blocks that I should now about before choosing PosgresSQL for this? And is there an easy way to view and edit that JSON field by hand like I can when using Studio 3T app for MongoDB?(I know PostgreSQL DB apps are not the greatest for some reason..)

Re: Ask HN: Posgresql JSON or MongoDB?

#2

Post by colanderman »

Make sure you're using at least PG 9.5, which includes operators for modifying JSON values: https://www.postgresql.org/docs/9.5/static/functions-json.ht...

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?

#3

Post by jlawer »

Postgres is amazingly powerful if you want to get into it, and its near obsession with ensuring that everything is correct ensures a level of reliability that is typically not present in the MongoDB / MySQL world.

However that does result in much of the tooling not being able to support all of the features, unless they simply pass you down to SQL commands. Most of this is due to the MASSIVE feature set that Postgres supports. I use JetBrains DataGrip for Postgres work (which is SQL command based) , where as for MySQL i do most work with Sequel Pro (Typical MySQL GUI).

I am working on a reporting data warehouse right now using Postgres, and we are storing all data as JSONB objects as an abstracted "Entities" which link to other "Entities", where I am trying to get some of the advantages of triple stores / graph databases, while keeping compatibility with SQL / RDBMS which our developers and systems are used to integrating with. I think postgress is the best starting point for this project, simply due to the ability to manage the DB consistency inside the DB using triggers and stored procedures. While I am dealing with many different structures, I still need to be able to have a certain "sub-schema" of properties to be able to link the data to other data, which I can enforce using PGSQL.

However if your literally looking for something to Stash and retrieve a JSON object by primay key... your not going to get that much value from Postgres. There is some benefit if you already have pgsql infrastructure, as MongoDB can have some caveats around reliability (at least with the older versions). This is not that it is difficult to set it up in a mostly reliable fashion, it just requires some attention. However it will really come down to is your requirements, knowledge of the tools and use case.

Re: Ask HN: Posgresql JSON or MongoDB?

#4

Post by angersock »

Postgres JSONB is strictly better performing on a single node than MongoDB. You can also create triggers and things to properly validate your data and do other cool stuff you can't with Mongo.

There is literally no reason to ever use Mongo for a small-medium project anymore.

If you need tooling, check out the new pgAdmin 4: https://www.pgadmin.org/

Re: Ask HN: Posgresql JSON or MongoDB?

#5

Post by maerF0x0 »

angersock wrote: post #4

Postgres JSONB is strictly better performing on a single node than MongoDB. You can also create triggers and things to properly validate your data and do other cool stuff you can't with Mongo. There is literally no reason to ever use Mongo for a small-medium project anymore. If you need tooling, check out the new pgAdmin 4: https://www.pgadmin.org/

what about High Availability?

Re: Ask HN: Posgresql JSON or MongoDB?

#7

Post by maerF0x0 »

colanderman wrote: post #2

Make sure you're using at least PG 9.5, which includes operators for modifying JSON values: https://www.postgresql.org/docs/9.5/static/functions-json.ht... 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…

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").

https://docs.mongodb.com/manual/faq/concurrency/

Re: Ask HN: Posgresql JSON or MongoDB?

#9

Post by e1g »

maerF0x0 wrote: post #6

I'm also curious if anyone has tried JSON types in Amazon PG compatible Aurora or RDS?

We have been using JSON on RDS extensively since 9.5. Today probably 80%+ of PG JSON capabilities are used by the app in some way for some pretty complex use-cases (fintech). Never had any issues with this on RDS - everything just works (multi-as, read replicas, performance profiling etc) and we love it. Haven't tested Aurora yet.

Re: Ask HN: Posgresql JSON or MongoDB?

#10

Post by tracker1 »

maerF0x0 wrote: post #5
angersock wrote: post #4

Postgres JSONB is strictly better performing on a single node than MongoDB. You can also create triggers and things to properly validate your data and do other cool stuff you can't with Mongo. There is literally no reason to ever use Mongo for a small-medium project anymore. If you need tooling, check out the new pgAdmin 4: https://www.pgadmin.org/

what about High Availability?

Depending on your needs, MongoDB (also look at RethinkDB) are more mature in terms of administration of clustered/replica nodes... PostgreSQL has more of this working its' way into the box, but imho not there yet... Also, write scaling will do much better with MongoDB sharding or RethinkDB clustering.
Post reply on HN