PostgreSQL データベースのメンテナンス

はじめに (Introduction)

この実験 (lab) では、PostgreSQL データベースの重要なメンテナンス作業について学びます。データベースオブジェクトのサイズを監視し、ルーチンメンテナンスを実行し、潜在的な問題をトラブルシューティングする方法を理解することが目標です。

まず、pg_size_pretty 関数と pg_relation_size 関数を使用して、テーブルとインデックスのサイズをクエリします。次に、クエリのパフォーマンスを最適化し、ストレージスペースを再利用するために、ANALYZE と VACUUM を実行する方法を学びます。最後に、アクティブなクライアント接続を一覧表示し、サーバーログを調べてエラーを確認し、データベースの問題を診断および解決する方法について説明します。

Skills Graph

PostgreSQL への接続とテーブルサイズのクエリ (Connect to PostgreSQL and Query Table Size)

このステップでは、PostgreSQL データベースに接続し、テーブルのサイズをクエリします。データベースオブジェクトのサイズを理解することは、パフォーマンスチューニングとキャパシティプランニングにとって非常に重要です。

まず、~/project ディレクトリでターミナルを開きます。

postgres ユーザーとして PostgreSQL サーバーに接続し、mydatabase データベースにアクセスするには、次のコマンドを実行します。

sudo -u postgres psql mydatabase

mydatabase=# のようなプロンプトが表示されるはずです。これは、mydatabase データベースに接続されていることを示します。

mydatabase データベースと mytable テーブルがない場合は、次の SQL コマンドを使用して作成します。

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    data TEXT
);

INSERT INTO mytable (name, data) SELECT 'Name ' || i, 'Data ' || i FROM generate_series(1, 1000) AS i;

次に、mytable のサイズを決定しましょう。pg_size_pretty 関数と pg_relation_size 関数を使用します。pg_relation_size 関数はテーブルのサイズをバイト単位で返し、pg_size_pretty はサイズを人間が読める形式 (例：KB、MB、GB) にフォーマットします。

次の SQL クエリを実行して、mytable のサイズを取得します。

SELECT pg_size_pretty(pg_relation_size('mytable'));

次のような出力が表示されるはずです。

 pg_size_pretty
------------------
 128 kB
(1 row)

これは、テーブル mytable が現在 128 KB のディスクスペースを占有していることを示しています。

インデックスサイズの確認 (Check Index Size)

このステップでは、インデックスを作成し、そのサイズをクエリします。インデックスはクエリのパフォーマンスを向上させるために使用されますが、ディスクスペースも消費します。インデックスを含むテーブルの合計サイズも確認します。

まず、mytable の name 列にインデックスを作成しましょう。

CREATE INDEX idx_mytable_name ON mytable (name);

次に、インデックスのサイズを確認しましょう。同じ関数を使用できますが、インデックス名を指定する必要があります。

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

出力は次のようになる可能性があります。

 pg_size_pretty
------------------
 48 kB
(1 row)

これは、インデックス idx_mytable_name が 48 KB のディスクスペースを占有していることを示しています。

最後に、インデックスを含むテーブルの合計サイズを取得しましょう。これには pg_total_relation_size 関数を使用できます。

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

出力は次のようになる可能性があります。

 pg_size_pretty
------------------
 176 kB
(1 row)

これは、すべてのインデックスを含むテーブル mytable の合計サイズが 176 KB であることを示しています。

メンテナンスのための ANALYZE と VACUUM の実行 (Run ANALYZE and VACUUM for Maintenance)

このステップでは、PostgreSQL の ANALYZE コマンドと VACUUM コマンドについて学習します。これらはデータベースのパフォーマンスを維持するために不可欠です。

ANALYZE は、クエリプランナが最も効率的な実行計画を選択するために使用するデータベース統計を更新します。これらの統計は、データベース内のテーブルの内容を記述します。正確な統計がないと、クエリプランナが不適切な選択をし、クエリのパフォーマンスが低下する可能性があります。

