Just Use Postgres:用一个数据库吃掉你架构里 80% 的中间件

"What can't Postgres do?"
—— 这两年技术圈最流行的一句话

"Use one database, but use it really well."
—— Hacker News 上一条 2k+ 赞的评论

写在最前面:一张让人窒息的架构图

先看一张图,看看你眼熟不:

                       [客户端]
                          │
                          ▼
                     [API Gateway]
        ┌─────────────────┼─────────────────┐
        ▼                 ▼                 ▼
    [业务服务]         [认证服务]         [搜索服务]
        │                 │                 │
        ├──→ MySQL    ├──→ Redis        ├──→ Elasticsearch
        ├──→ Redis    └──→ JWT Service   └──→ MongoDB(文档)
        ├──→ MongoDB
        ├──→ RabbitMQ ──→ Worker ──→ Kafka ──→ ClickHouse
        ├──→ Pinecone(向量)
        ├──→ InfluxDB(指标)
        ├──→ Neo4j(关系)
        ├──→ S3(对象)
        └──→ Cron Server / Airflow

数一数,11 个数据/中间件组件。

这是我见过的某个 30 人公司,真实的"现代化微服务架构"。看着特别专业,对吧?

我问他们 CTO:"上线一年多,系统稳吗?"

他笑了笑说:"稳是稳,就是一周两次半夜被叫醒,有一次 Pinecone 出问题排查到天亮,发现是网络抖动;有一次 Kafka 偏移量错乱,数据重复消费了一万条……运维已经是第三个了。"

所有组件都"基本能用",但合在一起,就是一个吞噬团队精力的怪兽。

如果你正在做一个新项目,千万不要默认就把下面这堆组件摆上图:

Redis(缓存) + RabbitMQ(队列) + Elasticsearch(搜索) +
MongoDB(文档) + InfluxDB(时序) + Pinecone(向量) +
Neo4j(图) + S3(对象) + Cron Server(定时) + ......

停一下。

先问自己一个问题:这些是我业务真正需要的,还是别人都这么画我也跟着画?

我自己经历过一个反面例子:初期"标准"架构上了 8 个中间件,运维一个人加班加点,半年后业务还没起来,运维先离职了。痛定思痛,下一个项目我们决定"PG 一把梭",一个 PostgreSQL 实例顶下了缓存、队列、搜索、向量、调度、定时任务、审计、地理位置。两年过去,系统跑得稳得像石头,团队精力全花在业务上,这才是我想要的工程。

这不是我一个人的体感。这两年技术圈有个明显的趋势,有个专有名词叫 "Postgres for Everything"(也叫 "Just Use Postgres" 或 "PG-First")。Hacker News 上隔三差五就有爆款帖讨论这个话题:

• 37signals(Basecamp、HEY 的母公司)CTO DHH 公开宣称删掉了 Redis、Elasticsearch,只留 PG,全公司从此世界清净
• Rails 8 默认的队列 SolidQueue、缓存 SolidCache、Cable SolidCable —— 全是 PG-based,连 Redis 都不再是默认依赖
• Notion:核心存储 PG;Block 数据 JSONB
• GitLab:全公司内部明确"PG-only 战略",老的 Redis 队列方案在被替换
• Figma:撑住了千万级实时协作,核心是 PG
• Discord:从 MongoDB 搬迁到 PG(后来又上了 ScyllaDB,但很多服务仍是 PG)

为什么这个趋势越来越猛?因为大家终于想明白了一件事:

复杂度是工程系统的最大敌人,而不是性能。

绝大多数系统的瓶颈,从来都不是数据库选型,而是人——人维护不过来那么多组件,人 debug 不了那么多故障域,人对接不上那么多数据一致性场景。

每多一个中间件,就多了:

• 一份运维成本
• 一个监控大盘
• 一个故障域
• 一套备份方案
• 一组安全策略
• 一个版本升级日历
• 一份"双写一致性"的心智负担
• 一次半夜被叫醒的可能

减少组件 = 减少故障 = 减少加班。这是工程师的尊严。

而 PostgreSQL,恰好是这个时代唯一一个把"什么都能做"做到了"什么都做得不错甚至很好"的数据库。它不是数据库,它是一个数据平台——一个有 30 年血脉、最强扩展生态、被全球最严苛业务打磨过的"瑞士军刀"。

这篇文章,我会把 PG 能"吃掉"的中间件场景一个一个拆开讲。每一节都遵循同样的结构:

【它能做到什么程度】 → 【原理是什么】 → 【完整可用的 SQL 示例】 → 【真实场景的坑】 → 【性能极限】 → 【什么时候真的需要专业方案】

文章很长(1 万 7 千多字),但每一节都可以独立看,收藏后按需查阅就好。

走起。

目录

1. PG 当队列用 —— 替代 Redis / RabbitMQ / Kafka(轻量场景)
2. PG 当缓存用 —— 替代 Redis(大部分场景)
3. PG 当文档数据库用 —— 替代 MongoDB
4. PG 当全文搜索引擎用 —— 替代 Elasticsearch(中小规模)
5. PG 当向量数据库用 —— 替代 Pinecone / Milvus
6. PG 当时序数据库用 —— 替代 InfluxDB
7. PG 当图数据库用 —— 替代 Neo4j(轻度场景)
8. PG 当地理空间引擎用 —— 行业标准 PostGIS
9. PG 当定时任务调度器用 —— 替代 Cron / Airflow
10. PG 当分布式锁/信号量用 —— 替代 Redis 的 SETNX
11. PG 直接吐 API —— 替代后端 CRUD 层
12. PG 当审计日志用 —— 替代埋点系统
13. PG 做 CDC / 流处理 —— 替代 Kafka 的部分场景
14. PG 的"边界"—— 什么时候真的不该用 PG
15. 实战路线图:一个项目从 0 到 1 的 PG 一把梭打法
16. 必装扩展清单 & 配置心法

1. PG 当队列用 —— 替代 Redis / RabbitMQ / Kafka(轻量场景)

1.1 一段真实的"队列踩坑史"

很多团队的"队列演化路线"是这样的:

1. 一开始:写一张 tasks 表,worker SELECT * FROM tasks WHERE status='pending' LIMIT 1,拿到再 UPDATE。
2. 上量后:发现两个 worker 拿到同一条任务,重复消费。
3. 改方案:加 SELECT ... FOR UPDATE,结果 worker 全部串行阻塞,吞吐暴跌。
4. 崩溃:把队列搬到 Redis,从此每周 deploy 时 Redis 队列丢任务,business owner 找上门。
5. 加码:再上 RabbitMQ + 死信队列 + 持久化磁盘 + 镜像集群,多了一个全职运维。
6. 回归:某天看到一个叫 SKIP LOCKED 的特性,当场删掉 RabbitMQ,半年没出过事。

第 6 步,就是这一节要讲的故事。

1.2 它能做到什么程度

结论先行:中小规模(< 万 TPS)的任务队列,PG 比 Redis/RabbitMQ 更好用,且持久性、事务性、可审计性全面碾压。

PG 9.5(2016 年)引入了 SELECT ... FOR UPDATE SKIP LOCKED,这一个看起来不起眼的特性,直接让 PG 升格为生产级队列引擎。配合 LISTEN/NOTIFY 的原生 pub/sub,你能拿到一个:

• ✅ 持久化(WAL + fsync,断电不丢)
• ✅ 事务化(任务出队 + 业务写库可以原子)
• ✅ 可审计(完整 SQL 历史可查)
• ✅ 支持延迟任务、优先级、重试、死信、唯一性约束
• ✅ 零额外组件

业界生产案例(必看,这些项目都建议读源码学习):

反过来想:如果 PG 队列不靠谱,Rails 8 不会拿它当默认方案。这是已经被严苛工程验证过的路。

1.3 核心原理:三个 PG 特性合体

队列要做对,需要三个 PG 特性互相配合,缺一不可。

特性一:FOR UPDATE SKIP LOCKED —— 抢任务不打架

传统的"用表当队列"方案有两个致命问题:

问题 A:多个 worker SELECT 同一行 → 重复消费。
问题 B:加 FOR UPDATE → 后到的 worker 阻塞,等前一个 commit 才能看到下一行。

SKIP LOCKED 一次性解决:已经被别人锁住的行,我直接跳过,继续找下一行。

-- 这一段在每个 worker 里都跑,互不阻塞、互不重复
BEGIN;

SELECT id, payload, queue
FROM jobs
WHERE status = 'pending'
  AND scheduled_at <= now()       -- 支持延迟任务
ORDER BY priority DESC, scheduled_at
LIMIT 1
FOR UPDATE SKIP LOCKED;           -- 这一行是灵魂

-- 立刻在同一事务内改状态,锁就锁住状态修改了
UPDATE jobs
SET status     = 'running',
    started_at = now(),
    heartbeat_at = now(),
    worker_id  = $1,
    attempts   = attempts + 1
WHERE id = $2;

COMMIT;

内部机理(脑补一下 PG 是怎么做到的):

1. PG 走 partial index WHERE status='pending',扫描候选行
2. 对每一行尝试加行级 ROW EXCLUSIVE 锁
3. 普通 FOR UPDATE:锁不到就等(pg_locks 里能看到 Lock 等待事件)
4. SKIP LOCKED:锁不到就跳过这一行,继续找下一个候选
5. 找到 LIMIT 个就返回

这是 Oracle 几十年前就有的功能,PG 9.5 才补齐 —— 一旦补齐,直接打开了"用 PG 做队列"的整个时代。

注:SKIP LOCKED 不是 NOWAIT。NOWAIT 是"锁不到立刻报错",SKIP LOCKED 是"锁不到换下一个",二者意义完全不同,别用错。

特性二:Partial Index —— 索引只盯活的行

队列表的特点:99% 是已完成的行,1% 是 pending 的行。如果用普通索引,索引会越来越大,扫描越来越慢。

Partial Index 是这一节最被低估的优化:

-- 注意 WHERE,这是 partial 的关键
CREATE INDEX idx_jobs_ready
ON jobs (priority DESC, scheduled_at)
WHERE status = 'pending';

效果:

• 索引大小只跟 pending 任务数相关,即使 jobs 表有 1 亿历史行,这个索引可能就几 MB
• 抢任务的查询走这个索引,永远扫不到已完成的死行
• VACUUM 也只需要清理这个小索引,极快

没用 partial index 的 PG 队列,跑久了一定会慢,这是新手最容易栽的地方。

特性三:LISTEN/NOTIFY —— 让 worker 别傻轮询

worker 启动后不能 while True: SELECT ...; sleep(1),这样:

• 空闲时 N 个 worker 持续打 PG,浪费
• 任务来了最多有 1 秒延迟

PG 内置了 pub/sub 机制 LISTEN/NOTIFY,直接当队列唤醒信号用:

-- 生产者:API 写完任务,通知 worker
BEGIN;
INSERT INTO jobs (queue, payload) VALUES ('default', '{...}');
NOTIFY jobs_default;     -- channel 名跟 queue 名对应
COMMIT;
-- 注意:NOTIFY 实际是在 COMMIT 时才发出的!这一点很重要

-- 消费者:worker 启动时订阅
LISTEN jobs_default;
-- 然后阻塞等待,被唤醒就去 SKIP LOCKED 抢

几个 NOTIFY 必须知道的细节:

1. NOTIFY 在事务 COMMIT 时才发,所以"先 INSERT 再 NOTIFY"是安全的——worker 收到通知时一定能 SELECT 到新任务。
2. NOTIFY 是事务性 deduplicate 的:同一事务内 NOTIFY x; NOTIFY x; 只会发一次。
3. payload 最大 8KB(PG_NOTIFY_PAYLOAD_LENGTH),所以只用作"有新任务了"的信号,不要塞业务数据。
4. NOTIFY 的传递不保证:连接断开期间的 NOTIFY 会丢。所以 worker 必须在 LISTEN 之外,定期(比如 5 秒)做一次兜底轮询,防止漏唤醒。

# 正确的 worker 主循环骨架
async def worker_loop():
    async with pool.acquire() as conn:
        await conn.add_listener('jobs_default', on_notify)
        while True:
            jobs = await fetch_and_lock_jobs(conn, limit=1)
            if jobs:
                await process(jobs)
            else:
                # 空闲 → 等 NOTIFY,5 秒兜底
                await asyncio.wait_for(notify_event.wait(), timeout=5)
                notify_event.clear()

1.4 完整生产级表结构(可直接抄)

CREATE TABLE jobs (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    queue         TEXT        NOT NULL DEFAULT 'default',
    kind          TEXT        NOT NULL,                          -- 任务类型,例如 'send_email'
    payload       JSONB       NOT NULL,
    status        TEXT        NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending','running','success','failed','dead','cancelled')),

    -- 优先级(数字越大越先执行)
    priority      SMALLINT    NOT NULL DEFAULT 0,

    -- 重试控制
    attempts      SMALLINT    NOT NULL DEFAULT 0,
    max_attempts  SMALLINT    NOT NULL DEFAULT 5,
    last_error    TEXT,

    -- 调度时间(支持延迟任务/重试退避)
    scheduled_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- 结果
    result        JSONB,

    -- worker 心跳(防止 worker 崩溃任务卡死)
    worker_id     TEXT,
    locked_at     TIMESTAMPTZ,
    heartbeat_at  TIMESTAMPTZ,

    -- 任务幂等键(同 key 不允许重复入队)
    dedup_key     TEXT,

    -- 时间戳
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    started_at    TIMESTAMPTZ,
    finished_at   TIMESTAMPTZ
);

-- ============ 关键索引 ============

-- 1. 抢任务索引(partial,只索引 pending),这是 #1 最重要的索引
CREATE INDEX idx_jobs_ready
ON jobs (queue, priority DESC, scheduled_at)
WHERE status = 'pending';

-- 2. 巡检 running 任务的心跳超时(partial)
CREATE INDEX idx_jobs_running
ON jobs (heartbeat_at)
WHERE status = 'running';

-- 3. 幂等键唯一约束(partial,允许多条已完成的同 key)
CREATE UNIQUE INDEX uq_jobs_dedup
ON jobs (dedup_key)
WHERE status IN ('pending','running') AND dedup_key IS NOT NULL;

-- 4. 业务上常用的状态查询(给监控/调用方用,partial 节约空间)
CREATE INDEX idx_jobs_finished
ON jobs (finished_at DESC)
WHERE status IN ('success','failed','dead');

-- ============ autovacuum 单独配置(关键!) ============

ALTER TABLE jobs SET (
    autovacuum_vacuum_scale_factor = 0.05,    -- 默认 0.2,改成 5% 死元组就 vacuum
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 2000       -- 加快 vacuum
);

为什么这个表这么"复杂"?因为每一个字段都是一个真实事故的纪念碑。下面会逐个讲。

1.5 完整的入队 / 出队 / 重试 / 心跳 SQL

入队(支持幂等)

-- 朴素入队
INSERT INTO jobs (queue, kind, payload, priority)
VALUES ('default', 'send_email', '{"to":"a@b.com"}', 10)
RETURNING id;

-- 幂等入队(同 dedup_key 已经在队,就不重复入)
INSERT INTO jobs (queue, kind, payload, dedup_key)
VALUES ('default', 'reconcile_order', '{"order_id":42}', 'reconcile:42')
ON CONFLICT (dedup_key) WHERE status IN ('pending','running') DO NOTHING
RETURNING id;

-- 延迟任务(10 分钟后才执行)
INSERT INTO jobs (kind, payload, scheduled_at)
VALUES ('check_payment', '{...}', now() + interval '10 minutes');

出队(完整版,带 worker_id 和心跳)

WITH picked AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
      AND scheduled_at <= now()
      AND queue = $1
    ORDER BY priority DESC, scheduled_at
    LIMIT $2
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status      = 'running',
    started_at  = now(),
    locked_at   = now(),
    heartbeat_at = now(),
    worker_id   = $3,
    attempts    = attempts + 1
FROM picked
WHERE j.id = picked.id
RETURNING j.id, j.kind, j.payload, j.attempts;

注意几点亮点:

• 用 CTE + UPDATE FROM 一句完成"找+锁+改",省一个 round trip
• 支持批量出队(LIMIT $2),适合 worker 一次拉一批处理
• 立即更新 heartbeat_at,巡检逻辑可以靠它

心跳(worker 处理过程中定期跑)

UPDATE jobs SET heartbeat_at = now()
WHERE id = ANY($1::uuid[]) AND worker_id = $2;

注意 AND worker_id = $2:防止 worker 心跳到一个已经被巡检 reset 的任务上(它已经被别的 worker 抢走了)。

完成 / 失败 / 重试

-- 成功
UPDATE jobs
SET status = 'success', finished_at = now(), result = $2
WHERE id = $1;

-- 失败 + 重试退避(指数退避)
UPDATE jobs
SET status       = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'pending' END,
    last_error   = $2,
    scheduled_at = CASE WHEN attempts >= max_attempts
                        THEN scheduled_at
                        ELSE now() + (power(2, attempts) || ' seconds')::interval
                   END,
    worker_id    = NULL,
    locked_at    = NULL
WHERE id = $1;

指数退避(2s → 4s → 8s → 16s → 32s)是处理外部依赖故障的最佳实践,直接写在 UPDATE 里,无需应用层算。

心跳巡检(关键!防止 worker 崩溃任务卡死)

-- 每分钟跑一次:心跳超过 2 分钟没更新的 → reset 回 pending
UPDATE jobs
SET status      = 'pending',
    worker_id   = NULL,
    locked_at   = NULL,
    last_error  = 'worker timeout / heartbeat lost'
WHERE status = 'running'
  AND heartbeat_at < now() - interval '2 minutes';

踩坑提醒:不要用 started_at 来判断超时,因为长任务正常运行也可能跑很久。心跳才是"活着"的信号。

1.6 你必须躲开的 7 个坑(每个都是血泪史)

坑 1:Worker 崩溃 → 任务永远 RUNNING

场景:worker OOM 被 kill / pod 被驱逐 / 网络断 → status 卡在 running,调用方查任务永远等不到结果。

解法:心跳 + 巡检(上面的 SQL)。这是必做项,不是可选项。

坑 2:NOTIFY 在事务 ROLLBACK 时不发 → 安静地丢任务

反例:

async with conn.transaction():
    await conn.execute("INSERT INTO jobs ...")
    await conn.execute("NOTIFY jobs_default")
    raise SomeError()  # 事务回滚
# worker 永远收不到通知,但你以为发了

这是对的(回滚时就该不发),但很多人不知道,以为 NOTIFY 是立即发送的。记住:NOTIFY 只在 COMMIT 时实际推出去。

坑 3:任务表无限膨胀 → PG 越来越慢

症状:跑半年后,jobs 表 5000 万行,简单出队也变慢。原因是 autovacuum 跟不上,dead tuple 堆积。

解法三选一(组合更好):

-- 方案 A:已完成任务定期归档到冷表
INSERT INTO jobs_archive
SELECT * FROM jobs
WHERE status IN ('success','dead','cancelled')
  AND finished_at < now() - interval '7 days';

DELETE FROM jobs
WHERE status IN ('success','dead','cancelled')
  AND finished_at < now() - interval '7 days';

-- 方案 B:配合 pg_cron 自动跑(后面 §9 会讲)
SELECT cron.schedule('archive-jobs', '0 3 * * *', $$ ... 上面的 SQL ... $$);

-- 方案 C:大量已完成任务时直接表分区(按周分区)
CREATE TABLE jobs (...) PARTITION BY RANGE (created_at);
CREATE TABLE jobs_2026w17 PARTITION OF jobs FOR VALUES FROM ('2026-04-21') TO ('2026-04-28');
-- 老分区直接 DROP,毫秒级

坑 4:max_connections 不够用

场景:200 个 worker,每个一个连接,API 进程又要 100 个连接,直接打爆 PG 的 max_connections=100 默认值。

解法:永远要用 PgBouncer(transaction pool 模式),把上层的几百个连接复用成 PG 后端的 20-50 个。

# pgbouncer.ini
[databases]
mydb = host=pg.local port=5432 pool_mode=transaction pool_size=30

[pgbouncer]
max_client_conn = 1000

注意:transaction pool 模式下,LISTEN/NOTIFY 不可用(因为 LISTEN 是 session 级的)。所以 worker 的 LISTEN 连接要直连 PG,不经过 PgBouncer;只有"入队/出队"的短事务走 PgBouncer。

坑 5:LIMIT 1 抢任务,吞吐打不上去

反例:LIMIT 1 每次抢一条,一次 round trip 处理一条,网络开销大。

优化:LIMIT N 批量抢(N=10/50),内存里串行或并发处理。

... LIMIT 50 FOR UPDATE SKIP LOCKED;

吞吐能直接翻 5-10 倍。

坑 6:任务超时无差别 reset → 死循环

场景:某个任务因 bug 必然崩,worker 拿到就 OOM,巡检 reset,下个 worker 又 OOM,循环烧机器。

解法:attempts 字段 + max_attempts 标 dead:

UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'pending' END,
    ...

dead 任务不会被重新调度,等人介入。监控大盘上接 count(*) FILTER (WHERE status='dead'),有死信立刻报警。

坑 7:用 LIKE 而不是索引扫描

反例:

SELECT * FROM jobs WHERE kind LIKE 'send_%' AND status='pending';
-- 这会全表扫 pending,不会走 partial index

解法:把 kind 也放进 partial index;或者按 kind 拆 queue:

CREATE INDEX idx_jobs_kind_ready ON jobs (kind, priority DESC, scheduled_at)
WHERE status = 'pending';

1.7 性能基准:PG 队列到底能跑多快

社区的 benchmark 数据(2023-2024 年公开测试):

调优手段:

1. synchronous_commit = off(队列容忍极小概率丢任务时,吞吐 +50%)
2. 批量入队(单 INSERT 多行,而非一行一次)
3. 批量出队(LIMIT 50)
4. partial index 是免费的速度
5. PgBouncer transaction pool

结论:除非你是 Twitter 量级,否则 PG 队列吞吐永远不是瓶颈,真正的瓶颈是任务本身的处理逻辑(比如调外部 API 慢)。

1.8 跟 Redis / RabbitMQ / Kafka 横评

PG 队列输的只有"极限吞吐"这一项。而这一项,90% 的业务一辈子用不到。

1.9 Outbox Pattern:PG 队列最闪光的应用

经典分布式痛点:业务 commit 了,但 MQ 发送失败,数据不一致。

# 反例:经典踩坑
db.commit_order(order)         # 成功
mq.publish('order_created', order)  # 失败 → 下游永远不知道

PG 一把梭做法:

BEGIN;
  INSERT INTO orders (...) VALUES (...);
  INSERT INTO outbox (event_type, payload, dedup_key)
  VALUES ('order_created', '{...}', 'order:42:created');
COMMIT;

业务和事件在一个事务里,绝对一致。然后起一个 outbox dispatcher worker(就是上面的 PG 队列模型),把事件发出去:

# dispatcher 主循环(简化)
async def dispatch():
    rows = await fetch_and_lock(conn, 'outbox', limit=50)
    for r in rows:
        try:
            await send_to_downstream(r.event_type, r.payload)
            await mark_success(conn, r.id)
        except Exception as e:
            await mark_failed(conn, r.id, str(e))

这是分布式系统里最优雅的事务消息方案,而你只需要 PG。

1.10 什么时候真的需要 Kafka

不要骗自己,以下场景 PG 真不行:

• ✅ 日志洪流,> 10w 条/秒持续写入
• ✅ 多消费者扇出(同一事件被 5 个独立服务消费)+ 持久化几个月
• ✅ 跨数据中心复制
• ✅ 真正的流处理(KSQL / Flink / Spark Streaming 衔接)
• ✅ 事件溯源 (Event Sourcing) + 长期回放

简单的"任务队列",PG 一万倍够用。

1.11 推荐学习路径

如果你要落地 PG 队列,按这个顺序看源码 / 读文档:

1. 先精读 pg-boss 的 SQL 设计 —— 最全面
2. 再看 Oban 的文档 —— 工程经验最丰富
3. 最后读 River 的设计文档 —— 最现代

把这三个抄一遍,你的 PG 队列就是工业级的。

2. PG 当缓存用 —— 替代 Redis(大部分场景)

2.1 反直觉的真相

"缓存必须用 Redis"是 21 世纪初到现在最被神化的一个迷思。

不信你回想一下,你团队加 Redis 的时候,是不是这样的对话:

A:这个查询慢,加个缓存吧。
B:那上 Redis?
A:对,先加 Redis。
(然后跑去搞了 3 周的双写、缓存失效、一致性 bug)

90% 的情况,根本不需要 Redis。一个被你忽略的事实是:PG 自己就是一个性能极强的内存数据库。

PG 启动的时候,会把 shared_buffers 那么多内存提前申请好,所有热数据都驻留在内存里。一个主键查询,大概率走的路径是:

Client → 网络 → PG postmaster → backend → buffer pool 命中 → 返回
                                       ↑
                                  纯内存,0 次 IO

延迟和 Redis 基本是一个数量级。

实测对比(8C/32GB,单条主键 lookup,本机):

没有数量级差距。而代价是:你省下了一个 Redis 集群、一套监控、一份运维文档、N 个双写一致性 bug、和一个永远要操心的 OOM 风险。

2.2 你必须懂的 PG buffer pool 工作原理

很多人把 shared_buffers 配成 128MB(默认值)然后抱怨 PG 慢,这是 PG 性能问题的 #1 根源。

PG 的内存结构如下:

┌─────────────────────────────────────────────┐
│              shared_buffers                  │  ← 所有进程共享,缓存表/索引页
│              (推荐:内存 25%-40%)            │
├─────────────────────────────────────────────┤
│              wal_buffers                     │  ← WAL 缓冲
├─────────────────────────────────────────────┤
│        每个 backend 的 work_mem              │  ← 排序、哈希用,临时
│        每个 backend 的 temp_buffers          │  ← 临时表
├─────────────────────────────────────────────┤
│                                              │
│            OS Page Cache                     │  ← OS 帮你缓存的文件页
│        (effective_cache_size 告诉规划器       │
│         大概有这么多,实际由 OS 管)          │
│                                              │
└─────────────────────────────────────────────┘

关键认知:

• 一个数据页可能同时存在 shared_buffers 和 OS cache(一份数据在内存被缓存了 2 次,看起来浪费,但 PG 设计就是这样)
• 第一次 SELECT 走磁盘,把页加载进 buffer pool
• 后续访问命中 buffer pool,完全不走磁盘,这就是"PG 当缓存用"的本质

监控你的缓存命中率(必看)

-- 全库缓存命中率(应该 > 99%)
SELECT
    sum(heap_blks_read) AS disk_reads,
    sum(heap_blks_hit)  AS cache_hits,
    round(sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit + heap_blks_read), 0), 4) AS hit_ratio
FROM pg_statio_user_tables;

-- 单表命中率
SELECT relname, heap_blks_read, heap_blks_hit,
       round(heap_blks_hit::numeric / nullif(heap_blks_hit + heap_blks_read, 0), 4) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC LIMIT 20;

hit_ratio < 0.99 是危险信号,要么 shared_buffers 太小,要么有大表全表扫,要么有人写了反索引 SQL。这一行 SQL 是 DBA 每天必看的指标。

2.3 五种缓存用法,从浅到深

用法一:啥也不做,信任 buffer pool

最朴素也最强大。先把配置调对:

# postgresql.conf,32GB 内存的机器
shared_buffers       = 8GB         # 25%
effective_cache_size = 24GB        # 75%(规划器用,不实际占用)
work_mem             = 32MB        # 注意:这是每连接每排序的!N 连接 × M 排序 × work_mem 总和
maintenance_work_mem = 2GB         # VACUUM、CREATE INDEX 用
random_page_cost     = 1.1         # SSD 时代,默认 4.0 太保守了

调好之后,热数据全在内存,SELECT 主键 ≈ 内存读。

用法二:UNLOGGED TABLE —— 不写 WAL 的高速表

WAL(Write-Ahead Log)是 PG 持久化的核心,但写 WAL 本身有开销。如果数据丢了无所谓(典型缓存语义),用 UNLOGGED:

CREATE UNLOGGED TABLE cache_kv (
    key        TEXT        PRIMARY KEY,
    value      JSONB       NOT NULL,
    expires_at TIMESTAMPTZ,
    hit_count  BIGINT      DEFAULT 0
);

UNLOGGED 表的特点(必须知道):

重点:UNLOGGED 表崩溃后表会被清空(不是丢一部分,是整个 truncate),所以只能存"丢了重新算就行"的数据。

典型用法:

• 应用层缓存(查询结果、API 响应、session token)
• 实时计数器(每分钟对账,丢了再算)
• 临时计算结果

用法三:Materialized View —— 预计算缓存(最强大)

聚合查询慢?把结果"物化"成一张表:

CREATE MATERIALIZED VIEW user_stats AS
SELECT
    user_id,
    count(*)            AS order_count,
    sum(amount)         AS total_spent,
    max(created_at)     AS last_order_at
FROM orders
GROUP BY user_id;

CREATE UNIQUE INDEX ON user_stats (user_id);

-- 刷新(默认会锁表)
REFRESH MATERIALIZED VIEW user_stats;

-- 推荐:并发刷新(需要唯一索引,不阻塞读)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

物化视图 vs Redis 缓存:

这是 BI / 报表场景的神器,Redis 根本做不到。

用法四:增量物化视图(实时刷新)

PG 原生 MV 是全量刷新,数据量大时慢。如果需要实时增量,有两个方案:

方案 A:pg_ivm 扩展(增量物化视图)

CREATE EXTENSION pg_ivm;

-- IMMV = Incrementally Maintained Materialized View
SELECT pgivm.create_immv('user_stats', $$
    SELECT user_id, count(*) AS order_count
    FROM orders GROUP BY user_id
$$);

-- 之后 INSERT/UPDATE/DELETE orders 时,user_stats 自动增量更新

方案 B:trigger 手动维护

CREATE TABLE user_stats_cache (
    user_id     BIGINT PRIMARY KEY,
    order_count INT NOT NULL DEFAULT 0,
    total_spent NUMERIC NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION update_user_stats() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_stats_cache (user_id, order_count, total_spent)
    VALUES (NEW.user_id, 1, NEW.amount)
    ON CONFLICT (user_id) DO UPDATE
    SET order_count = user_stats_cache.order_count + 1,
        total_spent = user_stats_cache.total_spent + EXCLUDED.total_spent;
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER orders_after_insert
AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION update_user_stats();

这种"trigger 维护的派生表"比应用层 + Redis 双写一致性强 100 倍——它就是事务的一部分,要么都成功,要么都回滚。

用法五:pg_prewarm —— 启动后立刻预热缓存

PG 重启后 buffer pool 是空的,前几分钟所有查询都走磁盘,性能悬崖。pg_prewarm 扩展可以主动把热数据加载进 buffer pool:

CREATE EXTENSION pg_prewarm;

-- 把整张表预加载
SELECT pg_prewarm('orders');

-- 把某个索引预加载
SELECT pg_prewarm('idx_orders_user_id');

-- 自动预热:重启时把上次缓存的页重新加载(autoprewarm 后台进程)
shared_preload_libraries = 'pg_prewarm'
pg_prewarm.autoprewarm = on

这是缓存型场景必装的扩展,关键时刻救命。

2.4 TTL 怎么实现(三种方案,深度对比)

PG 没有原生 TTL,但实现 TTL 有几种成熟方案。

方案 A:查询时过滤 + 定时清理

-- 写入时记录过期时间
INSERT INTO cache_kv (key, value, expires_at)
VALUES ($1, $2, now() + interval '1 hour')
ON CONFLICT (key) DO UPDATE
SET value = EXCLUDED.value, expires_at = EXCLUDED.expires_at;

-- 查询时过滤过期
SELECT value FROM cache_kv WHERE key = $1 AND expires_at > now();

-- pg_cron 定时清理(每 5 分钟一次)
SELECT cron.schedule('cleanup-cache', '*/5 * * * *',
  $$DELETE FROM cache_kv WHERE expires_at < now()$$);

优点:简单
缺点:DELETE 会产生 dead tuple,VACUUM 压力,大量 TTL 时不友好

方案 B:partition by time + DROP 分区(推荐大数据量)

CREATE TABLE cache_kv (
    key        TEXT,
    value      JSONB,
    expires_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (key, expires_at)
) PARTITION BY RANGE (expires_at);

CREATE TABLE cache_kv_2026w17 PARTITION OF cache_kv
    FOR VALUES FROM ('2026-04-21') TO ('2026-04-28');
CREATE TABLE cache_kv_2026w18 PARTITION OF cache_kv
    FOR VALUES FROM ('2026-04-28') TO ('2026-05-05');

-- 过期一整周?直接 DROP,毫秒级,无 dead tuple
DROP TABLE cache_kv_2026w17;

优点:删除接近 0 成本,无 vacuum 压力
缺点:粒度只到分区(比如一周),不能精确到秒级 TTL

方案 C:pg_partman + retention policy(推荐生产)

pg_partman 扩展自动管理分区:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.cache_kv',
    p_control      := 'expires_at',
    p_type         := 'native',
    p_interval     := 'weekly'
);

-- 自动 retain 4 周,过期分区自动 drop
UPDATE partman.part_config
SET retention = '4 weeks', retention_keep_table = false
WHERE parent_table = 'public.cache_kv';

生产环境的 TTL 缓存,这是终极方案。

2.5 缓存模式对照(把 Redis 模式翻译成 PG)

几乎所有 Redis 用法,PG 都有对应。

2.6 真实案例:37signals 删 Redis 之后

37signals(DHH 那个公司)2024 年公开博客:删掉 Redis 之后

"We replaced Redis with PostgreSQL for caching, sessions, and rate limiting. The cache hit rate stayed above 99%. We removed an entire tier of infrastructure. Production incidents dropped noticeably."

他们做的事:

• session:存 PG,带 TTL,用 partial index
• 缓存:SolidCache(Rails 8 内置),底层就是 PG 表
• 限流:计数器 + window,纯 SQL 实现

几个月后,运维心跳曲线第一次稳定下来。

2.7 什么时候真的需要 Redis

