redshift のAuto VACUUM について

• SVV_TABLE_INFO の unsorted(%) と vacuum_sort_benefit(%) の値が大きい場合に VACUUM SORT を実行するとクエリ性能が改善する可能性がある。
  • unsorted(%): ソートされていない割合
  • vacuum_sort_benefit(%): クエリのアクセステーブルへの選択率が高い、多数のクエリがテーブルにアクセスしていると値が大きくなる
• VACUUM が実行される閾値（TO threshold PERCENT で指定可能）はデフォルト95%で、ソート済み行が95%超の場合、 VACUUM SORT がスキップされ、削除対象としてマークされていない行の割合が 95% 超の場合、VACUUM DELETE がスキップされる。

列名	データ型	説明
unsorted	numeric(5,2)	テーブル内のソートされていない行の割合。
estimated_visible_rows	numeric(38,0)	テーブル内の予測された行。この値には、削除対象としてマークされた行は含まれません。
vacuum_sort_benefit	numeric(12,2)	VACUUM ソートを実行した場合に推定されるスキャンクエリパフォーマンスの最大改善率 (%)。

SVV_TABLE_INFO - Amazon Redshift

Amazon Redshift は、各テーブルのソートキーを使用するスキャンクエリを追跡します。Amazon Redshift は、テーブルが完全にソートされている場合、各テーブルのデータのスキャンやフィルターの最大改善率を見積ります。この見積もりは、vacuum_sort_benefitのSVV_TABLE_INFO列で表示されます。この列は、unsorted列と一緒に使用することができ、クエリの際、いつテーブルで VACUUM SORT を手動で実行したら効率的かを判断します。unsorted列は、テーブルの物理的なソート順を反映しています。vacuum_sort_benefit 列は、VACUUM SORT を手動で実行することでソートによるテーブルへの影響を特定します。

例えば、次のクエリを考えてみます。

select "table", unsorted,vacuum_sort_benefit from svv_table_info order by 1;

 table | unsorted | vacuum_sort_benefit 
-------+----------+---------------------
 sales |    85.71 |                5.00
 event |    45.24 |               67.00

「sales」というテーブルの場合、86% が物理的にソートされていなくても、86% ソートされていないテーブルからのクエリ実行の影響は、5% だけとなります。理由としては、クエリがテーブルの一部にしかアクセスしていないか、ごく少数のクエリがテーブルにアクセスしたかのどちらかとなります。「event」というテーブルの場合、テーブルは、45% が物理的にソートされていません。ですが、67% のクエリ実行の影響は、クエリがテーブルの大部分にアクセスしたか、テーブルにアクセスしたクエリが多かったことを表しています。「event」というテーブルには、VACUUM SORT の実行が潜在的に有効となります。

テーブルのバキューム処理 - Amazon Redshift

TO threshold PERCENT
VACUUM が削除フェーズをスキップする最低のしきい値、および削除フェーズでスペースを再利用する対象となるしきい値を指定する句です。ソートしきい値は、バキューム処理の前の指定されたテーブルにおけるソート済みの行の合計の割合です。 削除しきい値は、バキューム処理後に削除対象としてマークされていない行の合計の最低割合です。

VACUUM は、テーブルのソート済みの行の割合がソートしきい値以下の場合のみ行を再ソートするため、Amazon Redshift は、バキューム処理の時間を大幅に削減できます。同様に、VACUUM は削除対象としてマークされた行のスペースを 100 パーセント再利用するよう制約を受けない場合、削除対象としてマークされた行を数行のみ含むブロックの書き換えをスキップできます。

例えば、しきい値に 75 を指定すると、テーブルの行の 75 パーセント以上がすでにソート済みの場合、VACUUM はソートフェーズをスキップします。削除フェーズでは、バキューム処理後に削除対象としてマークされていない行がテーブルに 少なくとも 75 パーセントあるようなテーブルを VACUUM はディスク領域の再利用の対象として設定します。しきい値の値は、0 から 100 の間の整数でなければなりません。デフォルトは 95 です。100 という値を指定すると、VACUUM はすでに完全にソートされていない限り常にテーブルをソートし、削除対象としてマークされたすべての行のスペースを再利用します。0 という値を指定すると、VACUUM は一切テーブルをソートせず、一切スペースを再利用しません。

TO threshold PERCENT パラメータを含める場合は、テーブルの名前も指定する必要があります。テーブル名を省略すると、VACUUM は失敗します。

TO threshold PERCENT パラメータは REINDEX と併用できません。

VACUUM - Amazon Redshift

自動VACUUM 実行履歴は SYS_VACUUM_HISTORY で確認できる。

参考

• SVV_TABLE_INFO
• SYS_VACUUM_HISTORY
• Amazon Redshift でのバキュームソートの問題を解決する | AWS re:Post
• Amazon Redshift の新機能「Auto Vacuum & Auto Sort」の徹底検証 #reinvent | DevelopersIO

Redshift のソートキーについて

なるべくprefixのついていない文字列を選ぶ(先頭8バイトしかソートに使われない)

Amazon Redshiftの仕様を調べてみた | フューチャー技術ブログ

• The values within the block are prefixed with a string 8 bytes or longer.
  • The minimum and maximum values in the block header allow for 8 bytes of data to track each value. For long strings, we take the first 8 characters as a prefix.
  • Example: I filter on a column that stores URLs that are always prefixed with: ‘https://’ so the minimum and maximum values are always the same prefix, regardless of the rest of the URL.

Amazon Redshift Engineering’s Advanced Table Design Playbook: Compound and Interleaved Sort Keys | AWS Big Data Blog

Redshift で特定のテーブルにアクセスしているクエリの情報を取得する

select a.*, c.elapsed_time, c.queue_time, c. execution_time, c.compile_time, c.planning_time, c.lock_wait_time, c.start_time, c.end_time
from sys_query_detail a, svv_table_info b, sys_query_history c
where a.table_id = b.table_id and
a.query_id = c_query_id and
b.table = '<table_name>'
order by query_id, stream_id, segment_id, step_id;

参考

• SYS_QUERY_HISTORY - Amazon Redshift
• SYS_QUERY_DETAIL - Amazon Redshift
• SVV_TABLE_INFO - Amazon Redshift

Redshift で最小権限でユーザーにシステムテーブルの情報を参照させる

やりたいこと

• 元のシステムテーブルへアクセスさせずに、ユーザーにシステムテーブルの情報を参照させたい。
• SYSLOG ACCESS UNRESTRICTED 権限やACCESS SYSTEM TABLE 権限を付与すると、見せたい情報以外もユーザーが参照できてしまう。

ストアドプロシージャ方式

設定手順

• スーパーユーザーでログインする

psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439"

• 一般ユーザーを作成する。

create user test_user_a with password 'Password1';

• 一般ユーザー向けシステムテーブル公開用のスキーマを作成する。

create schema if not exists  sys_for_users;

• ストアドプロシージャを作成する。

CREATE OR REPLACE PROCEDURE sys_for_users.relation_write_privs_sp(rs_out INOUT REFCURSOR)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
	OPEN rs_out FOR SELECT
 	   a.namespace_name,
 	   a.relation_name,
 	   CASE
	        WHEN a.identity_type = 'user' THEN a.identity_name
 	       WHEN a.identity_type = 'role' THEN b.user_name
 	   END AS grantee_name,
 	   a.privilege_type
	FROM pg_catalog.SVV_RELATION_PRIVILEGES a
	LEFT JOIN pg_catalog.SVV_USER_GRANTS b
	ON a.identity_name = b.role_name
	WHERE a.privilege_type NOT IN ('SELECT','REFERENCES')
	GROUP BY 1,2,3,4;
END;
$$;

• 一般ユーザーにシステムテーブル公開用のスキーマへの Usage 権を付与する。

grant usage on schema sys_for_users to test_user_a;
-- revoke all on schema sys_for_users from test_user_a;

• 一般ユーザーにストアドプロシージャの実行権を付与する。

grant execute on procedure sys_for_users.relation_write_privs_sp(inout refcursor) to test_user_a;

検証結果

• 一般ユーザーでログインする。

psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=tesst_user_a dbname=dev port=5439"

• 一般ユーザーでプロシージャを実行する。

begin;
	call sys_for_users.relation_write_privs_sp( 'mycursor');
	fetch all from mycursor;
end;


↓実行結果: ★ 'SYSLOG ACCESS UNRESTRICTED', 'ACCESS SYSTEM TABLE' 権限がないと他ユーザーがどのテーブルに書込み権限があるか確認できない
 namespace_name | relation_name | grantee_name | privilege_type 
----------------+---------------+--------------+----------------
 pg_catalog     | pg_settings   |              | UPDATE
(1 row)

• システムテーブルを参照できないこと。

select * from svv_relation_privileges where namespace_name not in ('information_schema', 'pg_catalog');

 namespace_name | relation_name | privilege_type | identity_id | identity_name | identity_type | admin_option 
----------------+---------------+----------------+-------------+---------------+---------------+--------------
(0 rows)

select * from svv_user_grants;

 user_id | user_name | role_id | role_name | admin_option 
---------+-----------+---------+-----------+--------------
(0 rows)

View方式

設定手順

• スーパーユーザーでログインする

psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439"

• 一般ユーザーを作成する。

create user test_user_a with password 'Password1';

• 一般ユーザー向けシステムテーブル公開用のスキーマを作成する。

create schema if not exists  sys_for_users;

• View を作成する。

CREATE OR REPLACE VIEW sys_for_users.relation_write_privs_view
AS 
SELECT
    a.namespace_name,
    a.relation_name,
    CASE
        WHEN a.identity_type = 'user' THEN a.identity_name
        WHEN a.identity_type = 'role' THEN b.user_name
    END AS grantee_name,
    a.privilege_type
FROM pg_catalog.SVV_RELATION_PRIVILEGES a
LEFT JOIN pg_catalog.SVV_USER_GRANTS b
ON a.identity_name = b.role_name
WHERE a.privilege_type NOT IN ('SELECT','REFERENCES')
GROUP BY 1,2,3,4
WITH NO SCHEMA BINDING;

• 一般ユーザーにシステムテーブル公開用のスキーマへの Usage 権を付与する。

grant usage on schema sys_for_users to test_user_a;

• 一般ユーザーにストアドプロシージャの実行権を付与する。

grant select on sys_for_users.relation_write_privs_view to test_user_a;

検証結果

• 一般ユーザーでログインする。

psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=tesst_user_a dbname=dev port=5439"

• 一般ユーザーで View を参照する。

select * from sys_for_users.relation_write_privs_view;


↓実行結果: ★ 'SYSLOG ACCESS UNRESTRICTED', 'ACCESS SYSTEM TABLE' 権限がないと他ユーザーがどのテーブルに書込み権限があるか確認できない
 namespace_name | relation_name | grantee_name | privilege_type 
----------------+---------------+--------------+----------------
 pg_catalog     | pg_settings   |              | UPDATE
(1 row)

• システムテーブルを参照できないこと。

select * from svv_relation_privileges where namespace_name not in ('information_schema', 'pg_catalog');

 namespace_name |       relation_name       | privilege_type | identity_id | identity_name | identity_type | admin_option 
----------------+---------------------------+----------------+-------------+---------------+---------------+--------------
 sys_for_users  | relation_write_privs_view | SELECT         |         212 | test_user_a   | user          | f
(1 row)

select * from svv_user_grants;

 user_id | user_name | role_id | role_name | admin_option 
---------+-----------+---------+-----------+--------------
(0 rows)

補足

• 個別のシステムテーブル・ビュー単位でユーザーにアクセス権を付与することはできない。
• SYSLOG ACCESS UNRESTRICTED で権限を付与するとアクセス許可したい対象のシステムテーブル以外も参照できるようになる。

ALTER USER <user_name> WITH SYSLOG ACCESS UNRESTRICTED;
-- ALTER USER <user_name> WITH SYSLOG ACCESS RESTRICTED;

