RedshiftとPostgreSQL(9.6)のシステムテーブルを比較してみた

2016.12.16

こんにちは。三上です。

こないだ飲み会でハイボール縛りに合いました。。(なま中、注文禁止だなんて・・><。

はじめに(当エントリを書くに至った経緯)

Amazon Redshiftを勉強中です。

PostgreSQLも勉強中です。。(ずっとmySQL使ってました。。

Redshiftのテーブル設計を学ぼうと思い、AWS Solutions Architect ブログのAmazon Redshift テーブル設計詳細ガイドを読んでいたら、最適化のための拡張viewが紹介されていました。

これはRedshiftのチューニングに役に立つはず!

と思い、viewの中身を見てみようと思ったのですが、難解。。

どこまでがPostgreSQL標準でどこからがRedshift固有か、どのリファレンスを見ればいいのかもよくわからず。。

→PostgreSQLのお勉強兼ねて、Redshiftがシステムとしてどんな情報管理してるか、調べてみようと思った次第です。

やりたいこと

  • Redshiftでどんなシステム情報を管理してるか(参照できるか)知りたい
  • RedshiftってどこまでPostgreSQLと同じか(同じシステム情報を参照できるか)知りたい

調べてみる

調査対象

  • Redshift:Cluster Version 1.0.1125
  • PostgreSQL:Version 9.6.1

※どちらも、detabaseとuser(SuperUser)のみ作成した状態

RedshiftとPostgreSQL

Amazon Redshift および PostgreSQL

Amazon Redshift は、PostgreSQL 8.0.2 に基づいています。Amazon Redshift と PostgreSQL の間には非常に重要な相違がいくつかあり、データウェアハウスアプリケーションを設計して開発するときはそれを考慮する必要があります。

Redshift ≒ PostgreSQL

とは聞いておりましたが、ver.8.0だったんですね。(9.6インストールしちゃった。。

今回はPostgrSQL 9.6と比較しますが、8.0と9.6では、ざっくり、以下の差分があるようです。

  • レプリケーション関連機能
  • ホットスタンバイ機能の追加
  • セキュリティ、アクセス権限関連機能
  • マテリアライズドビューの追加
  • Window関数、集約関数の追加

PostgreSQL 9.6.0文書 リリースノート

PostgreSQL の information_schema

システム情報 = information_schema?

と思ったので、Redshiftにつないでスキーマを見てみました。

defaultdb=# ¥l
                  List of databases
     name     | owner | encoding | access privileges
--------------+-------+----------+-------------------
 defaultdb    | root  | UNICODE  |
 dev          | rdsdb | UNICODE  |
 padb_harvest | rdsdb | UNICODE  |
 template0    | rdsdb | UNICODE  | rdsdb=CT/rdsdb
 template1    | rdsdb | UNICODE  | rdsdb=CT/rdsdb
(5 rows)

defaultdb=# ¥dn
List of schemas
  name  | owner
--------+-------
 public | rdsdb
(1 row)

→あれ? インフォメーションスキーマ、いない。。(あせ

(MySQLだと、インフォメーションスキーマ、普通に見えるんだけどなぁ。。

※厳密にはMySQLにはスキーマない(データベース=スキーマ)ですが。。↓

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mikami             |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> use information_schema
Database changed
MariaDB [information_schema]> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| ALL_PLUGINS                           |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
   (中略)
| INNODB_CHANGED_PAGES                  |
| INNODB_FT_DELETED                     |
| INNODB_TABLESPACES_SCRUBBING          |
+---------------------------------------+
78 rows in set (0.01 sec)

→google先生に聞いてみます。。

PostgreSQL 9.6.0文書 第35章 情報スキーマ

情報スキーマは、現在のデータベースで定義されたオブジェクトについての情報を持つビューの集合から構成されます。

PostgreSQL 9.6.0文書 35.1. スキーマ

情報スキーマ自身は、information_schemaという名前のスキーマです。 このスキーマは自動的にすべてのデータベース内に存在します。

デフォルトでは、情報スキーマはスキーマの検索パスには含まれません。 ですので、修飾した名前で情報スキーマ内のすべてのオブジェクトにアクセスする必要があります。

  • ちゃんといるらしい。
  • システム情報管理しているのは、テーブルではなくビューらしい。
  • アクセスする時はちゃんとスキーマ名指定しないとダメらしい。

なるほど。。

では、スキーマ指定して、viewの一覧を見てみます。

defaultdb=# ¥dv information_schema.*
                          List of relations
       schema       |              name               | type | owner
--------------------+---------------------------------+------+-------
 information_schema | applicable_roles                | view | rdsdb
 information_schema | check_constraints               | view | rdsdb
 information_schema | column_domain_usage             | view | rdsdb
 information_schema | column_privileges               | view | rdsdb
 information_schema | column_udt_usage                | view | rdsdb
 information_schema | columns                         | view | rdsdb
 information_schema | constraint_column_usage         | view | rdsdb
 information_schema | constraint_table_usage          | view | rdsdb
 information_schema | data_type_privileges            | view | rdsdb
 information_schema | domain_constraints              | view | rdsdb
 information_schema | domain_udt_usage                | view | rdsdb
 information_schema | domains                         | view | rdsdb
 information_schema | element_types                   | view | rdsdb
 information_schema | enabled_roles                   | view | rdsdb
 information_schema | information_schema_catalog_name | view | rdsdb
 information_schema | key_column_usage                | view | rdsdb
 information_schema | parameters                      | view | rdsdb
 information_schema | referential_constraints         | view | rdsdb
 information_schema | role_column_grants              | view | rdsdb
 information_schema | role_routine_grants             | view | rdsdb
 information_schema | role_table_grants               | view | rdsdb
 information_schema | role_usage_grants               | view | rdsdb
 information_schema | routine_privileges              | view | rdsdb
 information_schema | routines                        | view | rdsdb
 information_schema | schemata                        | view | rdsdb
 information_schema | table_constraints               | view | rdsdb
 information_schema | table_privileges                | view | rdsdb
 information_schema | tables                          | view | rdsdb
 information_schema | triggered_update_columns        | view | rdsdb
 information_schema | triggers                        | view | rdsdb
 information_schema | usage_privileges                | view | rdsdb
 information_schema | view_column_usage               | view | rdsdb
 information_schema | view_table_usage                | view | rdsdb
 information_schema | views                           | view | rdsdb
(34 rows)

PostgreSQLのviewも見てみます。

postgres=# ¥dv information_schema.*
                               リレーションの一覧
      スキーマ      |                 名前                  |   型   |  所有者
--------------------+---------------------------------------+--------+----------
 information_schema | _pg_foreign_data_wrappers             | ビュー | postgres
 information_schema | _pg_foreign_servers                   | ビュー | postgres
 information_schema | _pg_foreign_table_columns             | ビュー | postgres
 information_schema | _pg_foreign_tables                    | ビュー | postgres
 information_schema | _pg_user_mappings                     | ビュー | postgres
 information_schema | administrable_role_authorizations     | ビュー | postgres
 information_schema | applicable_roles                      | ビュー | postgres
 information_schema | attributes                            | ビュー | postgres
 information_schema | character_sets                        | ビュー | postgres
 information_schema | check_constraint_routine_usage        | ビュー | postgres
 information_schema | check_constraints                     | ビュー | postgres
 information_schema | collation_character_set_applicability | ビュー | postgres
 information_schema | collations                            | ビュー | postgres
 information_schema | column_domain_usage                   | ビュー | postgres
 information_schema | column_options                        | ビュー | postgres
 information_schema | column_privileges                     | ビュー | postgres
 information_schema | column_udt_usage                      | ビュー | postgres
 information_schema | columns                               | ビュー | postgres
 information_schema | constraint_column_usage               | ビュー | postgres
 information_schema | constraint_table_usage                | ビュー | postgres
 information_schema | data_type_privileges                  | ビュー | postgres
 information_schema | domain_constraints                    | ビュー | postgres
 information_schema | domain_udt_usage                      | ビュー | postgres
 information_schema | domains                               | ビュー | postgres
 information_schema | element_types                         | ビュー | postgres
 information_schema | enabled_roles                         | ビュー | postgres
 information_schema | foreign_data_wrapper_options          | ビュー | postgres
 information_schema | foreign_data_wrappers                 | ビュー | postgres
 information_schema | foreign_server_options                | ビュー | postgres
 information_schema | foreign_servers                       | ビュー | postgres
 information_schema | foreign_table_options                 | ビュー | postgres
 information_schema | foreign_tables                        | ビュー | postgres
 information_schema | information_schema_catalog_name       | ビュー | postgres
 information_schema | key_column_usage                      | ビュー | postgres
 information_schema | parameters                            | ビュー | postgres
 information_schema | referential_constraints               | ビュー | postgres
 information_schema | role_column_grants                    | ビュー | postgres
 information_schema | role_routine_grants                   | ビュー | postgres
 information_schema | role_table_grants                     | ビュー | postgres
 information_schema | role_udt_grants                       | ビュー | postgres
 information_schema | role_usage_grants                     | ビュー | postgres
 information_schema | routine_privileges                    | ビュー | postgres
 information_schema | routines                              | ビュー | postgres
 information_schema | schemata                              | ビュー | postgres
 information_schema | sequences                             | ビュー | postgres
 information_schema | table_constraints                     | ビュー | postgres
 information_schema | table_privileges                      | ビュー | postgres
 information_schema | tables                                | ビュー | postgres
 information_schema | transforms                            | ビュー | postgres
 information_schema | triggered_update_columns              | ビュー | postgres
 information_schema | triggers                              | ビュー | postgres
 information_schema | udt_privileges                        | ビュー | postgres
 information_schema | usage_privileges                      | ビュー | postgres
 information_schema | user_defined_types                    | ビュー | postgres
 information_schema | user_mapping_options                  | ビュー | postgres
 information_schema | user_mappings                         | ビュー | postgres
 information_schema | view_column_usage                     | ビュー | postgres
 information_schema | view_routine_usage                    | ビュー | postgres
 information_schema | view_table_usage                      | ビュー | postgres
 information_schema | views                                 | ビュー | postgres
(60 行)

→けっこう違うんですね@@;

そして、調べてたとき、"pg_" からシステム情報取得してるサンプルクエリよく見ましたが、innformation_schemaに、"pg_"なひと、いませんね。。@@?

→公式ドキュメント読み返してみます。。

PostgreSQL 9.6.0文書 第35章 情報スキーマ

しかし、情報スキーマのビューには、PostgreSQL固有の機能についての情報が含まれていません。 これに問い合わせを行うためには、システムカタログもしくはPostgreSQL固有のビューに問い合わせを行う必要があります。

PostgreSQL 9.6.0文書 第50章 システムカタログ

システムカタログとは、リレーショナルデータベース管理システムがテーブルや列の情報などのスキーマメタデータと内部的な情報を格納する場所です。 PostgreSQLのシステムカタログは通常のテーブルです。

  • システムカタログってひとがいるらしい。
  • information_schema以外でも、システム情報管理してるらしい。。

PostgreSQL の システムテーブル

PostgreSQLでは、以下のシステムテーブル(ビュー)を持ってるとのことでした。

  • 情報スキーマ(information_schema)

・SQL標準(OracleやMySQLなど他のRDBMSにもある

・ユーザー情報、テーブル情報、ビュー情報、カラム情報など、現在のデータベースに関する情報を管理

・PostgreSQL固有機能に関しては、システムカタログを参照する

・(テーブルではなくて、ビュー。。

  • システムカタログ

・テーブルや列の情報などのスキーマメタデータと内部的な情報を管理

・通常のテーブルのため更新可能だが、更新でDBを壊してしまうこともあるため注意

  • 統計情報ビュー

・クエリやトランザクション、シーケンスなどのシステムの現在の状態を管理

・統計情報関数を使用して、独自の統計情報ビューを作成することも可能

→全テーブル&ビューを表示してみました。

  • Redshift
defaultdb=# select schemaname as schema, tablename as name, 'table' as type from pg_tables
defaultdb-# union
defaultdb-# select schemaname as schema, viewname as name, 'view' as type from pg_views
defaultdb-# order by schema, name;
       schema       |              name               | type
--------------------+---------------------------------+-------
 information_schema | applicable_roles                | view
 information_schema | check_constraints               | view
 information_schema | column_domain_usage             | view
 information_schema | column_privileges               | view
 information_schema | column_udt_usage                | view
 information_schema | columns                         | view
 information_schema | constraint_column_usage         | view
 information_schema | constraint_table_usage          | view
 information_schema | data_type_privileges            | view
 information_schema | domain_constraints              | view
 information_schema | domain_udt_usage                | view
 information_schema | domains                         | view
 information_schema | element_types                   | view
 information_schema | enabled_roles                   | view
 information_schema | information_schema_catalog_name | view
 information_schema | key_column_usage                | view
 information_schema | parameters                      | view
 information_schema | referential_constraints         | view
 information_schema | role_column_grants              | view
 information_schema | role_routine_grants             | view
 information_schema | role_table_grants               | view
 information_schema | role_usage_grants               | view
 information_schema | routine_privileges              | view
 information_schema | routines                        | view
 information_schema | schemata                        | view
 information_schema | sql_features                    | table
 information_schema | sql_implementation_info         | table
 information_schema | sql_languages                   | table
 information_schema | sql_packages                    | table
 information_schema | sql_sizing                      | table
 information_schema | sql_sizing_profiles             | table
 information_schema | table_constraints               | view
 information_schema | table_privileges                | view
 information_schema | tables                          | view
 information_schema | triggered_update_columns        | view
 information_schema | triggers                        | view
 information_schema | usage_privileges                | view
 information_schema | view_column_usage               | view
 information_schema | view_table_usage                | view
 information_schema | views                           | view
 pg_catalog         | padb_config_harvest             | table
 pg_catalog         | pg_aggregate                    | table
 pg_catalog         | pg_am                           | table
 pg_catalog         | pg_amop                         | table
 pg_catalog         | pg_amproc                       | table
 pg_catalog         | pg_attrdef                      | table
 pg_catalog         | pg_attribute                    | table
 pg_catalog         | pg_bar_ddllog                   | table
 pg_catalog         | pg_bar_repos                    | table
 pg_catalog         | pg_bar_state                    | table
 pg_catalog         | pg_cast                         | table
 pg_catalog         | pg_class                        | table
 pg_catalog         | pg_conf                         | table
 pg_catalog         | pg_constraint                   | table
 pg_catalog         | pg_conversion                   | table
 pg_catalog         | pg_database                     | table
 pg_catalog         | pg_database_extended            | table
 pg_catalog         | pg_database_info                | view
 pg_catalog         | pg_default_acl                  | table
 pg_catalog         | pg_depend                       | table
 pg_catalog         | pg_description                  | table
 pg_catalog         | pg_group                        | table
 pg_catalog         | pg_group_extended               | table
 pg_catalog         | pg_highwatermark                | table
 pg_catalog         | pg_index                        | table
 pg_catalog         | pg_indexes                      | view
 pg_catalog         | pg_inherits                     | table
 pg_catalog         | pg_language                     | table
 pg_catalog         | pg_largeobject                  | table
 pg_catalog         | pg_library                      | table
 pg_catalog         | pg_listener                     | table
 pg_catalog         | pg_locks                        | view
 pg_catalog         | pg_namespace                    | table
 pg_catalog         | pg_opclass                      | table
 pg_catalog         | pg_operator                     | table
 pg_catalog         | pg_proc                         | table
 pg_catalog         | pg_resize                       | table
 pg_catalog         | pg_rewrite                      | table
 pg_catalog         | pg_rules                        | view
 pg_catalog         | pg_settings                     | view
 pg_catalog         | pg_shadow                       | table
 pg_catalog         | pg_shadow_extended              | table
 pg_catalog         | pg_shdepend                     | table
 pg_catalog         | pg_stat_activity                | view
 pg_catalog         | pg_stat_all_indexes             | view
 pg_catalog         | pg_stat_all_tables              | view
 pg_catalog         | pg_stat_database                | view
 pg_catalog         | pg_stat_sys_indexes             | view
 pg_catalog         | pg_stat_sys_tables              | view
 pg_catalog         | pg_stat_user_indexes            | view
 pg_catalog         | pg_stat_user_tables             | view
 pg_catalog         | pg_statio_all_indexes           | view
 pg_catalog         | pg_statio_all_sequences         | view
 pg_catalog         | pg_statio_all_tables            | view
 pg_catalog         | pg_statio_sys_indexes           | view
 pg_catalog         | pg_statio_sys_sequences         | view
 pg_catalog         | pg_statio_sys_tables            | view
 pg_catalog         | pg_statio_user_indexes          | view
 pg_catalog         | pg_statio_user_sequences        | view
 pg_catalog         | pg_statio_user_tables           | view
 pg_catalog         | pg_statistic                    | table
 pg_catalog         | pg_statistic_indicator          | table
 pg_catalog         | pg_statistic_multicol           | table
 pg_catalog         | pg_stats                        | view
 pg_catalog         | pg_table_def                    | view
 pg_catalog         | pg_tables                       | view
 pg_catalog         | pg_tablespace                   | table
 pg_catalog         | pg_test                         | table
 pg_catalog         | pg_trigger                      | table
 pg_catalog         | pg_type                         | table
 pg_catalog         | pg_udf                          | table
 pg_catalog         | pg_user                         | view
 pg_catalog         | pg_user_info                    | view
 pg_catalog         | pg_views                        | view
 pg_catalog         | stl_aggr                        | table
 pg_catalog         | stl_aggr_distinct               | table
 pg_catalog         | stl_alert_event_log             | table
 pg_catalog         | stl_analyze                     | table
 pg_catalog         | stl_background_monitor          | table
 pg_catalog         | stl_backup                      | table
 pg_catalog         | stl_backup_compute              | table
 pg_catalog         | stl_backup_leader               | table
 pg_catalog         | stl_bcast                       | table
 pg_catalog         | stl_block_integrity_error       | table
 pg_catalog         | stl_block_pins                  | table
 pg_catalog         | stl_blocklist                   | table
 pg_catalog         | stl_bootstrap                   | table
 pg_catalog         | stl_clear_reps                  | table
 pg_catalog         | stl_client_write_timings        | table
 pg_catalog         | stl_column_errors               | table
 pg_catalog         | stl_column_stats                | table
 pg_catalog         | stl_comm_capture                | table
 pg_catalog         | stl_comm_diag_errors            | table
 pg_catalog         | stl_comm_excess                 | table
 pg_catalog         | stl_comm_integrity_error        | table
 pg_catalog         | stl_comm_packetpool_sink        | table
 pg_catalog         | stl_comm_packetpool_source      | table
 pg_catalog         | stl_comm_rexmit                 | table
 pg_catalog         | stl_comm_send                   | table
 pg_catalog         | stl_command_result              | table
 pg_catalog         | stl_command_start               | table
 pg_catalog         | stl_commit_regions              | table
 pg_catalog         | stl_commit_stats                | table
 pg_catalog         | stl_compile_info                | table
 pg_catalog         | stl_complyze                    | table
 pg_catalog         | stl_config                      | table
 pg_catalog         | stl_config_chooser              | table
 pg_catalog         | stl_config_history              | table
 pg_catalog         | stl_connection_log              | table
 pg_catalog         | stl_consistency                 | table
 pg_catalog         | stl_core_stat                   | table
 pg_catalog         | stl_cqilog                      | table
 pg_catalog         | stl_create_replication          | table
 pg_catalog         | stl_ddltext                     | table
 pg_catalog         | stl_delete                      | table
 pg_catalog         | stl_dirty_headers               | table
 pg_catalog         | stl_disk_cache_stats            | table
 pg_catalog         | stl_disk_extent_splits          | table
 pg_catalog         | stl_disk_failures               | table
 pg_catalog         | stl_disk_full_diag              | table
 pg_catalog         | stl_disk_speeds                 | table
 pg_catalog         | stl_disk_topology               | table
 pg_catalog         | stl_dist                        | table
 pg_catalog         | stl_dropped_replication         | table
 pg_catalog         | stl_error                       | table
 pg_catalog         | stl_event_trace                 | table
 pg_catalog         | stl_exec_state                  | table
 pg_catalog         | stl_explain                     | table
 pg_catalog         | stl_fabric_stats                | table
 pg_catalog         | stl_fetchers                    | table
 pg_catalog         | stl_file_scan                   | table
 pg_catalog         | stl_freed_disk_addrs            | table
 pg_catalog         | stl_hash                        | table
 pg_catalog         | stl_hash_partitions             | table
 pg_catalog         | stl_hashjoin                    | table
 pg_catalog         | stl_hashjoin_partitions         | table
 pg_catalog         | stl_hdr_integrity_error         | table
 pg_catalog         | stl_inflight                    | table
 pg_catalog         | stl_insert                      | table
 pg_catalog         | stl_invalid_replication         | table
 pg_catalog         | stl_io_error                    | table
 pg_catalog         | stl_io_perf                     | table
 pg_catalog         | stl_io_stat                     | table
 pg_catalog         | stl_leader_snapshot             | table
 pg_catalog         | stl_limit                       | table
 pg_catalog         | stl_load_commits                | table
 pg_catalog         | stl_load_error_info             | table
 pg_catalog         | stl_load_errors                 | table
 pg_catalog         | stl_load_trace                  | table
 pg_catalog         | stl_loaderror_detail            | table
 pg_catalog         | stl_mark_partitions             | table
 pg_catalog         | stl_mem_alloc_history           | table
 pg_catalog         | stl_mem_freelist                | table
 pg_catalog         | stl_mem_leaks                   | table
 pg_catalog         | stl_memefficiency               | table
 pg_catalog         | stl_memhisto                    | table
 pg_catalog         | stl_merge                       | table
 pg_catalog         | stl_mergejoin                   | table
 pg_catalog         | stl_metadata_step               | table
 pg_catalog         | stl_minmax_errors               | table
 pg_catalog         | stl_misc_sys_stat               | table
 pg_catalog         | stl_mutex_contention            | table
 pg_catalog         | stl_nestloop                    | table
 pg_catalog         | stl_net_stat                    | table
 pg_catalog         | stl_netmon_ip                   | table
 pg_catalog         | stl_netmon_mac                  | table
 pg_catalog         | stl_node_failovers              | table
 pg_catalog         | stl_nullyze                     | table
 pg_catalog         | stl_parse                       | table
 pg_catalog         | stl_pinned_blocks               | table
 pg_catalog         | stl_plan_explain                | table
 pg_catalog         | stl_plan_explain_props          | table
 pg_catalog         | stl_plan_explain_tree           | table
 pg_catalog         | stl_plan_info                   | table
 pg_catalog         | stl_plan_perf                   | table
 pg_catalog         | stl_plan_qid_map                | table
 pg_catalog         | stl_plan_querytext              | table
 pg_catalog         | stl_plan_rule_stats             | table
 pg_catalog         | stl_plan_state                  | table
 pg_catalog         | stl_print                       | table
 pg_catalog         | stl_proc_stat                   | table
 pg_catalog         | stl_project                     | table
 pg_catalog         | stl_query                       | table
 pg_catalog         | stl_query_mem_leaks             | table
 pg_catalog         | stl_query_metrics               | table
 pg_catalog         | stl_querytext                   | table
 pg_catalog         | stl_replacements                | table
 pg_catalog         | stl_rereplication               | table
 pg_catalog         | stl_res_leaks                   | table
 pg_catalog         | stl_restarted_sessions          | table
 pg_catalog         | stl_restarted_sessions_internal | table
 pg_catalog         | stl_restore                     | table
 pg_catalog         | stl_return                      | table
 pg_catalog         | stl_rpc                         | table
 pg_catalog         | stl_s3client                    | table
 pg_catalog         | stl_s3client_error              | table
 pg_catalog         | stl_s3client_trace              | table
 pg_catalog         | stl_save                        | table
 pg_catalog         | stl_scan                        | table
 pg_catalog         | stl_seg_completed_notify        | table
 pg_catalog         | stl_segment_ends_cleanly        | table
 pg_catalog         | stl_segment_starts              | table
 pg_catalog         | stl_sem_pool                    | table
 pg_catalog         | stl_sem_stat                    | table
 pg_catalog         | stl_sem_usage                   | table
 pg_catalog         | stl_server_write_timings        | table
 pg_catalog         | stl_sessions                    | table
 pg_catalog         | stl_smfaults                    | table
 pg_catalog         | stl_smfaults_cleared            | table
 pg_catalog         | stl_smfaults_mval               | table
 pg_catalog         | stl_smfaults_overflow           | table
 pg_catalog         | stl_smlog                       | table
 pg_catalog         | stl_snapshotbackup              | table
 pg_catalog         | stl_sort                        | table
 pg_catalog         | stl_sorted_checker              | table
 pg_catalog         | stl_sorted_checker_leader       | table
 pg_catalog         | stl_sshclient_error             | table
 pg_catalog         | stl_stack_trace                 | table
 pg_catalog         | stl_step_parse                  | table
 pg_catalog         | stl_steps                       | table
 pg_catalog         | stl_stream_segs                 | table
 pg_catalog         | stl_superblock_integrity_error  | table
 pg_catalog         | stl_swap                        | table
 pg_catalog         | stl_tr_conflict                 | table
 pg_catalog         | stl_udf                         | table
 pg_catalog         | stl_udf_compile_error           | table
 pg_catalog         | stl_udf_log                     | table
 pg_catalog         | stl_udf_trace                   | table
 pg_catalog         | stl_undo_transactions           | table
 pg_catalog         | stl_undone                      | table
 pg_catalog         | stl_unique                      | table
 pg_catalog         | stl_unload_log                  | table
 pg_catalog         | stl_userlog                     | table
 pg_catalog         | stl_utilitytext                 | table
 pg_catalog         | stl_vacuum                      | table
 pg_catalog         | stl_vacuum_background           | table
 pg_catalog         | stl_vacuum_detail               | table
 pg_catalog         | stl_warning                     | table
 pg_catalog         | stl_window                      | table
 pg_catalog         | stl_wlm_error                   | table
 pg_catalog         | stl_wlm_query                   | table
 pg_catalog         | stl_wlm_rule_action             | table
 pg_catalog         | stl_wlm_trace                   | table
 pg_catalog         | stl_wlm_trace_query_state       | table
 pg_catalog         | stv_active_cursors              | table
 pg_catalog         | stv_aio_reads                   | table
 pg_catalog         | stv_aio_writes                  | table
 pg_catalog         | stv_allocator                   | table
 pg_catalog         | stv_available_backups           | table
 pg_catalog         | stv_backup_state                | table
 pg_catalog         | stv_block_limits_metadata       | table
 pg_catalog         | stv_block_lru                   | table
 pg_catalog         | stv_block_reps                  | table
 pg_catalog         | stv_blocklist                   | table
 pg_catalog         | stv_channel_reservation         | table
 pg_catalog         | stv_channel_sink                | table
 pg_catalog         | stv_channel_source              | table
 pg_catalog         | stv_channel_usage               | table
 pg_catalog         | stv_code_pool                   | table
 pg_catalog         | stv_comm_diag_ping_stats        | table
 pg_catalog         | stv_comm_packetpool_sink        | table
 pg_catalog         | stv_comm_packetpool_source      | table
 pg_catalog         | stv_comm_send                   | table
 pg_catalog         | stv_comm_slice                  | table
 pg_catalog         | stv_compression                 | table
 pg_catalog         | stv_configuration               | table
 pg_catalog         | stv_core_stat                   | table
 pg_catalog         | stv_cursor_configuration        | table
 pg_catalog         | stv_disk_addresses              | table
 pg_catalog         | stv_disk_extents                | table
 pg_catalog         | stv_disk_map                    | table
 pg_catalog         | stv_exec_state                  | table
 pg_catalog         | stv_fdisk_stats                 | table
 pg_catalog         | stv_fragmentation               | table
 pg_catalog         | stv_gui_status                  | table
 pg_catalog         | stv_identity_highwater          | table
 pg_catalog         | stv_inflight                    | table
 pg_catalog         | stv_interleaved_counts          | table
 pg_catalog         | stv_invalid_replication         | table
 pg_catalog         | stv_invisible_xact              | table
 pg_catalog         | stv_io_local_pending            | table
 pg_catalog         | stv_io_local_pending_time       | table
 pg_catalog         | stv_io_local_times              | table
 pg_catalog         | stv_io_raidp_client_pending     | table
 pg_catalog         | stv_io_raidp_server_pending     | table
 pg_catalog         | stv_io_readahead                | table
 pg_catalog         | stv_io_stat                     | table
 pg_catalog         | stv_ip                          | table
 pg_catalog         | stv_load_state                  | table
 pg_catalog         | stv_locks                       | table
 pg_catalog         | stv_mem_alloc_history           | table
 pg_catalog         | stv_mem_freelist                | table
 pg_catalog         | stv_mem_freelist_len            | table
 pg_catalog         | stv_mem_leaks                   | table
 pg_catalog         | stv_net_stat                    | table
 pg_catalog         | stv_networks                    | table
 pg_catalog         | stv_nodes                       | table
 pg_catalog         | stv_partitions                  | table
 pg_catalog         | stv_pg_wal_length               | table
 pg_catalog         | stv_proc_stat                   | table
 pg_catalog         | stv_profiler                    | table
 pg_catalog         | stv_query_mem_leaks             | table
 pg_catalog         | stv_query_metrics               | table
 pg_catalog         | stv_query_stats                 | table
 pg_catalog         | stv_recent_io                   | table
 pg_catalog         | stv_recents                     | table
 pg_catalog         | stv_resize_library_import       | table
 pg_catalog         | stv_resize_state                | table
 pg_catalog         | stv_resize_table_import         | table
 pg_catalog         | stv_restore_state               | table
 pg_catalog         | stv_restore_table_state         | table
 pg_catalog         | stv_sem_usage                   | table
 pg_catalog         | stv_sessions                    | table
 pg_catalog         | stv_slices                      | table
 pg_catalog         | stv_startup_recovery_state      | table
 pg_catalog         | stv_superblock                  | table
 pg_catalog         | stv_superblock_locations        | table
 pg_catalog         | stv_tbl_perm                    | table
 pg_catalog         | stv_tbl_trans                   | table
 pg_catalog         | stv_transactions                | table
 pg_catalog         | stv_underrepped_blocks          | table
 pg_catalog         | stv_vacuum_state                | table
 pg_catalog         | stv_wlm_classification_config   | table
 pg_catalog         | stv_wlm_config                  | table
 pg_catalog         | stv_wlm_config_internal         | table
 pg_catalog         | stv_wlm_query_queue_state       | table
 pg_catalog         | stv_wlm_query_state             | table
 pg_catalog         | stv_wlm_query_task_state        | table
 pg_catalog         | stv_wlm_service_class_config    | table
 pg_catalog         | stv_wlm_service_class_state     | table
 pg_catalog         | stv_xact                        | table
 pg_catalog         | svl_compile                     | view
 pg_catalog         | svl_qlog                        | view
 pg_catalog         | svl_query_queue_info            | view
 pg_catalog         | svl_query_report                | view
 pg_catalog         | svl_query_summary               | view
 pg_catalog         | svl_statementtext               | view
 pg_catalog         | svl_udf_log                     | view
 pg_catalog         | svl_vacuum_percentage           | view
 pg_catalog         | svv_diskusage                   | view
 pg_catalog         | svv_interleaved_columns         | view
 pg_catalog         | svv_query_inflight              | view
 pg_catalog         | svv_query_state                 | view
 pg_catalog         | svv_restore_table_state         | view
 pg_catalog         | svv_table_info                  | view
 pg_catalog         | svv_transactions                | view
 pg_catalog         | svv_vacuum_progress             | view
 pg_catalog         | svv_vacuum_summary              | view
 pg_catalog         | systable_globaldict             | table
 pg_catalog         | systable_schema                 | table
 pg_catalog         | systable_topology               | table
(391 rows)
  • PostgreSQL
postgres=# select schemaname as schema, tablename as name, 'table' as type from pg_tables union
postgres-# select schemaname as schema, viewname as name, 'view' as type from pg_views
postgres-# order by schema, name;
       schema       |                 name                  | type
--------------------+---------------------------------------+-------
 information_schema | _pg_foreign_data_wrappers             | view
 information_schema | _pg_foreign_servers                   | view
 information_schema | _pg_foreign_table_columns             | view
 information_schema | _pg_foreign_tables                    | view
 information_schema | _pg_user_mappings                     | view
 information_schema | administrable_role_authorizations     | view
 information_schema | applicable_roles                      | view
 information_schema | attributes                            | view
 information_schema | character_sets                        | view
 information_schema | check_constraint_routine_usage        | view
 information_schema | check_constraints                     | view
 information_schema | collation_character_set_applicability | view
 information_schema | collations                            | view
 information_schema | column_domain_usage                   | view
 information_schema | column_options                        | view
 information_schema | column_privileges                     | view
 information_schema | column_udt_usage                      | view
 information_schema | columns                               | view
 information_schema | constraint_column_usage               | view
 information_schema | constraint_table_usage                | view
 information_schema | data_type_privileges                  | view
 information_schema | domain_constraints                    | view
 information_schema | domain_udt_usage                      | view
 information_schema | domains                               | view
 information_schema | element_types                         | view
 information_schema | enabled_roles                         | view
 information_schema | foreign_data_wrapper_options          | view
 information_schema | foreign_data_wrappers                 | view
 information_schema | foreign_server_options                | view
 information_schema | foreign_servers                       | view
 information_schema | foreign_table_options                 | view
 information_schema | foreign_tables                        | view
 information_schema | information_schema_catalog_name       | view
 information_schema | key_column_usage                      | view
 information_schema | parameters                            | view
 information_schema | referential_constraints               | view
 information_schema | role_column_grants                    | view
 information_schema | role_routine_grants                   | view
 information_schema | role_table_grants                     | view
 information_schema | role_udt_grants                       | view
 information_schema | role_usage_grants                     | view
 information_schema | routine_privileges                    | view
 information_schema | routines                              | view
 information_schema | schemata                              | view
 information_schema | sequences                             | view
 information_schema | sql_features                          | table
 information_schema | sql_implementation_info               | table
 information_schema | sql_languages                         | table
 information_schema | sql_packages                          | table
 information_schema | sql_parts                             | table
 information_schema | sql_sizing                            | table
 information_schema | sql_sizing_profiles                   | table
 information_schema | table_constraints                     | view
 information_schema | table_privileges                      | view
 information_schema | tables                                | view
 information_schema | transforms                            | view
 information_schema | triggered_update_columns              | view
 information_schema | triggers                              | view
 information_schema | udt_privileges                        | view
 information_schema | usage_privileges                      | view
 information_schema | user_defined_types                    | view
 information_schema | user_mapping_options                  | view
 information_schema | user_mappings                         | view
 information_schema | view_column_usage                     | view
 information_schema | view_routine_usage                    | view
 information_schema | view_table_usage                      | view
 information_schema | views                                 | view
 pg_catalog         | pg_aggregate                          | table
 pg_catalog         | pg_am                                 | table
 pg_catalog         | pg_amop                               | table
 pg_catalog         | pg_amproc                             | table
 pg_catalog         | pg_attrdef                            | table
 pg_catalog         | pg_attribute                          | table
 pg_catalog         | pg_auth_members                       | table
 pg_catalog         | pg_authid                             | table
 pg_catalog         | pg_available_extension_versions       | view
 pg_catalog         | pg_available_extensions               | view
 pg_catalog         | pg_cast                               | table
 pg_catalog         | pg_class                              | table
 pg_catalog         | pg_collation                          | table
 pg_catalog         | pg_config                             | view
 pg_catalog         | pg_constraint                         | table
 pg_catalog         | pg_conversion                         | table
 pg_catalog         | pg_cursors                            | view
 pg_catalog         | pg_database                           | table
 pg_catalog         | pg_db_role_setting                    | table
 pg_catalog         | pg_default_acl                        | table
 pg_catalog         | pg_depend                             | table
 pg_catalog         | pg_description                        | table
 pg_catalog         | pg_enum                               | table
 pg_catalog         | pg_event_trigger                      | table
 pg_catalog         | pg_extension                          | table
 pg_catalog         | pg_file_settings                      | view
 pg_catalog         | pg_foreign_data_wrapper               | table
 pg_catalog         | pg_foreign_server                     | table
 pg_catalog         | pg_foreign_table                      | table
 pg_catalog         | pg_group                              | view
 pg_catalog         | pg_index                              | table
 pg_catalog         | pg_indexes                            | view
 pg_catalog         | pg_inherits                           | table
 pg_catalog         | pg_init_privs                         | table
 pg_catalog         | pg_language                           | table
 pg_catalog         | pg_largeobject                        | table
 pg_catalog         | pg_largeobject_metadata               | table
 pg_catalog         | pg_locks                              | view
 pg_catalog         | pg_matviews                           | view
 pg_catalog         | pg_namespace                          | table
 pg_catalog         | pg_opclass                            | table
 pg_catalog         | pg_operator                           | table
 pg_catalog         | pg_opfamily                           | table
 pg_catalog         | pg_pltemplate                         | table
 pg_catalog         | pg_policies                           | view
 pg_catalog         | pg_policy                             | table
 pg_catalog         | pg_prepared_statements                | view
 pg_catalog         | pg_prepared_xacts                     | view
 pg_catalog         | pg_proc                               | table
 pg_catalog         | pg_range                              | table
 pg_catalog         | pg_replication_origin                 | table
 pg_catalog         | pg_replication_origin_status          | view
 pg_catalog         | pg_replication_slots                  | view
 pg_catalog         | pg_rewrite                            | table
 pg_catalog         | pg_roles                              | view
 pg_catalog         | pg_rules                              | view
 pg_catalog         | pg_seclabel                           | table
 pg_catalog         | pg_seclabels                          | view
 pg_catalog         | pg_settings                           | view
 pg_catalog         | pg_shadow                             | view
 pg_catalog         | pg_shdepend                           | table
 pg_catalog         | pg_shdescription                      | table
 pg_catalog         | pg_shseclabel                         | table
 pg_catalog         | pg_stat_activity                      | view
 pg_catalog         | pg_stat_all_indexes                   | view
 pg_catalog         | pg_stat_all_tables                    | view
 pg_catalog         | pg_stat_archiver                      | view
 pg_catalog         | pg_stat_bgwriter                      | view
 pg_catalog         | pg_stat_database                      | view
 pg_catalog         | pg_stat_database_conflicts            | view
 pg_catalog         | pg_stat_progress_vacuum               | view
 pg_catalog         | pg_stat_replication                   | view
 pg_catalog         | pg_stat_ssl                           | view
 pg_catalog         | pg_stat_sys_indexes                   | view
 pg_catalog         | pg_stat_sys_tables                    | view
 pg_catalog         | pg_stat_user_functions                | view
 pg_catalog         | pg_stat_user_indexes                  | view
 pg_catalog         | pg_stat_user_tables                   | view
 pg_catalog         | pg_stat_wal_receiver                  | view
 pg_catalog         | pg_stat_xact_all_tables               | view
 pg_catalog         | pg_stat_xact_sys_tables               | view
 pg_catalog         | pg_stat_xact_user_functions           | view
 pg_catalog         | pg_stat_xact_user_tables              | view
 pg_catalog         | pg_statio_all_indexes                 | view
 pg_catalog         | pg_statio_all_sequences               | view
 pg_catalog         | pg_statio_all_tables                  | view
 pg_catalog         | pg_statio_sys_indexes                 | view
 pg_catalog         | pg_statio_sys_sequences               | view
 pg_catalog         | pg_statio_sys_tables                  | view
 pg_catalog         | pg_statio_user_indexes                | view
 pg_catalog         | pg_statio_user_sequences              | view
 pg_catalog         | pg_statio_user_tables                 | view
 pg_catalog         | pg_statistic                          | table
 pg_catalog         | pg_stats                              | view
 pg_catalog         | pg_tables                             | view
 pg_catalog         | pg_tablespace                         | table
 pg_catalog         | pg_timezone_abbrevs                   | view
 pg_catalog         | pg_timezone_names                     | view
 pg_catalog         | pg_transform                          | table
 pg_catalog         | pg_trigger                            | table
 pg_catalog         | pg_ts_config                          | table
 pg_catalog         | pg_ts_config_map                      | table
 pg_catalog         | pg_ts_dict                            | table
 pg_catalog         | pg_ts_parser                          | table
 pg_catalog         | pg_ts_template                        | table
 pg_catalog         | pg_type                               | table
 pg_catalog         | pg_user                               | view
 pg_catalog         | pg_user_mapping                       | table
 pg_catalog         | pg_user_mappings                      | view
 pg_catalog         | pg_views                              | view
(177 行)

→Redshiftには、stl_, stv_で始まるテーブルと、svl_, svv_で始まるビューがたくさんありました@@!

Redshiftのシステムビュー、システムカタログ

AWSのリファレンスを参照します。

Amazon Redshift 開発者ガイド システムビュー

Amazon Redshift 開発者ガイド システムカタログテーブル

Redshiftのシステムテーブル

その他にも、Redshiftは

  • ログ記録のためのシステムテーブル
  • スナップショットデータのシステムテーブル

を持ってるとのこと

Amazon Redshift 開発者ガイド ログ記録のための STL テーブル

Amazon Redshift 開発者ガイド スナップショットデータの STV テーブル

RedshiftとPostgreSQL(9.6)のシステムテーブル一覧

名称
スキーマ
種別
Redshift
PSQL(9.6)
administrable_role_authorizations information_schema view
×
applicable_roles information_schema view
attributes information_schema view
×
character_sets information_schema view
×
check_constraint_routine_usage information_schema view
×
check_constraints information_schema view
collation_character_set_applicability information_schema view
×
collations information_schema view
×
column_domain_usage information_schema view
column_options information_schema view
×
column_privileges information_schema view
column_udt_usage information_schema view
columns information_schema view
constraint_column_usage information_schema view
constraint_table_usage information_schema view
data_type_privileges information_schema view
domain_constraints information_schema view
domain_udt_usage information_schema view
domains information_schema view
element_types information_schema view
enabled_roles information_schema view
foreign_data_wrapper_options information_schema view
foreign_data_wrappers information_schema view
×
foreign_server_options information_schema view
×
foreign_servers information_schema view
×
foreign_table_options information_schema view
×
foreign_tables information_schema view
×
information_schema_catalog_name information_schema view
key_column_usage information_schema view
parameters information_schema view
referential_constraints information_schema view
role_column_grants information_schema view
role_routine_grants information_schema view
role_table_grants information_schema view
role_udt_grants information_schema view
×
role_usage_grants information_schema view
routine_privileges information_schema view
routines information_schema view
schemata information_schema view
sequences information_schema view
×
table_constraints information_schema view
table_privileges information_schema view
tables information_schema view
transforms information_schema view
×
triggered_update_columns information_schema view
triggers information_schema view
udt_privileges information_schema view
×
usage_privileges information_schema view
user_defined_types information_schema view
×
user_mapping_options information_schema view
×
user_mappings information_schema view
×
view_column_usage information_schema view
view_routine_usage information_schema view
×
view_table_usage information_schema view
views information_schema view
pg_aggregate pg_catalog table
pg_am pg_catalog table
pg_amop pg_catalog table
pg_amproc pg_catalog table
pg_attrdef pg_catalog table
pg_attribute pg_catalog table
pg_auth_members pg_catalog table
×
pg_authid pg_catalog table
×
pg_available_extension_versions pg_catalog view
×
pg_available_extensions pg_catalog view
×
pg_bar_ddllog pg_catalog table
×
pg_bar_repos pg_catalog table
×
pg_bar_state pg_catalog table
×
pg_cast pg_catalog table
pg_class pg_catalog table
pg_collation pg_catalog table
×
pg_config pg_catalog view
×
pg_conf pg_catalog table
×
pg_constraint pg_catalog table
pg_conversion pg_catalog table
pg_cursors pg_catalog view
×
pg_database pg_catalog table
pg_database_extended pg_catalog table
×
pg_database_info pg_catalog view
×
pg_db_role_setting pg_catalog table
×
pg_default_acl pg_catalog table
pg_depend pg_catalog table
pg_description pg_catalog table
pg_enum pg_catalog table
×
pg_event_trigger pg_catalog table
×
pg_extension pg_catalog table
×
pg_file_settings pg_catalog view
×
pg_foreign_data_wrapper pg_catalog table
×
pg_foreign_server pg_catalog table
×
pg_foreign_table pg_catalog table
×
pg_group pg_catalog view
pg_group_extended pg_catalog table
×
pg_highwatermark pg_catalog table
×
pg_index pg_catalog table
pg_indexes pg_catalog view
pg_inherits pg_catalog table
pg_init_privs pg_catalog table
×
pg_language pg_catalog table
pg_largeobject pg_catalog table
pg_largeobject_metadata pg_catalog table
×
pg_library pg_catalog table
×
pg_listener pg_catalog table
×
pg_locks pg_catalog view
pg_matviews pg_catalog view
×
pg_namespace pg_catalog table
pg_opclass pg_catalog table
pg_operator pg_catalog table
pg_opfamily pg_catalog table
×
pg_pltemplate pg_catalog table
×
pg_policies pg_catalog view
×
pg_policy pg_catalog table
×
pg_prepared_statements pg_catalog view
×
pg_prepared_xacts pg_catalog view
×
pg_proc pg_catalog table
pg_range pg_catalog table
×
pg_replication_origin pg_catalog table
×
pg_replication_origin_status pg_catalog view
×
pg_replication_slots pg_catalog view
×
pg_resize pg_catalog table
×
pg_rewrite pg_catalog table
pg_roles pg_catalog view
×
pg_rules pg_catalog view
pg_seclabel pg_catalog table
×
pg_seclabels pg_catalog view
×
pg_settings pg_catalog view
pg_shadow pg_catalog view
pg_shadow_extended pg_catalog table
×
pg_shdepend pg_catalog table
pg_shdescription pg_catalog table
×
pg_shseclabel pg_catalog table
×
pg_stat_activity pg_catalog view
pg_stat_all_indexes pg_catalog view
pg_stat_all_tables pg_catalog view
pg_stat_archiver pg_catalog view
×
pg_stat_bgwriter pg_catalog view
×
pg_stat_database pg_catalog view
pg_stat_database_conflicts pg_catalog view
×
pg_stat_progress_vacuum pg_catalog view
×
pg_stat_replication pg_catalog view
×
pg_stat_ssl pg_catalog view
×
pg_stat_sys_indexes pg_catalog view
pg_stat_sys_tables pg_catalog view
pg_stat_user_functions pg_catalog view
×
pg_stat_user_indexes pg_catalog view
pg_stat_user_tables pg_catalog view
pg_stat_wal_receiver pg_catalog view
×
pg_stat_xact_all_tables pg_catalog view
×
pg_stat_xact_sys_tables pg_catalog view
×
pg_stat_xact_user_functions pg_catalog view
×
pg_stat_xact_user_tables pg_catalog view
×
pg_statio_all_indexes pg_catalog view
pg_statio_all_sequences pg_catalog view
pg_statio_all_tables pg_catalog view
pg_statio_sys_indexes pg_catalog view
pg_statio_sys_sequences pg_catalog view
pg_statio_sys_tables pg_catalog view
pg_statio_user_indexes pg_catalog view
pg_statio_user_sequences pg_catalog view
pg_statio_user_tables pg_catalog view
pg_statistic pg_catalog table
pg_statistic_indicator pg_catalog table
×
pg_statistic_multicol pg_catalog table
×
pg_stats pg_catalog view
pg_tables pg_catalog view
pg_table_def pg_catalog view
×
pg_tablespace pg_catalog table
pg_timezone_abbrevs pg_catalog view
×
pg_timezone_names pg_catalog view
×
pg_transform pg_catalog table
×
pg_trigger pg_catalog table
pg_ts_config pg_catalog table
×
pg_ts_config_map pg_catalog table
×
pg_ts_dict pg_catalog table
×
pg_ts_parser pg_catalog table
×
pg_ts_template pg_catalog table
×
pg_type pg_catalog table
pg_udf pg_catalog table
×
pg_user pg_catalog view
pg_user_info pg_catalog view
×
pg_user_mapping pg_catalog table
×
pg_user_mappings pg_catalog view
×
pg_views pg_catalog view
stl_aggr pg_catalog table
×
stl_alert_event_log pg_catalog table
×
stl_analyze pg_catalog table
×
stl_bcast pg_catalog table
×
stl_commit_stats pg_catalog table
×
stl_connection_log pg_catalog table
×
stl_ddltext pg_catalog table
×
stl_delete pg_catalog table
×
stl_dist pg_catalog table
×
stl_error pg_catalog table
×
stl_explain pg_catalog table
×
stl_file_scan pg_catalog table
×
stl_hash pg_catalog table
×
stl_hashjoin pg_catalog table
×
stl_insert pg_catalog table
×
stl_limit pg_catalog table
×
stl_load_commits pg_catalog table
×
stl_load_errors pg_catalog table
×
stl_loaderror_detail pg_catalog table
×
stl_merge pg_catalog table
×
stl_mergejoin pg_catalog table
×
stl_nestloop pg_catalog table
×
stl_parse pg_catalog table
×
stl_plan_info pg_catalog table
×
stl_project pg_catalog table
×
stl_query pg_catalog table
×
stl_querytext pg_catalog table
×
stl_replacements pg_catalog table
×
stl_restarted_sessions pg_catalog table
×
stl_return pg_catalog table
×
stl_s3client pg_catalog table
×
stl_s3client_error pg_catalog table
×
stl_save pg_catalog table
×
stl_scan pg_catalog table
×
stl_sessions pg_catalog table
×
stl_sort pg_catalog table
×
stl_sshclient_error pg_catalog table
×
stl_stream_segs pg_catalog table
×
stl_tr_conflict pg_catalog table
×
stl_undone pg_catalog table
×
stl_unique pg_catalog table
×
stl_unload_log pg_catalog table
×
stl_userlog pg_catalog table
×
stl_utilitytext pg_catalog table
×
stl_vacuum pg_catalog table
×
stl_warning pg_catalog table
×
stl_window pg_catalog table
×
stl_wlm_error pg_catalog table
×
stl_wlm_query pg_catalog table
×
stv_active_cursors pg_catalog table
×
stv_blocklist pg_catalog table
×
stv_cursor_configuration pg_catalog table
×
stv_exec_state pg_catalog table
×
stv_inflight pg_catalog table
×
stv_load_state pg_catalog table
×
stv_locks pg_catalog table
×
stv_partitions pg_catalog table
×
stv_recents pg_catalog table
×
stv_sessions pg_catalog table
×
stv_slices pg_catalog table
×
stv_startup_recovery_state pg_catalog table
×
stv_tbl_perm pg_catalog table
×
stv_tbl_trans pg_catalog table
×
stv_wlm_classification_config pg_catalog table
×
stv_wlm_query_queue_state pg_catalog table
×
stv_wlm_query_state pg_catalog table
×
stv_wlm_query_task_state pg_catalog table
×
stv_wlm_service_class_config pg_catalog table
×
stv_wlm_service_class_state pg_catalog table
×
svl_compile pg_catalog view
×
svl_qlog pg_catalog view
×
svl_query_queue_info pg_catalog view
×
svl_query_report pg_catalog view
×
svl_query_summary pg_catalog view
×
svl_statementtext pg_catalog view
×
svl_udf_log pg_catalog view
×
svl_vacuum_percentage pg_catalog view
×
svv_diskusage pg_catalog view
×
svv_interleaved_columns pg_catalog view
×
svv_query_inflight pg_catalog view
×
svv_query_state pg_catalog view
×
svv_restore_table_state pg_catalog view
×
svv_table_info pg_catalog view
×
svv_transactions pg_catalog view
×
svv_vacuum_progress pg_catalog view
×
svv_vacuum_summary pg_catalog view
×

※ ○ =「存在する」/ × =「存在しない」の意で使用(実際に使用されて いるかどうかは未検証

※ Redshift独自(stl_*, stv_*, sysdata_*)のリファレンスがないテーブルは省略

※ Redshiftのテスト用と思われるテーブル(pg_test)は省略

まとめ(分かったこと)

  • PostgreSQLではinformation_schema(SQL標準)とpg_catalog(PostgreSQL用)でシステム情報を管理
      →selectするときは、pg_catalogのテーブル見に行った方が楽(スキーマ指定不要なので
  • プレフィクスが s*l_, s*v_なら、Redshift独自のテーブル&ビュー
      →AWSのリファレンスを参照する
  • Redshiftでは統計情報が拡張されてる
      →最適化のためのシステム情報が豊富
  • Redshiftではログ情報などもかなりたくさん管理している
      →デバッグ情報も多い