Goodbye MongoDB, Hello PostgreSQL

jieforest

Migrating a Subset

Before we would even consider migrating all our data we needed to run tests using a small subset of the final data. There’s no point in migrating if you know that even a small chunk of data is going to give you lots of trouble.

While there are existing tools that can handle this we also had to transform some data (e.g. fields being renamed, types being different, etc) and as such had to write our own tools for this. These tools were mostly one-off Ruby scripts that each performed specific tasks such as moving over reviews, cleaning up encodings, correcting primary key sequences and so on.

The initial testing phase didn’t reveal any problems that might block the migration process, although there were some problems with some parts of our data. For example, certain user submitted content wasn’t always encoded correctly and as a result couldn’t be imported without being cleaned up first. Another interesting change that was required was changing the language names of reviews from their full names (“dutch”, “english”, etc) to language codes as our new sentiment analysis stack uses language codes instead of full names.

jieforest

Updating Applications

By far most time was spent in updating applications, especially those that relied heavily on MongoDB’s aggregation framework. Throw in a few legacy Rails applications with low test coverage and you have yourself a few weeks worth of work. The process of updating these applications was basically as following:

1. Replace MongoDB driver/model setup code with PostgreSQL related code

2. Run tests

3. Fix a few tests

4. Run tests again, rinse and repeat until all tests pass

jieforest

For non Rails applications we settled on using Sequel while we stuck with ActiveRecord for our Rails applications (at least for now). Sequel is a wonderful database toolkit, supporting most (if not all) PostgreSQL specific features that we might want to use. Its query building DSL is also much more powerful compared to ActiveRecord, although it can be a bit verbose at times.

As an example, say you want to calculate how many users use a certain locale along with the percentage of every locale (relative to the entire set). In plain SQL such a query could look like the following:

1. SELECT locale,
   
2. count(*) AS amount,
   
3. (count(*) / sum(count(*)) OVER ()) * 100.0 AS percentage
   
4. 
   
5. FROM users
   
6. 
   
7. GROUP BY locale
   
8. ORDER BY percentage DESC;
   

复制代码

jieforest

In our case this would produce the following output (when using the PostgreSQL commandline interface):

1. locale | amount |        percentage
   
2. --------+--------+--------------------------
   
3. en     |   2779 | 85.193133047210300429000
   
4. nl     |    386 | 11.833231146535867566000
   
5. it     |     40 |  1.226241569589209074000
   
6. de     |     25 |  0.766400980993255671000
   
7. ru     |     17 |  0.521152667075413857000
   
8.         |      7 |  0.214592274678111588000
   
9. fr     |      4 |  0.122624156958920907000
   
10. ja     |      1 |  0.030656039239730227000
    
11. ar-AE  |      1 |  0.030656039239730227000
    
12. eng    |      1 |  0.030656039239730227000
    
13. zh-CN  |      1 |  0.030656039239730227000
    
14. (11 rows)
    

复制代码

jieforest

Sequel allows you to write the above query using plain Ruby without the need of string fragments (as ActiveRecord often requires):

1. star = Sequel.lit('*')
   
2. 
   
3. User.select(:locale)
   
4.     .select_append { count(star).as(:amount) }
   
5.     .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percentage) }
   
6.     .group(:locale)
   
7.     .order(Sequel.desc(:percentage))
   

复制代码