PostgreSQL：从关系数据库到万能数据平台——扩展生态如何让 PG 吞掉一切场景

当一个数据库能同时搞定向量搜索、时序分析、图查询、消息队列、列式存储和全文检索，你还需要几个数据库？

一、背景：为什么 PostgreSQL 正在"赢家通吃"

2026 年 4 月，Google Cloud 发布了一份技术报告，详细介绍了他们对 PostgreSQL 核心功能的贡献——重点推进逻辑复制向 Active-Active 架构演进，引入自动冲突检测机制。同月，pg_duckpipe 发布了 3 月新特性，通过 WAL 将堆表实时同步到 DuckLake 列式存储，实现透明查询路由。而 pgvectorscale 引入了基于 DiskANN 的 StreamingDiskANN 索引，把向量搜索的成本从内存搬到了 SSD 上。

这三件事看似无关，但连起来看，指向同一个趋势：PostgreSQL 正在通过扩展生态，把所有数据库的能力吸纳进来。

过去十年，数据库领域的叙事是"碎片化"——关系型、文档型、时序、图、向量、列式，各领风骚。每个场景都有专用数据库：Elasticsearch 做搜索，InfluxDB 做时序，Neo4j 做图，Pinecone 做向量，ClickHouse 做分析。结果是：一个中等规模的公司，技术栈里躺着 5-8 个数据库，运维成本爆炸，数据孤岛严重。

PostgreSQL 的回答不同：我不做新数据库，我把你们都变成我的扩展。

这不是营销口号。来看数据：

这不是"支持扩展"，而是PG 本身就是扩展平台。

二、核心概念：PostgreSQL 扩展架构的设计哲学

2.1 为什么 PG 的扩展机制如此强大？

PostgreSQL 的扩展系统在数据库世界中几乎是独一无二的。它不是简单的"插件"或"存储过程"——它允许你深度侵入数据库内核的几乎每一个环节：

┌─────────────────────────────────────────────┐
│              PostgreSQL 内核                  │
│                                              │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐   │
│  │ 自定义    │  │ 自定义    │  │ 自定义    │   │
│  │ 数据类型  │  │ 索引方法  │  │ 表访问    │   │
│  │ (C Type) │  │ (AM)     │  │ 方法      │   │
│  └──────────┘  └──────────┘  └──────────┘   │
│                                              │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐   │
│  │ 自定义    │  │ 查询计划  │  │ WAL       │   │
│  │ 函数/算子 │  │ 钩子     │  │ 解码器    │   │
│  └──────────┘  └──────────┘  └──────────┘   │
│                                              │
│  ┌──────────────────────────────────────┐    │
│  │         Extension SPI 接口           │    │
│  └──────────────────────────────────────┘    │
└─────────────────────────────────────────────┘

关键接口包括：

1. 自定义类型（Custom Types）：可以定义全新的数据类型及其存储格式，pgvector 的 vector 类型就是这么来的
2. 自定义索引方法（Index Access Methods）：可以完全自己实现索引结构，HNSW、DiskANN 都是自定义 AM
3. 查询计划钩子（Planner Hooks）：pg_duckpipe 用它拦截查询，透明路由到列式引擎
4. WAL 解码器：逻辑复制和 CDC 的基础
5. 表访问方法（Table Access Methods）：PG 12+ 支持，可以替换堆表存储引擎

这套机制让扩展能做到的事，在其他数据库里几乎不可能。比如 MySQL 的插件只能做存储引擎层，无法定义新类型和新索引；MongoDB 的扩展只能用 JavaScript，性能受限。

2.2 扩展的编译与安装实战

让我们从零开始，在一个 Docker 容器中安装和配置 PostgreSQL + 核心扩展：

# 拉取 PostgreSQL 17 官方镜像
docker pull postgres:17

# 启动容器，挂载数据卷
docker run -d \
  --name pg17-universal \
  -e POSTGRES_PASSWORD=your_secure_password \
  -e POSTGRES_DB=app_db \
  -v pgdata17:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:17

# 进入容器安装扩展编译依赖
docker exec -it pg17-universal bash

# 在容器内安装编译工具
apt-get update && apt-get install -y \
  build-essential \
  postgresql-server-dev-17 \
  git \
  cmake \
  pkg-config

安装 pgvector：

# 克隆并编译 pgvector
cd /tmp
git clone --branch v0.8.1 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install

# 验证安装
psql -U postgres -d app_db -c "CREATE EXTENSION vector;"
psql -U postgres -d app_db -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';"

安装 pgvectorscale（DiskANN 索引）：

# pgvectorscale 需要 Rust 工具链
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh -s -- -y
source $HOME/.cargo/env

cd /tmp
git clone https://github.com/timescale/pgvectorscale.git
cd pgvectorscale/pgvectorscale
cargo pgx install --pg-config /usr/lib/postgresql/17/bin/pg_config --release

# 在数据库中启用
psql -U postgres -d app_db -c "CREATE EXTENSION vectorscale;"

三、架构分析：六大扩展如何覆盖全场景

3.1 pgvector + pgvectorscale：向量搜索的终极方案

