Why I Had to Migrate from MongoDB to PostgreSQL


The story

A few months ago, I made WeWatch, a mobile app to help you find what films to watch. I made the decision to use MongoDB because of the popularity of the MERN stack. The idea of WeWatch is very simple:

Thanks to MongoDB, I was able to move really fast, even with no prior knowledge of document databases. I finished this app in just a few weeks. It wasn't perfect, but it did everything that was promised.

When the app was done, I started sharing it on social media. I went viral a few times and had a large influx of users (34,000 at the time of writing this). As you can expect, with new users comes new feature requests.

New Features... So far so good...

Some features weren't hard to add.

I want to be able to mark a movie as SEEN instead of LIKE or DISLIKE

Okay, I'll just add an array of movies to the user document called 'seen'

I want to be able to be able to decide on a movie LATER

Okay, I'll just add an array of movies to the user document called 'later'

These features were not difficult, albeit a bit of monotonous work to add to my API due to a lack of abstraction.

New Features are Getting Complicated

Things started getting a bit messy, but still manageable.

I want to be able to filter movies by genres and streaming services

Uhh, well, I can add an array of genres to each movie, and an array of preferred genres to each user, but maintaining data integrity is difficult with a document database. There are ways to do it, but not as easily as it would be with a relational database...

I want to see the cast for each movie

Uhh, well, I can add an array of people to each movie, but that will get inefficient because I will be repeating a lot of actors. I guess I will make a PERSON document and make a lookup to it. Huh... I'm essentially using a foreign key in a document database...

I want to be able to review a movie

I want to only see movies on a streaming service if it's in my country

I want to be able to mark a movie as seen, but also have it in my watchlist

I want to be able to swipe on tv shows

I want to see a feed of my friend's activities

As you can see, more complex relationships have emerged in my app. Users want more complex relationships with movies. They want to be able to LIKE, DISLIKE, SEEN, LATER, SEENBUTWATCHAGAIN, and I imagine new relationships will continue to emerge.

Movies also have many more relationships to keep track of than I initially anticipated. Its genres, streaming providers per country, its cast, etc..

I was able to make all this work with MongoDB, but the next stage of my app is what pushed me over the edge.

The Final Straw

I want to be able to swipe on restaurants, books, podcasts, and more!

Whoa... Well, that is the obvious direction that WeWatch should head towards. Why stop at movies? People need to come to a consensus on tons of things in life, and it's hard to keep track of everything you wanted to do with someone.

The architecture of my database at this point was just not going to cut it. I would need to add 'restaurant_likes', 'book_likes', 'restaurant_dislikes', 'book_dislikes', etc. Clearly, this couldn't scale well.

I should have given up here, but I took one last stab at using MongoDB for these emerging relationships. Instead of nested documents, I decided to try and make an abstract join table that will capture the relationship between a user and an item.

It made my life a lot easier in terms of the API, although I wish I could have abstracted the 'movie' lookup to something like 'object.'

But here's the problem... I am working against what MongoDB is optimized for. And it showed. With the same amount of data, just in this new format, the size of my database increased from 150mb to over 700mb!! I could no longer use MongoDB on the free tier!!

When I saw this, I knew it was time to make the switch to a relational database.

One Last Thing

MongoDB is great if you want to go fast; I recommend it if you are just playing around with an idea. But the moment you start working against the system, you need to change something about your approach. In my case, I changed the system.

Now, I have to learn about modeling polymorphic associations in PostgreSQL 😅

Thanks for reading!

I hope you learned something new, or just enjoyed reading :) If you liked this, you can follow me on twitter and keep up to date with all the stuff I'm learning!