PostgreSQL の拡張モジュールなどの周辺ツールをご紹介
本エントリは、PostgreSQL Advent Calendar 2022 の14日目の記事です。
昨日は、@yohsita_ さんによるこちらの記事でした。
はじめに
PostgreSQLは CREATE EXTENSION コマンドでモジュールを追加することより、色々な機能拡張が可能です。モジュールの大まかな分類として、PostgreSQL 本体のソースコードに含まれるか含まれないか、があります。PostgreSQL本体に含まるモジュールは contrib ディレクトリの下にあるため contrib と呼ばれており、contrib のrpmファイルをインストールすることで付録F 追加で提供されるモジュールを追加して拡張可能となります。
PostgreSQL本体のソースコードに含まれるcontrib モジュール以外にも、多種多様なモジュールがあちこちのサイトからダウンロードして利用可能ですが、常にメンテナンスされているモジュールもあれば、長い期間メンテナンスされていないモジュールもあり玉石混交です。また、個人的な好みや判断に依存しますが、contribモジュールでもよく利用されると考えられるものはそんなに多くありません。
そこで、本エントリでは、最低限 PostgreSQL 14 に対応しているモジュールの中から主に性能問題が発生した時に役に立ちそうと考えているモジュールをピックアップしてご紹介します。その後、モジュール以外もご紹介します。
contribのお勧め拡張モジュール
auto_explain
実行時間がかかるSQL文を PostgreSQL ログに出力するパラメータは PostgreSQL 本体でも log_min_duration_statement パラメータがあり、一定時間以上かかったSQL文が出力されますが、その時の実行計画もあわせて PostgreSQLログに出力してくれるモジュールが auto_explain モジュールです。
最低限設定が必要なパラメータは auto_explain.log_min_duration パラメータで、これは log_min_duration_statement の auto_explain 版です。
ストアドプロシージャを使っている場合のお勧めはauto_explain.log_nested_statements パラメータで、有効にすればストアドプロシージャので内部でネストした文の実行計画もあわせて出力されます。
実行計画そのものが悪い場合は上記の情報でよいと思いますが、実行計画と実際の実行時間がかけ離れている場合は、性能への影響を考慮しつつ auto_explain.log_analyze パラメータを有効にすることをご検討ください。
pg_stat_statements
pg_stat_statements モジュールは、PostgreSQLのSQL文のプラン作成時と実行時の統計情報を記録します。単体で導入される場合ももちろんありますが、後述の pg_statsinfo モジュールを使う時に(これまた後述の pg_store_plans モジュール)と合わせて導入される場合が多く、本モジュールで収集したデータを pg_stasinfo で集めて蓄積して、蓄積したデータを pg_stats_reporter で見るという流れで使用されます。
postgresql_fdw
postgresql_fdw モジュールは、外部(他)のPostgreSQLデータベースに接続する機能を持ちます。末尾の FDW は Foreign Data Wrapper の略で他にも種々の FDW があり、有名どころでは Oracle DB に接続する oracle_fdw モジュールがあります(会社の技術ブログ)。
am_check
am_checkモジュールは、B-Treeインデックスやヒープ(テーブル)の構造の一貫性を検査する、別の言い方をすると内容(データ)が壊れていないかどうかをチェックする機能を持ちます。PostgreSQL14からpg_amcheckコマンドが利用可能となったのでコマンドラインから簡単に実行できるようになりました。データベースが壊れていないかどうかのチェックに今後使われていくと思います。
contrib以外のお勧め拡張モジュール
pg_statsinfo
pg_stasinfo は、性能情報を収集する pg_statsinfo モジュールと、収集した情報をWebブラウザでGUIで表示する pg_stats_reporter から構成されています。Oracle DB で雑に例えると statspack / AWR と OEM みたいなものです(例えが古い?)。NTT OSS センタのDBMS開発サポートチームが開発しているソフトウェアで、本カテゴリ(contrib以外のお勧めモジュール)のほとんどがこの組織が開発しているソフトウェアです。
最近の流れとして、本ソフトウェアもPostgreSQL 13 までSourceforge で配布していましたが、PostgreSQL 14 からGitHub に移行しています。マニュアルは13のpg_statsinfoとpg_stats_reporterが見やすいです。
pg_statsinfo を導入した環境では継続的に性能情報を収集してくれているので、性能が悪化した時など後から調査できる情報が残っているのはサポートとして特にありがたいと思っています。そうそう、少し古いバージョンが対象(PostgreSQL13)ですが、会社の技術ブログでも紹介しています。
pg_hint_plan
pg_hint_plan モジュールは、ヒント句を指定することでSQL文の実行計画を固定して、実行計画が変化することによる性能悪化を防ぐ機能を有します。これも NTT OSSセンタの開発ソフトウェアです。こちらも PostgreSQL 13までOSDNで配布していましたが、現在はGitHubに移行しています。バージョンは古いですがマニュアルは pg_hint_plan 1.1.0 が見やすいです。
Oracle DBを使ってから PostgreSQL を使っているので、ヒント句で実行計画を望みのものに固定することは全く違和感がないのですが、突然性能が悪化して困った時にこの機能が使えるようになっているかどうかは大きいですので、是非初期導入時にこのモジュールも導入しておいてください。性能問題の調査対応として、auto_explain と pg_hint_plan がまず思いつきますのでお勧めです。そうそう、こちらもかなり古いバージョンが対象(PostgreSQL9.6)ですが、会社の技術ブログで紹介しています。
pg_store_plans
pg_store_plansモジュールは実行計画の統計情報を取得するモジュールで、前述のpg_stasinfo モジュールの導入とともに導入される場合が多いです。これもNTT OSSセンタの開発ソフトウェアです。pg_hint_plan のOSDNのサイトはPostgreSQL 14も配布されており、GitHubのサイトに移行したわけでもなさそうです。英語のドキュメントが見つかります。
pgaudit/pgauditlogtofile
pgAudit モジュールは、PostgreSQLで監査ログを PostgreSQlログに出力する機能、pgAudit Log to File モジュールは pgAudit で出力される監査ログをPostgreSQLログとは別の独立した監査ログファイルに出力する機能を提供します。監査ログを取る必要がある時にはこの二つを思い出していただければと思います。そうそう、会社の技術ブログで pgAudit と pgAudit Log to File を紹介しています。
pg_repack
pg_repack は、必要最小限のロックで、テーブルやインデックスの再編成をする機能を提供します。PostgreSQL はアーキテクチャとして VACUUM という定期的なメンテナンスが必要ですが、VACUUMを止めて定期的に pg_repack でテーブルやインデックスを再編成する運用をしているシステムもあります。CREATE EXTENSION でモジュールを導入して、pg_repack コマンドを使ってテーブルやインデックスを再編成します。そうそう、こちらも対象バージョンは古いですが、会社の技術ブログで紹介しています。
その他ソフトウェア(拡張モジュール以外)
pg_rman
pg_rmanは物理バックアップの容易な管理と差分バックアップが出来るソフトウェアで、超ざっくり言うと、Oracle RMAN のPostgreSQL版です。見やすいマニュアルは pg_rman 1.3 になります。
pg_basebackup を使ってバックアップは取得できますが、バックアップしたファイルを複数世代保管して古いバックアップファイルを削除するとか、データベースが壊れてリストア・リカバリが必要となり PITR (ポイントインタイムリカバリ)の実行が必要となった時に、PITRをきちんとそれなりに(かなり)面倒なので、お手軽にバックアップファイルの複数世代管理が出来てPITRが出来るツールは、バックアップ・リカバリの敷居が低くなって良いと思います。そうそう、こちらも対象バージョンは古いですが、会社の技術ブログで紹介しています。
pgAdmin4
pgAdmin4は、PostgreSQLのGUI管理ツールです。とても使いやすいツールだと思います。会社でPostgreSQLソフトウェアのリリース情報に携わっていますが、このソフトウェアの更新頻度は半端なく一番で、月に1回更新しているのでは？というレベルです。そうそう、これは珍しく対象バージョンが新しく、会社の技術ブログで紹介しています。
終わりに
PostgreSQL本体に拡張機能として色々追加できる拡張モジュールと周辺ソフトウェアの主なものを一通り書いてみると、NTT OSSセンタの開発ソフトウェアの紹介とそれらのサイトがPostgreSQL14から徐々にGitHubに移行しているという状況、会社の技術ブログの紹介とそれらの対象バージョンが古いことをあらためて認識して更新せねばと思った記事となりました。今回紹介したソフトウェアのほとんどがよく使われていますし、今回紹介できなかった周辺ソフトウェア(pg_bigm, PostGIS, psqlODBC, TimescaleDBなど)は会社の技術ブログの下の方に「サードパーティーソフトウェア」として記事がありますので、とりあえず使ってみようと思った時にみていただければありがたいです。