为什么向量搜索重要？

AI 应用的核心是 Embedding——把文本、图片、音频转化为高维向量，然后通过相似性搜索找到"最接近"的结果。RAG（检索增强生成）的检索层，本质上就是向量搜索。

过去，你需要在 PostgreSQL 旁边再部署一个 Pinecone 或 Milvus。现在，pgvector 直接在 PG 内部搞定。

pgvector 的核心能力

-- 创建带向量字段的表
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536),  -- OpenAI ada-002 维度
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入数据
INSERT INTO documents (content, embedding, metadata)
VALUES (
    'PostgreSQL 是世界上最先进的开源关系数据库',
    '[0.012, -0.034, 0.056, ...]',  -- 1536维向量
    '{"source": "official", "category": "database"}'
);

-- 创建 HNSW 索引（推荐用于大多数场景）
CREATE INDEX idx_documents_embedding
ON documents USING hnsw (embedding vector_cosine_ops);

-- 余弦相似度搜索
SELECT id, content, metadata,
       1 - (embedding <=> '[0.015, -0.032, 0.048, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.015, -0.032, 0.048, ...]'
LIMIT 10;

HNSW vs IVFFlat 索引选择

-- HNSW：适合高召回率、低延迟场景
-- 构建慢，查询快，无需训练步骤
CREATE INDEX idx_hnsw_cosine
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- m: 每层最大连接数，越大召回越高，内存越大
-- ef_construction: 构建时搜索宽度，越大构建越慢但质量越好

-- IVFFlat：适合超大规模数据集（1亿+向量）
-- 需要先训练，查询时指定探测列表数
CREATE INDEX idx_ivfflat_cosine
ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);
-- lists: 倒排列表数，通常设为 sqrt(行数)

-- 查询时控制精度
SET ivfflat.probes = 10;  -- 探测列表数，越多越准但越慢

pgvectorscale 的 StreamingDiskANN：向量搜索的降本利器

pgvector 的 HNSW 和 IVFFlat 索引都有一个致命问题：必须全放在内存里。1 亿条 1536 维向量，光索引就要吃掉 600GB+ 内存。这对大多数公司来说是不可承受的。

pgvectorscale 引入的 StreamingDiskANN 索引解决了这个问题：

-- 启用 pgvectorscale 扩展
CREATE EXTENSION vectorscale;

-- 创建 StreamingDiskANN 索引
CREATE INDEX idx_diskann_embedding
ON documents
USING diskann (embedding vector_cosine_ops)
WITH (
    num_neighbors = 32,       -- 每个节点的邻居数
    search_list_size = 100,   -- 搜索列表大小
    max_alpha = 1.2,          -- 内存-精度权衡参数
    num_dimensions = 1536,    -- 向量维度
    storage_layout = 'memory_optimized'  -- 内存优化存储
);

-- 查询语法与 pgvector 完全一致
SELECT id, content,
       1 - (embedding <=> $query_vector) AS similarity
FROM documents
ORDER BY embedding <=> $query_vector
LIMIT 10;

DiskANN 的核心原理：

┌─────────────────────────────────────────────┐
│              内存层 (PQ 压缩向量)             │
│  ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐       │
│  │PQ │ │PQ │ │PQ │ │PQ │ │PQ │ │PQ │       │
│  └─┬─┘ └─┬─┘ └─┬─┘ └─┬─┘ └─┬─┘ └─┬─┘       │
└────┼─────┼─────┼─────┼─────┼─────┼──────────┘
     │     │     │     │     │     │
┌────┼─────┼─────┼─────┼─────┼─────┼──────────┐
│    ▼     ▼     ▼     ▼     ▼     ▼          │
│           SSD 层 (完整向量 + 图结构)           │
│  ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐           │
│  │Full │ │Full │ │Full │ │Full │  ...       │
│  │Vec+ │ │Vec+ │ │Vec+ │ │Vec+ │           │
│  │Edge │ │Edge │ │Edge │ │Edge │           │
│  └─────┘ └─────┘ └─────┘ └─────┘           │
└─────────────────────────────────────────────┘

• 内存中只存 Product Quantization (PQ) 压缩后的向量，大小约为原始向量的 1/32
• SSD 上存完整向量和图结构
• 查询时：先在内存中粗筛候选集，再从 SSD 精确计算
• Streaming 特性：支持动态插入和删除，无需离线重建索引

性能对比实测（pgvectorscale 官方基准）：

用 4 倍延迟换 30 倍成本降低，对大多数业务来说这是一笔划算的交易。

3.2 pg_duckpipe + DuckLake：在 PG 里做 HTAP

这是 2026 年最让我兴奋的 PG 扩展。

痛点：你的 OLTP 数据在 PostgreSQL，但分析查询跑不动——1 亿行的 GROUP BY 直接把数据库打满，影响线上业务。传统做法是用 Debezium + Kafka + ClickHouse 搭一整套数据管道，运维成本极高。

pg_duckpipe 的方案：通过 WAL 将堆表实时同步到 DuckLake 列式表中，分析查询自动路由到列式引擎，一条 SQL 都不用改。

