learnNote

主题

第 19 章 综合实战项目 · 博客 + 全文检索 + 标签 + 评论树 + AI 语义检索 ​

一个把前 18 章 PG 知识全部串起来的真实项目：

模块用到的 PG 能力代码文件
注册登录pgcrypto bcrypt 哈希code/auth.py
发文/标签JSONB + GIN(jsonb_path_ops) + 触发器code/posts.py
全文检索tsvector + to_tsvector + GIN + ts_rankcode/search_fulltext.py
模糊检索pg_trgm 的 %/<->/similaritycode/search_trgm.py
语义检索pgvector + HNSW 索引 + <=> 余弦距离code/search_vector.py
评论树WITH RECURSIVE 递归 CTEcode/comments_tree.py
热门榜MATERIALIZED VIEW + REFRESH CONCURRENTLYcode/refresh_hot.py
月度归档声明式 PARTITION BY RANGEinit.sql
多租户ROW LEVEL SECURITY + current_settingcode/multi_tenant_demo.py
慢查询pg_stat_statements + EXPLAIN ANALYZEcode/perf_audit.py

快速启动（三步上手） ​

方式 A：docker-compose（推荐） ​

bash
cd /data/workspace/learnNote/postgre/19_project

# 1) 一键启动带 pgvector + pg_stat_statements 的 PG 16
docker compose up -d

# 容器 entrypoint 会自动执行 init.sql → seed.sql，等待 ~10s
docker compose logs -f pg | grep -m1 'database system is ready'

# 2) 安装 Python 依赖
pip install -r requirements.txt

# 3) 跑起来
python code/auth.py                 # 注册 + 登录
python code/posts.py                # 发文 + 标签
python code/search_fulltext.py "postgres MVCC"
python code/search_trgm.py "psotgres"         # 故意拼错
python code/search_vector.py "如何向量检索"
python code/comments_tree.py 1
python code/refresh_hot.py
python code/multi_tenant_demo.py
python code/perf_audit.py

# 或者一次性启 API
python code/api.py                  # → http://localhost:8000/docs

方式 B：本地已有 PG ​

bash
# 确保已装扩展：pgcrypto / pg_trgm / vector / pg_stat_statements
# 并在 postgresql.conf 里：shared_preload_libraries = 'pg_stat_statements'

psql -h 127.0.0.1 -U postgres -d learn_pg -f init.sql
psql -h 127.0.0.1 -U postgres -d learn_pg -f seed.sql

# 改 DSN（默认连 127.0.0.1:5432/learn_pg）
export PG_DSN="host=127.0.0.1 port=5432 dbname=learn_pg user=postgres password=你的密码"

pip install -r requirements.txt
python code/api.py

目录结构 ​

19_project/
├── README.md
├── docker-compose.yml        # pgvector 镜像 + pg_stat_statements
├── init.sql                  # 建表 / 索引 / 触发器 / 物化视图 / RLS
├── seed.sql                  # 10 用户 / 50 文章 / 500+ 评论 / 20 标签
├── demo.html                 # 项目架构看板 + 三种检索对比
├── requirements.txt
└── code/
    ├── db.py                 # psycopg v3 连接池
    ├── auth.py               # 注册 / 登录（pgcrypto）
    ├── posts.py              # 发文 / 标签
    ├── search_fulltext.py    # 全文检索
    ├── search_trgm.py        # 模糊检索
    ├── search_vector.py      # 语义检索（需 sentence-transformers）
    ├── comments_tree.py      # 评论树 递归 CTE
    ├── refresh_hot.py        # 物化视图刷新 + Top N
    ├── multi_tenant_demo.py  # RLS 多租户验证
    ├── perf_audit.py         # pg_stat_statements 慢 SQL Top N
    └── api.py                # FastAPI REST（可选）

常见问题 ​

Q1：CREATE EXTENSION vector 报错？ A：请用 pgvector/pgvector:pg16 镜像；自建 PG 需要先 apt install postgresql-16-pgvector 或 make install 源码。

Q2：pg_stat_statements 查不到？ A：必须 ①在 postgresql.conf 里 shared_preload_libraries = 'pg_stat_statements'；②重启 PG；③CREATE EXTENSION pg_stat_statements;。docker-compose 里已通过 command -c 注入。

Q3：search_vector.py 报找不到 sentence-transformers？ A：可选依赖，未装时会退化为随机向量（只演示流程，结果不准）。pip install sentence-transformers 即可。首次运行会下载 ~90MB 的模型文件。

Q4：RLS 演示无法连接 blog_app？ A：默认 docker 镜像 pg_hba.conf 对 host 默认是 scram 密码登录，init.sql 已 CREATE ROLE blog_app LOGIN PASSWORD 'blog_app_pwd'。如果在容器外连，用 host=127.0.0.1 + TCP 即可。

Q5：docker compose up 后一直报 could not connect？ A：healthcheck 需要 10 秒左右初始化，看 docker compose logs pg 里出现 database system is ready to accept connections 再跑 Python。

清理 ​

bash
docker compose down -v       # 连同数据卷一起删