PostgreSQL Vs. MongoDB

This series of posts analyzes how specific databases stack up against each other. We've covered the following comparisons so far:

In this post, we'll see how PostgreSQL compares to MongoDB.

 Who Uses These Databases?

The following companies use either PostgreSQL or MongoDB.

PostgreSQL: Apple, BioPharm, Etsy, IMDB, Macworld, Debian, Fujitsu, Red Hat, Sun Microsystem, Cisco, Skype. You can see the full list here.

MongoDB: Google, UPS, Facebook, Cisco, eBay, BOSH, Adobe, SAP, Forbes, and really many more. You can see the full list here.

 What About Database Structure?

PostgreSQL: PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. PostgreSQL is ACID-compliant, transactional, has updatable and materialized views, triggers, and foreign keys. It also supports functions and stored procedures.

PostgreSQL uses tables, constraints, triggers, roles, stored procedures and views as the core components that you work with. A table consists of rows, and each row contains a same set of columns. PostgreSQL uses primary keys to uniquely identify each row (a.k.a record) in a table, and foreign keys to assure the referential integrity between two related tables.

PostgreSQL also supports many NoSQL features as well.

MongoDB: MongoDB uses JSON-like documents to store schema-free data. In MongoDB, collections of documents do not require a predefined structure and columns can vary for different documents.

MongoDB has many of the features of a relational database, including an expressive query language and strong consistency. However, since it is schema-free MongoDB allows you to create documents without having to create the structure for the document first.

A useful comparison with relational database management systems (RDBMS) in which you have: Table | Column | Value | Records. In comparison, in MongoDB you have: Collection | Key | Value | Document. This means that collections in MongoDB are like tables in RDBMS.

Documents are like records in a RDBMS. Documents can easily be modified by adding or deleting fields without having to restructure the entire document.

 Are Indexes Needed?

Indexes enhance database performance, as they allow the database server to find and retrieve specific rows much faster than without an index. But, indexes add a certain overhead to the database system as a whole, so they should be used sensibly.

Without an index, the database server must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more costly operation.

PostgreSQL: PostgreSQL includes built-in support for regular B-tree and hash indexes. Indexes in PostgreSQL also support the following features:

  • Expression indexes - created with an index of the result of an expression or function, instead of simply the value of a column
  • Partial indexes - index only a part of a table

 

MongoDB: Indexes are preferred in MongoDB. If an index is missing, every document within the collection must be searched to select the documents that were requested in the query. This can slow down read times.

 How Are Their Queries Different?

We’ll now look at PostgreSQL vs MongoDB in terms of queries. We’ll compare PostgreSQL queries to MongoDB queries on a customer table.

Selecting records from the customer table

PostgreSQL:
SELECT * FROM customer;

MongoDB:
db.customer.find()

Inserting records into the customer table

PostgreSQL:
INSERT INTO customer(cust_id, branch, status) VALUES ('appl01', 'main', 'A');

MongoDB:
db.customer.insert({
cust_id: 'appl01',
branch: 'main',
status: 'A'
})

Updating records in the customer table

PostgreSQL:
UPDATE customer SET branch="main" WHERE custage > 2;

MongoDB:
db.customer.update(
{custage: { $gt: 2 }},
{$set: { branch: 'main' }},
{multi: true}
)

Where (And How) Are These Databases Deployed?

PostgreSQL: PostgreSQL was written in C, with support for the following programming languages: C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, and Erlang. This is the REST API for any Postgres database.

MongoDB: MongoDB was written in C++, with support for the following programming languages: Actionscript, C, C#, C++, Clojure, ColdFusion, D, Dart, Delphi, Erlang, Go, Groovy, Haskell, Java, JavaScript, Lisp, Lua, MatLab, Perl, PHP, PowerShell, Prolog, Python, R, Ruby, Scala, and Smalltalk.

Both PostgreSQL database and MySQL database are available on multiple operating systems.

What Types Of Replication / Clustering Are Available?

Replication enables you to have multiple copies of the data copied automatically from 'master' to 'slave' databases. Multiple benefits to this process include:

  • backup
  • spreading the load to improve performance
  • analytics team can work on one of the slave databases, thus not hurting the performance of the main database with long-running and intensive queries

Clustering, in the context of databases, refers to using shared storage and putting more database front-ends on it. The front end servers share an IP address and cluster network name that clients use to connect, and they decide between themselves who is currently in charge of serving clients requests.

PostgreSQL: PostgreSQL replication is synchronous (called 2-safe replication), so that it utilizes two database instances running simultaneously where your master database is synchronized with a slave database. Unless both databases crash simultaneously, data won't be lost.


With synchronous replication, each write waits until confirmation is received from both master and slave. For more information, please refer to the detailed wiki.

MongoDB: A replica set in MongoDB is a group of mongod processes that maintain the same data set. Replica sets provide redundancy and high availability, and are the basis for all production deployments. For more information, please refer to the detailed manual.

Who's Currently Behind The Databases?

PostgreSQL: PostgreSQL is an open-source project maintained by PostgreSQL Global Development Group and their prolific community. Here's a full list of the contributors, and the source code is on Github.

MongoDB: MongoDB was started in 2007 by 10gen, which created the product based on the word “humongous”. In 2009, it was released and 10gen later changed their company name to MongoDB, Inc. MongoDB, Inc. provides development of the software and sells their enterprise solution.

Who Provides Support and is There a Community?

PostgreSQL: PostgreSQL has a wide variety of community and commercial support options available for users. The Community support includes mailing lists and IRC.

Companies that offer commercial support are listed here.

MongoDB: MongoDB offers the Community Support Forum, ServerFault, and StackOverflow. Users can also get enterprise support 24x7 via Enterprise grade support.

Who Maintains The Documentation?

PostgreSQL: There is a wealth of PostgreSQL information available.

MongoDB: MongoDB maintains the MongoDB documentation. From there, you can find information about the MongoDB Server, Atlas (database-as-a-service), cloud manager for hosted MongoDB, and Ops Manager.

Very useful community sites are the omnipresent StackOverflow and a bit more database-specific StackExchange for Databases.

Which Database Is Right For Your Business?

PostgreSQL: PostgreSQL seems to be gaining more popularity. If you're looking for a solution that is standard compliant, transactional and ACID compliant out of the box and has wide support for NoSQL features, then you should check out PostgreSQL.

MongoDB: MongoDB can be a great choice if you need scalability and caching for real-time analytics; however, it is not built for transactional data (accounting systems, etc.). MongoDB is frequently used for mobile apps, content management, real-time analytics, and applications involving the Internet of Things. If you have no clear schema definition, MongoDB can be a good choice.

We hope that this PostgreSQL vs. MongoDB post helped you with your decision.

Get a free consultation with a data architect to see how to build a data warehouse in minutes.
Request Demo
Read more in:

Work smarter, better, and faster with weekly tips and how-tos.