MySQLからPostgresの移行時にやったこと(AWS Aurora)

はじめに

今携わっているWebアプリケーションではMySQL(AWS Aurora)を使用していますが、昨今のユーザー数増加により少しずつ問題が起き始めています。
元々Indexの作りがよくないこともあるのですが、PostgreSQL(AWS Aurora)に移行したらどうだろう？という検証を行った結果、数倍どころの問題じゃないぐらいの性能差が出たため、移行作業を行うこととしました。
今回はその際にやったことをまとめてみました。

環境

移行前

AWS Aurora MySQL 5.7

移行後

AWS Aurora PostgreSQL 11.8

DBの移行方法

AWS DMSという移行を簡単にできるサービスがあるため、それを使用して移行しました。
一度きりの移行も継続的なレプリケーションも対応しているため、移行がものすごく楽になりました。

移行時にテストしてクエリを修正した際のポイント

MySQLとPostgreSQLではクエリが少し変わります。ここではその違いによって変更した点を記載していきます。（これがメインコンテンツ）

シングルクォーテーションとダブルクォーテーション

文字列を囲む際ですが、MySQLは特にシングルとダブルどちらでも問題ありません。
しかし、PostgreSQLはシングルクォーテーションしか受け付けません。
なので、その違いがあれば吸収してあげる必要があります。
こういう時のためにシングルクォーテーションに統一しておくというのはアリかと思います。検索でどうこう出来る修正ではないため、結構修正は面倒くさかったです。

# MYSQL
name = "hogehoge"

# PostgreSQL
name = 'hogehoge'

関数の違い

例えばMySQLのifnullはPostgreSQLではCOALESCEといった違いがそこそこあります。
これも1つ1つ調べるのも手間なので、動かしてエラーになった箇所を書き換えるというやり方で修正しました。
また、同じ関数があった場合でも引数の型の問題でエラーになることもあります。
例えばlpad関数は、MySQLでは数字でも問題なく動作してくれますが、PostGreSQLでは文字を渡して上げなければいけないといった違いが存在します。
分かりづらいですが、このような違いによるエラーはかなり発生しやすいのと普段から両方に対応した書き方はしづらい箇所なので、移行時の手間として許容するしかなさそうです。こういう処理を全てアプリケーション側でやれば移行時の手間は無くなりますが、それはそれで手間です・・・

# MySQL
lpad(id, 4, "0")

# PostGreSQL
lpad(cast(id as character varying), 4, '0')

日付の扱い方について

一番大きく修正したのは日付（正確には時刻）周りです。
ここら辺はMySQLの方が便利になっていて、例えば日付の差分を取得する方法は以下となります。

# MySQL
DATEDIFF(NOW(), created_at)

# PostGreSQL
extract(epoch from (now() - created_at)) / (60 * 60 * 24)

また、時刻に秒とか分を足すというロジックにも違いがあります。
例えば秒を足すというロジックには以下の違いがあります。

# MySQL
DATE_ADD(time, INTERVAL value SECOND)

# PostGreSQL
time + make_interval(secs => value)

こうした違いはどうしても存在するため、これも手間として許容する必要があります。

group byの違い

MySQLは結果に一意性があれば良いのですが、PostGreSQLはクエリの段階で一意性を求めてくるようです。
なので、sumだったりで集計する箇所かつjoin絡むような少し複雑なクエリを書いている箇所でgroup byするカラム不足であるエラーが多発しました。
単純なクエリであれば特に問題ありませんが、joinが絡むとこの問題が多発します。

例としてhogehogeとmembersというテーブルをjoinしてデータを取得したいとします。
その際のクエリを書きましたが、このクエリはMySQLだと動作しますが、PostGreSQLではエラーになってしまいます。
PostGreSQLでは、このエラーとなったmembers.nameカラムもgroup byに含める必要があるのです。

# MySQL
select
  hogehoge.member_id,
  members.name,
  hogehoge.date,
  sum(hogehoge.value)
from
  hogehoge
  INNER JOIN members on members.id = hogehoge.member_id
group by hogehoge.member_id, hogehoge.date

# エラー内容
ERROR:  column "members.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:   members.name,
          ^
SQL state: 42803
Character: 47

# PostGreSQL
select
  hogehoge.member_id,
  members.name,
  hogehoge.date,
  sum(hogehoge.value)
from
  hogehoge
  INNER JOIN members on members.id = hogehoge.member_id
group by hogehoge.member_id, hogehoge.date

Railsの補足

Railsでfirstでデータを取得するとクエリにorder by {primary key}が付与されます。
これを今回のようなケースで書いているとこの{primary key}もgroup byに含める必要が出てくるため、自分で書いたクエリ部分は追加したけどエラーになるというような状況が出てきてしまいます。
ご注意ください。

selectの返却順

PostgreSQLはMySQLと違って計算過程で見つけた順番でselectした結果を返却してくるため、思ってない順番で返却されることがあります。
まとめて取得するというような場合はきちんとsortしてあげるようにしないと、思った通りの取得順番にはならないため注意が必要です。

終わりに

MySQL5.7のスタートってもう7年前なんですね・・・