<commit_before>---
layout: post
title:  "A JSON use case comparison between PostgreSQL and MongoDB"
date:   2018-10-31 12:14:56 +0200
categories: PostgreSQL MongoDB Performances Benchmarking
author: <NAME> and <NAME>
---

Last Update: November 11, 2018 - Added: Comparison on Mongo compression 



![Mongo_VS_Postgres](https://raw.githubusercontent.com/Portavita/portavita.github.io/master/img/mongo_vs_postgres.jpeg)




Here at [Portavita][portavita] we work with a lot of data in the JSON format and we store it in MongoDB, a open source, non-relational database (NoSQL) born in 2007.


Since version 9.2, also PostgreSQL is able to talk JSON and several progresses have been made over the years making PostgreSQL more and more production ready to serve JSON efficiently.

Very recently, a new project has been kickstarted here at Portavita, and since Portavita uses both PostgreSQL and MongoDB, the following question arose spontaneously: which database is the most suitable to support our use cases?

Me and my colleague Wouter discussed the topic and did some preliminary research trying to compare on query execution speed and disk space consumption.
After all that reading, learning, asking and studying, the conclusion was that we did not have a clue if Mongo was still the best choice to store our JSON data.

That is the reason behind the decision to perform some tests by ourself and start discussing with some numbers at hand.

So here we are sharing our results for your consideration, and in the hope our efforts might turn handy to some other people out there.

If you notice inaccuracy, mistakes, or anything that you want to discuss, please reach out to us. (<EMAIL>  and <EMAIL>)

The topic is to us complex and vast, and we are more than willing to listen from you and hopefully learn from it.

# How and what


Now that you know 'why' we felt the need to experiment with both systems ourselves, I will tell you more about what we actually did to benchmark one product versus the other.

We used a test machine running CentOS 6.8 equipped with one SSD disk, 10 GB RAM, and 4 cores. It's the same virtual machine we already used in the past to run tests on several different products (like in the previous blog post when I compared InfluxDB to Postgres).

While not optimal (I would’ve prefered bare metal), this is what we had available for testing. This setup allows read-only benchmarks, since there are no separate set of disks for data and journaling.

Both Mongo and Postgres were installed on the machine, and only one product was running at the time. All tests were repeated over multiple runs, and only the average is reported.

As dataset, we used anonymized data derived from real data. As generic good practice, and as demanded by law in case of medical records, all data is anonymized, and subsequently  made accessible to employees. On top of that, for security reasons, on this paper all the mentioned fields have been altered in the name and in their content, and not relevant ones completely removed.

Both versions of Postgres and Mongo that we’ve used are vanilla versions.

This is the relevant part of postgresql.conf
```
checkpoint_completion_target = 0.9
effective_cache_size = 3GB
work_mem = 200MB
wal_buffers = 16MB
shared_buffers = 5GB
random_page_cost = 1   
```

and mongod.conf

```
storage:
  dbPath: /var/lib/mongo
  journal:
    enabled: true

processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

operationProfiling:
  slowOpThresholdMs: 0
```

The dataset comes from an existing project in use with Mongo and is composed of several collections. The data is structured as specified in the [FHIR][FHIR] message exchange format standards.

While this research can be considered as a standalone paper, we are planning to use it as starting point for future studies.

So far we experimented with only typical queries which we use in the real world.

# Postgres: JSON VS JSONB

Even if we use Postgres since a few years already, and I'm the DBA of the company, at the beginning of our talks I was completely green on JSON on Postgres.

The first thing I learned is that 2 representation of JSON are possible within Postgres:

* JSON:   JavaScript Object Notation
* JSONB:  The binary format of JSON

This is what official docs say:


'There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency.
The json data type stores an exact copy of the input text, which processing functions must reparse on each execution;
while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead,
but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.'


We stored every single collection into a Postgres table. Two schemas are holding the tables: fhir2 and fhir3, which correspond basically to the data exchange format they belong to.

We started experimenting on Postgres 9.6.6, and later on Postgres 10.5 and this is a granular view of the space usage using plain JSON on 9.6.6:

|    Relation      | Schema | Total size |
|---|---| --- |
| m_table         | fhir2    | 36 GB    |<commit_msg>add table creation details<commit_after>---
layout: post
title:  "A JSON use case comparison between PostgreSQL and MongoDB"
date:   2018-10-31 12:14:56 +0200
categories: PostgreSQL MongoDB Performances Benchmarking
author: <NAME> and <NAME>
---

Last Update: November 20, 2018 - Added: Postgres table creation details



![Mongo_VS_Postgres](https://raw.githubusercontent.com/Portavita/portavita.github.io/master/img/mongo_vs_postgres.jpeg)




Here at [Portavita][portavita] we work with a lot of data in the JSON format and we store it in MongoDB, a open source, non-relational database (NoSQL) born in 2007.


Since version 9.2, also PostgreSQL is able to talk JSON and several progresses have been made over the years making PostgreSQL more and more production ready to serve JSON efficiently.

Very recently, a new project has been kickstarted here at Portavita, and since Portavita uses both PostgreSQL and MongoDB, the following question arose spontaneously: which database is the most suitable to support our use cases?

Me and my colleague Wouter discussed the topic and did some preliminary research trying to compare on query execution speed and disk space consumption.
After all that reading, learning, asking and studying, the conclusion was that we did not have a clue if Mongo was still the best choice to store our JSON data.

That is the reason behind the decision to perform some tests by ourself and start discussing with some numbers at hand.

So here we are sharing our results for your consideration, and in the hope our efforts might turn handy to some other people out there.

If you notice inaccuracy, mistakes, or anything that you want to discuss, please reach out to us. (<EMAIL>  and <EMAIL>)

The topic is to us complex and vast, and we are more than willing to listen from you and hopefully learn from it.

# How and what


Now that you know 'why' we felt the need to experiment with both systems ourselves, I will tell you more about what we actually did to benchmark one product versus the other.

We used a test machine running CentOS 6.8 equipped with one SSD disk, 10 GB RAM, and 4 cores. It's the same virtual machine we already used in the past to run tests on several different products (like in the previous blog post when I compared InfluxDB to Postgres).

While not optimal (I would’ve prefered bare metal), this is what we had available for testing. This setup allows read-only benchmarks, since there are no separate set of disks for data and journaling.

Both Mongo and Postgres were installed on the machine, and only one product was running at the time. All tests were repeated over multiple runs, and only the average is reported.

As dataset, we used anonymized data derived from real data. As generic good practice, and as demanded by law in case of medical records, all data is anonymized, and subsequently  made accessible to employees. On top of that, for security reasons, on this paper all the mentioned fields have been altered in the name and in their content, and not relevant ones completely removed.

Both versions of Postgres and Mongo that we’ve used are vanilla versions.

This is the relevant part of postgresql.conf
```
checkpoint_completion_target = 0.9
effective_cache_size = 3GB
work_mem = 200MB
wal_buffers = 16MB
shared_buffers = 5GB
random_page_cost = 1   
```

and mongod.conf

```
storage:
  dbPath: /var/lib/mongo
  journal:
    enabled: true

processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

operationProfiling:
  slowOpThresholdMs: 0
```

The dataset comes from an existing project in use with Mongo and is composed of several collections. The data is structured as specified in the [FHIR][FHIR] message exchange format standards.

While this research can be considered as a standalone paper, we are planning to use it as starting point for future studies.

So far we experimented with only typical queries which we use in the real world.

# Postgres: JSON VS JSONB

Even if we use Postgres since a few years already, and I'm the DBA of the company, at the beginning of our talks I was completely green on JSON on Postgres.

The first thing I learned is that 2 representation of JSON are possible within Postgres:

* JSON:   JavaScript Object Notation
* JSONB:  The binary format of JSON

This is what official docs say:


'There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency.
The json data type stores an exact copy of the input text, which processing functions must reparse on each execution;
while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead,
but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.'


We stored every single collection into a Postgres table. Two schemas are holding the tables: fhir2 and fhir3, which correspond basically to the data exchange format they belong to.

The tables has been initially created as:
```
 create table schema.table (data json not null );
 ```
We started experimenting on Postgres 9.6.6, and later on Postgres 10.5 and this is a granular view of the space usage using plain JSON on 9.6.6:

|    Relation      | Schema | Total size |
|---|---| --- |
| m_table         | fhir2    | 36 GB    |
{'fraction_non_alphanumeric': 0.0432928525076481, 'fraction_numerical': 0.011309910076944471, 'mean_word_length': 4.1940298507462686, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5836084', 'n_tokens_mistral': 2864, 'n_tokens_neox': 2656, 'n_words': 1667}