Table of contents
§8.6.

Postgres versus MongoDB

Postgres and MongoDB both solve the challenge of data storage.

Key differences are tabulated below:

PostgreSQL

MongoDB

Theoretical basis:

Relational model
A relation is a set of tuples. Each tuple consists of labeled values corresponding to attributes (i.e., “rows” with “columns”).

Document collection
A document is a complex object with arbitrary nesting and structure (i.e., a JavaScript or JSON object).

Structure:

create table statements declare the structure in advance. For example:

create table shopping_item(
  id serial primary key,
  description text,
  quantity numeric
);

No declarations are required. Collections accept the insertion of any document.

Example record:

(1, 'Chocolate bar', 5)

{ description: "Chocolate bar", quantity: 5 }

Record identification:

Primary key
The primary key is a set of attributes that uniquely identify records. No two records in a relation can have identical values for their primary key.

Object ID
The database generates an internal reference code for every object in the database.

Query and data manipulation language:

SQL

JavaScript

Example query:
(Retrieve the quantity for "Chocolate bar")

 
select quantity
from shopping_item
where description = 'Chocolate bar';
 
db.items.findOne({
  description: {$eq: "Chocolate bar"}
}).quantity

Storing related information:

Additional relations store related information. For example, the following definition creates an additional table for permitted brands when shopping:

create table approved_brands (
  item_id integer
    references shopping_item(id),
  brand_name text
);

Then associated records can be added to the new table:
(1, 'Darrell Lea')
(1, 'Lindt')

Related information is embedded directly into the inserted documents through complex structure. For example:

{
  description: "Chocolate bar",
  quantity: 5,
  approved_brands: [
    "Darrell Lea",
    "Lindt"
  ]
}

Further information about PostgreSQL and MongoDB can be found on their websites.

Reflection: Benefits and weaknesses

The table above compares PostgreSQL and MongoDB without any judgment about which is “better”.

In which circumstances would the differences be strengths or weaknesses when building web applications?

How would you decide between PostgreSQL and MongoDB when starting development on a new application?