• Sales 0808 1686 777
  • Support 0333 0142 700

MongoDB vs PostgreSQL databases

We recently wrote a comparison of the database management systems MariaDB and MySQL. These are two of the most popular relational databases, but there are alternative database solutions. In this blog, we’ll compare MongoDB with PostgreSQL.

To explain the differences between MongoDB and PostgreSQL we must first explain the differences between SQL and NoSQL databases. SQL means Structured Query Language and is used for defining and manipulating data in a database.

SQL vs NoSQL databases

The main difference between the two databases is the way in which they structure data. SQL (relational) databases store data in tables made up of columns and rows, whereas NoSQL (non-relational) databases are document-oriented.

If you imagine the structure of a basic social media site, imagine that you have these elements: Post, User, Comments, Likes. In an SQL database the data for these elements is stored in individual tables. These tables are all linked, so to see which ‘User’ ‘Liked’ which ‘Post’ you use JOIN clauses to collate related data between the tables.

The NoSQL way of managing this type of social media site would be to create a document for each post. Each document holds non-relational data about that post, and within the document shows who the user is, which users liked the post, which users commented on the post, and what the comment said. The NoSQL ‘document’ for a post is written in JSON (JavaScript object notation) and might look like this:

{
   "POST": "62789739812793",
   "Author": ["John Doe", "UID4"],
   "Likes": {
        "Count": 5,
        "Users": ["UID2435","UID7494", "UID8999", "UID9000", "UID9432"]
   },
   "Comments": {
        "CommentId_1": {
               "UserID": "UID1",
               "UserName": "Daisy Dukes",
               "Comment": "Lorem ipsum dolor!",
               "Timestamp": "Sat, 29 Nov 2014 16:57:56 GMT"
         }
   }
}

Advantages of SQL vs NoSQL

NoSQL’s document-oriented method allows for more flexibility than SQL. For example, one of the comments on the post could have included a photo, or an added description. To store this extra information with NoSQL is as easy as adding a new field to the document, whereas with an SQL database you would need to reformat and restructure the database to allow for new fields. This requires locking down the tables until data fields are updated, which can lead to a period of downtime.

On a broader scale, a NoSQL database is better when data has relatively little structure, and when data of new types is likely to be added. This allows you to freely upload new data types without having to know what ‘type’ the data will be when you first set up the database. This also saves you time up front when setting up the database.

However, if you know that your data structure will always remain the same an SQL database may be the better option. The schema of an SQL database minimises data loss and stops data tables getting out of sync. This and its improved ability for ACID compliance (atomicity, consistency, isolation, and durability) makes SQL databases the better choice for financial institutes.

MongoDB vs PostgreSQL

Now that we’ve explained the different types of database, we can begin to talk about MongoDB and PostgreSQL and the key differences between the two that might make either more suitable to the needs of a specific project.

Relational differences

The first difference is that PostgreSQL is, perhaps obviously, an SQL database, whereas MongoDB is, intuitively, a NoSQL/non-relational database.

JSON

Where PostgreSQL is unique from other relational databases is its support for JSON data. This makes PostgreSQL a viable alternative to MongoDB for those looking to store static JSON data types. But for full JSON compatibility, MongoDB is the preferred choice.

Data types

As previously discussed, a NoSQL document-oriented structure is better for handling new data types. As technology develops from applications like social media sites to internet of things devices there are always many new data types being created, and MongoDB handles this better.

Scalability

Of the two, MongoDB is more suited to scaling and offers out-of-the-box support for scaling a database over multiple servers. And as more and more data is stored in the database MongoDB scales elastically and horizontally. PostgreSQL does offer the ability for horizontal scaling through a partitioning technique called ‘sharding’ but this is much more complex to execute than the native scaling of MongoDB.

Support

One often forgotten factor to consider when choosing a database system is the available support. Both MongoDB and PostgreSQL are open-source databases so the support for the ‘product’ comes from the community itself. PostgreSQL – initially released in 1997 – has been around for 12 years longer than MongoDB, which means there’s larger, more extensive community support available for PostgreSQL.

ACID transactions

PostgreSQL is fully ACID compliant, and this ensures that data transactions are secure through a wide range of scenarios. In the example of a bank transaction, you want to make sure that all of the records remain in sync with each other, and, in the event of a power outage or disaster, transactions revert back to the original state and no money is lost or duplicated. MongoDB is not ACID compliant so cannot guarantee this.

 

As is often the conclusion with these types of comparisons, there is no overall best choice for database management systems. The choice is situation and project dependent. But whichever database you choose for your projects they are both supported on CloudNX Root Servers from Fasthosts. Or, if you want the power and scalability of cloud servers without having to worry about managing the infrastructure, MongoDB is now available on our CloudNX Managed Stacks.

James Norman's picture

James Norman

Content Editor James is a writer and content editor for Fasthosts. He contributes articles on upcoming trends in web hosting and technology to the Fasthosts blog.