この実験 (Lab) では、PostgreSQL データベースの必須のメンテナンス作業について学習します。データベースオブジェクトのサイズを監視する方法、パフォーマンスを最適化するための定期的なメンテナンスの実行方法、そして接続やログを確認して潜在的な問題をトラブルシューティングする方法を理解することを目的とします。
まず、PostgreSQL データベースに接続し、テーブルとインデックスのサイズをクエリすることから始めます。次に、データベース統計情報を更新するための ANALYZE の実行方法と、ストレージスペースを再利用するための VACUUM の実行方法を学びます。最後に、アクティブなクライアント接続を一覧表示する方法と、エラーがないかサーバーログを確認してデータベースの問題を診断および解決する方法について説明します。
このステップでは、PostgreSQL データベースに接続し、テーブルとそのインデックスのサイズをクエリします。データベースオブジェクトのサイズを理解することは、パフォーマンスチューニングとキャパシティプランニングにとって非常に重要です。
まず、ターミナルを開きます。postgres ユーザーとして PostgreSQL サーバーに接続し、mydatabase データベースにアクセスするには、次のコマンドを実行します。
sudo -u postgres psql mydatabasepsql プロンプト (mydatabase=#) が表示され、mydatabase データベースに接続されていることが示されます。この実験 (Lab) での以降のすべての SQL コマンドは、特に指定がない限り、この psql シェルで実行してください。
次に、mytable のサイズを調べます。pg_size_pretty 関数と pg_relation_size 関数を使用します。pg_relation_size はテーブルのサイズをバイト単位で返し、pg_size_pretty はそれを人間が読める形式（例：KB、MB）にフォーマットします。
mytable のサイズを取得するには、次の SQL クエリを実行します。
SELECT pg_size_pretty(pg_relation_size('mytable'));テーブルのデータが使用しているディスクスペースを示す、次のような出力が表示されるはずです。
pg_size_pretty
----------------
56 kB
(1 row)次に、name 列に作成されたインデックス idx_mytable_name のサイズを確認します。
SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));出力は、インデックスが消費しているスペースを示します。
pg_size_pretty
----------------
48 kB
(1 row)最後に、すべてのインデックスを含むテーブルの合計サイズを取得するには、pg_total_relation_size 関数を使用します。
SELECT pg_size_pretty(pg_total_relation_size('mytable'));この出力は、テーブルとそのインデックスの合計サイズを示します。
pg_size_pretty
----------------
176 kB
(1 row)このステップでは、良好なクエリパフォーマンスを維持するために不可欠な ANALYZE コマンドについて学習します。
ANALYZE の理解
ANALYZE コマンドは、データベース内のテーブルの内容に関する統計情報を収集します。PostgreSQL のクエリプランナーは、これらの統計情報を使用して、クエリの最も効率的な実行プランを選択します。正確な統計情報がないと、プランナーは不適切な選択をしてしまい、クエリパフォーマンスの低下につながる可能性があります。特にテーブルデータに大きな変更があった後は、定期的に ANALYZE を実行することが良い習慣です。
まだ psql シェル内にいる状態で、mytable テーブルに対して ANALYZE を実行します。
ANALYZE mytable;このコマンドは mytable を分析し、その統計情報を更新します。コマンドが成功したことを確認する次の出力が表示されます。
ANALYZEコマンドは単純に ANALYZE を返しますが、バックグラウンドで mytable の内部統計情報を更新しています。
このステップでは、もう一つの重要なメンテナンスタスクであるストレージの回収を行うために VACUUM コマンドを使用します。
VACUUM の理解
PostgreSQL では、行が更新または削除された場合、行の古いバージョン（「デッドタプル」）はディスクから直ちに削除されません。VACUUM は、これらのデッドタプルが占有しているストレージを回収し、そのスペースを再利用可能にします。また、データの可視性情報も更新し、クエリパフォーマンスの向上に役立ちます。
psql シェル内で、mytable テーブルに対して VACUUM を実行しましょう。
VACUUM mytable;このコマンドはテーブルを処理し、確認メッセージを返します。
VACUUMVACUUM と ANALYZE を単一の効率的なコマンドに組み合わせることもできます。これは一般的なメンテナンスプラクティスです。
VACUUM ANALYZE mytable;このコマンドは、まずストレージを回収し、次にテーブルの統計情報を更新します。完了すると VACUUM を返します。
注意： VACUUM FULL というコマンドもあります。これはより積極的にスペースを回収し、ディスク上のファイルを縮小します。しかし、テーブル全体をロックするため、操作中は読み書きができなくなります。そのため、注意して、必要な場合にのみ使用する必要があります。
この最終ステップでは、アクティブなデータベース接続を監視する方法と、エラーがないかサーバーログを確認する方法を学習します。これらはトラブルシューティングの鍵となるスキルです。
まず、psql シェル内にいる間に、pg_stat_activity ビューをクエリすることで、サーバーへのすべてのアクティブな接続を確認できます。
以下の SQL クエリを実行します。
SELECT datname, usename, client_addr, state FROM pg_stat_activity;このクエリは、データベース名 (datname)、ユーザー名 (usename)、クライアントの IP アドレス (client_addr)、および接続の現在の状態 (state) を表示します。出力には、以下のような自身の接続が表示されます。
datname | usename | client_addr | state
-----------+----------+-------------+--------
mydatabase| postgres | | active
postgres | postgres | |
| labex | |
(3 rows)次に、サーバーログを確認します。これを行うには、まず psql シェルを終了する必要があります。\q と入力して Enter キーを押します。
\qこれで、標準の Linux ターミナルプロンプトに戻りました。PostgreSQL のログは /var/log/postgresql/ ディレクトリにあります。このシステムでは、ログファイルは postgresql-14-main.log です。
grep コマンドを使用して、ログファイル内の "ERROR" を含む行を検索します。
grep ERROR /var/log/postgresql/postgresql-14-main.logエラーがない場合、このコマンドは何も出力しません。この実験では何もエラーを引き起こすような操作を行っていないため、これは期待される結果です。サーバーログの確認は、データベースの問題を診断するための基本的なステップです。
この実験では、PostgreSQL データベースの基本的なメンテナンスタスクを実行する方法を学びました。テーブルとインデックスのサイズを監視する方法、クエリパフォーマンスを最適化するために ANALYZE を実行する方法、そしてストレージスペースを回収するために VACUUM を使用する方法を理解しました。また、アクティブなクライアント接続を一覧表示し、エラーがないかサーバーログを確認する練習もしました。これらのスキルは、PostgreSQL データベースの健全性、パフォーマンス、および信頼性を確保するために不可欠です。