为了客观,以下场景 PG 真的输:

判断标准:如果你的"缓存"需求 < 10k QPS,PG 一定够。先调好 PG 再说要不要 Redis。

2.8 一段心法

"Cache invalidation" is one of the two hardest problems in computer science.
用 PG 当缓存,那个问题直接消失了 —— 因为业务和缓存在同一个事务里。

3. PG 当文档数据库用 —— 替代 MongoDB

3.1 残酷的真相

JSONB 出来之后,MongoDB 在技术上就没什么不可替代性了。

EnterpriseDB 在 2014 年(JSONB 刚出)做过一次 benchmark,PG 在 NoSQL 场景已经追上 Mongo;到了 2018 年,PG 的 JSONB 写入吞吐反超 MongoDB 数倍,内存占用更低(因为 JSONB 是二进制格式,而 Mongo 早期 BSON 在某些场景反而更胖)。

10 年过去,JSONB 几乎吸收了 Mongo 的全部 query 语义:@>、?、#>、JSONPath、生成列、表达式索引……该有的都有。

但最大的差距不是性能,而是这一条:

PG = 关系型 + 文档型;Mongo = 只是文档型。

你用 PG 时同时拥有了:ACID 事务、外键、SQL 关联、窗口函数、CTE、视图、Trigger、统计扩展、向量、时序…… 这些 Mongo 要么没有,要么是后加且性能差(Mongo 的多文档事务到了 4.0 才支持,有明显性能损失)。

Discord 的故事:早期用 MongoDB 存消息,2017 年迁回 Cassandra(后又上 ScyllaDB)。原因之一是 Mongo 在大量更新场景的写放大严重,而 JSONB 的 partial update 在 PG 里是一个简单事务。

3.2 你必须懂的 JSONB 内部存储

json vs jsonb 别用错:

永远用 jsonb,除非你有极端需求要保留键序和空格(比如签名验证)。

TOAST:大 JSONB 怎么存的

PG 一行(tuple)有 8KB 上限。如果你存一个 100KB 的 JSONB,PG 会自动:

1. 压缩(LZ4 或 PGLZ)
2. 拆分到 TOAST(The Oversized-Attribute Storage Technique)表里,每片 2KB
3. 主表只存一个指针

读取时 PG 透明地拼回来。这是 PG 处理大文档的秘密武器,也是为什么你存大 JSONB 不会爆。

但有个性能影响:只 SELECT 主表其它列时,TOAST 不会被读;只要 SELECT 那个 JSONB 列,TOAST 拼接成本就来了。所以——

优化 #1:如果文档大,且大部分查询不需要全文档,把"常查的属性"提到普通列(用生成列自动同步)。

生成列(Generated Column):JSONB 的最佳搭档

CREATE TABLE products (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data  JSONB NOT NULL,

    -- 从 JSONB 抽出来的列,自动维护
    name  TEXT  GENERATED ALWAYS AS (data->>'name') STORED,
    price INT   GENERATED ALWAYS AS ((data->>'price')::int) STORED,
    in_stock BOOLEAN GENERATED ALWAYS AS ((data->>'stock')::int > 0) STORED
);

-- 这些"虚拟列"可以建普通 B-tree 索引
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_products_in_stock ON products (in_stock) WHERE in_stock;

这是 Mongo 用户根本想不到的 PG 神技:JSONB 灵活 + 关系列高效,鱼和熊掌兼得。

3.3 JSONB 完整用法手册

基础查询(背下来)

INSERT INTO products (data) VALUES
('{"name":"iPhone","price":999,"tags":["phone","apple"],"specs":{"ram":8,"color":"black"}}');

-- ↓↓↓ 操作符速查 ↓↓↓

-- 1. 取字段(返回 jsonb)
SELECT data -> 'name' FROM products;        -- "iPhone"  (注意带引号)

-- 2. 取字段为文本(返回 text)
SELECT data ->> 'name' FROM products;       -- iPhone   (无引号)

-- 3. 路径访问 jsonb
SELECT data #> '{specs,ram}' FROM products; -- 8

-- 4. 路径访问 text
SELECT data #>> '{specs,ram}' FROM products; -- '8'

-- 5. 数组访问
SELECT data -> 'tags' -> 0 FROM products;   -- "phone"

-- 6. 包含查询(@>)
SELECT * FROM products WHERE data @> '{"tags":["apple"]}';

-- 7. 反向包含(<@)
SELECT * FROM products WHERE '{"name":"iPhone"}' <@ data;

-- 8. 键存在
SELECT * FROM products WHERE data ? 'specs';

-- 9. 任一键存在
SELECT * FROM products WHERE data ?| array['specs','features'];

-- 10. 全部键存在
SELECT * FROM products WHERE data ?& array['name','price'];

JSONPath(PG 12+,堪比 MongoDB DSL)

JSONPath 是处理嵌套/数组结构的杀手级特性:

-- 找所有 ram > 8 的产品
SELECT * FROM products WHERE data @? '$.specs.ram ? (@ > 8)';

-- 找标签数组里有 'apple' 的
SELECT * FROM products WHERE data @? '$.tags[*] ? (@ == "apple")';

-- 提取多个值
SELECT jsonb_path_query(data, '$.specs.*') FROM products;

-- 复杂条件
SELECT * FROM products
WHERE data @? '$ ? (@.price > 500 && @.specs.ram >= 8)';

JSONPath 表达力比 Mongo 的 find() 嵌套对象还强。

索引(灵魂!没建索引等于没用 JSONB)

-- 1. GIN 索引,默认支持 @>、?、?|、?&
CREATE INDEX idx_products_data ON products USING GIN (data);

-- 2. GIN jsonb_path_ops,只支持 @>,但更小更快(推荐!)
CREATE INDEX idx_products_data_path ON products USING GIN (data jsonb_path_ops);

-- 3. 表达式索引,针对特定路径
CREATE INDEX idx_products_tags ON products USING GIN ((data -> 'tags'));

-- 4. B-tree 索引,针对单值字段(等值/范围)
CREATE INDEX idx_products_price ON products (((data->>'price')::int));

-- 5. partial + 表达式,极致优化
CREATE INDEX idx_products_active_price
ON products (((data->>'price')::int))
WHERE data @> '{"status":"active"}';

索引选择指南:

陷阱:jsonb_path_ops 索引比默认小 30%-50%,但只支持 @>。99% 场景用它就够,记住默认就用 path_ops。

更新 JSONB(MongoDB 用户最爱的部分)

-- 1. 设置/添加字段
UPDATE products SET data = jsonb_set(data, '{price}', '888') WHERE id=$1;

-- 2. 设置嵌套字段
UPDATE products SET data = jsonb_set(data, '{specs,color}', '"red"') WHERE id=$1;

-- 3. 删除字段
UPDATE products SET data = data - 'old_field';
UPDATE products SET data = data #- '{specs,color}';   -- 删嵌套

-- 4. 深度合并(注意:|| 是浅合并!)
UPDATE products SET data = data || '{"specs":{"warranty":2}}'::jsonb;
-- 结果:specs 整个被覆盖!不是深合并

-- 5. 真正的深合并(PG 16+ 用 jsonb_merge,或自己写函数)
-- PG 14+ 推荐写法:
UPDATE products
SET data = jsonb_set(data, '{specs}', (data->'specs') || '{"warranty":2}'::jsonb)
WHERE id=$1;

-- 6. 数组操作:追加
UPDATE products SET data = jsonb_set(data, '{tags}', (data->'tags') || '"new_tag"');

-- 7. 数组操作:删除元素
UPDATE products SET data = jsonb_set(data, '{tags}',
    (data->'tags') - 'old_tag');

复杂查询:平展数组、聚合、关联

-- 平展数组(jsonb_array_elements)
SELECT id, tag
FROM products, jsonb_array_elements_text(data->'tags') AS tag
WHERE tag LIKE 'phone%';

-- JSONB 聚合
SELECT jsonb_agg(data) FROM products WHERE (data->>'price')::int < 1000;

-- 把 JSONB 转表(jsonb_to_record)
SELECT * FROM jsonb_to_record(
    '{"name":"iPhone","price":999}'::jsonb
) AS x(name TEXT, price INT);

-- JOIN 关系表 + JSONB
SELECT u.email, o.data->>'product_name' AS product
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.data @> '{"shipped":true}';

这种"JSONB + JOIN"是 Mongo 永远做不到的(Mongo 的 $lookup 性能堪忧且语义受限)。

3.4 Schema 演进策略(JSONB 项目最大的隐藏话题)

JSONB 的"灵活"是双刃剑——一不小心就变成"什么都往里塞"的垃圾桶。生产里几个最佳实践:

1. 核心列 + JSONB 元数据

CREATE TABLE events (
    id          UUID PRIMARY KEY,
    user_id     BIGINT NOT NULL,         -- 核心,必查
    event_type  TEXT   NOT NULL,         -- 核心,必查
    occurred_at TIMESTAMPTZ NOT NULL,    -- 核心,必查
    properties  JSONB  NOT NULL          -- 灵活,业务定义
);

核心字段做普通列(可外键、可强约束),弹性属性塞 JSONB。

2. JSONB 也可以加 schema 校验(pg_jsonschema)

CREATE EXTENSION pg_jsonschema;

ALTER TABLE products ADD CONSTRAINT data_valid CHECK (
    jsonb_matches_schema('{
        "type": "object",
        "required": ["name","price"],
        "properties": {
            "name":  {"type":"string"},
            "price": {"type":"number","minimum":0}
        }
    }', data)
);

这就把 JSONB 变成了"半结构化但有约束"的字段,Mongo 的 schema validation 也是这套。

3. 渐进式迁移 JSONB → 普通列

随着业务清晰,把高频字段从 JSONB 迁出来:

-- 加普通列,先用生成列双写
ALTER TABLE products ADD COLUMN price INT
    GENERATED ALWAYS AS ((data->>'price')::int) STORED;
CREATE INDEX ON products (price);

-- 应用代码改读 普通 price 列
-- 一段时间后,从 JSONB 里删掉(可选)

JSONB 的最佳实践:从灵活开始,逐步演化成结构化。这是 schema-less 数据库做不到的——它们只能一直 schema-less。

3.5 性能基准:JSONB vs MongoDB

社区 benchmark(2023 年,1000 万文档):

结论:在大多数维度,JSONB 等于或优于 MongoDB。

3.6 真还需要 MongoDB 的场景

诚实地说,以下场景 Mongo 仍占优:

• ✅ 数据严格 schema-less,且永远不需要关联查询(罕见)
• ✅ 超大规模 sharding(几百节点起步,Atlas 自动 sharding 顺手)
• ✅ MongoDB Atlas 的全托管体验(Atlas Search、Charts 等生态)
• ✅ 团队历史包袱(已有 Mongo 代码,迁移成本高)

新项目 99% 应该选 PG + JSONB。

3.7 一段心法

JSONB 不是为了让你"用 PG 当 Mongo",
而是让你写 SQL 时也能享受 schema-less 的灵活,写 NoSQL 风格代码时也能享受 ACID 的安全。

鱼和熊掌兼得,这才是 PG 的魔法。

4. PG 当全文搜索引擎用 —— 替代 Elasticsearch(中小规模)

4.1 一个被低估的"重型武器"

很多团队的"搜索演化路线":

1. 一开始用 LIKE '%keyword%' —— 跑得动,但全表扫,数据量一上来就慢成狗。
2. 加索引?LIKE '%xx%' 不走 B-tree 索引(前缀通配)。
3. 上 Elasticsearch —— 增加一个 JVM 集群、ZooKeeper(老版本)、Logstash、Kibana……运维成本指数上升。
4. 一年后发现:ES 集群比业务库还耗资源,但日均搜索 QPS 只有几百。

90% 这种场景,PG 自带的全文搜索就是答案。它从 PG 8.3(2008 年)就有,十几年迭代下来,功能不亚于一个轻量版 ES。

业界最响亮的案例:

• 37signals:删 ES,搜索全部用 PG,延迟反而降低
• Notion:核心搜索就是 PG + tsvector
• Sentry:日志搜索曾用 ES,后来部分迁回 PG
• GitLab:代码搜索之外的搜索全在 PG

4.2 你必须懂的 tsvector 内部机制

PG 全文搜索不是简单的 LIKE,它内部做了一整套 IR(信息检索)流水线:

原文本 "PostgreSQL is a great database"
   ↓ 1. 分词(tokenization)
['PostgreSQL', 'is', 'a', 'great', 'database']
   ↓ 2. 标准化(lowercase)
['postgresql', 'is', 'a', 'great', 'database']
   ↓ 3. 字典处理(stop words / 词干)
   - 'is', 'a' 是停用词,丢掉
   - 'database' → 'databas' (Snowball 词干)
['postgresql', 'great', 'databas']
   ↓ 4. 加位置 + 权重
'postgresql':1 'great':4 'databas':5
   ↓ 5. 存为 tsvector

最终的 tsvector 是这样的:

SELECT to_tsvector('english', 'PostgreSQL is a great database');
--      'databas':5 'great':4 'postgresql':1

为什么 'database' 变成 'databas'? 因为 Snowball 词干提取算法,把 database / databases / databased 都规约为同一个词根,这样搜 'databases' 也能命中。

字典(dictionary)是核心

PG 的 text search config 包含一条字典链,文本被依次喂给:

parser → simple → english_stem → english_hunspell → english_synonym ...

每个字典处理特定职责:

• simple:仅小写化
• english_stem:Snowball 词干
• synonym:同义词替换
• thesaurus:多词组短语
• unaccent:去重音(法语 café → cafe)

你可以自定义字典链,这是 ES 的 analyzer chain 在 PG 里的等价物。

-- 看默认 english 配置
\dF+ english

-- 自定义 config
CREATE TEXT SEARCH CONFIGURATION my_config (COPY = english);
ALTER TEXT SEARCH CONFIGURATION my_config
    ALTER MAPPING FOR word, asciiword WITH unaccent, english_stem;

4.3 完整的全文搜索 schema(可直接抄)

CREATE TABLE articles (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    body        TEXT NOT NULL,
    author      TEXT,
    tags        TEXT[],
    published_at TIMESTAMPTZ,

    -- 关键:tsvector 用生成列,自动维护
    -- 关键:setweight 给不同字段不同权重(A最高 → D最低)
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')),  'A') ||
        setweight(to_tsvector('english', coalesce(author, '')), 'B') ||
        setweight(to_tsvector('english', array_to_string(coalesce(tags, ARRAY[]::text[]), ' ')), 'C') ||
        setweight(to_tsvector('english', coalesce(body, '')),   'D')
    ) STORED
);

-- 关键索引
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- 时间维度 partial,提高最近内容的查询效率
CREATE INDEX idx_articles_recent
ON articles (published_at DESC)
WHERE published_at > now() - interval '90 days';

setweight 是相关度排序的灵魂 —— 标题命中比正文命中更重要,这一行配置直接决定了搜索质量。

4.4 完整查询手册

基础检索

-- 简单 AND 查询
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & database');

-- 网页搜索语法(websearch_to_tsquery)— PG 11+ 推荐
-- 直接接受 "postgres database" -junk 这种用户友好语法
SELECT id, title FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'postgres database -mysql');

-- 短语搜索(连续词)
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'distributed system');

websearch_to_tsquery 支持:

• word1 word2 → AND
• "word1 word2" → 短语
• -word → NOT
• OR → OR

这就是 Google 风格的搜索语法,直接拿来给前端用就行。

相关度排序(ts_rank)

SELECT id, title,
       ts_rank(search_vector, q) AS rank,
       ts_rank_cd(search_vector, q) AS rank_cd  -- cover density,考虑距离
FROM articles, websearch_to_tsquery('english', 'postgres queue') q
WHERE search_vector @@ q
ORDER BY rank_cd DESC
LIMIT 20;

ts_rank vs ts_rank_cd:

• ts_rank:基于 TF(词频)
• ts_rank_cd:Cover Density,考虑词在文档中的距离——挨在一起的得分更高

生产推荐 ts_rank_cd,效果更接近 ES 的 BM25。

高亮(给前端展示用)

SELECT id, title,
       ts_headline('english', body, q,
           'StartSel=<mark>, StopSel=</mark>, MaxWords=30, MinWords=15')
FROM articles, websearch_to_tsquery('english', 'postgres') q
WHERE search_vector @@ q;

输出:

"PostgreSQL is a fantastic <mark>database</mark>. <mark>postgres</mark> rules."

这一行直接拿来贴到搜索结果页,不需要前端做任何处理。

多表搜索(用 UNION 或视图)

CREATE VIEW search_index AS
    SELECT id, 'article' AS type, title AS heading, search_vector FROM articles
    UNION ALL
    SELECT id, 'comment',  body  AS heading, search_vector FROM comments;

SELECT * FROM search_index
WHERE search_vector @@ websearch_to_tsquery('english', 'postgres')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'postgres')) DESC
LIMIT 20;

4.5 中文搜索(必看)

PG 自带的分词器只对空格语言友好。中文需要装第三方分词扩展:

实战:pg_jieba

CREATE EXTENSION pg_jieba;

-- pg_jieba 提供几种 mode
SELECT to_tsvector('jiebacfg',  '我爱使用 PostgreSQL 数据库');  -- 精确模式
-- 输出: '使用':3 '我爱':1 '数据库':5 'postgresql':4

SELECT to_tsvector('jieba_mp',  '南京市长江大桥');  -- 多分词
-- 输出: '南京':1 '南京市':2 '长江':4 '长江大桥':5 '大桥':6 '市长':3

-- 在表里用
CREATE TABLE cn_docs (
    id BIGSERIAL PRIMARY KEY,
    title TEXT, body TEXT,
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('jiebacfg', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('jiebacfg', coalesce(body, '')),  'B')
    ) STORED
);
CREATE INDEX ON cn_docs USING GIN (search_vector);

-- 查询
SELECT * FROM cn_docs
WHERE search_vector @@ to_tsquery('jiebacfg', '数据库 & 优化');

注意:中文 websearch_to_tsquery 也支持,但要传对 config 名:

SELECT * FROM cn_docs
WHERE search_vector @@ websearch_to_tsquery('jiebacfg', '"数据库优化"');

4.6 模糊匹配 / 拼写容错(pg_trgm)

tsvector 处理"完整词",但用户经常拼错字 / 输入残词。这时候用 trigram(三元字符组):

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);

-- 1. 相似度 % 操作符(默认阈值 0.3)
SELECT title FROM articles WHERE title % 'postgrss';
-- 拼错的 'postgrss' 也能匹配到 'postgres'

-- 2. 自定义阈值
SET pg_trgm.similarity_threshold = 0.5;

-- 3. 排序
SELECT title, similarity(title, 'postgrss') AS sim
FROM articles
WHERE title % 'postgrss'
ORDER BY sim DESC LIMIT 10;

-- 4. 替代 LIKE '%xx%'(走 trigram 索引,极快!)
SELECT * FROM articles WHERE title ILIKE '%postgres%';
-- 注意:必须有 trigram 索引才会快

pg_trgm 还有一个杀手锏:它让 LIKE '%xx%' 也能走索引!这一招就能让大部分"模糊查询"性能提升 100 倍。

4.7 混合检索:全文 + 模糊 + 向量

生产级搜索从来不是单一手段,多路召回 + 重排序才是王道:

-- 召回 A:全文匹配
WITH fts AS (
    SELECT id, ts_rank_cd(search_vector, q) * 1.0 AS score
    FROM articles, websearch_to_tsquery('english', 'postgres') q
    WHERE search_vector @@ q
    LIMIT 100
),
-- 召回 B:模糊匹配
trgm AS (
    SELECT id, similarity(title, 'postgres') * 0.5 AS score
    FROM articles
    WHERE title % 'postgres'
    LIMIT 100
),
-- 召回 C:向量相似(见 §5)
vec AS (
    SELECT id, (1 - (embedding <=> $1::vector)) * 0.8 AS score
    FROM articles
    ORDER BY embedding <=> $1::vector
    LIMIT 100
)
SELECT id, sum(score) AS final_score
FROM (
    SELECT * FROM fts UNION ALL
    SELECT * FROM trgm UNION ALL
    SELECT * FROM vec
) t
GROUP BY id
ORDER BY final_score DESC
LIMIT 20;

这是一套完整的现代搜索架构,全部在 PG 里,一句 SQL。ES 想做这个要拼好几个组件。

4.8 性能基准

PG 全文搜索的极限大约就在 1 亿文档级别,再往上 ES 优势开始明显。

但95% 的业务搜索都在千万级以下,PG 完美胜任。

4.9 你必须躲开的几个坑

坑 1:没用 STORED 生成列,每次查询都重算 tsvector

反例:

-- 这种写法每次查询都临时算 tsvector!慢死
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('postgres');

正解:用生成列 + GIN 索引(见 §4.3)。

坑 2:GIN 索引写入慢

GIN 索引的写入开销比 B-tree 高几倍。如果是高写入场景,启用 fastupdate:

ALTER INDEX idx_articles_search SET (fastupdate = on, gin_pending_list_limit = '4MB');

但记得监控 pg_stat_user_indexes 里的 pending list 大小,过大要手动 gin_clean_pending_list。

坑 3:中文分词词典更新

pg_jieba 默认词典不包含很多业务专名词(品牌、人名)。必须加自定义词典:

echo "PostgreSQL\nDroid\n字节跳动" >> /path/to/jieba/userdict.txt

否则你搜"字节跳动"会被切成"字节" + "跳动",搜索质量灾难。

坑 4:相关度排序需要 LIMIT

ts_rank 是计算密集型,不要在百万行结果上排序。永远配合 LIMIT,先用索引收口:

-- 反例:全集排序
SELECT * FROM articles WHERE search_vector @@ q ORDER BY ts_rank(...);

-- 正解:GIN 索引收口 + 重排序
SELECT * FROM (
    SELECT id, search_vector FROM articles
    WHERE search_vector @@ q
    LIMIT 1000     -- 先收 1000 个候选
) t
ORDER BY ts_rank(search_vector, q) DESC
LIMIT 20;

4.10 真需要 ES 的场景

诚实地说:

新项目搜索量级 < 千万,无脑选 PG。等真不够用再换 ES。

4.11 一段心法

ES 是搜索引擎,PG 是数据库。
但当你的搜索需求 < ES 的最低复杂度时,PG 这个"兼职搜索引擎"反而比"专业搜索引擎"轻便、稳定、贴近业务。

所有的"专业方案"都有最低运维成本,而 PG 的运维你已经付过了。

5. PG 当向量数据库用 —— 替代 Pinecone / Milvus

5.1 AI 时代 PG 最闪光的一面

2023 年,pgvector 一个扩展,让一整个赛道的纯向量数据库公司估值集体跳水。这不是夸张。

为什么?因为 RAG / Agent 应用的核心痛点,纯向量库根本解不了。

举个例子,一个企业知识库 RAG 的真实查询:

检索:"我们公司去年北美市场的销售策略" 的相关文档
限定:当前用户有权访问 + 文档 未过期 + 文档类型 = "策略报告" + 时间范围 2024-2025

纯向量库(Pinecone、Weaviate)需要:

1. 应用层先查权限服务,拿到该用户能看的 doc_ids
2. 应用层把 doc_ids 切片(metadata 过大 Pinecone 拒绝)
3. 调向量库,带上 metadata filter
4. 拿到向量结果后,再回业务库 fetch 详情
5. 应用层重排
6. 代码量 > 200 行,延迟叠加 4 个网络往返

PG + pgvector 的实现:

SELECT d.id, d.title, d.content, d.created_at,
       1 - (d.embedding <=> $1::vector) AS similarity
FROM documents d
JOIN user_doc_permissions p ON p.doc_id = d.id
WHERE p.user_id = $2
  AND d.expires_at > now()
  AND d.doc_type = 'strategy_report'
  AND d.created_at BETWEEN '2024-01-01' AND '2025-12-31'
ORDER BY d.embedding <=> $1::vector
LIMIT 10;

一句 SQL,搞定。 这就是为什么:

"Vector is just another data type." —— Andrew Kane,pgvector 作者

业界证据:

• OpenAI 自己的应用 用 PG + pgvector 做内部 RAG
• Supabase:整个 AI 栈基于 pgvector
• Neon / Crunchy Data / TimescaleDB / EnterpriseDB:全部把 pgvector 列为头牌特性
• Anthropic、Cohere 的官方文档 都用 pgvector 当首选示例

5.2 pgvector 内部原理(必看)

向量类型

pgvector 提供三种向量类型:

生产强烈推荐 halfvec:

• OpenAI text-embedding-3-large(3072 维):float32 = 12KB/向量,float16 = 6KB
• 千万向量:float32 = 120GB,half = 60GB
• 召回率几乎不变(实测差距 < 1%)

三种距离操作符

99% RAG 用 <=>(余弦距离),因为 OpenAI / 大部分模型的 embedding 都已 normalize。

5.3 HNSW vs IVFFlat:你必须选对的索引

pgvector 有两种近似最近邻(ANN)索引,选错了性能差 10 倍:

IVFFlat(老方案)

原理:聚类(K-means)+ 倒排索引

• 把所有向量先聚成 N 个簇
• 查询时只搜最近的 nprobe 个簇

CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);   -- 数据量 / 1000 大约是 sqrt(N) 经验值

-- 查询时调精度
SET ivfflat.probes = 10;   -- 默认 1,越大越准但越慢

优点:建索引快、内存小
缺点:召回率不如 HNSW,需要先有数据才能建(冷启动需要数据)

HNSW(推荐)

原理:Hierarchical Navigable Small World,多层图结构

• 每个向量是图的一个节点
• 上层稀疏(快速跳跃),下层稠密(精细搜索)
• 类似跳表的多层索引

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 查询时调精度
SET hnsw.ef_search = 40;   -- 默认 40,越大越准

参数解读:

• m:每个节点的连接数(默认 16,16 够用,32 更准但贵 2x)
• ef_construction:建索引时的搜索宽度(默认 64,256 是质量上限)
• ef_search:查询时的搜索宽度(默认 40,40-200 之间动态调)

对比表:

结论:99% 选 HNSW。除非你内存特别紧张,或者数据集过亿且每天大量重建。

5.4 metadata filtering:pgvector 的核心战场

这是 PG 比 Pinecone 强 10 倍的地方。

三种过滤策略

-- 策略 1:Pre-filter(先 filter 再搜)
-- 适合:filter 选择性高(< 10% 数据)
SELECT id FROM docs
WHERE user_id = $1 AND status = 'active'   -- 假设这个条件命中 1000 行
ORDER BY embedding <=> $2::vector LIMIT 10;

-- 策略 2:Post-filter(先搜再 filter)
-- 适合:filter 选择性低(> 50% 数据)
SELECT id FROM (
    SELECT id, user_id, status, embedding
    FROM docs
    ORDER BY embedding <=> $2::vector LIMIT 100
) t
WHERE user_id = $1 AND status = 'active'
LIMIT 10;

-- 策略 3:Iterative scan(pgvector 0.8+ 杀手锏)
-- 自动决定 pre/post,逐步扩大搜索范围直到拿够 10 个
SET hnsw.iterative_scan = on;

重点:pgvector 0.8(2024)的 iterative_scan 是革命性升级——它让 metadata filter 不再需要手动调 ef_search,自动迭代直到拿够结果。这一步直接抹平了和 Pinecone 的功能差距。

partial 索引 + 多向量索引

-- partial HNSW:只索引活跃文档
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
WHERE status = 'active';

-- 多个 partial 索引,按 tenant 分
CREATE INDEX docs_t1_emb ON docs USING hnsw (embedding vector_cosine_ops) WHERE tenant_id = 1;
CREATE INDEX docs_t2_emb ON docs USING hnsw (embedding vector_cosine_ops) WHERE tenant_id = 2;

这种 per-tenant 索引在多租户 RAG 里效果显著,Pinecone 做不到。

5.5 完整的 RAG schema(可直接抄)

CREATE EXTENSION vector;

-- 文档表
CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    tenant_id   BIGINT NOT NULL,
    user_id     BIGINT,
    title       TEXT,
    source      TEXT,                       -- 来源 URL / 文件路径
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT now(),
    expires_at  TIMESTAMPTZ
);

-- chunk 表(一篇文档切多块)
CREATE TABLE chunks (
    id          BIGSERIAL PRIMARY KEY,
    doc_id      BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    seq         INT NOT NULL,                -- chunk 顺序
    content     TEXT NOT NULL,
    token_count INT,
    embedding   halfvec(1536),               -- 用 halfvec 省空间
    -- 也存一份 tsvector,做混合检索
    search_vector tsvector GENERATED ALWAYS AS (to_tsvector('simple', content)) STORED
);

-- 关键索引
CREATE INDEX chunks_doc ON chunks (doc_id);
CREATE INDEX chunks_emb ON chunks USING hnsw (embedding halfvec_cosine_ops) WITH (m=16, ef_construction=64);
CREATE INDEX chunks_fts ON chunks USING GIN (search_vector);

-- 多租户的 partial 索引(可选,租户多时强烈推荐)
CREATE INDEX chunks_emb_active ON chunks USING hnsw (embedding halfvec_cosine_ops)
    WHERE doc_id IN (SELECT id FROM documents WHERE expires_at IS NULL OR expires_at > now());

完整的混合检索 SQL(向量 + 全文,RRF 融合)

RRF(Reciprocal Rank Fusion) 是工业级 RAG 的标准融合算法:

WITH vec_search AS (
    SELECT c.id, RANK() OVER (ORDER BY c.embedding <=> $1::halfvec) AS rank
    FROM chunks c
    JOIN documents d ON c.doc_id = d.id
    WHERE d.tenant_id = $2 AND (d.expires_at IS NULL OR d.expires_at > now())
    ORDER BY c.embedding <=> $1::halfvec
    LIMIT 50
),
fts_search AS (
    SELECT c.id, RANK() OVER (ORDER BY ts_rank_cd(c.search_vector, q) DESC) AS rank
    FROM chunks c
    JOIN documents d ON c.doc_id = d.id,
         websearch_to_tsquery('simple', $3) q
    WHERE d.tenant_id = $2 AND c.search_vector @@ q
    LIMIT 50
)
SELECT c.id, c.content, c.doc_id,
       sum(1.0 / (60 + r.rank)) AS rrf_score        -- RRF 公式,k=60
FROM (
    SELECT id, rank FROM vec_search UNION ALL
    SELECT id, rank FROM fts_search
) r
JOIN chunks c ON c.id = r.id
GROUP BY c.id, c.content, c.doc_id
ORDER BY rrf_score DESC
LIMIT 10;

这就是工业级混合检索,你直接抄走能省一周开发。

5.6 性能基准

(2024 年公开 benchmark,百万向量,768 维)

pgvector 在性能上完全在第一梯队,而你免费获得了 PG 的所有其他能力。

5.7 你必须躲开的几个坑

坑 1:不调 maintenance_work_mem,建索引慢成狗

HNSW 建索引非常吃内存。默认 maintenance_work_mem=64MB 时,千万向量建索引可能要 24 小时。

# 临时提升
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 8;

-- 然后建索引
CREATE INDEX CONCURRENTLY ON chunks USING hnsw ...

提到 8GB 之后,千万向量建索引降到 30-60 分钟。

坑 2:UPDATE embedding 触发索引重建

每次更新一个 embedding,HNSW 索引都要修复图结构。不要频繁 UPDATE 同一行的 embedding。

最佳实践:embedding 一次性写入,不更新。如果文档变了,新插入一行,标记旧的为 archived。

坑 3:维度选择影响巨大

OpenAI 的 text-embedding-3-large 是 3072 维,但支持 truncate —— 你可以用前 1024 维或 256 维,召回率只下降 2-3%,索引大小降 3-12 倍。

embedding = openai_embed(text, dimensions=1024)  # 主动 truncate

生产强推荐:1024 维 + halfvec,性能与质量的甜区。

坑 4:忘了 ANALYZE

PG 规划器需要统计信息才能选对索引。新建 HNSW 索引后必须 ANALYZE:

ANALYZE chunks;

否则可能走全表扫,慢 100 倍。

5.8 真需要 Pinecone / Milvus 的场景

否则,pgvector is all you need。

5.9 一段心法

AI 时代,数据 + 向量必须在一起。
把它们切开放在两个数据库,等于把肉和骨头分开装,搬运代价巨大。
pgvector 出现的意义不是"PG 多了一个功能",而是让 PG 成为 AI 应用的天然数据底座。

6. PG 当时序数据库用 —— 替代 InfluxDB

6.1 一个被 PG 吞掉的赛道

时序数据库(TSDB)曾经是 2015-2020 年最热门的 NoSQL 赛道:InfluxDB、Prometheus、TDengine、Druid、QuestDB、Apache IoTDB……一堆专业方案。

然后 TimescaleDB 出现了。

它是一个 PG 扩展,装上之后,普通的 PG 表瞬间变成可以扛百亿行的时序库。它的杀手锏是:SQL 100% 兼容,你以前的 PG 知识全部继续生效。

更猛的是 InfluxDB 自己:它从 InfluxQL → Flux → 又回到 SQL,自我打脸地承认 SQL 才是终极答案。InfluxDB 3.0 干脆基于 Apache DataFusion + Parquet,变成一个"披着 InfluxDB 皮的 SQL 数据库"。

业界证据:

• Stack Overflow 的所有指标存储都用 TimescaleDB
• Cloudflare Logpush 用 TimescaleDB
• CERN(欧洲核子研究中心) 的物理实验数据用 TimescaleDB
• Mirantis、Comcast、Bloomberg 的内部监控
• 各大 IoT 平台:MotorTrend、E-On、Schneider Electric

新项目搞监控/IoT/金融行情/任何带时间戳的数据,无脑选 TimescaleDB。

6.2 你必须懂的 hypertable 内部原理

普通 PG 表插入越来越慢的原因:索引越来越大,B-tree 高度增加,缓存不下。

TimescaleDB 的解法是:把一张逻辑大表自动切成很多小物理表(chunk),每个 chunk 按时间区间保存:

逻辑表: metrics
  ├── _hyper_1_1_chunk     (2026-04-01 ~ 2026-04-08)
  ├── _hyper_1_2_chunk     (2026-04-08 ~ 2026-04-15)
  ├── _hyper_1_3_chunk     (2026-04-15 ~ 2026-04-22)
  └── _hyper_1_4_chunk     (2026-04-22 ~ 2026-04-29)

效果:

1. 写入永远写在最新 chunk,小、热、缓存友好,写入速度恒定
2. 查询时间范围 → 自动只扫相关 chunk(chunk exclusion)
3. drop 老 chunk 是 DROP TABLE,毫秒级,不是 DELETE
4. 每个 chunk 独立 vacuum、独立索引、独立压缩

这就是为什么 TimescaleDB 能扛百亿行而单 PG 表挂掉的原因。

6.3 完整可用的 schema

CREATE EXTENSION timescaledb;

CREATE TABLE metrics (
    time      TIMESTAMPTZ NOT NULL,
    device_id INT          NOT NULL,
    metric    TEXT         NOT NULL,
    value     DOUBLE PRECISION,
    tags      JSONB
);

-- 一键转 hypertable
SELECT create_hypertable(
    'metrics', 'time',
    chunk_time_interval => INTERVAL '1 day',     -- 一天一个 chunk
    if_not_exists => TRUE
);

-- 时间序列必备索引
CREATE INDEX ON metrics (device_id, time DESC);
CREATE INDEX ON metrics (metric, time DESC);

chunk 间隔怎么选? 经验值:

• 每个 chunk 应该能装进 25% 内存(让活跃 chunk 在 buffer pool 里)
• 写入速度 1k/s → 一天一个
• 写入速度 10k/s → 一小时一个
• 写入速度 1M/s → 几分钟一个

太大查询慢,太小元数据膨胀。SELECT chunk_relation_size('metrics'); 查看现状。

多列 + 空间分区(高基数维度场景)

如果 device_id 很多(几百万设备),只按时间分区还会慢。可以双维分区:

SELECT create_hypertable(
    'metrics', 'time',
    chunk_time_interval => INTERVAL '1 day'
);

SELECT add_dimension('metrics', 'device_id', number_partitions => 16);
-- device_id 通过 hash 分到 16 个空间分区

这相当于给时序数据自带 sharding,InfluxDB / Prometheus 都很难做到。

6.4 time_bucket:时序 SQL 的核心动词

-- 每 5 分钟聚合
SELECT time_bucket('5 minutes', time) AS bucket,
       device_id,
       avg(value) AS avg_v,
       max(value) AS max_v,
       percentile_disc(0.95) WITHIN GROUP (ORDER BY value) AS p95
FROM metrics
WHERE metric = 'cpu' AND time > now() - interval '1 day'
GROUP BY bucket, device_id
ORDER BY bucket;

-- 不规则时段(如交易日窗口)
SELECT time_bucket('1 hour', time, '08:00') AS bucket, ...
-- 从 8 点对齐桶起点

-- 时间补齐(填充无数据的桶)
SELECT time_bucket_gapfill('5 minutes', time, now() - interval '1 day', now()) AS bucket,
       device_id,
       avg(value),
       interpolate(avg(value)) AS interpolated_v,    -- 线性插值
       locf(avg(value))         AS last_observed     -- 用上次值填充
FROM metrics
WHERE device_id = $1
GROUP BY bucket, device_id;

time_bucket_gapfill + interpolate 是时序场景的杀手锏——监控大盘上的"为什么这一段没数据"难题,SQL 一行搞定。

6.5 连续聚合(Continuous Aggregates):时序 MV 的进化版

普通物化视图是全量刷新,百亿行刷一次要几小时。TimescaleDB 的连续聚合只增量计算新数据:

-- 创建连续聚合
CREATE MATERIALIZED VIEW metrics_5min
WITH (timescaledb.continuous) AS
SELECT time_bucket('5 minutes', time) AS bucket,
       device_id,
       avg(value)  AS avg_v,
       max(value)  AS max_v,
       min(value)  AS min_v,
       count(*)    AS n
FROM metrics
GROUP BY bucket, device_id
WITH NO DATA;

-- 自动刷新策略
SELECT add_continuous_aggregate_policy('metrics_5min',
    start_offset      => INTERVAL '1 day',     -- 重算最近 1 天(允许迟到数据)
    end_offset        => INTERVAL '5 minutes', -- 不算最近 5 分钟(还在变化)
    schedule_interval => INTERVAL '1 minute'); -- 每分钟跑一次

-- 查询直接走预聚合,飞快
SELECT * FROM metrics_5min
WHERE device_id = 42 AND bucket > now() - interval '1 hour';

多层连续聚合(分级预聚合):

-- 5 分钟粒度
CREATE MATERIALIZED VIEW metrics_5min ...;

-- 1 小时粒度,基于 5 分钟粒度建!
CREATE MATERIALIZED VIEW metrics_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', bucket) AS bucket,
       device_id, avg(avg_v), max(max_v), min(min_v)
FROM metrics_5min
GROUP BY 1, 2;

-- 1 天粒度,基于 1 小时粒度
CREATE MATERIALIZED VIEW metrics_1d ...;

Grafana 大盘根据查询时间范围自动选粒度,百亿行的 dashboard 也能秒开。

6.6 列存压缩:节省 90%+ 空间

TimescaleDB 的压缩是把行存 chunk 重组为列存格式 + 字典编码 + Delta-Delta + Gorilla(Facebook 的浮点压缩算法):

ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',           -- 列存按 device_id 分组
    timescaledb.compress_orderby   = 'time DESC, metric'    -- 时间倒序
);

-- 自动压缩 7 天前的数据
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- 看压缩效果
SELECT pg_size_pretty(before_compression_total_bytes) AS before,
       pg_size_pretty(after_compression_total_bytes)  AS after,
       round(100 * (1 - after_compression_total_bytes::numeric / before_compression_total_bytes), 2) AS ratio_pct
FROM hypertable_compression_stats('metrics');
-- 典型输出: before=120GB, after=12GB, ratio_pct=90%

真实案例:某 IoT 公司 3 亿行/天的设备数据,压缩前 2TB,压缩后 180GB,省 91%,且查询速度因为列存反而快 2-5x(因为只读相关列)。

6.7 数据保留策略

-- 90 天前的数据自动删除(DROP CHUNK,毫秒级)
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- 高级:不同维度的差异化保留
-- 用 hierarchical aggregates,原始 7 天,5min 30 天,1h 1 年,1d 永久
SELECT add_retention_policy('metrics',     INTERVAL '7 days');
SELECT add_retention_policy('metrics_5min', INTERVAL '30 days');
SELECT add_retention_policy('metrics_1h',   INTERVAL '1 year');
-- metrics_1d 不加 → 永久保留

这是工业级时序数据治理的标准模式,InfluxDB 也叫 retention policy,但 TimescaleDB 的 SQL 可读性更好。

6.8 性能基准(TimescaleDB 官方 + 第三方)

(写入吞吐,1000 列宽,8C/32GB):

TimescaleDB 在 99% 时序场景都领先,极端 OLAP 大宽表场景会输给 ClickHouse(那是另一个赛道,见 §14)。

6.9 真实玩法:观测平台只用 PG

一个只有 PG + TimescaleDB 的完整观测体系:

-- 1. metrics 表(指标)
CREATE TABLE metrics (...);
SELECT create_hypertable('metrics','time');

-- 2. logs 表(日志)
CREATE TABLE logs (
    time TIMESTAMPTZ, service TEXT, level TEXT,
    message TEXT,
    fields JSONB,
    search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', message)) STORED
);
SELECT create_hypertable('logs','time');
CREATE INDEX ON logs USING GIN (search_vector);
CREATE INDEX ON logs (service, time DESC);

-- 3. traces 表(链路)
CREATE TABLE traces (
    time TIMESTAMPTZ, trace_id UUID, span_id UUID, parent_span_id UUID,
    service TEXT, operation TEXT, duration_ms NUMERIC,
    attributes JSONB
);
SELECT create_hypertable('traces','time');
CREATE INDEX ON traces (trace_id);
CREATE INDEX ON traces USING GIN (attributes jsonb_path_ops);

Grafana 直接连 PG(它支持 PostgreSQL 数据源),把 metrics + logs + traces 都画上。Loki + Tempo + Prometheus 三件套被一个 PG 替代。

6.10 你必须躲开的坑

坑 1:用 PG 自带的 INSERT,而不是 COPY 大批量

时序数据写入要用 COPY 或 INSERT ... VALUES ((...),(...),(...)),单行 INSERT 慢 100 倍。

坑 2:chunk 太多 → 元数据爆炸

每个 chunk 是一张表,chunk 太小(1 分钟一个)跑一年就有 50 万张表,PG 元数据卡住。保持 chunk 数 < 1 万。

坑 3:没设 retention,磁盘炸

时序数据是无限增长的,忘了加 retention policy = 早晚磁盘满。第一天就要设。

坑 4:time 字段没索引或 chunk 字段错

-- 反例:用 created_at 当 chunk 字段,但查询都按 event_time 过滤
SELECT create_hypertable('events','created_at');
-- 查询 event_time 时无法 chunk exclusion,慢

chunk 字段必须是查询最常用的时间字段。

6.11 真需要 InfluxDB / ClickHouse 的场景

诚实地说:

新项目 99% 选 TimescaleDB。

6.12 一段心法

时序数据库不是关系型数据库的对立面,
它只是关系型数据库针对时间维度做的特化。
TimescaleDB 用最优雅的方式证明了:特化不必脱离 SQL。

7. PG 当图数据库用 —— 替代 Neo4j(轻度场景)

7.1 诚实开场:这一块 PG 是相对最弱的

不像前面几节我可以拍胸脯说"PG 完全够",图数据库这块,我得诚实一点。

重度图查询(社交网络深度遍历、知识图谱推理、PageRank、社区发现),Neo4j / TigerGraph / Nebula 仍然有显著优势——它们的存储引擎、查询引擎、内存模型都是为"图遍历"专门设计的。

但好消息是:90% 的项目根本不需要"图数据库",只是需要"能查关系"。这种场景 PG 三种方案任选其一都够用:

1. Recursive CTE(SQL 标准,任何 PG 都有)
2. Apache AGE 扩展(Cypher 查询语言,等价 Neo4j)
3. ltree 扩展(树形结构特化)

下面分别讲。

7.2 方案 A:Recursive CTE(递归查询)

最朴素也最通用,SQL 标准里就有。

完整示例:N 度关注网络

CREATE TABLE follows (
    follower_id BIGINT NOT NULL,
    followee_id BIGINT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (follower_id, followee_id)
);

CREATE INDEX idx_follows_follower ON follows (follower_id);
CREATE INDEX idx_follows_followee ON follows (followee_id);

-- 查 user=1 的 3 度关注网络(BFS)
WITH RECURSIVE network AS (
    -- 锚定项(anchor):起点
    SELECT followee_id AS user_id,
           1 AS depth,
           ARRAY[1::BIGINT, followee_id] AS path     -- 记录路径,防环
    FROM follows
    WHERE follower_id = 1

    UNION ALL

    -- 递归项:从已发现的节点继续走
    SELECT f.followee_id,
           n.depth + 1,
           n.path || f.followee_id
    FROM follows f
    JOIN network n ON f.follower_id = n.user_id
    WHERE n.depth < 3
      AND f.followee_id <> ALL(n.path)              -- 防环关键!
)
SELECT user_id, min(depth) AS shortest_distance
FROM network
GROUP BY user_id
ORDER BY shortest_distance;

两个关键技巧:

• ARRAY 记录路径,防止成环死循环(社交图必备)
• UNION ALL + 外层 min 取最短距离,而不是 UNION DISTINCT(后者性能差)

性能优化:用 BREADTH 控制方向(PG 14+)

WITH RECURSIVE network AS (
    SELECT 1::BIGINT AS user_id, 0 AS depth
    UNION ALL
    SELECT f.followee_id, n.depth + 1
    FROM follows f JOIN network n ON f.follower_id = n.user_id
    WHERE n.depth < 3
) SEARCH BREADTH FIRST BY user_id SET ordercol      -- 显式 BFS,规划器更友好
SELECT * FROM network;

Recursive CTE 的极限

3 度以内 OK,5 度以上请上 AGE 或专业图库。

7.3 方案 B:Apache AGE(Cypher in PostgreSQL)

Apache AGE 是 PG 的图扩展,直接支持 Cypher 查询语言(Neo4j 用的那个)。

CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

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

-- 创建节点和边(Cypher 风格)
SELECT * FROM cypher('social', $$
    CREATE (alice:Person {name: 'Alice', age: 30}),
           (bob:Person {name: 'Bob', age: 25}),
           (alice)-[:FOLLOWS]->(bob)
$$) AS (a agtype);

-- 多跳查询
SELECT * FROM cypher('social', $$
    MATCH (a:Person {name: 'Alice'})-[:FOLLOWS*1..3]->(b:Person)
    RETURN b.name, b.age
$$) AS (name agtype, age agtype);

-- 复杂模式:朋友的朋友推荐(共同好友数排序)
SELECT * FROM cypher('social', $$
    MATCH (me:Person {name: 'Alice'})-[:FOLLOWS]->(friend)-[:FOLLOWS]->(suggested)
    WHERE NOT (me)-[:FOLLOWS]->(suggested) AND suggested <> me
    RETURN suggested.name AS name, count(*) AS common_friends
    ORDER BY common_friends DESC
    LIMIT 10
$$) AS (name agtype, common_friends agtype);

这就是 Neo4j 用户最熟悉的语法,直接搬过来。

AGE 的好处:

• Cypher 表达力强,多跳查询写起来比 SQL 优雅 10 倍
• 与普通 SQL 在同一个 PG 实例,可以混合查询
• 随 PG 备份、复制

AGE 的局限:

• 比 Neo4j 慢(没有专门的图存储引擎)
• 社区相对小,文档不如 Neo4j
• 复杂图算法(PageRank、Betweenness)支持弱

适合场景:项目主要用 PG,但有少量图查询需求,不想再上一个 Neo4j。

7.4 方案 C:ltree —— 树形结构特化

如果你的"关系"是树(分类、组织架构、目录、评论嵌套),不是任意图,ltree 是降维打击。

CREATE EXTENSION ltree;

CREATE TABLE categories (
    id   SERIAL PRIMARY KEY,
    name TEXT,
    path LTREE NOT NULL                             -- 例如:'electronics.phone.iphone'
);

CREATE INDEX idx_categories_path ON categories USING GIST (path);

INSERT INTO categories (name, path) VALUES
    ('Electronics',  'electronics'),
    ('Phone',        'electronics.phone'),
    ('iPhone',       'electronics.phone.iphone'),
    ('Android',      'electronics.phone.android'),
    ('Laptop',       'electronics.laptop');

-- 查询子树(电子产品下所有)
SELECT * FROM categories WHERE path <@ 'electronics';

-- 查询祖先
SELECT * FROM categories WHERE path @> 'electronics.phone.iphone';

-- 模式匹配(电子产品下任意手机)
SELECT * FROM categories WHERE path ~ 'electronics.phone.*';

-- 找直接子节点
SELECT * FROM categories WHERE path ~ 'electronics.*{1}';

性能:百万级树节点,任意子树查询 < 1ms。秒杀任何关系自连接方案。

典型用法:

• 电商商品分类
• 组织架构
• 评论嵌套(Reddit 风格)
• 文件目录

7.5 方案对比

7.6 真需要 Neo4j / TigerGraph 的场景

• ✅ 重度图算法(PageRank、社区发现、最短路径)
• ✅ 关系深度 > 5 的高频查询
• ✅ 图分析为核心的业务(反欺诈、知识图谱)
• ✅ 团队已经全员 Cypher 流派

否则,PG 三选一足够。

8. PG 当地理空间引擎用 —— 行业标准 PostGIS

8.1 这一节没什么好讨论的(但要讲透)

PostGIS 是 GIS 行业的事实标准,Google Maps 之外几乎所有地图/位置服务都在用:

• Uber(早期)、Foursquare、Airbnb、Lyft:核心 location 引擎
• Mapbox、Carto、Mapzen:地图服务底座
• OpenStreetMap:全球开源地图,后端就是 PostGIS
• 美国 USDA、FAA、NOAA、欧洲 ESA、联合国 的官方 GIS 系统

它没有"替代品"这个说法——商业地理空间软件(Esri ArcGIS)几十年的功能,PostGIS 都做到了,而且开源免费。这不是夸张,GIS 行业普遍共识。

8.2 你必须懂的两个类型:geometry vs geography

PostGIS 有两个相似但不同的类型,新手最容易选错:

经验法则:

• 你不知道 SRID 是啥 → 用 geography
• 全球应用 → 用 geography
• 性能极致敏感 + 局部地图 → 用 geometry + 合适投影

8.3 SRID:坐标系编号

PostGIS 用 SRID(Spatial Reference System Identifier)区分坐标系。常用:

中国坐标的坑:GPS 输出 WGS84,但百度/高德地图用 GCJ-02 / BD-09 偏移坐标。直接用 GPS 坐标查百度地图会偏几百米。生产里要做坐标转换,搜 coord_china 库或自实现。

8.4 完整例子:外卖配送场景

CREATE EXTENSION postgis;

-- 商户表
CREATE TABLE shops (
    id        SERIAL PRIMARY KEY,
    name      TEXT NOT NULL,
    location  GEOGRAPHY(Point, 4326) NOT NULL,
    delivery_range_m INT NOT NULL DEFAULT 3000     -- 配送范围 3km
);

-- 关键索引:GiST(用于空间数据)
CREATE INDEX idx_shops_location ON shops USING GIST (location);

-- 插入(注意经度在前!ST_MakePoint(lng, lat))
INSERT INTO shops (name, location) VALUES
    ('麦当劳·王府井', ST_MakePoint(116.4097, 39.9152)::geography),
    ('星巴克·三里屯', ST_MakePoint(116.4565, 39.9384)::geography);

-- 业务查询 1:找用户 5km 内的店,按距离排序
SELECT
    id, name,
    ST_Distance(location, ST_MakePoint($1, $2)::geography) AS dist_m
FROM shops
WHERE ST_DWithin(location, ST_MakePoint($1, $2)::geography, 5000)
ORDER BY location <-> ST_MakePoint($1, $2)::geography     -- KNN 操作符,走索引!
LIMIT 20;

-- 业务查询 2:用户位置是否在某商户的配送范围内
SELECT *
FROM shops
WHERE ST_DWithin(location, ST_MakePoint($1, $2)::geography, delivery_range_m);

<-> 操作符是 KNN(K-Nearest Neighbor)查询,走 GiST 索引,亿级数据 ms 级。这是 PostGIS 的杀手级特性。

8.5 高级几何:多边形、面、行政区

-- 行政区表(多边形)
CREATE TABLE districts (
    id         SERIAL PRIMARY KEY,
    name       TEXT,
    boundary   GEOGRAPHY(MultiPolygon, 4326)
);
CREATE INDEX ON districts USING GIST (boundary);

-- 用户位置属于哪个区?
SELECT name FROM districts
WHERE ST_Within(ST_MakePoint($1, $2)::geography, boundary);

-- 两个区是否接壤?
SELECT a.name, b.name
FROM districts a, districts b
WHERE a.id < b.id AND ST_Touches(a.boundary, b.boundary);

-- 区域面积(平方公里)
SELECT name, ST_Area(boundary) / 1e6 AS area_km2 FROM districts;

行政区数据:网上能下到全国行政区 GeoJSON / Shapefile,直接 ogr2ogr 导入 PostGIS,5 分钟搞定。

8.6 路径与轨迹

CREATE TABLE driver_tracks (
    driver_id BIGINT,
    time      TIMESTAMPTZ,
    location  GEOGRAPHY(Point, 4326)
);

-- 把一段时间的轨迹连成一条线
SELECT driver_id,
       ST_MakeLine(location::geometry ORDER BY time)::geography AS path,
       ST_Length(ST_MakeLine(location::geometry ORDER BY time)::geography) AS total_m
FROM driver_tracks
WHERE driver_id = 1 AND time BETWEEN $1 AND $2
GROUP BY driver_id;

-- 路径是否经过某区域?
SELECT * FROM driver_tracks d, districts dist
WHERE dist.name = '朝阳区' AND ST_Intersects(d.location, dist.boundary);

配合 TimescaleDB,把 driver_tracks 转成 hypertable,实时位置 + 历史轨迹分析,一个 PG 通吃。

8.7 地理 + 业务 + 时间维度,一锅炖

外卖派单的真实查询:

-- 找一个最适合接单的骑手:
-- 1. 在 5km 内
-- 2. 在线
-- 3. 当前订单数 < 3
-- 4. 评分 >= 4.5
-- 按距离 + 评分综合排序
SELECT d.id,
       ST_Distance(d.location, $1::geography) AS dist,
       d.rating,
       d.current_orders
FROM drivers d
WHERE d.online = TRUE
  AND d.current_orders < 3
  AND d.rating >= 4.5
  AND ST_DWithin(d.location, $1::geography, 5000)
ORDER BY (ST_Distance(d.location, $1::geography) / 1000) - (d.rating * 100) ASC
LIMIT 1;

Neo4j / Mongo / Redis 没法做这种"地理 + 业务 + 排序"的混合查询,只有 PostGIS 这种"完整 SQL + 空间能力"才能优雅解决。

8.8 性能基准

亿级数据无压力,这是 PostGIS 几十年优化打磨的结果。

8.9 一段心法

地图就是一种带着空间维度的关系数据。
用 PostGIS,你就是在 SQL 里画地图。
这种优雅,是 Mongo Geo / Redis Geo 永远给不了的。

9. PG 当定时任务调度器用 —— 替代 Cron / Airflow

9.1 你想想这个场景

你写了一个数据清理 SQL,需要每天凌晨 3 点跑一次。怎么做?

经典方案:

1. 起一台机器
2. 跑一个 cron daemon
3. 写个 shell 脚本,里面 psql -c "..."
4. 配 SSH key、防火墙、监控、告警
5. 这台机器还要做高可用(主备)
6. 备份这台机器的 cron 配置
7. 下一个开发新人接手,完全不知道这个 cron 存在

整个过程,真正有意义的代码就是那一句 SQL,周边却堆了一堆基础设施。

pg_cron 让这一切回归初心:SQL 数据 + SQL 调度,逻辑就在数据旁边。

9.2 pg_cron 完整用法

安装与配置

# postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'mydb'

-- 重启后
CREATE EXTENSION pg_cron;

调度任务

-- 每 5 分钟清理过期缓存
SELECT cron.schedule('cleanup-cache', '*/5 * * * *',
    $$DELETE FROM cache_kv WHERE expires_at < now()$$);

-- 每天凌晨 3 点归档(多语句)
SELECT cron.schedule('archive-jobs', '0 3 * * *', $$
    WITH moved AS (
        DELETE FROM jobs
        WHERE status IN ('success','dead')
          AND finished_at < now() - interval '7 days'
        RETURNING *
    )
    INSERT INTO jobs_archive SELECT * FROM moved;
$$);

-- 每周一早 9 点发周报(调用业务函数)
SELECT cron.schedule('weekly-report', '0 9 * * 1',
    $$SELECT generate_weekly_report()$$);

-- 每分钟做心跳巡检(配合 §1 的队列)
SELECT cron.schedule('jobs-heartbeat-watchdog', '* * * * *', $$
    UPDATE jobs SET status = 'pending', worker_id = NULL
    WHERE status = 'running' AND heartbeat_at < now() - interval '2 minutes'
$$);

-- 跨数据库执行(PG 16+,新特性)
SELECT cron.schedule_in_database(
    'cleanup-other-db', '0 4 * * *',
    'DELETE FROM logs WHERE created_at < now() - interval ''30 days''',
    'logs_db');

管理任务

-- 查看所有任务
SELECT jobid, schedule, command, jobname, active FROM cron.job;

-- 查看运行历史(必看!出问题就靠这个)
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC LIMIT 50;

-- 失败的任务
SELECT * FROM cron.job_run_details
WHERE status = 'failed' ORDER BY start_time DESC;

-- 取消任务
SELECT cron.unschedule('cleanup-cache');

-- 临时禁用
UPDATE cron.job SET active = false WHERE jobname = 'archive-jobs';

9.3 pg_cron 跟 OS cron 的本质差异

核心优势:逻辑就在数据旁边。

9.4 复杂场景:pg_timetable

pg_cron 是 cron-like(时间触发简单 SQL),如果你需要:

• 任务依赖链(A 完成才跑 B)
• 重试策略 / 超时控制
• 任务分组、并发控制
• 跨任务变量传递

pg_timetable 是更强大的 PG 调度方案(独立守护进程,但所有数据存 PG):

-- 创建一个 chain(任务链)
SELECT timetable.add_job(
    job_name      => 'daily-etl',
    job_schedule  => '0 2 * * *',
    job_command   => 'SELECT extract_data()',
    job_kind      => 'SQL'
);

-- 给 chain 加后续步骤
SELECT timetable.add_task(
    task_name => 'transform',
    kind      => 'SQL',
    command   => 'SELECT transform_data()',
    chain_id  => (SELECT chain_id FROM timetable.chain WHERE chain_name = 'daily-etl')
);

SELECT timetable.add_task(
    task_name => 'notify',
    kind      => 'SHELL',
    command   => 'curl -X POST https://...'
);

pg_timetable 支持 SQL / SHELL / BUILTIN 多种任务类型,基本就是 PG 版的 mini Airflow。

9.5 真实案例:整个 ETL 流水线在 PG 里

-- 凌晨 1 点:从源表抽取增量
SELECT cron.schedule('etl-extract', '0 1 * * *', $$
    INSERT INTO staging.events
    SELECT * FROM source.events
    WHERE event_time > (SELECT max(event_time) FROM staging.events)
$$);

-- 凌晨 2 点:转换
SELECT cron.schedule('etl-transform', '0 2 * * *', $$
    INSERT INTO marts.daily_summary
    SELECT date_trunc('day', event_time), user_id, count(*)
    FROM staging.events
    WHERE event_time >= current_date - 1
    GROUP BY 1, 2
    ON CONFLICT (day, user_id) DO UPDATE SET cnt = EXCLUDED.cnt
$$);

-- 凌晨 3 点:刷新报表
SELECT cron.schedule('etl-refresh-report', '0 3 * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY weekly_kpi$$);

-- 每天告警(没数据时报警)
SELECT cron.schedule('etl-alert', '0 4 * * *', $$
    INSERT INTO alerts (level, msg)
    SELECT 'critical', 'Yesterday data missing!'
    WHERE NOT EXISTS (
        SELECT 1 FROM marts.daily_summary
        WHERE day = current_date - 1
    )
$$);

整套数据管道,0 行 Python,0 个 Airflow,纯 PG。

9.6 监控与告警(必做)

-- 每天检查最近 24 小时是否有失败任务
SELECT cron.schedule('cron-monitor', '0 8 * * *', $$
    INSERT INTO alerts (level, msg, payload)
    SELECT 'warning',
           'pg_cron job failures in last 24h: ' || count(*),
           jsonb_agg(jsonb_build_object('job', jobname, 'time', start_time, 'error', return_message))
    FROM cron.job_run_details
    WHERE status = 'failed' AND start_time > now() - interval '24 hours'
    HAVING count(*) > 0;
$$);

9.7 真需要 Airflow 的场景

单纯定时跑 SQL,pg_cron 就是答案。

9.8 一段心法

数据治理的最高境界是:数据自己治理自己。
pg_cron 让"清理、归档、聚合、巡检"这些治理动作和数据共生,而不是分布在某台失忆的机器上。

10. PG 当分布式锁/信号量用 —— 替代 Redis 的 SETNX

10.1 一个不为人知的 PG 神器

很多用了 10 年 PG 的人都不知道 pg_advisory_lock 这个函数。

但如果你了解它,Redis 分布式锁这一整套库(Redlock、Redisson、Curator)都可以扔掉。

10.2 PG 的锁体系全图

PG 内部有 4 层锁,从粗到细:

1. 表级锁(Table Lock)        ← LOCK TABLE / DDL 用,粗粒度
2. 行级锁(Row Lock)          ← FOR UPDATE / FOR SHARE,跟着事务走
3. 页级锁(Page Lock)          ← PG 内部用,你看不到
4. Advisory Lock(咨询锁)      ← 应用层用,任意整数 key

Advisory Lock 是 PG 给应用程序留的"自由锁":

• PG 不解释这个锁的含义,不管它锁的是什么
• 你给一个 64 位整数(或两个 32 位整数)当 key
• PG 帮你保证"同一时刻只有一个会话能持有这个 key"

用法分两个维度,组合出 4 种 API:

完整 API 表

-- 阻塞式获取(锁不到就等)
SELECT pg_advisory_lock(123);              -- session 级,要手动释放
SELECT pg_advisory_xact_lock(123);         -- 事务级,COMMIT/ROLLBACK 自动释放

-- 非阻塞获取(锁不到立刻返回 false)
SELECT pg_try_advisory_lock(123);          -- session 级
SELECT pg_try_advisory_xact_lock(123);     -- 事务级,推荐!

-- 释放(只有 session 级需要)
SELECT pg_advisory_unlock(123);
SELECT pg_advisory_unlock_all();           -- 释放当前 session 所有锁

-- 双 32 位整数版本(给你两个命名空间)
SELECT pg_try_advisory_xact_lock(NAMESPACE, RESOURCE_ID);

-- 共享锁(读锁)
SELECT pg_advisory_xact_lock_shared(123);

生产推荐 pg_try_advisory_xact_lock(ns, id):

• 非阻塞(可以做 fail-fast)
• 事务级(自动释放,绝对不会死锁)
• 双 key(命名空间隔离)

10.3 命名空间设计模式

为不同业务场景分配不同的命名空间(高 32 位):

class LockNamespace:
    CRON_JOB    = 1
    USER_LOCK   = 2
    LLM_RATE    = 3
    MIGRATION   = 4
    OUTBOX      = 5

# 用户 42 的临界区
got = await conn.fetchval(
    "SELECT pg_try_advisory_xact_lock($1, $2)",
    LockNamespace.USER_LOCK, 42
)

好处:不会跨业务串号,可读性强。

10.4 实战 1:分布式定时任务防重

多个 PG cron 实例 / Sidekiq scheduler / Quartz cluster 同时跑同一个任务,只允许一个执行:

async def run_cron_safely(job_id: int, fn):
    async with conn.transaction():
        got = await conn.fetchval(
            "SELECT pg_try_advisory_xact_lock($1, $2)",
            LockNamespace.CRON_JOB, job_id
        )
        if not got:
            return  # 别人在跑
        await fn()
    # 事务结束 → 锁自动释放

这是分布式 scheduler 的最优雅实现,Quartz cluster 那一套 SQL 表 + ROW LOCK 都比不上。

10.5 实战 2:全局并发信号量(N 个槽位)

你的 LLM 服务最多并发 20 调用,多实例部署时必须全局限流:

LLM_NS = LockNamespace.LLM_RATE
SLOT_COUNT = 20

async def call_llm_with_global_limit(prompt: str):
    for slot in range(SLOT_COUNT):
        async with conn.transaction():
            got = await conn.fetchval(
                "SELECT pg_try_advisory_xact_lock($1, $2)",
                LLM_NS, slot
            )
            if got:
                # 抢到 slot,在事务内调 LLM
                return await llm_api.call(prompt)
    raise BusyError("All 20 slots busy, retry later")

关键巧思:用 0~19 共 20 个 slot,worker 抢任意一个就能跑。这就是分布式信号量。

跟 Redis Redlock 比:

• Redis Redlock 需要 5 节点 quorum,复杂、误用很多、有理论争议
• PG advisory lock 简单、明确、随事务自动释放
• 不需要"我什么时候释放锁"的心智负担

10.6 实战 3:启动时迁移防重

多实例服务启动时跑数据库迁移,只让一个实例跑:

async def run_migrations_once():
    conn = await pg.connect()
    async with conn.transaction():
        got = await conn.fetchval(
            "SELECT pg_try_advisory_xact_lock($1)", 9999
        )
        if not got:
            print("Another instance is migrating, skip")
            return
        await apply_migrations()
        # 事务结束自动释放

Flyway / Liquibase 内部就是这个原理。

10.7 SELECT FOR UPDATE 各种模式对比

除了 advisory lock,PG 还有行级锁的多种模式,用对了能优雅解决很多并发问题:

生产里最常用三种:

• FOR UPDATE:经典更新前锁
• FOR UPDATE SKIP LOCKED:队列出队
• FOR NO KEY UPDATE:有外键引用时,默认推荐(避免不必要的强锁)

10.8 跟 Redis 锁的对比

90% 场景 PG advisory lock 更优,Redis 锁只在"百万 QPS 加锁"场景有意义。

10.9 一段心法

一个好的并发原语,不是看它能锁多快,而是看它能否在任何异常下都不死锁。
Redis 锁靠 TTL 防死锁(导致"我以为锁还在,其实已被人抢"),
PG advisory lock 靠事务生命周期防死锁(无歧义,无心智负担)。
后者才是真正的工程美学。

11. PG 直接吐 API —— 替代后端 CRUD 层

11.1 一个被严重低估的赛道

10 年前我们写后端:Controller → Service → DAO → DB。

90% 的代码在做什么?做 CRUD 的样板代码。

PostgREST / Hasura / Supabase 这一系工具的核心思想是:既然 90% 的后端就是数据库的薄包装,那直接把 PG schema 暴露成 API 不就完了?

听起来激进,但这已经是 Supabase 整个商业模式的基石(估值 5 亿美金以上),Firebase 的 PG 替代品。

11.2 PostgREST 完整能力

PostgREST 是一个 Haskell 写的极快单 binary,启动后:

• 每张表 → 一个 RESTful endpoint
• 每个视图 → 一个 endpoint
• 每个函数 → 一个 RPC endpoint
• JWT 鉴权、RLS 行级权限
• 支持复杂查询、JOIN、聚合

# postgrest.conf
db-uri = "postgres://app:secret@localhost/mydb"
db-schemas = "api"
db-anon-role = "anonymous"
jwt-secret = "your-secret"
server-port = 3000

postgrest postgrest.conf
# 起一个进程,搞定

自动生成的 API

# 1. 列表 + 过滤 + 排序 + 分页
curl 'http://localhost:3000/products?price=gt.100&category=eq.phone&order=created_at.desc&limit=20&offset=40'

# 2. 单条
curl 'http://localhost:3000/products?id=eq.42'

# 3. 创建
curl -X POST 'http://localhost:3000/products' \
  -H 'Content-Type: application/json' \
  -d '{"name":"iPhone","price":999}'

# 4. 更新
curl -X PATCH 'http://localhost:3000/products?id=eq.42' \
  -d '{"price":888}'

# 5. 删除
curl -X DELETE 'http://localhost:3000/products?id=eq.42'

# 6. 嵌入资源(JOIN)
curl 'http://localhost:3000/orders?select=*,user:users(name,email),items(*)'
# 这一行 = 多表 JOIN + 嵌套返回 JSON

# 7. 聚合
curl 'http://localhost:3000/products?select=category,count' --header 'Prefer: count=exact'

# 8. 调用函数(RPC)
curl -X POST 'http://localhost:3000/rpc/search_articles' \
  -d '{"keyword":"postgres"}'

这个查询能力比手写后端 CRUD 还强,且毫无样板代码。

11.3 RLS:数据库层的鉴权

Row Level Security(行级安全)是 PG 9.5 起的内置特性,让数据库自己决定每个用户能看哪些行:

ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- 政策 1:用户只能看到自己创建的
CREATE POLICY user_owns_products ON products
    FOR SELECT USING (user_id = current_setting('jwt.claims.user_id')::bigint);

-- 政策 2:用户只能改自己的
CREATE POLICY user_modifies_own ON products
    FOR UPDATE USING (user_id = current_setting('jwt.claims.user_id')::bigint);

-- 政策 3:管理员看到全部
CREATE POLICY admin_full_access ON products
    FOR ALL TO admin_role USING (true);

配合 PostgREST 的 JWT,前端发请求带 token,PostgREST 把 JWT claims 注入到 PG 的 current_setting,RLS 自动过滤行。

整个鉴权层被压缩成几行 SQL。Supabase 的"几小时就能上线一个全栈应用"卖点核心就是这个。

11.4 完整 SaaS 多租户案例

-- schema
CREATE TABLE tenants (id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, tenant_id BIGINT, email TEXT);
CREATE TABLE projects (id BIGSERIAL PRIMARY KEY, tenant_id BIGINT, name TEXT);

-- 启用 RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE users    ENABLE ROW LEVEL SECURITY;

-- 多租户隔离:每个用户只能看到自己 tenant 的项目
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = (current_setting('jwt.claims.tenant_id'))::bigint);

