PostgreSQLでデータベース、テーブル、フィールドの使用容量を確認する方法

はじめに

最近、社内製品使っているデータベースはSQL ServerからPostgreSQLへの移行作業行っています。移行の過程で、両方のデータベースにおける使用容量の差異を調査し、最適な移行を行うためにその確認方法をメモとして残します。

データベース全体の使用容量を確認する方法

PostgreSQLのpg_database_size関数を使用すると、特定のデータベース全体のサイズを取得できます。

方法１：データベース全体のサイズを確認するクエリ

SELECT pg_size_pretty(pg_database_size('your_database_name')) AS database_size;
  • your_database_nameの部分には、確認したいデータベースの名前を指定します。
  • pg_size_pretty関数を使用することで、読みやすい形式（KB, MB, GBなど）でサイズが表示されます。

例：

SELECT pg_size_pretty(pg_database_size('test01db')) as database_size;

結果：

“database_size”
“846 MB”

方法２：データベースのフォルダのサイズを確認する

クエリでデータベースのフォルダID取得

select datid, datname from pg_stat_database where datname = 'test01db';

結果：

“datid” “datname”
“227820”“test01db”

PostgreSQLサーバのdataフォルダ➡baseフォルダ➡227820のフォルダサイズを確認

私の場合、C:\Program Files\PostgreSQL\16\data\base\227820

テーブルごとの使用容量を確認する方法

PostgreSQLのpg_table_sizeやpg_total_relation_size関数を使用すると、テーブルのサイズを取得できます。

すべてのテーブルのサイズを一覧で確認するクエリ

SELECT 
    relname AS table_name, 
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_table_size(relid)) AS data_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM 
    pg_catalog.pg_statio_user_tables 
ORDER BY 
    pg_total_relation_size(relid) DESC;
  • pg_total_relation_sizeはテーブルとそのインデックス、トーストデータなどを含む全体のサイズを返します。
  • pg_table_size ( regclass ) インデックスを含まないディスクスペースを計算します。
  • pg_indexes_size ( regclass ) → bigint指定したテーブルに付与されたインデックスで使用されている全ディスクスペースを計算します。

各フィールドの使用容量を確認する方法

PostgreSQLのpg_column_size関数を使用すると、フィールドのサイズを取得できます。

テーブルのフィールドサイズを確認するクエリ

SELECT pg_size_pretty(sum(pg_column_size(your_field_name))) from your_table_name;
  • your_field_nameの部分には、確認したいフィールドの名前を指定します
  • your_table_nameの部分には、テーブルの名前を指定します

例：

SELECT pg_size_pretty(sum(pg_column_size(kono))) as kono_size, 
       pg_size_pretty(sum(pg_column_size(data)) as data_size ,	
       FROM test01_table;

結果：

kono_sizedata_size
2789 kB4080 kB

データベースオブジェクトサイズ関数まとめ

参考：https://www.postgresql.jp/document/16/html/functions-admin.html

関数説明
pg_column_size ( "any" ) → integer個々のデータ値を格納するのに使用されるバイト数を表示します。 テーブルの列の値に直接適用すると、圧縮が行われていればそれを反映します。
pg_column_compression ( "any" ) → text個々の可変長値で使われた圧縮アルゴリズムを表示します。 値が圧縮されていなければ、NULLを返します。
pg_database_size ( name ) → bigintpg_database_size ( oid ) → bigint名前あるいはOIDで指定したデータベースによって使われている全ディスクスペースを計算します。 この関数を使うには、指定したデータベースにCONNECT権限（デフォルトで付与されています）を持っているか、pg_read_all_statsロールの権限を持っていなければいけません。
pg_indexes_size ( regclass ) → bigint指定したテーブルに付与されたインデックスで使用されている全ディスクスペースを計算します。
pg_relation_size ( relation regclass [, fork text ] ) → bigint指定したリレーションの一つの「fork」で使用されているディスクスペースを計算します。 （大抵の目的には、すべてのフォークのサイズを合計する高レベルのpg_total_relation_sizeあるいはpg_table_sizeを使う方が便利です。） 引数1つではリレーションの主データフォークのサイズを返します。 2番目の引数で対象となるのがどのフォークであるかを指定できます。mainはリレーションの主データフォークのサイズを返します。fsmを指定すると、リレーションに関連した空き領域マップ(73.3を参照)のサイズを返します。vmを指定すると、リレーションに関連した可視性マップ(73.4を参照)のサイズを返します。initを指定すると、あれば、リレーションに関連した初期化フォークのサイズを返します。
pg_size_bytes ( text ) → bigint（pg_size_prettyが返す）人間が読めるフォーマットのサイズをバイトに変換します。 有効な値は、bytes、B、kB、MB、GB、TB、PBです。
pg_size_pretty ( bigint ) → textpg_size_pretty ( numeric ) → textバイトサイズを、サイズ単位(バイト、kB、MB、GB、TB、PBのうちの適切なもの)を使った、より人間が読みやすい形式に変換します。 単位は10のべき乗ではなく、2のべき乗であることに注意してください。ですから1kBは1024バイトで、1MBは10242 = 1048576バイト、などとなります。
pg_table_size ( regclass ) → bigint指定テーブルが使用している、インデックスを含まないディスクスペースを計算します。（ただしあればTOASTテーブル、空き領域マップ、可視性マップを含みます。）
pg_tablespace_size ( name ) → bigintpg_tablespace_size ( oid ) → bigint名前あるいはOIDで指定されたテーブル空間で使用されているディスクスペースを計算します。 現在のデータベースのデフォルトテーブル空間でない限り、この関数を使うには、指定したテーブル空間にCREATE権限を持っているか、pg_read_all_statsロールの権限を持っていなければいけません。
pg_total_relation_size ( regclass ) → bigint指定テーブルが使用している、インデックスとTOASTデータを含む全ディスクスペースを計算します。 結果はpg_table_size + pg_indexes_sizeと等価です。

コメント

タイトルとURLをコピーしました