VACUUM は、不要になったタプル (dead tuple) によって占有されているストレージを再利用します。PostgreSQL では、行が更新または削除されると、行の古いバージョンはすぐには削除されません。代わりに、不要になったものとしてマークされます。VACUUM は、これらの不要になったタプルによって占有されているスペースを再利用し、再利用できるようにします。また、可視性マップ (visibility map) を更新し、クエリプランナがトランザクションにどの行が表示されるかを判断するのに役立ちます。

mytable テーブルで ANALYZE を実行してみましょう。

ANALYZE mytable;

このコマンドは mytable テーブルを分析し、統計を更新します。出力は表示されませんが、統計はバックグラウンドで更新されます。

次に、mytable テーブルで VACUUM を実行してみましょう。

VACUUM mytable;

このコマンドは、mytable テーブル内の不要になったタプルによって占有されているストレージを再利用します。繰り返しますが、出力は表示されませんが、バキューム処理はバックグラウンドで実行されます。

より積極的なクリーンアップのために、VACUUM FULL を使用できます。ただし、VACUUM FULL はテーブルを排他的にロックするため、バキューム処理中にテーブルに対して他の操作を実行できなくなります。VACUUM FULL を使用する特定の理由がない限り、通常は VACUUM を使用することをお勧めします。

-- VACUUM FULL mytable; -- VACUUM FULL を実行するには、この行のコメントを外してください (使用には注意が必要です)

最後に、ANALYZE と VACUUM を 1 つのコマンドに組み合わせることができます。

VACUUM ANALYZE mytable;

このコマンドは、最初に不要になったタプルによって占有されているストレージを再利用し、次に統計を更新します。これは、データベースのパフォーマンスを維持するための最も効率的な方法であることがよくあります。

アクティブなクライアント接続のリスト表示とサーバーログの確認 (List Active Client Connections and Check Server Logs)

このステップでは、アクティブなクライアント接続をリスト表示する方法と、エラーがないかサーバーログを確認する方法について説明します。

アクティブなクライアント接続をリスト表示するには、pg_stat_activity ビューをクエリします。このビューは、ユーザー、データベース、実行中のクエリなど、各サーバープロセスに関する情報を提供します。

次の SQL クエリを実行します。

SELECT datname, usename, client_addr, state, query FROM pg_stat_activity WHERE state != 'idle';

このクエリは、アイドル状態ではないすべてのアクティブな接続を表示します。出力には、データベース名 (datname)、ユーザー名 (usename)、クライアントアドレス (client_addr)、現在の状態 (state)、および実行中のクエリ (query) が含まれます。

次に、エラーがないかサーバーログを確認しましょう。PostgreSQL のログは通常、/var/log/postgresql ディレクトリにあります。ログファイル名は通常、postgresql-VERSION-main.log というパターンに従います。VERSION は PostgreSQL のバージョン番号です。

ログにエラーがないか確認するには、grep コマンドを使用できます。たとえば、ログファイルでエラーを検索するには、新しいターミナル (psql の外部) で次のコマンドを実行します。

grep ERROR /var/log/postgresql/postgresql-14-main.log

注： postgresql-14-main.log を実際の PostgreSQL ログファイル名に置き換えてください。/var/log/postgresql ディレクトリ内のファイルを一覧表示して、正しいログファイル名を見つけることができます。

このコマンドは、ログファイルに "ERROR" という単語が含まれている行を表示します。サーバーログの調査は、データベースの問題を診断および解決するために非常に重要です。

まとめ (Summary)

この実験 (lab) では、PostgreSQL データベースの基本的なメンテナンス作業を実行する方法を学びました。データベースオブジェクトのサイズを監視する方法、クエリのパフォーマンスを最適化し、ストレージスペースを再利用するために ANALYZE と VACUUM を実行する方法、アクティブなクライアント接続をリスト表示する方法、およびエラーがないかサーバーログを確認する方法を理解しました。これらのスキルは、PostgreSQL データベースの健全性とパフォーマンスを確保するために非常に重要です。