Goodbye MongoDB, Hello PostgreSQL: Olery’s Journey to a More Reliable Database Architecture

Olery, a five‑year‑old Ruby‑driven company, originally relied on a hybrid database setup—MySQL for core data and MongoDB for comments—supporting more than 25 Ruby web and background applications.

Operational issues surfaced as the platform grew, especially with MongoDB: massive document deletions caused prolonged locks, repair operations took hours, and performance degradations were hard to trace, often only resolved by replacing a primary node.

The lack of a fixed schema in MongoDB introduced implicit schema problems; example Ruby code that assumed a #post.title field failed for documents without that field, prompting the need for defensive checks or explicit schema definitions via tools like Mongoid.

Olery defined four core database requirements—consistency, visibility, correctness, and scalability—and evaluated MySQL and PostgreSQL against these criteria. MySQL suffered from silent type coercion warnings and table‑level locks during schema changes, while PostgreSQL enforced strict typing, offered non‑blocking schema alterations, and provided advanced features such as trigram indexes, full‑text search, JSON support, and publish/subscribe.

Choosing PostgreSQL, the team planned a three‑step migration: set up a PostgreSQL instance and migrate a data subset, update all applications to use PostgreSQL (replacing MongoDB drivers with PostgreSQL‑specific code), and finally move production data and cut over.

# Ruby example for slug generation post_slug = post.title.downcase.gsub(/\W+/, '-')

# Ruby conditional handling for missing title if post.title post_slug = post.title.downcase.gsub(/\W+/, '-') else # ...handle missing title... end

# MySQL demonstration of type coercion warnings create table example ( `number` int(11) not null ); insert into example (number) values ('wat'); -- warning, value coerced to 0

# PostgreSQL strict type enforcement (errors on invalid input) create table example ( number int not null ); insert into example (number) values ('wat'); -- ERROR

Data migration required custom Ruby scripts to rename fields, change types, and normalize language codes for sentiment analysis.

Application updates focused on replacing MongoDB models with PostgreSQL equivalents, using ActiveRecord for Rails apps and Sequel for non‑Rails services. Example queries:

# SQL query for locale distribution SELECT locale, count(*) AS amount, (count(*) / sum(count(*)) OVER ()) * 100.0 AS percentage FROM users GROUP BY locale ORDER BY percentage DESC;

# Equivalent Sequel (Ruby) query star = Sequel.lit('*') User.select(:locale) .select_append { count(star).as(:amount) } .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percentage) } .group(:locale) .order(Sequel.desc(:percentage))

After the migration (performed on January 21), response times for the hotel‑review API, the review‑persistence daemon, and the scraping scheduler dropped by roughly half, and overall system stability improved.

While a small feedback service still runs on MongoDB, Olery plans to eventually move it to PostgreSQL as well.