📐 数据流总览

客户端 → FastAPI 业务层 → psycopg3 连接池 → PostgreSQL 内核 & 扩展

flowchart LR classDef cli fill:#1e3a8a,stroke:#60a5fa,color:#fff classDef api fill:#4c1d95,stroke:#a78bfa,color:#fff classDef pg fill:#064e3b,stroke:#34d399,color:#fff classDef ext fill:#7c2d12,stroke:#fbbf24,color:#fff U[用户]:::cli -->|HTTP| API[FastAPI 网关]:::api API --> Auth[auth.signup/login] API --> Post[posts.publish] API --> Full[search fulltext] API --> Trgm[search trgm] API --> Vec[search vector] API --> Tree[comments.tree] API --> Hot[hot.topN] subgraph PG[PostgreSQL 内核] direction TB U0[(users)]:::pg P0[(posts 分区表)]:::pg C0[(comments)]:::pg E0[(post_embeddings)]:::pg L0[(post_likes)]:::pg MV[[mv_hot_posts 物化视图]]:::pg end Auth --> U0 Post --> P0 Full --> P0 Trgm --> P0 Vec --> E0 Tree --> C0 Hot --> MV subgraph EXT[扩展生态] direction TB X1[pgcrypto]:::ext X2[pg_trgm]:::ext X3[pgvector HNSW]:::ext X4[pg_stat_statements]:::ext end Auth -.使用.-> X1 Trgm -.使用.-> X2 Vec -.使用.-> X3 API -.审计.-> X4

🧩 项目模块卡片

每个模块对应前 18 章的一组知识点。鼠标悬浮卡片看高亮。

🔐认证模块

bcrypt 哈希 + 常数时间比较
pgcryptoCh13 权限Ch12 函数 
INSERT INTO users(..., password)
VALUES (..., crypt(pwd, gen_salt('bf', 10)));

-- 校验
SELECT id FROM users
WHERE password = crypt(pwd, password);

📝发文模块

JSONB 标签 + 触发器自动维护 search_vector
JSONBTriggerCh3 / Ch12 
INSERT INTO posts(..., tags) VALUES
  (..., '["postgres","pgvector"]'::jsonb);

-- BEFORE INSERT 触发器自动算出：
search_vector := to_tsvector('simple', title || body);

🔍全文检索

tsvector + GIN + ts_rank 相关度排序
tsvectorGINCh6 索引 
SELECT id, title,
       ts_rank(search_vector, q) AS rank
FROM   posts,
       websearch_to_tsquery('simple', $1) q
WHERE  search_vector @@ q
ORDER  BY rank DESC
LIMIT  10;

🔤模糊检索

pg_trgm：拼错也能查到
pg_trgmsimilarityCh18 扩展 
SELECT id, title,
       similarity(title, $1) AS sim
FROM   posts
WHERE  title % $1              -- 相似度 > 0.3
ORDER  BY title <-> $1            -- 距离升序
LIMIT  10;

🤖语义检索

pgvector + HNSW + 余弦距离
pgvectorHNSWembedding 
-- 384 维 MiniLM-L6 向量
SELECT p.id, p.title,
       e.embedding <=> $1::vector AS dist
FROM   post_embeddings e
JOIN   posts p ON p.id = e.post_id
ORDER  BY e.embedding <=> $1::vector
LIMIT  5;

🌳评论树

递归 CTE 一次 SQL 查完
WITH RECURSIVECh5 高级查询 
WITH RECURSIVE tree AS (
  SELECT *, 1 AS depth,
         ARRAY[id] AS path
  FROM   comments
  WHERE  post_id = $1 AND parent_id IS NULL
  UNION ALL
  SELECT c.*, t.depth+1, t.path || c.id
  FROM   comments c JOIN tree t
         ON c.parent_id = t.id)
SELECT * FROM tree ORDER  BY path;

🏷️按标签筛选

JSONB GIN(jsonb_path_ops) 索引
JSONB @>jsonb_path_opsCh6 
SELECT id, title
FROM   posts
WHERE  tenant_id = 1
  AND  tags @> '["postgres"]'::jsonb
ORDER  BY created_at DESC;

-- Bitmap Index Scan on idx_posts_tags

🔥热门榜

物化视图每小时刷新
MATERIALIZED VIEWpg_cron 
CREATE MATERIALIZED VIEW mv_hot_posts AS
SELECT p.id, p.title,
       count(l.*) FILTER (
           WHERE l.created_at > now() - INTERVAL '7 days'
       ) AS recent_likes
FROM   posts p LEFT JOIN post_likes l ...;

-- 每小时：
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_hot_posts;

📅月度分区

RANGE 分区 + 分区裁剪
PARTITIONCh16 
CREATE TABLE posts (...)
PARTITION BY RANGE (created_at);

CREATE TABLE posts_2025_01 PARTITION OF posts
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- 冷数据归档：
ALTER TABLE posts DETACH PARTITION posts_2023_01;

🛡️多租户 RLS

一张表服务多个租户、策略自动注入 WHERE
ROW LEVEL SECURITYCh13 
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY sel_tenant ON posts
  USING (tenant_id =
    current_setting('app.tenant_id')::bigint);

-- 业务连接启动时：
SELECT set_config('app.tenant_id', '1', false);

🩺慢查询审计

pg_stat_statements + auto_explain
pg_stat_statementsCh17 
SELECT substring(query, 1, 60) AS q,
       calls,
       round(total_exec_time::numeric, 2)
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

⚡索引选型

一表 5 索引，各司其职
B-TreeGINHNSW 
CREATE INDEX ON posts USING GIN(search_vector);
CREATE INDEX ON posts USING GIN(tags jsonb_path_ops);
CREATE INDEX ON posts USING GIN(title gin_trgm_ops);
CREATE INDEX ON posts(author_id);
CREATE INDEX ON posts(tenant_id, status);

CREATE INDEX ON post_embeddings
  USING hnsw(embedding vector_cosine_ops);

🔎 三种检索对比演示

输入一个关键词，直观感受 全文检索 / 模糊检索 / 语义检索 的差异。
演示用前端的 mock 数据库，不连后端，重点是概念区别。

试一下：

全文检索 · tsvector + GIN

只匹配词项（token）精确包含，按 ts_rank 排序
SELECT * FROM posts
WHERE  search_vector @@ to_tsquery($1)
ORDER  BY ts_rank(search_vector, ...)

    模糊检索 · pg_trgm

    按 3-gram 相似度，能容忍拼写错误
    SELECT * FROM posts
WHERE  title % $1
ORDER  BY title <-> $1

      语义检索 · pgvector

      按向量余弦距离，理解「意思」而不是字面
      SELECT * FROM post_embeddings
ORDER  BY embedding <=> $1::vector
LIMIT  5
        全文：精确词项 模糊：字符相似 语义：含义相近 快捷键 Enter 触发搜索
        第 19 章 综合实战项目 · HTML 演示页 · README | 主文档
        项目代码位于 /data/workspace/learnNote/postgre/19_project/