はじめに

エンジニアのsassaです。 MySQLからPostgreSQLへの移行を進める中で、enum型に起因する問題が浮き彫りとなりました。本ブログでは、移行時に直面した課題やその解決策を共有します。具体的には、enum型をvarchar型に変更することで問題を解消した経験についてお話しします。

直面した課題と原因

PostgreSQLでは異なるenum型カラムでのJOINができない

PostgreSQLに移行後、異なるenum型のカラムを持つテーブルをJOINする際にエラーが発生しました。

例えば、以下のような格納されている値が異なるenum型のカラムをそれぞれ持つテーブルAとテーブルBがあるとします。

// tableAのenum型
CREATE TYPE tableA_content_type AS ENUM ('typeA', 'typeB', 'typeC'); 
// tableAのテーブル定義
CREATE TABLE tableA (
  id bigserial NOT NULL,
  content_type tableA_content_type NOT NULL,
  created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
  updated_at timestamptz NULL
);

// tableBのenum型
CREATE TYPE tableB_content_type AS ENUM ('typeA', 'typeB'); 
// tableBのテーブル定義
CREATE TABLE tableB (
  id bigserial NOT NULL,
  content_type tableB_content_type NOT NULL,
  created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
  updated_at timestamptz NULL
);

テーブルAのenum型カラムとテーブルBのenum型カラムでJOINしようとすると、エラーが出ます。

SELECT *
FROM tableA
JOIN tableB
ON tableB.content_type = tableA.content_type;

ERROR:  operator does not exist: tableA_content_type = tableB_content_type
LINE 1: ...tableB on tableB.content_type = cd.conte...
                                                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

原因

PostgreSQLでは、異なるenum型の直接比較が許可されていないのが原因でした。MySQLではテーブル単位でenum型を定義できるのに対し、PostgreSQLではデータベーススキーマ全体で管理される型となるため、各テーブルで独立してenum型を定義していた構造が影響していました。

MySQLとPostgreSQLのenum型の違い

MySQLのenum型

• 内部的に整数インデックスとして保存される型
• テーブルごとに独立したenum型定義
• テーブル定義時に直接指定できる
• 柔軟性が高い
  • 他の型と比較可能
  • データがある状態で値の変更、削除が容易
  • ENUM型に新しい値を追加するのは高速

PostgreSQLのenum型

• 4バイトを占める独自の型として扱われる
• データベーススキーマ内で型として管理
• 事前に独立して型を作成する必要がある
• 柔軟性が低い
  • 同じenum型同士でしか比較できない
  • データがある状態で値の変更、削除ができない
  • ENUM型に新しい値を追加するとフルスキャンが走る

enum型からvarchar型への変更

PostgreSQLではenum型を使用せず、varchar型に置き換えることにしました。 varchar型の整合性が保てないデメリットに関して、アプリケーション側からの更新操作がないため、影響はほぼなかった。もしあったとしても、アプリケーション側で制約を与えることでカバーできる。また、enum型の値は短い文字列を使用していたため、インデックスサイズの増加も許容できた。

Postgresqlでのenum型とvarchar型の違い

enum型

• メリット
  • データの整合性を保てる
  • インデックスのサイズを小さくできる
• デメリット
  • 異なるenum型同士の比較ができない
  • 柔軟性が低く、変更が必要な場合に面倒

varchar型

• メリット
  • JOIN時に型を気にする必要がない。
  • 値の追加や変更が容易で柔軟性がある
• デメリット
  • データの整合性をDB側で保てない
  • インデックスサイズがenum型に比べると肥大化する

まとめ

今回はMySQLからPostgreSQLに移行する際、直面したenum型のJOIN制約をvarchar型に変更することで解決した話をしました。今後はDB間の互換性の低いenum型はできる限り避け、汎用的なデータ型や参照テーブルを活用し、柔軟なアプローチを取っていきたいと思います。