AWS Aurora PostgreSQL(11.6)の拡張一覧（メモ）

# AWS Aurora PostgreSQL(11.6)の拡張一覧（メモ）

2020/3/28時点で最新の11.6の拡張一覧を確認。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/179607/edc130e5-af2f-2c1d-52d2-f1f7d7491b30.png)

オンプレミスで構築したPostgreSQLと異なり、Auroraでは自分で拡張モジュールを追加することができません。その代わり代表的なモジュールは最初から利用できるようになっています。


```
postgres=> select * from pg_available_extensions;
             name             | default_version | installed_version |                                                       comment      
                                                 
------------------------------+-----------------+-------------------+--------------------------------------------------------------------
-------------------------------------------------
 dblink                       | 1.2             |                   | connect to other PostgreSQL databases from within a database
 pg_repack                    | 1.4.4           |                   | Reorganize tables in PostgreSQL databases with minimal locks
 hstore                       | 1.5             |                   | data type for storing sets of (key, value) pairs
 postgis_tiger_geocoder       | 2.5.2           |                   | PostGIS tiger geocoder and reverse geocoder
 pg_visibility                | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 orafce                       | 3.7             |                   | Functions and operators that emulate a subset of functions and pack
ages from the Oracle RDBMS
 prefix                       | 1.2.0           |                   | Prefix Range module for PostgreSQL
 isn                          | 1.2             |                   | data types for international product numbering standards
 pg_prewarm                   | 1.2             |                   | prewarm relation data
 pgrowlocks                   | 1.2             |                   | show row-level locking information
 aws_s3                       | 1.1             |                   | AWS S3 extension for importing data from S3
 pg_freespacemap              | 1.2             |                   | examine the free space map (FSM)
 pgaudit                      | 1.3             |                   | provides auditing functionality
 address_standardizer         | 2.5.2           |                   | Used to parse an address into constituent elements. Generally used 
to support geocoding address normalization step.
 aws_commons                  | 1.1             |                   | Common data types across AWS services
 ip4r                         | 2.2             |                   | 
 test_parser                  | 1.0             |                   | example of a custom parser for full-text search
 plv8                         | 2.3.8           |                   | PL/JavaScript (v8) trusted procedural language
 unaccent                     | 1.1             |                   | text search dictionary that removes accents
 uuid-ossp                    | 1.1             |                   | generate universally unique identifiers (UUIDs)
 pg_stat_statements           | 1.6             |                   | track execution statistics of all SQL statements executed
 pg_similarity                | 1.0             |                   | support similarity queries
 postgis                      | 2.5.2           |                   | PostGIS geometry, geography, and raster spatial types and functions
 tablefunc                    | 1.0             |                   | functions that manipulate whole tables, including crosstab
 rds_activity_stream          | 1.3             |                   | RDS ACTIVITY Agent
 dict_xsyn                    | 1.0             |                   | text search dictionary template for extended synonym processing
 pg_hint_plan                 | 1.3.4           |                   | 
 postgres_fdw                 | 1.0             |                   | foreign-data wrapper for remote PostgreSQL servers
 fuzzystrmatch                | 1.1             |                   | determine similarities and distance between strings
 hstore_plperl                | 1.0             |                   | transform between hstore and plperl
 tsm_system_rows              | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 pgrouting                    | 2.6.1           |                   | pgRouting Extension
 bloom                        | 1.0             |                   | bloom access method - signature file based index
 jsonb_plperl                 | 1.0             |                   | transform between jsonb and plperl
 plls                         | 2.3.8           |                   | PL/LiveScript (v8) trusted procedural language
 pgstattuple                  | 1.5             |                   | show tuple-level statistics
 intarray                     | 1.2             |                   | functions, operators, and index support for 1-D arrays of integers
 intagg                       | 1.1             |                   | integer aggregator and enumerator (obsolete)
 log_fdw                      | 1.1             |                   | foreign-data wrapper for Postgres log file access
 plcoffee                     | 2.3.8           |                   | PL/CoffeeScript (v8) trusted procedural language
 cube                         | 1.4             |                   | data type for multidimensional cubes
 tsm_system_time              | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 pltcl                        | 1.0             |                   | PL/Tcl procedural language
 hll                          | 2.11            |                   | type for storing hyperloglog data
 earthdistance                | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 postgis_topology             | 2.5.2           |                   | PostGIS topology spatial types and functions
 dict_int                     | 1.0             |                   | text search dictionary template for integers
 pg_buffercache               | 1.3             |                   | examine the shared buffer cache
 plprofiler                   | 4.1             |                   | server-side support for profiling PL/pgSQL functions
 aws_ml                       | 1.0             |                   | ml integration
 pg_trgm                      | 1.4             |                   | text similarity measurement and index searching based on trigrams
 plperl                       | 1.0             |                   | PL/Perl procedural language
 amcheck                      | 1.1             |                   | functions for verifying relation integrity
 aurora_stat_utils            | 1.0             |                   | Statistics utility functions
 citext                       | 1.5             |                   | data type for case-insensitive character strings
 ltree                        | 1.1             |                   | data type for hierarchical tree-like structures
 pgcrypto                     | 1.3             |                   | cryptographic functions
 sslinfo                      | 1.2             |                   | information about SSL certificates
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 address_standardizer_data_us | 2.5.2           |                   | Address Standardizer US dataset example
 btree_gist                   | 1.5             |                   | support for indexing common datatypes in GiST
 apg_plan_mgmt                | 2.0             |                   | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 btree_gin                    | 1.3             |                   | support for indexing common datatypes in GIN
 pgtap                        | 1.0.0           |                   | Unit testing for PostgreSQL
(64 rows)
```

shared_preload_librariesを見るとデフォルトではpg_stat_statementsだけですが、他にもauto_explain等もありますね。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/179607/613ad0d0-5f00-e1fd-8753-8a98e1888842.png)


pg_repack, pg_hint_plan, pgstattuple, pg_buffercache, pgaudit, pg_stat_statements, auto_explainなどよく使う拡張は一通り揃っている。
私が普段使うことがある拡張で入っていないのはpg_statsinfo, pg_store_planぐらい。
pg_statsinfoはPerformance Insights等で代替するしかなさそう。

拡張の一覧は以下のURLでも確認できる。

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeaturesExtensions