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改进 |
注意事项
- 部分扩展需要超级用户权限才能安装
- 某些扩展需要修改postgresql.conf并重启服务
- 扩展一旦创建就与数据库绑定，需在每个需要使用的数据库中单独创建
- 生产环境启用扩展前应评估性能影响
完整内置扩展列表获取
要获取您的PostgreSQL实例中完整的内置扩展列表，可以执行：
SELECT name, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;
或者检查安装目录：
ls /usr/share/postgresql/<version>/extension/