-- 安装 pg_duckpipe
CREATE EXTENSION duckpipe;

-- 配置同步：将 orders 表同步到 DuckLake
SELECT duckpipe.add_table('orders');

-- 配置查询路由
SET duckpipe.query_routing = 'auto';

-- 这条查询会被自动路由到 DuckLake 列式引擎
-- 你不需要改任何 SQL！
SELECT
    customer_id,
    SUM(total) AS total_spent,
    COUNT(*) AS order_count,
    AVG(total) AS avg_order_value
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 100;

-- 查看同步状态
SELECT * FROM duckpipe.sync_status;

-- 查看哪些查询被路由到了 DuckLake
SELECT * FROM duckpipe.routing_log
WHERE routed_to = 'ducklake'
ORDER BY query_time DESC
LIMIT 10;

pg_duckpipe 2026 年 3 月新特性详解：

-- 1. 追加同步模式（恰好一次语义）
-- 适合不可变事件日志，性能更高
SELECT duckpipe.set_sync_mode('orders', 'append');

-- 2. 扇入流式传输：多源合并
-- 将多个数据库的 orders 表合并到一个分析表中
SELECT duckpipe.add_remote_source(
    'orders',
    host := 'secondary-db.internal',
    port := 5432,
    database := 'app_db'
);

-- 3. 分区表支持（零配置）
-- 如果源表是分区的，DuckLake 自动处理
CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INTEGER,
    total DECIMAL(10,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- 4. DDL 自动传播
-- 在源表上 ADD COLUMN，DuckLake 自动同步
ALTER TABLE orders ADD COLUMN region TEXT DEFAULT 'unknown';
-- DuckLake 侧自动添加 region 列，无需手动操作

-- 5. 分层配置
-- 在表级别覆盖全局刷新频率
SELECT duckpipe.set_refresh_interval('orders', INTERVAL '5 seconds');
-- 全局默认可能是 30 秒，热表可以更频繁

架构原理：

┌─────────────┐     WAL      ┌──────────────┐
│  PostgreSQL  │─────────────▶│  DuckLake    │
│  堆表 (OLTP) │              │  列式 (OLAP) │
└──────┬──────┘              └──────┬───────┘
       │                            │
       │   ┌─────────────────┐     │
       └──▶│  Query Router   │◀────┘
           │  (Planner Hook) │
           └────────┬────────┘
                    │
            ┌───────┴───────┐
            │   判断查询类型  │
            │               │
     OLTP查询│     OLAP查询  │
            ▼               ▼
     ┌──────────┐   ┌──────────┐
     │  堆表扫描 │   │ 列式扫描  │
     │  行存储   │   │ 向量化    │
     └──────────┘   └──────────┘

关键设计决策：

• Planner Hook 拦截：在查询规划阶段判断查询模式，分析型查询重写到列式引擎
• 恰好一次语义：通过 WAL 位移跟踪确保数据不丢不重
• 透明性：应用代码无需任何修改

3.3 TimescaleDB：时序数据的最佳实践

当你的业务涉及 IoT 传感器、监控指标、金融行情时，时序数据是刚需。TimescaleDB 让 PostgreSQL 拥有了与 InfluxDB 竞争的能力。

-- 安装 TimescaleDB
CREATE EXTENSION timescaledb;

-- 创建普通表
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ       NOT NULL,
    sensor_id   INTEGER           NOT NULL,
    temperature DOUBLE PRECISION  NULL,
    humidity    DOUBLE PRECISION  NULL,
    pressure    DOUBLE PRECISION  NULL
);

-- 转换为超表（Hypertable）
SELECT create_hypertable('sensor_data', 'time',
    chunk_time_interval => INTERVAL '1 day',
    partitioning_column => 'sensor_id',
    number_partitions => 4
);

-- 创建连续聚合（Continuous Aggregate）—— 物化视图的时序版本
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp,
    AVG(humidity) AS avg_humidity,
    COUNT(*) AS sample_count
FROM sensor_data
GROUP BY bucket, sensor_id;

-- 自动刷新聚合
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset    => INTERVAL '3 hours',
    end_offset      => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

-- 数据保留策略：自动清理旧数据
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- 压缩策略：对历史分块自动压缩
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- 查询最近 24 小时的每小时温度趋势
SELECT bucket, sensor_id, avg_temp, max_temp, min_temp
FROM sensor_hourly
WHERE bucket >= NOW() - INTERVAL '24 hours'
  AND sensor_id = 42
ORDER BY bucket;

压缩效果实测：

原始数据：1 亿行，约 12GB
压缩后：  约 1.2GB（压缩率 10:1）
查询压缩数据：性能损失 < 15%

3.4 PostGIS：地理空间的黄金标准

PostGIS 是 PostgreSQL 扩展中最成熟、最广泛使用的一个。事实上，在 GIS 领域，PostGIS 的功能已经超过了大多数专用 GIS 数据库。

-- 安装 PostGIS
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

-- 创建带地理字段的表
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    location GEOGRAPHY(POINT, 4326),  -- WGS84 坐标系
    service_area GEOGRAPHY(POLYGON, 4326),
    category TEXT
);

