PostgreSQLのテーブル一覧をpsqlで確認する手順｜スキーマごとの洗い出しと行数・サイズの把握

この記事のポイント

・\dt でテーブル一覧、\dt *.* で全スキーマを一括表示できる
・\dn でスキーマ一覧を確認してから絞り込むのが現場の基本手順
・pg_stat_user_tables と pg_total_relation_size で行数とサイズを把握できる
・information_schema.tables で複数スキーマをSQL一発で洗い出せる

# postgres ユーザーへ切り替えてpsqlを起動する
sudo -u postgres psql

# 特定のDBへ直接接続する場合
sudo -u postgres psql -d mydb

psql (16.3)
Type "help" for help.

postgres=#

\dt

          List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | appuser
 public | orders    | table | appuser
 public | products  | table | appuser
(3 rows)

\dt+

                               List of relations
 Schema |   Name    | Type  |  Owner  | Persistence | Access method |  Size   | Description
--------+-----------+-------+---------+-------------+---------------+---------+-------------
 public | customers | table | appuser | permanent   | heap          | 6416 kB |
 public | orders    | table | appuser | permanent   | heap          | 24 MB   |
 public | products  | table | appuser | permanent   | heap          | 1024 kB |
(3 rows)

\dn

      List of schemas
  Name  |       Owner
--------+-------------------
 auth   | appuser
 public | pg_database_owner
 sales  | appuser
(3 rows)

# 全スキーマのテーブルを一覧表示する
\dt *.*

# 特定スキーマのみ絞り込む場合
\dt sales.*

                     List of relations
 Schema |     Name      | Type  |   Owner
--------+---------------+-------+----------
 auth   | sessions      | table | appuser
 auth   | users         | table | appuser
 public | customers     | table | appuser
 public | orders        | table | appuser
 public | products      | table | appuser
 sales  | campaign_logs | table | appuser
 sales  | deals         | table | appuser
(7 rows)

SELECT
    table_schema,
    table_name,
    table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_name;

 table_schema |   table_name  | table_type
--------------+---------------+------------
 auth         | sessions      | BASE TABLE
 auth         | users         | BASE TABLE
 public       | customers     | BASE TABLE
 public       | orders        | BASE TABLE
 public       | products      | BASE TABLE
 sales        | campaign_logs | BASE TABLE
 sales        | deals         | BASE TABLE
(7 rows)

SELECT
    schemaname,
    relname AS tablename,
    n_live_tup AS row_estimate
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

 schemaname |   tablename   | row_estimate
------------+---------------+--------------
 public     | orders        |       128453
 auth       | sessions      |        87621
 public     | customers     |        34210
 sales      | campaign_logs |        15332
 public     | products      |         4508
 auth       | users         |         1073
 sales      | deals         |          422
(7 rows)

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

 schemaname |   tablename   | total_size
------------+---------------+------------
 public     | orders        | 32 MB
 auth       | sessions      | 22 MB
 public     | customers     | 7168 kB
 sales      | campaign_logs | 3584 kB
 public     | products      | 1280 kB
 auth       | users         | 384 kB
 sales      | deals         | 192 kB
(7 rows)

SELECT
    t.schemaname,
    t.tablename,
    COALESCE(s.n_live_tup, 0) AS row_estimate,
    pg_size_pretty(pg_total_relation_size(t.schemaname || '.' || t.tablename)) AS total_size
FROM pg_tables t
LEFT JOIN pg_stat_user_tables s
    ON t.schemaname = s.schemaname AND t.tablename = s.relname
WHERE t.schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_total_relation_size(t.schemaname || '.' || t.tablename) DESC;

# 現在の search_path を確認する
SHOW search_path;

# search_path を一時的に変更してから \dt を実行する
SET search_path TO sales, public;
\dt

# または全スキーマを対象にする
\dt *.*

# テーブルへのアクセス権を確認する（\dp = \z と同義）
\dp customers

# または information_schema で確認する
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'customers';

# 対象テーブルを手動で ANALYZE する
ANALYZE VERBOSE public.orders;

# またはDB全体をまとめてANALYZEする（接続は psql 外から）
vacuumdb --analyze-only -d mydb -U postgres