PostgreSQL 内置扩展列表

PostgreSQL 内置扩展列表

PostgreSQL 自带了许多内置扩展（built-in extensions），这些扩展提供了额外的功能而不需要额外安装。以下是主要的内置扩展分类和说明：

标准内置扩展（随核心安装）

1. 管理类扩展

• pg_stat_statements：跟踪SQL语句执行统计
• pg_buffercache：查看共享缓冲区使用情况
• pg_prewarm：预加载关系数据到缓冲区
• pg_visibility：检查表的可见性映射
• pg_freespacemap：检查空闲空间映射

2. 监控诊断类

• auto_explain：自动记录执行计划
• pgstattuple：获取元组级统计信息
• pg_stat_plans（某些发行版）：执行计划统计
• pg_trgm：文本相似度分析（也用于索引）

3. 数据完整性检查

• amcheck：检查索引和表的逻辑一致性
• pageinspect：检查数据库页面的低级结构

4. 实用工具类

• file_fdw：通过外部数据包装器访问文件
• dblink：跨数据库连接
• pgcrypto：加密函数
• uuid-ossp：UUID生成函数
• citext：大小写不敏感的文本类型

5. 索引增强类

• btree_gin：使GIN索引支持普通数据类型
• btree_gist：使GiST索引支持普通数据类型
• bloom：布隆过滤器索引访问方法

6. 时间序列处理

• timescaledb（某些发行版内置）：时序数据库功能
• pg_partman（某些发行版内置）：分区表管理

查看可用内置扩展

-- 查看所有可用扩展（包括未安装的）
SELECT * FROM pg_available_extensions;-- 查看已安装扩展
SELECT * FROM pg_extension;-- 查看扩展详细信息
\dx

输出示例： 查看所有可用扩展（包括未安装的）

white=# SELECT * FROM pg_available_extensions order by 1;name        | default_version | installed_version |                                comment                                 
--------------------+-----------------+-------------------+------------------------------------------------------------------------adminpack          | 2.1             |                   | administrative functions for PostgreSQLamcheck            | 1.3             | 1.3               | functions for verifying relation integrityauto_explain       | 1.0             |                   | auto_explain sql exceted functionsautoinc            | 1.0             |                   | functions for autoincrementing fieldsbloom              | 1.0             |                   | bloom access method - signature file based indexbtree_gin          | 1.3             |                   | support for indexing common datatypes in GINbtree_gist         | 1.7             |                   | support for indexing common datatypes in GiSTcitext             | 1.6             |                   | data type for case-insensitive character stringscredcheck          | 3.0.0           | 3.0.0             | credcheck - postgresql plain text credential checkercube               | 1.5             |                   | data type for multidimensional cubesdblink             | 1.2             |                   | connect to other PostgreSQL databases from within a databasedict_int           | 1.0             |                   | text search dictionary template for integersdict_xsyn          | 1.0             |                   | text search dictionary template for extended synonym processingearthdistance      | 1.1             |                   | calculate great-circle distances on the surface of the Earthfile_fdw           | 1.0             |                   | foreign-data wrapper for flat file accessfuzzystrmatch      | 1.2             |                   | determine similarities and distance between stringshstore             | 1.8             |                   | data type for storing sets of (key, value) pairsinsert_username    | 1.0             |                   | functions for tracking who changed a tableintagg             | 1.1             |                   | integer aggregator and enumerator (obsolete)intarray           | 1.5             |                   | functions, operators, and index support for 1-D arrays of integersisn                | 1.2             |                   | data types for international product numbering standardslo                 | 1.1             |                   | Large Object maintenanceltree              | 1.2             |                   | data type for hierarchical tree-like structuresmoddatetime        | 1.0             |                   | functions for tracking last modification timeold_snapshot       | 1.0             |                   | utilities in support of old_snapshot_thresholdpageinspect        | 1.12            |                   | inspect the contents of database pages at a low levelpg_buffercache     | 1.4             |                   | examine the shared buffer cachepg_bulkload        | 3.1.21          | 3.1.21            | pg_bulkload is a high speed data loading utility for PostgreSQLpg_dirtyread       | 2               | 2                 | Read dead but unvacuumed rows from tablepg_freespacemap    | 1.2             |                   | examine the free space map (FSM)pg_prewarm         | 1.2             |                   | prewarm relation datapg_repack          | 1.5.0           | 1.5.0             | Reorganize tables in PostgreSQL databases with minimal lockspg_stat_kcache     | 2.2.3           |                   | Kernel statistics gatheringpg_stat_statements | 1.10            | 1.10              | track planning and execution statistics of all SQL statements executedpg_surgery         | 1.0             |                   | extension to perform surgery on a damaged relationpg_trgm            | 1.6             |                   | text similarity measurement and index searching based on trigramspg_visibility      | 1.2             | 1.2               | examine the visibility map (VM) and page-level visibility infopg_walinspect      | 1.1             |                   | functions to inspect contents of PostgreSQL Write-Ahead Logpgrowlocks         | 1.2             |                   | show row-level locking informationpgstattuple        | 1.5             | 1.5               | show tuple-level statisticsplpgsql            | 1.0             | 1.0               | PL/pgSQL procedural languagepostgres_fdw       | 1.1             |                   | foreign-data wrapper for remote PostgreSQL serversrefint             | 1.0             |                   | functions for implementing referential integrity (obsolete)seg                | 1.4             |                   | data type for representing line segments or floating-point intervalstablefunc          | 1.0             |                   | functions that manipulate whole tables, including crosstabtcn                | 1.0             |                   | Triggered change notificationstsm_system_rows    | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limittsm_system_time    | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limitunaccent           | 1.1             |                   | text search dictionary that removes accents
(49 rows)