-- 插入数据（经度 纬度）
INSERT INTO stores (name, location, service_area, category)
VALUES (
    '望京店',
    ST_GeogFromText('POINT(116.4801 39.9968)'),
    ST_GeogFromText('POLYGON((116.46 39.98, 116.50 39.98, 116.50 40.01, 116.46 40.01, 116.46 39.98))'),
    'coffee'
);

-- 查找 3km 范围内的咖啡店
SELECT id, name,
    ST_Distance(location, ST_GeogFromText('POINT(116.4812 39.9980)')) AS distance_m
FROM stores
WHERE category = 'coffee'
  AND ST_DWithin(location, ST_GeogFromText('POINT(116.4812 39.9980)'), 3000)
ORDER BY distance_m
LIMIT 20;

-- 创建空间索引
CREATE INDEX idx_stores_location ON stores USING GIST (location);
CREATE INDEX idx_stores_service_area ON stores USING GIST (service_area);

-- 判断点是否在服务区内
SELECT name,
    ST_Contains(service_area::geometry, ST_Point(116.4850, 39.9990)::geometry) AS in_service_area
FROM stores;

-- 计算服务区面积
SELECT name,
    ST_Area(service_area) AS area_sq_meters
FROM stores;

-- 空间连接：找出所有服务区重叠的店铺对
SELECT a.name AS store_a, b.name AS store_b,
    ST_Area(ST_Intersection(a.service_area, b.service_area)::geography) AS overlap_area
FROM stores a, stores b
WHERE a.id < b.id
  AND ST_Intersects(a.service_area, b.service_area);

3.5 Apache AGE：把图数据库装进 PostgreSQL

AGE (A Graph Extension) 是 Apache 基金会孵化的项目，它让 PostgreSQL 支持 Cypher 查询语言——没错，就是 Neo4j 的那个 Cypher。

-- 安装 AGE
CREATE EXTENSION age;

-- 加载 Cypher 查询支持
LOAD 'age';

-- 创建图
SELECT create_graph('social_network');

-- 创建节点（用户）
SELECT * FROM cypher('social_network', $$
    CREATE (alice:User {name: 'Alice', age: 30}),
           (bob:User {name: 'Bob', age: 25}),
           (charlie:User {name: 'Charlie', age: 35}),
           (david:User {name: 'David', age: 28})
    RETURN alice, bob, charlie, david
$$) AS (alice agtype, bob agtype, charlie agtype, david agtype);

-- 创建关系
SELECT * FROM cypher('social_network', $$
    MATCH (a:User {name: 'Alice'}), (b:User {name: 'Bob'})
    CREATE (a)-[:FOLLOWS {since: '2024-01-15'}]->(b)
    RETURN a, b
$$) AS (a agtype, b agtype);

-- 更多关系
SELECT * FROM cypher('social_network', $$
    MATCH (a:User {name: 'Bob'}), (c:User {name: 'Charlie'})
    CREATE (a)-[:FOLLOWS {since: '2024-03-20'}]->(c)
$$) AS (result agtype);

SELECT * FROM cypher('social_network', $$
    MATCH (a:User {name: 'Alice'}), (d:User {name: 'David'})
    CREATE (a)-[:KNOWS {level: 'colleague'}]->(d)
$$) AS (result agtype);

-- 查询：Alice 关注的人关注了谁？（二度关系）
SELECT * FROM cypher('social_network', $$
    MATCH (a:User {name: 'Alice'})-[:FOLLOWS]->(b:User)-[:FOLLOWS]->(c:User)
    RETURN b.name AS followed_by_alice, c.name AS followed_by_followed
$$) AS (followed_by_alice agtype, followed_by_followed agtype);

-- 查询：找出所有 25 岁以上用户的社交网络
SELECT * FROM cypher('social_network', $$
    MATCH (u:User)-[r]->(v:User)
    WHERE u.age > 25
    RETURN u.name, type(r), v.name
    ORDER BY u.name
$$) AS (from_user agtype, rel_type agtype, to_user agtype);

-- 混合查询：图查询 + SQL（这是专用图数据库做不到的！）
SELECT
    u.name AS user_name,
    u.age,
    (SELECT COUNT(*) FROM cypher('social_network', $$
        MATCH (me:User {name: 'Alice'})-[:FOLLOWS]->(friend)
        RETURN friend
    $$) AS (friend agtype)) AS alice_friend_count
FROM (
    SELECT * FROM cypher('social_network', $$
        MATCH (u:User)
        RETURN u.name, u.age
    $$) AS (name agtype, age agtype)
) u;

AGE vs Neo4j 的关键差异：

3.6 pgmq：把消息队列塞进数据库

pgmq 是 Tembo 开发的 PostgreSQL 消息队列扩展，API 设计对标 AWS SQS 和 RabbitMQ，但数据就存在 PG 里。

-- 安装 pgmq
CREATE EXTENSION pgmq;

-- 创建队列
SELECT pgmq.create('order_events');

