English 中文 العربية 日本語 Português

PostgreSQL: PostgreSQL扩展生态、外部数据封装与向量搜索

最后更新：2026-08-26

1. 你将学到

2. 故事

Alice 的电商公司要做三件事：(1) 接入 AI 商品推荐，需要向量搜索；(2) 遗留 MySQL 数据迁移到 PG，需要跨库查询；(3) 模糊搜索太慢，需要 pg_trgm 加速。她发现 PostgreSQL 的扩展生态能一站式解决——pgvector 存商品 embedding，postgres_fdw 连接 MySQL 做数据迁移，pg_trgm 提升模糊搜索性能。一个数据库替代了 Elasticsearch + 中间件 + 自建推荐服务。

3. Concept：扩展机制概述

(1) 什么是 PostgreSQL 扩展

扩展（Extension）是 PostgreSQL 的插件系统，将相关 SQL 对象（函数、类型、操作符、索引方法）打包为一个单元，一键安装和卸载。

SQL 
-- List available extensions
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

(2) 扩展管理命令

命令 作用
CREATE EXTENSION ext_name 安装扩展
CREATE EXTENSION IF NOT EXISTS ext_name 幂等安装
CREATE EXTENSION ext_name VERSION '1.2' 指定版本安装
DROP EXTENSION ext_name 卸载扩展（保留配置需 CASCADE）
ALTER EXTENSION ext_name UPDATE TO '2.0' 升级扩展
\dx (psql) 列出已安装扩展

(3) 扩展安装前提

条件 说明
共享库文件 .so / .dll 必须在 shared_preload_libraries 或 dynamic_library_path
控制文件 extension_name.control 在 SHAREDIR/extension/
SQL 脚本 extension_name--version.sql 定义对象
权限 需要 CREATE 权限 on 当前数据库 + superuser（部分扩展）

4. 操作：常用扩展

▶ 示例：uuid-ossp 生成 UUID

SQL 
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Generate UUID v4 (random)
SELECT uuid_generate_v4();

-- Generate UUID v1 (time-based)
SELECT uuid_generate_v1();

-- Use as default column value
CREATE TABLE api_keys (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    key_name    TEXT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

输出：

TEXT 📖 仅展示 
CREATE TABLE

▶ 示例：pgcrypto 数据加密

SQL 
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Hash password with salt
SELECT crypt('MySecret123', gen_salt('bf'));

-- Verify password
SELECT crypt('MySecret123', stored_hash) = stored_hash AS is_match;

-- AES encryption
SELECT encode(encrypt('credit card data'::bytea,
    'secret_key_16bytes'::bytea, 'aes'), 'hex');

-- Generate random token
SELECT encode(gen_random_bytes(32), 'hex');

输出：

TEXT 📖 仅展示 
CREATE TABLE

▶ 示例：pg_trgm 模糊搜索

SQL 
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Show similarity score
SELECT similarity('PostgreSQL', 'Postgres');

-- GIN trigram index for fast fuzzy search
CREATE INDEX idx_products_name_trgm ON products
    USING GIN (name gin_trgm_ops);

-- Fuzzy search with threshold
SELECT name, similarity(name, 'iphon') AS score
FROM products
WHERE name % 'iphon'
ORDER BY score DESC;
TEXT 📖 仅展示 
     name      | score
---------------+-------
 iPhone 15 Pro |  0.42
 iPhone 14     |  0.38
(2 rows)

▶ 示例：pg_stat_statements 慢查询统计

SQL 
-- Must be in shared_preload_libraries first
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by total execution time
SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

输出：

TEXT 📖 仅展示 
  result  
----------
   42.50
(1 row)

▶ 示例：PostGIS 空间数据简介

SQL 
CREATE EXTENSION IF NOT EXISTS postgis;

-- Store point geometry
CREATE TABLE stores (
    id       SERIAL PRIMARY KEY,
    name     TEXT,
    location GEOMETRY(POINT, 4326)
);

-- Insert coordinates (longitude, latitude)
INSERT INTO stores (name, location)
VALUES ('Dubai Mall', ST_SetSRID(ST_MakePoint(55.2796, 25.1972), 4326));

-- Find stores within 5 km
SELECT name,
    ST_Distance(location::geography,
        ST_SetSRID(ST_MakePoint(55.2700, 25.2000), 4326)::geography
    ) AS distance_m
FROM stores
WHERE ST_DWithin(location::geography,
    ST_SetSRID(ST_MakePoint(55.2700, 25.2000), 4326)::geography, 5000);

输出：

TEXT 📖 仅展示 
INSERT 0 1

5. Concept：Foreign Data Wrappers

(1) FDW 架构

100% 
flowchart LR
    A["Local PG<br/>postgres_fdw"] -->|"CREATE SERVER"| B["Remote PG<br/>(or MySQL/Oracle)"]
    A -->|"CREATE SERVER"| C["file_fdw<br/>(CSV/Log files)"]
    A -->|"CREATE SERVER"| D["Other FDW<br/>(Redis/MongoDB...)"]
    B --> E["IMPORT FOREIGN SCHEMA"]
    C --> F["CREATE FOREIGN TABLE"]
    D --> G["Cross-system JOIN"]
FDW 名称 目标数据源 用途
postgres_fdw 远程 PostgreSQL 跨库查询、数据迁移
mysql_fdw 远程 MySQL MySQL→PG 迁移
file_fdw 本地 CSV 文件 日志分析、数据导入
redis_fdw Redis 缓存查询
mongo_fdw MongoDB 文档查询

(2) FDW 配置步骤

步骤 命令
1. 安装扩展 CREATE EXTENSION postgres_fdw
2. 创建 SERVER CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (...)
3. 创建 USER MAPPING CREATE USER MAPPING FOR local_user SERVER remote OPTIONS (...)
4. 创建 FOREIGN TABLE CREATE FOREIGN TABLE ft_xxx SERVER remote OPTIONS (...)
5. 或导入整个 SCHEMA IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER remote INTO remote_schema

6. 操作：postgres_fdw 跨库查询

▶ 示例：配置远程 PostgreSQL 连接

SQL 
-- Step 1: Install extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Step 2: Create server connection
CREATE SERVER legacy_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.100', port '5432', dbname 'legacy');

-- Step 3: Map local user to remote credentials
CREATE USER MAPPING FOR current_user
    SERVER legacy_db
    OPTIONS (user 'admin', password 'secret123');

-- Step 4: Import foreign schema
IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, products, orders)
    FROM SERVER legacy_db
    INTO legacy_schema;

