PostgreSQLをWindows環境からLinux環境に移行する際に注意したい照合順序の問題

CX事業本部@大阪の岩田です。

ライセンスコストの削減等を目的にPostgreSQL on Windows Serverの環境からEC2やRDSの環境に移行するようなユースケースではPostgreSQLのlc_xxx系のパラメータに注意が必要です。 日本語環境のWindowsに「次へ」、「次へ」で標準インストールしたPostgreSQLはlc_xxx系のパラメータがJapanese_Japan.932でデータベースクラスタが構築されます。このJapanese_Japan.932というパラメータはWindows上にPostgreSQLをインストールした場合しか指定できないため、Linux環境ではlc_xxx系のパラメータに何を設定するべきか検討する必要があります。

このブログではlc_collateの指定によるソート順の違いと、ICUサポートを利用した場合のソート順についてご紹介します。

環境

今回検証に利用した環境です。

  • Windows環境
    • Windows_Server-2016-Japanese-Full-Base-2019.08.16 (ami-09c688c3fdee6f78f)
    • PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit
  • RDS環境
    • PostgreSQL 11.4-R1

PostgreSQLへの接続とSQLの発行はpsqlを利用しました。クライアント側のエンコーディングはUTF-8を、データベース側のエンコーディングも全てUTF-8を利用しています。

PostgreSQL on Windowsの場合

まずWindows環境で試してみます。

CREATE DATABASE cp932;
CREATE TABLE t (t text);

テーブルを作成し、テストデータを投入します。投入用のSQLはこちらのブログから拝借しました。

PostgreSQL 10のICUコレーションを使うと日本語を普通にソートでき、更に文字順序までカスタマイズできる

INSERT INTO t (t) VALUES 
('う'), ('ウ'), ('C'), ('かさ'), ('3'), ('c'), ('イ'), ('がく'), ('C'), ('ウ'),  
('1'), ('ア'), ('b'), ('2'), ('B'), ('B'), ('1'), ('A'),('い'),('ア'), 
('か'), ('A'), ('a'), ('AbC'), ('aBc');

ソートしてみます

cp932=> select * from t order by t ;
  t
------
 1
 1
 2
 3
 a
 A
 A
 aBc
 AbC
 b
 B
 B
 c
 C
 C
 ア
 ア
 イ
 い
 ウ
 ウ
 う
 か
 がく
 かさ
(25 rows)

RDS環境の場合

続いてRDS環境でも試してみます。日本語の環境ではlc_collateにはCもしくはja_JP.UTF-8を指定するケースが多いと思うので、この2パターンとICUサポートを利用するパターンの合計3パターンで試してみます。テーブルの作成やテストデータの投入はWindows環境と同様の手順となります。

なお、RDS環境でlc_collateを指定する方法はこちらのブログをご参照ください。

RDS for PostgreSQLのロケールをCにする

lc_collate=Cの場合

lc_collateがC(no-localeも同様)のDBを作って、Windows環境と同様のSQLを実行してみます。

no_locale=> select * from t order by t ;
  t
------
 1
 2
 A
 AbC
 B
 C
 a
 aBc
 b
 c
 い
 う
 か
 かさ
 がく
 ア
 ウ
 1
 3
 A
 B
 C
 ア
 イ
 ウ
(25 rows)

うーん。。。

Windows環境とのdiffです

2d1
< 1
4,5d2
< 3
< a
7,8d3
< A
< aBc
10d4
< b
12,13d5
< B
< c
15,18c7,10
< C
< ア
< ア
< イ
---
> a
> aBc
> b
> c
20,21d11
< ウ
< ウ
24d13
< がく
25a15,25
> がく
> ア
> ウ
> 1
> 3
> A
> B
> C
> ア
> イ
> ウ

ガッツリ差分が出ています。

ja_JP.UTF-8の場合

続いてlc_collateja_JP.UTF-8を指定した場合です。

ja_jp_utf8=> select * from t order by t;
  t
------
 1
 2
 A
 AbC
 B
 C
 a
 aBc
 b
 c
 ア
 イ
 ウ
 1
 3
 A
 B
 C
 い
 う
 か
 かさ
 がく
 ア
 ウ

Windows環境とのdiffです

2d1
< 1
4,5d2
< 3
< a
7,8d3
< A
< aBc
10d4
< b
12,13d5
< B
< c
15c7,10
< C
---
> a
> aBc
> b
> c
17d11
< ア
19d12
< い
21c14,19
< ウ
---
> 1
> 3
> A
> B
> C
> い
24d21
< がく
25a23,25
> がく
> ア
> ウ

ほんの少しだけ差分が減りました。

ICUサポートを利用した場合

RDS環境ではICUサポートが利用可能です。 ICUライブラリを利用してORDER BY句の指定にja-x-icuの指定を追加してみましょう。※この場合はデータベースのlc_collateはソート順に関係ありません。

 ja_jp_utf8=> select * from t order by t COLLATE "ja-x-icu";
  t
------
 1
 1
 2
 3
 a
 A
 A
 aBc
 AbC
 b
 B
 B
 c
 C
 C
 ア
 ア
 い
 イ
 う
 ウ
 ウ
 か
 がく
 かさ
(25 rows)

Windows環境とのdiffです

16d15
< ア
18c17
< イ
---
> ア
20,21c19
< ウ
< ウ
---
> イ
22a21,22
> ウ
> ウ
25c25
< かさ
---
> かさ

大分Windows環境に近くなりました! あとは半角カナが全角カナより先に来てくれれば、、、というところです。 CREATE COLLATIONを使えば独自の照合順序オブジェクトを作成できるので、頑張ればWindows環境と完全に同一な日本語ソートを実現できるのかもしれませんが、ICUのサイトを見ても半角カナと全角カナのソート順を制御するようなScriptが見つからなかったので今回はあきらめました。

International Components for Unicode

まとめ

Linux環境のPostgreSQLでWindows環境と完全に同一な日本語ソートを実現するのは不可能もしくはかなりハードルが高そうなので、Windows環境からLinux環境への移行を検討する際は

  • 日本語のソート順が変わっても問題ないか?
  • どこまでWindows環境に近い日本語ソートを目標にするか?

といった観点も含めて検討頂ければと思います。

また、PostgreSQL11の時点でもデータベース作成時にはICUロケールを指定できず、テーブル作成時もしくはSQL実行時に指定する必要があります。 ICUサポートを利用する場合は、少なからずアプリ側にも影響があるのでその点も注意が必要です。

参考

こちらのブログを参考にさせて頂きました。ありがとうございます。

関連するPostgreSQL11のドキュメントはこちらです