第 25 章：常用扩展 (Extensions) 🧩 – PostgreSQL入门

内容分享4天前发布 cbslyameawf

0 0 0

PostgreSQL 的核心本身已经超级强劲，但它最伟大的设计思想之一就是其高度的可扩展性。PostgreSQL 允许任何人编写“扩展包”，来为数据库增加全新的功能、数据类型、索引方法，甚至是过程语言。

第 25 章：常用扩展 (Extensions) 🧩 - PostgreSQL入门

这使得 PostgreSQL 不仅仅是一个数据库，更像是一个“数据平台”。你需要地理空间数据处理能力？装 PostGIS。你需要模糊字符串匹配？装 pg_trgm。你需要处理时间序列数据？装 TimescaleDB。

这个充满活力的扩展生态系统，是 PostgreSQL 如此受欢迎的关键缘由之一。

扩展 (Extension) 是一个封装好的、包含了一系列 SQL 对象（如函数、数据类型、操作符、索引类型等）的软件包。安装和管理扩展超级简单，只需要几个简单的 SQL 命令。

如何管理扩展？

查看当前数据库已安装的扩展:
在 psql 中，使用 dx 命令。
dx
查看所有可用的扩展:
每个 PostgreSQL 发行版都自带了一些超级有用的“官方”扩展。你可以查询 pg_available_extensions 视图来看到它们。
SELECT name, default_version, comment FROM pg_available_extensions;
安装一个扩展:
使用 CREATE EXTENSION 命令。你必须是超级用户才能安装扩展。
CREATE EXTENSION extension_name;
这个命令会运行该扩展的安装脚本，创建所有必需的函数、类型等。
卸载一个扩展:
使用 DROP EXTENSION 命令。
DROP EXTENSION extension_name;

下面，我们就来介绍几个在实际工作中超级常用、能极大提升开发效率的官方扩展。

25.1uuid-ossp(生成 UUID)

我们在第 16 章已经和它打过交道了。这个扩展提供了生成通用唯一标识符 (UUID) 的函数，对于需要分布式生成唯一 ID 的系统至关重大。

安装:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

IF NOT EXISTS 是一个好习惯，避免在扩展已安装时报错。

使用:
最常用的函数是 uuid_generate_v4()，它生成一个基于随机数的 UUID。

CREATE TABLE my_distributed_table (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    data TEXT
);

INSERT INTO my_distributed_table (data) VALUES ('some data');
SELECT * FROM my_distributed_table;

25.2pg_trgm(模糊字符串匹配)

你是否遇到过这样的需求：“用户搜索时输错了几个字母，我们能帮他找到最类似的结果吗？”

LIKE '%keyword%' 无法解决这个问题，而且性能很差。pg_trgm 扩展就是为此而生的。它基于 Trigram (三元模型) 算法，通过将字符串拆分成一个个三个字符的组合，来计算字符串之间的“类似度”。

安装:

CREATE EXTENSION IF NOT EXISTS "pg_trgm";

核心功能:

similarity(text1, text2) 函数: 返回两个字符串的类似度（0 到 1 之间）。
% 操作符: 判断两个字符串的类似度是否超过某个阈值（默认为 0.3）。
支持 GiST 或 GIN 索引，可以极大地加速类似度查询。

场景：在 users 表中，根据一个可能拼写错误的词，查找最类似的用户名。

第一步：创建索引
为了让查询飞起来，我们必须创建一个 GIN 或 GiST 索引。

CREATE INDEX idx_users_username_trgm ON users USING GIN (username gin_trgm_ops);

第二步：查询
假设用户想找 zhangsan，但不小心输成了 zhangsn。

-- 先看看类似度是多少
SELECT similarity('zhangsan', 'zhangsn');
--  similarity
-- ------------
--    0.727273

-- 设置一个类似度阈值，并进行查询
-- 这个阈值可以随时调整
SET pg_trgm.similarity_threshold = 0.6;

-- 使用 % 操作符进行查询，它会自动利用上面的索引
SELECT username FROM users WHERE username % 'zhangsn';
--  username
-- ----------
--  zhangsan

pg_trgm 对于实现搜索框的“您是不是要找…”功能超级有用。

25.3 PostGIS 简介 (地理空间数据处理)

PostGIS 可能是 PostgreSQL 最著名、最强劲的扩展，它将 PostgreSQL 变成了一个功能完备的、世界顶级的地理信息系统 (GIS) 数据库。

PostGIS 增加了一系列地理空间数据类型和函数，让你可以在数据库中存储、查询和分析地理位置数据。

核心能力:

geometry 数据类型: 用于存储点、线、多边形等地理形状。
空间函数:ST_Distance(): 计算两个地理对象之间的距离。ST_DWithin(): 判断一个对象是否在另一个对象的特定距离范围内（这是实现“附近的人”功能的核心）。ST_Contains(): 判断一个形状是否包含另一个形状。ST_Area(): 计算多边形的面积。
空间索引: 使用 GiST 索引来极速查询海量的地理数据。

场景: 找出一个点附近 10 公里内的所有商店。

-- 假设有一个 stores 表，包含一个名为 geom 的 geometry(Point, 4326) 列
SELECT name, address
FROM stores
WHERE ST_DWithin(
    geom,
    'SRID=4326;POINT(116.39 39.9)', -- 北京市中心的经纬度
    10000 -- 距离，单位是米
);

如果你需要处理任何与地理位置相关的数据，PostGIS 是不二之选。

本章小结

你已经领略到了 PostgreSQL 开放生态的强劲魅力。