If you already have PostgreSQL and only need limited indexing against the JSON, I'd use that... If you're looking for a document datastore for HA and/or read/write scaling, then I'd look at RethinkDB or MongoDB. I like RethinkDB better (as do many), it's currently pretty stable, I'm hoping to see some development pick up. There was some scare late last year as the backing company shuttered, but it's now under the Linux Foundation, so should see continued support.
Ask HN: Posgresql JSON or MongoDB?
11–20 of 26 posts
Re: Ask HN: Posgresql JSON or MongoDB?
#12Use Postgres 9.6 JSONB. Trust me no one has ever looked back and said "I wish I had used MongoDB instead of PostgreSQL".
Re: Ask HN: Posgresql JSON or MongoDB?
#13Use Postgres 9.6 JSONB. Trust me no one has ever looked back and said "I wish I had used MongoDB instead of PostgreSQL".
You said "pretty much". So use PostgreSQL 9.6 and but use JSONB only for the schemaless data but normal typed columns for the rest. And remember that JSONB is a column type so don't try to store too many different things in a one-column table. You can have as many tables as you need to sensibly organize your data, and then as many non-JSONB columns as you need for things like meta-data. For instance, date created, date changed, who created it, who changed it, is there an audit log for this table, when was it last replicated, backed up, etc. And then, think about whether you should just stuff all the data into one JSONB column or several.
For that matter you could have both a JSON and a JSONB column. If you run into data that is not well-formed JSON then you will get an error trying to insert it into a JSONB column but it will INSERT just fine in a JSON column allowing you to deal with the well-formedness problem later.
And do make use of the rich selection of JSON and JSONB functions to create indexes on your tables because nothing speeds up querying like an index that lets you filter your data and only process the important subset.
PostgreSQL has been undergoing some heavy development in recent years as more and more companies shift away from proprietary commercial databases. There are several companies offering full commercial support for PostgreSQL if you need/want that.
This development work is leading to a constant stream of improvements, both performance and new features. This alone is a good reason to choose PostgreSQL. It now embraces both the SQL schema world and the schemaless NOSQL world in one database system.
Re: Ask HN: Posgresql JSON or MongoDB?
#14Postgres 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?
PostgreSQL is not just a tool, it is now an ecosystem as well. There are numerous add-ons to provide various types of high availability. Some are plugins to PostgreSQL and some not. Also, some of these have commercial support if you need it.
And PostgreSQL 10 which is out in September will have logical replication added, which is rather useful in engineering a high-availability database.
Re: Ask HN: Posgresql JSON or MongoDB?
#15be careful: UPDATE operations are nightmarish compared to mongo
at least for some use cases, as this one:
http://stackoverflow.com/a/42434665/244058
Re: Ask HN: Posgresql JSON or MongoDB?
#16Make 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…
does this apply to jsonb as well?
Re: Ask HN: Posgresql JSON or MongoDB?
#17Postgres 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?
Just use Amazon RDS and let them deal with it.
None of the theoretical scaling benefits of Mongo outweigh the clusterfuck of maintenance, data inconsistency, and other general problems that shitty, shitty piece of software has.
Re: Ask HN: Posgresql JSON or MongoDB?
#18If you're already using Postgres then just use that. If it doesn't work out switch to Mongo. No sense in adding another layer of infrastructure and complexity if you don't need it.
Re: Ask HN: Posgresql JSON or MongoDB?
#19Make 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/
WT is the default storage engine and it locks at document level ("row")
Re: Ask HN: Posgresql JSON or MongoDB?
#20Postgres 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?
MongoDB has HA built-in (replica sets). You will need to meddle with pacemaker/coroync or repmgr for HA in PostgreSQL.