MongoDB vs PostgreSQL: What to consider when choosing a database

Why do we need databases?#

This question may be a bit obvious, but understanding why we need databases helps when it comes to choosing a database structure for your stack. Databases are a basic foundation of software development, and they serve many purposes for building projects of all sizes and types. Let’s take a deeper dive into the uses of a database.

Data Persistence#

How would you feel if you were to visit a website, add items to a shopping cart, and navigate away from the site only to come back to an empty cart? A frustrating experience, I’m sure. This is where databases come in.

Having a database allows for session persistence so that a user can log in and stay logged in for an extended period of time. When thinking of your database choice, what type of data do you need to be persisted?

The type of data you are using help you choose the database that will most suit your data and client needs.

Organization#

In one of my first jobs, I recall using a system that used 5x7 ledger cards to keep track of customers, payments, and the rental inventory we carried in the warehouse. It was cumbersome, not without human error, and led to redundancies in records.

A database with an inventory management system could have helped this company come into the 21st century and reduce the need for a bookkeeper and inventory reconciliation.

A database assists in normalizing your data. There are several different flavors of normalization, but the high level explanation is that it reduces redundancy and anomalies in your data. The retail store example from above could have certainly used a computerized database to increase productivity and reduce the amount of manual tabulating.

With the organization of a database, you can learn a lot more about your data, as it makes that information readily available to assist decision making.

How you organize that data will help you choose the database that will most suit your data and client needs.

Records#

Businesses thrive on data. Having a database to collect customer information, such as likes, dislikes, order history, or articles read, allows a business or organization to target their consumers more readily. This will lead to higher sales, more traffic, and better targeted ads.

Data collection and analysis is key for any business to survive in this big data era. How you want to access and use data will help you choose the database that will most suit your data and client needs.

Important terms to know before making a database decision#

Now that we are familiar with the main reasons we should use a database, let’s look at some important terms we need to know before making a database decision. The following list is certainly not an exhaustive list, but knowing these basic terms will assist you in choosing a database that’s right for your project.

ACID#

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. It’s a property of relational databases that guarantees database validation and is quite reliable. Definitions of the four properties are as follows:

• Atomicity. The basic idea behind atomicity is that it supports a transaction paradigm. This paradigm is used primarily by relational-based databases. When we say transaction paradigm, what does it actually mean? Either a transaction fails completely or succeeds completely, such as a transfer on funds from one account to another.

• Consistency. When there is no effect on either bank account because of a failed transaction, it means that the database is in a consistent state. Consistency tells us that a transaction has brought the database from one valid state (pre-transaction) to another valid state (post-transaction). Valid in this sense means that the data is set according to defined rules or constraints.

• Isolation. Imagine we have two separate people who are using an ATM to each retrieve cash from the same account at the same time. The idea behind isolation means they wouldn’t be able to fool the bank into thinking they are only pulling $100 from the account when actually they are pulling $200 ($100 from each ATM). The bank’s database treats them as sequential transactions.

• Durability. If there are power failures, catastrophic outages, or crashes, durability guarantees that completed transactions are already recorded.

BASE#

BASE is an acronym that means Basically Available, Soft State, Eventually Consistent. It is unique to non-relational databases. These types of databases don’t have the ACID guarantee, as they are eventually consistent. This means that there could be times where the database is not reliable, but over time it will reach consistency. So your data is basically in a fluid state until it becomes consistent.

Join#

This is a term used in relational databases to connect two tables. It reduces the amount of redundancy in your data. In SQL, a JOIN clause is used to combine rows from two or more tables, based on a common column, and there are three types of JOIN clauses for different needs.

NewSQL#

A term coined for database systems (i.e. VoltDB and MemSQL) that combines the best aspects of relational databases (ACID) with the efficiency and horizontal scalability of NoSQL databases.

Normalization#

Normalization is the process of structuring a relational database. It allows us to use tables and columns to reduce redundancy in data, minimize anomalies in data modification, and simplify queries. Normalization is used in relational databases.

NoSQL#

A type of database system that does not necessarily use traditional structured query language (SQL) to query database systems. Examples of NoSQL databases include MongoDB and DynamoDB. NoSQL databases are non-tabular, and they vary based on their different data models, such are document, wide-column, key-value, or graph. Their structure provides flexible schemas, and they can be scaled easily.

Persistence#

As we discussed in our first section, data is persistent when it outlives the process that created it. Persistence refers to a process or object that continues to exist after the parent ceases or after the system is switched off.

RDBMS#

