【データベース管理者必見】PostgreSQL ストレージ容量を最適化する！pg_table_size、pg_relation_size、pg_total_relation_size を使いこなす

2024-05-20

PostgreSQL における pg_table_size、pg_relation_size、pg_total_relation_size の違い

pg_table_size:

この関数は、指定された表とその関連付けられたすべての TOAST テーブルの合計サイズを返します。つまり、データ、インデックス、TOAST データを含む、表に関連するすべてのストレージ領域を考慮します。

一方、pg_relation_size は、指定された表のデータと TOAST データのみのサイズを返します。インデックスのサイズは含まれません。

pg_total_relation_size は、pg_table_size と pg_indexes_size の合計値を返します。つまり、表、関連付けられた TOAST テーブル、そしてその表のすべてのインデックスを含む、表全体のストレージ容量を表します。

pg_table_size: 表、TOAST データ、インデックスを含む全体のサイズ
pg_relation_size: 表と TOAST データのみのサイズ

例:

SELECT
  tablename,
  pg_table_size(tablename) AS table_size,
  pg_relation_size(tablename) AS data_size,
  pg_indexes_size(tablename) AS index_size,
  pg_total_relation_size(tablename) AS total_size
FROM pg_catalog.pg_tables;

このクエリを実行すると、各表のサイズに関する詳細情報を確認できます。

PostgreSQL におけるストレージ容量を確認するサンプルコード

-- PostgreSQL 10以降で使用可能
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_table_size(tablename)) AS table_size,
  pg_size_pretty(pg_relation_size(tablename)) AS data_size,
  pg_size_pretty(pg_indexes_size(tablename)) AS index_size,
  pg_size_pretty(pg_total_relation_size(tablename)) AS total_size
FROM pg_catalog.pg_tables;

説明:

schemaname: 表のスキーマ名
tablename: 表名
pg_size_pretty(pg_table_size(tablename)): 表の合計サイズ (読みやすい形式で表示)

補足:

pg_size_pretty 関数は、ストレージ容量をバイト、キロバイト、メガバイトなどの単位に変換して、読みやすい形式で表示します。
PostgreSQL 9.0 以前の場合は、pg_size_pretty 関数に代わって pg_size_ext 関数を使用する必要があります。

実行方法:

上記のコードをテキストエディタに貼り付けます。
ファイルを .sql 拡張子で保存します。
PostgreSQL クライアントを開き、保存したファイルをデータベースに対して実行します。

psql -d mydatabase -f table_sizes.sql

このコマンドを実行すると、データベース内のすべての表のストレージ容量が表示されます。

注意事項:

このクエリは、データベース全体のパフォーマンスに影響を与える可能性があります。大規模なデータベースの場合は、クエリを実行する前に十分な時間とリソースを確保してください。
より詳細なストレージ容量分析が必要な場合は、pg_storage_stat_view ビューを使用することもできます。

オペレーティングシステムのツールを使用して、PostgreSQL データディレクトリのサイズを確認することもできます。これは、特にデータベースサーバーに直接アクセスできる場合に役立ちます。

du -sh /path/to/postgresql/data/

このコマンドは、postgresql データディレクトリとそのサブディレクトリの合計サイズを表示します。

pgAdmin などサードパーティ製のツール:

pgAdmin や Navicat などのサードパーティ製ツールを使用すると、PostgreSQL データベースを GUI で視覚化し、ストレージ容量を含むさまざまな情報を簡単に確認できます。

Cloud Monitoring ツール:

Amazon RDS や Google Cloud SQL などのクラウドホスティングサービスを使用している場合は、Cloud Monitoring ツールを使用してデータベースのストレージ容量を監視できます。これらのツールは、ストレージ使用量の推移をグラフで表示したり、使用量アラートを設定したりするなど、より詳細な機能を提供します。

最適な方法の選択:

使用する方法は、ニーズと環境によって異なります。