MongoDB vs PostgreSQL: Which is Perfect For You? - ByteScout
  • Home
  • /
  • Blog
  • /
  • MongoDB vs PostgreSQL: Which is Perfect For You?

MongoDB vs PostgreSQL: Which is Perfect For You?

MongoDB and PostgreSQL present us with two rich but different paradigms for database management. In this Bytescout developer intro, we will compare the features of these two paradigms in depth. To facilitate the best design decision for your project, we will reveal the nuances and distinctions of both Mongo and Postgre. Which DB is best for your enterprise?

Relational databases, with SQL queries being the most widely used, constantly evolve with exciting new features. PostgreSQL is an excellent example, providing an object-oriented version of a MySQL relational database. This harmonizes perfectly with OOP languages like Python and Java. Traditionally, these are the well-known features of standard SQL databases:

  • Relational database schemas
  • Relational database management
  • Highly structured data – Tabular data
  • Procedural language – Structured Query Language
  • Primary key relational DB

Newer “NoSQL”  databases such as MongoDB provide rich capabilities including non-tabular, multi-structured – also called polymorphic data, and indexed data. Schema-free databases and real-time aggregation are among the innovative capabilities featured in NoSQL. Here are several additional features of the ever-evolving NoSQL databases platforms like MongoDB:

  • Multi-structure data models and schemas
  • Indexed non-tabular database tables
  • Faster DevOps/Agile development cycles
  • Open source platforms
  • High scalability

Our in-depth DB comparison today features PostgreSQL as an example of relational databases. And for comparison the best representative in the NoSQL category is MongoDB. Each has its unique and ideal application. We will demo both with code examples to enable you to determine which is best for your developer project. Let’s start with basic definitions of each.

  1. PostgreSQL Core Features
  2. PostgreSQL Inheritance
  3. MongoDB Fundamentals
  4. The Critical Nature of Indexing
  5. PostgreSQL Indexing Methods
  6. Indexing MongoDB Documents
  7. Scalability of NoSQL Platforms
  8. Now and Ten Years Hence

PostgreSQL Core Features

As an object-relational database management system (ORDBMS), PostgreSQL supports extensibility and ACID compliance. PostgreSQL is a transactional DB platform, and supports the following design methods:

  • Foreign keys
  • Updatable views
  • Triggers

Like many SQL variants, PostgreSQL supports functions and stored procedures. Tabular data with standard row and column format is the core of the DB structure. PostgreSQL implements primary keys to identify data rows, and foreign keys to guarantee referential integrity among joined relational tables. However, PostgreSQL also has one foot in the NoSQL door too. With PostgreSQL, coders can leverage methods and events including constraints and roles.

PostgreSQL Inheritance

We have seen that PostgreSQL supports object-oriented programming features. Along those lines, let’s look at an example which demonstrates the use of inheritance in PostgreSQL code. Look at this example:

CREATE TABLE city (
  CityName       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (city);

PostgreSQL language supports the INHERITS command. In this case, the capitals table inherits all the fields and properties of the city table. This ease of coding facilitates rapid development and readability of code.

Daily Automation with RPA Tools

MongoDB Fundamentals

Alternatively, MongoDB supports multi-structured data types. These are non-tabular or polymorphic data structures which do not conform to any SQL relational database type schema. Today’s web apps demand an indexable DB to automatically scale both up and down, in capacity. Flexibility in structure and scalability are the realm of MongoDB in particular.

In MongoDB, developers store schema-free data in docs resembling JSON format. “Schema free” means that no predefined data structure or relational schema is required. Records are found and retrieved via indexing and keyword searches. This variability of the structure is ideal for the diverse data types required by web apps including video and graphics data.

Just as Postgre has NoSQL features, MongoDB likewise has SQL features! Mongo implements an expressive query language with strong continuity. But developers can create loosely structured document data model at the outset because MongoDB is schema-free. In other words, an IoT device output can easily become the data model for MongoDB.

Let’s compare an object-relational database management system example in PostgreSQL with that of MongoDB. Although there is a correspondence, the methods and data content are very different:

Postgre Mongo

Table               Collection

Column            Key

Value               Value

Records           Document

Postgre records are equivalent to Mongo Documents in that these are the fundamental units of data storage. Likewise, a MongoDB Collection is equivalent to the ORDBMS Table of PostgreSQL. However, the great exception which appeals to Agile/DevOps developers is that fields can be added to a Mongo Document without first restructuring the DB.

This flexibility of schema-free data structures in MongoDB appeals to Agile and DevOps teams.  These developers are often under pressure to add components in a development ecosystem characterized by rapidly changing storage requirements for new media types. Now that we’ve covered data storage, Let’s look at how both platforms index, search and retrieve data.

The Critical Nature of Indexing

Indexing a database can improve performance, but can be costly. Indexing may simply incur too much overhead for real-time apps with high-frequency data updates. The decision to index all or part of the database is yet another flexible feature supported in both Postgre and Mongo. Developers may use innovative optimization strategies to fetch data. Let’s see how this works.

PostgreSQL Indexing Methods

PostgreSQL supports B-tree as well as hash indexes. B-tree is a heuristic or tree data structure of sorted data which supports searching and sequential access, as well as insertions and deletions. PostgreSQL also supports expression indexes.  Partial indexes are a flexible way of selectively indexing a part of a Postgre table.

Indexing MongoDB Documents

As you know, if a DB is not indexed, the DB engine must search every record every time a simply query runs. Building an index in advance saves time in each query. But building the index also takes time. With MongoDB there are many options for predicting queries and optimizing searches. MongoDB supports these types of indexes for specific data types and query types:

  • Single Field

MongoDB supports user-defined indexes on a single field of a document. Here is the example below considering a single column index. For example, if DoB field is indexed, then the statement:

db.userData.find().sort({"DoB":1})

Sorts the date of birth field in ascending order.

  • Default _id

MongoDB collections have an _id field index by default. If  _id has no value then mongodb auto creates an _id field and inserts the ObjectId value. The _id index is unique as a primary key in SQL. This prevents insertion of two documents with equal values for in the the _id field.

  • Multikey Index

Array data is indexed with multikey indexes in MongoDB. If a data column that contains an array value is indexed then MongoDB builds individual index entries for each element of the array. Multikey indexes of this type enable queries to select documents containing arrays by matching array elements. This is an extraordinarily powerful feature of MongoDB. So, let’s demonstrate with an example. In this Mongo Doc:

{
        "_id" : ObjectId("528f3er50fe5e6467e770"),
        "user_id" : "user13",
        "password" : "r1l2m3",
        "sex" : "Female",
        "age" : 23,
        "DoB" : "16/10/2018",
        "education" : "M.S.",
        "profession" : "QA Engineer",
        "interest" : "Cosmology",
        "extra" : {
                <span class="style1">"friends"</span> : {
                        "<span class="style2">valued_friends_id</span>" : [
                                "John",
                                "Paul",
                                "Peter"
                        ],
                        <span class="style2">"ban_friends_id</span>" : [
                                "Sue",
                                "Lou",
                                "Hue"
                        ]
                }
        }
}

The following query will sort within the ban_friends array:
db.userdetails.find().sort({"extra.friends.ban_friends_id":1})

  • Text Indexes

MongoDB provides a specific text index which supports searching for any text string within a collection of Docs. Like most search engines, MongoDB text indexes ignore words like “the” and  “of.” And Mongo likewise keys to root words. Here is the format:

{
  $text:
    {
      $search: <string>,
      $language: <string>,
      $caseSensitive: <boolean>,
      $diacriticSensitive: <boolean>
    }
}
  • Geospatial Index

Geospatial coordinate data is easily indexed and queried in MongoDB collections. Here is another unique feature to set MongoDB apart from traditional RDBMSs in today’s web app world. MongoDB supports both 2d indexes and 2sphere indexes for use with geospatial data types. 2d is for planar coordinate geometry, and 2 spheres for 3D spherical geometry data. Here is a typical expression of GPS coordinates in MongoDB:

location: {
      type: "Point",
      coordinates: [-23.853, 30.447]
}

And a typical query for data of this type:

db.places.find(
   {
     location:
       { $near:
          {
            $geometry: { type: "Point",  coordinates: [ -23.853, 30.447 ] },
            $minDistance: 500,
            $maxDistance: 1000
          }
       }
   }
)
  • Hashed Indexes

MongoDB supports hash-based sharding. This is accomplished with a hashed index type. The hashed index actually indexes the hash of the value of a field. Because these indexes have a random distribution of values, only exactly equal matches can be queried. Range values and inequalities are not supported for hashed index queries. Creating a hashed index is as easy as this:

db.collection.createIndex( { _id: "hashed" } )

  • Compound Index

MongoDB offers support for user-defined indexes on simultaneous multiple fields. The order of compound fields is significant. If a compound index consists of {“DoB”: 1, “password”: -1 }, then the index sorts first by DoB and then subsorts by subsequent fields in the compound index list. For example:

db.userdetails.find().sort({"DoB":1,"password":-1})

  • Index Properties
  1. Unique Indexes

The unique property for an index rejects duplicate values for an indexed field. A unique index guarantees that the indexed fields do not contain duplicate values. MongoDB thus enforces the unique field value like a primary key in SQL.

  1. Sparse Indexes

The sparse index property prevents MongoDB from auto-generating indexes for docs with missing indexes. This property ensures that only the indexes of Docs with indexed fields are included. The sparse index property can be combined with the unique index property to skip Docs with duplicate values for a field but ignore those without an indexed key.

  1. TTL Indexes

The TTL index is a special index property which executes a timed expiration for data. In other words, a TTL index removes documents from its collection after a specified time duration. MongoDB provides support in this way for reducing outdated material and provides for maintenance of time-sensitive data.

Because the index and query are crucial to developer logic in creating web apps based on MongoDB Docs, we need to devote exceptional effort to understanding these concepts. Perhaps beyond any other single feature, indexing clearly distinguishes MongoDB from standard RDBMSs, making MongoDB uniquely well suited for diverse mobile apps and IoT applications.

Geospatial Code Example

In order to highlight some of the MongoDB Indexing features we’ve mentioned, let’s look at a substantial example app. In this app, we will develop MongoDB docs to store and query geospatial data. The importance of handling this type of data in today’s GPS dependent world cannot be overstated. Here we demo inserting data into a MongoDB doc:

db.places.insert( {
    name: "NY Park",
   location: { type: "Point", coordinates: [ -73.91, 40.82 ] },
   category: "Parks"
} );
db.places.insert( {
   name: "S. Roosevelt Park",
   location: { type: "Point", coordinates: [ -73.28, 40.93 ] },
   category: "Parks"
} );
db.places.insert( {
   name: "P. Grounds Stadium",
   location: { type: "Point", coordinates: [ -73.75, 40.03 ] },
   category: "Stadiums"
} );

We want to create a 2dsphere index for querying the location field. This is accomplished with a single line as:

db.places.createIndex( { location: "2dsphere" } )

And now for the magic, we can query all referenced points within a specified distance range. We enter a point compatible with the doc format above and then specify the min and max distance range. The DB will return all points in the doc automatically!

db.places.find(
   {
     location:
       { $near:
          {
            $geometry: { type: "Point",  coordinates: [ -73.67, 40.178 ] },
            $minDistance: 1500,
            $maxDistance: 5600
          }
       }
   }
)

The extraordinary advantage here is that all this development work is already done. Such an application would be arduous to build in a traditional SQL platform. And this one already achieves compatibility with many popular web apps.

Scalability of NoSQL Platforms

We will now delve into an important issue which is less obvious to developers on the surface: gargantuan scalability. The MongoDB platform architecture supports nearly unlimited scalability. The scalability feature of Mongo has three components:

  • Cluster Scale
  • Performance Scale
  • Data Scale

The cluster Scale refers to the capacity of the Mongo platform to distributing a database over more than 100 nodes and across multiple data centers. The performance scale refers to the capacity to support more than 100,000 databases read/write queries per second while maintaining strict compliance latency SLA. The data Scale capacity of MongoDB attains storage of over one billion documents in a database.

Now and Ten Years Hence

We’ve seen some of the superficial mechanics of the differences between PostgreSQL and MongoDB. But the momentum of trends toward MongoDB and its peers is staggering.  the momentum created by millions of developers in the community deserves special attention. Today these trends include:

  • Developers now architect applications involving innovative and rich new, and rapidly changing data types, including structured, semi-structured, unstructured and polymorphic data in massive volume! Big Data needs NoSQL, and there’s no indication of a change to this momentum.
  • Waterfall methodology has submitted to DevOps and Agile rapid development cycles. Small teams roll out new apps in agile sprints, iterating quickly and committing code every week in continuous integration pipelines.
  • Applications now delivered as services must be always-on, and accessible from many devices types. Scalability must extend to the global scope. And data distribution must be optimally close to users for low latency, in order to achieve compliance with data sovereignty regulation.
  • Organizations now use open software running on commodity servers and cloud computing platforms rather than bulky server and storage hardware.

These are the momentous trends of current and immediate future development around database apps. Stay tuned to Bytescout as we keep a watchful eye on state of the art developer news, and keep you in the loop!

 

About the Author

Author Mark

Mark Ronald Moore

Mark is a freelance consultant and coder in the areas of machine learning, automation testing, and web app development. He currently writes coding tutorials and tech articles regularly for ByteScout. Mark is a resident of Humboldt, California, and enjoys hiking in the redwoods.

 

 

prev
next