输出示例： 查看已安装扩展

white=# SELECT * FROM pg_extension order by 2;oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------17952 | amcheck            |       10 |         2200 | t              | 1.3        |           | 17896 | credcheck          |       10 |         2200 | f              | 3.0.0      |           | 17840 | pg_bulkload        |       10 |         2200 | f              | 3.1.21     |           | 17861 | pg_dirtyread       |       10 |         2200 | t              | 2          |           | 17620 | pg_repack          |       10 |         2200 | f              | 1.5.0      |           | 17659 | pg_stat_statements |       10 |         2200 | t              | 1.10       |           | 17941 | pg_visibility      |       10 |         2200 | t              | 1.2        |           | 17739 | pgstattuple        |       10 |         2200 | t              | 1.5        |           | 14270 | plpgsql            |       10 |           11 | f              | 1.0        |           | 
(9 rows)

输出示例： 查看扩展详细信息

white=# \dxList of installed extensionsName        | Version |   Schema   |                              Description                               
--------------------+---------+------------+------------------------------------------------------------------------amcheck            | 1.3     | public     | functions for verifying relation integritycredcheck          | 3.0.0   | public     | credcheck - postgresql plain text credential checkerpg_bulkload        | 3.1.21  | public     | pg_bulkload is a high speed data loading utility for PostgreSQLpg_dirtyread       | 2       | public     | Read dead but unvacuumed rows from tablepg_repack          | 1.5.0   | public     | Reorganize tables in PostgreSQL databases with minimal lockspg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executedpg_visibility      | 1.2     | public     | examine the visibility map (VM) and page-level visibility infopgstattuple        | 1.5     | public     | show tuple-level statisticsplpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(9 rows)

常用内置扩展启用示例

-- 启用pg_stat_statements（需在postgresql.conf中添加shared_preload_libraries）
CREATE EXTENSION pg_stat_statements;-- 启用性能监控工具
CREATE EXTENSION pg_buffercache;
CREATE EXTENSION pg_stat_plans;-- 启用加密功能
CREATE EXTENSION pgcrypto;-- 启用UUID生成
CREATE EXTENSION "uuid-ossp";-- 启用跨数据库查询
CREATE EXTENSION dblink;

各版本差异

不同PostgreSQL版本的内置扩展可能有所不同：

版本	新增重要内置扩展
14+	pg_amcheck（增强的amcheck功能）
13+	pg_stat_statements跟踪更多信息
12+	pg_checksums（数据校验和）
11+	pg_partman（某些发行版内置）
10+	pg_prewarm改进

注意事项

1. 部分扩展需要超级用户权限才能安装
2. 某些扩展需要修改postgresql.conf并重启服务
3. 扩展一旦创建就与数据库绑定，需在每个需要使用的数据库中单独创建
4. 生产环境启用扩展前应评估性能影响

完整内置扩展列表获取

要获取您的PostgreSQL实例中完整的内置扩展列表，可以执行：

SELECT name, comment 
FROM pg_available_extensions 
WHERE installed_version IS NOT NULL 
ORDER BY name;

或者检查安装目录：

ls /usr/share/postgresql/<version>/extension/

更详细的内容请查看官方文档：

https://www.postgresql.org/docs/17/contrib.html