• ACCESS SYSTEM TABLE で権限を付与するとアクセス許可したい対象のシステムテーブル以外も参照できるようになる。

GRANT ACCESS SYSTEM TABLE TO ROLE <role_name>;
GRANT ROLE <role_name> TO <username>;
-- REVOKE ROLE <role_name> FROM <username>;
-- REVOKE ACCESS SYSTEM TABLE TO ROLE <role_name>;

参考

• ストアドプロシージャから結果セットを返す - Amazon Redshift

SYSLOG ACCESS RESTRICTED アクセス許可を持つ通常のユーザーは、ユーザーが表示できるシステムテーブルとビューで自分が生成した行のみを表示できます。デフォルトは RESTRICTED です。
SYSLOG ACCESS UNRESTRICTED アクセス許可を持つ通常のユーザーは、ユーザーが表示できるシステムテーブルとビューのすべての行 (別のユーザーが生成した行を含む) を表示できます。UNRESTRICTED を指定しても、スーパーユーザーが表示可能なテーブルへのアクセス権が、通常のユーザーに付与されるわけではありません。スーパーユーザーが表示可能なテーブルを表示できるのはスーパーユーザーだけです。

ALTER USER - Amazon Redshift

There are 2 options to do this, use role name 'test_role' as example:

grant ACCESS SYSTEM TABLE to role ;
System permissions for RBAC - https://docs.aws.amazon.com/redshift/latest/dg/r_roles-system-privileges.html

grant role sys:monitor to role ;
This system defined role sys:monitor has the permission to access catalog or system tables. Amazon Redshift system-defined roles - https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html

Allow Amazon Redshift users to view system tables from other users | AWS re:Post

AWS User Notificationsで Redshift の AWS Health Event を通知する

設定手順

• [AWS User Notifications] - [通知設定を作成] -
  • 一般的な通知を有効にするサービスを選択します: Health
  • リージョン: Tokyo
  • E メール: メールアドレスを入力
• [AWS User Notifications] - [通知設定] で作成した通知を選択して、[編集]
  • 高度なフィルター (オプション) で Redshift と Redshift-Serverless の通知に絞る

{
  "source": [
    "aws.redshift",
    "aws.redshift-serverless"
  ]
}

参考

• [新サービス] AWS User Notificationsで通知設定を試してみた | DevelopersIO
• 【図解あり】AWS Healthイベント通知をUser Notificationsで最適化する - ForgeVision Engineer Blog

Redshift Serverless を使ってみる

Redshift Serverless セットアップ

ワークループを作成

• ワークグループの名前: dwh-serverless-cnt-prod
• Performance and cost controls : Price-performance target(50)
• 最大容量: 16 (Memory: 256 GB = 16GB * 16)
• Track: Trailing
• Virtual Private Cloud (VPC): Provisioned クラスターと同じ VPC を選択
• VPC セキュリティグループ: Provisioned クラスターと同じセキュリティグループを選択
• サブネット: 3 AZ のサブネットを選択する
• 拡張された VPC のルーティング: 拡張された VPC ルーティングをオンにする
• 名前空間: dwh-serverless-cnt-prod
• データベース名とパスワード
  • データベース名: dev
  • 管理者ユーザー認証情報をカスタマイズ
  • 管理者ユーザー名: cntadmin
  • 管理者パスワードを手動で追加する
  • 管理者ユーザーパスワード: <任意のパスワード>
• 関連付けられた IAM ロール: DWH_CNT_PROD_IAMRole <-- Provisioned と同じ IAM ロールを関連づける
• 暗号化設定をカスタマイズする (高度): チェック
  • AWS カスタマーマネージドキーを選択: KMS CMK を選択
• 監査ログ記録
  • ユーザーログ: チェック
  • 接続ログ: チェック
  • ユーザーアクティビティログ: チェック

PostgreSQL でトリガーの引き金となったクエリと同じトランザクションとして扱われる

すべての場合において、トリガはトリガが引き金となった文と同じトランザクションの一部として実行されるため、文またはトリガのいずれかがエラーを引き起こした場合、両方の結果がロールバックされます。

37.1. トリガ動作の概要