最佳 Postgres 扩展（2023 版）

Original 天舟 Bytebase 2023-10-31

可扩展性在 Postgres 的基因里，这要归功于其原始设计（https://dsf.berkeley.edu/papers/ERL-M85-95.pdf）。

这种设计理念赋予了 Postgres 许多独特的能力，其中之一就是它的扩展。通过 Postgres 扩展，第三方可以在不触碰任何 Postgres 核心代码的情况下扩展其功能。

如今，大多跑在生产环境中的 Postgres 都运行着一些扩展，以下我们介绍一些最常用的。

pg_stat_statements

https://www.postgresql.org/docs/current/pgstatstatements.html

pg_stat_statements 提供了跟踪服务器执行的所有 SQL 语句的规划和执行统计信息的方法。当 pg_stat_statements 处于活动状态时，它会跟踪服务器上所有数据库的统计信息。该模块收集到的统计数据可以通过一个名为 pg_stat_statements 的视图进行访问。

不过要注意，pg_stat_statements 扩展仅跟踪自启用后执行的查询。如果想要跟踪所有查询，请在服务器启动时将以下行添加到 postgresql.conf 文件中来启用该扩展：

shared_preload_libraries = 'pg_stat_statements'

比如要找到总执行时间最长的前 10 个查询：

SELECT query, total_timeFROM pg_stat_statementsORDER BY total_time DESCLIMIT 10;

PostGIS

http://postgis.net/

PostGIS 为 Postgres 添加了对地理数据的存储、索引和查询支持，它是最复杂的 Postgres 扩展，也从侧面证明了 Postgres 的扩展系统有多强大。

比如要找到离给定点最近的城市：

1. 假设我们有以下包含城市及其位置（用坐标表示）的表。注：location 列具有由 PostGIS 扩展提供的 GEOMETRY 类型。

CREATE TABLE cities (name TEXT,location GEOMETRY(Point, 4326));

2. 要找到离给定点最近的地方，可以用 ST_Distance 函数来计算该点与表中每个地方之间的距离，然后按照距离对结果进行排序。例如，以下命令可找到离纽约（-74.005941, 40.712784）最近的城市：

SELECT name, ST_Distance(location, ST_SetSRID(ST_MakePoint(-74.005941, 40.712784), 4326)) AS distanceFROM citiesORDER BY location <-> ST_SetSRID(ST_MakePoint(-74.005941, 40.712784), 4326)LIMIT 1;

这个查询计算了 cities 表中每个城市与坐标 (-74.005941, 40.712784) 之间的距离，并使用 <-> 对结果进行排序，LIMIT 1 子句返回最近的城市。

不过注意，ST_Distance 函数默认以米为单位返回两点之间的距离。你可以通过使用适当的 PostGIS 函数（例如 ST_Distance_Sphere）将结果转换为其他测量单位，比如千米。

postgres_fdw

https://www.postgresql.org/docs/current/postgres-fdw.html

postgres_fdw 可以用于访问存储在外部 Postgres 服务器中的数据，它是 dblink 扩展的继任者，但提供了更透明和符合标准的语法来访问远程表，并且在许多情况下可以提供更好的性能。

使用 postgres_fdw，你可以查询任何其他 Postgres 数据库。

1. 创建一个用来查询的新数据库。例如，我们这里创建一个名为 my_other_database 的库：

CREATE DATABASE my_other_database;

2. 连接到要创建外部表的数据库（这里，我们将使用默认的 postgres 数据库）。

3. 为将访问远程数据库的用户创建用户映射。例如，如果想使用当前连接的相同用户，可以运行以下命令：

CREATE USER MAPPING FOR current_userSERVER my_other_databaseOPTIONS (user 'postgres', password '');

4. 使用 postgres_fdw 扩展创建一个外部服务器。

CREATE SERVER my_other_database_serverFOREIGN DATA WRAPPER postgres_fdwOPTIONS (dbname 'my_other_database');

这个命令使用 postgres_fdw 外部数据封装器创建了一个名为 my_other_database_server 的服务器，并将 dbname 选项设置为 my_other_database。

5. 在本地数据库中创建一个外部表，将其映射到远程的 my_other_database 数据库中的一张表。

CREATE FOREIGN TABLE my_other_table (id INTEGER,name TEXT)SERVER my_other_database_serverOPTIONS (schema_name 'public', table_name 'my_table');

这个命令在本地数据库中创建了一个名为 my_other_table 的外部表，它映射到 my_other_database 数据库的 public schema 下的一个名为 my_table 的表。

6. 可以像使用普通表一样，在查询中使用这个外部表。

SELECT * FROM my_other_table WHERE id = 1;

你还可以在查询中将外部表与本地表连接，就像处理常规表一样。

注意，在使用 postgres_fdw 查询同一 Postgres 实例上的远程数据库时，你可能需要调整 postgresql.conf 文件并重新启动 Postgres 服务器，以便让其他数据库能够访问 pg_hba.conf 文件。

