Why The Guardian Switched From MongoDB to PostgreSQL

Why they switched, how they migrated—and why they wouldn’t do it today

In early 2018, The Guardian, a British newspaper and online publication, switched its 2.3m content items from MongoDB to PostgreSQL.

Why did they switch? What would they do differently today?

The Day the London Servers Baked

In July 2015, a massive heatwave struck London, with the unusual consequence of The Guardian switching from MongoDB to PostgreSQL just three years later.

At the time, The Guardian’s data was in a standard NoSQL database; MongoDB stored locally at Guardian Cloud, the data center in the basement of The Guardian’s office near Kings Cross.

While writers for The Guardian were live-blogging about the heat, developers at The Guardian were scrambling to bring the main servers back online.

The extreme heat had caused a failure of The Guardian’s in-house CMS tool, Composer, and its database — causing The Guardian’s site to temporarily switch to the fail-over servers elsewhere in London.

This was the decisive factor motivating that the switch to the more reliable Amazon Web Services (AWS) was to happen sooner rather than later.

But why the move away from MongoDB?

AWS Has MongoDB Too, but…

The Guardian moved their servers from Guardian Cloud to AWS shortly after the heatwave stress-tested their fail-over procedures.

But, the real point of contention with MongoDB came out shortly.

The developers who worked on the project revealed that problems with OpsManager — Mongo’s database management software — required hundreds of hours of engineering time:

OpsManager didn’t really deliver on its promise of hassle-free database management. [...] We lost at least two months of engineering time a year doing this database management work. — Philip McMahon, Maria-Livia Chiorean, Susie Coleman and Akash Askoolum in The Guardian

In fact, the team had paid for an expensive Mongo support contract, but then didn’t receive helpful support during two-hour-long database outages.

MongoDB Was Not a Good Fit on AWS

In fairness, The Guardian had unique requirements that required hundreds of lines of Ruby scripts to handle the AWS infrastructure:

Due to editorial requirements, we needed to run the database cluster and OpsManager on our own infrastructure in AWS rather than using Mongo’s managed database offering. — Philip McMahon et al. in The Guardian

This meant that developers were constantly working on the database infrastructure, instead of having OpsManager handle the back-end.

The Decision to Switch From NoSQL

After two years, The Guardian decided it was time to finally move away from their custom infrastructure for MongoDB on AWS.

The natural fit would have been DynamoDB, Amazon’s NoSQL offering.

But one key feature was missing, and it made all the difference.

Timing was the main reason that The Guardian switched from MongoDB to PostgreSQL — and a different choice would likely have been made today since DynamoDB recently added that missing important feature.

The Killer Missing Feature

The Guardian needed a feature called encryption at rest, and unfortunately, DynamoDB did not offer it at the time.

The developers delayed the project, but eventually, they had to decide:

After waiting around nine months for this feature to be added, we ended up giving up and looking for something else, ultimately choosing to use Postgres on AWS RDS [Relational Database Service]. — Philip McMahon et al. in The Guardian

Switching from NoSQL to SQL brought its own set of challenges, as well as a unique technical solution: the jsonb column type, which has support for indexes on fields within the JSON blob.

How They Made It Work

SQL and NoSQL databases are often seen as diametrically opposed:

Document databases such as MongoDB use JSON documents in order to store records, just as tables and rows store records in a relational database. — MongoDB white paper on JSON

According to the developers, the jsonb type helped make the data model in PostgreSQL look more like the MongoDB records:

We hoped that by using the jsonb type, we could migrate off Mongo onto Postgres with minimal changes to our data model. In addition, if we wanted to move to a more relational model in future we’d have that option. — Philip McMahon et al. in The Guardian

Using the JSON blob meant less data reorganization and a faster migration.

The Power of Stack Overflow

It’s possible that community support is the most important factor in deciding what software is broadly adopted.

That definitely seems to be the case with The Guardian’s decision:

Another great thing about Postgres is how mature it is: every question we wanted to ask had in most cases already been answered on Stack Overflow. — Philip McMahon et al. in The Guardian

Clearly, using a well-known and common SQL server was an advantage.

How The Guardian Made the Switch

The developers needed a way to talk to the new PostgreSQL database, so the first step was making a PostgreSQL API.

They needed to duplicate the old MongoDB API feature-for-feature:

We used doobie, a pure functional JDBC layer for Scala, added Docker for running locally and testing, and improved logging and separation of concerns. . — Philip McMahon et al. in The Guardian