CREATE POLICY tenant_isolation_users ON users
    USING (tenant_id = (current_setting('jwt.claims.tenant_id'))::bigint);

之后,前端用不同 tenant 的 JWT 调同一个 endpoint,自动隔离。

这是 SaaS 多租户最优雅的实现。比 ORM 层手动 WHERE tenant_id = ? 更安全(忘加 WHERE 是经典事故,RLS 把它变成数据库强制约束)。

11.5 PostgREST vs Hasura vs Supabase 对比

新项目选择:

• 想自己掌控、最轻量 → PostgREST
• GraphQL 主义 → Hasura
• 要 BaaS 体验 → Supabase

11.6 适合 / 不适合的场景

强烈适合:

• ✅ 管理后台 / 中台(CRUD 占 80%)
• ✅ MVP / Hackathon(快速上线)
• ✅ 数据 API(给数据团队的内部 API)
• ✅ 简单业务的全栈应用(博客、待办、笔记)
• ✅ 只读分析 API(BI 工具的后端)

不适合(还是该有正经后端):

• ❌ 复杂业务流程(订单 + 库存 + 支付的多步事务)
• ❌ 重度第三方集成(支付、短信、AI)
• ❌ 性能极致优化的核心服务

最佳实践:主业务用正经后端,次要业务(管理后台、报表、内部工具)用 PostgREST。两全其美。

11.7 一段心法

90% 的"后端代码"是 CRUD 样板,
10% 是真正的业务逻辑。

把 90% 交给 PostgREST,你才有时间打磨那 10%。

12. PG 当审计日志用 —— 替代埋点系统

12.1 一个真实合规故事

某金融客户被监管要求:任何客户敏感字段的修改都必须留痕,可追溯到操作人,保存 7 年。

他们的初版方案:

1. 业务代码里手动写日志
2. 漏掉了大概 30% 的修改路径(批量脚本、运维 SQL、应急修复)
3. 一次合规检查直接被罚

第二版:Trigger + History 表 + pgaudit,所有路径无差别留痕,从 SQL 层做到合规。审计员 SQL 一查就有,简单直接。

这一节讲怎么做到。

12.2 三种粒度,三种方案

12.3 pgaudit:数据库会话级审计

CREATE EXTENSION pgaudit;

# postgresql.conf
pgaudit.log = 'write, ddl, role'    -- write/ddl/role/read/all
pgaudit.log_relation = on            -- 记录表名
pgaudit.log_parameter = on           -- 记录参数
pgaudit.log_statement_once = on      -- 多语句只记一次

输出到 PG 日志:

AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.orders,
       UPDATE orders SET status='shipped' WHERE id=42,
       <none>

生产配置建议:

• pgaudit.log = 'write, ddl'(只审计写入和 DDL,读操作太多)
• 用 pg_log 集中收集到 SIEM(Splunk / ELK)
• 配合 log_min_duration_statement = 0 可以记录所有语句(注意性能)

12.4 业务级审计:Trigger + History 表

通用方案

每张需要审计的业务表,配一张 _audit 历史表 + 一个统一 trigger:

-- 1. 历史表
CREATE TABLE orders_audit (
    audit_id    BIGSERIAL   PRIMARY KEY,
    audit_op    CHAR(1)     NOT NULL,            -- I/U/D
    audit_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    audit_by    TEXT,                            -- 操作人(从 app.user 设置进来)
    audit_ip    INET,
    audit_txid  BIGINT      DEFAULT txid_current(),

    -- 原表的所有列(用 JSONB 兜底,无需 schema 同步)
    row_id      TEXT        NOT NULL,
    old_row     JSONB,
    new_row     JSONB,
    diff        JSONB                             -- 仅变更字段
);

CREATE INDEX ON orders_audit (row_id, audit_at DESC);
CREATE INDEX ON orders_audit (audit_at DESC);
CREATE INDEX ON orders_audit (audit_by);

-- 2. 通用 trigger 函数
CREATE OR REPLACE FUNCTION audit_trigger_fn() RETURNS TRIGGER AS $$
DECLARE
    v_old jsonb;
    v_new jsonb;
    v_diff jsonb;
    v_id   text;
BEGIN
    v_old := CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) ELSE NULL END;
    v_new := CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) ELSE NULL END;
    v_id  := COALESCE(NEW.id::text, OLD.id::text);

    -- 计算 diff
    IF TG_OP = 'UPDATE' THEN
        SELECT jsonb_object_agg(k, jsonb_build_object('old', v_old->k, 'new', v_new->k))
        INTO v_diff
        FROM (
            SELECT key AS k FROM jsonb_each(v_new)
            EXCEPT
            SELECT key FROM jsonb_each(v_old)
            WHERE v_new->key IS DISTINCT FROM v_old->key
        ) t;
    END IF;

    EXECUTE format('INSERT INTO %I_audit (audit_op, audit_by, audit_ip, row_id, old_row, new_row, diff)
                    VALUES ($1,$2,$3,$4,$5,$6,$7)', TG_TABLE_NAME)
    USING substring(TG_OP, 1, 1),
          current_setting('app.user', true),
          current_setting('app.ip', true)::inet,
          v_id, v_old, v_new, v_diff;

    RETURN COALESCE(NEW, OLD);
END $$ LANGUAGE plpgsql;

-- 3. 给业务表加 trigger
CREATE TRIGGER orders_audit_trg
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

应用层注入操作人

业务连接数据库时,设置一下当前用户:

async with conn.transaction():
    await conn.execute("SELECT set_config('app.user', $1, true)", current_user.email)
    await conn.execute("SELECT set_config('app.ip',   $1, true)", request.client.host)
    # 后续所有 SQL 的 trigger 都能拿到这两个值
    await conn.execute("UPDATE orders SET status='shipped' WHERE id=42")

这套方案的好处:

• 所有业务路径无差别留痕(应用代码、批量脚本、psql 手动跑)
• diff 字段可以快速看到"改了什么"
• JSONB 兜底,业务表加字段不需要改 audit 表

查询历史

-- 看某订单的所有变更
SELECT audit_at, audit_by, audit_op, diff
FROM orders_audit
WHERE row_id = '42'
ORDER BY audit_at DESC;

-- 看某用户最近一周改了什么
SELECT audit_at, TG_TABLE_NAME, row_id, diff
FROM orders_audit
WHERE audit_by = 'alice@example.com'
  AND audit_at > now() - interval '7 days'
ORDER BY audit_at DESC;

12.5 Temporal Tables:时间旅行查询

CREATE EXTENSION temporal_tables;

CREATE TABLE products (
    id          INT PRIMARY KEY,
    name        TEXT, price NUMERIC,
    sys_period  tstzrange NOT NULL DEFAULT tstzrange(now(), null)
);

-- 历史表
CREATE TABLE products_history (LIKE products);

-- 一行 trigger 启用时间旅行
CREATE TRIGGER products_versioning
BEFORE INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'products_history', true);

之后:

-- 看 2026-04-01 时刻的产品价格(时间旅行)
SELECT * FROM products WHERE id = 1
  AND sys_period @> '2026-04-01'::timestamptz
UNION ALL
SELECT * FROM products_history WHERE id = 1
  AND sys_period @> '2026-04-01'::timestamptz;

这就是 SVN for your data:任何时刻的数据快照都能查到。

12.6 SCD Type 2:维度数据演进

业务里经常需要维度演进(用户改名、客户更换归属、产品调价)。SCD-2(Slowly Changing Dimension Type 2) 是数据仓库的经典模型:

CREATE TABLE customer_scd2 (
    surrogate_key BIGSERIAL PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    name          TEXT,
    region        TEXT,
    valid_from    TIMESTAMPTZ NOT NULL,
    valid_to      TIMESTAMPTZ,            -- NULL 代表当前
    is_current    BOOLEAN GENERATED ALWAYS AS (valid_to IS NULL) STORED
);

CREATE INDEX ON customer_scd2 (customer_id, valid_to);
CREATE UNIQUE INDEX ON customer_scd2 (customer_id) WHERE valid_to IS NULL;

这种结构让你可以在 BI 报表里"以历史身份"统计——比如"2024 年时这个客户属于哪个区域?"。

12.7 一段心法

数据真正的价值不是它当下的样子,
而是它怎么变成现在这个样子的轨迹。
一个不留历史的系统,等于一个失忆的人。

13. PG 做 CDC / 流处理 —— 替代 Kafka 的部分场景

13.1 一个被低估的能力

CDC(Change Data Capture)是现代数据架构的核心:把数据库的每一次变更实时流出来,驱动下游一切。

很多团队的方案是 Kafka + Debezium,但你知道吗?PG 自带的逻辑复制就能做 CDC,而且 Debezium 也是基于它实现的。

如果你的下游消费者不多(< 5 个)、不需要长期存储事件,直接用 PG 的逻辑复制比 Kafka 简单 10 倍。

13.2 PG 逻辑复制原理

PG 的 WAL(Write-Ahead Log)记录了所有变更。逻辑复制就是:

事务写入 → WAL → 逻辑解码插件 → Replication Slot → 订阅者

关键概念:

关键提醒:Replication Slot 会让 WAL 不被回收。如果订阅者断了不再消费,WAL 会无限堆积撑爆磁盘。生产里必须监控:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

13.3 三种使用方式

方式 1:PG 原生订阅(PG → PG 复制)

PG 10+ 的内置功能,无需任何外部工具:

-- 在源库
CREATE PUBLICATION orders_pub FOR TABLE orders, customers;

-- 在目标库
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=source.db port=5432 dbname=mydb user=replicator'
    PUBLICATION orders_pub;

用途:

• 多区域只读副本
• 蓝绿部署 / 数据库迁移
• 跨业务库的数据同步

方式 2:Debezium → Kafka(经典 CDC)

如果下游消费者多,用 Debezium 把 PG 变更发到 Kafka:

# Debezium connector config
{
  "name": "orders-pg-source",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "pg.example.com",
    "database.dbname": "mydb",
    "schema.include.list": "public",
    "table.include.list": "public.orders,public.customers",
    "publication.name": "debezium_pub",
    "slot.name": "debezium_slot"
  }
}

每条变更变成一个 Kafka 事件,下游随便消费。

方式 3:wal2json / pgoutput + 自己消费(轻量)

不想上 Kafka?直接命令行接收:

# 安装 wal2json
CREATE_REPLICATION_SLOT my_slot LOGICAL wal2json;

# 持续吐 JSON
pg_recvlogical -d mydb --slot=my_slot --start -f - -o pretty-print=1

或者程序消费(Python 用 psycopg):

import psycopg
from psycopg import sql

with psycopg.connect("dbname=mydb replication=database", autocommit=True) as conn:
    cur = conn.cursor()
    cur.start_replication(slot_name='my_slot', decode=True, options={
        'pretty-print': '0'
    })
    for msg in cur:
        event = json.loads(msg.payload)
        # event = {"action":"U","schema":"public","table":"orders","columns":[...]}
        process(event)
        cur.send_feedback(flush_lsn=msg.data_start)

13.4 Outbox Pattern:PG CDC 最闪光的应用

经典分布式痛点:业务 commit 了,但 MQ 发送失败,导致下游不一致。

# 反例:经典踩坑
db.commit_order(order)              # 成功
mq.publish('order_created', order)  # 失败 → 下游永远不知道
# 现在系统状态:DB 有订单,MQ 没事件,业务损坏

Outbox Pattern 的解法:把"发消息"也变成数据库写,放在同一事务里。

表设计

CREATE TABLE outbox (
    id            BIGSERIAL    PRIMARY KEY,
    aggregate     TEXT         NOT NULL,            -- 例:'order'
    aggregate_id  TEXT         NOT NULL,            -- 例:'42'
    event_type    TEXT         NOT NULL,            -- 例:'order_created'
    payload       JSONB        NOT NULL,
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
    published_at  TIMESTAMPTZ,                      -- NULL 表示未发布
    retry_count   SMALLINT     NOT NULL DEFAULT 0,
    dedup_key     TEXT
);

CREATE INDEX idx_outbox_unpublished ON outbox (created_at)
WHERE published_at IS NULL;

业务写入(关键!业务 + 事件同事务)

BEGIN;
    INSERT INTO orders (id, ...) VALUES (42, ...);
    INSERT INTO outbox (aggregate, aggregate_id, event_type, payload, dedup_key)
    VALUES ('order', '42', 'order_created', '{...}', 'order:42:created');
COMMIT;

事务原子性保证:要么订单 + 事件都成功,要么都不成功。

Dispatcher Worker(发送事件)

复用 §1 的 PG 队列模式:

async def dispatch():
    while True:
        # 用 SKIP LOCKED 抢一批未发送的事件
        rows = await conn.fetch("""
            SELECT id, event_type, payload, retry_count
            FROM outbox
            WHERE published_at IS NULL
            ORDER BY created_at LIMIT 50
            FOR UPDATE SKIP LOCKED
        """)
        for r in rows:
            try:
                await send_to_kafka(r['event_type'], r['payload'])
                await conn.execute(
                    "UPDATE outbox SET published_at = now() WHERE id = $1", r['id'])
            except Exception:
                await conn.execute(
                    "UPDATE outbox SET retry_count = retry_count + 1 WHERE id = $1",
                    r['id'])

进阶:Outbox + Debezium = 极致优雅

最优雅方案:Debezium 直接监听 outbox 表的 INSERT,把 payload 发到 Kafka——你完全不需要写 dispatcher。

Debezium 有专门的 Outbox Event Router SMT,配置一下就能用。

业务 → INSERT outbox → Debezium 监听 WAL → Kafka

这是分布式系统里最优雅的事务消息方案,业界最佳实践,你只需要 PG + Debezium。

13.5 触发器 + NOTIFY:超轻量 CDC

如果你的 CDC 需求只是"业务表变了通知应用刷缓存",根本不用 Debezium:

CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('table_change',
        jsonb_build_object('table', TG_TABLE_NAME, 'op', TG_OP, 'id', NEW.id)::text);
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER products_change_notify
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION notify_change();

应用 LISTEN 一下,实时收到变更通知。轻量、零依赖。

13.6 真需要 Kafka 的场景

否则,PG 逻辑复制 / Outbox 一把梭。

13.7 一段心法

现代分布式系统的核心问题是 "怎么让多个组件最终一致"。
Outbox Pattern 把这个问题简化为 "业务和事件在同一个事务里"。
而 PG 是当代数据库里最适合实现 Outbox 的——
因为 SKIP LOCKED + WAL + Debezium = 一套优雅的全栈解决方案。

14. PG 的"边界"—— 什么时候真的不该用 PG

前面 13 节我都在为 PG 站台。但这一节我要诚实——没有银弹。

每个工具都有自己的边界,到了下面的边界,该上专业方案就上。但更重要的是:理解为什么这些场景 PG 不行,这样你才能判断自己有没有真正到达边界。

14.1 极致写入吞吐(> 10w/s 持续)

典型场景:

• 大规模物联网(每秒百万条传感器数据)
• 日志洪流(全公司应用日志聚合)
• 广告点击流

为什么 PG 不行:

1. MVCC 写放大:每次 UPDATE 实际是"插入新版本 + 标记旧版本死亡",行存膨胀
2. WAL 写入是单点:所有写入串行经过 WAL,即使你有 NVMe
3. autovacuum 扛不住:高写入下 dead tuple 堆积,清理跟不上

该用:

• Kafka:消息流,扇出消费
• ClickHouse:列存,写入百万级/秒不眨眼
• ScyllaDB / Cassandra:LSM-tree,写优化
• TimescaleDB(算 PG 一族):到几百万行/秒可扛,如果在乎 SQL 兼容,先试它

迁移策略:不要 all-in,双写——核心业务在 PG,洪流数据进 ClickHouse,通过 Outbox 关联。

14.2 OLAP 大宽表实时分析

典型场景:

• 几十亿行宽表(几百列)
• BI dashboard 要 sub-second 响应
• 复杂 GROUP BY + 多维聚合

为什么 PG 不行:

1. 行存对分析不友好:即使你只查 1 列,也要读完整行
2. 没有列裁剪、向量化执行、SIMD 加速(虽然 PG 17 在改进)
3. 没有 ZoneMap / MinMax 索引(Skip 大段不相关数据)

该用:

• ClickHouse:列存王者,聚合飞快,OLAP 性能 10-100x PG
• DuckDB:单机分析,SQLite 体验,Python 友好
• Snowflake / BigQuery:云数仓,无脑用
• StarRocks / Doris:开源 OLAP,中国主流

注意 PG 也在追赶:

• TimescaleDB + 列存压缩:亿级聚合 OK
• Citus(PG 扩展):分布式 OLAP
• pg_duckdb(2024 新):在 PG 里嵌入 DuckDB 引擎做分析

判断口诀:数据量 < 10 亿,先试 TimescaleDB 列存;> 100 亿无脑 ClickHouse。

14.3 超高并发简单 KV(微秒级,> 50k QPS)

典型场景:

• 广告竞价(每次请求查特征)
• 推荐引擎在线特征
• 千万级在线 session

为什么 PG 不行:

1. 每个连接都是一个进程,fork 开销大,几千连接就吃掉所有 CPU
2. TCP/network 往返延迟就要 0.3-0.5ms,PG 加上事务管理 ≈ 1ms
3. 没有 in-memory 数据结构特化(Hash 表 / Sorted Set)

该用:

• Redis / KeyDB / DragonflyDB:微秒级,百万 QPS
• Memcached:更轻量,只 KV
• Aerospike:超低延迟,大规模
• Hazelcast / Ignite:Java 生态分布式缓存

生产策略:核心数据 PG + 热数据 Redis + 应用层缓存三层架构,各取所长。

14.4 真正的图计算

典型场景:

• PageRank、社区发现、最短路径
• 多跳深度遍历(> 5 跳)
• 知识图谱推理

为什么 PG 不行:Recursive CTE 没有图遍历优化,深度增加时性能指数下降;Apache AGE 比 Neo4j 慢几倍。

该用:

• Neo4j:图数据库标杆,Cypher 生态
• TigerGraph / Nebula:大规模分布式图
• NetworkX(单机分析):Python 内存图

判断口诀:图深度 ≤ 3 + 节点 < 1 亿,PG 够用;否则专业方案。

14.5 全文搜索的极高规模

典型场景:

• 文档量 > 1 亿,日志检索 + Kibana
• 复杂打分、聚合 facets、多语言混搜
• 实时索引 + 实时搜索(秒级)

为什么 PG 不行:GIN 索引在 1 亿级膨胀严重,延迟到秒级;ts_rank 不如 BM25 灵活。

该用:

• Elasticsearch / OpenSearch:搜索引擎标杆
• Meilisearch / Typesense:小巧的搜索
• Solr:老牌
• Quickwit:云原生,新兴

14.6 多区域强一致写入

典型场景:

• 全球多活(美国、欧洲、亚太同时写)
• 跨区域强一致(金融跨境)

为什么 PG 不行:PG 主备模式,只有一个主库可写,跨区域延迟大。

该用:

• CockroachDB:Spanner 开源版,PG 协议兼容
• YugabyteDB:PG 协议兼容,分布式
• Spanner(GCP):TrueTime,强一致
• TiDB:MySQL 协议,但理念相似

注意:CockroachDB / YugabyteDB 都是 PG 兼容协议,还是 PG 一族!切换成本极低。

14.7 一个判断口诀(精炼版)

如果一个场景的需求是 "PG 我会少 X 个数量级",而 X ≥ 2,才考虑专业方案。
否则,继续 PG。

10x 的差距?先调优 PG(配置、索引、SQL),大概率能补回来。
100x 的差距?专业方案出场。

14.8 何时该迁移?(决策框架)

最大的工程错误是"未雨绸缪"地引入复杂度。等你真的撞墙再换,那时你已经知道精确需要什么。

15. 实战路线图:一个项目从 0 到 1 的 PG 一把梭打法

15.1 心法:先做减法

很多团队画第一张架构图时,就把未来 5 年可能需要的所有组件都摆上去了。结果是:复杂度提前透支。

正确的做法是先做减法:

"如果不写一行配置,这个项目至少要几个组件才能跑起来?"
"PostgreSQL"
"够了。"

15.2 第 0 天:开局

架构图就一个框:

                  [Client]
                     │
                     ▼
                  [App x N]
                     │
                     ▼
                [PostgreSQL]

不要画 Redis,不要画 Kafka,不要画 ES,不要画 MongoDB。默认全 PG。

写在 README 第一行:

"Architecture: Single PostgreSQL. Components added only when proven necessary."

15.3 第 1 周:基础设施一次到位

1. PG 集群:单主 + 1 同步备 + 1 异步备(三节点是最小生产配置)
2. WAL 归档:archive_mode = on,归档到 S3/OSS,这是 PITR 的根基
3. PgBouncer 连接池:transaction pool 模式,默认就装上,不要等性能问题再加
4. 备份:每日 pg_basebackup + WAL 归档,定期演练恢复(演练才是真备份)
5. 监控:Prometheus + postgres_exporter + Grafana 模板,十分钟搞定

配置调优:

shared_buffers       = 内存 * 25%
effective_cache_size = 内存 * 75%
work_mem             = 32MB
maintenance_work_mem = 1GB
random_page_cost     = 1.1            # SSD
max_wal_size         = 8GB
wal_compression      = on

15.4 第 1 个月:常用扩展一次装齐

-- 必装(基础能力)
CREATE EXTENSION pg_stat_statements;  -- 慢 SQL 统计,#1 重要
CREATE EXTENSION pg_trgm;             -- 模糊匹配 + LIKE 加速
CREATE EXTENSION pgcrypto;            -- 加密、UUID
CREATE EXTENSION pg_cron;             -- 定时任务
CREATE EXTENSION btree_gist;          -- 复合 GiST 索引(范围 + 等值)

-- 强烈推荐(几乎所有项目都用得上)
CREATE EXTENSION vector;              -- 向量(AI / 推荐 / 搜索)
CREATE EXTENSION timescaledb;         -- 时序(指标/日志/任何带时间戳)

-- 按需(看业务)
-- CREATE EXTENSION postgis;          -- 地理位置
-- CREATE EXTENSION pg_jieba;         -- 中文搜索
-- CREATE EXTENSION age;              -- 图查询
-- CREATE EXTENSION pgaudit;          -- 合规审计

一次装齐,永远不要"等需要时再装"。装了不用是免费的,要用时去现装,可能踩兼容性坑。

15.5 第 1-3 个月:业务起步

按需启用各 PG 能力,优先级遵循"先用最简单的":

15.6 第 3-6 个月:业务长大

• AI / RAG 需求 → pgvector + 混合检索(§5)
• 报表 dashboard → Materialized View / Continuous Aggregate
• 跨表事件 → Outbox + dispatcher(§13)
• 管理后台 → PostgREST / Hasura(§11)
• 多租户 → RLS(§11)
• 审计合规 → trigger + history(§12)

15.7 第 6-12 个月:扩展性能

到这阶段,仍然不要换数据库,而是把 PG 用到极致:

• 读多 / OLAP:加只读副本(逻辑复制或物理复制),读写分离
• 单表大 :按时间 / hash 分区(PARTITION BY)
• 写入吞吐瓶颈:Citus(PG 扩展)分布式 sharding
• 高可用:Patroni + etcd 或上云(Neon、Supabase、Aurora、RDS)
• HTAP:TimescaleDB 列存压缩 + 普通 OLTP

15.8 第 12 个月后:精确引入专业方案

到这时候你清楚地知道:

• 哪个场景 PG 真扛不住(可能就 1-2 个)
• 这些场景精确需要什么

这才是引入新组件的正确时机——基于真实瓶颈,而不是想象。

举例:

• 真的有"日志洪流",不可避免 → 上 ClickHouse,只用于日志分析,不动业务库
• 真的需要 GraphQL Subscription 推流给前端 → 上 Hasura,只做实时层,不替代后端
• 真的需要图算法 → 上 Neo4j,只做图分析,不存核心数据

始终保持 PG 是"中心",其他组件是"配角"。

15.9 反直觉的事实

90% 的中型公司,一辈子触碰不到 PG 的天花板。

公开案例:

• Instagram 早期:单 PG 实例撑到 1 亿用户
• Notion:核心 PG,百万付费用户
• GitLab:全公司 PG-only,百万企业用户
• Discord:从 PG 起步,千万 DAU 才换部分服务到 ScyllaDB
• Figma:实时协作 + PG,撑到独角兽
• Stack Overflow:SQL Server,亿级 PV(SQL Server 是堂兄,逻辑相同)

你的项目大概率永远不会到这些公司的规模。

15.10 一段心法

工程的最大美德不是"为未来准备",
而是"为当下解决",并保留演化的可能。
PG 是这两点的最佳载体——它当下足够强,演化路径足够清晰。

16. 必装扩展清单 & 配置心法

16.1 必装扩展全表

16.2 配置心法(10 条军规,生产级)

1. shared_buffers = 内存 * 25%-40%

默认 128MB 是 PG 性能问题的 #1 根源。32GB 内存机器,至少配 8GB。

2. effective_cache_size = 内存 * 50%-75%

告诉规划器 OS 大概有多少 cache,不实际占用内存,影响查询计划选择。

3. work_mem 别设太大

这是每个连接每个排序/哈希操作的内存。100 连接 × 5 个排序 × work_mem=256MB = 125GB,会 OOM。

经验值:work_mem = (总内存 - shared_buffers) / max_connections / 4,通常 32-64MB。

4. maintenance_work_mem = 1GB+

VACUUM、CREATE INDEX、REINDEX 用。建索引慢的 #1 原因就是这个值太小。

5. max_connections 不要随便调大

每个连接 = 一个进程,fork + 内存开销大。永远配 PgBouncer,把上层几百连接复用成 PG 后端 30-50 个。

6. autovacuum 永远开着,且要激进

队列表 / 高更新表单独配置:

ALTER TABLE jobs SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 2000
);

7. WAL 归档必开

archive_mode = on
archive_command = 'aws s3 cp %p s3://bucket/wal/%f'

没开 archive 就没法 PITR(时间点恢复),丢数据风险极高。

8. log_min_duration_statement = 1s

超过 1 秒的 SQL 自动进 PG log。生产监控必开。

9. 三种"高级索引"必须会

-- partial index:只索引一部分行
CREATE INDEX ON jobs (priority DESC) WHERE status='pending';

-- 表达式索引:索引计算结果
CREATE INDEX ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com';  -- 走索引

-- 覆盖索引(INCLUDE):避免回表
CREATE INDEX ON orders (user_id) INCLUDE (status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 42;  -- 不需要回表

这三个能解决 80% 性能问题,但 80% 的人不会用。

10. EXPLAIN (ANALYZE, BUFFERS) 是你最好的朋友

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ... ;

看实际执行计划、buffer 命中率、行数估算误差。慢 SQL 第一步永远是 EXPLAIN ANALYZE。

16.3 监控必看的 SQL(收藏)

-- 1. 慢 SQL Top 20(总耗时排序)
SELECT round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       calls,
       substring(query, 1, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- 2. 缓存命中率(全库,应 > 99%)
SELECT round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0), 2) AS hit_pct
FROM pg_statio_user_tables;

-- 3. 表膨胀(找 dead tuple 多的表)
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC LIMIT 20;

-- 4. 没用过的索引(可以删)
SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 5. 索引大小排行
SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;

-- 6. 表大小排行
SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

