PostgresでDB内にある全てのテーブルサイズを知りたい時に使うクエリ

豆知識、というか備忘録。

SELECT 
    schemaname AS schema,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM 
    pg_catalog.pg_statio_user_tables
ORDER BY 
    pg_total_relation_size(relid) DESC;

これでテーブルサイズがわかります。

出力はこんな感じ。

schema	table_name	total_size
public	large_table	152 MB
public	medium_table	30 MB
public	small_table	5 MB

サイズの単位いらん！っていう場合はpg_size_prettyを使わなければOK。

SELECT 
    schemaname AS schema,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_total_relation_size(relid) AS total_size_row
FROM 
    pg_catalog.pg_statio_user_tables
ORDER BY 
    pg_total_relation_size(relid) DESC;