-- 发送消息
SELECT pgmq.send('order_events', '{"event": "order_created", "order_id": 12345, "customer_id": 678}');
SELECT pgmq.send('order_events', '{"event": "payment_received", "order_id": 12345, "amount": 99.99}');
SELECT pgmq.send('order_events', '{"event": "order_shipped", "order_id": 12345, "tracking": "SF1234567890"}');

-- 消费消息（可见性超时 30 秒）
SELECT * FROM pgmq.read('order_events', 30, 1);
-- 返回: msg_id | read_ct | enqueued_at | vt | message
-- 消费后消息进入不可见状态（visibility timeout）

-- 确认消费（删除消息）
SELECT pgmq.delete('order_events', 1);  -- 传入 msg_id

-- 批量消费
SELECT * FROM pgmq.read('order_events', 30, 10);  -- 最多取 10 条

-- 设置消息延迟投递
SELECT pgmq.send_delayed('order_events',
    '{"event": "order_cancel_warning", "order_id": 12345}',
    INTERVAL '24 hours'  -- 24小时后变为可见
);

-- 归档已消费的消息（而不是删除）
SELECT pgmq.archive('order_events', 2);

-- 查看队列统计
SELECT queue_name, queue_length FROM pgmq.metrics('order_events');

-- 删除队列
SELECT pgmq.drop_queue('order_events');

pgmq 的适用场景：

• 轻量级异步任务：不需要 Kafka 的吞吐量，但需要消息可靠性
• 事务性消息：消息发送和业务数据在同一个 PG 事务里，要么一起成功要么一起回滚
• 数据一致性：消息和业务数据同库，不存在分布式一致性难题
• 快速原型：不需要额外搭建消息中间件

不适用场景：百万级 QPS 的消息流，这时候还是得上 Kafka 或 Pulsar。

四、代码实战：构建一个 AI 驱动的智能应用

让我们把上面的扩展组合起来，构建一个真实的 AI 应用——智能文档助手，它需要：

1. 文档存储和全文搜索
2. 向量化 + 语义搜索（RAG）
3. 分析查询（文档统计）
4. 事件通知（新文档入库时触发）

-- ============================================
-- 第一步：创建核心表结构
-- ============================================

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS vectorscale;
CREATE EXTENSION IF NOT EXISTS pgmq;
CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- 模糊搜索

-- 文档表
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    summary TEXT,
    embedding VECTOR(1536),
    tags TEXT[],
    category TEXT,
    author TEXT,
    word_count INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建队列用于事件通知
SELECT pgmq.create('doc_events');

-- ============================================
-- 第二步：创建索引
-- ============================================

-- 全文搜索索引
ALTER TABLE documents ADD COLUMN tsv TSVECTOR
    GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(summary, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(content, '')), 'C')
    ) STORED;

CREATE INDEX idx_documents_tsv ON documents USING GIN (tsv);

-- 向量索引（DiskANN，内存友好）
CREATE INDEX idx_documents_embedding_diskann
ON documents USING diskann (embedding vector_cosine_ops)
WITH (
    num_neighbors = 32,
    search_list_size = 100,
    num_dimensions = 1536,
    storage_layout = 'memory_optimized'
);

-- 模糊搜索索引（支持 LIKE '%keyword%'）
CREATE INDEX idx_documents_title_trgm ON documents USING GIN (title gin_trgm_ops);
CREATE INDEX idx_documents_content_trgm ON documents USING GIN (content gin_trgm_ops);

-- B-Tree 索引
CREATE INDEX idx_documents_category ON documents (category);
CREATE INDEX idx_documents_created_at ON documents (created_at DESC);

-- ============================================
-- 第三步：混合搜索函数（关键词 + 语义 + 模糊）
-- ============================================

CREATE OR REPLACE FUNCTION hybrid_search(
    query_text TEXT,
    query_embedding VECTOR(1536),
    match_threshold FLOAT DEFAULT 0.7,
    result_limit INTEGER DEFAULT 20
)
RETURNS TABLE (
    id BIGINT,
    title TEXT,
    summary TEXT,
    category TEXT,
    semantic_score FLOAT,
    keyword_score FLOAT,
    combined_score FLOAT
) AS $$
DECLARE
    ts_query_text TEXT;
BEGIN
    -- 构建全文搜索查询
    ts_query_text := plainto_tsquery('simple', query_text)::TEXT;

    RETURN QUERY
    WITH semantic_results AS (
        SELECT
            d.id,
            1 - (d.embedding <=> query_embedding) AS score
        FROM documents d
        WHERE d.embedding IS NOT NULL
          AND 1 - (d.embedding <=> query_embedding) > match_threshold
        ORDER BY d.embedding <=> query_embedding
        LIMIT result_limit * 2
    ),
    keyword_results AS (
        SELECT
            d.id,
            ts_rank_cd(d.tsv, plainto_tsquery('simple', query_text)) AS score
        FROM documents d
        WHERE d.tsv @@ plainto_tsquery('simple', query_text)
        LIMIT result_limit * 2
    )
    SELECT
        d.id,
        d.title,
        d.summary,
        d.category,
        COALESCE(sr.score, 0)::FLOAT AS semantic_score,
        COALESCE(kr.score, 0)::FLOAT AS keyword_score,
        (COALESCE(sr.score, 0) * 0.6 + COALESCE(kr.score, 0) * 0.4)::FLOAT AS combined_score
    FROM documents d
    LEFT JOIN semantic_results sr ON d.id = sr.id
    LEFT JOIN keyword_results kr ON d.id = kr.id
    WHERE sr.id IS NOT NULL OR kr.id IS NOT NULL
    ORDER BY combined_score DESC
    LIMIT result_limit;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- 第四步：自动事件触发器