-- 7. 当前活跃 SQL(找堵塞)
SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS duration,
       substring(query, 1, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- 8. 锁等待链(找谁堵住了谁)
SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 9. 复制延迟(主从)
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS sent_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;

-- 10. 复制槽 lag(逻辑复制必看!)
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

把这 10 条做成 Grafana / Metabase dashboard,每天看一眼,出问题第一时间发现。

16.4 推荐工具链

收尾:一个工程哲学

文章很长(2 万字往上),但其实可以浓缩成一句话:

复杂度是可以累积的,简单度也是可以累积的。

你每多上一个组件,就给未来的自己埋一个雷;
你每少上一个组件,就给未来的自己留一份自由。

这十几年,数据库行业走了一个圈

2010 年代是 NoSQL 的黄金年代:Mongo、Cassandra、Redis、ES、Neo4j、InfluxDB……人人都说"关系数据库不行了"。

但十年过去,我们看到了另一面:

• Mongo 加事务、加 schema validation、改 SQL-like —— 像在变成 PG
• ES 加 SQL 接口 —— 像在变成 PG
• Cassandra 加 ACID Light、加二级索引 —— 像在变成 PG
• InfluxDB 3.0 改回 SQL —— 像在变成 PG
• Snowflake、Redshift、BigQuery —— 本来就是 PG 兼容族
• CockroachDB、YugabyteDB、Neon、Supabase —— PG 协议直接抄

所有专业方案都在朝 PG 靠拢,而 PG 本身也在吸收所有专业方案的能力(JSONB / 向量 / 时序 / 地理 / Cypher……)。

这是一场关于"简洁永胜"的胜利。

PG 的护城河,是 30 年的耐心

PG 这个东西,1996 年首次发布,到现在快 30 年了。

它无聊地稳定运行,无聊地一年发一个大版本,无聊地把别人吹的"颠覆性新方案"一个个吸纳成扩展。

它没有融资,没有 IPO,没有市场部。但它一直在那里。

这种 "Boring Technology"(无聊技术)才是工程的终极美学。Dan McKinley 那篇神文写过:

"Choose boring technology.
Innovate where it matters: in your product.
Use boring for the rest."

你的"创新预算"是有限的,花在核心业务上,基础设施越无聊越好。

PG 是你能找到的最无聊的、也最强大的那块基础设施。

给你一个 30 秒的工程仪式

下次画架构图前,强迫自己做这个仪式:

1. 画一个框,写上 "PostgreSQL"。
2. 盯着这个框看 30 秒。
3. 问自己:"这个框,真的不够用吗?"
4. 如果答案是"够用",那就什么都别加。

90% 的情况,这个仪式之后,你的架构图就停在第 1 步了。

而第 1 步的架构,永远是最稳的、最便宜的、最易维护的、最让你能下班的架构。

推荐阅读

流派宣言

• Just Use Postgres for Everything — 这个流派的"宣言",必读
• Postgres is Enough — GitHub 上的经典清单,被反复引用
• Choose Boring Technology — Dan McKinley 的经典演讲
• The Part of PostgreSQL We Hate the Most — CMU Pavlo 教授,客观看待 PG 缺点(MVCC 设计的历史包袱)

真实案例

• 37signals(DHH)关于删 Redis、删 ES 的几篇博客
• Notion Engineering Blog,关于 PG sharding
• GitLab 的 PG 优化文章

进阶必读

• pgvector 作者 Andrew Kane 的设计博客
• TimescaleDB 文档,特别是 hypertable 的设计哲学
• Use The Index, Luke! —— 索引调优圣经

项目源码(学最好的工程)

• pg-boss — 学 PG 队列
• Oban — 学 Elixir 风格的 PG 队列
• PostgREST — 学怎么把 PG 变 API
• Supabase — 学怎么做 PG 全栈

写这篇文章的初心

看到太多团队架构图越画越复杂,运维越加越多,业务却越走越慢。

写这篇文章,是想让一些人在画下一张架构图之前,多停 30 秒,问一下自己:

"PG 不行吗?"

大概率,是行的。

而且,它一直行。

如果你看到这里,说明你真的把这篇文章读完了。
我相信 PG 的这条"少即是多"的路,值得这两万字的篇幅。

接下来要做的,就是从你下一个项目开始,把这张图画简单。

祝你的系统稳如磐石,祝你的运维不再失眠。Just Use Postgres:用一个数据库吃掉你架构里 80% 的中间件

"What can't Postgres do?"
—— 这两年技术圈最流行的一句话

"Use one database, but use it really well."
—— Hacker News 上一条 2k+ 赞的评论

写在最前面:一张让人窒息的架构图

先看一张图,看看你眼熟不:

                       [客户端]
                          │
                          ▼
                     [API Gateway]
        ┌─────────────────┼─────────────────┐
        ▼                 ▼                 ▼
    [业务服务]         [认证服务]         [搜索服务]
        │                 │                 │
        ├──→ MySQL    ├──→ Redis        ├──→ Elasticsearch
        ├──→ Redis    └──→ JWT Service   └──→ MongoDB(文档)
        ├──→ MongoDB
        ├──→ RabbitMQ ──→ Worker ──→ Kafka ──→ ClickHouse
        ├──→ Pinecone(向量)
        ├──→ InfluxDB(指标)
        ├──→ Neo4j(关系)
        ├──→ S3(对象)
        └──→ Cron Server / Airflow

数一数,11 个数据/中间件组件。

这是我见过的某个 30 人公司,真实的"现代化微服务架构"。看着特别专业,对吧?

我问他们 CTO:"上线一年多,系统稳吗?"

他笑了笑说:"稳是稳,就是一周两次半夜被叫醒,有一次 Pinecone 出问题排查到天亮,发现是网络抖动;有一次 Kafka 偏移量错乱,数据重复消费了一万条……运维已经是第三个了。"

所有组件都"基本能用",但合在一起,就是一个吞噬团队精力的怪兽。

如果你正在做一个新项目,千万不要默认就把下面这堆组件摆上图:

Redis(缓存) + RabbitMQ(队列) + Elasticsearch(搜索) +
MongoDB(文档) + InfluxDB(时序) + Pinecone(向量) +
Neo4j(图) + S3(对象) + Cron Server(定时) + ......

停一下。

先问自己一个问题:这些是我业务真正需要的,还是别人都这么画我也跟着画?

我自己经历过一个反面例子:初期"标准"架构上了 8 个中间件,运维一个人加班加点,半年后业务还没起来,运维先离职了。痛定思痛,下一个项目我们决定"PG 一把梭",一个 PostgreSQL 实例顶下了缓存、队列、搜索、向量、调度、定时任务、审计、地理位置。两年过去,系统跑得稳得像石头,团队精力全花在业务上,这才是我想要的工程。

这不是我一个人的体感。这两年技术圈有个明显的趋势,有个专有名词叫 "Postgres for Everything"(也叫 "Just Use Postgres" 或 "PG-First")。Hacker News 上隔三差五就有爆款帖讨论这个话题:

• 37signals(Basecamp、HEY 的母公司)CTO DHH 公开宣称删掉了 Redis、Elasticsearch,只留 PG,全公司从此世界清净
• Rails 8 默认的队列 SolidQueue、缓存 SolidCache、Cable SolidCable —— 全是 PG-based,连 Redis 都不再是默认依赖
• Notion:核心存储 PG;Block 数据 JSONB
• GitLab:全公司内部明确"PG-only 战略",老的 Redis 队列方案在被替换
• Figma:撑住了千万级实时协作,核心是 PG
• Discord:从 MongoDB 搬迁到 PG(后来又上了 ScyllaDB,但很多服务仍是 PG)

为什么这个趋势越来越猛?因为大家终于想明白了一件事:

复杂度是工程系统的最大敌人,而不是性能。

绝大多数系统的瓶颈,从来都不是数据库选型,而是人——人维护不过来那么多组件,人 debug 不了那么多故障域,人对接不上那么多数据一致性场景。

每多一个中间件,就多了:

• 一份运维成本
• 一个监控大盘
• 一个故障域
• 一套备份方案
• 一组安全策略
• 一个版本升级日历
• 一份"双写一致性"的心智负担
• 一次半夜被叫醒的可能

减少组件 = 减少故障 = 减少加班。这是工程师的尊严。

而 PostgreSQL,恰好是这个时代唯一一个把"什么都能做"做到了"什么都做得不错甚至很好"的数据库。它不是数据库,它是一个数据平台——一个有 30 年血脉、最强扩展生态、被全球最严苛业务打磨过的"瑞士军刀"。

这篇文章,我会把 PG 能"吃掉"的中间件场景一个一个拆开讲。每一节都遵循同样的结构:

【它能做到什么程度】 → 【原理是什么】 → 【完整可用的 SQL 示例】 → 【真实场景的坑】 → 【性能极限】 → 【什么时候真的需要专业方案】

文章很长(1 万 7 千多字),但每一节都可以独立看,收藏后按需查阅就好。

走起。

目录

1. PG 当队列用 —— 替代 Redis / RabbitMQ / Kafka(轻量场景)
2. PG 当缓存用 —— 替代 Redis(大部分场景)
3. PG 当文档数据库用 —— 替代 MongoDB
4. PG 当全文搜索引擎用 —— 替代 Elasticsearch(中小规模)
5. PG 当向量数据库用 —— 替代 Pinecone / Milvus
6. PG 当时序数据库用 —— 替代 InfluxDB
7. PG 当图数据库用 —— 替代 Neo4j(轻度场景)
8. PG 当地理空间引擎用 —— 行业标准 PostGIS
9. PG 当定时任务调度器用 —— 替代 Cron / Airflow
10. PG 当分布式锁/信号量用 —— 替代 Redis 的 SETNX
11. PG 直接吐 API —— 替代后端 CRUD 层
12. PG 当审计日志用 —— 替代埋点系统
13. PG 做 CDC / 流处理 —— 替代 Kafka 的部分场景
14. PG 的"边界"—— 什么时候真的不该用 PG
15. 实战路线图:一个项目从 0 到 1 的 PG 一把梭打法
16. 必装扩展清单 & 配置心法

1. PG 当队列用 —— 替代 Redis / RabbitMQ / Kafka(轻量场景)

1.1 一段真实的"队列踩坑史"

很多团队的"队列演化路线"是这样的:

1. 一开始:写一张 tasks 表,worker SELECT * FROM tasks WHERE status='pending' LIMIT 1,拿到再 UPDATE。
2. 上量后:发现两个 worker 拿到同一条任务,重复消费。
3. 改方案:加 SELECT ... FOR UPDATE,结果 worker 全部串行阻塞,吞吐暴跌。
4. 崩溃:把队列搬到 Redis,从此每周 deploy 时 Redis 队列丢任务,business owner 找上门。
5. 加码:再上 RabbitMQ + 死信队列 + 持久化磁盘 + 镜像集群,多了一个全职运维。
6. 回归:某天看到一个叫 SKIP LOCKED 的特性,当场删掉 RabbitMQ,半年没出过事。

第 6 步,就是这一节要讲的故事。

1.2 它能做到什么程度

结论先行:中小规模(< 万 TPS)的任务队列,PG 比 Redis/RabbitMQ 更好用,且持久性、事务性、可审计性全面碾压。

PG 9.5(2016 年)引入了 SELECT ... FOR UPDATE SKIP LOCKED,这一个看起来不起眼的特性,直接让 PG 升格为生产级队列引擎。配合 LISTEN/NOTIFY 的原生 pub/sub,你能拿到一个:

• ✅ 持久化(WAL + fsync,断电不丢)
• ✅ 事务化(任务出队 + 业务写库可以原子)
• ✅ 可审计(完整 SQL 历史可查)
• ✅ 支持延迟任务、优先级、重试、死信、唯一性约束
• ✅ 零额外组件

业界生产案例(必看,这些项目都建议读源码学习):

反过来想:如果 PG 队列不靠谱,Rails 8 不会拿它当默认方案。这是已经被严苛工程验证过的路。

1.3 核心原理:三个 PG 特性合体

队列要做对,需要三个 PG 特性互相配合,缺一不可。

特性一:FOR UPDATE SKIP LOCKED —— 抢任务不打架

传统的"用表当队列"方案有两个致命问题:

问题 A:多个 worker SELECT 同一行 → 重复消费。
问题 B:加 FOR UPDATE → 后到的 worker 阻塞,等前一个 commit 才能看到下一行。

SKIP LOCKED 一次性解决:已经被别人锁住的行,我直接跳过,继续找下一行。

-- 这一段在每个 worker 里都跑,互不阻塞、互不重复
BEGIN;

SELECT id, payload, queue
FROM jobs
WHERE status = 'pending'
  AND scheduled_at <= now()       -- 支持延迟任务
ORDER BY priority DESC, scheduled_at
LIMIT 1
FOR UPDATE SKIP LOCKED;           -- 这一行是灵魂

-- 立刻在同一事务内改状态,锁就锁住状态修改了
UPDATE jobs
SET status     = 'running',
    started_at = now(),
    heartbeat_at = now(),
    worker_id  = $1,
    attempts   = attempts + 1
WHERE id = $2;

COMMIT;

内部机理(脑补一下 PG 是怎么做到的):

1. PG 走 partial index WHERE status='pending',扫描候选行
2. 对每一行尝试加行级 ROW EXCLUSIVE 锁
3. 普通 FOR UPDATE:锁不到就等(pg_locks 里能看到 Lock 等待事件)
4. SKIP LOCKED:锁不到就跳过这一行,继续找下一个候选
5. 找到 LIMIT 个就返回

这是 Oracle 几十年前就有的功能,PG 9.5 才补齐 —— 一旦补齐,直接打开了"用 PG 做队列"的整个时代。

注:SKIP LOCKED 不是 NOWAIT。NOWAIT 是"锁不到立刻报错",SKIP LOCKED 是"锁不到换下一个",二者意义完全不同,别用错。

特性二:Partial Index —— 索引只盯活的行

队列表的特点:99% 是已完成的行,1% 是 pending 的行。如果用普通索引,索引会越来越大,扫描越来越慢。

Partial Index 是这一节最被低估的优化:

-- 注意 WHERE,这是 partial 的关键
CREATE INDEX idx_jobs_ready
ON jobs (priority DESC, scheduled_at)
WHERE status = 'pending';

效果:

• 索引大小只跟 pending 任务数相关,即使 jobs 表有 1 亿历史行,这个索引可能就几 MB
• 抢任务的查询走这个索引,永远扫不到已完成的死行
• VACUUM 也只需要清理这个小索引,极快

没用 partial index 的 PG 队列,跑久了一定会慢,这是新手最容易栽的地方。

特性三:LISTEN/NOTIFY —— 让 worker 别傻轮询

worker 启动后不能 while True: SELECT ...; sleep(1),这样:

• 空闲时 N 个 worker 持续打 PG,浪费
• 任务来了最多有 1 秒延迟

PG 内置了 pub/sub 机制 LISTEN/NOTIFY,直接当队列唤醒信号用:

-- 生产者:API 写完任务,通知 worker
BEGIN;
INSERT INTO jobs (queue, payload) VALUES ('default', '{...}');
NOTIFY jobs_default;     -- channel 名跟 queue 名对应
COMMIT;
-- 注意:NOTIFY 实际是在 COMMIT 时才发出的!这一点很重要

-- 消费者:worker 启动时订阅
LISTEN jobs_default;
-- 然后阻塞等待,被唤醒就去 SKIP LOCKED 抢

几个 NOTIFY 必须知道的细节:

1. NOTIFY 在事务 COMMIT 时才发,所以"先 INSERT 再 NOTIFY"是安全的——worker 收到通知时一定能 SELECT 到新任务。
2. NOTIFY 是事务性 deduplicate 的:同一事务内 NOTIFY x; NOTIFY x; 只会发一次。
3. payload 最大 8KB(PG_NOTIFY_PAYLOAD_LENGTH),所以只用作"有新任务了"的信号,不要塞业务数据。
4. NOTIFY 的传递不保证:连接断开期间的 NOTIFY 会丢。所以 worker 必须在 LISTEN 之外,定期(比如 5 秒)做一次兜底轮询,防止漏唤醒。

# 正确的 worker 主循环骨架
async def worker_loop():
    async with pool.acquire() as conn:
        await conn.add_listener('jobs_default', on_notify)
        while True:
            jobs = await fetch_and_lock_jobs(conn, limit=1)
            if jobs:
                await process(jobs)
            else:
                # 空闲 → 等 NOTIFY,5 秒兜底
                await asyncio.wait_for(notify_event.wait(), timeout=5)
                notify_event.clear()

1.4 完整生产级表结构(可直接抄)

CREATE TABLE jobs (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    queue         TEXT        NOT NULL DEFAULT 'default',
    kind          TEXT        NOT NULL,                          -- 任务类型,例如 'send_email'
    payload       JSONB       NOT NULL,
    status        TEXT        NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending','running','success','failed','dead','cancelled')),

    -- 优先级(数字越大越先执行)
    priority      SMALLINT    NOT NULL DEFAULT 0,

    -- 重试控制
    attempts      SMALLINT    NOT NULL DEFAULT 0,
    max_attempts  SMALLINT    NOT NULL DEFAULT 5,
    last_error    TEXT,

    -- 调度时间(支持延迟任务/重试退避)
    scheduled_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- 结果
    result        JSONB,

    -- worker 心跳(防止 worker 崩溃任务卡死)
    worker_id     TEXT,
    locked_at     TIMESTAMPTZ,
    heartbeat_at  TIMESTAMPTZ,

    -- 任务幂等键(同 key 不允许重复入队)
    dedup_key     TEXT,

    -- 时间戳
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    started_at    TIMESTAMPTZ,
    finished_at   TIMESTAMPTZ
);

-- ============ 关键索引 ============

-- 1. 抢任务索引(partial,只索引 pending),这是 #1 最重要的索引
CREATE INDEX idx_jobs_ready
ON jobs (queue, priority DESC, scheduled_at)
WHERE status = 'pending';

-- 2. 巡检 running 任务的心跳超时(partial)
CREATE INDEX idx_jobs_running
ON jobs (heartbeat_at)
WHERE status = 'running';

-- 3. 幂等键唯一约束(partial,允许多条已完成的同 key)
CREATE UNIQUE INDEX uq_jobs_dedup
ON jobs (dedup_key)
WHERE status IN ('pending','running') AND dedup_key IS NOT NULL;

-- 4. 业务上常用的状态查询(给监控/调用方用,partial 节约空间)
CREATE INDEX idx_jobs_finished
ON jobs (finished_at DESC)
WHERE status IN ('success','failed','dead');

-- ============ autovacuum 单独配置(关键!) ============

ALTER TABLE jobs SET (
    autovacuum_vacuum_scale_factor = 0.05,    -- 默认 0.2,改成 5% 死元组就 vacuum
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 2000       -- 加快 vacuum
);

为什么这个表这么"复杂"?因为每一个字段都是一个真实事故的纪念碑。下面会逐个讲。

1.5 完整的入队 / 出队 / 重试 / 心跳 SQL

入队(支持幂等)

-- 朴素入队
INSERT INTO jobs (queue, kind, payload, priority)
VALUES ('default', 'send_email', '{"to":"a@b.com"}', 10)
RETURNING id;

-- 幂等入队(同 dedup_key 已经在队,就不重复入)
INSERT INTO jobs (queue, kind, payload, dedup_key)
VALUES ('default', 'reconcile_order', '{"order_id":42}', 'reconcile:42')
ON CONFLICT (dedup_key) WHERE status IN ('pending','running') DO NOTHING
RETURNING id;

-- 延迟任务(10 分钟后才执行)
INSERT INTO jobs (kind, payload, scheduled_at)
VALUES ('check_payment', '{...}', now() + interval '10 minutes');

出队(完整版,带 worker_id 和心跳)

WITH picked AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
      AND scheduled_at <= now()
      AND queue = $1
    ORDER BY priority DESC, scheduled_at
    LIMIT $2
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status      = 'running',
    started_at  = now(),
    locked_at   = now(),
    heartbeat_at = now(),
    worker_id   = $3,
    attempts    = attempts + 1
FROM picked
WHERE j.id = picked.id
RETURNING j.id, j.kind, j.payload, j.attempts;

注意几点亮点:

• 用 CTE + UPDATE FROM 一句完成"找+锁+改",省一个 round trip
• 支持批量出队(LIMIT $2),适合 worker 一次拉一批处理
• 立即更新 heartbeat_at,巡检逻辑可以靠它

心跳(worker 处理过程中定期跑)

UPDATE jobs SET heartbeat_at = now()
WHERE id = ANY($1::uuid[]) AND worker_id = $2;

注意 AND worker_id = $2:防止 worker 心跳到一个已经被巡检 reset 的任务上(它已经被别的 worker 抢走了)。

完成 / 失败 / 重试

-- 成功
UPDATE jobs
SET status = 'success', finished_at = now(), result = $2
WHERE id = $1;

-- 失败 + 重试退避(指数退避)
UPDATE jobs
SET status       = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'pending' END,
    last_error   = $2,
    scheduled_at = CASE WHEN attempts >= max_attempts
                        THEN scheduled_at
                        ELSE now() + (power(2, attempts) || ' seconds')::interval
                   END,
    worker_id    = NULL,
    locked_at    = NULL
WHERE id = $1;

指数退避(2s → 4s → 8s → 16s → 32s)是处理外部依赖故障的最佳实践,直接写在 UPDATE 里,无需应用层算。

心跳巡检(关键!防止 worker 崩溃任务卡死)

-- 每分钟跑一次:心跳超过 2 分钟没更新的 → reset 回 pending
UPDATE jobs
SET status      = 'pending',
    worker_id   = NULL,
    locked_at   = NULL,
    last_error  = 'worker timeout / heartbeat lost'
WHERE status = 'running'
  AND heartbeat_at < now() - interval '2 minutes';

踩坑提醒:不要用 started_at 来判断超时,因为长任务正常运行也可能跑很久。心跳才是"活着"的信号。

1.6 你必须躲开的 7 个坑(每个都是血泪史)

坑 1:Worker 崩溃 → 任务永远 RUNNING

场景:worker OOM 被 kill / pod 被驱逐 / 网络断 → status 卡在 running,调用方查任务永远等不到结果。

解法:心跳 + 巡检(上面的 SQL)。这是必做项,不是可选项。

坑 2:NOTIFY 在事务 ROLLBACK 时不发 → 安静地丢任务

反例:

async with conn.transaction():
    await conn.execute("INSERT INTO jobs ...")
    await conn.execute("NOTIFY jobs_default")
    raise SomeError()  # 事务回滚
# worker 永远收不到通知,但你以为发了

这是对的(回滚时就该不发),但很多人不知道,以为 NOTIFY 是立即发送的。记住:NOTIFY 只在 COMMIT 时实际推出去。

坑 3:任务表无限膨胀 → PG 越来越慢

症状:跑半年后,jobs 表 5000 万行,简单出队也变慢。原因是 autovacuum 跟不上,dead tuple 堆积。

解法三选一(组合更好):

-- 方案 A:已完成任务定期归档到冷表
INSERT INTO jobs_archive
SELECT * FROM jobs
WHERE status IN ('success','dead','cancelled')
  AND finished_at < now() - interval '7 days';

DELETE FROM jobs
WHERE status IN ('success','dead','cancelled')
  AND finished_at < now() - interval '7 days';

-- 方案 B:配合 pg_cron 自动跑(后面 §9 会讲)
SELECT cron.schedule('archive-jobs', '0 3 * * *', $$ ... 上面的 SQL ... $$);

-- 方案 C:大量已完成任务时直接表分区(按周分区)
CREATE TABLE jobs (...) PARTITION BY RANGE (created_at);
CREATE TABLE jobs_2026w17 PARTITION OF jobs FOR VALUES FROM ('2026-04-21') TO ('2026-04-28');
-- 老分区直接 DROP,毫秒级

坑 4:max_connections 不够用

场景:200 个 worker,每个一个连接,API 进程又要 100 个连接,直接打爆 PG 的 max_connections=100 默认值。

解法:永远要用 PgBouncer(transaction pool 模式),把上层的几百个连接复用成 PG 后端的 20-50 个。

# pgbouncer.ini
[databases]
mydb = host=pg.local port=5432 pool_mode=transaction pool_size=30

[pgbouncer]
max_client_conn = 1000

注意:transaction pool 模式下,LISTEN/NOTIFY 不可用(因为 LISTEN 是 session 级的)。所以 worker 的 LISTEN 连接要直连 PG,不经过 PgBouncer;只有"入队/出队"的短事务走 PgBouncer。

坑 5:LIMIT 1 抢任务,吞吐打不上去

反例:LIMIT 1 每次抢一条,一次 round trip 处理一条,网络开销大。

优化:LIMIT N 批量抢(N=10/50),内存里串行或并发处理。

... LIMIT 50 FOR UPDATE SKIP LOCKED;

吞吐能直接翻 5-10 倍。

坑 6:任务超时无差别 reset → 死循环

场景:某个任务因 bug 必然崩,worker 拿到就 OOM,巡检 reset,下个 worker 又 OOM,循环烧机器。

解法:attempts 字段 + max_attempts 标 dead:

UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'pending' END,
    ...

dead 任务不会被重新调度,等人介入。监控大盘上接 count(*) FILTER (WHERE status='dead'),有死信立刻报警。

坑 7:用 LIKE 而不是索引扫描

反例:

SELECT * FROM jobs WHERE kind LIKE 'send_%' AND status='pending';
-- 这会全表扫 pending,不会走 partial index

解法:把 kind 也放进 partial index;或者按 kind 拆 queue:

CREATE INDEX idx_jobs_kind_ready ON jobs (kind, priority DESC, scheduled_at)
WHERE status = 'pending';

1.7 性能基准:PG 队列到底能跑多快

社区的 benchmark 数据(2023-2024 年公开测试):

调优手段:

1. synchronous_commit = off(队列容忍极小概率丢任务时,吞吐 +50%)
2. 批量入队(单 INSERT 多行,而非一行一次)
3. 批量出队(LIMIT 50)
4. partial index 是免费的速度
5. PgBouncer transaction pool

结论:除非你是 Twitter 量级,否则 PG 队列吞吐永远不是瓶颈,真正的瓶颈是任务本身的处理逻辑(比如调外部 API 慢)。

1.8 跟 Redis / RabbitMQ / Kafka 横评

PG 队列输的只有"极限吞吐"这一项。而这一项,90% 的业务一辈子用不到。

1.9 Outbox Pattern:PG 队列最闪光的应用

经典分布式痛点:业务 commit 了,但 MQ 发送失败,数据不一致。

# 反例:经典踩坑
db.commit_order(order)         # 成功
mq.publish('order_created', order)  # 失败 → 下游永远不知道

PG 一把梭做法:

BEGIN;
  INSERT INTO orders (...) VALUES (...);
  INSERT INTO outbox (event_type, payload, dedup_key)
  VALUES ('order_created', '{...}', 'order:42:created');
COMMIT;

业务和事件在一个事务里,绝对一致。然后起一个 outbox dispatcher worker(就是上面的 PG 队列模型),把事件发出去:

# dispatcher 主循环(简化)
async def dispatch():
    rows = await fetch_and_lock(conn, 'outbox', limit=50)
    for r in rows:
        try:
            await send_to_downstream(r.event_type, r.payload)
            await mark_success(conn, r.id)
        except Exception as e:
            await mark_failed(conn, r.id, str(e))

这是分布式系统里最优雅的事务消息方案,而你只需要 PG。

1.10 什么时候真的需要 Kafka

不要骗自己,以下场景 PG 真不行:

• ✅ 日志洪流,> 10w 条/秒持续写入
• ✅ 多消费者扇出(同一事件被 5 个独立服务消费)+ 持久化几个月
• ✅ 跨数据中心复制
• ✅ 真正的流处理(KSQL / Flink / Spark Streaming 衔接)
• ✅ 事件溯源 (Event Sourcing) + 长期回放

简单的"任务队列",PG 一万倍够用。

1.11 推荐学习路径

如果你要落地 PG 队列,按这个顺序看源码 / 读文档:

1. 先精读 pg-boss 的 SQL 设计 —— 最全面
2. 再看 Oban 的文档 —— 工程经验最丰富
3. 最后读 River 的设计文档 —— 最现代

把这三个抄一遍,你的 PG 队列就是工业级的。

2. PG 当缓存用 —— 替代 Redis(大部分场景)

2.1 反直觉的真相

"缓存必须用 Redis"是 21 世纪初到现在最被神化的一个迷思。

不信你回想一下,你团队加 Redis 的时候,是不是这样的对话:

A:这个查询慢,加个缓存吧。
B:那上 Redis?
A:对,先加 Redis。
(然后跑去搞了 3 周的双写、缓存失效、一致性 bug)

90% 的情况,根本不需要 Redis。一个被你忽略的事实是:PG 自己就是一个性能极强的内存数据库。

PG 启动的时候,会把 shared_buffers 那么多内存提前申请好,所有热数据都驻留在内存里。一个主键查询,大概率走的路径是:

Client → 网络 → PG postmaster → backend → buffer pool 命中 → 返回
                                       ↑
                                  纯内存,0 次 IO

延迟和 Redis 基本是一个数量级。

实测对比(8C/32GB,单条主键 lookup,本机):

没有数量级差距。而代价是:你省下了一个 Redis 集群、一套监控、一份运维文档、N 个双写一致性 bug、和一个永远要操心的 OOM 风险。

2.2 你必须懂的 PG buffer pool 工作原理

很多人把 shared_buffers 配成 128MB(默认值)然后抱怨 PG 慢,这是 PG 性能问题的 #1 根源。

PG 的内存结构如下:

┌─────────────────────────────────────────────┐
│              shared_buffers                  │  ← 所有进程共享,缓存表/索引页
│              (推荐:内存 25%-40%)            │
├─────────────────────────────────────────────┤
│              wal_buffers                     │  ← WAL 缓冲
├─────────────────────────────────────────────┤
│        每个 backend 的 work_mem              │  ← 排序、哈希用,临时
│        每个 backend 的 temp_buffers          │  ← 临时表
├─────────────────────────────────────────────┤
│                                              │
│            OS Page Cache                     │  ← OS 帮你缓存的文件页
│        (effective_cache_size 告诉规划器       │
│         大概有这么多,实际由 OS 管)          │
│                                              │
└─────────────────────────────────────────────┘

关键认知:

• 一个数据页可能同时存在 shared_buffers 和 OS cache(一份数据在内存被缓存了 2 次,看起来浪费,但 PG 设计就是这样)
• 第一次 SELECT 走磁盘,把页加载进 buffer pool
• 后续访问命中 buffer pool,完全不走磁盘,这就是"PG 当缓存用"的本质

监控你的缓存命中率(必看)

-- 全库缓存命中率(应该 > 99%)
SELECT
    sum(heap_blks_read) AS disk_reads,
    sum(heap_blks_hit)  AS cache_hits,
    round(sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit + heap_blks_read), 0), 4) AS hit_ratio
FROM pg_statio_user_tables;

-- 单表命中率
SELECT relname, heap_blks_read, heap_blks_hit,
       round(heap_blks_hit::numeric / nullif(heap_blks_hit + heap_blks_read, 0), 4) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC LIMIT 20;

hit_ratio < 0.99 是危险信号,要么 shared_buffers 太小,要么有大表全表扫,要么有人写了反索引 SQL。这一行 SQL 是 DBA 每天必看的指标。

2.3 五种缓存用法,从浅到深

用法一:啥也不做,信任 buffer pool

最朴素也最强大。先把配置调对:

# postgresql.conf,32GB 内存的机器
shared_buffers       = 8GB         # 25%
effective_cache_size = 24GB        # 75%(规划器用,不实际占用)
work_mem             = 32MB        # 注意:这是每连接每排序的!N 连接 × M 排序 × work_mem 总和
maintenance_work_mem = 2GB         # VACUUM、CREATE INDEX 用
random_page_cost     = 1.1         # SSD 时代,默认 4.0 太保守了

调好之后,热数据全在内存,SELECT 主键 ≈ 内存读。

用法二:UNLOGGED TABLE —— 不写 WAL 的高速表

WAL(Write-Ahead Log)是 PG 持久化的核心,但写 WAL 本身有开销。如果数据丢了无所谓(典型缓存语义),用 UNLOGGED:

CREATE UNLOGGED TABLE cache_kv (
    key        TEXT        PRIMARY KEY,
    value      JSONB       NOT NULL,
    expires_at TIMESTAMPTZ,
    hit_count  BIGINT      DEFAULT 0
);

UNLOGGED 表的特点(必须知道):

重点:UNLOGGED 表崩溃后表会被清空(不是丢一部分,是整个 truncate),所以只能存"丢了重新算就行"的数据。

典型用法:

• 应用层缓存(查询结果、API 响应、session token)
• 实时计数器(每分钟对账,丢了再算)
• 临时计算结果

用法三:Materialized View —— 预计算缓存(最强大)

聚合查询慢?把结果"物化"成一张表:

CREATE MATERIALIZED VIEW user_stats AS
SELECT
    user_id,
    count(*)            AS order_count,
    sum(amount)         AS total_spent,
    max(created_at)     AS last_order_at
FROM orders
GROUP BY user_id;

CREATE UNIQUE INDEX ON user_stats (user_id);

-- 刷新(默认会锁表)
REFRESH MATERIALIZED VIEW user_stats;

-- 推荐:并发刷新(需要唯一索引,不阻塞读)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

物化视图 vs Redis 缓存:

这是 BI / 报表场景的神器,Redis 根本做不到。

用法四:增量物化视图(实时刷新)

PG 原生 MV 是全量刷新,数据量大时慢。如果需要实时增量,有两个方案:

方案 A:pg_ivm 扩展(增量物化视图)

CREATE EXTENSION pg_ivm;

-- IMMV = Incrementally Maintained Materialized View
SELECT pgivm.create_immv('user_stats', $$
    SELECT user_id, count(*) AS order_count
    FROM orders GROUP BY user_id
$$);

-- 之后 INSERT/UPDATE/DELETE orders 时,user_stats 自动增量更新

方案 B:trigger 手动维护

CREATE TABLE user_stats_cache (
    user_id     BIGINT PRIMARY KEY,
    order_count INT NOT NULL DEFAULT 0,
    total_spent NUMERIC NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION update_user_stats() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_stats_cache (user_id, order_count, total_spent)
    VALUES (NEW.user_id, 1, NEW.amount)
    ON CONFLICT (user_id) DO UPDATE
    SET order_count = user_stats_cache.order_count + 1,
        total_spent = user_stats_cache.total_spent + EXCLUDED.total_spent;
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER orders_after_insert
AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION update_user_stats();

这种"trigger 维护的派生表"比应用层 + Redis 双写一致性强 100 倍——它就是事务的一部分,要么都成功,要么都回滚。

用法五:pg_prewarm —— 启动后立刻预热缓存

PG 重启后 buffer pool 是空的,前几分钟所有查询都走磁盘,性能悬崖。pg_prewarm 扩展可以主动把热数据加载进 buffer pool:

CREATE EXTENSION pg_prewarm;

-- 把整张表预加载
SELECT pg_prewarm('orders');

-- 把某个索引预加载
SELECT pg_prewarm('idx_orders_user_id');

-- 自动预热:重启时把上次缓存的页重新加载(autoprewarm 后台进程)
shared_preload_libraries = 'pg_prewarm'
pg_prewarm.autoprewarm = on

这是缓存型场景必装的扩展,关键时刻救命。

2.4 TTL 怎么实现(三种方案,深度对比)

PG 没有原生 TTL,但实现 TTL 有几种成熟方案。

方案 A:查询时过滤 + 定时清理

-- 写入时记录过期时间
INSERT INTO cache_kv (key, value, expires_at)
VALUES ($1, $2, now() + interval '1 hour')
ON CONFLICT (key) DO UPDATE
SET value = EXCLUDED.value, expires_at = EXCLUDED.expires_at;

-- 查询时过滤过期
SELECT value FROM cache_kv WHERE key = $1 AND expires_at > now();

-- pg_cron 定时清理(每 5 分钟一次)
SELECT cron.schedule('cleanup-cache', '*/5 * * * *',
  $$DELETE FROM cache_kv WHERE expires_at < now()$$);

优点:简单
缺点:DELETE 会产生 dead tuple,VACUUM 压力,大量 TTL 时不友好

方案 B:partition by time + DROP 分区(推荐大数据量)

CREATE TABLE cache_kv (
    key        TEXT,
    value      JSONB,
    expires_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (key, expires_at)
) PARTITION BY RANGE (expires_at);

CREATE TABLE cache_kv_2026w17 PARTITION OF cache_kv
    FOR VALUES FROM ('2026-04-21') TO ('2026-04-28');
CREATE TABLE cache_kv_2026w18 PARTITION OF cache_kv
    FOR VALUES FROM ('2026-04-28') TO ('2026-05-05');

-- 过期一整周?直接 DROP,毫秒级,无 dead tuple
DROP TABLE cache_kv_2026w17;

优点:删除接近 0 成本,无 vacuum 压力
缺点:粒度只到分区(比如一周),不能精确到秒级 TTL

方案 C:pg_partman + retention policy(推荐生产)

pg_partman 扩展自动管理分区:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.cache_kv',
    p_control      := 'expires_at',
    p_type         := 'native',
    p_interval     := 'weekly'
);

-- 自动 retain 4 周,过期分区自动 drop
UPDATE partman.part_config
SET retention = '4 weeks', retention_keep_table = false
WHERE parent_table = 'public.cache_kv';

生产环境的 TTL 缓存,这是终极方案。

2.5 缓存模式对照(把 Redis 模式翻译成 PG)

几乎所有 Redis 用法,PG 都有对应。

2.6 真实案例:37signals 删 Redis 之后

37signals(DHH 那个公司)2024 年公开博客:删掉 Redis 之后

"We replaced Redis with PostgreSQL for caching, sessions, and rate limiting. The cache hit rate stayed above 99%. We removed an entire tier of infrastructure. Production incidents dropped noticeably."

他们做的事:

• session:存 PG,带 TTL,用 partial index
• 缓存:SolidCache(Rails 8 内置),底层就是 PG 表
• 限流:计数器 + window,纯 SQL 实现

几个月后,运维心跳曲线第一次稳定下来。

2.7 什么时候真的需要 Redis

