🐘

マネージドPostgreSQLの実現に向けたPostgreSQL機能向上 - 補足資料

2023/12/13に公開

 はじめにPostgreSQL Conference Japan 2023で本記事のタイトルで講演を行いました。

資料は以下になります。
この講演では、PostgreSQLの機能と、それらの機能がPostgreSQL本体に入るまでクラウドのマネージドPostgreSQLではどのような対応がされているか、ということをお話しました。

講演ではすべてを話すことはできなかったので、表にして載せておきます。

 検証環境

製品名
バージョン


Amazon RDS for PostgreSQL
PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

Azure Database for PostgreSQL（フレキシブルサーバ）
PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

Cloud SQL for PostgreSQL
PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit


 検証結果
 SQLコマンド

SQL
AWS
Azure
GCP


ANALYZE (to global catalogs)
Yes
No
No

ANALYZE (to local catalogs)
Yes
Yes
Yes

CHECKPOINT
Yes
Yes
No

CLUSTER (to catalogs)
No
No
No

CREATE ACCESS METHOD
No
No
No

CREATE EVENT TRIGGER
Yes
Yes
Yes

CREATE EXTENSION (not trusted)
Yes
Yes
Yes

CREATE FOREIGN DATA WRAPPER
No
No
No

CREATE FUNCTION LANGUAGE C
No
No
No

CREATE LANGUAGE
No
No
No

CREATE OPERATOR CLASS
Yes
No
No

CREATE OPERATOR FAMILY
Yes
No
No

CREATE ROLE (SUPERUSER, NOSUPERUSER)
No
No
No

CREATE ROLE (REPLICATION, NOREPLICATION)
No
Yes
Yes

CREATE ROLE (BYPASSRLS, NOBYPASSRLS)
Yes
No
Yes

CREATE TABLESPACE
Yes
No
No

CREATE TEXT SEARCH PARSER
No
No
No

CREATE TEXT SEARCH TEMPLATE
No
No
No

CREATE TYPE (Base Types)
No
No
No

GRANT <any> to <superuser>
No
Yes
Yes

GRANT <superuser> to <any>
No
No
No

REINDEX (to catalogs)
Yes
No
No

REVOKE <any> FROM <superuser>
No
Yes
Yes

REVOKE <superuser> FROM <any>
No
No
Yes

SET ROLE (to SUPERUSER)
No
No
No

SET SESSION AUTHORIZATION
Yes
No
No

VACUUM (to global catalogs)
Yes
No
No

VACUUM (to local catalogs)
Yes
Yes
Yes


 GUCパラメータ（context=superuserのみ）

GUCパラメータ
AWS
Azure
GCP


allow_in_place_tablespaces
No
No
No

allow_system_table_mods
Yes
No
No

backtrace_functions
No
No
No

commit_delay
Yes
No
No

compute_query_id
No
No
No

deadlock_timeout
Yes
No
No

debug_discard_caches
No
No
No

dynamic_library_path
No
No
No

ignore_checksum_failure
No
No
No

jit_dump_bitcode
No
No
No

lc_messages
Yes
No
No

lo_compat_privileges
No
No
No

log_duration
Yes
No
No

log_error_verbosity
Yes
No
No

log_executor_stats
Yes
No
No

log_lock_waits
Yes
No
No

log_min_duration_sample
Yes
No
No

log_min_duration_statement
Yes
No
No

log_min_error_statement
Yes
No
No

log_min_messages
Yes
No
No

log_parameter_max_length
No
No
No

log_parser_stats
Yes
No
No

log_planner_stats
Yes
No
No

log_replication_commands
Yes
No
No

log_statement
Yes
No
No

log_statement_sample_rate
Yes
No
No

log_statement_stats
Yes
No
No

log_temp_files
Yes
No
No

log_transaction_sample_rate
Yes
No
No

max_stack_depth
No
No
No

session_preload_libraries
No
No
No

session_replication_role
Yes
Yes
No

temp_file_limit
Yes
No
No

track_activities
Yes
No
No

track_counts
Yes
No
No

track_functions
Yes
No
No

track_io_timing
Yes
No
No

track_wal_io_timing
Yes
No
No

update_process_title
No
No
No

wal_compression
No
No
No

wal_consistency_checking
No
No
No

wal_init_zero
No
No
No