-- ============================================

CREATE OR REPLACE FUNCTION notify_new_document()
RETURNS TRIGGER AS $$
DECLARE
    event_json JSONB;
BEGIN
    event_json := jsonb_build_object(
        'event', 'document_created',
        'document_id', NEW.id,
        'title', NEW.title,
        'category', NEW.category,
        'author', NEW.author,
        'created_at', NEW.created_at
    );

    -- 发送到消息队列
    PERFORM pgmq.send('doc_events', event_json::TEXT);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_notify_new_document
AFTER INSERT ON documents
FOR EACH ROW
EXECUTE FUNCTION notify_new_document();

-- ============================================
-- 第五步：使用示例
-- ============================================

-- 插入文档（embedding 由应用层调用 OpenAI API 生成后传入）
INSERT INTO documents (title, content, summary, embedding, tags, category, author, word_count)
VALUES (
    'PostgreSQL 扩展架构深度解析',
    'PostgreSQL 的扩展系统允许开发者深度定制数据库功能...',
    '本文深入分析 PostgreSQL 的扩展架构设计',
    '[0.012, -0.034, 0.056, ...]'::vector,
    ARRAY['PostgreSQL', '数据库', '架构'],
    '数据库',
    '程序员茄子',
    5200
);

-- 语义搜索
SELECT id, title, summary, semantic_score, keyword_score, combined_score
FROM hybrid_search(
    'PostgreSQL 扩展开发',
    '[0.015, -0.032, 0.048, ...]'::vector,
    0.5,  -- 降低阈值以获取更多结果
    10
);

-- 全文搜索
SELECT id, title, ts_rank_cd(tsv, plainto_tsquery('simple', 'PostgreSQL 扩展')) AS rank
FROM documents
WHERE tsv @@ plainto_tsquery('simple', 'PostgreSQL 扩展')
ORDER BY rank DESC
LIMIT 10;

-- 模糊搜索（支持错别字和部分匹配）
SELECT id, title, similarity(title, 'PostgrSQL') AS sim
FROM documents
WHERE title % 'PostgrSQL'  -- % 操作符使用 trgm 相似度
ORDER BY sim DESC
LIMIT 10;

-- 消费事件
SELECT * FROM pgmq.read('doc_events', 30, 5);

五、性能优化：让多扩展协同不翻车

在一个 PG 实例上跑多个扩展不是没有代价的。以下是我踩过的坑和对应的优化方案。

5.1 共享内存与连接池

每个扩展都可能吃内存。pgvector 的 HNSW 索引在内存中，pg_duckpipe 的 DuckLake 也有缓存，PostGIS 的空间操作需要工作内存。当它们同时运行时，shared_buffers 和 work_mem 的配置至关重要。

# postgresql.conf — 多扩展协同的关键配置

# 共享缓冲区：物理内存的 25%，但不超 8GB
# 多扩展场景需要更大缓冲区
shared_buffers = 4GB

# 工作内存：每个排序/哈希操作的内存
# 空间查询和向量搜索需要更多
work_mem = 64MB

# 维护工作内存：VACUUM、CREATE INDEX 等
# 大表建索引需要
maintenance_work_mem = 1GB

# 有效缓存大小：操作系统 + PG 的缓存估计
# 影响查询计划器的索引使用决策
effective_cache_size = 12GB

# 并行查询
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# WAL 配置（影响 pg_duckpipe 同步延迟）
wal_level = logical
max_wal_senders = 10
wal_keep_size = 1GB

# 向量搜索专用配置
# hnsw.ef_search 控制查询精度和速度的权衡
SET hnsw.ef_search = 40;  -- 默认 40，增大更准但更慢

# DiskANN 专用配置
SET vectorscale.num_neighbors = 32;

5.2 连接池：PgBouncer 是必需品

多扩展并发查询容易耗尽 PG 连接。使用 PgBouncer 做连接池：

# pgbouncer.ini
[databases]
app_db = host=127.0.0.1 port=5432 dbname=app_db

[pgbouncer]
pool_mode = transaction        -- 事务级池化，最大化连接复用
max_client_conn = 500          -- 最大客户端连接
default_pool_size = 25         -- 每个数据库/用户对的默认池大小
reserve_pool_size = 5          -- 预留池
reserve_pool_timeout = 3       -- 等待预留池的超时（秒）
server_idle_timeout = 300      -- 空闲服务端连接超时

5.3 查询计划优化：让扩展各司其职

-- 查看 pg_duckpipe 的路由决策
EXPLAIN (ANALYZE, VERBOSE)
SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id;
-- 如果显示 "DuckLake Scan"，说明被路由到了列式引擎