-- Now query remote tables as if local
SELECT u.name, COUNT(o.id) AS order_count
FROM legacy_schema.users u
JOIN legacy_schema.orders o ON u.id = o.user_id
GROUP BY u.name;

输出：

TEXT 📖 仅展示 
 count 
-------
     5
(1 row)

▶ 示例：跨库 JOIN 本地与远程

SQL 
-- Local: new PG product catalog
-- Remote: legacy MySQL order data via postgres_fdw

SELECT p.name,
       SUM(loi.quantity) AS total_sold,
       SUM(loi.quantity * loi.unit_price) AS revenue
FROM products p
JOIN legacy_schema.order_items loi ON p.id = loi.product_id
WHERE p.category = 'Electronics'
GROUP BY p.name
ORDER BY revenue DESC;

输出：

TEXT 📖 仅展示 
  result  
----------
   42.50
(1 row)

▶ 示例：file_fdw 读取外部 CSV

SQL 
CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER csv_server
    FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE access_logs_csv (
    ip_address    TEXT,
    request_time  TIMESTAMP,
    method        TEXT,
    path          TEXT,
    status_code   INT,
    response_time NUMERIC
) SERVER csv_server
OPTIONS (filename '/var/log/nginx/access.csv', format 'csv', header 'true');

-- Analyze nginx logs with SQL
SELECT path,
       COUNT(*) AS hits,
       AVG(response_time) AS avg_ms
FROM access_logs_csv
WHERE status_code = 200
GROUP BY path
ORDER BY hits DESC
LIMIT 20;

输出：

TEXT 📖 仅展示 
 count 
-------
     5
(1 row)

▶ 示例：FDW 数据迁移实战

SQL 
-- Migrate users from legacy to local
INSERT INTO users (name, email, created_at)
SELECT name, email, created_at
FROM legacy_schema.users
WHERE id NOT IN (SELECT legacy_id FROM users);

-- Use dblink for incremental sync
CREATE EXTENSION IF NOT EXISTS dblink;

SELECT dblink_connect('legacy', 'host=192.168.1.100 dbname=legacy user=admin password=secret123');

SELECT * FROM dblink('legacy',
    'SELECT id, name, email FROM users WHERE created_at > now() - interval ''1 day'''
) AS t(id BIGINT, name TEXT, email TEXT);

输出：

TEXT 📖 仅展示 
INSERT 0 1

7. Concept：pgvector 向量搜索

(1) 向量搜索原理

AI 模型将文本/图片编码为高维向量（embedding），向量间的距离代表语义相似度。pgvector 让 PostgreSQL 原生存储和检索向量。