为了客观,以下场景 PG 真的输:

判断标准:如果你的"缓存"需求 < 10k QPS,PG 一定够。先调好 PG 再说要不要 Redis。

2.8 一段心法

"Cache invalidation" is one of the two hardest problems in computer science.
用 PG 当缓存,那个问题直接消失了 —— 因为业务和缓存在同一个事务里。

3. PG 当文档数据库用 —— 替代 MongoDB

3.1 残酷的真相

JSONB 出来之后,MongoDB 在技术上就没什么不可替代性了。

EnterpriseDB 在 2014 年(JSONB 刚出)做过一次 benchmark,PG 在 NoSQL 场景已经追上 Mongo;到了 2018 年,PG 的 JSONB 写入吞吐反超 MongoDB 数倍,内存占用更低(因为 JSONB 是二进制格式,而 Mongo 早期 BSON 在某些场景反而更胖)。

10 年过去,JSONB 几乎吸收了 Mongo 的全部 query 语义:@>、?、#>、JSONPath、生成列、表达式索引……该有的都有。

但最大的差距不是性能,而是这一条:

PG = 关系型 + 文档型;Mongo = 只是文档型。

你用 PG 时同时拥有了:ACID 事务、外键、SQL 关联、窗口函数、CTE、视图、Trigger、统计扩展、向量、时序…… 这些 Mongo 要么没有,要么是后加且性能差(Mongo 的多文档事务到了 4.0 才支持,有明显性能损失)。

Discord 的故事:早期用 MongoDB 存消息,2017 年迁回 Cassandra(后又上 ScyllaDB)。原因之一是 Mongo 在大量更新场景的写放大严重,而 JSONB 的 partial update 在 PG 里是一个简单事务。

3.2 你必须懂的 JSONB 内部存储

json vs jsonb 别用错:

永远用 jsonb,除非你有极端需求要保留键序和空格(比如签名验证)。

TOAST:大 JSONB 怎么存的

PG 一行(tuple)有 8KB 上限。如果你存一个 100KB 的 JSONB,PG 会自动:

1. 压缩(LZ4 或 PGLZ)
2. 拆分到 TOAST(The Oversized-Attribute Storage Technique)表里,每片 2KB
3. 主表只存一个指针

读取时 PG 透明地拼回来。这是 PG 处理大文档的秘密武器,也是为什么你存大 JSONB 不会爆。

但有个性能影响:只 SELECT 主表其它列时,TOAST 不会被读;只要 SELECT 那个 JSONB 列,TOAST 拼接成本就来了。所以——

优化 #1:如果文档大,且大部分查询不需要全文档,把"常查的属性"提到普通列(用生成列自动同步)。

生成列(Generated Column):JSONB 的最佳搭档

CREATE TABLE products (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data  JSONB NOT NULL,

    -- 从 JSONB 抽出来的列,自动维护
    name  TEXT  GENERATED ALWAYS AS (data->>'name') STORED,
    price INT   GENERATED ALWAYS AS ((data->>'price')::int) STORED,
    in_stock BOOLEAN GENERATED ALWAYS AS ((data->>'stock')::int > 0) STORED
);

-- 这些"虚拟列"可以建普通 B-tree 索引
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_products_in_stock ON products (in_stock) WHERE in_stock;

这是 Mongo 用户根本想不到的 PG 神技:JSONB 灵活 + 关系列高效,鱼和熊掌兼得。

3.3 JSONB 完整用法手册

基础查询(背下来)

INSERT INTO products (data) VALUES
('{"name":"iPhone","price":999,"tags":["phone","apple"],"specs":{"ram":8,"color":"black"}}');

-- ↓↓↓ 操作符速查 ↓↓↓

-- 1. 取字段(返回 jsonb)
SELECT data -> 'name' FROM products;        -- "iPhone"  (注意带引号)

-- 2. 取字段为文本(返回 text)
SELECT data ->> 'name' FROM products;       -- iPhone   (无引号)

-- 3. 路径访问 jsonb
SELECT data #> '{specs,ram}' FROM products; -- 8

-- 4. 路径访问 text
SELECT data #>> '{specs,ram}' FROM products; -- '8'

-- 5. 数组访问
SELECT data -> 'tags' -> 0 FROM products;   -- "phone"

-- 6. 包含查询(@>)
SELECT * FROM products WHERE data @> '{"tags":["apple"]}';

-- 7. 反向包含(<@)
SELECT * FROM products WHERE '{"name":"iPhone"}' <@ data;

-- 8. 键存在
SELECT * FROM products WHERE data ? 'specs';

-- 9. 任一键存在
SELECT * FROM products WHERE data ?| array['specs','features'];

-- 10. 全部键存在
SELECT * FROM products WHERE data ?& array['name','price'];

JSONPath(PG 12+,堪比 MongoDB DSL)

JSONPath 是处理嵌套/数组结构的杀手级特性:

-- 找所有 ram > 8 的产品
SELECT * FROM products WHERE data @? '$.specs.ram ? (@ > 8)';

-- 找标签数组里有 'apple' 的
SELECT * FROM products WHERE data @? '$.tags[*] ? (@ == "apple")';

-- 提取多个值
SELECT jsonb_path_query(data, '$.specs.*') FROM products;

-- 复杂条件
SELECT * FROM products
WHERE data @? '$ ? (@.price > 500 && @.specs.ram >= 8)';

JSONPath 表达力比 Mongo 的 find() 嵌套对象还强。

索引(灵魂!没建索引等于没用 JSONB)

-- 1. GIN 索引,默认支持 @>、?、?|、?&
CREATE INDEX idx_products_data ON products USING GIN (data);

-- 2. GIN jsonb_path_ops,只支持 @>,但更小更快(推荐!)
CREATE INDEX idx_products_data_path ON products USING GIN (data jsonb_path_ops);

-- 3. 表达式索引,针对特定路径
CREATE INDEX idx_products_tags ON products USING GIN ((data -> 'tags'));

-- 4. B-tree 索引,针对单值字段(等值/范围)
CREATE INDEX idx_products_price ON products (((data->>'price')::int));

-- 5. partial + 表达式,极致优化
CREATE INDEX idx_products_active_price
ON products (((data->>'price')::int))
WHERE data @> '{"status":"active"}';

索引选择指南:

陷阱:jsonb_path_ops 索引比默认小 30%-50%,但只支持 @>。99% 场景用它就够,记住默认就用 path_ops。

更新 JSONB(MongoDB 用户最爱的部分)

-- 1. 设置/添加字段
UPDATE products SET data = jsonb_set(data, '{price}', '888') WHERE id=$1;

-- 2. 设置嵌套字段
UPDATE products SET data = jsonb_set(data, '{specs,color}', '"red"') WHERE id=$1;

-- 3. 删除字段
UPDATE products SET data = data - 'old_field';
UPDATE products SET data = data #- '{specs,color}';   -- 删嵌套

-- 4. 深度合并(注意:|| 是浅合并!)
UPDATE products SET data = data || '{"specs":{"warranty":2}}'::jsonb;
-- 结果:specs 整个被覆盖!不是深合并

-- 5. 真正的深合并(PG 16+ 用 jsonb_merge,或自己写函数)
-- PG 14+ 推荐写法:
UPDATE products
SET data = jsonb_set(data, '{specs}', (data->'specs') || '{"warranty":2}'::jsonb)
WHERE id=$1;

-- 6. 数组操作:追加
UPDATE products SET data = jsonb_set(data, '{tags}', (data->'tags') || '"new_tag"');

-- 7. 数组操作:删除元素
UPDATE products SET data = jsonb_set(data, '{tags}',
    (data->'tags') - 'old_tag');

复杂查询:平展数组、聚合、关联

-- 平展数组(jsonb_array_elements)
SELECT id, tag
FROM products, jsonb_array_elements_text(data->'tags') AS tag
WHERE tag LIKE 'phone%';

-- JSONB 聚合
SELECT jsonb_agg(data) FROM products WHERE (data->>'price')::int < 1000;

-- 把 JSONB 转表(jsonb_to_record)
SELECT * FROM jsonb_to_record(
    '{"name":"iPhone","price":999}'::jsonb
) AS x(name TEXT, price INT);

-- JOIN 关系表 + JSONB
SELECT u.email, o.data->>'product_name' AS product
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.data @> '{"shipped":true}';

这种"JSONB + JOIN"是 Mongo 永远做不到的(Mongo 的 $lookup 性能堪忧且语义受限)。

3.4 Schema 演进策略(JSONB 项目最大的隐藏话题)

JSONB 的"灵活"是双刃剑——一不小心就变成"什么都往里塞"的垃圾桶。生产里几个最佳实践:

1. 核心列 + JSONB 元数据

CREATE TABLE events (
    id          UUID PRIMARY KEY,
    user_id     BIGINT NOT NULL,         -- 核心,必查
    event_type  TEXT   NOT NULL,         -- 核心,必查
    occurred_at TIMESTAMPTZ NOT NULL,    -- 核心,必查
    properties  JSONB  NOT NULL          -- 灵活,业务定义
);

核心字段做普通列(可外键、可强约束),弹性属性塞 JSONB。

2. JSONB 也可以加 schema 校验(pg_jsonschema)

CREATE EXTENSION pg_jsonschema;

ALTER TABLE products ADD CONSTRAINT data_valid CHECK (
    jsonb_matches_schema('{
        "type": "object",
        "required": ["name","price"],
        "properties": {
            "name":  {"type":"string"},
            "price": {"type":"number","minimum":0}
        }
    }', data)
);

这就把 JSONB 变成了"半结构化但有约束"的字段,Mongo 的 schema validation 也是这套。

3. 渐进式迁移 JSONB → 普通列

随着业务清晰,把高频字段从 JSONB 迁出来:

-- 加普通列,先用生成列双写
ALTER TABLE products ADD COLUMN price INT
    GENERATED ALWAYS AS ((data->>'price')::int) STORED;
CREATE INDEX ON products (price);

-- 应用代码改读 普通 price 列
-- 一段时间后,从 JSONB 里删掉(可选)

JSONB 的最佳实践:从灵活开始,逐步演化成结构化。这是 schema-less 数据库做不到的——它们只能一直 schema-less。

3.5 性能基准:JSONB vs MongoDB

社区 benchmark(2023 年,1000 万文档):

结论:在大多数维度,JSONB 等于或优于 MongoDB。

3.6 真还需要 MongoDB 的场景

诚实地说,以下场景 Mongo 仍占优:

• ✅ 数据严格 schema-less,且永远不需要关联查询(罕见)
• ✅ 超大规模 sharding(几百节点起步,Atlas 自动 sharding 顺手)
• ✅ MongoDB Atlas 的全托管体验(Atlas Search、Charts 等生态)
• ✅ 团队历史包袱(已有 Mongo 代码,迁移成本高)

新项目 99% 应该选 PG + JSONB。

3.7 一段心法

JSONB 不是为了让你"用 PG 当 Mongo",
而是让你写 SQL 时也能享受 schema-less 的灵活,写 NoSQL 风格代码时也能享受 ACID 的安全。

鱼和熊掌兼得,这才是 PG 的魔法。

4. PG 当全文搜索引擎用 —— 替代 Elasticsearch(中小规模)

4.1 一个被低估的"重型武器"

很多团队的"搜索演化路线":

1. 一开始用 LIKE '%keyword%' —— 跑得动,但全表扫,数据量一上来就慢成狗。
2. 加索引?LIKE '%xx%' 不走 B-tree 索引(前缀通配)。
3. 上 Elasticsearch —— 增加一个 JVM 集群、ZooKeeper(老版本)、Logstash、Kibana……运维成本指数上升。
4. 一年后发现:ES 集群比业务库还耗资源,但日均搜索 QPS 只有几百。

90% 这种场景,PG 自带的全文搜索就是答案。它从 PG 8.3(2008 年)就有,十几年迭代下来,功能不亚于一个轻量版 ES。

业界最响亮的案例:

• 37signals:删 ES,搜索全部用 PG,延迟反而降低
• Notion:核心搜索就是 PG + tsvector
• Sentry:日志搜索曾用 ES,后来部分迁回 PG
• GitLab:代码搜索之外的搜索全在 PG

4.2 你必须懂的 tsvector 内部机制

PG 全文搜索不是简单的 LIKE,它内部做了一整套 IR(信息检索)流水线:

原文本 "PostgreSQL is a great database"
   ↓ 1. 分词(tokenization)
['PostgreSQL', 'is', 'a', 'great', 'database']
   ↓ 2. 标准化(lowercase)
['postgresql', 'is', 'a', 'great', 'database']
   ↓ 3. 字典处理(stop words / 词干)
   - 'is', 'a' 是停用词,丢掉
   - 'database' → 'databas' (Snowball 词干)
['postgresql', 'great', 'databas']
   ↓ 4. 加位置 + 权重
'postgresql':1 'great':4 'databas':5
   ↓ 5. 存为 tsvector

最终的 tsvector 是这样的:

SELECT to_tsvector('english', 'PostgreSQL is a great database');
--      'databas':5 'great':4 'postgresql':1

为什么 'database' 变成 'databas'? 因为 Snowball 词干提取算法,把 database / databases / databased 都规约为同一个词根,这样搜 'databases' 也能命中。

字典(dictionary)是核心

PG 的 text search config 包含一条字典链,文本被依次喂给:

parser → simple → english_stem → english_hunspell → english_synonym ...

每个字典处理特定职责:

• simple:仅小写化
• english_stem:Snowball 词干
• synonym:同义词替换
• thesaurus:多词组短语
• unaccent:去重音(法语 café → cafe)

你可以自定义字典链,这是 ES 的 analyzer chain 在 PG 里的等价物。

-- 看默认 english 配置
\dF+ english

-- 自定义 config
CREATE TEXT SEARCH CONFIGURATION my_config (COPY = english);
ALTER TEXT SEARCH CONFIGURATION my_config
    ALTER MAPPING FOR word, asciiword WITH unaccent, english_stem;

4.3 完整的全文搜索 schema(可直接抄)

CREATE TABLE articles (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    body        TEXT NOT NULL,
    author      TEXT,
    tags        TEXT[],
    published_at TIMESTAMPTZ,

    -- 关键:tsvector 用生成列,自动维护
    -- 关键:setweight 给不同字段不同权重(A最高 → D最低)
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')),  'A') ||
        setweight(to_tsvector('english', coalesce(author, '')), 'B') ||
        setweight(to_tsvector('english', array_to_string(coalesce(tags, ARRAY[]::text[]), ' ')), 'C') ||
        setweight(to_tsvector('english', coalesce(body, '')),   'D')
    ) STORED
);

-- 关键索引
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- 时间维度 partial,提高最近内容的查询效率
CREATE INDEX idx_articles_recent
ON articles (published_at DESC)
WHERE published_at > now() - interval '90 days';

setweight 是相关度排序的灵魂 —— 标题命中比正文命中更重要,这一行配置直接决定了搜索质量。

4.4 完整查询手册

基础检索

-- 简单 AND 查询
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & database');

-- 网页搜索语法(websearch_to_tsquery)— PG 11+ 推荐
-- 直接接受 "postgres database" -junk 这种用户友好语法
SELECT id, title FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'postgres database -mysql');

-- 短语搜索(连续词)
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'distributed system');

websearch_to_tsquery 支持:

• word1 word2 → AND
• "word1 word2" → 短语
• -word → NOT
• OR → OR

这就是 Google 风格的搜索语法,直接拿来给前端用就行。

相关度排序(ts_rank)

SELECT id, title,
       ts_rank(search_vector, q) AS rank,
       ts_rank_cd(search_vector, q) AS rank_cd  -- cover density,考虑距离
FROM articles, websearch_to_tsquery('english', 'postgres queue') q
WHERE search_vector @@ q
ORDER BY rank_cd DESC
LIMIT 20;

ts_rank vs ts_rank_cd:

• ts_rank:基于 TF(词频)
• ts_rank_cd:Cover Density,考虑词在文档中的距离——挨在一起的得分更高

生产推荐 ts_rank_cd,效果更接近 ES 的 BM25。

高亮(给前端展示用)

SELECT id, title,
       ts_headline('english', body, q,
           'StartSel=<mark>, StopSel=</mark>, MaxWords=30, MinWords=15')
FROM articles, websearch_to_tsquery('english', 'postgres') q
WHERE search_vector @@ q;

输出:

"PostgreSQL is a fantastic <mark>database</mark>. <mark>postgres</mark> rules."

这一行直接拿来贴到搜索结果页,不需要前端做任何处理。

多表搜索(用 UNION 或视图)

CREATE VIEW search_index AS
    SELECT id, 'article' AS type, title AS heading, search_vector FROM articles
    UNION ALL
    SELECT id, 'comment',  body  AS heading, search_vector FROM comments;

SELECT * FROM search_index
WHERE search_vector @@ websearch_to_tsquery('english', 'postgres')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'postgres')) DESC
LIMIT 20;

4.5 中文搜索(必看)

PG 自带的分词器只对空格语言友好。中文需要装第三方分词扩展:

实战:pg_jieba

CREATE EXTENSION pg_jieba;

-- pg_jieba 提供几种 mode
SELECT to_tsvector('jiebacfg',  '我爱使用 PostgreSQL 数据库');  -- 精确模式
-- 输出: '使用':3 '我爱':1 '数据库':5 'postgresql':4

SELECT to_tsvector('jieba_mp',  '南京市长江大桥');  -- 多分词
-- 输出: '南京':1 '南京市':2 '长江':4 '长江大桥':5 '大桥':6 '市长':3

-- 在表里用
CREATE TABLE cn_docs (
    id BIGSERIAL PRIMARY KEY,
    title TEXT, body TEXT,
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('jiebacfg', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('jiebacfg', coalesce(body, '')),  'B')
    ) STORED
);
CREATE INDEX ON cn_docs USING GIN (search_vector);

-- 查询
SELECT * FROM cn_docs
WHERE search_vector @@ to_tsquery('jiebacfg', '数据库 & 优化');

注意:中文 websearch_to_tsquery 也支持,但要传对 config 名:

SELECT * FROM cn_docs
WHERE search_vector @@ websearch_to_tsquery('jiebacfg', '"数据库优化"');

4.6 模糊匹配 / 拼写容错(pg_trgm)

tsvector 处理"完整词",但用户经常拼错字 / 输入残词。这时候用 trigram(三元字符组):

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);

-- 1. 相似度 % 操作符(默认阈值 0.3)
SELECT title FROM articles WHERE title % 'postgrss';
-- 拼错的 'postgrss' 也能匹配到 'postgres'

-- 2. 自定义阈值
SET pg_trgm.similarity_threshold = 0.5;

-- 3. 排序
SELECT title, similarity(title, 'postgrss') AS sim
FROM articles
WHERE title % 'postgrss'
ORDER BY sim DESC LIMIT 10;

-- 4. 替代 LIKE '%xx%'(走 trigram 索引,极快!)
SELECT * FROM articles WHERE title ILIKE '%postgres%';
-- 注意:必须有 trigram 索引才会快

pg_trgm 还有一个杀手锏:它让 LIKE '%xx%' 也能走索引!这一招就能让大部分"模糊查询"性能提升 100 倍。

4.7 混合检索:全文 + 模糊 + 向量

生产级搜索从来不是单一手段,多路召回 + 重排序才是王道:

-- 召回 A:全文匹配
WITH fts AS (
    SELECT id, ts_rank_cd(search_vector, q) * 1.0 AS score
    FROM articles, websearch_to_tsquery('english', 'postgres') q
    WHERE search_vector @@ q
    LIMIT 100
),
-- 召回 B:模糊匹配
trgm AS (
    SELECT id, similarity(title, 'postgres') * 0.5 AS score
    FROM articles
    WHERE title % 'postgres'
    LIMIT 100
),
-- 召回 C:向量相似(见 §5)
vec AS (
    SELECT id, (1 - (embedding <=> $1::vector)) * 0.8 AS score
    FROM articles
    ORDER BY embedding <=> $1::vector
    LIMIT 100
)
SELECT id, sum(score) AS final_score
FROM (
    SELECT * FROM fts UNION ALL
    SELECT * FROM trgm UNION ALL
    SELECT * FROM vec
) t
GROUP BY id
ORDER BY final_score DESC
LIMIT 20;

这是一套完整的现代搜索架构,全部在 PG 里,一句 SQL。ES 想做这个要拼好几个组件。

4.8 性能基准

PG 全文搜索的极限大约就在 1 亿文档级别,再往上 ES 优势开始明显。

但95% 的业务搜索都在千万级以下,PG 完美胜任。

4.9 你必须躲开的几个坑

坑 1:没用 STORED 生成列,每次查询都重算 tsvector

反例:

-- 这种写法每次查询都临时算 tsvector!慢死
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('postgres');

正解:用生成列 + GIN 索引(见 §4.3)。

坑 2:GIN 索引写入慢

GIN 索引的写入开销比 B-tree 高几倍。如果是高写入场景,启用 fastupdate:

ALTER INDEX idx_articles_search SET (fastupdate = on, gin_pending_list_limit = '4MB');

但记得监控 pg_stat_user_indexes 里的 pending list 大小,过大要手动 gin_clean_pending_list。

坑 3:中文分词词典更新

pg_jieba 默认词典不包含很多业务专名词(品牌、人名)。必须加自定义词典:

echo "PostgreSQL\nDroid\n字节跳动" >> /path/to/jieba/userdict.txt

否则你搜"字节跳动"会被切成"字节" + "跳动",搜索质量灾难。

坑 4:相关度排序需要 LIMIT

ts_rank 是计算密集型,不要在百万行结果上排序。永远配合 LIMIT,先用索引收口:

-- 反例:全集排序
SELECT * FROM articles WHERE search_vector @@ q ORDER BY ts_rank(...);

-- 正解:GIN 索引收口 + 重排序
SELECT * FROM (
    SELECT id, search_vector FROM articles
    WHERE search_vector @@ q
    LIMIT 1000     -- 先收 1000 个候选
) t
ORDER BY ts_rank(search_vector, q) DESC
LIMIT 20;

4.10 真需要 ES 的场景

诚实地说:

新项目搜索量级 < 千万,无脑选 PG。等真不够用再换 ES。

4.11 一段心法

ES 是搜索引擎,PG 是数据库。
但当你的搜索需求 < ES 的最低复杂度时,PG 这个"兼职搜索引擎"反而比"专业搜索引擎"轻便、稳定、贴近业务。

所有的"专业方案"都有最低运维成本,而 PG 的运维你已经付过了。

5. PG 当向量数据库用 —— 替代 Pinecone / Milvus

5.1 AI 时代 PG 最闪光的一面

2023 年,pgvector 一个扩展,让一整个赛道的纯向量数据库公司估值集体跳水。这不是夸张。

为什么?因为 RAG / Agent 应用的核心痛点,纯向量库根本解不了。

举个例子,一个企业知识库 RAG 的真实查询:

检索:"我们公司去年北美市场的销售策略" 的相关文档
限定:当前用户有权访问 + 文档 未过期 + 文档类型 = "策略报告" + 时间范围 2024-2025

纯向量库(Pinecone、Weaviate)需要:

1. 应用层先查权限服务,拿到该用户能看的 doc_ids
2. 应用层把 doc_ids 切片(metadata 过大 Pinecone 拒绝)
3. 调向量库,带上 metadata filter
4. 拿到向量结果后,再回业务库 fetch 详情
5. 应用层重排
6. 代码量 > 200 行,延迟叠加 4 个网络往返

PG + pgvector 的实现:

SELECT d.id, d.title, d.content, d.created_at,
       1 - (d.embedding <=> $1::vector) AS similarity
FROM documents d
JOIN user_doc_permissions p ON p.doc_id = d.id
WHERE p.user_id = $2
  AND d.expires_at > now()
  AND d.doc_type = 'strategy_report'
  AND d.created_at BETWEEN '2024-01-01' AND '2025-12-31'
ORDER BY d.embedding <=> $1::vector
LIMIT 10;

一句 SQL,搞定。 这就是为什么:

"Vector is just another data type." —— Andrew Kane,pgvector 作者

业界证据:

• OpenAI 自己的应用 用 PG + pgvector 做内部 RAG
• Supabase:整个 AI 栈基于 pgvector
• Neon / Crunchy Data / TimescaleDB / EnterpriseDB:全部把 pgvector 列为头牌特性
• Anthropic、Cohere 的官方文档 都用 pgvector 当首选示例

5.2 pgvector 内部原理(必看)

向量类型

pgvector 提供三种向量类型:

生产强烈推荐 halfvec:

• OpenAI text-embedding-3-large(3072 维):float32 = 12KB/向量,float16 = 6KB
• 千万向量:float32 = 120GB,half = 60GB
• 召回率几乎不变(实测差距 < 1%)

三种距离操作符

99% RAG 用 <=>(余弦距离),因为 OpenAI / 大部分模型的 embedding 都已 normalize。

5.3 HNSW vs IVFFlat:你必须选对的索引

pgvector 有两种近似最近邻(ANN)索引,选错了性能差 10 倍:

IVFFlat(老方案)

原理:聚类(K-means)+ 倒排索引

• 把所有向量先聚成 N 个簇
• 查询时只搜最近的 nprobe 个簇

CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);   -- 数据量 / 1000 大约是 sqrt(N) 经验值

-- 查询时调精度
SET ivfflat.probes = 10;   -- 默认 1,越大越准但越慢

优点:建索引快、内存小
缺点:召回率不如 HNSW,需要先有数据才能建(冷启动需要数据)

HNSW(推荐)

原理:Hierarchical Navigable Small World,多层图结构

• 每个向量是图的一个节点
• 上层稀疏(快速跳跃),下层稠密(精细搜索)
• 类似跳表的多层索引

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 查询时调精度
SET hnsw.ef_search = 40;   -- 默认 40,越大越准

参数解读:

• m:每个节点的连接数(默认 16,16 够用,32 更准但贵 2x)
• ef_construction:建索引时的搜索宽度(默认 64,256 是质量上限)
• ef_search:查询时的搜索宽度(默认 40,40-200 之间动态调)

对比表:

结论:99% 选 HNSW。除非你内存特别紧张,或者数据集过亿且每天大量重建。

5.4 metadata filtering:pgvector 的核心战场

这是 PG 比 Pinecone 强 10 倍的地方。

三种过滤策略

-- 策略 1:Pre-filter(先 filter 再搜)
-- 适合:filter 选择性高(< 10% 数据)
SELECT id FROM docs
WHERE user_id = $1 AND status = 'active'   -- 假设这个条件命中 1000 行
ORDER BY embedding <=> $2::vector LIMIT 10;

-- 策略 2:Post-filter(先搜再 filter)
-- 适合:filter 选择性低(> 50% 数据)
SELECT id FROM (
    SELECT id, user_id, status, embedding
    FROM docs
    ORDER BY embedding <=> $2::vector LIMIT 100
) t
WHERE user_id = $1 AND status = 'active'
LIMIT 10;

-- 策略 3:Iterative scan(pgvector 0.8+ 杀手锏)
-- 自动决定 pre/post,逐步扩大搜索范围直到拿够 10 个
SET hnsw.iterative_scan = on;

重点:pgvector 0.8(2024)的 iterative_scan 是革命性升级——它让 metadata filter 不再需要手动调 ef_search,自动迭代直到拿够结果。这一步直接抹平了和 Pinecone 的功能差距。

partial 索引 + 多向量索引

-- partial HNSW:只索引活跃文档
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
WHERE status = 'active';

-- 多个 partial 索引,按 tenant 分
CREATE INDEX docs_t1_emb ON docs USING hnsw (embedding vector_cosine_ops) WHERE tenant_id = 1;
CREATE INDEX docs_t2_emb ON docs USING hnsw (embedding vector_cosine_ops) WHERE tenant_id = 2;

这种 per-tenant 索引在多租户 RAG 里效果显著,Pinecone 做不到。

5.5 完整的 RAG schema(可直接抄)

CREATE EXTENSION vector;

-- 文档表
CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    tenant_id   BIGINT NOT NULL,
    user_id     BIGINT,
    title       TEXT,
    source      TEXT,                       -- 来源 URL / 文件路径
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT now(),
    expires_at  TIMESTAMPTZ
);

-- chunk 表(一篇文档切多块)
CREATE TABLE chunks (
    id          BIGSERIAL PRIMARY KEY,
    doc_id      BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    seq         INT NOT NULL,                -- chunk 顺序
    content     TEXT NOT NULL,
    token_count INT,
    embedding   halfvec(1536),               -- 用 halfvec 省空间
    -- 也存一份 tsvector,做混合检索
    search_vector tsvector GENERATED ALWAYS AS (to_tsvector('simple', content)) STORED
);

-- 关键索引
CREATE INDEX chunks_doc ON chunks (doc_id);
CREATE INDEX chunks_emb ON chunks USING hnsw (embedding halfvec_cosine_ops) WITH (m=16, ef_construction=64);
CREATE INDEX chunks_fts ON chunks USING GIN (search_vector);

-- 多租户的 partial 索引(可选,租户多时强烈推荐)
CREATE INDEX chunks_emb_active ON chunks USING hnsw (embedding halfvec_cosine_ops)
    WHERE doc_id IN (SELECT id FROM documents WHERE expires_at IS NULL OR expires_at > now());

完整的混合检索 SQL(向量 + 全文,RRF 融合)

RRF(Reciprocal Rank Fusion) 是工业级 RAG 的标准融合算法:

WITH vec_search AS (
    SELECT c.id, RANK() OVER (ORDER BY c.embedding <=> $1::halfvec) AS rank
    FROM chunks c
    JOIN documents d ON c.doc_id = d.id
    WHERE d.tenant_id = $2 AND (d.expires_at IS NULL OR d.expires_at > now())
    ORDER BY c.embedding <=> $1::halfvec
    LIMIT 50
),
fts_search AS (
    SELECT c.id, RANK() OVER (ORDER BY ts_rank_cd(c.search_vector, q) DESC) AS rank
    FROM chunks c
    JOIN documents d ON c.doc_id = d.id,
         websearch_to_tsquery('simple', $3) q
    WHERE d.tenant_id = $2 AND c.search_vector @@ q
    LIMIT 50
)
SELECT c.id, c.content, c.doc_id,
       sum(1.0 / (60 + r.rank)) AS rrf_score        -- RRF 公式,k=60
FROM (
    SELECT id, rank FROM vec_search UNION ALL
    SELECT id, rank FROM fts_search
) r
JOIN chunks c ON c.id = r.id
GROUP BY c.id, c.content, c.doc_id
ORDER BY rrf_score DESC
LIMIT 10;

这就是工业级混合检索,你直接抄走能省一周开发。

5.6 性能基准

(2024 年公开 benchmark,百万向量,768 维)

pgvector 在性能上完全在第一梯队,而你免费获得了 PG 的所有其他能力。

5.7 你必须躲开的几个坑

坑 1:不调 maintenance_work_mem,建索引慢成狗

HNSW 建索引非常吃内存。默认 maintenance_work_mem=64MB 时,千万向量建索引可能要 24 小时。

# 临时提升
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 8;

-- 然后建索引
CREATE INDEX CONCURRENTLY ON chunks USING hnsw ...

提到 8GB 之后,千万向量建索引降到 30-60 分钟。

坑 2:UPDATE embedding 触发索引重建

每次更新一个 embedding,HNSW 索引都要修复图结构。不要频繁 UPDATE 同一行的 embedding。

最佳实践:embedding 一次性写入,不更新。如果文档变了,新插入一行,标记旧的为 archived。

坑 3:维度选择影响巨大

OpenAI 的 text-embedding-3-large 是 3072 维,但支持 truncate —— 你可以用前 1024 维或 256 维,召回率只下降 2-3%,索引大小降 3-12 倍。

embedding = openai_embed(text, dimensions=1024)  # 主动 truncate

生产强推荐:1024 维 + halfvec,性能与质量的甜区。

坑 4:忘了 ANALYZE

PG 规划器需要统计信息才能选对索引。新建 HNSW 索引后必须 ANALYZE:

ANALYZE chunks;

否则可能走全表扫,慢 100 倍。

5.8 真需要 Pinecone / Milvus 的场景

否则,pgvector is all you need。

5.9 一段心法

AI 时代,数据 + 向量必须在一起。
把它们切开放在两个数据库,等于把肉和骨头分开装,搬运代价巨大。
pgvector 出现的意义不是"PG 多了一个功能",而是让 PG 成为 AI 应用的天然数据底座。

6. PG 当时序数据库用 —— 替代 InfluxDB

6.1 一个被 PG 吞掉的赛道

时序数据库(TSDB)曾经是 2015-2020 年最热门的 NoSQL 赛道:InfluxDB、Prometheus、TDengine、Druid、QuestDB、Apache IoTDB……一堆专业方案。

然后 TimescaleDB 出现了。

它是一个 PG 扩展,装上之后,普通的 PG 表瞬间变成可以扛百亿行的时序库。它的杀手锏是:SQL 100% 兼容,你以前的 PG 知识全部继续生效。

更猛的是 InfluxDB 自己:它从 InfluxQL → Flux → 又回到 SQL,自我打脸地承认 SQL 才是终极答案。InfluxDB 3.0 干脆基于 Apache DataFusion + Parquet,变成一个"披着 InfluxDB 皮的 SQL 数据库"。

业界证据:

• Stack Overflow 的所有指标存储都用 TimescaleDB
• Cloudflare Logpush 用 TimescaleDB
• CERN(欧洲核子研究中心) 的物理实验数据用 TimescaleDB
• Mirantis、Comcast、Bloomberg 的内部监控
• 各大 IoT 平台:MotorTrend、E-On、Schneider Electric

新项目搞监控/IoT/金融行情/任何带时间戳的数据,无脑选 TimescaleDB。

6.2 你必须懂的 hypertable 内部原理

普通 PG 表插入越来越慢的原因:索引越来越大,B-tree 高度增加,缓存不下。

TimescaleDB 的解法是:把一张逻辑大表自动切成很多小物理表(chunk),每个 chunk 按时间区间保存:

逻辑表: metrics
  ├── _hyper_1_1_chunk     (2026-04-01 ~ 2026-04-08)
  ├── _hyper_1_2_chunk     (2026-04-08 ~ 2026-04-15)
  ├── _hyper_1_3_chunk     (2026-04-15 ~ 2026-04-22)
  └── _hyper_1_4_chunk     (2026-04-22 ~ 2026-04-29)

效果:

1. 写入永远写在最新 chunk,小、热、缓存友好,写入速度恒定
2. 查询时间范围 → 自动只扫相关 chunk(chunk exclusion)
3. drop 老 chunk 是 DROP TABLE,毫秒级,不是 DELETE
4. 每个 chunk 独立 vacuum、独立索引、独立压缩

这就是为什么 TimescaleDB 能扛百亿行而单 PG 表挂掉的原因。

6.3 完整可用的 schema

CREATE EXTENSION timescaledb;

CREATE TABLE metrics (
    time      TIMESTAMPTZ NOT NULL,
    device_id INT          NOT NULL,
    metric    TEXT         NOT NULL,
    value     DOUBLE PRECISION,
    tags      JSONB
);

-- 一键转 hypertable
SELECT create_hypertable(
    'metrics', 'time',
    chunk_time_interval => INTERVAL '1 day',     -- 一天一个 chunk
    if_not_exists => TRUE
);

-- 时间序列必备索引
CREATE INDEX ON metrics (device_id, time DESC);
CREATE INDEX ON metrics (metric, time DESC);

chunk 间隔怎么选? 经验值:

• 每个 chunk 应该能装进 25% 内存(让活跃 chunk 在 buffer pool 里)
• 写入速度 1k/s → 一天一个
• 写入速度 10k/s → 一小时一个
• 写入速度 1M/s → 几分钟一个

太大查询慢,太小元数据膨胀。SELECT chunk_relation_size('metrics'); 查看现状。

多列 + 空间分区(高基数维度场景)

如果 device_id 很多(几百万设备),只按时间分区还会慢。可以双维分区:

SELECT create_hypertable(
    'metrics', 'time',
    chunk_time_interval => INTERVAL '1 day'
);

SELECT add_dimension('metrics', 'device_id', number_partitions => 16);
-- device_id 通过 hash 分到 16 个空间分区

这相当于给时序数据自带 sharding,InfluxDB / Prometheus 都很难做到。

6.4 time_bucket:时序 SQL 的核心动词

-- 每 5 分钟聚合
SELECT time_bucket('5 minutes', time) AS bucket,
       device_id,
       avg(value) AS avg_v,
       max(value) AS max_v,
       percentile_disc(0.95) WITHIN GROUP (ORDER BY value) AS p95
FROM metrics
WHERE metric = 'cpu' AND time > now() - interval '1 day'
GROUP BY bucket, device_id
ORDER BY bucket;

-- 不规则时段(如交易日窗口)
SELECT time_bucket('1 hour', time, '08:00') AS bucket, ...
-- 从 8 点对齐桶起点

-- 时间补齐(填充无数据的桶)
SELECT time_bucket_gapfill('5 minutes', time, now() - interval '1 day', now()) AS bucket,
       device_id,
       avg(value),
       interpolate(avg(value)) AS interpolated_v,    -- 线性插值
       locf(avg(value))         AS last_observed     -- 用上次值填充
FROM metrics
WHERE device_id = $1
GROUP BY bucket, device_id;

time_bucket_gapfill + interpolate 是时序场景的杀手锏——监控大盘上的"为什么这一段没数据"难题,SQL 一行搞定。

6.5 连续聚合(Continuous Aggregates):时序 MV 的进化版

普通物化视图是全量刷新,百亿行刷一次要几小时。TimescaleDB 的连续聚合只增量计算新数据:

-- 创建连续聚合
CREATE MATERIALIZED VIEW metrics_5min
WITH (timescaledb.continuous) AS
SELECT time_bucket('5 minutes', time) AS bucket,
       device_id,
       avg(value)  AS avg_v,
       max(value)  AS max_v,
       min(value)  AS min_v,
       count(*)    AS n
FROM metrics
GROUP BY bucket, device_id
WITH NO DATA;

-- 自动刷新策略
SELECT add_continuous_aggregate_policy('metrics_5min',
    start_offset      => INTERVAL '1 day',     -- 重算最近 1 天(允许迟到数据)
    end_offset        => INTERVAL '5 minutes', -- 不算最近 5 分钟(还在变化)
    schedule_interval => INTERVAL '1 minute'); -- 每分钟跑一次

-- 查询直接走预聚合,飞快
SELECT * FROM metrics_5min
WHERE device_id = 42 AND bucket > now() - interval '1 hour';

多层连续聚合(分级预聚合):

-- 5 分钟粒度
CREATE MATERIALIZED VIEW metrics_5min ...;

-- 1 小时粒度,基于 5 分钟粒度建!
CREATE MATERIALIZED VIEW metrics_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', bucket) AS bucket,
       device_id, avg(avg_v), max(max_v), min(min_v)
FROM metrics_5min
GROUP BY 1, 2;

-- 1 天粒度,基于 1 小时粒度
CREATE MATERIALIZED VIEW metrics_1d ...;

Grafana 大盘根据查询时间范围自动选粒度,百亿行的 dashboard 也能秒开。

6.6 列存压缩:节省 90%+ 空间

TimescaleDB 的压缩是把行存 chunk 重组为列存格式 + 字典编码 + Delta-Delta + Gorilla(Facebook 的浮点压缩算法):

ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',           -- 列存按 device_id 分组
    timescaledb.compress_orderby   = 'time DESC, metric'    -- 时间倒序
);

-- 自动压缩 7 天前的数据
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- 看压缩效果
SELECT pg_size_pretty(before_compression_total_bytes) AS before,
       pg_size_pretty(after_compression_total_bytes)  AS after,
       round(100 * (1 - after_compression_total_bytes::numeric / before_compression_total_bytes), 2) AS ratio_pct
FROM hypertable_compression_stats('metrics');
-- 典型输出: before=120GB, after=12GB, ratio_pct=90%

真实案例:某 IoT 公司 3 亿行/天的设备数据,压缩前 2TB,压缩后 180GB,省 91%,且查询速度因为列存反而快 2-5x(因为只读相关列)。

6.7 数据保留策略

-- 90 天前的数据自动删除(DROP CHUNK,毫秒级)
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- 高级:不同维度的差异化保留
-- 用 hierarchical aggregates,原始 7 天,5min 30 天,1h 1 年,1d 永久
SELECT add_retention_policy('metrics',     INTERVAL '7 days');
SELECT add_retention_policy('metrics_5min', INTERVAL '30 days');
SELECT add_retention_policy('metrics_1h',   INTERVAL '1 year');
-- metrics_1d 不加 → 永久保留

这是工业级时序数据治理的标准模式,InfluxDB 也叫 retention policy,但 TimescaleDB 的 SQL 可读性更好。

6.8 性能基准(TimescaleDB 官方 + 第三方)

(写入吞吐,1000 列宽,8C/32GB):

TimescaleDB 在 99% 时序场景都领先,极端 OLAP 大宽表场景会输给 ClickHouse(那是另一个赛道,见 §14)。

6.9 真实玩法:观测平台只用 PG

一个只有 PG + TimescaleDB 的完整观测体系:

-- 1. metrics 表(指标)
CREATE TABLE metrics (...);
SELECT create_hypertable('metrics','time');

-- 2. logs 表(日志)
CREATE TABLE logs (
    time TIMESTAMPTZ, service TEXT, level TEXT,
    message TEXT,
    fields JSONB,
    search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', message)) STORED
);
SELECT create_hypertable('logs','time');
CREATE INDEX ON logs USING GIN (search_vector);
CREATE INDEX ON logs (service, time DESC);

-- 3. traces 表(链路)
CREATE TABLE traces (
    time TIMESTAMPTZ, trace_id UUID, span_id UUID, parent_span_id UUID,
    service TEXT, operation TEXT, duration_ms NUMERIC,
    attributes JSONB
);
SELECT create_hypertable('traces','time');
CREATE INDEX ON traces (trace_id);
CREATE INDEX ON traces USING GIN (attributes jsonb_path_ops);

Grafana 直接连 PG(它支持 PostgreSQL 数据源),把 metrics + logs + traces 都画上。Loki + Tempo + Prometheus 三件套被一个 PG 替代。

6.10 你必须躲开的坑

坑 1:用 PG 自带的 INSERT,而不是 COPY 大批量

时序数据写入要用 COPY 或 INSERT ... VALUES ((...),(...),(...)),单行 INSERT 慢 100 倍。

坑 2:chunk 太多 → 元数据爆炸

每个 chunk 是一张表,chunk 太小(1 分钟一个)跑一年就有 50 万张表,PG 元数据卡住。保持 chunk 数 < 1 万。

坑 3:没设 retention,磁盘炸

时序数据是无限增长的,忘了加 retention policy = 早晚磁盘满。第一天就要设。

坑 4:time 字段没索引或 chunk 字段错

-- 反例:用 created_at 当 chunk 字段,但查询都按 event_time 过滤
SELECT create_hypertable('events','created_at');
-- 查询 event_time 时无法 chunk exclusion,慢

chunk 字段必须是查询最常用的时间字段。

6.11 真需要 InfluxDB / ClickHouse 的场景

诚实地说:

新项目 99% 选 TimescaleDB。

6.12 一段心法

时序数据库不是关系型数据库的对立面,
它只是关系型数据库针对时间维度做的特化。
TimescaleDB 用最优雅的方式证明了:特化不必脱离 SQL。

7. PG 当图数据库用 —— 替代 Neo4j(轻度场景)

7.1 诚实开场:这一块 PG 是相对最弱的

不像前面几节我可以拍胸脯说"PG 完全够",图数据库这块,我得诚实一点。

重度图查询(社交网络深度遍历、知识图谱推理、PageRank、社区发现),Neo4j / TigerGraph / Nebula 仍然有显著优势——它们的存储引擎、查询引擎、内存模型都是为"图遍历"专门设计的。

但好消息是:90% 的项目根本不需要"图数据库",只是需要"能查关系"。这种场景 PG 三种方案任选其一都够用:

1. Recursive CTE(SQL 标准,任何 PG 都有)
2. Apache AGE 扩展(Cypher 查询语言,等价 Neo4j)
3. ltree 扩展(树形结构特化)

下面分别讲。

7.2 方案 A:Recursive CTE(递归查询)

最朴素也最通用,SQL 标准里就有。

完整示例:N 度关注网络

CREATE TABLE follows (
    follower_id BIGINT NOT NULL,
    followee_id BIGINT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (follower_id, followee_id)
);

CREATE INDEX idx_follows_follower ON follows (follower_id);
CREATE INDEX idx_follows_followee ON follows (followee_id);

-- 查 user=1 的 3 度关注网络(BFS)
WITH RECURSIVE network AS (
    -- 锚定项(anchor):起点
    SELECT followee_id AS user_id,
           1 AS depth,
           ARRAY[1::BIGINT, followee_id] AS path     -- 记录路径,防环
    FROM follows
    WHERE follower_id = 1

    UNION ALL

    -- 递归项:从已发现的节点继续走
    SELECT f.followee_id,
           n.depth + 1,
           n.path || f.followee_id
    FROM follows f
    JOIN network n ON f.follower_id = n.user_id
    WHERE n.depth < 3
      AND f.followee_id <> ALL(n.path)              -- 防环关键!
)
SELECT user_id, min(depth) AS shortest_distance
FROM network
GROUP BY user_id
ORDER BY shortest_distance;

两个关键技巧:

• ARRAY 记录路径,防止成环死循环(社交图必备)
• UNION ALL + 外层 min 取最短距离,而不是 UNION DISTINCT(后者性能差)

性能优化:用 BREADTH 控制方向(PG 14+)

WITH RECURSIVE network AS (
    SELECT 1::BIGINT AS user_id, 0 AS depth
    UNION ALL
    SELECT f.followee_id, n.depth + 1
    FROM follows f JOIN network n ON f.follower_id = n.user_id
    WHERE n.depth < 3
) SEARCH BREADTH FIRST BY user_id SET ordercol      -- 显式 BFS,规划器更友好
SELECT * FROM network;

Recursive CTE 的极限

3 度以内 OK,5 度以上请上 AGE 或专业图库。

7.3 方案 B:Apache AGE(Cypher in PostgreSQL)

Apache AGE 是 PG 的图扩展,直接支持 Cypher 查询语言(Neo4j 用的那个)。

CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

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

-- 创建节点和边(Cypher 风格)
SELECT * FROM cypher('social', $$
    CREATE (alice:Person {name: 'Alice', age: 30}),
           (bob:Person {name: 'Bob', age: 25}),
           (alice)-[:FOLLOWS]->(bob)
$$) AS (a agtype);

-- 多跳查询
SELECT * FROM cypher('social', $$
    MATCH (a:Person {name: 'Alice'})-[:FOLLOWS*1..3]->(b:Person)
    RETURN b.name, b.age
$$) AS (name agtype, age agtype);

-- 复杂模式:朋友的朋友推荐(共同好友数排序)
SELECT * FROM cypher('social', $$
    MATCH (me:Person {name: 'Alice'})-[:FOLLOWS]->(friend)-[:FOLLOWS]->(suggested)
    WHERE NOT (me)-[:FOLLOWS]->(suggested) AND suggested <> me
    RETURN suggested.name AS name, count(*) AS common_friends
    ORDER BY common_friends DESC
    LIMIT 10
$$) AS (name agtype, common_friends agtype);

这就是 Neo4j 用户最熟悉的语法,直接搬过来。

AGE 的好处:

• Cypher 表达力强,多跳查询写起来比 SQL 优雅 10 倍
• 与普通 SQL 在同一个 PG 实例,可以混合查询
• 随 PG 备份、复制

AGE 的局限:

• 比 Neo4j 慢(没有专门的图存储引擎)
• 社区相对小,文档不如 Neo4j
• 复杂图算法(PageRank、Betweenness)支持弱

适合场景:项目主要用 PG,但有少量图查询需求,不想再上一个 Neo4j。

7.4 方案 C:ltree —— 树形结构特化

如果你的"关系"是树(分类、组织架构、目录、评论嵌套),不是任意图,ltree 是降维打击。

CREATE EXTENSION ltree;

CREATE TABLE categories (
    id   SERIAL PRIMARY KEY,
    name TEXT,
    path LTREE NOT NULL                             -- 例如:'electronics.phone.iphone'
);

CREATE INDEX idx_categories_path ON categories USING GIST (path);

INSERT INTO categories (name, path) VALUES
    ('Electronics',  'electronics'),
    ('Phone',        'electronics.phone'),
    ('iPhone',       'electronics.phone.iphone'),
    ('Android',      'electronics.phone.android'),
    ('Laptop',       'electronics.laptop');

-- 查询子树(电子产品下所有)
SELECT * FROM categories WHERE path <@ 'electronics';

-- 查询祖先
SELECT * FROM categories WHERE path @> 'electronics.phone.iphone';

-- 模式匹配(电子产品下任意手机)
SELECT * FROM categories WHERE path ~ 'electronics.phone.*';

-- 找直接子节点
SELECT * FROM categories WHERE path ~ 'electronics.*{1}';

性能:百万级树节点,任意子树查询 < 1ms。秒杀任何关系自连接方案。

典型用法:

• 电商商品分类
• 组织架构
• 评论嵌套(Reddit 风格)
• 文件目录

7.5 方案对比

7.6 真需要 Neo4j / TigerGraph 的场景

• ✅ 重度图算法(PageRank、社区发现、最短路径)
• ✅ 关系深度 > 5 的高频查询
• ✅ 图分析为核心的业务(反欺诈、知识图谱)
• ✅ 团队已经全员 Cypher 流派

否则,PG 三选一足够。

8. PG 当地理空间引擎用 —— 行业标准 PostGIS

8.1 这一节没什么好讨论的(但要讲透)

PostGIS 是 GIS 行业的事实标准,Google Maps 之外几乎所有地图/位置服务都在用:

• Uber(早期)、Foursquare、Airbnb、Lyft:核心 location 引擎
• Mapbox、Carto、Mapzen:地图服务底座
• OpenStreetMap:全球开源地图,后端就是 PostGIS
• 美国 USDA、FAA、NOAA、欧洲 ESA、联合国 的官方 GIS 系统

它没有"替代品"这个说法——商业地理空间软件(Esri ArcGIS)几十年的功能,PostGIS 都做到了,而且开源免费。这不是夸张,GIS 行业普遍共识。

8.2 你必须懂的两个类型:geometry vs geography

PostGIS 有两个相似但不同的类型,新手最容易选错:

经验法则:

• 你不知道 SRID 是啥 → 用 geography
• 全球应用 → 用 geography
• 性能极致敏感 + 局部地图 → 用 geometry + 合适投影

8.3 SRID:坐标系编号

PostGIS 用 SRID(Spatial Reference System Identifier)区分坐标系。常用:

中国坐标的坑:GPS 输出 WGS84,但百度/高德地图用 GCJ-02 / BD-09 偏移坐标。直接用 GPS 坐标查百度地图会偏几百米。生产里要做坐标转换,搜 coord_china 库或自实现。

8.4 完整例子:外卖配送场景

CREATE EXTENSION postgis;

-- 商户表
CREATE TABLE shops (
    id        SERIAL PRIMARY KEY,
    name      TEXT NOT NULL,
    location  GEOGRAPHY(Point, 4326) NOT NULL,
    delivery_range_m INT NOT NULL DEFAULT 3000     -- 配送范围 3km
);

-- 关键索引:GiST(用于空间数据)
CREATE INDEX idx_shops_location ON shops USING GIST (location);

-- 插入(注意经度在前!ST_MakePoint(lng, lat))
INSERT INTO shops (name, location) VALUES
    ('麦当劳·王府井', ST_MakePoint(116.4097, 39.9152)::geography),
    ('星巴克·三里屯', ST_MakePoint(116.4565, 39.9384)::geography);

-- 业务查询 1:找用户 5km 内的店,按距离排序
SELECT
    id, name,
    ST_Distance(location, ST_MakePoint($1, $2)::geography) AS dist_m
FROM shops
WHERE ST_DWithin(location, ST_MakePoint($1, $2)::geography, 5000)
ORDER BY location <-> ST_MakePoint($1, $2)::geography     -- KNN 操作符,走索引!
LIMIT 20;

-- 业务查询 2:用户位置是否在某商户的配送范围内
SELECT *
FROM shops
WHERE ST_DWithin(location, ST_MakePoint($1, $2)::geography, delivery_range_m);

<-> 操作符是 KNN(K-Nearest Neighbor)查询,走 GiST 索引,亿级数据 ms 级。这是 PostGIS 的杀手级特性。

8.5 高级几何:多边形、面、行政区

-- 行政区表(多边形)
CREATE TABLE districts (
    id         SERIAL PRIMARY KEY,
    name       TEXT,
    boundary   GEOGRAPHY(MultiPolygon, 4326)
);
CREATE INDEX ON districts USING GIST (boundary);

-- 用户位置属于哪个区?
SELECT name FROM districts
WHERE ST_Within(ST_MakePoint($1, $2)::geography, boundary);

-- 两个区是否接壤?
SELECT a.name, b.name
FROM districts a, districts b
WHERE a.id < b.id AND ST_Touches(a.boundary, b.boundary);

-- 区域面积(平方公里)
SELECT name, ST_Area(boundary) / 1e6 AS area_km2 FROM districts;

行政区数据:网上能下到全国行政区 GeoJSON / Shapefile,直接 ogr2ogr 导入 PostGIS,5 分钟搞定。

8.6 路径与轨迹

CREATE TABLE driver_tracks (
    driver_id BIGINT,
    time      TIMESTAMPTZ,
    location  GEOGRAPHY(Point, 4326)
);

-- 把一段时间的轨迹连成一条线
SELECT driver_id,
       ST_MakeLine(location::geometry ORDER BY time)::geography AS path,
       ST_Length(ST_MakeLine(location::geometry ORDER BY time)::geography) AS total_m
FROM driver_tracks
WHERE driver_id = 1 AND time BETWEEN $1 AND $2
GROUP BY driver_id;

-- 路径是否经过某区域?
SELECT * FROM driver_tracks d, districts dist
WHERE dist.name = '朝阳区' AND ST_Intersects(d.location, dist.boundary);

配合 TimescaleDB,把 driver_tracks 转成 hypertable,实时位置 + 历史轨迹分析,一个 PG 通吃。

8.7 地理 + 业务 + 时间维度,一锅炖

外卖派单的真实查询:

-- 找一个最适合接单的骑手:
-- 1. 在 5km 内
-- 2. 在线
-- 3. 当前订单数 < 3
-- 4. 评分 >= 4.5
-- 按距离 + 评分综合排序
SELECT d.id,
       ST_Distance(d.location, $1::geography) AS dist,
       d.rating,
       d.current_orders
FROM drivers d
WHERE d.online = TRUE
  AND d.current_orders < 3
  AND d.rating >= 4.5
  AND ST_DWithin(d.location, $1::geography, 5000)
ORDER BY (ST_Distance(d.location, $1::geography) / 1000) - (d.rating * 100) ASC
LIMIT 1;

Neo4j / Mongo / Redis 没法做这种"地理 + 业务 + 排序"的混合查询,只有 PostGIS 这种"完整 SQL + 空间能力"才能优雅解决。

8.8 性能基准

亿级数据无压力,这是 PostGIS 几十年优化打磨的结果。

8.9 一段心法

地图就是一种带着空间维度的关系数据。
用 PostGIS,你就是在 SQL 里画地图。
这种优雅,是 Mongo Geo / Redis Geo 永远给不了的。

9. PG 当定时任务调度器用 —— 替代 Cron / Airflow

9.1 你想想这个场景

你写了一个数据清理 SQL,需要每天凌晨 3 点跑一次。怎么做?

经典方案:

1. 起一台机器
2. 跑一个 cron daemon
3. 写个 shell 脚本,里面 psql -c "..."
4. 配 SSH key、防火墙、监控、告警
5. 这台机器还要做高可用(主备)
6. 备份这台机器的 cron 配置
7. 下一个开发新人接手,完全不知道这个 cron 存在

整个过程,真正有意义的代码就是那一句 SQL,周边却堆了一堆基础设施。

pg_cron 让这一切回归初心:SQL 数据 + SQL 调度,逻辑就在数据旁边。

9.2 pg_cron 完整用法

安装与配置

# postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'mydb'

-- 重启后
CREATE EXTENSION pg_cron;

调度任务

-- 每 5 分钟清理过期缓存
SELECT cron.schedule('cleanup-cache', '*/5 * * * *',
    $$DELETE FROM cache_kv WHERE expires_at < now()$$);

-- 每天凌晨 3 点归档(多语句)
SELECT cron.schedule('archive-jobs', '0 3 * * *', $$
    WITH moved AS (
        DELETE FROM jobs
        WHERE status IN ('success','dead')
          AND finished_at < now() - interval '7 days'
        RETURNING *
    )
    INSERT INTO jobs_archive SELECT * FROM moved;
$$);

-- 每周一早 9 点发周报(调用业务函数)
SELECT cron.schedule('weekly-report', '0 9 * * 1',
    $$SELECT generate_weekly_report()$$);

-- 每分钟做心跳巡检(配合 §1 的队列)
SELECT cron.schedule('jobs-heartbeat-watchdog', '* * * * *', $$
    UPDATE jobs SET status = 'pending', worker_id = NULL
    WHERE status = 'running' AND heartbeat_at < now() - interval '2 minutes'
$$);

-- 跨数据库执行(PG 16+,新特性)
SELECT cron.schedule_in_database(
    'cleanup-other-db', '0 4 * * *',
    'DELETE FROM logs WHERE created_at < now() - interval ''30 days''',
    'logs_db');

管理任务

-- 查看所有任务
SELECT jobid, schedule, command, jobname, active FROM cron.job;

-- 查看运行历史(必看!出问题就靠这个)
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC LIMIT 50;

-- 失败的任务
SELECT * FROM cron.job_run_details
WHERE status = 'failed' ORDER BY start_time DESC;

-- 取消任务
SELECT cron.unschedule('cleanup-cache');

-- 临时禁用
UPDATE cron.job SET active = false WHERE jobname = 'archive-jobs';

9.3 pg_cron 跟 OS cron 的本质差异

核心优势:逻辑就在数据旁边。

9.4 复杂场景:pg_timetable

pg_cron 是 cron-like(时间触发简单 SQL),如果你需要:

• 任务依赖链(A 完成才跑 B)
• 重试策略 / 超时控制
• 任务分组、并发控制
• 跨任务变量传递

pg_timetable 是更强大的 PG 调度方案(独立守护进程,但所有数据存 PG):

-- 创建一个 chain(任务链)
SELECT timetable.add_job(
    job_name      => 'daily-etl',
    job_schedule  => '0 2 * * *',
    job_command   => 'SELECT extract_data()',
    job_kind      => 'SQL'
);

-- 给 chain 加后续步骤
SELECT timetable.add_task(
    task_name => 'transform',
    kind      => 'SQL',
    command   => 'SELECT transform_data()',
    chain_id  => (SELECT chain_id FROM timetable.chain WHERE chain_name = 'daily-etl')
);

SELECT timetable.add_task(
    task_name => 'notify',
    kind      => 'SHELL',
    command   => 'curl -X POST https://...'
);

pg_timetable 支持 SQL / SHELL / BUILTIN 多种任务类型,基本就是 PG 版的 mini Airflow。

9.5 真实案例:整个 ETL 流水线在 PG 里

-- 凌晨 1 点:从源表抽取增量
SELECT cron.schedule('etl-extract', '0 1 * * *', $$
    INSERT INTO staging.events
    SELECT * FROM source.events
    WHERE event_time > (SELECT max(event_time) FROM staging.events)
$$);

-- 凌晨 2 点:转换
SELECT cron.schedule('etl-transform', '0 2 * * *', $$
    INSERT INTO marts.daily_summary
    SELECT date_trunc('day', event_time), user_id, count(*)
    FROM staging.events
    WHERE event_time >= current_date - 1
    GROUP BY 1, 2
    ON CONFLICT (day, user_id) DO UPDATE SET cnt = EXCLUDED.cnt
$$);

-- 凌晨 3 点:刷新报表
SELECT cron.schedule('etl-refresh-report', '0 3 * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY weekly_kpi$$);

-- 每天告警(没数据时报警)
SELECT cron.schedule('etl-alert', '0 4 * * *', $$
    INSERT INTO alerts (level, msg)
    SELECT 'critical', 'Yesterday data missing!'
    WHERE NOT EXISTS (
        SELECT 1 FROM marts.daily_summary
        WHERE day = current_date - 1
    )
$$);

整套数据管道,0 行 Python,0 个 Airflow,纯 PG。

9.6 监控与告警(必做)

-- 每天检查最近 24 小时是否有失败任务
SELECT cron.schedule('cron-monitor', '0 8 * * *', $$
    INSERT INTO alerts (level, msg, payload)
    SELECT 'warning',
           'pg_cron job failures in last 24h: ' || count(*),
           jsonb_agg(jsonb_build_object('job', jobname, 'time', start_time, 'error', return_message))
    FROM cron.job_run_details
    WHERE status = 'failed' AND start_time > now() - interval '24 hours'
    HAVING count(*) > 0;
$$);

9.7 真需要 Airflow 的场景

单纯定时跑 SQL,pg_cron 就是答案。

9.8 一段心法

数据治理的最高境界是:数据自己治理自己。
pg_cron 让"清理、归档、聚合、巡检"这些治理动作和数据共生,而不是分布在某台失忆的机器上。

10. PG 当分布式锁/信号量用 —— 替代 Redis 的 SETNX

10.1 一个不为人知的 PG 神器

很多用了 10 年 PG 的人都不知道 pg_advisory_lock 这个函数。

但如果你了解它,Redis 分布式锁这一整套库(Redlock、Redisson、Curator)都可以扔掉。

10.2 PG 的锁体系全图

PG 内部有 4 层锁,从粗到细:

1. 表级锁(Table Lock)        ← LOCK TABLE / DDL 用,粗粒度
2. 行级锁(Row Lock)          ← FOR UPDATE / FOR SHARE,跟着事务走
3. 页级锁(Page Lock)          ← PG 内部用,你看不到
4. Advisory Lock(咨询锁)      ← 应用层用,任意整数 key

Advisory Lock 是 PG 给应用程序留的"自由锁":

• PG 不解释这个锁的含义,不管它锁的是什么
• 你给一个 64 位整数(或两个 32 位整数)当 key
• PG 帮你保证"同一时刻只有一个会话能持有这个 key"

用法分两个维度,组合出 4 种 API:

完整 API 表

-- 阻塞式获取(锁不到就等)
SELECT pg_advisory_lock(123);              -- session 级,要手动释放
SELECT pg_advisory_xact_lock(123);         -- 事务级,COMMIT/ROLLBACK 自动释放

-- 非阻塞获取(锁不到立刻返回 false)
SELECT pg_try_advisory_lock(123);          -- session 级
SELECT pg_try_advisory_xact_lock(123);     -- 事务级,推荐!

-- 释放(只有 session 级需要)
SELECT pg_advisory_unlock(123);
SELECT pg_advisory_unlock_all();           -- 释放当前 session 所有锁

-- 双 32 位整数版本(给你两个命名空间)
SELECT pg_try_advisory_xact_lock(NAMESPACE, RESOURCE_ID);

-- 共享锁(读锁)
SELECT pg_advisory_xact_lock_shared(123);

生产推荐 pg_try_advisory_xact_lock(ns, id):

• 非阻塞(可以做 fail-fast)
• 事务级(自动释放,绝对不会死锁)
• 双 key(命名空间隔离)

10.3 命名空间设计模式

为不同业务场景分配不同的命名空间(高 32 位):

class LockNamespace:
    CRON_JOB    = 1
    USER_LOCK   = 2
    LLM_RATE    = 3
    MIGRATION   = 4
    OUTBOX      = 5

# 用户 42 的临界区
got = await conn.fetchval(
    "SELECT pg_try_advisory_xact_lock($1, $2)",
    LockNamespace.USER_LOCK, 42
)

好处:不会跨业务串号,可读性强。

10.4 实战 1:分布式定时任务防重

多个 PG cron 实例 / Sidekiq scheduler / Quartz cluster 同时跑同一个任务,只允许一个执行:

async def run_cron_safely(job_id: int, fn):
    async with conn.transaction():
        got = await conn.fetchval(
            "SELECT pg_try_advisory_xact_lock($1, $2)",
            LockNamespace.CRON_JOB, job_id
        )
        if not got:
            return  # 别人在跑
        await fn()
    # 事务结束 → 锁自动释放

这是分布式 scheduler 的最优雅实现,Quartz cluster 那一套 SQL 表 + ROW LOCK 都比不上。

10.5 实战 2:全局并发信号量(N 个槽位)

你的 LLM 服务最多并发 20 调用,多实例部署时必须全局限流:

LLM_NS = LockNamespace.LLM_RATE
SLOT_COUNT = 20

async def call_llm_with_global_limit(prompt: str):
    for slot in range(SLOT_COUNT):
        async with conn.transaction():
            got = await conn.fetchval(
                "SELECT pg_try_advisory_xact_lock($1, $2)",
                LLM_NS, slot
            )
            if got:
                # 抢到 slot,在事务内调 LLM
                return await llm_api.call(prompt)
    raise BusyError("All 20 slots busy, retry later")

关键巧思:用 0~19 共 20 个 slot,worker 抢任意一个就能跑。这就是分布式信号量。

跟 Redis Redlock 比:

• Redis Redlock 需要 5 节点 quorum,复杂、误用很多、有理论争议
• PG advisory lock 简单、明确、随事务自动释放
• 不需要"我什么时候释放锁"的心智负担

10.6 实战 3:启动时迁移防重

多实例服务启动时跑数据库迁移,只让一个实例跑:

async def run_migrations_once():
    conn = await pg.connect()
    async with conn.transaction():
        got = await conn.fetchval(
            "SELECT pg_try_advisory_xact_lock($1)", 9999
        )
        if not got:
            print("Another instance is migrating, skip")
            return
        await apply_migrations()
        # 事务结束自动释放

Flyway / Liquibase 内部就是这个原理。

10.7 SELECT FOR UPDATE 各种模式对比

除了 advisory lock,PG 还有行级锁的多种模式,用对了能优雅解决很多并发问题:

生产里最常用三种:

• FOR UPDATE:经典更新前锁
• FOR UPDATE SKIP LOCKED:队列出队
• FOR NO KEY UPDATE:有外键引用时,默认推荐(避免不必要的强锁)

10.8 跟 Redis 锁的对比

90% 场景 PG advisory lock 更优,Redis 锁只在"百万 QPS 加锁"场景有意义。

10.9 一段心法

一个好的并发原语,不是看它能锁多快,而是看它能否在任何异常下都不死锁。
Redis 锁靠 TTL 防死锁(导致"我以为锁还在,其实已被人抢"),
PG advisory lock 靠事务生命周期防死锁(无歧义,无心智负担)。
后者才是真正的工程美学。

11. PG 直接吐 API —— 替代后端 CRUD 层

11.1 一个被严重低估的赛道

10 年前我们写后端:Controller → Service → DAO → DB。

90% 的代码在做什么?做 CRUD 的样板代码。

PostgREST / Hasura / Supabase 这一系工具的核心思想是:既然 90% 的后端就是数据库的薄包装,那直接把 PG schema 暴露成 API 不就完了?

听起来激进,但这已经是 Supabase 整个商业模式的基石(估值 5 亿美金以上),Firebase 的 PG 替代品。

11.2 PostgREST 完整能力

PostgREST 是一个 Haskell 写的极快单 binary,启动后:

• 每张表 → 一个 RESTful endpoint
• 每个视图 → 一个 endpoint
• 每个函数 → 一个 RPC endpoint
• JWT 鉴权、RLS 行级权限
• 支持复杂查询、JOIN、聚合

# postgrest.conf
db-uri = "postgres://app:secret@localhost/mydb"
db-schemas = "api"
db-anon-role = "anonymous"
jwt-secret = "your-secret"
server-port = 3000

postgrest postgrest.conf
# 起一个进程,搞定

自动生成的 API

# 1. 列表 + 过滤 + 排序 + 分页
curl 'http://localhost:3000/products?price=gt.100&category=eq.phone&order=created_at.desc&limit=20&offset=40'

# 2. 单条
curl 'http://localhost:3000/products?id=eq.42'

# 3. 创建
curl -X POST 'http://localhost:3000/products' \
  -H 'Content-Type: application/json' \
  -d '{"name":"iPhone","price":999}'

# 4. 更新
curl -X PATCH 'http://localhost:3000/products?id=eq.42' \
  -d '{"price":888}'

# 5. 删除
curl -X DELETE 'http://localhost:3000/products?id=eq.42'

# 6. 嵌入资源(JOIN)
curl 'http://localhost:3000/orders?select=*,user:users(name,email),items(*)'
# 这一行 = 多表 JOIN + 嵌套返回 JSON

# 7. 聚合
curl 'http://localhost:3000/products?select=category,count' --header 'Prefer: count=exact'

# 8. 调用函数(RPC)
curl -X POST 'http://localhost:3000/rpc/search_articles' \
  -d '{"keyword":"postgres"}'

这个查询能力比手写后端 CRUD 还强,且毫无样板代码。

11.3 RLS:数据库层的鉴权

Row Level Security(行级安全)是 PG 9.5 起的内置特性,让数据库自己决定每个用户能看哪些行:

ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- 政策 1:用户只能看到自己创建的
CREATE POLICY user_owns_products ON products
    FOR SELECT USING (user_id = current_setting('jwt.claims.user_id')::bigint);

-- 政策 2:用户只能改自己的
CREATE POLICY user_modifies_own ON products
    FOR UPDATE USING (user_id = current_setting('jwt.claims.user_id')::bigint);

-- 政策 3:管理员看到全部
CREATE POLICY admin_full_access ON products
    FOR ALL TO admin_role USING (true);

配合 PostgREST 的 JWT,前端发请求带 token,PostgREST 把 JWT claims 注入到 PG 的 current_setting,RLS 自动过滤行。

整个鉴权层被压缩成几行 SQL。Supabase 的"几小时就能上线一个全栈应用"卖点核心就是这个。

11.4 完整 SaaS 多租户案例

-- schema
CREATE TABLE tenants (id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, tenant_id BIGINT, email TEXT);
CREATE TABLE projects (id BIGSERIAL PRIMARY KEY, tenant_id BIGINT, name TEXT);

-- 启用 RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE users    ENABLE ROW LEVEL SECURITY;

-- 多租户隔离:每个用户只能看到自己 tenant 的项目
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = (current_setting('jwt.claims.tenant_id'))::bigint);

