In the past, the Postgres vs. MongoDB debate looked like this: you had Postgres on one side, able to handle SQL (and later NoSQL) data, but not JSON. On the other, you had purpose-built database management systems (DBMS) — like MongoDB, which was designed as a native JSON database.
Today, though, this strict separation has been muddled by the advent of a bunch of in-between options.
The SQL-rooted database architecture PostgreSQL now offers enhanced JSON storage capabilities. So why would you opt to keep both tools?
Before we get into that, let’s remind ourselves what we’re dealing with here. What is this JSON data format we’re trying so hard to accommodate?
All of this makes JSON an important step towards user-friendly computing. Today, many prefer it to XML, and the JSON data format is used by a number of NoSQL data stores.
JSON does, however, lack indexing — and the JSONB data format was created to tackle this problem. JSONB stores data in a binary format, instead of a simple JSON blob. Data input is a bit slower, but processing becomes a lot faster since the data doesn’t need to be re-parsed.
Let’s take a look at the differences between these two commonly used databases.
MongoDB is an open-source database. It’s designed to be agile and scalable, and it uses dynamic schemas so that you can create records without defining the structure first. It also supports hierarchical documentation of data.
PostgreSQL is also open-source, but it’s a relational database that is much more concerned with standards compliance and extensibility than with giving you freedom over how you store data. It uses both dynamic and static schemas and allows you to use it for relational data and normalized form storage. MongoDB, with its unstructured approach, can’t do that.
Which one should you use to store your JSON / JSONB data?
First, to be clear, Postgres and MongoDB both have functions for JSON and JSONB data storage (although MongoDB calls the latter “BSON”).
There are differences, though:
The big thing, of course, is that Postgres lets you keep your options open. You can choose to route data to a JSON column, allowing you to model it later, or you can put it into an SQL-schema table, all within the same Postgres database.
One of the best things about NoSQL database management systems is their performance.
Since they work with simpler data structures than SQL databases, storage and retrieval tend to be faster in NoSQL database systems.
While they may lack the ACID (atomicity, consistency, isolation, and durability) properties you need for financial transactions, etc., they’re great for handling large volumes of unstructured data, at speed.
That said, Postgres gave everyone a shock by beating MongoDB’s performance ratings on EnterpriseDB.com way back in 2014.
You read that right. Incredibly, in tests based on selecting, loading, and inserting complex document data to the tune of 50 million records, Postgres was around twice as fast at data ingestion, two and half times as fast at data selection, and three times as fast at data inserts … all while consuming 25% less disk space.
In fairness, MongoDB 3.0 has since risen to the challenge, introducing a WiredTiger database engine that increases write speeds by 7-10x while cutting disk space by compressing data by 50%.
While MongoDB certainly hasn’t lost its edge, the performance argument is no longer as cut and dry as it once was.
So do you choose Postgres or MongoDB as the best JSON database?
The answer depends on what you want to achieve, and what you currently have in place. To help you make the right decision, ask yourself these seven questions:
Postgres has been around longer and is included free of charge in many Linux operating systems, so it’s well established. That’s not to say you’ll struggle to find MongoDB experts; it’s now the fifth most popular database technology out there, after all.
Just bear in mind what talent you have in-house, and who else you’ll need to take on when making your choice.
Which one to choose is a complex decision as this article has no doubt shown.
To make your decision, think really carefully about what you need out of your database system — and just as importantly, what you’re likely to need in a few years. Not just in terms of storage, but also in terms of what you want to do with your data.
And yes, if you’re already using either MongoDB or Postgres, changing track might feel like a massive pain in the neck, but you’ll want to get this right as soon as you can. As your data keeps growing and getting more complex, turning that ship around will only get tougher.