Postgres vs Mongo

Postgres vs Mongo

❗️ disclaimer ❗️

intro

As a postgres fanboy I’ve been using it for most of my projects. Yet, there are so many different databases out there. Supposedly some are better for certain use cases than others, but deep down I know it’s a lie and postgres is king. Now that we’ve settled that I’m objective and not biased: let’s dive into the comparison of write and read operations in Mongo and Postgres.

setup

In my lab project I will store mocked user data in Postgres and Mongo. The two most common data operations will be compared: write and read. We know that while Postgres is a relational database, Mongo is a document database. This means that Postgres has a fixed number of columns, while Mongo has a dynamic number of document properties. Mainly. Postgres also offers the JSONB type which is a great way to store unstructured data and this resembles the document model of Mongo.

I’ll aim for a setup which is simple, yet incorporates realtional and document-like characteristics.

Four root level properties in mongo and also four dedicated columns in postgres:

• uuid
• name
• email
• user data (object / JSONB)

The user data is an object with a number of different properties.

In mongo all the properties are stored in the document on the root level and the user data is an object with a different properties.

In postgres a fix number of properties are stored in dedicated table columns in atomic form and the user data is stored in the user_data jsonb column with a different properties.

SUMMARY: the the “CR” from CRUD operations were tested in Postgres and Mongo with varying data set sizes. The small set was 1k rows, the medium 100k rows and the large 1M rows. In the first runs no indexes or optimizations were applied. Later on the databases were tuned (partially beyond the line of sane reason, just to squeeze out faster operations and part with “D” from ACID.

Read the story below

CREATE operations

to conclude non tuned insertions:

Mongo performed better in most cases. The initial batch takes always a bit longer in Mongo but then it runs at a constant speed which is faster than in Postgres.

In the next part I wanted to test different optimizations which aim for faster insertions both in Postgres and in Mongo.

optimizations in Postgres

Initially I used parameterised queries for the insertions. Then I used the COPY command along with optimized postgres settings. This had some hiccups becasue the pg npm package doesn’t support COPY from stdin so even though it was supposed to bypass The query planner and going straigt to storage layer, it didn’t result in much faster insertion.

pg-copy-streams came for rescue as it eliminates parsing, planning, and optimization overhead.

Also, COPY uses a single network connection with streaming data, whereas at INSERT each batch requires multiple network calls (BEGIN, INSERT, COMMIT)

Optimizations in MongoDB

TL;TR: Mongo offers insane performance for insertions if you are willing to go in into some trade-offs. 8.8 seconds for 1M records. This is 3,67x faster than Postgres.

What has been done exactly:

• Writeconcern was set to 0 which means that the database will not wait for the data to be written to the disk.
• Journaling was disabled.
• Indexes on the collection were dropped and created only after the data was fully inserted.
• Unordered inserts were used, so the order of the documents was not guaranteed.
• The min pool size was set to 10 and the max pool size was set to 50.

All in all, most of the optimizations were not making a big difference. The writeconcern and journaling are the two settings that make most of the performance boost. But why?

Writeconcern is a setting that controls the level of write acknowledgement from the server. This required a lot of async operations and a lot of network calls which add up to a considerable overhead. With writeconcern disabled the command is fired and Mongo hopes for the best without knowing that it was written successfully. Journaling is a feature that allows the database to recover from a crash by replaying the journal.

So in the end it’s a trade-off between speed and safety.

MongoDB’s Advantage: Write concern optimization (w: 0, j: false) provides massive performance gains, a 4.2x improvement when durability guarantees are removed PostgreSQL’s Advantage: COPY command is more efficient than MongoDB’s insertMany. The Speed vs Safety Trade-off: MongoDB’s 8.8 seconds: Fire-and-forget writes (data could be lost) PostgreSQL’s 34 seconds: Still has some safety guarantees with UNLOGGED tables.

insertion summary:

While I got inspired by the performance of Mongo and tried to disable all the safety guarantees in postgres too, it didn’t result in any performance boost in postgres. I disabled the wal logging, the autovacuum, dropped the indexes before the insertion, disabled fsync, disabled full page writes, increased the size of shared buffers, etc… This all brought me significant. At some point I even got a worse performance than the inital postgres setup. I’m guessing Postgres just prefers to play it safe.

READ operations

The second section for this post is about the speed measurements for the read operations. In Mongo and in Postgres I’ve covered the same SELECT cases. There were 19 cases all together with 1M records in the table.

The journey had three distinct phases:

• Phase 1: Untuned - Raw performance with default configurations
• Phase 2: Slightly tuned - Basic optimizations applied
• Phase 3: Properly tuned - Strategic indexing and configuration

The read performance journey

phase 2: light tuning

After the initial runs, I applied basic optimizations to both databases. Not very thought trough operations, but defining a number of indexes, adding GIN indexes on the JSONB columns, adding partial indexes for common filter conditions, introducing trigram search for text searches… For the sake of getting to a point I will jump straight to the optimized metrics.

phase 3: strategic indexing

The final phase involved proper indexing strategies tailored to each query type. A significant number of indexes were added. This will be visible in the end of the post that sometimes it has messed up the query planner and it was overwhelmed a bit which resulted in some cases in worse performance. Not often though.

the optimization journey

How the query times have changed over the optimization phases.

the final comparison

takeaways

1. Indexing strategy matters a lot

The most dramatic performance improvements came not from a particular database, but from implementing proper indexing strategies. A 159x improvement in PostgreSQL’s name searches is powerful indeed.

2. Postgres’ query optimizer is sophisticated

Even with similar indexing strategies, PostgreSQL consistently outperformed Mongo. The query optimizer’s ability to choose optimal execution paths even (or maybe especially) with complex queries is impressive.

3. JSONB vs Documents: Postgres wins. Obviously. Duh.

What I was silently hoping for from the beginning: PostgreSQL’s JSONB performance beats Mongo. When for document-style queries Mongo should excel, PostgreSQL with GIN indexes and trigram search performed in my case almost always better.

4. Mongo’s strengths in document queries are worse than expected

Mongo performed well in specific scenarios (some unindexed operations, very simple document retrievals), but PostgreSQL’s performance across diverse query types is just insane.

the verdict

After three phases of optimization and hundreds of benchmark runs, PostgreSQL is the clear winner for read operations. Surely take it with a grain of salt, I had only one data table which is not very production system-like setup. But still… Postgres outperformed Mongo in almost every single case. This means to me that Postgres not only has the relational database adventages but also is a better fit for unstructured data than Mongo. The GIN index and the trigram based search on JSONB columns is a game changer.

complete performance matrix

Here’s the comprehensive view of all benchmark results across all optimization phases. This heatmap shows every single query from all 6 benchmark runs, giving you the complete picture of how each database performed throughout the optimization journey.