距离度量 公式简述 适用场景
L2 距离 (<=>) 欧几里得距离 空间距离
内积 (<#>) 点积 已归一化向量
余弦距离 (<=>) 1 - cos(θ) 语义相似度

(2) pgvector 索引选择

100% 
flowchart TD
    A["Vector column created"] --> B{"Rows < 10K?"}
    B -->|Yes| C["Exact search<br/>(no index needed)"]
    B -->|No| D{"Recall requirement?"}
    D -->|"High recall (> 99%)"| E["IVFFlat<br/>(probes=lists)"]
    D -->|"Fast + good recall"| F["HNSW<br/>(ef_search tuning)"]
    E --> G["CREATE INDEX ... USING ivfflat<br/>(vector_cosine_ops)"]
    F --> H["CREATE INDEX ... USING hnsw<br/>(vector_cosine_ops)"]
索引 构建速度 查询速度 召回率 适合规模
无索引（暴力） N/A 慢 100% < 10K
IVFFlat 中 快 95-99% 10K-1M
HNSW 慢 最快 97-99.5% 100K-10M+

8. 操作：pgvector 实战

▶ 示例：安装 pgvector 与创建向量列

SQL 
-- Install pgvector
CREATE EXTENSION IF NOT EXISTS vector;

-- Product table with embedding vector (1536 dims for OpenAI)
CREATE TABLE products_vec (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    embedding   vector(1536)
);

输出：

TEXT 📖 仅展示 
CREATE TABLE

▶ 示例：插入与余弦相似度搜索

SQL 
-- Insert product with embedding
INSERT INTO products_vec (name, category, price, embedding)
VALUES ('Wireless Headphones', 'Electronics', 79.99,
    '[0.012, -0.034, 0.056, ...]'::vector);

-- Find top 5 similar products by cosine distance
SELECT p.id, p.name, p.category, p.price,
       p.embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS distance
FROM products_vec p
ORDER BY p.embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 5;

输出：

TEXT 📖 仅展示 
INSERT 0 1

▶ 示例：创建 HNSW 索引

SQL 
-- HNSW index for fast approximate search
CREATE INDEX idx_products_vec_hnsw ON products_vec
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Tune search accuracy vs speed
SET hnsw.ef_search = 100;

-- Query with index (much faster on large datasets)
SELECT name,
       embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS distance
FROM products_vec
ORDER BY embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 10;

输出：

TEXT 📖 仅展示 
CREATE TABLE

▶ 示例：IVFFlat 索引与调优

SQL 
-- IVFFlat index (build after data loaded for best centroids)
CREATE INDEX idx_products_vec_ivf ON products_vec
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

-- Increase probes for higher recall
SET ivfflat.probes = 10;

SELECT name, embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS dist
FROM products_vec
ORDER BY embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 5;

输出：

TEXT 📖 仅展示 
CREATE TABLE

▶ 示例：AI 商品推荐完整流程

SQL 
-- Store product embeddings from AI model
INSERT INTO products_vec (name, category, price, embedding)
VALUES
    ('Running Shoes',     'Sports',   129.99, array_to_vec(ARRAY[0.1,0.2,0.3])::vector),
    ('Yoga Mat',          'Sports',    39.99, array_to_vec(ARRAY[0.11,0.19,0.31])::vector),
    ('Bluetooth Speaker', 'Electronics', 49.99, array_to_vec(ARRAY[0.5,0.1,0.2])::vector);

-- User viewed "Running Shoes", recommend similar items
WITH target AS (
    SELECT embedding FROM products_vec WHERE name = 'Running Shoes'
)
SELECT p.name, p.category, p.price,
       p.embedding <=> (SELECT embedding FROM target) AS similarity
FROM products_vec p
WHERE p.name != 'Running Shoes'
ORDER BY similarity
LIMIT 3;

输出：

TEXT 📖 仅展示 
INSERT 0 1

9. 操作：扩展管理最佳实践

(1) 版本管理

SQL 
-- Check current extension version
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- Upgrade extension
ALTER EXTENSION pgvector UPDATE TO '0.7.0';

-- Upgrade all extensions
SELECT extname,
       installed_version,
       default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version != default_version;

(2) 权限管理

场景 推荐做法
生产安装扩展 superuser 执行 CREATE EXTENSION
普通用户使用 GRANT USAGE ON SCHEMA / 函数执行权限
FDW 连接 USER MAPPING 存储凭证，不硬编码密码
扩展升级 先在 staging 测试，再生产执行

(3) 生产环境审核清单

检查项 说明
shared_preload_libraries pg_stat_statements/pgvector 需预加载
扩展来源 只用官方或可信第三方
版本锁定 记录生产扩展版本，避免自动升级
安全审计 pgcrypto 密钥管理，FDW 凭证保护
卸载测试 确认 DROP EXTENSION 影响范围

10. 综合示例

SQL 
-- Full setup: extensions + FDW + pgvector for e-commerce AI

-- Step 1: Install core extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgvector;

-- Step 2: Product catalog with vector search
CREATE TABLE products_ai (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    attrs       JSONB DEFAULT '{}',
    embedding   vector(1536)
);

-- Step 3: GIN index for fuzzy name search
CREATE INDEX idx_products_name_trgm ON products_ai
    USING GIN (name gin_trgm_ops);

-- Step 4: HNSW index for vector similarity
CREATE INDEX idx_products_embedding ON products_ai
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Step 5: Connect legacy database via FDW
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER legacy_db FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.50', port '5432', dbname 'legacy_shop');
CREATE USER MAPPING FOR current_user SERVER legacy_db
    OPTIONS (user 'migrate_user', password 'secure_pass');
IMPORT FOREIGN SCHEMA public LIMIT TO (old_products, old_users)
    FROM SERVER legacy_db INTO legacy;

-- Step 6: Migrate and transform data
INSERT INTO products_ai (name, category, price, attrs)
SELECT name, category, price,
       jsonb_build_object('weight_kg', weight, 'color', color)
FROM legacy.old_products
WHERE active = true;

-- Step 7: Combined fuzzy + vector search
SELECT p.name, p.price,
       similarity(p.name, 'wireless earbuds') AS text_score,
       p.embedding <=> '[0.015,-0.030,0.050]'::vector AS vec_dist
FROM products_ai p
WHERE p.name % 'wireless earbuds'
ORDER BY vec_dist ASC
LIMIT 5;

❓ 常见问题

Q CREATE EXTENSION 需要 superuser 吗？
A 大部分扩展需要 superuser，因为它会加载 C 动态库。PG 14+ 部分扩展可以用 GRANT CREATE ON DATABASE + trusted extension 机制。
Q pgvector 的 vector 列最大支持多少维？
A pgvector 支持最大 16,000 维（0.7.0+），但建议根据模型输出选择（OpenAI 1536、Cohere 1024 等）。维度越高索引越慢。
Q postgres_fdw 查询性能如何？
A FDW 有网络开销，简单查询延迟约 2-5ms。use_remote_estimate=on 可让远程生成成本估算，改善计划质量。大批量迁移建议用 COPY 而非 FDW。
Q HNSW 和 IVFFlat 该选哪个？
A 数据量 < 100K 可不建索引；100K-1M 选 IVFFlat（构建快）；> 100K 且需要低延迟选 HNSW（查询最快）。HNSW 构建慢但查询性能显著优于 IVFFlat。
Q pg_trgm GIN 索引会让 INSERT 变慢吗？
A 会。trigram 索引会拆出大量 token，写入开销约为普通 B-Tree 的 3-5 倍。适合读多写少的搜索场景。
Q FDW 能写远程表吗？
A postgres_fdw 支持对远程表 INSERT/UPDATE/DELETE。file_fdw 只读。写远程表时有分布式事务风险，建议只做只读查询或批量迁移。
Q 扩展升级会锁表吗？
A 一般 ALTER EXTENSION UPDATE 需要 ACCESS EXCLUSIVE 锁，时间取决于扩展内容。建议在低峰期执行，先在 staging 验证。

📖 小节

📝 作业

  1. ⭐ 安装 uuid-ossp 和 pgcrypto 扩展，创建一张 api_tokens 表，用 UUID 作主键、用 crypt() 存储密码哈希，并编写验证密码的查询。

  2. ⭐⭐ 配置 postgres_fdw 连接一个远程 PG 数据库（可用 Docker 模拟），导入远程 products 表，编写一个跨库 JOIN 查询：本地订单表 + 远程商品表。

  3. ⭐⭐⭐ 为商品搜索设计双引擎方案：pg_trgm 处理模糊文本搜索，pgvector 处理语义向量搜索。编写综合查询函数 search_products(keyword TEXT, query_vec vector, limit_count INT)，融合文本分数和向量距离排序，测试不同权重下的结果差异。

Web-Tutorial.com

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

100%

🙏 帮我们做得更好

我们是刚上线的编程教程站，几个人的小团队，精力有限。页面虽经检查，难免还有疏漏——链接失效、排版错乱、内容有误、语言生硬……

如果您发现了，麻烦告诉我们，我们会在收到反馈后第一时间进行修复，再次感谢您的光临 🙏