They accept almost identical sets of values as input. ‘There are two JSON data types: json and jsonb. The first thing I learned is that 2 representation of JSON are possible within Postgres: Postgres: JSON VS JSONBĮven 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. So far we experimented with only typical queries which we use in the real world. While this research can be considered as a standalone paper, we are planning to use it as starting point for future studies. The data is structured as specified in the FHIR message exchange format standards. The dataset comes from an existing project in use with Mongo and is composed of several collections. PidFilePath: /var/run/mongodb/mongod.pid # location of pidfile 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.īoth versions of Postgres and Mongo that we’ve used are vanilla versions.

As generic good practice, and as demanded by law in case of medical records, all data is anonymized, and subsequently made accessible to employees. All tests were repeated over multiple runs, and only the average is reported.Īs dataset, we used anonymized data derived from real data. This setup allows read-only benchmarks, since there are no separate set of disks for data and journaling.īoth Mongo and Postgres were installed on the machine, and only one product was running at the time. While not optimal (I would’ve prefered bare metal), this is what we had available for testing. 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). We used a test machine running CentOS 6.8 equipped with one SSD disk, 10 GB RAM, and 4 cores. 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. topic is to us complex and vast, and we are more than willing to listen from you and hopefully learn from it. If you notice inaccuracy, mistakes, or anything that you want to discuss, please reach out to us. 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. That is the reason behind the decision to perform some tests by ourself and start discussing with some numbers at hand. Me and my colleague Wouter discussed the topic and did some preliminary research trying to compare on query execution speed and disk space consumption.Īfter 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. 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? 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.

Here at 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. Last Update: NovemAdded: Postgres table creation details

2 json compare