wal_recycle
No
No
No

zero_damaged_pages
No
No
No


 参考https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.FeatureSupport.html

https://learn.microsoft.com/ja-jp/azure/postgresql/flexible-server/how-to-bulk-load-data#drop-constraints

https://cloud.google.com/sql/docs/postgres/extensions?hl=ja

https://cloud.google.com/sql/docs/postgres/users?hl=ja#superuser_restrictions

製品名	バージョン
Amazon RDS for PostgreSQL	PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
Azure Database for PostgreSQL（フレキシブルサーバ）	PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
Cloud SQL for PostgreSQL	PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit

SQL	AWS	Azure	GCP
ANALYZE (to global catalogs)	Yes	No	No
ANALYZE (to local catalogs)	Yes	Yes	Yes
CHECKPOINT	Yes	Yes	No
CLUSTER (to catalogs)	No	No	No
CREATE ACCESS METHOD	No	No	No
CREATE EVENT TRIGGER	Yes	Yes	Yes
CREATE EXTENSION (not trusted)	Yes	Yes	Yes
CREATE FOREIGN DATA WRAPPER	No	No	No
CREATE FUNCTION LANGUAGE C	No	No	No
CREATE LANGUAGE	No	No	No
CREATE OPERATOR CLASS	Yes	No	No
CREATE OPERATOR FAMILY	Yes	No	No
CREATE ROLE (SUPERUSER, NOSUPERUSER)	No	No	No
CREATE ROLE (REPLICATION, NOREPLICATION)	No	Yes	Yes
CREATE ROLE (BYPASSRLS, NOBYPASSRLS)	Yes	No	Yes
CREATE TABLESPACE	Yes	No	No
CREATE TEXT SEARCH PARSER	No	No	No
CREATE TEXT SEARCH TEMPLATE	No	No	No
CREATE TYPE (Base Types)	No	No	No
GRANT <any> to <superuser>	No	Yes	Yes
GRANT <superuser> to <any>	No	No	No
REINDEX (to catalogs)	Yes	No	No
REVOKE <any> FROM <superuser>	No	Yes	Yes
REVOKE <superuser> FROM <any>	No	No	Yes
SET ROLE (to SUPERUSER)	No	No	No
SET SESSION AUTHORIZATION	Yes	No	No
VACUUM (to global catalogs)	Yes	No	No
VACUUM (to local catalogs)	Yes	Yes	Yes

GUCパラメータ	AWS	Azure	GCP
allow_in_place_tablespaces	No	No	No
allow_system_table_mods	Yes	No	No
backtrace_functions	No	No	No
commit_delay	Yes	No	No
compute_query_id	No	No	No
deadlock_timeout	Yes	No	No
debug_discard_caches	No	No	No
dynamic_library_path	No	No	No
ignore_checksum_failure	No	No	No
jit_dump_bitcode	No	No	No
lc_messages	Yes	No	No
lo_compat_privileges	No	No	No
log_duration	Yes	No	No
log_error_verbosity	Yes	No	No
log_executor_stats	Yes	No	No
log_lock_waits	Yes	No	No
log_min_duration_sample	Yes	No	No
log_min_duration_statement	Yes	No	No
log_min_error_statement	Yes	No	No
log_min_messages	Yes	No	No
log_parameter_max_length	No	No	No
log_parser_stats	Yes	No	No
log_planner_stats	Yes	No	No
log_replication_commands	Yes	No	No
log_statement	Yes	No	No
log_statement_sample_rate	Yes	No	No
log_statement_stats	Yes	No	No
log_temp_files	Yes	No	No
log_transaction_sample_rate	Yes	No	No
max_stack_depth	No	No	No
session_preload_libraries	No	No	No
session_replication_role	Yes	Yes	No
temp_file_limit	Yes	No	No
track_activities	Yes	No	No
track_counts	Yes	No	No
track_functions	Yes	No	No
track_io_timing	Yes	No	No
track_wal_io_timing	Yes	No	No
update_process_title	No	No	No
wal_compression	No	No	No
wal_consistency_checking	No	No	No
wal_init_zero	No	No	No
wal_recycle	No	No	No
zero_damaged_pages	No	No	No

はじめに

検証環境

検証結果

SQLコマンド

GUCパラメータ（context=superuserのみ）

参考

Discussion