-- 强制路由到特定引擎
SET duckpipe.query_routing = 'off';  -- 全部走堆表
SET duckpipe.query_routing = 'on';   -- 全部走 DuckLake
SET duckpipe.query_routing = 'auto'; -- 自动判断（推荐）

-- 向量搜索的查询计划检查
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, content
FROM documents
ORDER BY embedding <=> '[0.015, ...]'::vector
LIMIT 10;
-- 应该看到 "Index Scan using idx_documents_embedding_diskann"
-- 如果看到 "Seq Scan"，说明索引没被使用，检查：
-- 1. 是否启用了扩展
-- 2. 是否设置了足够的 work_mem
-- 3. 是否 ANALYZE 过表

-- 更新统计信息
ANALYZE documents;

5.4 分区策略：大表必备

当文档表超过 1 亿行时，分区是必需的：

-- 按时间范围分区
CREATE TABLE documents_partitioned (
    id BIGSERIAL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    summary TEXT,
    embedding VECTOR(1536),
    tags TEXT[],
    category TEXT,
    author TEXT,
    word_count INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    tsv TSVECTOR
) PARTITION BY RANGE (created_at);

-- 创建月度分区
CREATE TABLE documents_2026_01 PARTITION OF documents_partitioned
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE documents_2026_02 PARTITION OF documents_partitioned
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE documents_2026_03 PARTITION OF documents_partitioned
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE documents_2026_04 PARTITION OF documents_partitioned
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- 自动创建未来分区（使用 pg_partman 扩展）
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table := 'public.documents_partitioned',
    p_control := 'created_at',
    p_type := 'range',
    p_interval := '1 month',
    p_premake := 3  -- 预创建 3 个月的分区
);

5.5 VACUUM 策略：多扩展场景的垃圾回收

-- 查看表的膨胀情况
SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    n_dead_tup AS dead_tuples,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 对高写入表调整 autovacuum 参数
ALTER TABLE documents SET (
    autovacuum_vacuum_scale_factor = 0.05,   -- 5% 死元组就触发
    autovacuum_analyze_scale_factor = 0.02,   -- 2% 变更就更新统计
    autovacuum_vacuum_cost_delay = 10         -- 降低 VACUUM 对业务的影响
);

-- 对分区表，旧分区可以更激进地 VACUUM
ALTER TABLE documents_2026_01 SET (
    autovacuum_vacuum_scale_factor = 0.01,
    fillfactor = 90  -- 留 10% 空间给 HOT 更新
);

六、Google Cloud 的 Active-Active 贡献：PG 复制的里程碑

2026 年 4 月，Google Cloud 向 PostgreSQL 社区贡献了一系列逻辑复制增强，最引人注目的是自动冲突检测——这是 Active-Active 复制的基础。

6.1 为什么 Active-Active 这么难？

Active-Active（多主）复制意味着多个节点同时接受写入，然后将变更同步到其他节点。核心挑战是冲突处理：

节点 A: UPDATE users SET name='Alice' WHERE id=1  (时间 T1)
节点 B: UPDATE users SET name='Bob' WHERE id=1    (时间 T1)
    
两边的 UPDATE 在同一行上冲突了，怎么办？

传统方案：

1. Last-Writer-Wins (LWW)：用时间戳决定谁赢——但时钟偏移是大坑
2. 应用层冲突解决：把冲突抛给应用代码——复杂且易出错
3. 避免冲突：用 CRDT 或分区策略——限制了灵活性

6.2 Google Cloud 的方案

Google 的贡献是在逻辑复制层面引入行级冲突检测：

-- 配置逻辑复制（PG 17+ 语法）
-- 发布端
CREATE PUBLICATION pub_active_active FOR ALL TABLES
    WITH (publish = 'insert, update, delete, truncate');

-- 订阅端（带冲突检测）
CREATE SUBSCRIPTION sub_active_active
    CONNECTION 'host=node-a port=5432 dbname=app_db'
    PUBLICATION pub_active_active
    WITH (
        copy_data = true,
        streaming = 'parallel',
        conflict_detection = 'on'   -- 新参数！自动冲突检测
    );

当检测到冲突时，PG 会自动记录冲突详情：

-- 查看冲突日志
SELECT * FROM pg_conflict_log
ORDER BY conflict_time DESC
LIMIT 10;

-- 典型输出：
-- conflict_time | local_txid | remote_txid | table  | tuple_pk | resolution
-- 2026-04-20... | 12345      | 67890       | users  | (1)      | last_wins

这是 PostgreSQL 走向企业级多主架构的关键一步。虽然还不能完全替代 CockroachDB 或 YugabyteDB 的分布式一致性，但对于同区域多活场景（比如同一个城市的两个机房），已经足够实用。

6.3 实战：双节点 Active-Active 配置

# 节点 A（北京机房）
# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

# 节点 B（上海机房）
# postgresql.conf（相同配置）
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

-- 节点 A
CREATE PUBLICATION pub_node_a FOR ALL TABLES;
CREATE SUBSCRIPTION sub_node_b
    CONNECTION 'host=shanghai-db port=5432 dbname=app_db user=replicator'
    PUBLICATION pub_node_b
    WITH (conflict_detection = 'on');