uuid-ossp

https://www.postgresql.org/docs/current/uuid-ossp.html

uuid-ossp 提供了使用几种标准算法之一生成通用唯一标识符 (UUID) 的函数。Postgres 已经内置了 gen_random_uuid() 函数来生成 v4 UUID (随机)。如果想生成其他版本的 UUID，需要使用 uuid-ossp。

比如，生成 v5 UUID：

SELECT uuid_generate_v5(uuid_ns_url(), 'example.com');

这个命令基于 URL 的命名空间标识符 (uuid_ns_url()) 和名称字符串 'example.com' 生成了一个 v5 UUID。输出类似：`f1f5d9f0-2a4c-5f24-9536-3f1f69e68a7e`

你还可以使用 uuid-ossp 函数 uuid_ns_create() 来创建自己的命名空间标识符。

SELECT uuid_ns_create('example');

此命令使用名称 'example' 创建一个命名空间标识符，并将其作为 UUID 返回。

然后可以使用此命名空间标识符和名称字符串与 uuid_generate_v5() 一起生成基于该命名空间的 UUID。

在安全性比较关键的应用程序中推荐使用 UUID v5，因为它使用命名空间标识符和名称字符串的 SHA-1 哈希生成，比其他 UUID 版本更不容易发生冲突。

pg_cron

https://github.com/citusdata/pg_cron

pg_cron 是一个简单的基于 cron 的任务调度程序，作为扩展运行在数据库内部。它使用与常规 cron 相同的语法，但可以直接从数据库中调度 Postgres 命令。

1. 创建一个新 cron 任务，运行以下命令：

SELECT cron.schedule('0 0 * * ', 'INSERT INTO my_table SELECT FROM my_other_table');

2. 确认任务已经创建

SELECT cron.jobid, cron.expr, cron.command FROM cron.job;

3. 查看正在运行和最近完成的任务状态

select * from cron.job_run_details order by start_time desc limit 5;

timescaledb

https://www.timescale.com/

timescaledb 提供了对时序数据的优化存储和查询功能。

1. 创建一个 hypertable

Hypertable 是 TimescaleDB 中专门用于存储和查询时序数据的特殊类型表。可以使用 CREATE_HYPERTABLE 函数来创建 hypertable。

CREATE TABLE sensor_data (time TIMESTAMP NOT NULL,value FLOAT NOT NULL);SELECT create_hypertable('sensor_data', 'time');

2. 插入一些数据到 sensor_data 表

INSERT INTO sensor_data (time, value)VALUES('2023-07-01 00:00:00', 10.0),('2023-07-01 01:00:00', 15.0),('2023-07-01 02:00:00', 20.0);

3. 查询数据

TimescaleDB 提供了许多针对时序数据进行优化的函数，例如 time_bucket 用于将数据聚合到时间间隔中。要计算每小时数据的平均值，可以运行以下查询：

SELECT time_bucket('1 hour', time) AS hour, AVG(value) AS avg_valueFROM sensor_dataGROUP BY hour;

pgvector

https://github.com/pgvector/pgvector

pgvector 提供了对向量处理的支持。你可以在数据组上执行向量化操作，这可以为特定类型的查询提供显著的性能改进。

欲获取最接近向量的邻居：

1. 创建一个带有向量列的新表

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));

2. 插入向量

INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

3. 查询向量的最近邻

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

🐘 Neon 最近发布了一个类似的扩展 pg_embedding，声称比 pgvector 快 20 倍。

总结一下

Postgres 的众多扩展是与其友商 MySQL 的关键区别：比如你有业务需要处理地理空间数据，那么 Postgres 是唯一的选择（因为有 PostGIS 扩展）。完整 Postgres 和 MySQL 对比请参阅「全方位对比 Postgres 和 MySQL (2023 版)」。

MySQL Schema 比较同步工具汇总

Bytebase 2.4.1 - VCS 集成支持 GitHub 企业版

如何安装 MySQL 客户端 - Mac, Ubuntu, CentOS 或 Windows

Star History 月度开源精选｜2023 年 6 月

继续滑动看下一个

陈佩斯，这次真悬了！

不能返税、不能补贴，招商局长们怎么办？

2024年最佳公众号排行，不用瞎忙，关注它们你就成功了一半

微博遗存之七

刚刚，我国DUV光刻机实现里程碑式突破！

最佳 Postgres 扩展（2023 版）

您可能也对以下帖子感兴趣

陈佩斯，这次真悬了！

不能返税、不能补贴，招商局长们怎么办？

2024年最佳公众号排行，不用瞎忙，关注它们你就成功了一半

微博遗存之七

刚刚，我国DUV光刻机实现里程碑式突破！

生成图片，分享到微信朋友圈

最佳 Postgres 扩展（2023 版）

您可能也对以下帖子感兴趣