CREATE POLICY tenant_isolation_users ON users
    USING (tenant_id = (current_setting('jwt.claims.tenant_id'))::bigint);

之后,前端用不同 tenant 的 JWT 调同一个 endpoint,自动隔离。

这是 SaaS 多租户最优雅的实现。比 ORM 层手动 WHERE tenant_id = ? 更安全(忘加 WHERE 是经典事故,RLS 把它变成数据库强制约束)。

11.5 PostgREST vs Hasura vs Supabase 对比

新项目选择:

• 想自己掌控、最轻量 → PostgREST
• GraphQL 主义 → Hasura
• 要 BaaS 体验 → Supabase

11.6 适合 / 不适合的场景

强烈适合:

• ✅ 管理后台 / 中台(CRUD 占 80%)
• ✅ MVP / Hackathon(快速上线)
• ✅ 数据 API(给数据团队的内部 API)
• ✅ 简单业务的全栈应用(博客、待办、笔记)
• ✅ 只读分析 API(BI 工具的后端)

不适合(还是该有正经后端):

• ❌ 复杂业务流程(订单 + 库存 + 支付的多步事务)
• ❌ 重度第三方集成(支付、短信、AI)
• ❌ 性能极致优化的核心服务

最佳实践:主业务用正经后端,次要业务(管理后台、报表、内部工具)用 PostgREST。两全其美。

11.7 一段心法

90% 的"后端代码"是 CRUD 样板,
10% 是真正的业务逻辑。

把 90% 交给 PostgREST,你才有时间打磨那 10%。

12. PG 当审计日志用 —— 替代埋点系统

12.1 一个真实合规故事

某金融客户被监管要求:任何客户敏感字段的修改都必须留痕,可追溯到操作人,保存 7 年。

他们的初版方案:

1. 业务代码里手动写日志
2. 漏掉了大概 30% 的修改路径(批量脚本、运维 SQL、应急修复)
3. 一次合规检查直接被罚

第二版:Trigger + History 表 + pgaudit,所有路径无差别留痕,从 SQL 层做到合规。审计员 SQL 一查就有,简单直接。

这一节讲怎么做到。

12.2 三种粒度,三种方案

12.3 pgaudit:数据库会话级审计

CREATE EXTENSION pgaudit;

# postgresql.conf
pgaudit.log = 'write, ddl, role'    -- write/ddl/role/read/all
pgaudit.log_relation = on            -- 记录表名
pgaudit.log_parameter = on           -- 记录参数
pgaudit.log_statement_once = on      -- 多语句只记一次

输出到 PG 日志:

AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.orders,
       UPDATE orders SET status='shipped' WHERE id=42,
       <none>

生产配置建议:

• pgaudit.log = 'write, ddl'(只审计写入和 DDL,读操作太多)
• 用 pg_log 集中收集到 SIEM(Splunk / ELK)
• 配合 log_min_duration_statement = 0 可以记录所有语句(注意性能)

12.4 业务级审计:Trigger + History 表

通用方案

每张需要审计的业务表,配一张 _audit 历史表 + 一个统一 trigger:

-- 1. 历史表
CREATE TABLE orders_audit (
    audit_id    BIGSERIAL   PRIMARY KEY,
    audit_op    CHAR(1)     NOT NULL,            -- I/U/D
    audit_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    audit_by    TEXT,                            -- 操作人(从 app.user 设置进来)
    audit_ip    INET,
    audit_txid  BIGINT      DEFAULT txid_current(),

    -- 原表的所有列(用 JSONB 兜底,无需 schema 同步)
    row_id      TEXT        NOT NULL,
    old_row     JSONB,
    new_row     JSONB,
    diff        JSONB                             -- 仅变更字段
);

CREATE INDEX ON orders_audit (row_id, audit_at DESC);
CREATE INDEX ON orders_audit (audit_at DESC);
CREATE INDEX ON orders_audit (audit_by);

-- 2. 通用 trigger 函数
CREATE OR REPLACE FUNCTION audit_trigger_fn() RETURNS TRIGGER AS $$
DECLARE
    v_old jsonb;
    v_new jsonb;
    v_diff jsonb;
    v_id   text;
BEGIN
    v_old := CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) ELSE NULL END;
    v_new := CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) ELSE NULL END;
    v_id  := COALESCE(NEW.id::text, OLD.id::text);

    -- 计算 diff
    IF TG_OP = 'UPDATE' THEN
        SELECT jsonb_object_agg(k, jsonb_build_object('old', v_old->k, 'new', v_new->k))
        INTO v_diff
        FROM (
            SELECT key AS k FROM jsonb_each(v_new)
            EXCEPT
            SELECT key FROM jsonb_each(v_old)
            WHERE v_new->key IS DISTINCT FROM v_old->key
        ) t;
    END IF;

    EXECUTE format('INSERT INTO %I_audit (audit_op, audit_by, audit_ip, row_id, old_row, new_row, diff)
                    VALUES ($1,$2,$3,$4,$5,$6,$7)', TG_TABLE_NAME)
    USING substring(TG_OP, 1, 1),
          current_setting('app.user', true),
          current_setting('app.ip', true)::inet,
          v_id, v_old, v_new, v_diff;

    RETURN COALESCE(NEW, OLD);
END $$ LANGUAGE plpgsql;

-- 3. 给业务表加 trigger
CREATE TRIGGER orders_audit_trg
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

应用层注入操作人

业务连接数据库时,设置一下当前用户:

async with conn.transaction():
    await conn.execute("SELECT set_config('app.user', $1, true)", current_user.email)
    await conn.execute("SELECT set_config('app.ip',   $1, true)", request.client.host)
    # 后续所有 SQL 的 trigger 都能拿到这两个值
    await conn.execute("UPDATE orders SET status='shipped' WHERE id=42")

这套方案的好处:

• 所有业务路径无差别留痕(应用代码、批量脚本、psql 手动跑)
• diff 字段可以快速看到"改了什么"
• JSONB 兜底,业务表加字段不需要改 audit 表

查询历史

-- 看某订单的所有变更
SELECT audit_at, audit_by, audit_op, diff
FROM orders_audit
WHERE row_id = '42'
ORDER BY audit_at DESC;

-- 看某用户最近一周改了什么
SELECT audit_at, TG_TABLE_NAME, row_id, diff
FROM orders_audit
WHERE audit_by = 'alice@example.com'
  AND audit_at > now() - interval '7 days'
ORDER BY audit_at DESC;

12.5 Temporal Tables:时间旅行查询

CREATE EXTENSION temporal_tables;

CREATE TABLE products (
    id          INT PRIMARY KEY,
    name        TEXT, price NUMERIC,
    sys_period  tstzrange NOT NULL DEFAULT tstzrange(now(), null)
);

-- 历史表
CREATE TABLE products_history (LIKE products);

-- 一行 trigger 启用时间旅行
CREATE TRIGGER products_versioning
BEFORE INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'products_history', true);

之后:

-- 看 2026-04-01 时刻的产品价格(时间旅行)
SELECT * FROM products WHERE id = 1
  AND sys_period @> '2026-04-01'::timestamptz
UNION ALL
SELECT * FROM products_history WHERE id = 1
  AND sys_period @> '2026-04-01'::timestamptz;

这就是 SVN for your data:任何时刻的数据快照都能查到。

12.6 SCD Type 2:维度数据演进

业务里经常需要维度演进(用户改名、客户更换归属、产品调价)。SCD-2(Slowly Changing Dimension Type 2) 是数据仓库的经典模型:

CREATE TABLE customer_scd2 (
    surrogate_key BIGSERIAL PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    name          TEXT,
    region        TEXT,
    valid_from    TIMESTAMPTZ NOT NULL,
    valid_to      TIMESTAMPTZ,            -- NULL 代表当前
    is_current    BOOLEAN GENERATED ALWAYS AS (valid_to IS NULL) STORED
);

CREATE INDEX ON customer_scd2 (customer_id, valid_to);
CREATE UNIQUE INDEX ON customer_scd2 (customer_id) WHERE valid_to IS NULL;

这种结构让你可以在 BI 报表里"以历史身份"统计——比如"2024 年时这个客户属于哪个区域?"。

12.7 一段心法

数据真正的价值不是它当下的样子,
而是它怎么变成现在这个样子的轨迹。
一个不留历史的系统,等于一个失忆的人。

13. PG 做 CDC / 流处理 —— 替代 Kafka 的部分场景

13.1 一个被低估的能力

CDC(Change Data Capture)是现代数据架构的核心:把数据库的每一次变更实时流出来,驱动下游一切。

很多团队的方案是 Kafka + Debezium,但你知道吗?PG 自带的逻辑复制就能做 CDC,而且 Debezium 也是基于它实现的。

如果你的下游消费者不多(< 5 个)、不需要长期存储事件,直接用 PG 的逻辑复制比 Kafka 简单 10 倍。

13.2 PG 逻辑复制原理

PG 的 WAL(Write-Ahead Log)记录了所有变更。逻辑复制就是:

事务写入 → WAL → 逻辑解码插件 → Replication Slot → 订阅者

关键概念:

关键提醒:Replication Slot 会让 WAL 不被回收。如果订阅者断了不再消费,WAL 会无限堆积撑爆磁盘。生产里必须监控:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

13.3 三种使用方式

方式 1:PG 原生订阅(PG → PG 复制)

PG 10+ 的内置功能,无需任何外部工具:

-- 在源库
CREATE PUBLICATION orders_pub FOR TABLE orders, customers;

-- 在目标库
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=source.db port=5432 dbname=mydb user=replicator'
    PUBLICATION orders_pub;

用途:

• 多区域只读副本
• 蓝绿部署 / 数据库迁移
• 跨业务库的数据同步

方式 2:Debezium → Kafka(经典 CDC)

如果下游消费者多,用 Debezium 把 PG 变更发到 Kafka:

# Debezium connector config
{
  "name": "orders-pg-source",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "pg.example.com",
    "database.dbname": "mydb",
    "schema.include.list": "public",
    "table.include.list": "public.orders,public.customers",
    "publication.name": "debezium_pub",
    "slot.name": "debezium_slot"
  }
}

每条变更变成一个 Kafka 事件,下游随便消费。

方式 3:wal2json / pgoutput + 自己消费(轻量)

不想上 Kafka?直接命令行接收:

# 安装 wal2json
CREATE_REPLICATION_SLOT my_slot LOGICAL wal2json;

# 持续吐 JSON
pg_recvlogical -d mydb --slot=my_slot --start -f - -o pretty-print=1

或者程序消费(Python 用 psycopg):

import psycopg
from psycopg import sql

with psycopg.connect("dbname=mydb replication=database", autocommit=True) as conn:
    cur = conn.cursor()
    cur.start_replication(slot_name='my_slot', decode=True, options={
        'pretty-print': '0'
    })
    for msg in cur:
        event = json.loads(msg.payload)
        # event = {"action":"U","schema":"public","table":"orders","columns":[...]}
        process(event)
        cur.send_feedback(flush_lsn=msg.data_start)

13.4 Outbox Pattern:PG CDC 最闪光的应用

经典分布式痛点:业务 commit 了,但 MQ 发送失败,导致下游不一致。

# 反例:经典踩坑
db.commit_order(order)              # 成功
mq.publish('order_created', order)  # 失败 → 下游永远不知道
# 现在系统状态:DB 有订单,MQ 没事件,业务损坏

Outbox Pattern 的解法:把"发消息"也变成数据库写,放在同一事务里。

表设计

CREATE TABLE outbox (
    id            BIGSERIAL    PRIMARY KEY,
    aggregate     TEXT         NOT NULL,            -- 例:'order'
    aggregate_id  TEXT         NOT NULL,            -- 例:'42'
    event_type    TEXT         NOT NULL,            -- 例:'order_created'
    payload       JSONB        NOT NULL,
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
    published_at  TIMESTAMPTZ,                      -- NULL 表示未发布
    retry_count   SMALLINT     NOT NULL DEFAULT 0,
    dedup_key     TEXT
);

CREATE INDEX idx_outbox_unpublished ON outbox (created_at)
WHERE published_at IS NULL;

业务写入(关键!业务 + 事件同事务)

BEGIN;
    INSERT INTO orders (id, ...) VALUES (42, ...);
    INSERT INTO outbox (aggregate, aggregate_id, event_type, payload, dedup_key)
    VALUES ('order', '42', 'order_created', '{...}', 'order:42:created');
COMMIT;

事务原子性保证:要么订单 + 事件都成功,要么都不成功。

Dispatcher Worker(发送事件)

复用 §1 的 PG 队列模式:

async def dispatch():
    while True:
        # 用 SKIP LOCKED 抢一批未发送的事件
        rows = await conn.fetch("""
            SELECT id, event_type, payload, retry_count
            FROM outbox
            WHERE published_at IS NULL
            ORDER BY created_at LIMIT 50
            FOR UPDATE SKIP LOCKED
        """)
        for r in rows:
            try:
                await send_to_kafka(r['event_type'], r['payload'])
                await conn.execute(
                    "UPDATE outbox SET published_at = now() WHERE id = $1", r['id'])
            except Exception:
                await conn.execute(
                    "UPDATE outbox SET retry_count = retry_count + 1 WHERE id = $1",
                    r['id'])

进阶:Outbox + Debezium = 极致优雅

最优雅方案:Debezium 直接监听 outbox 表的 INSERT,把 payload 发到 Kafka——你完全不需要写 dispatcher。

Debezium 有专门的 Outbox Event Router SMT,配置一下就能用。

业务 → INSERT outbox → Debezium 监听 WAL → Kafka

这是分布式系统里最优雅的事务消息方案,业界最佳实践,你只需要 PG + Debezium。

13.5 触发器 + NOTIFY:超轻量 CDC

如果你的 CDC 需求只是"业务表变了通知应用刷缓存",根本不用 Debezium:

CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('table_change',
        jsonb_build_object('table', TG_TABLE_NAME, 'op', TG_OP, 'id', NEW.id)::text);
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER products_change_notify
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION notify_change();

应用 LISTEN 一下,实时收到变更通知。轻量、零依赖。

13.6 真需要 Kafka 的场景

否则,PG 逻辑复制 / Outbox 一把梭。

13.7 一段心法

现代分布式系统的核心问题是 "怎么让多个组件最终一致"。
Outbox Pattern 把这个问题简化为 "业务和事件在同一个事务里"。
而 PG 是当代数据库里最适合实现 Outbox 的——
因为 SKIP LOCKED + WAL + Debezium = 一套优雅的全栈解决方案。

14. PG 的"边界"—— 什么时候真的不该用 PG

前面 13 节我都在为 PG 站台。但这一节我要诚实——没有银弹。

每个工具都有自己的边界,到了下面的边界,该上专业方案就上。但更重要的是:理解为什么这些场景 PG 不行,这样你才能判断自己有没有真正到达边界。

14.1 极致写入吞吐(> 10w/s 持续)

典型场景:

• 大规模物联网(每秒百万条传感器数据)
• 日志洪流(全公司应用日志聚合)
• 广告点击流

为什么 PG 不行:

1. MVCC 写放大:每次 UPDATE 实际是"插入新版本 + 标记旧版本死亡",行存膨胀
2. WAL 写入是单点:所有写入串行经过 WAL,即使你有 NVMe
3. autovacuum 扛不住:高写入下 dead tuple 堆积,清理跟不上

该用:

• Kafka:消息流,扇出消费
• ClickHouse:列存,写入百万级/秒不眨眼
• ScyllaDB / Cassandra:LSM-tree,写优化
• TimescaleDB(算 PG 一族):到几百万行/秒可扛,如果在乎 SQL 兼容,先试它

迁移策略:不要 all-in,双写——核心业务在 PG,洪流数据进 ClickHouse,通过 Outbox 关联。

14.2 OLAP 大宽表实时分析

典型场景:

• 几十亿行宽表(几百列)
• BI dashboard 要 sub-second 响应
• 复杂 GROUP BY + 多维聚合

为什么 PG 不行:

1. 行存对分析不友好:即使你只查 1 列,也要读完整行
2. 没有列裁剪、向量化执行、SIMD 加速(虽然 PG 17 在改进)
3. 没有 ZoneMap / MinMax 索引(Skip 大段不相关数据)

该用:

• ClickHouse:列存王者,聚合飞快,OLAP 性能 10-100x PG
• DuckDB:单机分析,SQLite 体验,Python 友好
• Snowflake / BigQuery:云数仓,无脑用
• StarRocks / Doris:开源 OLAP,中国主流

注意 PG 也在追赶:

• TimescaleDB + 列存压缩:亿级聚合 OK
• Citus(PG 扩展):分布式 OLAP
• pg_duckdb(2024 新):在 PG 里嵌入 DuckDB 引擎做分析

判断口诀:数据量 < 10 亿,先试 TimescaleDB 列存;> 100 亿无脑 ClickHouse。

14.3 超高并发简单 KV(微秒级,> 50k QPS)

典型场景:

• 广告竞价(每次请求查特征)
• 推荐引擎在线特征
• 千万级在线 session

为什么 PG 不行:

1. 每个连接都是一个进程,fork 开销大,几千连接就吃掉所有 CPU
2. TCP/network 往返延迟就要 0.3-0.5ms,PG 加上事务管理 ≈ 1ms
3. 没有 in-memory 数据结构特化(Hash 表 / Sorted Set)

该用:

• Redis / KeyDB / DragonflyDB:微秒级,百万 QPS
• Memcached:更轻量,只 KV
• Aerospike:超低延迟,大规模
• Hazelcast / Ignite:Java 生态分布式缓存

生产策略:核心数据 PG + 热数据 Redis + 应用层缓存三层架构,各取所长。

14.4 真正的图计算

典型场景:

• PageRank、社区发现、最短路径
• 多跳深度遍历(> 5 跳)
• 知识图谱推理

为什么 PG 不行:Recursive CTE 没有图遍历优化,深度增加时性能指数下降;Apache AGE 比 Neo4j 慢几倍。

该用:

• Neo4j:图数据库标杆,Cypher 生态
• TigerGraph / Nebula:大规模分布式图
• NetworkX(单机分析):Python 内存图

判断口诀:图深度 ≤ 3 + 节点 < 1 亿,PG 够用;否则专业方案。

14.5 全文搜索的极高规模

典型场景:

• 文档量 > 1 亿,日志检索 + Kibana
• 复杂打分、聚合 facets、多语言混搜
• 实时索引 + 实时搜索(秒级)

为什么 PG 不行:GIN 索引在 1 亿级膨胀严重,延迟到秒级;ts_rank 不如 BM25 灵活。

该用:

• Elasticsearch / OpenSearch:搜索引擎标杆
• Meilisearch / Typesense:小巧的搜索
• Solr:老牌
• Quickwit:云原生,新兴

14.6 多区域强一致写入

典型场景:

• 全球多活(美国、欧洲、亚太同时写)
• 跨区域强一致(金融跨境)

为什么 PG 不行:PG 主备模式,只有一个主库可写,跨区域延迟大。

该用:

• CockroachDB:Spanner 开源版,PG 协议兼容
• YugabyteDB:PG 协议兼容,分布式
• Spanner(GCP):TrueTime,强一致
• TiDB:MySQL 协议,但理念相似

注意:CockroachDB / YugabyteDB 都是 PG 兼容协议,还是 PG 一族!切换成本极低。

14.7 一个判断口诀(精炼版)

如果一个场景的需求是 "PG 我会少 X 个数量级",而 X ≥ 2,才考虑专业方案。
否则,继续 PG。

10x 的差距?先调优 PG(配置、索引、SQL),大概率能补回来。
100x 的差距?专业方案出场。

14.8 何时该迁移?(决策框架)

最大的工程错误是"未雨绸缪"地引入复杂度。等你真的撞墙再换,那时你已经知道精确需要什么。

15. 实战路线图:一个项目从 0 到 1 的 PG 一把梭打法

15.1 心法:先做减法

很多团队画第一张架构图时,就把未来 5 年可能需要的所有组件都摆上去了。结果是:复杂度提前透支。

正确的做法是先做减法:

"如果不写一行配置,这个项目至少要几个组件才能跑起来?"
"PostgreSQL"
"够了。"

15.2 第 0 天:开局

架构图就一个框:

                  [Client]
                     │
                     ▼
                  [App x N]
                     │
                     ▼
                [PostgreSQL]

不要画 Redis,不要画 Kafka,不要画 ES,不要画 MongoDB。默认全 PG。

写在 README 第一行:

"Architecture: Single PostgreSQL. Components added only when proven necessary."

15.3 第 1 周:基础设施一次到位

1. PG 集群:单主 + 1 同步备 + 1 异步备(三节点是最小生产配置)
2. WAL 归档:archive_mode = on,归档到 S3/OSS,这是 PITR 的根基
3. PgBouncer 连接池:transaction pool 模式,默认就装上,不要等性能问题再加
4. 备份:每日 pg_basebackup + WAL 归档,定期演练恢复(演练才是真备份)
5. 监控:Prometheus + postgres_exporter + Grafana 模板,十分钟搞定

配置调优:

shared_buffers       = 内存 * 25%
effective_cache_size = 内存 * 75%
work_mem             = 32MB
maintenance_work_mem = 1GB
random_page_cost     = 1.1            # SSD
max_wal_size         = 8GB
wal_compression      = on

15.4 第 1 个月:常用扩展一次装齐

-- 必装(基础能力)
CREATE EXTENSION pg_stat_statements;  -- 慢 SQL 统计,#1 重要
CREATE EXTENSION pg_trgm;             -- 模糊匹配 + LIKE 加速
CREATE EXTENSION pgcrypto;            -- 加密、UUID
CREATE EXTENSION pg_cron;             -- 定时任务
CREATE EXTENSION btree_gist;          -- 复合 GiST 索引(范围 + 等值)

-- 强烈推荐(几乎所有项目都用得上)
CREATE EXTENSION vector;              -- 向量(AI / 推荐 / 搜索)
CREATE EXTENSION timescaledb;         -- 时序(指标/日志/任何带时间戳)

-- 按需(看业务)
-- CREATE EXTENSION postgis;          -- 地理位置
-- CREATE EXTENSION pg_jieba;         -- 中文搜索
-- CREATE EXTENSION age;              -- 图查询
-- CREATE EXTENSION pgaudit;          -- 合规审计

一次装齐,永远不要"等需要时再装"。装了不用是免费的,要用时去现装,可能踩兼容性坑。

15.5 第 1-3 个月:业务起步

按需启用各 PG 能力,优先级遵循"先用最简单的":

15.6 第 3-6 个月:业务长大

• AI / RAG 需求 → pgvector + 混合检索(§5)
• 报表 dashboard → Materialized View / Continuous Aggregate
• 跨表事件 → Outbox + dispatcher(§13)
• 管理后台 → PostgREST / Hasura(§11)
• 多租户 → RLS(§11)
• 审计合规 → trigger + history(§12)

15.7 第 6-12 个月:扩展性能

到这阶段,仍然不要换数据库,而是把 PG 用到极致:

• 读多 / OLAP:加只读副本(逻辑复制或物理复制),读写分离
• 单表大 :按时间 / hash 分区(PARTITION BY)
• 写入吞吐瓶颈:Citus(PG 扩展)分布式 sharding
• 高可用:Patroni + etcd 或上云(Neon、Supabase、Aurora、RDS)
• HTAP:TimescaleDB 列存压缩 + 普通 OLTP

15.8 第 12 个月后:精确引入专业方案

到这时候你清楚地知道:

• 哪个场景 PG 真扛不住(可能就 1-2 个)
• 这些场景精确需要什么

这才是引入新组件的正确时机——基于真实瓶颈,而不是想象。

举例:

• 真的有"日志洪流",不可避免 → 上 ClickHouse,只用于日志分析,不动业务库
• 真的需要 GraphQL Subscription 推流给前端 → 上 Hasura,只做实时层,不替代后端
• 真的需要图算法 → 上 Neo4j,只做图分析,不存核心数据

始终保持 PG 是"中心",其他组件是"配角"。

15.9 反直觉的事实

90% 的中型公司,一辈子触碰不到 PG 的天花板。

公开案例:

• Instagram 早期:单 PG 实例撑到 1 亿用户
• Notion:核心 PG,百万付费用户
• GitLab:全公司 PG-only,百万企业用户
• Discord:从 PG 起步,千万 DAU 才换部分服务到 ScyllaDB
• Figma:实时协作 + PG,撑到独角兽
• Stack Overflow:SQL Server,亿级 PV(SQL Server 是堂兄,逻辑相同)

你的项目大概率永远不会到这些公司的规模。

15.10 一段心法

工程的最大美德不是"为未来准备",
而是"为当下解决",并保留演化的可能。
PG 是这两点的最佳载体——它当下足够强,演化路径足够清晰。

16. 必装扩展清单 & 配置心法

16.1 必装扩展全表

16.2 配置心法(10 条军规,生产级)

1. shared_buffers = 内存 * 25%-40%

默认 128MB 是 PG 性能问题的 #1 根源。32GB 内存机器,至少配 8GB。

2. effective_cache_size = 内存 * 50%-75%

告诉规划器 OS 大概有多少 cache,不实际占用内存,影响查询计划选择。

3. work_mem 别设太大

这是每个连接每个排序/哈希操作的内存。100 连接 × 5 个排序 × work_mem=256MB = 125GB,会 OOM。

经验值:work_mem = (总内存 - shared_buffers) / max_connections / 4,通常 32-64MB。

4. maintenance_work_mem = 1GB+

VACUUM、CREATE INDEX、REINDEX 用。建索引慢的 #1 原因就是这个值太小。

5. max_connections 不要随便调大

每个连接 = 一个进程,fork + 内存开销大。永远配 PgBouncer,把上层几百连接复用成 PG 后端 30-50 个。

6. autovacuum 永远开着,且要激进

队列表 / 高更新表单独配置:

ALTER TABLE jobs SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 2000
);

7. WAL 归档必开

archive_mode = on
archive_command = 'aws s3 cp %p s3://bucket/wal/%f'

没开 archive 就没法 PITR(时间点恢复),丢数据风险极高。

8. log_min_duration_statement = 1s

超过 1 秒的 SQL 自动进 PG log。生产监控必开。

9. 三种"高级索引"必须会

-- partial index:只索引一部分行
CREATE INDEX ON jobs (priority DESC) WHERE status='pending';

-- 表达式索引:索引计算结果
CREATE INDEX ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com';  -- 走索引

-- 覆盖索引(INCLUDE):避免回表
CREATE INDEX ON orders (user_id) INCLUDE (status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 42;  -- 不需要回表

这三个能解决 80% 性能问题,但 80% 的人不会用。

10. EXPLAIN (ANALYZE, BUFFERS) 是你最好的朋友

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ... ;

看实际执行计划、buffer 命中率、行数估算误差。慢 SQL 第一步永远是 EXPLAIN ANALYZE。

16.3 监控必看的 SQL(收藏)

-- 1. 慢 SQL Top 20(总耗时排序)
SELECT round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       calls,
       substring(query, 1, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- 2. 缓存命中率(全库,应 > 99%)
SELECT round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0), 2) AS hit_pct
FROM pg_statio_user_tables;

-- 3. 表膨胀(找 dead tuple 多的表)
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC LIMIT 20;

-- 4. 没用过的索引(可以删)
SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 5. 索引大小排行
SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;

-- 6. 表大小排行
SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

-- 7. 当前活跃 SQL(找堵塞)
SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS duration,
       substring(query, 1, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- 8. 锁等待链(找谁堵住了谁)
SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 9. 复制延迟(主从)
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS sent_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;

-- 10. 复制槽 lag(逻辑复制必看!)
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

把这 10 条做成 Grafana / Metabase dashboard,每天看一眼,出问题第一时间发现。

16.4 推荐工具链

收尾:一个工程哲学

文章很长(2 万字往上),但其实可以浓缩成一句话:

复杂度是可以累积的,简单度也是可以累积的。

你每多上一个组件,就给未来的自己埋一个雷;
你每少上一个组件,就给未来的自己留一份自由。

这十几年,数据库行业走了一个圈

2010 年代是 NoSQL 的黄金年代:Mongo、Cassandra、Redis、ES、Neo4j、InfluxDB……人人都说"关系数据库不行了"。

但十年过去,我们看到了另一面:

• Mongo 加事务、加 schema validation、改 SQL-like —— 像在变成 PG
• ES 加 SQL 接口 —— 像在变成 PG
• Cassandra 加 ACID Light、加二级索引 —— 像在变成 PG
• InfluxDB 3.0 改回 SQL —— 像在变成 PG
• Snowflake、Redshift、BigQuery —— 本来就是 PG 兼容族
• CockroachDB、YugabyteDB、Neon、Supabase —— PG 协议直接抄

所有专业方案都在朝 PG 靠拢,而 PG 本身也在吸收所有专业方案的能力(JSONB / 向量 / 时序 / 地理 / Cypher……)。

这是一场关于"简洁永胜"的胜利。

PG 的护城河,是 30 年的耐心

PG 这个东西,1996 年首次发布,到现在快 30 年了。

它无聊地稳定运行,无聊地一年发一个大版本,无聊地把别人吹的"颠覆性新方案"一个个吸纳成扩展。

它没有融资,没有 IPO,没有市场部。但它一直在那里。

这种 "Boring Technology"(无聊技术)才是工程的终极美学。Dan McKinley 那篇神文写过:

"Choose boring technology.
Innovate where it matters: in your product.
Use boring for the rest."

你的"创新预算"是有限的,花在核心业务上,基础设施越无聊越好。

PG 是你能找到的最无聊的、也最强大的那块基础设施。

给你一个 30 秒的工程仪式

下次画架构图前,强迫自己做这个仪式:

1. 画一个框,写上 "PostgreSQL"。
2. 盯着这个框看 30 秒。
3. 问自己:"这个框,真的不够用吗?"
4. 如果答案是"够用",那就什么都别加。

90% 的情况,这个仪式之后,你的架构图就停在第 1 步了。

而第 1 步的架构,永远是最稳的、最便宜的、最易维护的、最让你能下班的架构。

推荐阅读

流派宣言

• Just Use Postgres for Everything — 这个流派的"宣言",必读
• Postgres is Enough — GitHub 上的经典清单,被反复引用
• Choose Boring Technology — Dan McKinley 的经典演讲
• The Part of PostgreSQL We Hate the Most — CMU Pavlo 教授,客观看待 PG 缺点(MVCC 设计的历史包袱)

真实案例

• 37signals(DHH)关于删 Redis、删 ES 的几篇博客
• Notion Engineering Blog,关于 PG sharding
• GitLab 的 PG 优化文章

进阶必读

• pgvector 作者 Andrew Kane 的设计博客
• TimescaleDB 文档,特别是 hypertable 的设计哲学
• Use The Index, Luke! —— 索引调优圣经

项目源码(学最好的工程)

• pg-boss — 学 PG 队列
• Oban — 学 Elixir 风格的 PG 队列
• PostgREST — 学怎么把 PG 变 API
• Supabase — 学怎么做 PG 全栈

写这篇文章的初心

看到太多团队架构图越画越复杂,运维越加越多,业务却越走越慢。

写这篇文章,是想让一些人在画下一张架构图之前,多停 30 秒,问一下自己:

"PG 不行吗?"

大概率,是行的。

而且,它一直行。

如果你看到这里,说明你真的把这篇文章读完了。
我相信 PG 的这条"少即是多"的路,值得这两万字的篇幅。

接下来要做的,就是从你下一个项目开始,把这张图画简单。

祝你的系统稳如磐石,祝你的运维不再失眠。