RDBMS is an acronym that stands for Relational Database Management System. It’s usually a SQL-based database such as PostgreSQL or MySQL and meets the ACID requirement. RDBMS makes it easy to access and locate values in a database. We call it “relational” because the values in a table and tables themselves are related, making it possible to run queries across many tables at the same time.

SQL#

Structured Query Language is designed for performing CRUD (Create, Read, Update, Delete) operations on a database. We use SQL to communicate with a database, and we can use SQL statements to perform tasks like updating or retrieving data from a database.

Overview of MongoDB and PostgreSQL#

When choosing your database structure, it’s important to factor in speed, reliability and accuracy. On the one hand, we have a relational paradigm that can guarantee data to be in a valid state. On the other hand, we have a non-relational paradigm that guarantees eventual consistency. So, how do we choose? Let’s take a look at two of the most popular databases that come from each paradigm: MongoDB and PostgreSQL.

MongoDB: a NoSQL Database#

NoSQL basically means one thing: it doesn’t only use SQL to query its database (The “No” in NoSQL stands for “not only”). MongoDB is a NoSQL, document-oriented database that uses documents to manage information. Let’s look at the key features on MongoDB to get a better sense of its uses.

BSON/JSON#

MongoDB uses BSON, a binary JavaScript Object Notation, under the hood to make querying extremely fast. BSON is not meant to be human readable. The BSON is translated to JSON to be read when you open your collection. When viewing documents, you will recognize the ever popular JSON format with key/value pairs that is language agnostic.

Document-oriented#

Using JSON allows you to change your schema on a whim without repercussion. Documents can vary in terms of key/value pairs. Unlike relational databases, where altering your table is necessary to make any changes, MongoDB is a bit more flexible because it uses the JSON/BSON format. Individual entries are their own instance of the schema that was written. As time goes on, the schema can be changed with no consequence to the database. The frontend developer would just need to perform some error handling if null values are present in the API calls.

ACID properties in MongoDB?#

NoSQL databases don’t usually conform to the ACID properties but instead adopt eventual consistency. This makes NoSQL databases less ideal for financial institutions where the validity of its transactions is most important.

That being said, MongoDB has adopted more to the ACID format in 2018, though it still has yet to measure up to the rigidity of those properties. A study published in May 2020 identified a bug that affects the claim that Mongo performs ACID transactions at an acceptable level. If you are leaning toward making ACID a priority, research should be done to investigate if transactions are performed in an acceptable manner for your product.

PostgreSQL: an SQL-based Relational Database#

PostgreSQL, also known as Postgres, is an open-source relational database management system that emphasizes extensibility and SQL compliance. Let’s look at the key features on Postgres to get a better sense of its uses.

SQL#

Postgres employs SQL ultimately under the hood, a structured query language, to define, to access and to manipulate the database. Postgres does use its own flavor of SQL called PL/pgSQL (procedural language/postgreSQL). The big difference between the two is that the latter can perform more complex queries than SQL. Other relational database models have their own flavor of SQL, which leads to minor differences across the board between the different databases.

Transactions#

Postgres transactions follow the ACID principle. This means that the validity of its data is quite reliable.

Tables#

Because this database has a relational structure, the primary component that defines the difference between this and MongoDB covered earlier is that the whole schema needs to be designed and configured at creation. Altering a table after onset can be done, but can lead to not easily identifiable bugs down the road.

Foreign Keys#

PostgreSQL databases can use foreign keys. Foreign keys allow us to keep our data normalized by referencing an object from one table in another so the second table has access to the first table’s keys and values.

Choosing between MongoDB and PostgreSQL#

So, now that we know what each database has to offer, we need to determine when to choose each depending on the data, organization, and requirements in question. Let’s break it down to inform our database decision. Keep in mind that there is no perfect database. The key is to identify your needs and best match the abilities and benefits with those guidelines.

Next step for your learning#

MongoDB and PostgreSQL are both reputable databases that have their advantages and disadvantages. What is most important is how your data is going to be used, what structure will it have, and how will your application scale.

The best way to choose the best database is learn more about the options available to you. Educative’s hands-on course Database Design Fundamentals for Software Engineers walks you through the need-to-know concepts and essential techniques for optimizing your database. You learn about:

• SQL
• Relational databases
• Data modeling
• Normalization
• and more

Happy learning!

Continue reading about databases#

• The complete guide to system design in 2022
• MySQL Tutorial: The beginner’s guide to using MySQL
• MongoDB vs MySQL: Which database to use