この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは。三上です。
こないだ飲み会でハイボール縛りに合いました。。(なま中、注文禁止だなんて・・><。
はじめに(当エントリを書くに至った経緯)
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 の 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章 情報スキーマ
情報スキーマは、現在のデータベースで定義されたオブジェクトについての情報を持つビューの集合から構成されます。
情報スキーマ自身は、
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)のシステムテーブル一覧
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ではログ情報などもかなりたくさん管理している
- →デバッグ情報も多い