-- 节点 B
CREATE PUBLICATION pub_node_b FOR ALL TABLES;
CREATE SUBSCRIPTION sub_node_a
    CONNECTION 'host=beijing-db port=5432 dbname=app_db user=replicator'
    PUBLICATION pub_node_a
    WITH (conflict_detection = 'on');

-- 验证复制状态
-- 节点 A 上执行：
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

注意事项：

1. 避免序列冲突：使用不同的序列起点和步长

-- 节点 A
CREATE SEQUENCE global_id_seq START WITH 1 INCREMENT BY 2;  -- 奇数
ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('global_id_seq');

-- 节点 B
CREATE SEQUENCE global_id_seq START WITH 2 INCREMENT BY 2;  -- 偶数
ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('global_id_seq');

2. 避免更新同一个热行：应用层通过分片键路由写入
3. 监控复制延迟：跨城网络延迟可能导致秒级延迟

-- 监控复制延迟
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

七、PostgreSQL 18：最新版本的关键增强

PostgreSQL 18（2026 年 2 月发布）带来了一些对扩展生态至关重要的改进：

7.1 增强的逻辑复制

• 序列复制：逻辑复制现在可以复制序列值，减少迁移和升级时手动同步的需求
• 大对象复制：pg_upgrade 的大对象管理得到优化
• 订阅管理修复：自死锁（self-deadlock）问题已修复

7.2 性能提升

-- PG 18 的增量排序优化
-- 当查询有 ORDER BY a, b 且已有 a 的索引时，
-- PG 18 会利用索引的有序性，只对每组 a 值内的 b 进行排序
EXPLAIN (ANALYZE)
SELECT * FROM documents ORDER BY category, created_at DESC LIMIT 100;
-- PG 17: Sort (cost=... rows=100 width=...)
-- PG 18: Incremental Sort (cost=... rows=100 width=...)  ← 更快！

7.3 更智能的并行查询

-- PG 18 改进了并行顺序扫描的调度
-- 对大表的全表扫描更高效
SET max_parallel_workers_per_gather = 8;

EXPLAIN (ANALYZE)
SELECT COUNT(*), category FROM documents GROUP BY category;
-- PG 18 在多核机器上可以获得接近线性的加速

八、选型建议：什么时候用 PG 扩展，什么时候上专用数据库

说了这么多 PG 扩展的好处，但我要诚实地说——PG 扩展不是银弹。

适合用 PG 扩展的场景

应该上专用数据库的场景

核心判断标准：如果你的数据量在 PG 的舒适区（单表 <10 亿行），且团队 DBA 资源有限，PG 扩展方案的综合成本远低于多数据库栈。但如果某个场景的规模和性能要求超出了 PG 的能力边界，就该果断上专用方案。

九、总结与展望

PostgreSQL 的扩展生态正在经历一个质变：从"PG 支持扩展"到"PG 就是扩展平台"。这个转变的核心驱动力有三个：

1. AI 浪潮：pgvector 的爆发证明了 PG 可以在 AI 基础设施中占据一席之地。向量搜索不再是专用数据库的专利，而是每个 PG 实例都能开箱即用的能力。

2. HTAP 需求：pg_duckpipe 和 DuckLake 让 PG 在不牺牲 OLTP 性能的前提下获得了 OLAP 能力。对大多数中等规模业务来说，这意味着不再需要搭建复杂的数据管道。

3. 运维成本压力：在经济下行周期，公司越来越不愿意维护 5-8 个不同的数据库。一个能覆盖 80% 场景的 PG 实例，比 5 个各覆盖 20% 场景的专用数据库更有吸引力。

但 PG 的扩展之路也面临挑战：

• 内存压力：多个扩展同时运行时，内存争用是实际问题
• 扩展兼容性：不同扩展之间可能有冲突（比如两个扩展都想 Hook 同一个查询计划器节点）
• 运维复杂度：虽然比维护多个数据库简单，但一个"什么都做"的 PG 实例出了问题，排查起来也更复杂
• 性能天花板：PG 的进程模型和存储引擎在某些场景下确实有硬限制

我的判断是：未来 3 年，PostgreSQL 会成为 70% 以上中小团队的核心数据库，但不会取代所有专用数据库。扩展生态会继续膨胀——我甚至预测会出现 PG 扩展市场（类似 VS Code 插件市场），让安装和组合扩展变得像搭积木一样简单。

对开发者来说，现在最值得投入学习的是：

1. pgvector + pgvectorscale：AI 应用的基础设施
2. pg_duckpipe：HTAP 的最简方案
3. PostGIS：LBS 应用的不二选择
4. 自定义扩展开发：掌握 PG 的 SPI 接口，你能把 PG 变成任何你想要的数据库

最后的最后，一句忠告：不要因为 PG "能做"就一定要用 PG 做。技术选型的本质是取舍，PG 扩展的价值在于减少不必要的碎片化，而不是消灭所有专用方案。选择适合你团队规模和业务阶段的方案，才是最务实的做法。