# 值得了解的扩展 > 了解 Postgres 扩展如何安装、加载和升级，哪些扩展值得占据一席之地，各自要付出什么代价，以及在依赖某个扩展前如何审查它。 --- LLMS 索引： [llms.txt](/llms.txt) --- 在安装了一个扩展的环境中，我们把 200 万条与本书示例相似的事件加载进一张表。一条 `alter table` 加上一个循环之后，同样的数据连同索引只占 30 MB，而不是 324 MB；一个月的部署时长查询读取大约 300 个页，而不是大约 3,050 个页。在另一台服务器上，“法兰克福 50 km 内有哪些人”的查询，从读取表的每一个页变成了读取 112 个页。 这两者都不是 Postgres 核心功能。它们都是扩展；Postgres 正是通过扩展获得其他数据库作为独立产品出售的能力：新类型和新的索引方法，加载进你已经在运行的服务器（[你还不需要另一个数据库](/15-models/#basics-chapter-15)）。扩展也是你没有编写、却运行在数据库进程内部的代码。下面介绍截至 2026 年 10 月值得了解的扩展、它们的代价，以及如何审查它们。 ## 扩展是数据库跟踪的一个软件包 {#an-extension-is-a-package-the-database-tracks} 扩展是服务器磁盘上的一组文件（控制文件、SQL 脚本，通常还有编译好的库），`create extension` 会把它们变成某个数据库中的对象，并将它们作为一个整体跟踪，因此 `drop extension` 和 `pg_dump` 也会把它们当成一个整体。由此有三件事，而每一件都可能带来麻烦：它只会安装软件包管理器放到磁盘上的内容（这就是托管服务会给你一张清单的原因）；它按数据库生效；SQL 对象的版本可能与库文件的版本发生漂移。 ### 有些扩展必须在服务器启动时加载 {#some-must-be-loaded-at-server-start} 需要共享内存、后台工作进程或规划器钩子的扩展，必须放进 `shared_preload_libraries`；修改它需要重启。 > [!WARNING] 预加载库加载失败会让服务器无法启动 > > 如果 `shared_preload_libraries` 中的任何库缺失或无法加载（列表中有拼写错误、软件包被移除，或者还没有为新的大版本构建），服务器就会拒绝启动，你安排的重启也会变成一次中断。先安装软件包，并在重启主库之前，在预发布服务器或备库上检查这个设置。 `pg_stat_statements` 则是反过来的经典陷阱：没有预加载时它可以顺利安装，但读取它时会失败，并报 `must be loaded via "shared_preload_libraries"`。 > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > ```sql > create extension pg_stat_statements; > select lab.prove('pg_stat_statements installs without being preloaded', > exists (select 1 from pg_extension where extname = 'pg_stat_statements')); > select lab.prove('but reading it fails until it is in shared_preload_libraries: 55000', > lab.try('select count(*) from pg_stat_statements') like '55000:%'); > ``` > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) ### 受信任的扩展不需要超级用户 {#trusted-extensions-dont-need-a-superuser} 从 PostgreSQL 13 起，被标记为 `trusted` 的扩展，也就是“不能提供访问数据库外部功能的途径”的扩展，任何拥有数据库 `CREATE` 权限的角色都可以安装：`citext`、`pg_trgm`、`btree_gist`、`ltree` 可以；会打开连接的 `postgres_fdw` 不可以。托管服务根本没有超级用户，因此在围绕某个扩展设计之前，先检查服务商的清单（[扩展来自允许列表](/34-managed/#extensions-come-from-an-allowlist)）。 > [!DETAILS]- 深入阅读：哪些扩展受信任，普通角色能做什么 > > ```text > name | version | trusted | superuser > --------------+---------+---------+----------- > citext | 1.8 | t | t > pg_cron | 1.6 | f | t > plpgsql | 1.0 | t | t > plpython3u | 1.0 | f | t > postgis | 3.6.4 | f | t > postgres_fdw | 1.2 | f | t > ``` > > 这是每个扩展的默认版本对应的 `pg_available_extension_versions`。一个拥有数据库 `CREATE` 权限的角色安装了 `citext` 并编写了 PL/pgSQL；但它得到的 `42501` 错误来自 `postgres_fdw` 和 PL/Python。 > > > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > > > ```sql > > create role app login; > > grant create on database lab to app; > > grant create on schema public to app; > > set role app; > > select lab.prove('a role with CREATE on the database, not a superuser, can install a trusted extension (citext)', > > lab.try('create extension citext') = 'ok'); > > select lab.prove('the same role cannot install an untrusted one (postgres_fdw): 42501', > > lab.try('create extension postgres_fdw') like '42501:%'); > > select lab.prove('it can write functions in trusted plpgsql', > > lab.try($q$create function one_pg() returns int language plpgsql > > as 'begin return 1; end'$q$) = 'ok'); > > select lab.prove('but not in untrusted plpython3u: 42501', > > lab.try($q$create function one_py() returns int language plpython3u > > as 'return 1'$q$) like '42501:%'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) ### 版本通过 alter extension update 推进 {#versions-move-with-alter-extension-update} 每次软件包、次版本或大版本升级后，都要在每个数据库中为每个扩展运行 `alter extension ... update`。没有任何东西会替你做这件事，托管服务也一样（[服务商会在 CREATE EXTENSION 上加一道自己的门槛](/34-managed/#the-provider-adds-its-own-gate-on-create-extension)）。而大版本升级要等到每个扩展都为新的大版本构建完成。 > [!DETAILS]- 深入阅读：Postgres 如何串起升级脚本 > > 扩展会提供名为 `extension--from--to.sql` 的脚本，Postgres 会把它们串起来。从 `hstore` 1.4 出发，`pg_extension_update_paths('hstore')` 给出到 1.8 的路径 `1.4--1.5--1.6--1.7--1.8`，而 `alter extension hstore update` 会依次运行这四个脚本。在我们的实验中，同一条命令把 TimescaleDB 从 2.29.2 升到了 2.30.2，因为它的镜像会特意把旧版本的库保留在磁盘上。`pg_upgrade` 会带着 SQL 对象一起迁移，并为仍待执行的更新写出脚本。 > > > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > > > ```sql > > create extension hstore version '1.4'; > > select lab.prove('there is no 1.4-to-1.8 script; Postgres chains four update scripts', > > (select path from pg_extension_update_paths('hstore') > > where source = '1.4' and target = '1.8') = '1.4--1.5--1.6--1.7--1.8'); > > alter extension hstore update; > > select lab.prove('alter extension hstore update walks it to the default version, 1.8', > > (select extversion from pg_extension where extname = 'hstore') = '1.8'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) ## Contrib 是最安全的一层，不过其中有些已经过时 {#contrib-is-the-safest-shelf-though-some-of-it-is-obsolete} 大约 50 个扩展随 Postgres 自己的发布周期和许可证一同发布在 `contrib` 中。它们是最安全的一批，但其中有些已经过时： | 模块 | 用途 | 仍然需要吗？ | | --- | --- | --- | | `pg_stat_statements` | 每查询统计信息 | 到处都需要 | | `pg_trgm` | `like '%x%'`、相似度 | 需要 | | `btree_gist` | 排他约束中的标量值 | 需要 | | `pgcrypto` | 密码哈希、PGP 加密 | 仅用于 `crypt()` 和 PGP | | `citext` | 不区分大小写的文本 | 大多已被 `lower()` 索引取代 | | `hstore` | 扁平键值列 | 大多已被 `jsonb` 取代 | | `uuid-ossp` | 生成 UUID | 只为 v1, v3, v5 | **Table 16-1.** Contrib 是最安全的一层，不过其中有些已经过时 核心已经提供 `gen_random_uuid()`、`uuidv7()` 和 `sha256()`。对于不区分大小写的文本，我们默认使用基于 `lower()` 的唯一索引；`citext` 自己的文档则把非确定性排序规则指为另一个选择（[对于电子邮件，用 lower() 做唯一键](/12-types/#for-emails-make-lower-the-unique-key)）。如果你使用 pgcrypto 的 PGP 函数，请使用 18.6 或更高版本：在此之前，OpenSSL 拒绝的一种加密算法（比如在 FIPS 模式下）会静默地产生极易被破解的数据（[18.6 发行说明](https://www.postgresql.org/docs/18/release-18-6.html)）。 ## PostGIS 是许多团队运行 Postgres 的根本原因 {#postgis-is-why-many-teams-run-postgres-at-all} [PostGIS](https://postgis.net/) 增加空间类型、函数和 [GiST](/43-glossary/#gist)空间索引。它成熟且广泛提供：3.6.4（2026 年 6 月），支持 PostgreSQL 12 到 18，采用 [GPL v2 或更高版本](https://github.com/postgis/postgis/blob/master/LICENSE.TXT)许可证；该许可证约束的是分发它，而不是让它在你的应用后面运行。 ### 类型决定距离的含义 {#the-type-decides-what-distance-means} PostGIS 有两类类型，选择哪一类决定了“距离”意味着什么。**`geometry`** 在平面上工作，单位取自其空间参考系（**SRID**）：经纬度使用度数（4326），投影使用米。**`geography`** 在弯曲的地球表面上工作，单位是米。法兰克福到阿姆斯特丹，有四种结果： ```text geometry_4326 | geography | mercator_3857 | laea_3035 ---------------+-----------+---------------+----------- 4.404 | 364465 | 581921 | 364479 ``` 真实答案大约是 364.5 km。4326 中的 `geometry` 给出 4.4 个“度”，没有意义。大多数网页地图使用的 Web Mercator 给出 582 km，长了 60%。全球范围分布的点使用 `geography`，区域数据则使用投影后的 SRID 中的 `geometry`。永远不要用度数计算距离。 > [!DETAILS]- 深入阅读：四种距离，用 SQL 表达 > > ```sql > select st_distance('SRID=4326;POINT(8.68 50.11)'::geometry, > 'SRID=4326;POINT(4.90 52.37)'::geometry) as geometry_4326, > st_distance('SRID=4326;POINT(8.68 50.11)'::geography, > 'SRID=4326;POINT(4.90 52.37)'::geography) as geography, > st_distance(st_transform('SRID=4326;POINT(8.68 50.11)'::geometry, 3857), > st_transform('SRID=4326;POINT(4.90 52.37)'::geometry, 3857)) > as mercator_3857, > st_distance(st_transform('SRID=4326;POINT(8.68 50.11)'::geometry, 3035), > st_transform('SRID=4326;POINT(4.90 52.37)'::geometry, 3035)) > as laea_3035; > ``` > > （输出已四舍五入。）经度的一度会随着向北移动而缩短，而 Mercator 离赤道越远，距离拉伸得越厉害。所有数据都应使用同一个 SRID；混用 SRID 轻则报错，粗心地使用 `st_transform` 时最坏会得到错误答案。 > > > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > > > ```sql > > select lab.prove('as geography: about 364.5 km, in meters, on the spheroid', > > st_distance('SRID=4326;POINT(8.68 50.11)'::geography, > > 'SRID=4326;POINT(4.90 52.37)'::geography) between 364000 and 365000); > > select lab.prove('in Web Mercator (3857): about 582 km, 60 percent too long', > > st_distance(st_transform('SRID=4326;POINT(8.68 50.11)'::geometry, 3857), > > st_transform('SRID=4326;POINT(4.90 52.37)'::geometry, 3857)) > > between 575000 and 590000); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) ### ST_DWithin 使用索引，ST_Distance 不使用 {#st_dwithin-uses-the-index-st_distance-doesnt} 给本书的 100,000 个用户各分配一个靠近五个种子区域之一的最近一次出现位置 `geography` 点，并为它建立 GiST 索引。“法兰克福 50 km 内的用户”： ```sql select count(*) from user_locations where st_dwithin(last_seen, 'SRID=4326;POINT(8.68 50.11)'::geography, 50000); ``` ```text Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=773 -> Bitmap Heap Scan on user_locations (actual rows=955.00 loops=1) Rows Removed by Filter: 660 Heap Blocks: exact=725 -> Bitmap Index Scan on user_locations_gist (actual rows=1615.00 loops=1) Index Cond: (last_seen && _st_expand('0101...'::geography, '50000'::double precision)) Buffers: shared hit=48 ``` 从底部向上读：一个边界框测试（`&&` 与扩展 50 km 的框比较）在 48 个索引页中找到 1,615 个候选，随后精确距离检查保留了 955 个。 现在看所有人最先写出的版本 `st_distance(last_seen, ...) < 50000`：它返回同样的 955 行，却会顺序扫描每个页，因为没有任何信息告诉规划器“低于 50 km”意味着“在这个框内”。与 `st_buffer` 圆相交会使用索引，但它用 32 条边画圆，漏掉了 6 个用户。使用 `ST_DWithin`，永远如此。 ### 索引找到行，物理顺序决定页数 {#the-index-finds-rows-physical-order-decides-pages} 再看看缓冲区：从一张有 834 个页的表中读取 955 行，需要 773 个页。附近的用户按 ID 顺序插入，因此分散在 725 个不同的页上。`cluster user_locations using user_locations_gist` 按空间顺序重写了表，同一个查询只读取 112 个页。`cluster` 会在重写期间锁住表，而随着行发生变化，物理顺序也会逐渐衰退，因此它适合批量加载的表。 最近邻是空间应用的另一个主要查询，形式为 `order by last_seen <-> point limit 5`；它按距离顺序遍历索引，只读取 12 个页。 PostGIS 自己的账单会在升级时到期：它链接 GEOS 和 PROJ 库，而 Postgres 大版本升级要等到 PostGIS 为新的大版本构建完成。 > [!DETAILS]- 深入阅读：已经验证的边界框测试、聚簇和最近邻计划，以及 PostGIS 的代价 > > > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > > > ```sql > > \set fra '''SRID=4326;POINT(8.68 50.11)''::geography' > > \set within 'select count(*) from user_locations where st_dwithin(last_seen, ' :fra ', 50000)' > > \set dist 'select count(*) from user_locations where st_distance(last_seen, ' :fra ') < 50000' > > \set buf 'select count(*) from user_locations where st_intersects(last_seen, st_buffer(' :fra ', 50000))' > > > > select lab.prove('the box finds 1,615 candidates; the exact distance check keeps 955', > > (select (p -> 'Plan' -> 'Plans' -> 0 -> 'Plans' -> 0 ->> 'Actual Rows')::numeric = 1615 > > and (p -> 'Plan' -> 'Plans' -> 0 ->> 'Rows Removed by Filter')::numeric = 660 > > from lab.plan(:'within') p)); > > select lab.prove('ST_Distance(...) < 50000 gives the same answer and never uses the index', > > (:dist) = 955 and 'Seq Scan' = any (lab.nodes(:'dist')) > > and not ('Bitmap Index Scan' = any (lab.nodes(:'dist')))); > > select lab.prove('a 50 km ST_Buffer is a 32-sided polygon, so intersecting it misses 6 of the 955', > > st_npoints(st_buffer(:fra, 50000)::geometry) = 33 and (:buf) = 949); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) > > ```text > Aggregate (actual rows=1.00 loops=1) > Buffers: shared read=112 > -> Bitmap Heap Scan on user_locations (actual rows=955.00 loops=1) > Heap Blocks: exact=62 > > Limit (actual rows=5.00 loops=1) > Buffers: shared hit=12 > -> Index Scan using user_locations_gist > on user_locations (actual rows=5.00 loops=1) > Order By: (last_seen <-> '0101...'::geography) > ``` > > `<->` 索引只有在 `order by` 中才会使用；对于 `geography`，它按球面而不是椭球面排序，因此距离接近的结果可能会有轻微的顺序偏差；对返回的行计算 `st_distance`。 > > 至于升级本身，[`postgis_extensions_upgrade()`](https://postgis.net/docs/PostGIS_Extensions_Upgrade.html) 会把 PostGIS 及其伴随扩展一起迁移。它有许多伴随扩展，只安装你实际使用的那些。另外，GiST 索引存储边界框，因此一个铺展开来的多边形，其边界框匹配的内容会远多于多边形本身；文档建议为 `ST_Subdivide` 切出的片段建立索引。 > > > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > > > ```sql > > \set knn 'select user_id from user_locations order by last_seen <-> ' :fra ' limit 5' > > cluster user_locations using user_locations_gist; > > analyze user_locations; > > select lab.prove('after CLUSTER on the GiST index, the same query reads under 150 pages', > > (:within) = 955 and lab.buffers(:'within') < 150); > > select lab.prove('nearest neighbors with <-> walk the index in distance order and read under 20 pages', > > lab.nodes(:'knn') = '{Limit,"Index Scan"}' and lab.buffers(:'knn') < 20); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) ## TimescaleDB 是带有主张的分区 {#timescaledb-is-partitioning-with-opinions} [TimescaleDB](https://github.com/timescale/timescaledb) 会把一张表变成**超表（hypertable）**，按时间动态创建子表（**分块（chunks）**），为较旧的分块提供列式格式，并增量维护汇总。我们在 PostgreSQL 18 上运行了当前版本 2.30.2；它背后的公司过去叫 Timescale，现在叫 Tiger Data。 ### 超表就是自动分区 {#a-hypertable-is-automatic-partitioning} 原生分区（[为了删除而分区](/39-partitioning/#basics-chapter-39)）要求你在行到来之前创建每个分区。超表则按需创建分块：`create table events (...) with (tsdb.hypertable, tsdb.partition_column = 'created_at', tsdb.chunk_interval = '7 days')`，插入本书示例中的一年事件后，不需要额外 DDL 就创建了 53 个周分块。 ### 列存储：324 MB 变成 30 MB {#the-columnstore-324-mb-to-30-mb} 列存储会把一个分块重写成每批最多 1,000 行的批次；每个批次的每一列都作为一个压缩数组存储，数据按 `segmentby` 列分组，并按 `orderby` 排序： ```sql alter table events set (timescaledb.enable_columnstore, timescaledb.segmentby = 'kind', timescaledb.orderby = 'created_at desc'); -- then call convert_to_columnstore() on each chunk, or let a policy do it ``` ```text before | after | ratio --------+-------+------- 324 MB | 30 MB | 10.8 ``` 一个月的部署时长查询从行分块读取大约 3,050 个页，从列存储读取大约 300 个页；列存储跳过了月份之外的批次，只解压查询点名的列。我们的按时间排序数据正是这种格式所针对的形状；你的数据可能压缩得少一些。代价转移到了写入：更新已压缩的行会解压并重新压缩批次，因此它适合几天后就主要只追加的数据。 > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > ```sql > -- :q is the month-of-deploys query; :rowstore_buffers is what it read before conversion > select lab.prove('the columnstore shrinks the events from about 324 MB to about 30 MB: a ratio over 9', > (select before_compression_total_bytes::numeric / after_compression_total_bytes > 9 > and before_compression_total_bytes between 300e6 and 360e6 > from hypertable_columnstore_stats('events'))); > select lab.prove('the same query now reads under a tenth of the pages', > lab.buffers(:'q') * 10 < :rowstore_buffers); > ``` > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) > [!DETAILS]- 深入阅读：列式计划内部，以及转换循环 > > ```sql > do $$ declare c regclass; > begin > for c in select show_chunks('events') loop > call convert_to_columnstore(c); > end loop; > end $$; > ``` > > 在生产环境中，`call add_columnstore_policy('events', after => interval '7 days')` 会完成这件事，并让最新的分块继续以行存储形式存在，以便快速插入。压缩比来自 `hypertable_columnstore_stats('events')`。下面是月份查询涉及的一个分块： > > ```text > -> Custom Scan (ColumnarScan) on _hyper_1_48_chunk (actual rows=2820.00 loops=1) > Vectorized Filter: > (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone) > Buffers: shared hit=42 > -> Index Scan using > _hyper_1_48_chunk_compressed_kind__ts_meta_v2_first_created_idx > on _hyper_1_48_chunk_compressed (actual rows=3.00 loops=1) > ``` > > （这个功能在 2.18 之前叫“压缩”；旧的函数名仍然有效。） ### 连续聚合要等刷新才会跟上 {#continuous-aggregates-lag-until-a-refresh} **连续聚合（continuous aggregate）**是建立在超表上的物化视图，TimescaleDB 会增量刷新它，只重新计算发生行变化的时间桶。自 2.13 起的默认行为会让人吃惊：它们是**仅物化的（materialized only）**。我们插入了一条日期为 10 月 5 日的事件，直到刷新覆盖那一天之后，它才出现在每日汇总中。设置 `timescaledb.materialized_only = false`，让新行合并进来，或者用策略安排刷新。 > [!DETAILS]- 深入阅读：漏掉一次部署的每日汇总 > > ```sql > create materialized view events_daily with (timescaledb.continuous) as > select time_bucket('1 day', created_at) as day, kind, count(*) as n > from events group by 1, 2 > with no data; > call refresh_continuous_aggregate('events_daily', null, null); > ``` > > 它包含 1,826 行，每天每种事件类型一行。再加入一条 10 月 5 日的部署事件后，视图显示 1,431，而表显示 1,432，直到下一次刷新。 > > > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > > > ```sql > > insert into events values (1, 1, 'deploy', '{}', '2026-10-05 12:00'); > > select lab.prove('continuous aggregates are materialized-only by default: a new event is invisible until a refresh', > > (select materialized_only from timescaledb_information.continuous_aggregates > > where view_name = 'events_daily') > > and (select n from events_daily where day = '2026-10-05' and kind = 'deploy') + 1 > > = (select count(*) from events where kind = 'deploy' > > and created_at >= '2026-10-05' and created_at < '2026-10-06')); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) ### 离开 Tiger Data 的云服务，最多期待超表 {#off-tiger-datas-cloud-expect-hypertables-at-most} 在基于它构建之前，先读清这一点。[LICENSE](https://github.com/timescale/timescaledb/blob/main/LICENSE) 将仓库拆成两部分：`tsl` 目录之外的代码采用 Apache 2.0，目录内的代码则采用可查看源码的 [Timescale License](https://github.com/timescale/timescaledb/blob/main/tsl/LICENSE-TIMESCALE)（TSL）。你可以随意把它用于自己的产品，但不能把它作为托管数据库出售。列存储、连续聚合、保留策略和任务调度器都属于 TSL（[版本](https://www.tigerdata.com/docs/about/latest/timescaledb-editions)），所以在 Tiger Data 自己的服务之外使用其他托管服务时，最多期待超表。 当列存储改变了你所需机器的规模时，TimescaleDB 才值得使用。如果你的时序数据只有一张 events 表，保留策略是“丢弃旧月份”，那么原生分区配合 pg_partman 和 BRIN 更简单，也没有许可证问题。 ## pg_cron 和 pg_partman 帮你把时间掌握在自己手里 {#pg_cron-and-pg_partman-keep-time-on-your-side} **[pg_cron](https://github.com/citusdata/pg_cron)**（PostgreSQL 许可证；1.6.8）从预加载的后台工作进程中按 cron 计划运行 SQL，间隔最低可到一秒。任务会随数据库一起故障切换，不需要另一个调度器主机来运行。每次运行都会进入 `cron.job_run_details`，而没有任何东西会清理它，因此也要为这个表安排删除任务。 **[pg_partman](https://github.com/pgpartman/pg_partman)**（PostgreSQL 许可证；5.5.0）管理原生分区：它预先创建分区，并在每次 `run_maintenance()` 运行时删除超出保留期限的分区；可以从 pg_cron 或它自己的工作进程运行（[在需要之前创建分区](/39-partitioning/#create-partitions-before-you-need-them)）。 ## 重建膨胀的表而不锁住它们 {#rebuild-bloated-tables-without-locking-them} `vacuum` 让死空间可以重新使用，但很少把空间还给操作系统；`vacuum full` 则通过在 `ACCESS EXCLUSIVE` 锁下重写表来归还空间（[大表执行 VACUUM FULL 会造成停机](/10-vacuum/#vacuum-full-is-an-outage-on-a-big-table)）。有两个扩展做同样的重写，同时允许你继续使用这张表。 我们把三张完全相同的表膨胀到 85 MB，大约是其存活数据的三倍，然后在使用它们的同时分别重建。`vacuum full` 把它的副本缩小到三分之一；运行期间，哪怕普通的 `select` 也会遇到它的[`lock_timeout`](/43-glossary/#lock_timeout)。**[pg_repack](https://github.com/reorg/pg_repack)**（BSD；1.5.3）把副本缩小了同样的幅度，同时插入可以直接通过。**[pg_squeeze](https://github.com/cybertec-postgresql/pg_squeeze)**（PostgreSQL 风格许可证；1.9.4）也做到了同样的事。 两者都会把整张表写入[WAL](/43-glossary/#wal)，副本和备份都会感受到这笔代价。先修复导致膨胀的原因。 > [!DETAILS]- 深入阅读：pg_repack 和 pg_squeeze 如何捕获写入，以及它们需要什么 > > **pg_repack** 通过触发器把每次变更记录到旁边的表中，复制原表，建立索引，重放日志，再交换文件；它只在开始和交换时持有 `ACCESS EXCLUSIVE`。它需要主键或非空唯一索引，以及容纳完整副本的空间。在复制之前，它会等待所有更早的事务：一个被遗忘的事务中空闲会话会无限期地拖住它。 > > **pg_squeeze** 改用逻辑解码，因此写入者不承担触发器代价。它需要 `shared_preload_libraries`；在 PostgreSQL 19 之前，它还需要 `wal_level = logical`；从 18.6 起，还要把它的名称加入 `output_plugin_libraries`，也就是新的解码插件允许列表；否则 `squeeze_table` 会失败并报“may not be used as an output plugin”。截至 2026 年 10 月，处于 beta 阶段的 PostgreSQL 19 会把 [`REPACK CONCURRENTLY`](https://www.postgresql.org/docs/devel/sql-repack.html) 带进核心。 > > > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > > > ```bash > > written=$(pg -c "set lock_timeout = '1s'" \ > > -c "insert into rp values (999999, 0, 'written during the repack') returning 'written'") > > prove "while pg_repack rebuilds the table, a write goes through within a 1 s lock_timeout" \ > > test "$written" = "written" > > ``` > > > > ```sql > > select lab.prove('while VACUUM FULL runs, even a plain select waits: lock_timeout fires (55P03)', > > (select outcome from seen) = 'blocked'); > > select lab.prove('pg_repack also shrank it to about a third, and kept the row written mid-copy', > > pg_table_size('rp') * 2.5 < (select bloated from sizes where t = 'rp') > > and (select pad from rp where id = 999999) = 'written during the repack' > > and (select count(*) from rp) = 200001); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) ## pgaudit 记录谁做了什么，代价是日志量增加 {#pgaudit-logs-who-did-what-at-the-cost-of-log-volume} **[pgaudit](https://github.com/pgaudit/pgaudit)**（PostgreSQL 许可证；18.0）按类别（`read`、`write`、`ddl`、`role` 等）以审计员要求的固定、可解析格式记录语句，也可以通过角色授权将范围缩小到特定表。它必须预加载。代价是日志量：广泛审计写入和 DDL，窄范围审计读取（[先用服务器日志开始审计；它是免费的](/33-security/#start-auditing-with-the-server-log-its-free)）。 ## 向规划器提出假设问题 {#ask-the-planner-hypothetical-questions} **[HypoPG](https://github.com/HypoPG/hypopg)**（PostgreSQL 许可证；1.4.3）创建**假设索引（hypothetical indexes）**，普通 `explain` 能看到它们，但磁盘上并没有它们。一次对 `users.email` 的查找读取了全部 1,126 个页；有了假设索引后，`explain` 在我们真正创建任何东西之前就选中了它。几秒钟就能测试列顺序或部分索引的谓词。**[pg_hint_plan](https://github.com/ossc-db/pg_hint_plan)**（BSD；1.8.0）则反过来，通过注释指定计划；把它留给事故场景（[pg_hint_plan 适合计划翻车的夜晚](/21-plans/#pg_hint_plan-is-for-the-night-a-plan-flips)）。 > [!DETAILS]- 深入阅读：HypoPG 能展示什么，不能展示什么 > > ```sql > select * from hypopg_create_index('create index on users (email)'); > explain select * from users where email = 'user4242@example.com'; > ``` > > ```text > Index Scan using "<13640>btree_users_email" on users (cost=0.04..8.06 rows=1 ...) > ``` > > `explain analyze` 会忽略假设索引，因此真实运行仍然读取了 1,126 个页；建立真实索引后，读取量降到 5 个页以下。`hypopg_relation_size()` 对索引大小的估计误差在 25% 以内，而 `hypopg_hide_index()` 会把真实索引从你的会话中隐藏，让你可以询问“如果我删掉它会怎样？”。 > > > [!EXAMPLE]- 动手验证 · lab `15-extensions.sh` > > > > ```sql > > \set q 'select * from users where email = ''user4242@example.com''' > > select lab.prove('without an index on email, the lookup reads all 1,126 pages of users', > > lab.nodes(:'q') = '{"Seq Scan"}' and lab.buffers(:'q') = 1126 > > and pg_relation_size('users') / 8192 = 1126); > > select indexrelid as hypo, hypopg_relation_size(indexrelid) as hypo_bytes > > from hypopg_create_index('create index on users (email)') \gset > > select lab.prove('with a hypothetical index, plain EXPLAIN picks it', > > lab.plan_text(:'q') like '%btree_users_email%'); > > select lab.prove('EXPLAIN ANALYZE ignores it: the query still reads all 1,126 pages', > > lab.nodes(:'q') = '{"Seq Scan"}' and lab.buffers(:'q') = 1126); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/15-extensions.sh) ## PGroonga 搜索内置解析器无法切分的文本 {#pgroonga-searches-text-the-built-in-parser-cant-split} pgvector 和[BM25](/43-glossary/#bm25)搜索扩展在搜索章集中介绍，那里会说明它们各自的许可证和运行位置（[搜索扩展把 BM25 放进索引](/23-search/#search-extensions-put-bm25-in-the-index)）。这里还要介绍一个：**[PGroonga](https://pgroonga.github.io/)**（PostgreSQL 许可证；4.0.9）可以搜索日文和中文文本，而内置解析器按空格寻找单词，无法处理这类文本。 ## Apache AGE 用更好的语言表达同样的连接 {#apache-age-is-a-nicer-language-for-the-same-joins} **[Apache AGE](https://github.com/apache/age)**（Apache 2.0；1.8.0）在普通表之上增加 openCypher 图查询语言。像 `-[:PARENT_OF*1..]->` 这样的匹配，就是用更顺手的语言表达[递归 CTE](/15-models/#adjacency-lists-make-moves-cheap-and-walks-recursive)；每一跳仍然是一次连接，因此[性能上的理由](/15-models/#a-graph-database-earns-its-place-on-deep-dense-graphs)仍然成立。每个会话都必须 `load 'age'`，这会与连接池发生冲突；项目从 2024 年 9 月到 2025 年 5 月一度沉寂（[董事会报告](https://whimsy.apache.org/board/minutes/AGE.html)）。 > [!DETAILS]- 深入阅读：用 Cypher 表达团队树 > > ```sql > create extension age; > load 'age'; > set search_path = ag_catalog, "$user", public; > select create_graph('org'); > select * from cypher('org', $$ > create (:Team {name: 'Engineering'})-[:PARENT_OF]->(:Team {name: 'Platform'}) > -[:PARENT_OF]->(:Team {name: 'Databases'}) > -[:PARENT_OF]->(:Team {name: 'Postgres'}) > $$) as (v agtype); > select * from cypher('org', $$ > match (:Team {name: 'Engineering'})-[:PARENT_OF*1..]->(t:Team) return t.name > $$) as (name agtype); > ``` > > 它返回 `"Platform"`、`"Databases"`、`"Postgres"`，类型为 `agtype`。图按每个标签一张表存放，例如 `org."Team"` 和 `org."PARENT_OF"`。 ## 在副本上运行分析引擎，永远不要在主库上运行 {#run-an-analytics-engine-on-a-replica-never-the-primary} 行存储会读取完整的行，这也是 Postgres 在大范围扫描中处于劣势的原因（[汇总和列式扩展可以弥合大部分差距](/41-limits/#rollups-and-a-column-extension-close-much-of-the-gap)）。**[pg_duckdb](https://github.com/duckdb/pg_duckdb)**（MIT；1.1.1）嵌入 DuckDB 的列式引擎，并读取 Parquet 和 Iceberg；**[pg_lake](https://github.com/Snowflake-Labs/pg_lake)**（Apache 2.0）让数据湖文件可以通过表访问。有些项目正在淡出：ParadeDB 已归档 pg_analytics，而 [pg_mooncake](https://github.com/Mooncake-Labs/pg_mooncake) 最近一次发布是在 2025 年 2 月（同年 12 月随后又出现了 v0.1.3 标签）。每一个都是进程中的第二个引擎；把它运行在副本上，永远不要运行在 OLTP 主库上。 ## pgmq 仍然是一张 Postgres 表 {#pgmq-is-still-a-postgres-table} **[pgmq](https://github.com/pgmq/pgmq)**（PostgreSQL 许可证；1.13.0）把 SKIP LOCKED 队列（[Postgres 队列与业务写入一起提交](/25-concurrency/#a-postgres-queue-commits-with-the-business-write)）包装成云队列 API：`send()`、带可见性超时的 `read()`、`archive()`。它仍然是一张 Postgres 表，需要同样的清理。 ## 不受信任的语言需要超级用户 {#untrusted-languages-need-a-superuser} PL/pgSQL 是受信任的：它不能做 SQL 做不到的事（[数据库中的代码](/24-functions/#basics-chapter-24)）。不受信任的语言（`plpython3u`、`plperlu`）以服务器操作系统用户的身份运行，因此只有超级用户能用它们编写函数，托管服务也会把它们排除在外。受信任的 JavaScript 可通过 [PL/v8](https://github.com/plv8/plv8) 和 [PLJS](https://github.com/plv8/pljs) 获得。数据库中的 Python 通常应该放在应用中。 ## 会崩溃的扩展会断开所有连接，因此要审查它 {#a-crashing-extension-drops-every-connection-so-vet-it} 扩展代码运行在后端进程内部，而后端崩溃时，服务器会结束其他每个会话：一次糟糕的函数调用，就能断开所有连接。在依赖某个扩展之前，先问： 1. **它有人维护吗？** 看发布日期、它支持上一个大版本的速度，以及提交代码的人数。 2. **许可证是什么，分别适用于哪些部分？** 阅读完整的 LICENSE：有些项目按目录拆分许可证。 3. **下一个大版本还会有它吗？** 你的升级要等每个扩展构建完成。 4. **你运行 Postgres 的地方能运行它吗？** 看服务商清单，以及它是否需要预加载或超级用户。 5. **它会对你的进程做什么？** 工作进程、共享内存、每条语句上的钩子，以及 WAL。 6. **退出路径是什么？** 一个定义了你数据类型的扩展（PostGIS、pgvector）是长期承诺。 然后在预发布环境测试它的升级路径。 > [!DETAILS]- 深入阅读：本章所有扩展一览 > > 截至 2026 年 10 月。“预加载”指 `shared_preload_libraries`。 > > | 扩展 | 增加的能力 | 许可证 | 来源 | > | --- | --- | --- | --- | > | PostGIS 3.6.4 | 空间类型、GiST 空间搜索 | GPL v2+ | 第三方 | > | TimescaleDB 2.30.2 | 超表、列存储、汇总 | Apache 2.0 + TSL | 第三方，需预加载 | > | pgvector 0.8.7 | 向量、HNSW 和 IVFFlat | PostgreSQL | 第三方 | > | Apache AGE 1.8.0 | openCypher 图 | Apache 2.0 | 第三方 | > | pg_cron 1.6.8 | 按计划运行 SQL | PostgreSQL | 第三方，需预加载 | > | pg_partman 5.5.0 | 创建分区和管理保留期 | PostgreSQL | 第三方 | > | pg_repack 1.5.3 | 在线重建、基于触发器 | BSD 3-clause | 第三方 | > | pg_squeeze 1.9.4 | 在线重建、逻辑解码 | PostgreSQL 风格 | 第三方，需预加载 | > | pgaudit 18.0 | 分类、可解析的审计日志 | PostgreSQL | 第三方，需预加载 | > | HypoPG 1.4.3 | 假设索引 | PostgreSQL | 第三方 | > | pg_hint_plan 1.8.0 | 注释中的规划器提示 | BSD 3-clause | 第三方，`load` 或预加载 | > | PGroonga 4.0.9 | 支持所有语言的全文检索 | PostgreSQL | 第三方 | > | pg_duckdb 1.1.1 | DuckDB 引擎、数据湖文件 | MIT | 第三方 | > | pgmq 1.13.0 | 基于表的消息队列 API | PostgreSQL | 第三方 | > | PL/v8 3.2.5, PLJS 1.0.5 | 受信任的 JavaScript | PostgreSQL 风格 | 第三方 | > > > > **Table 16-2.** 会崩溃的扩展会断开所有连接，因此要审查它 > > Contrib（预加载的 `pg_stat_statements`，以及 `auto_explain`、`pg_prewarm`、`hstore`、`ltree`、`citext`）采用 PostgreSQL 许可证，并随服务器发布。 ## 本章要点 {#the-short-version} - 扩展是服务器上的文件，加上某个数据库中的 SQL 对象。有些需要 `shared_preload_libraries` 和重启；所有扩展在升级后都需要运行 `alter extension ... update`。 - 删除 `uuid-ossp` 以及大部分 `pgcrypto` 和 `citext`：核心函数与 `lower()` 索引已经够用。 - 在 PostGIS 中，使用 `geography` 或投影后的 SRID，并用能使用索引的 `ST_DWithin` 过滤，永远不要用会读取每个页的 `ST_Distance`。 - TimescaleDB 的列存储把我们的事件从 324 MB 变成 30 MB，把一个月的查询从大约 3,050 个页降到大约 300 个页。这是 Timescale License 下的代码：可以免费自托管，但不能转售。 - `pg_repack` 和 `pg_squeeze` 可以在线重建膨胀的表，而 `vacuum full` 连读取都会阻塞。 - 建立索引之前，先用 HypoPG 测试它。 - 从维护情况、许可证、下一个大版本支持、可用性和崩溃风险几个方面审查每个扩展。 ## 延伸阅读 {#further-reading} - [Packaging Related Objects into an Extension](https://www.postgresql.org/docs/18/extend-extensions.html)，PostgreSQL 文档。控制文件、受信任的扩展、更新脚本，以及如何编写自己的扩展。 - [Additional Supplied Modules and Extensions](https://www.postgresql.org/docs/18/contrib.html)，PostgreSQL 文档。所有 contrib 模块，以及哪些模块受信任。 - [PostGIS manual: spatial queries](https://postgis.net/docs/using_postgis_query.html)，PostGIS 项目。索引使用、`ST_DWithin` 和最近邻搜索，以及每项选择背后的原因。 - [TimescaleDB editions](https://www.tigerdata.com/docs/about/latest/timescaledb-editions)，Tiger Data。哪些功能采用 Apache 2.0，哪些功能采用 Timescale License。 - [pg_repack documentation](https://reorg.github.io/pg_repack/)，reorg 项目。在线重建的要求、锁和选项。 - [HypoPG documentation](https://hypopg.readthedocs.io/)，HypoPG 项目。假设索引和隐藏索引，以及它们的限制。