主题
03-02 · 数据库 · PostgreSQL
PostgreSQL 是公司默认主数据库。 它在事务、JSON、向量（pgvector）、地理（PostGIS）、并发上的综合能力是开源生态最强。我们的所有业务数据、Agent 中间状态、向量检索都跑在 PG 上。
一、为什么是 PostgreSQL
| 候选 | 评价 |
|---|---|
| PostgreSQL（推荐） | 事务、JSON、向量、地理、并发都顶级；社区与云厂商支持最好 |
| MySQL | 事务一般、JSON 弱、扩展性差，新项目禁用 |
| SQLite | 适合 dev / 测试 / Vault 工具，不做生产 |
| MongoDB | 文档型；除非业务真是 schema-less，否则 PG JSONB 已经够 |
| ClickHouse | 适合分析型场景，做 OLAP 时考虑（与 PG 配合） |
公司硬约定：新项目主库一律 PostgreSQL 16+。
二、安装与本地运行
公司不要求你直接装在系统里。首选 Docker Compose，省心、可丢弃、版本可控。
2.1 Docker Compose（推荐）
docker-compose.yml：
yaml
services:
db:
image: postgres:16-alpine
container_name: qdy-pg
restart: unless-stopped
environment:
POSTGRES_USER: qdy
POSTGRES_PASSWORD: qdy
POSTGRES_DB: qdy_dev
TZ: Asia/Shanghai
PGTZ: Asia/Shanghai
ports:
- "5432:5432"
volumes:
- pg-data:/var/lib/postgresql/data
- ./init-sql:/docker-entrypoint-initdb.d:ro
healthcheck:
test: ["CMD", "pg_isready", "-U", "qdy", "-d", "qdy_dev"]
interval: 5s
timeout: 3s
retries: 10
redis:
image: redis:7-alpine
container_name: qdy-redis
restart: unless-stopped
ports:
- "6379:6379"
volumes:
- redis-data:/data
volumes:
pg-data:
redis-data:启动：
bash
docker compose up -d
docker compose ps # 应当看到 healthy2.2 直接本机安装（可选）
| 系统 | 命令 |
|---|---|
| macOS | brew install postgresql@16 && brew services start postgresql@16 |
| Ubuntu/Debian | sudo apt install -y postgresql-16 |
| Windows | 官网安装包 https://www.postgresql.org/download/windows/ |
生产数据库永远跑云上托管（阿里云 RDS / 腾讯云 PG / Supabase / Neon / Railway）。本机只跑开发库。
三、连接验证
bash
# 装 psql 客户端（如还没装）
sudo apt install -y postgresql-client # Linux
brew install libpq && brew link --force libpq # macOS
# 连入开发库
psql "postgresql://qdy:qdy@localhost:5432/qdy_dev"
# 在 psql 里
\dt -- 列表
\d leads -- 表结构
\q -- 退出或用 GUI（公司推荐）：
- TablePlus（macOS / Windows，免费版够用）
- Beekeeper Studio（开源）
- DBeaver（开源，功能最全）
- Postico（macOS only，UX 最好）
四、连接池与异步驱动（与 FastAPI 协同）
后端文档已经给出 SQLAlchemy 2.x async + asyncpg 的写法（参见 01-后端-FastAPI.md 第六节）。这里补充几个 公司级最佳实践：
| 场景 | 公司默认 |
|---|---|
| 异步驱动 | asyncpg |
| 同步驱动（仅 Alembic） | psycopg（v3）或 psycopg2-binary |
| 连接池大小 | pool_size=10, max_overflow=20（小服务） / 按 RPS 调高 |
| 连接预检 | pool_pre_ping=True（必开） |
| 连接回收 | pool_recycle=1800（30 min） |
| 长事务监控 | 数据库侧加 idle_in_transaction_session_timeout=60s |
五、必装扩展
进开发库后执行（一次性）：
sql
-- 模糊搜索（中文场景能用，但生产建议 ES）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- UUID 工具
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- JSONB 路径加速
CREATE EXTENSION IF NOT EXISTS btree_gin;
-- 向量检索（如果要做 RAG / 语义搜索）
CREATE EXTENSION IF NOT EXISTS vector;pgvector：让 PG 直接做向量检索，省掉单独跑 Pinecone / Milvus 的成本。我们的 obsidian-rag、leads 语义匹配都用它。
启用 pgvector 后，建表示例：
sql
CREATE TABLE doc_embeddings (
id BIGSERIAL PRIMARY KEY,
doc_id BIGINT NOT NULL,
embedding vector(1536), -- OpenAI text-embedding-3-small 维度
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX ON doc_embeddings
USING hnsw (embedding vector_cosine_ops);六、数据库迁移规范（Alembic）
公司硬约定：任何 schema 变更必须 PR + 迁移脚本。直接
ALTER TABLE是 fired offense。
6.1 工作流程
bash
# 1. 改 ORM 模型
# 2. 自动生成 migration
uv run alembic revision --autogenerate -m "add lead.industry"
# 3. ★ 必须人工 review 生成的 migration ★
# Autogenerate 不是 100% 准的，尤其 enum / index / 外键
# 4. 在本地应用
uv run alembic upgrade head
# 5. 跑测试 → 提 PR6.2 命名约定
- migration message：英文动宾，
add_lead_industry/drop_users_phone_index - 数据迁移与 schema 迁移 分开两个 revision：先改结构，再迁数据
- 不可逆操作（drop 列）必须分两次 release：先停写、下次再 drop
6.3 生产部署
yaml
# 上线前 (CI / CD)
steps:
- alembic upgrade head # 先迁移
- rolling restart api pods # 再起新版七、备份与恢复
7.1 开发库（Docker volume）
bash
# 备份
docker exec qdy-pg pg_dump -U qdy qdy_dev > backup.sql
# 恢复
cat backup.sql | docker exec -i qdy-pg psql -U qdy qdy_dev7.2 生产库
由云厂商 RDS 自动每日全备 + WAL 增量。我们只需：
- 配置好备份保留时间（公司标准：30 天）
- 每月做一次"恢复演练"（在 staging 拉一份生产备份恢复，验证可用）
八、性能调优 5 条铁律
- 永远写 EXPLAIN ANALYZE：怀疑哪条 SQL 慢，先 explain，不要靠猜
- 慢查询日志开起来：
log_min_duration_statement = 200，超 200ms 全记 - 索引不是越多越好：写多读少的表少建索引
- JSONB 路径要建 GIN 索引：
CREATE INDEX ON t USING GIN (jsonb_col jsonb_path_ops) - 谨慎用
SELECT *：尤其涉及大 TEXT / JSONB 列
九、AI 协同：把 PG 接成 MCP
9.1 给 Claude Code 挂只读 MCP
bash
# 安全做法：用只读账号
psql -U qdy -d qdy_dev -c "CREATE USER claude_ro WITH PASSWORD '<readonly_pass>';"
psql -U qdy -d qdy_dev -c "GRANT CONNECT ON DATABASE qdy_dev TO claude_ro;"
psql -U qdy -d qdy_dev -c "GRANT USAGE ON SCHEMA public TO claude_ro;"
psql -U qdy -d qdy_dev -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO claude_ro;"
# 挂 MCP（Claude Code）
claude mcp add postgres-dev -s user -- \
npx -y @modelcontextprotocol/server-postgres \
"postgresql://claude_ro:<readonly_pass>@localhost:5432/qdy_dev"之后在 Claude Code 里：
> 用 postgres-dev 工具帮我看一下 leads 表里 industry 分布9.2 公司硬约定（重要）
| 行为 | 是否允许 |
|---|---|
| 给 AI 挂 生产读写 MCP | ❌ 严禁 |
| 给 AI 挂 生产只读 MCP（且限定 schema） | ⚠️ 需要 Lead 审批 |
| 给 AI 挂 开发库只读 MCP | ✅ 推荐 |
| 给 AI 挂 本机沙盒库 | ✅ 自由使用 |
任何"AI 直接 DROP 了一张表"的故事，都从"给生产挂了写 MCP"开始。别成为故事的主角。
十、未来股东 · 第一周必练
- [ ] 用 Docker Compose 起一个本地 PG，能用 psql / GUI 连入
- [ ] 装好 pgvector，能创建一张带
vector(1536)列的表 - [ ] 跑通 Alembic：建表、改字段、回滚一格
- [ ] 给本机开发库挂只读 MCP，让 Claude Code 能读
- [ ] 至少跑一次
EXPLAIN ANALYZE，看一条查询的执行计划