■ はじめに
システムの マイグレーションを行っているのだが、 PostgreSQL 互換の AWS Redshift のテーブル情報の一覧が欲しい とのことなので調べてみた。
目次
【１】システムが保持しているビュー ０）information_schema << 新規追加(2023/08/03) １）pg_namespace ２）pg_class ３）pg_tables ４）pg_stat_user_tables 【２】使用上の注意 【３】サンプル 例１：テーブル一覧（行数＆サイズ付き） 例２：ユーザテーブルを行数が多いごとに表示 【４】補足：Redshift について １）Redshiftのブロック数「relpages」 ２）Redshift の システムビュー
【１】システムが保持しているビュー
* 以下も読んでおくと面白いかも。
https://amg-solution.jp/blog/23077
PostgreSQL のシステムカタログ一覧
https://www.postgresql.jp/document/14/html/catalogs.html
０）information_schema
* PostgreSQL/Redshiftだけでなく、他のDBでも使えるらしい
https://www.postgresql.jp/document/8.1/html/information-schema.html
例
https://postgresweb.com/system-catalog-and-information-schema
-- スキーマ一覧 select * from information_schema.schemata; -- テーブル一覧 select * from information_schema.tables; -- ビュー一覧 select * from information_schema.views; -- トリガー一覧 select * from information_schema.triggers;
１）pg_namespace
名前空間を保存
主な項目
* それ以外は、以下のサイトを参照。
https://www.postgresql.jp/document/9.4/html/catalog-pg-namespace.html
| 名前 | 説明 | 備考 |
|---|---|---|
| oid | 行識別子 | 「２）pg_class」の「relnamespace」とJOIN可能 |
| nspname | 名前空間の名前 |
２）pg_class
* テーブルやインデックスなど、リレーションの情報が格納
主な項目
* それ以外は、以下のサイトを参照。
https://www.postgresql.jp/document/9.6/html/catalog-pg-class.html
| 名前 | 説明 | 備考 |
|---|---|---|
| oid | 行識別子 | 「３）pg_stat_user_tables」の「relid」とJOIN可能 |
| relname | テーブルを持つスキーマ名 | |
| relnamespace | リレーションを持つ名前空間のOID | 「１）pg_namespace」の「oid」とJOIN可能 |
| relpages | ブロック数 | サイズ(容量)に使用。bytes に変換する際は 8192(8K) をかける |
| reltuples | テーブル内の行数 | |
| relpersistence | pは永続テーブル、uはログを取らないテーブル、tは一時テーブルを表す | |
| relkind | rは通常のテーブル、iはインデックス、Sはシーケンス、vはビュー、mはマテリアライズドビュー、cは複合型、tはTOASTテーブル、fは外部テーブルを表します | 普通のテーブル取りたいなら、relkind = 'r' |
３）pg_tables
データベース内の各テーブル情報を保持 => テーブル一覧だけでよければ、こいつをSELECTしてあげるだけでいい
主な項目
* それ以外は、以下のサイトを参照。
https://www.postgresql.jp/document/9.6/html/view-pg-tables.html
| 名前 | 説明 | 備考 |
|---|---|---|
| schemaname | テーブルを持つスキーマ名 | |
| tablename | テーブル名 |
４）pg_stat_user_tables
現在のデータベースの各ユーザテーブルごとに１行の形で、 特定のテーブルへのアクセスに関する統計情報 => pg_stat_sys_tablesはシステムテーブル、pg_stat_all_tablesは全テーブル
主な項目
* それ以外は、以下のサイトを参照。
https://www.postgresql.jp/document/9.6/html/monitoring-stats.html#pg-stat-all-tables-view
| 名前 | 説明 | 備考 |
|---|---|---|
| relid | テーブルのOID | 「２）pg_class」の「oid」でJOIN可能 |
| schemaname | テーブルを持つスキーマ名 | |
| relname | テーブル名 | |
| n_live_tup | 有効行の推定値 | テーブルの行数 |
【２】使用上の注意
あくまで統計情報で実サイズとは異なっている可能性がある （更新されていない可能性がある） => 更新したい場合は、ANALYZE <table_name>で統計情報を更新 => 詳細は、以下のサイトを参照。
https://ichinari.work/postgresql_20210128/
https://www.insight-ltd.co.jp/tech_blog/postgresql/559/
【３】サンプル
例１：テーブル一覧（行数＆サイズ付き）
SELECT n.nspname AS schema_name , c.relname AS table_name , c.reltuples AS rows , (c.relpages * 8192) AS size FROM pg_class AS c JOIN pg_namespace AS n ON c.relnamespace = n.oid ;
例２：ユーザテーブルを行数が多いごとに表示
SELECT n.nspname AS schema_name , c.relname AS table_name , c.reltuples::bigint AS rows -- Cast は 指数表示防止 , (c.relpages * 8192) AS size FROM pg_stat_user_tables AS u JOIN pg_class AS c ON u.relid = c.oid JOIN pg_namespace AS n ON c.relnamespace = n.oid ORDER BY c.reltuples::numeric DESC ;
補足：pg_size_pretty
* pg_size_pretty(bigint) => 容量単位(kB、MB、GB、もしくはTB単位)で目で見て理解できる形式に バイト数に領域を変換
https://www.postgresql.jp/document/8.1/html/functions-admin.html
【４】補足：Redshift について
１）Redshiftのブロック数「relpages」
* 全てのユーザテーブルで「0」だった => Redshift では、実データを別で保持しているのかも、、、 => SVV_TABLE_INFO であればサイズの出力も可能（詳細は以下の関連記事を参照）
Redshift ～ システムビュー ～
https://dk521123.hatenablog.com/entry/2023/03/06/184928
２）Redshift の システムビュー
* 以下の関連記事を参照のこと。 => SVV_TABLE_INFO でほしい情報が取れる
Redshift ～ システムビュー ～
https://dk521123.hatenablog.com/entry/2023/03/06/184928
参考文献
https://qiita.com/awakia/items/99c3d114aa16099e825d
https://tm.root-n.com/database:postgresql:etc:pages_tuples
関連記事
PostgreSQL ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2014/03/08/000100
よく使う pdqlコマンド
https://dk521123.hatenablog.com/entry/2020/01/10/224818
psql で CSV出力する
https://dk521123.hatenablog.com/entry/2020/02/08/001155
Amazon Redshift ～ システムビュー ～
https://dk521123.hatenablog.com/entry/2023/03/06/184928