By the end of August 2017 they had a fast, modern API deployed that was using PostgreSQL as its database, but it would be 10 months before they switched the production servers to PostgreSQL.

Under the Hood at The Guardian

At The Guardian, all of the articles live in The Guardian’s Content API (CAPI), which powers the apps and website.

Meanwhile, The Guardian’s in-house CMS, Composer, is built on a web stack of Scala, Scalatra Framework and Angular.js dating from 2014.

If anything were to happen to the CAPI’s Elasticsearch cluster then we would reindex it from Composer’s database. — Philip McMahon et al. in The Guardian

In other words, the developers just had to migrate one database.

Migration Magic

The migration process went smoothly with the Scala sbt tool:

[W]e used an sbt project instead to perform the migration. The approach we took allowed us to work in a language we were confident in and perform multiple ‘test migrations’ until we were confident to run it in production. — Philip McMahon et al. in The Guardian

In late 2017, the developers were working frantically to test the new API accessing the PostgreSQL database as well as migrate the data.

But trying to test the migrated server before launching to production created its own set of problems: short outages associated with proxy problems.

Pulling the plug

Finally, in April 2018, it was time to turn off the proxy and launch the newly migrated PostgreSQL database to the world:

The team gathered around one computer. The switch was one click away. No one was breathing anymore. Complete silence. Click! And the change was out. Nothing broke! We all relaxed. — Philip McMahon et al. in The Guardian

Impressively, over 2.3m articles had been moved from MongoDB to PostgreSQL with zero downtime — and no one noticed until the article describing the process was published in November 2018.

Would It Have Been Different Today?

The killer feature that led to the PostgreSQL decision was the lack of DynamoDB supporting encryption at rest. This leads to the question:

What would have happened if the project had been delayed until 2019?

DynamoDB Now Offers Encryption at Rest

As of September 2019, DynamoDB defaults to encryption at rest.

The encryption at rest feature was necessary for The Guardian to secure its data from unauthorized access to the underlying storage.

But waiting for DynamoDB to add it would have meant another year of hosting content on a flaky server with an expensive support contract.

Clearly, hour-long database outages are a strong motivator to switch.

Is It Too Late to Switch Back?

The developers who made the switch had hope that the additional features of PostgreSQL as a relational database might one day be useful:

In addition, if we wanted to move to a more relational model in future we’d have that option. — Philip McMahon et al. in The Guardian

Given the type of content published on The Guardian — documents — it is likely that The Guardian’s database will stay in a document store format.

That fact, and the use of the JSON blob data type mean the option is always there to switch back from PostgreSQL to a NoSQL document database.

However, switching back would be rewriting the NoSQL API from scratch, after engineers just spent nearly a year writing the brand-new SQL API.

This would likely be a deal-breaker since there are not any clear advantages to switching back at the current time.

A Fateful Decision

The twists of fate of the hottest July day on record in London and the delayed launch of encryption at rest in DynamoDB mean that The Guardian’s content is now hosted on a PostgreSQL database.

Thanks to the developers detailing their journey in The Guardian, we have had a look into why the publication gave up on their local servers in London and why that move led to a migration from NoSQL to a SQL database.

Through the use of the JSON blob column type, the developers were able to quickly move from MongoDB to PostgreSQL with zero downtime.

But, things would have been different when considering a similar move today — because DynamoDB on AWS now supports encryption at rest.

If developers were considering the change now, they would likely choose to stay on NoSQL, resulting in a much quicker transition.

Further Reading

• Shippable migrated from MongoDB to PostgreSQL in November 2017:

• Ajay Kulkarni discusses why SQL is beating NoSQL in a July 2019 post:

• Rick Negrin thinks NoSQL is just not the right tool in a July 2018 post:

• Judah Himango discusses moving to NoSQL in a September 2017 post:

• This 2018 question on StackOverflow asked by a former developer at The Guardian, Maria-Livia, who co-authored The Guardian article about the switch, shows how PostgreSQL works with the jsonb data type:

• Reuven Lerner hinted about using PostgreSQL as a document database using jsonb while writing in Linux Journal back in January 2015:

• Olery also migrated from MongoDB to PostgreSQL back in 2015:

Dr. Derek Austin is the author of Career Programming: How You Can Become a Successful 6-Figure Programmer in 6 Months, now available on Amazon.