こんにちは！インサイトテクノロジーの松尾です。
本投稿では、標準サポート終了が2025年2月に迫る Aurora PostgreSQL 12 の EOL 対策として、SQL テストツール Insight SQL Testing を使ってバージョンアップ互換性検証テストを行う例をご紹介します。
延長サポート料金は1か月あたり約10万円 (8コアの場合)
ご存知のように延長サポートに自動で移行します。東京リージョンで「USD 0.12/VCPU-時間」となかなかいい金額がかかりますので、自社で運用しているクラスタのコア数を確認して計算しておきましょう。8コアだとだいたい 10万円/1か月 くらいでしょうか。Aurora を利用されている方は、もっと多くのコアを使っているケースも多々ありますよね。なお、これは1年目、2年目の場合の費用で、3年目はさらに倍となります。
AWS からも警告メールが来ていますが、標準サポートバージョンアップの期限は十分注意しておきましょう。
マネージド DB は塩漬けはだめよ
| Aurora 標準サポート終了日 | RDS 延長サポートの終了日 | |
|---|---|---|
| Aurora PostgreSQL 11 | 2024 年 2 月 29 日(既に終了) | 2027 年 3 月 31 日 |
| Aurora PostgreSQL 12 | 2025 年 2 月 28 日 | 2028 年 2 月 29 日 |
| Aurora PostgreSQL 13 | 2026 年 2 月 28 日 | 2029 年 2 月 28 日 |
マネージドDBの利便性や拡張性の享受、新機能への対応などを踏まえると、以前のようにDBバージョンを塩漬けにして利用し続けるというのは合わなくなってきています。そのため、今までより、より計画的にバージョンアップを行っていくことが重要であることは覚えておきましょう。
Aurora PostgreSQL 12 から最新の LTS に乗り換えるなら、移行先は今のところ Aurora PostgreSQL 14.6
次のバージョンは何に乗り換えればいいでしょうか？
最新バージョンであれば 16 が出ています。16 に乗り換えるのが危険だという話もあまり聞きませんので 15 や 16 をターゲットにされるケースも多いと思います。
一方 LTS (Long-term support) バージョンを利用するということをポリシーとしている場合、現時点では最新でも 14.6 になります。その場合は、次の標準サポート終了もさらにまた 2 年後にやってきそうであることは想像できますね。
DB バージョンアップでありがちな変更など
同じ DB エンジンのバージョンアップでは、Oracle から PostgreSQL へ変更するなどの DB 移行に比べれば、一般的にはそこまでクリティカルな非互換は発生しません。いっぽうで、大方は問題ないけれども、以下のような非互換が発生することにより、今まで動作してきた SQL が動かなくなったり、異なる結果を返すようなことがおこります。
- SQLの仕様変更
- 構文チェックが厳しくなる
- 予約語の追加
- SQL実行結果が変わる
- 演算の仕様変更
- 関数の仕様変更
- 関数内でのエラーチェックの厳密化
- 動作仕様の変更
- 性能の変化
- オプティマイザ仕様変更による性能変化
一般的なDBバージョンアップの確認手順
これらの変更が入ることを想定した場合に、一般的にはどのような手順で対応を行うでしょうか？
おそらく以下のような手順で確認と対処を行うと思います。
- DBのバージョンアップにおける非互換情報などをドキュメントで確認
- アプリのソースコードから修正が必要なコードを探して修正対応
- DBバージョンアップであれば通常はあまりクリティカルな変更はない
- DBバージョンアップ後のテストDBを用意
- テストシナリオに従って動作確認
- シナリオによっては膨大なテスト工数 😫
- 発行され得るSQLをどれだけ網羅できるか？
バージョンアップ前の確認作業は、ほとんど問題ないであろう中ごくわずかにあるかもしれない問題を探すつらい作業です。どこまで確認すればいいのか、の線引きもなかなか難しいと思います。
SQLテストで備えましょう♪
SQLテストとは、これまで実行されていたSQLが、これまでと同じことを確認するテストのことです。
極端な話、SQLの挙動がこれまでと全く同じであれば、DBを変更してもアプリ側には何も影響はないはずでう。
では、SQLテストをどう実現すればよいでしょうか？ステップに分解すると以下のようになると思います。
- “これまで運用してきたSQL” を集める
- テスト用DBを用意する
- 集めたSQLをテスト用DBに対して実行する
- 実行した結果を確認する
この各ステップを、Insight SQL Testing を使うと簡単に実施することができます。
Insight SQL Testingの機能
Insight SQL Testingは以下の機能で構成されます。
- 現行環境でアプリケーションが発行するSQLを自動収集
- 収集したSQLをテスト
- 実行可否
- 実行結果比較(Select、更新DML)
- 実行速度比較
- テスト結果の確認・評価
- サマリー画面
- 生成AIによるエラー説明・修正案提示
Insight SQL Testingの対応DB
Insight SQL Testingは以下の環境で使うことができます。
- SQL取得元
- Oracle Database, RDS for Oracle
- Microsoft SQL Server, RDS for SQL Server
- PostgreSQL, Aurora PostgreSQL,RDS for PostgreSQL
- MySQL, Aurora MySQL, RDS for MySQL
- SQL実行先
- Oracle Database (RDS, OCI, ..)
- Microsoft SQL Server (RDS, Azure SQL, …)
- PostgreSQL (RDS, Azure, YugabyteDB, …)
- MySQL (RDS, Azure, TiDB, …)
- Snowflake
が、人気のパターンは決まっており、以下でのお問合せや実際のご利用が多い感触(当社比)があります。
- Oracle → PostgreSQL
- MySQL バージョンアップ
- PostgreSQL バージョンアップ
- Oracle バージョンアップ
特に直近は、やはり Aurora MySQL のバージョンアップテストや、Aurora PostgreSQL のバージョンアップテストでのご利用やお問合せが多くなっています。
アセスメント実施例 Aurora PostgreSQL 12.9 → 14.6
では早速、Aurora PostgreSQL で LTS を利用し続けることを想定して 12.9 から 14.6 へバージョンアップを行う際のアセスメント(テスト)実施例をご紹介します。
まず、以下のような SQL があった場合に、問題になる SQL があるか、すぐわかりますか？
| SQL | 備考 |
|---|---|
| insert into bonus values ('a', 'b', 10, 10) | |
| insert into bonus values ('a', 'b', 10, 10) returning * | Insertにreturningを付けた例 |
| select extract (minute FROM '2021-10-05'::date) | |
| select variance('NaN'::NUMERIC) | |
| select current_timestamp | |
| select 3! | |
| select factorial(-1) | |
| select regexp_match(E'abc\ndef', '(\D+)', 'n') | |
| set "aaa.aaa!" to 1 | |
| show "aaa.aaa!" | |
| select * from emp |
PostgreSQL へ精通している方ならわかるかもしれませんが、なかなか普段使わない機能だったりすると、調べないとすぐわかりませんよね？
Insight SQL Testing をでアセスメントを実行すると以下のようにそれぞれの SQL 実行結果が分類されて表示されます。
- ターゲット DB のみで失敗：旧バージョンでは成功、新バージョンでは実行失敗となった SQL
- 結果が相違：両バージョンで SQL は実行できたものの SELECT で返す結果セットなど、挙動が異なったもの
- 性能が劣化：SQL の挙動に問題はなかったが、実行性能が劣化 (劣化とみなす基準は事前に指定) したもの
- 成功：SQL の挙動に問題はなく、性能の劣化もなかったもの
- 両 DB で失敗、テスト用ソース DB で失敗：何らかの理由で旧バージョンで SQL が失敗したもの
この結果から、新バージョンで失敗した SQL が 5 件、結果が相違となった SQL が 3 件あったことがわかります。
それぞれの SQL について見ていきます。
エラーとなった SQL
エラーとなったは以下でした。これらは Aurora PostgreSQL 12 では実行できる SQL が Aurora PostgreSQL 14 ではエラーとなったことを示しています。
さらに個々の SQL をドリルダウンすると詳細を見ていくことができます。
エラー：select extract (minute FROM '2021-10-05'::date)
右上に出ている date units "minute" not supported は実際に PostgreSQL が返したエラーメッセージです。ドキュメント( https://www.sraoss.co.jp/tech-blog/pgsql/14-0/ )を確認すると以下のような記載を確認できます。
EXTRACT(date) は date型に含まれない部分を要求したときに、以下のようにエラーを投げるようになりました。
なかなか、この記述をドキュメントから探すのも大変かもしれません。
なお、右上に表示されている「エラー分析」ボタンをクリックすることで、生成 AI を利用してエラーの説明や SQL の修正案を確認することができます。
生成 AI なので必ず正しい SQL を返すことを保証はしませんが、かなり有益な参考情報を得られるでしょう。試しに提案された SQL を実行すると、実際に SQL を実行可能なことが確認できました。
エラー：select 3!
syntax error です。Aurora PostgreSQL 14 では階乗の演算子が削除されているようです。
同じように生成 AI で確認してみます。
生成 AI では、factorial 関数を別途定義するようアドバイスしています。一方、Aurora PostgreSQL 14 には実際には factorial 関数は組み込み関数として存在していますので、単純に factorial 関数を使うように変更すれば問題ありません。
エラー：select factorial(-1)
factorial 関数に負の値を入れても実行できていたものがエラーになるように挙動が変更となりました。サンプルのように直接負の値を設定することはあまりないと思いますが、何らかの演算結果を入れる場合には負の値が設定されているケースがあるかもしれません。これまではその結果により何かを判定しているものがあったとしても、SQL がエラーになるように挙動が変っているわけです。これをどう修正するかはアプリケーション次第でしょうか。
エラー：set "aaa.aaa!" to 1
値を設定するための変数名として「!」を使用することができなくなりました。これはそのように変更する必要がありそうですね。
エラー：show "aaa.aaa!"
前のエラーと同様に変数名として「!」を使用することができないのでそれに合わせて修正が必要になるでしょう。
結果の異なる SQL
結果の異なる SQL は3件ありました。current_timestamp は当然なので説明は割愛します。
結果の異なる SQL：select variance('NaN'::NUMERIC)
結果の異なる SQL については、どこが異なるかを確認することができます。
今まで NaN を返していたものが NULL を返すように挙動が変更となりました。これもドキュメントに記載があるものにはなりますが、ドキュメントを確認するのも大変ですよね、、、
結果の異なる SQL：select regexp_match(E'abc\ndef', '(\D+)', 'n')
正規表現はよく変更が入りがちなものですね。こちらもドキュメントには説明があります。
その他
更新系 DML の結果比較について
更新系の DML (insert, update, delete)については通常は更新行数のみの比較となりますが returning 句を使用して更新内容を返すようなクエリーにすると、select と同様に更新内容を比較することができます。
性能劣化時の確認ついて
大きな性能劣化があった場合、実行計画を確認することが多いと思います。Insight SQL Testing では実行計画の比較までは行いませんが、自動で取得を行うことができますので、確認作業を大幅に効率化できます。
さて、最初に挙げた SQL は以下のような結果となりました。予想できましたか？
| SQL | 備考 |
|---|---|
| insert into bonus values ('a', 'b', 10, 10) | 〇 |
| insert into bonus values ('a', 'b', 10, 10) returning * | 〇 |
| select extract (minute FROM '2021-10-05'::date) | 実行エラー：timestampに修正が必要 |
| select variance('NaN'::NUMERIC) | 結果相違：仕様変更 |
| select current_timestamp | (※当然結果は異なる) |
| select 3! | 実行エラー：factorial関数を使用する |
| select factorial(-1) | 実行エラー：無意味な計算 |
| select regexp_match(E'abc\ndef', '(\D+)', 'n') | 結果相違：修正が必要 |
| set "aaa.aaa!" to 1 | 実行エラー：パラメータ名に ! を使用不可 |
| show "aaa.aaa!" | 実行エラー：パラメータ名に ! を使用不可 |
| select * from emp | 〇 |
おわりに
あまり使わないような処理もあったかもしれませんが、Aurora PostgreSQL 12を14にバージョンアップするにあたっても、エラーになったり挙動が変わるSQLは存在することを確認できました。実際には、エンジニアがどのような SQL を使っているかを全て把握するのは無理なので、テストを行なわない訳にはいかないでしょう。
今回はサンプルでは、あえてエラーになる SQL をまぎれさせましたが、実際のアプリケーションやサービスでは、特にバージョンアップテストの場合は、バージョンアップ後もほとんどは問題のない SQL であることが多く、その中からごくわずかの問題となる SQL を探したり、もしくは、問題がないことを確認する、というケースが多いと思います。そのような作業を行うのは簡単ではなく骨の折れる作業です。SQL テストを活用すると、そのような確認作業を省力化することが可能です。そして Insight SQL Testing は SQL テストのための SQL の準備や実行、確認に便利な機能を備えているため、 SQL テストを効率的に実施することができます。
本投稿でご紹介した Insight SQL Testing にご興味がある方はぜひお問合せください。