知识广场

开发期想要一个随手起、不污染主机、带管理界面的 PostgreSQL。 Compose 是最低摩擦方案。 ## 目录结构 ``` ~/pg-stack/ ├── docker-compose.yml ├── .env ├── init/ │ └── 01-init.sql └── data/ # 卷挂载点（git 忽略） ``` ## docker-compose.yml ```yaml services: db: image: postgres:16-alpine restart: unless-stopped environment: POSTGRES_USER: ${PG_USER} POSTGRES_PASSWORD: ${PG_PASS} POSTGRES_DB: ${PG_DB} # 让 init/*.sql 在首次启动时跑 PGDATA: /var/lib/postgresql/data/pgdata volumes: - ./data:/var/lib/postgresql/data - ./init:/docker-entrypoint-initdb.d:ro ports: - "127.0.0.1:5432:5432" # 只在 localhost 暴露 healthcheck: test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER -d $$POSTGRES_DB"] interval: 10s timeout: 3s retries: 5 pgadmin: image: dpage/pgadmin4:latest restart: unless-stopped environment: PGADMIN_DEFAULT_EMAIL: ${PGADMIN_EMAIL} PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASS} PGADMIN_LISTEN_PORT: 80 ports: - "127.0.0.1:5050:80" depends_on: db: condition: service_healthy volumes: - pgadmin-data:/var/lib/pgadmin volumes: pgadmin-data: ``` ## .env（不要进 git） ``` PG_USER=appuser PG_PASS=change-me-some-long-random-string PG_DB=appdb PGADMIN_EMAIL=admin@local PGADMIN_PASS=change-me-too ``` ## init/01-init.sql ```sql -- 启用常用扩展 CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- 只读分析账号 CREATE USER analytics WITH PASSWORD 'analytics-pass'; GRANT CONNECT ON DATABASE appdb TO analytics; GRANT USAGE ON SCHEMA public TO analytics; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics; ``` ## 启动 ```bash docker compose up -d docker compose ps docker compose logs -f db # 看初始化 ``` ## 校验 ```bash # 通过 psql 客户端连 docker compose exec db psql -U appuser -d appdb -c '\dx' # 或者本地 psql psql -h 127.0.0.1 -U appuser -d appdb ``` pgAdmin 打开 `http://localhost:5050`，登录用 `.env` 里的邮箱密码。 进去后 "Add new server"： - Host: `db`（容器名，pgAdmin 在同一 Compose 网络） - Port: 5432 - User/Password: 同 `.env` ## 备份 / 恢复 ```bash # 备份 docker compose exec -T db pg_dump -U appuser -d appdb -Fc > backup-$(date +%F).dump # 恢复（连接已存在的数据库会失败，先 drop） docker compose exec -T db psql -U postgres -c 'DROP DATABASE appdb;' docker compose exec -T db psql -U postgres -c 'CREATE DATABASE appdb OWNER appuser;' docker compose exec -T db pg_restore -U appuser -d appdb < backup-2026-05-20.dump ``` ## 升级 Postgres 大版本 主版本（如 16 → 17）跨越时 **数据文件不兼容**，需要 dump/restore： ```bash docker compose exec -T db pg_dumpall -U postgres > all.sql # 改 image: postgres:17-alpine # 清空 ./data 重启 docker compose down rm -rf ./data docker compose up -d docker compose exec -T db psql -U postgres < all.sql ``` ## 踩过的坑 - 端口写成 `5432:5432` 而不是 `127.0.0.1:5432:5432`：整个公网都能连到 你的数据库，扫描器十分钟内就来撞密码。一定要绑 127.0.0.1。 - `./data` 不要用 NFS 挂载点，PostgreSQL 对 `fsync` 行为要求严格， NFS 上跑会丢数据。 - `init/` 只在 **数据目录为空** 时执行；如果你后来改了 init.sql 想重跑， 得先 `docker compose down -v` 清掉。 - Alpine 镜像比 Debian 小但 glibc 差异偶尔翻车（特别是某些 extension）， 生产用 `postgres:16-bookworm` 更稳。

## 起因 PostgreSQL 每个 client connection 是一个进程，几 MB RAM。 应用每 worker 一个 connection × 几十 worker × 几个服务 → 几千连接 → PG 默认 `max_connections=100` 撑不住，加到 1000 也吃几 GB RAM。 **PgBouncer** 在应用和 PG 之间做 connection pool： ``` [app worker × 100] ←──── 100 cheap connection ────→ [PgBouncer] ↓ [10 actual PG conn] ↓ [Postgres] ``` PG 真正打开 10 connection 即可，PgBouncer 复用给 100 个 app worker。 ## pool_mode 三选 ```ini # pgbouncer.ini pool_mode = session | transaction | statement ``` ### session mode client 一直占有 PG conn 直到断开。 跟没 pgbouncer 几乎一样（只是 connection 数仍上限）。 基本无意义，跳过。 ### transaction mode（默认 + 最常用） client 一个 transaction 内独占一个 PG conn，transaction 结束归还。 ``` client A: BEGIN → 拿 conn1 client A: SELECT client A: UPDATE client A: COMMIT → 还 conn1 client B: BEGIN → 拿 conn1 ... ``` 效率高，但有限制： - ❌ session 状态（SET、prepared statement、cursor 等）不跨 transaction - ❌ LISTEN/NOTIFY（pub/sub） - ❌ 临时表（声明周期是 session） ### statement mode 每个 SQL 语句独立 conn。 最高复用，但禁止 transaction（不能 BEGIN/COMMIT）。 极少用，特殊只读 / 简单 OLTP 场景。 ## 典型配置 ```ini # pgbouncer.ini [databases] mydb = host=pg.example.com port=5432 dbname=mydb pool_size=20 [pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction default_pool_size = 20 # 每 db × user 组合 20 个 PG conn max_client_conn = 1000 # 接受最多 1000 个 client conn reserve_pool_size = 5 server_idle_timeout = 600 # 重要：让 prepared statement 在 transaction mode 工作 max_prepared_statements = 100 # pgbouncer 1.21+ ``` ## userlist.txt ``` "appuser" "SCRAM-SHA-256$4096:..." ``` `scram-sha-256` hash，从 PG 查： ```sql SELECT rolname, rolpassword FROM pg_authid; ``` ## 客户端连接 应用连 pgbouncer 地址，不是 PG： ```python # Django settings DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql', 'HOST': 'pgbouncer.example.com', 'PORT': 6432, 'NAME': 'mydb', 'USER': 'appuser', 'PASSWORD': '...', 'CONN_MAX_AGE': 0, # 不用应用层 connection pool（让 pgbouncer 管） } } ``` `CONN_MAX_AGE=0` 是关键：让 Django 每请求重新拿 conn（实际是从 pgbouncer 池）。 `CONN_MAX_AGE > 0` 会让 app worker 长期持 connection → pgbouncer 无用。 ## Django / Rails 与 transaction mode 的坑 ### prepared statement ```python # psycopg2 默认 prepared statement queryset = MyModel.objects.filter(x=1) ``` psycopg2 用 server-side prepared statement 提速。 但 transaction mode 跨 transaction conn 切换 → prepared statement 在 另一 conn 没准备 → 报错。 解决： **option A**：禁用 prepared statement ```python # Django + psycopg2 DATABASES['default']['OPTIONS'] = { 'options': '-c default_transaction_isolation=read_committed', } # psycopg3： DATABASES['default']['OPTIONS'] = { 'prepare_threshold': None, } ``` **option B**：pgbouncer 1.21+ 支持 prepared statement caching： ```ini max_prepared_statements = 100 ``` 我建议 option B（pgbouncer 新版本 + 不改应用）。 ### SET / 临时表 / cursor 业务代码避免： ```python # bad（transaction mode 不持久） cursor.execute("SET application_name = 'myapp'") # 下个 query 可能换 conn → SET 丢 # bad cursor.execute("CREATE TEMP TABLE ...") # 临时表跨 transaction 没了 # bad LISTEN / NOTIFY → 直接不能用 ``` 需要 SET / 临时表 → 包在同 transaction 内，或者直连 PG 不走 pgbouncer。 ## pool_size 怎么定 经验： - PG 总 `max_connections`：保留给所有 client 之和 - pgbouncer `default_pool_size`：少于 PG max_connections / N（N = client app 数） 例：PG max_connections=100，2 app server，每 app 配 30 pool_size → 2×30=60 < 100，留 40 给 admin / monitoring。 pool_size 太小 → app 等 conn timeout。 pool_size 太大 → PG 进程多 RAM 涨。 经验值：CPU 核数 × 2-4。32 vCPU PG → pool_size 64-128 / app。 ## 监控 `SHOW POOLS;`（连 pgbouncer 跑）： ``` database | user | cl_active | cl_waiting | sv_active | sv_idle mydb | appuser | 50 | 0 | 18 | 2 ``` - `cl_waiting > 0` → 客户端在等 conn，pool_size 不够 - `sv_active / pool_size 接近 1` → 长期满负载 prometheus pgbouncer_exporter 抓 → Grafana panel。 ## 透明部署 vs sidecar 部署方式： 1. **每 app server 一个 pgbouncer**：sidecar，本地连 2. **集中 pgbouncer cluster**：多 app 共用 3. **PG container 内嵌** 我倾向 1（sidecar）： - app 连 localhost:6432 几乎 0 网络开销 - pgbouncer 进程独立 crash 不影响 PG - 配置分散但简单 ## 与 pgcat / odyssey 对比 - **pgcat**（Rust）：现代 PG pooler，原生支持 read replica 路由 + sharding - **odyssey**（Yandex）：高性能 multi-threaded pooler - **pgbouncer**：单线程 C，事实标准，稳 90% 场景 pgbouncer 够。需要 read replica 路由 / sharding → pgcat。 ## 实战：解决 too many connections 我们一个 Django app + Celery + 30 worker × 4 instance = 120 conn 需求。 PG max_connections=200 撑得住但内存吃紧。 加 pgbouncer： ```ini pool_size = 25 max_client_conn = 200 pool_mode = transaction ``` 实际 PG conn 数：25 × 1（pgbouncer）= 25。 app 仍能并发 200 个 logical conn（pgbouncer 排队复用）。 PG RAM 从 8 GB → 2 GB。预算节省。 ## 踩过的坑 1. **CONN_MAX_AGE > 0**：以为加 pgbouncer 就万事大吉，结果 app 仍持 长 conn → pgbouncer 无效。改 0。 2. **scram-sha-256 vs md5**：PG 14+ 默认 scram，pgbouncer 1.20+ 才好 支持。老版本配 md5。 3. **prepared statement 误报**：奇怪的 `prepared statement "_pg_q1" does not exist` → transaction mode + prepared 不兼容。pgbouncer 1.21 + prepare cache 或者禁 prepared。 4. **transaction 中调存储过程**：某些 PG 存过程内 `COMMIT` 触发 → pgbouncer 状态错乱。pool_mode statement 或者重构。 5. **pgbouncer 重启 = 断所有 client**：升级 pgbouncer 谨慎。优雅做法 多个 pgbouncer + LB rolling restart。

`jsonb` 是 PostgreSQL 9.4+ 的内部二进制 JSON 类型。比 `json`（纯文本存） 快得多，且可以建 GIN 索引做高速字段查询。 典型场景：审计日志、用户配置、设备上报数据、事件流。 ## 建表 ```sql CREATE TABLE events ( id BIGSERIAL PRIMARY KEY, type TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(), payload JSONB NOT NULL ); ``` ## 插入 ```sql INSERT INTO events (type, payload) VALUES ('login', '{"user_id": 42, "ip": "1.2.3.4", "ua": "Mozilla/5.0..."}'), ('purchase', '{"user_id": 42, "amount": 99.5, "items": ["a", "b"]}'); ``` ## 常用查询操作符 ```sql -- 取字段 SELECT payload->>'user_id' AS uid FROM events; -- 文本 SELECT (payload->>'amount')::numeric FROM events; -- 转型 SELECT payload->'items'->0 FROM events; -- 数组取第 0 个 -- 包含查询（最常用） SELECT * FROM events WHERE payload @> '{"user_id": 42}'; -- 键存在 SELECT * FROM events WHERE payload ? 'amount'; SELECT * FROM events WHERE payload ?& array['user_id', 'ip']; -- 全部存在 SELECT * FROM events WHERE payload ?| array['user_id', 'guest_id']; -- 任一存在 ``` ## GIN 索引：让上面查询变快 ```sql -- 全字段 GIN：所有 key/value 路径都索引（大） CREATE INDEX events_payload_gin ON events USING GIN (payload); -- jsonb_path_ops GIN：只索引 @>，体积小 30-50% CREATE INDEX events_payload_pgin ON events USING GIN (payload jsonb_path_ops); -- 部分索引：只有特定 type 的事件才索引 CREATE INDEX events_login_payload ON events USING GIN (payload) WHERE type = 'login'; ``` `jsonb_path_ops` 是大多数 @> 查询的最优选择，体积小但只支持 `@>`。 ## 函数式索引（针对单字段） 如果你只查 payload 里某一个字段： ```sql -- 在 (payload->>'user_id') 上建普通 btree CREATE INDEX events_user_id ON events (((payload->>'user_id')::bigint)); -- 查询 EXPLAIN ANALYZE SELECT * FROM events WHERE (payload->>'user_id')::bigint = 42; ``` 比 GIN 还快，但只能用于这一种查询。 ## 更新 ```sql -- 替换整个 payload UPDATE events SET payload = '{"...":...}' WHERE id = 1; -- 修改单字段 UPDATE events SET payload = payload || '{"status": "done"}' WHERE id = 1; -- 删字段 UPDATE events SET payload = payload - 'status' WHERE id = 1; -- 嵌套路径设置 UPDATE events SET payload = jsonb_set(payload, '{user, name}', '"alice"', true) WHERE id = 1; ``` ## 聚合 / 展开 ```sql -- 取所有不同的 type 值 SELECT DISTINCT payload->>'level' FROM events; -- 展开 JSON 数组为多行 SELECT id, jsonb_array_elements_text(payload->'items') AS item FROM events; -- 按 JSON 字段聚合 SELECT payload->>'country' AS country, count(*) FROM events GROUP BY payload->>'country' ORDER BY count(*) DESC; ``` ## 校验 / Schema 约束（jsonb_schema） PG 17 之前没原生 JSON Schema 校验。可以用 CHECK 约束做基础校验： ```sql ALTER TABLE events ADD CONSTRAINT payload_has_user_id CHECK (payload ? 'user_id' AND jsonb_typeof(payload->'user_id') = 'number'); ``` 更复杂的校验建议在应用层做（Pydantic / JSON Schema 库）。 ## 性能边界 - 行级 `payload` 字段 > 8KB 会被 TOAST（外部存储），读取需要解压 - 单值超过 1MB 性能急剧下降 - 经常修改的字段不要塞 jsonb：每次 UPDATE 整列重写 - 数组 push 没有"in-place"，每次 append 都是整列重写 如果数据本质就是结构化的，建关系表，别用 jsonb 偷懒。 ## EXPLAIN 验证索引被用上 ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE payload @> '{"user_id": 42}'; -- Bitmap Index Scan on events_payload_pgin -- Recheck Cond: (payload @> '{"user_id": 42}') -- ... ``` `Seq Scan` 出现说明索引没生效 —— 检查统计信息是否够新（`ANALYZE events`） 或者索引类型是否匹配查询。 ## Django ORM 用法 ```python from django.db import models from django.contrib.postgres.indexes import GinIndex from django.contrib.postgres.fields import JSONField # PG-specific, 旧 # 现代 Django 用通用的 models.JSONField，PG 后端自动 jsonb class Event(models.Model): type = models.CharField(max_length=50) occurred_at = models.DateTimeField(auto_now_add=True) payload = models.JSONField() class Meta: indexes = [ GinIndex(fields=['payload'], opclasses=['jsonb_path_ops'], name='events_payload_pgin'), ] # 查询： Event.objects.filter(payload__contains={'user_id': 42}) Event.objects.filter(payload__user_id=42) # 自动 ->> Event.objects.filter(payload__has_key='ip') ``` ## 踩过的坑 - 字段路径里 `->` 是 jsonb，`->>` 是 text。`->>` 后才能比较字符串； `(payload->>'amount')::numeric` 才能数值比较。 - GIN 索引建索引非常慢且占用空间大（可能比表本身还大）。生产建议 `CREATE INDEX CONCURRENTLY` 在线建，不锁表。 - `jsonb_path_ops` 不支持 `?`、`?|`、`?&` 操作符；如果要用这几个， 必须用普通 GIN。 - 在 PG 16 之前 `jsonb_set` 修改不存在的路径 + `create_missing=true` 时行为有 quirk；升 PG 16+ 之后行为更一致。

RAG / 语义搜索的标准做法：把文档切成 chunk → 用 embedding model 转向量 → 存向量库 → 查询时 embedding 后 ANN 搜索。 向量库选项： - 专用：Qdrant / Milvus / Weaviate / Chroma - 通用 + 向量扩展：**PostgreSQL + pgvector** 如果你已经在用 PG，pgvector 是最省事的——一套数据库管业务数据 + 向量， 不引入新系统。下面是完整流程。 ## 1. 装扩展 ```bash # Debian / Ubuntu sudo apt install postgresql-16-pgvector # 或编译： # git clone https://github.com/pgvector/pgvector # cd pgvector && make && sudo make install ``` ```sql -- 在目标数据库里执行 CREATE EXTENSION IF NOT EXISTS vector; ``` ## 2. 建表 ```sql CREATE TABLE documents ( id BIGSERIAL PRIMARY KEY, source TEXT NOT NULL, chunk TEXT NOT NULL, embedding vector(1536), -- OpenAI text-embedding-3-small 维度 metadata JSONB, created_at TIMESTAMPTZ DEFAULT now() ); ``` `vector(N)` 是定长向量类型，N 必须匹配你的 embedding model 输出维度。 常见： - OpenAI `text-embedding-3-small`: 1536 - OpenAI `text-embedding-3-large`: 3072 - Cohere `embed-multilingual-v3`: 1024 - `bge-large-zh-v1.5`: 1024 - `bge-m3`: 1024 - `nomic-embed-text`: 768 ## 3. 插入 embedding ```python import psycopg from openai import OpenAI client = OpenAI() def embed(text): resp = client.embeddings.create(input=text, model='text-embedding-3-small') return resp.data[0].embedding # List[float] con = psycopg.connect('postgresql://localhost/mydb') text = 'PostgreSQL 是开源关系数据库...' emb = embed(text) con.execute( 'INSERT INTO documents (source, chunk, embedding) VALUES (%s, %s, %s)', ('manual.md', text, emb) ) con.commit() ``` psycopg3 + pgvector-python： ```bash uv add psycopg pgvector ``` ```python from pgvector.psycopg import register_vector register_vector(con) # 现在能直接传 numpy array / list 给 vector 字段 ``` ## 4. ANN 搜索 ```sql -- 找最相似的 5 条（距离最小） SELECT id, source, chunk, embedding <=> $1::vector AS distance FROM documents ORDER BY embedding <=> $1::vector LIMIT 5; ``` `<=>` 是余弦距离运算符。pgvector 还支持： - `<->`：欧氏距离 (L2) - `<#>`：内积负值（dot product 越大越相似，所以取负） 最常用的是 `<=>` 余弦距离，对长度归一化的 embedding 等价于内积。 ## 5. 索引：HNSW 或 IVFFlat 无索引时是 brute-force 扫表，100k 行还能用，百万级就慢。 建索引： ```sql -- HNSW（推荐，召回高） CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- IVFFlat（建索引快，召回略低） CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); ``` 索引必须匹配你的距离操作符： - `vector_cosine_ops` ↔ `<=>` - `vector_l2_ops` ↔ `<->` - `vector_ip_ops` ↔ `<#>` `lists` 推荐 `sqrt(rows)`，`ef_construction` 越大召回越好但建索引越慢。 ## 6. 查询时调召回 / 速度 ```sql -- HNSW SET hnsw.ef_search = 100; -- 默认 40；越大召回越好越慢 SELECT ... FROM documents ORDER BY embedding <=> $1 LIMIT 10; -- IVFFlat SET ivfflat.probes = 10; -- 默认 1；越大召回越好越慢 SELECT ... FROM documents ORDER BY embedding <=> $1 LIMIT 10; ``` 通常 `ef_search` 64-256 之间是甜点。 ## 7. 混合搜索（向量 + 全文 + 元数据过滤） 向量搜索的弱点：精确关键词容易丢。最佳实践是混合： ```sql -- 假设 chunk 上建了 to_tsvector('simple', chunk) 的 GIN 索引 WITH vector_results AS ( SELECT id, chunk, embedding <=> $1::vector AS dist FROM documents WHERE metadata->>'project' = $2 ORDER BY embedding <=> $1::vector LIMIT 50 ), fts_results AS ( SELECT id, chunk, ts_rank(to_tsvector('simple', chunk), plainto_tsquery('simple', $3)) AS rank FROM documents WHERE metadata->>'project' = $2 AND to_tsvector('simple', chunk) @@ plainto_tsquery('simple', $3) LIMIT 50 ) SELECT * FROM ( SELECT id, chunk, 1 - dist AS score FROM vector_results UNION ALL SELECT id, chunk, rank * 5 AS score FROM fts_results ) GROUP BY id, chunk ORDER BY MAX(score) DESC LIMIT 10; ``` 或者更精致用 RRF (Reciprocal Rank Fusion) 算法。 ## 8. 性能数据（参考） | 量级 | brute force | HNSW | |---|---|---| | 10k 行 | 10-50ms | < 5ms | | 100k | 100-500ms | 10ms | | 1M | 几秒 | 20-50ms | | 10M | 60s+ | 100ms | 1000 万向量是 PostgreSQL + pgvector 大致甜点。再大上 Qdrant / Milvus。 ## 9. 批量插入 ```python from pgvector.psycopg import register_vector register_vector(con) # 批量 with con.cursor() as cur: cur.executemany( 'INSERT INTO documents (source, chunk, embedding) VALUES (%s, %s, %s)', [(s, c, e) for s, c, e in zip(sources, chunks, embeddings)] ) con.commit() ``` 千条以上用 `COPY ... FROM STDIN`，10x 速度。 ## 10. 用 Django ```python # settings: 装 'pgvector.django' 应用 from pgvector.django import VectorField, HnswIndex class Document(models.Model): source = models.CharField(max_length=200) chunk = models.TextField() embedding = VectorField(dimensions=1536) class Meta: indexes = [ HnswIndex( name='doc_emb_hnsw', fields=['embedding'], m=16, ef_construction=64, opclasses=['vector_cosine_ops'], ), ] # 查询 from pgvector.django import CosineDistance Document.objects.alias(d=CosineDistance('embedding', query_emb)).order_by('d')[:10] ``` ## 踩过的坑 - 维度不匹配：插 1024 维向量到 `vector(1536)` 字段会报错。 embedding model 一定要固定，换 model 必须重建索引。 - HNSW 索引构建非常慢且耗内存（10M 行可能要几小时 + 10GB+ 内存）。 生产建议在低峰期 `CREATE INDEX CONCURRENTLY`。 - pgvector 不存储原文，只存向量：要返回相关文档需要把原文也存表里。 - 别在 vector 列上做 `WHERE` 条件而不带 ORDER BY ... LIMIT： 全表扫的 vector 距离计算极慢。索引只在 ORDER BY 配 LIMIT 时生效。

## 起因 单 PG 写多读多，CPU 经常 80%+。临时方案是加内存 + SSD，但读 query 还是抢主库 CPU。 PG 自带的 streaming replication 几乎免费——配一个 standby 把读流量 分过去，写仍走主。 ## 整体架构 ``` 应用 (写) 应用 (读) ↓ ↓ 主库 (primary) ----→ 从库 (standby) WAL 流 ``` - 主库正常处理读写 - 从库实时 replay 主库 WAL，几乎实时同步（毫秒级延迟） - 从库只读，可以服务 SELECT 查询 - 主库挂了 → 提升从库为新主 ## 解决方案 ### 1. 主库配置 `/etc/postgresql/16/main/postgresql.conf`： ``` wal_level = replica max_wal_senders = 5 wal_keep_size = 1GB # 保留多少 WAL 给落后的 standby 追 hot_standby = on ``` `/etc/postgresql/16/main/pg_hba.conf` 允许复制连接： ``` host replication replicator <standby_ip>/32 scram-sha-256 ``` 创建复制用户： ```sql CREATE USER replicator REPLICATION LOGIN PASSWORD 'strong-pass'; ``` `sudo systemctl restart postgresql`。 ### 2. 从库初始化（pg_basebackup） 从库**全空状态**（删 data 目录或新机器）： ```bash sudo systemctl stop postgresql sudo rm -rf /var/lib/postgresql/16/main/* sudo -u postgres pg_basebackup \ -h <primary_ip> -U replicator -p 5432 \ -D /var/lib/postgresql/16/main \ -Fp -Xs -P -R ``` `-R` 自动生成 `postgresql.auto.conf` + `standby.signal`， 让这个 instance 启动后就是 standby。 ```bash sudo systemctl start postgresql # 校验 sudo -u postgres psql -c "SELECT pg_is_in_recovery();" # t (true，是 standby) ``` ### 3. 在主库上看复制状态 ```sql SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn, sync_state, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes FROM pg_stat_replication; -- client_addr | state | ... | lag_bytes -- 10.0.0.2 | streaming | ... | 0 ``` `state=streaming` + `lag_bytes` 接近 0 = 健康。 ### 4. 应用读写分离 最简单：用两个连接池。 ```python # Django settings DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql', 'HOST': 'primary.db.local', 'NAME': 'myapp', 'USER': 'app', 'PASSWORD': '...', }, 'replica': { 'ENGINE': 'django.db.backends.postgresql', 'HOST': 'standby.db.local', 'NAME': 'myapp', 'USER': 'app', 'PASSWORD': '...', }, } DATABASE_ROUTERS = ['myapp.routers.PrimaryReplicaRouter'] ``` ```python # routers.py class PrimaryReplicaRouter: def db_for_read(self, model, **hints): return 'replica' def db_for_write(self, model, **hints): return 'default' def allow_relation(self, obj1, obj2, **hints): return True def allow_migrate(self, db, app_label, **hints): return db == 'default' ``` 读 query 自动走 replica，写走 primary。 Node / Python 其它框架同理：分两个连接池，业务代码按操作类型选。 ### 5. 同步 vs 异步复制 默认**异步**：主库 commit 立刻返回，WAL 后台 stream 到 standby。 代价：主挂时 standby 可能差几秒数据。 切**同步**（commit 等 standby 确认）： ``` # postgresql.conf synchronous_standby_names = 'standby1' synchronous_commit = on ``` 代价：standby 慢 / 挂时主库写阻塞。生产建议 quorum： ``` synchronous_standby_names = 'ANY 1 (standby1, standby2, standby3)' ``` 3 个 standby 任一确认即可——既保证 RPO=0 又有容错。 ### 6. 自动 failover：repmgr / Patroni PG 自带不做"主挂了自动提升 standby"。需要外部工具： - **repmgr**：简单成熟 - **Patroni**：基于 etcd / Consul，K8s 友好 - **pg_auto_failover**：Citus 出品 最简 repmgr： ```bash sudo apt install postgresql-16-repmgr # 注册主库 sudo -u postgres repmgr -f /etc/repmgr.conf primary register # 注册 standby sudo -u postgres repmgr -f /etc/repmgr.conf standby register # 启动 daemon（监控 + 自动 failover） sudo systemctl enable --now repmgrd ``` 主挂后 repmgrd 30 秒内提升某 standby 为新主，更新所有节点配置。 应用层用 PgBouncer + 监听 repmgr 事件改 backend 指向新主。 或者用 HAProxy 在前面做 health check： ``` backend pg_primary option pgsql-check user healthcheck server primary primary.db:5432 check server standby1 standby1.db:5432 check backup ``` `backup` 表示 standby 在 primary down 时才接流量。 ### 7. 监控复制延迟 ```sql -- 主库看每个 standby 的延迟（bytes） SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS lag_mb FROM pg_stat_replication; -- standby 上看延迟（秒） SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds; ``` Prometheus postgres_exporter 自动暴露这些指标。 告警阈值：lag > 30 秒 → warning，> 5 分钟 → critical。 ### 8. logical replication（不同 schema 选择性复制） streaming replication 是物理（整库 / 所有表 / 同版本）。 logical replication（PG 10+）按表选择性复制，可跨版本： ```sql -- 主库 CREATE PUBLICATION mypub FOR TABLE users, posts; -- 订阅端（可以是另一台 PG，不要求版本一致） CREATE SUBSCRIPTION mysub CONNECTION 'host=primary user=replicator dbname=myapp password=...' PUBLICATION mypub; ``` 用于：跨版本升级（旧版做 publication，新版做 subscription，同步后切流量）、 ETL（把 production 部分表 logical 复制到分析库）。 ## 效果 我们配主从后： - 主库 CPU 80% → 35%（读流量去 standby） - 报表 query（重 read）不再影响业务写性能 - 主库挂过一次，repmgr 28 秒 failover，业务无感知 - 监控显示复制延迟稳定 < 100ms ## 与其它扩容方案对比 | | 物理复制（流） | 逻辑复制 | 读写分离中间件 | Citus / 分库 | |---|---|---|---|---| | 复杂度 | 低 | 中 | 中 | 高 | | 适合 | 读扩容 / HA | 跨版本 / 部分表 | 多主 | 横向扩容 PB 级 | | 跨主版本 | ❌ | ✅ | N/A | ✅ | | 自动 failover | 需 repmgr/Patroni | 难 | ✅ | ✅ | ## 踩过的坑 1. **standby IP 防火墙**：5432 端口必须从主库 → standby、standby → 主库 双向通（standby 需要拉 WAL）。 2. **wal_keep_size 太小**：standby 落后超过这个大小后 WAL 被回收， standby 无法追上 → 必须 full re-base。生产至少 1-10 GB。或者用 replication slot（slot 让 PG 保留 WAL 直到 slot 消费完）： ```sql SELECT pg_create_physical_replication_slot('slot_standby1'); ``` standby 配 `primary_slot_name='slot_standby1'`。 3. **standby 上跑长 query 阻塞复制**：standby 默认会 cancel 长 query 让复制优先。要避免就调 `max_standby_streaming_delay = 30s`（query 能跑多久）。 4. **switchover 后没清旧主**：旧主重启后会变 "split brain"（同时两个 主）。一定先 demote 旧主或者关掉 PG service。 5. **同步复制 standby 全挂主库 hang**：synchronous + 没 standby 时主库 write 阻塞等。设 `synchronous_commit = local` 或者 quorum `ANY 1` 避免单点。

## 起因 PG 慢。CPU 跑满。但具体是哪条 query / 哪个业务模块？ slow query log 能记慢的，但漏掉"单次快、调用极多次"的；也不容易聚合 看"Top N 总耗时 query"。 `pg_stat_statements` 是 PG 自带 extension，按"query 模板" 累计每条 SQL 的总耗时 / 调用次数 / 平均时间。性能分析的瑞士军刀。 ## 启用 `postgresql.conf`： ``` shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all # all / top / none pg_stat_statements.track_utility = on ``` 需要重启 PG： ```bash sudo systemctl restart postgresql ``` 加载 extension（一次性）： ```sql CREATE EXTENSION pg_stat_statements; ``` ## 经典 Top 查询 ### Top 10 总耗时 query ```sql SELECT substring(query, 1, 100) AS query_short, calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, round((total_exec_time / sum(total_exec_time) OVER ()) * 100, 1) AS pct FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``` 输出： ``` query_short | calls | total_ms | mean_ms | pct SELECT * FROM orders WHERE user_id = $1 | 1234567 | 245678.12 | 0.20 | 32.5 SELECT * FROM products WHERE category = $1 | 234567 | 123456.78 | 0.53 | 16.3 UPDATE sessions SET ... WHERE id = $1 | 5678901 | 89012.34 | 0.016 | 11.8 ``` `pct` 列告诉你"这条 query 占总 DB 时间 32.5%"。**显著大头一目了然**。 ### Top 10 平均最慢 ```sql SELECT substring(query, 1, 100), calls, round(mean_exec_time::numeric, 2) AS mean_ms, round(stddev_exec_time::numeric, 2) AS stddev_ms FROM pg_stat_statements WHERE calls > 100 -- 调用 < 100 次的忽略（统计意义弱） ORDER BY mean_exec_time DESC LIMIT 10; ``` 找"单次很慢但调用不多"的 query。 ### Top IO 消耗 ```sql SELECT substring(query, 1, 100), calls, shared_blks_hit, shared_blks_read, round((shared_blks_hit::numeric * 100 / (shared_blks_hit + shared_blks_read + 1)), 1) AS hit_pct, round(total_exec_time::numeric, 2) AS total_ms FROM pg_stat_statements WHERE shared_blks_read > 0 ORDER BY shared_blks_read DESC LIMIT 10; ``` `shared_blks_read` 高 = 经常从磁盘读（cache miss）。 `hit_pct` 低 = working set 不在 shared_buffers 内。 ### Top temp 文件用量 ```sql SELECT substring(query, 1, 100), calls, temp_blks_read, temp_blks_written FROM pg_stat_statements WHERE temp_blks_written > 0 ORDER BY temp_blks_written DESC LIMIT 10; ``` `temp_blks_written > 0` = `work_mem` 不够大，PG 用磁盘做 sort / hash。 调大 work_mem 或者改 query 让 sort 集少点。 ## 让 query 文本可读 默认 `query` 字段把字面值替换成 `$1` `$2` 等： ``` SELECT * FROM orders WHERE user_id = $1 AND status = $2 ``` 同一 query 模板不同参数算同一条。 分析 perspective 想看完整 SQL： ```sql ALTER SYSTEM SET pg_stat_statements.track = 'all'; ALTER SYSTEM SET pg_stat_statements.save = on; SELECT pg_reload_conf(); ``` 不过 `$1`/`$2` 模板化是 feature，便于聚合分析。 ## reset stats ```sql SELECT pg_stat_statements_reset(); -- 清零，重新开始统计 ``` 定期 reset 让数据反映最近的负载。 跑性能优化前 reset → 跑业务 1 小时 → 看 stats。 ## 跟 EXPLAIN ANALYZE 配合 pg_stat_statements 告诉你"哪条 query 是瓶颈"。 EXPLAIN ANALYZE 告诉你"这条 query 为什么慢"。 组合用： ```sql -- 1. pg_stat_statements 找 Top 1 -- 2. 拿完整 SQL（替换 $1 等占位符） -- 3. EXPLAIN ANALYZE 跑一遍 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending'; -- 4. 看 plan： -- Seq Scan? → 加索引 -- 估算 rows 偏差大? → ANALYZE 表 -- 大 Hash join? → work_mem 不够 ``` ## 生产监控仪表盘 Grafana + postgres_exporter 自动暴露 pg_stat_statements 数据： ```yaml # postgres_exporter 配置 queries: - pg_stat_statements_top: query: | SELECT queryid::text, query, total_exec_time, calls FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20 metrics: - queryid: { usage: LABEL } - query: { usage: LABEL } - total_exec_time: { usage: GAUGE } - calls: { usage: GAUGE } ``` Grafana 仪表盘 panel： - Top 10 query by total time（pie chart） - Top 10 query by mean time - query 数 / 慢 query 数趋势线 ## 真实 case：减少 90% DB 时间 我们一个 web app 用 pg_stat_statements 跑一周后看 Top 5： ``` 1. SELECT COUNT(*) FROM users WHERE deleted_at IS NULL | 38% pct 2. SELECT * FROM products JOIN ... | 24% pct 3. SELECT * FROM sessions WHERE expires > now() | 18% pct 4. SELECT * FROM logs WHERE ... | 8% pct 5. ... ``` 行动： 1. `SELECT COUNT(*) FROM users WHERE deleted_at IS NULL`：被首页用了 N 次。改成 cache 5 分钟（Redis），从 38% → 0.5%。 2. `SELECT * FROM products JOIN ...`：N+1 query。改成 prefetch + 单次 JOIN。 3. `SELECT * FROM sessions ...`：缺索引。`CREATE INDEX ON sessions(expires)`。 reset stats + 跑一周后 Top 5 完全变化，DB CPU 从 70% → 12%。 ## 限制 + 注意 ### 1. plan 不存 pg_stat_statements 只记 query 文本 + 计数 / 时间，不存 query plan。 要看 plan 仍要 EXPLAIN。 ### 2. session 级变量影响 `SET LOCAL work_mem = '256MB'` 等 session 设置影响 query 但 stats 不区分。 ### 3. 安全（敏感数据） extension 默认 query 文本会被截短（参数 `track_activity_query_size`）。 完整 SQL 可能含 schema 名 / 表名 / 业务逻辑细节。访问 pg_stat_statements 要 superuser 权限。 ### 4. 性能 overhead 收集统计本身极轻（< 1% CPU）。生产开它是标准做法。 ## 几个查询模板我经常用 ```sql -- 找"慢但调用少" 可优化但可能 ROI 低 SELECT substring(query, 1, 80), calls, round(mean_exec_time::numeric, 2) FROM pg_stat_statements WHERE mean_exec_time > 1000 AND calls < 100 ORDER BY total_exec_time DESC LIMIT 20; -- 找"非常频繁的快 query"（可能 cache 化） SELECT substring(query, 1, 80), calls, round(mean_exec_time::numeric, 3) FROM pg_stat_statements WHERE calls > 10000 AND mean_exec_time < 5 ORDER BY calls DESC LIMIT 20; -- 找写操作 SELECT substring(query, 1, 80), calls, round(total_exec_time::numeric, 2) FROM pg_stat_statements WHERE query ILIKE 'UPDATE%' OR query ILIKE 'INSERT%' OR query ILIKE 'DELETE%' ORDER BY total_exec_time DESC LIMIT 20; ``` ## 与 auto_explain `auto_explain` 是另一个 extension，自动对慢 query 跑 EXPLAIN 并 log： ``` shared_preload_libraries = 'pg_stat_statements, auto_explain' auto_explain.log_min_duration = 1000 # > 1s 自动 EXPLAIN auto_explain.log_analyze = true ``` 慢 query 自动留下 plan，不需要事后复现。 但 log_analyze 让 query 跑两次（一次正常 + 一次 ANALYZE），有性能开销。 ## 踩过的坑 1. **shared_preload_libraries 改了忘重启**：extension 不加载。 `CREATE EXTENSION` 也会报错 `library not loaded`。 2. **pg_stat_statements.max 太小**：超过后老 query 被 evict。 显著负载多种 query 的应用建议 10000+。 3. **reset 频繁** → 失去历史趋势。生产建议每月 reset 一次 + 之前 dump 数据到分析 DB。 4. **生产 query 含动态 IN** ：`WHERE id IN ($1, $2, $3)` 跟 `IN ($1, $2)` 被算作不同 query（IN 元素数不一样模板就不同）。考虑改成 `= ANY($1)` 传数组。 5. **跨 DB**：pg_stat_statements 是 DB 级别。每个 DB 都要单独装 extension + 查看。

应用慢 90% 是 DB 查询慢。能读 EXPLAIN ANALYZE 输出是 SQL 调优的前置技能。 ## 1. 一个最简单的例子 ```sql EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]'; ``` 输出： ``` Index Scan using users_email_idx on users (cost=0.43..8.45 rows=1 width=84) (actual time=0.025..0.026 rows=1 loops=1) Index Cond: ((email)::text = '[email protected]'::text) Planning Time: 0.123 ms Execution Time: 0.045 ms ``` 读懂这几个字段： - `Index Scan using users_email_idx`：用了哪个索引（好） - `cost=0.43..8.45`：估算的相对开销（启动..总） - `rows=1`：估算返回行数 - `actual time=0.025..0.026`：**实际**启动时间..完成时间（毫秒） - `rows=1 loops=1`：实际行数 + 循环次数 `Execution Time` 是最终关注点。 ## 2. 看到 Seq Scan 警觉 ``` Seq Scan on orders (cost=0.00..18334.00 rows=987 width=58) (actual time=0.123..123.456 rows=982 loops=1) Filter: (status = 'pending') Rows Removed by Filter: 999018 ``` Seq Scan = 全表扫描。1M 行表里筛 1k 行，扫了 1M 行。 解决：给 status 建索引。 ```sql CREATE INDEX idx_orders_status ON orders (status); ``` 但 PG 优化器有时仍选 Seq Scan（如果 status='pending' 占行数比例 > 5-10% PG 会觉得全扫更快——是对的，索引扫 + 回表的成本可能更高）。 ## 3. estimated vs actual 行数差异 ``` Bitmap Heap Scan on events (cost=... rows=1) (actual time=... rows=12345 loops=1) ``` `rows=1` 估算 vs `rows=12345` 实际，差 4 个数量级。 说明统计信息陈旧或不准。 ```sql ANALYZE events; -- 更激进 ALTER TABLE events ALTER COLUMN type SET STATISTICS 1000; ANALYZE events; ``` `STATISTICS` 默认 100，加大让统计直方图更细。 PG 自动 ANALYZE 大量更新后触发，但 batch ETL 后建议显式 ANALYZE。 ## 4. Nested Loop / Hash Join / Merge Join ``` Hash Join (cost=... ) Hash Cond: (o.user_id = u.id) -> Seq Scan on orders o (...) -> Hash (...) -> Seq Scan on users u (...) ``` 三种 JOIN 策略： - **Nested Loop**：外层循环 + 内层索引查找。适合小集合 + 内表有索引 - **Hash Join**：内表建 hash 表，外表 lookup。适合两边都大的等值连接 - **Merge Join**：两边排序后归并。适合已排序数据 PG 自动选；策略错（如 nested loop 跑 1M × 1M）会爆。 慢的话试着调 `work_mem`（影响 Hash 是否能放内存）： ```sql SET work_mem = '256MB'; ``` ## 5. Bitmap Index Scan ``` Bitmap Heap Scan on logs (cost=... rows=10000) -> Bitmap Index Scan on logs_date_idx (cost=...) Index Cond: (date >= '2024-01-01' AND date < '2024-02-01') ``` Bitmap scan 是"读索引拿到所有匹配的 row position 后批量去表里取"。 适合中等选择度（1-30% 行匹配）。Index Scan 适合更小（< 1%）， Seq Scan 适合大量（> 30%）。 ## 6. EXPLAIN ANALYZE BUFFERS ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT ... ``` 输出加上： ``` Buffers: shared hit=128 read=4520 written=12 ``` - `hit`：命中 shared buffer（内存里的 PG cache） - `read`：从磁盘读 - `dirtied` / `written`：脏页 / 写盘 `read` 数字大 = 慢查询是 I/O 受限的。如果同一查询第二次跑 read 变 hit， 就是缓存效应，单次测试不准。 ```sql -- 重置缓存 + 多次跑取平均 EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- 跑 3-5 次 ``` ## 7. EXPLAIN (ANALYZE, VERBOSE, FORMAT JSON) ```sql EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) SELECT ...; ``` JSON 格式适合丢进可视化工具，如： - [explain.dalibo.com](https://explain.dalibo.com/) - [explain.depesz.com](https://explain.depesz.com/) - pgMustard 把 EXPLAIN 输出粘进去，图形化展示树 + 高亮慢节点。强烈推荐。 ## 8. 慢查询日志 `postgresql.conf`： ``` log_min_duration_statement = 100 # ms，超过就记录 log_duration = on log_statement_stats = off ``` ```bash sudo systemctl reload postgresql sudo tail -f /var/log/postgresql/postgresql-*.log ``` 或用 pg_stat_statements 扩展看 Top N 慢查询： ```sql CREATE EXTENSION pg_stat_statements; SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; ``` `pg_stat_statements` 是生产 PG 的必备扩展之一。 ## 9. 几个常见慢查询根因 ### A. 缺索引 ```sql EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; -- Seq Scan + Rows Removed by Filter: 多 -- 解：CREATE INDEX idx_orders_user_id ON orders (user_id); ``` ### B. 索引顺序错了 ```sql -- 索引 (a, b, c) SELECT ... WHERE b = ? -- ❌ 跳过 a，索引用不上 SELECT ... WHERE a = ? AND b = ? -- ✅ 用上索引前缀 ``` ### C. 不能用 SARGable 表达式 ```sql -- ❌ 函数应用在列上，索引无效 SELECT ... WHERE lower(email) = '[email protected]' -- ✅ 用函数索引 CREATE INDEX idx_users_email_lower ON users (lower(email)); ``` ### D. JOIN 时 row estimate 严重偏差 → ANALYZE 更新统计；或者改写 query 让 PG 估算更准。 ### E. 大表 DELETE/UPDATE 没回收空间 ```sql VACUUM ANALYZE big_table; VACUUM FULL big_table; -- 会锁表，谨慎 ``` ## 10. 索引大概念 | 索引类型 | 适合 | |---|---| | btree | 默认；等值、范围、ORDER BY | | hash | 仅等值（PG 10+ 才 WAL safe） | | gin | 数组、jsonb、全文搜索 | | gist | 几何、范围类型 | | brin | 大表 + 数据有物理顺序（如 append-only 日志） | 99% 用 btree。 ## 11. 部分索引（partial index） ```sql -- 只索引 active 的订单 CREATE INDEX idx_orders_pending_user ON orders (user_id) WHERE status = 'pending'; ``` 索引体积小、维护便宜，但只在查询条件包含 `status='pending'` 时生效。 ## 12. 表达式索引 ```sql CREATE INDEX idx_users_created_month ON users ((date_trunc('month', created_at))); -- 查询用同样表达式才命中 SELECT count(*) FROM users WHERE date_trunc('month', created_at) = '2024-01-01'; ``` ## 踩过的坑 - 用 EXPLAIN（不带 ANALYZE）只看估算，不真跑 → 估算和实际差很多时 误导。诊断必带 ANALYZE。 - EXPLAIN ANALYZE 在 INSERT/UPDATE/DELETE 真的执行！要 dry-run 包 事务：`BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;` - "我加了索引为啥还 Seq Scan"：PG 觉得 Seq Scan 更快（数据量 / 选择度 问题）；ANALYZE 表 + 看 actual stats。 - VACUUM FULL 锁表，生产慎用。日常依赖 autovacuum；不够用 pg_repack 在线 repack。

单张 PG 表超过几亿行后： - 索引体积膨胀，新插入慢 - VACUUM / 索引重建动辄几小时 - 删旧数据扫全表慢 分区把一张大表按某列（通常是时间）分成多个物理子表，PG 透明 routing 查询到对应分区。下面是声明式分区（PG 10+）的完整流程。 ## 1. 按月分区一张事件表 ```sql -- 主表（不能直接插数据，只是定义结构 + 分区策略） CREATE TABLE events ( id BIGSERIAL, user_id BIGINT NOT NULL, type TEXT NOT NULL, payload JSONB, occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(), PRIMARY KEY (id, occurred_at) -- partition key 必须在 PK 里 ) PARTITION BY RANGE (occurred_at); -- 索引：每个分区自动继承 CREATE INDEX events_user_id_idx ON events (user_id); CREATE INDEX events_type_idx ON events (type); ``` ## 2. 创建实际分区 ```sql CREATE TABLE events_2026_05 PARTITION OF events FOR VALUES FROM ('2026-05-01') TO ('2026-06-01'); CREATE TABLE events_2026_06 PARTITION OF events FOR VALUES FROM ('2026-06-01') TO ('2026-07-01'); CREATE TABLE events_2026_07 PARTITION OF events FOR VALUES FROM ('2026-07-01') TO ('2026-08-01'); ``` INSERT 进 `events` 时 PG 自动 routing： ```sql INSERT INTO events (user_id, type, occurred_at) VALUES (42, 'login', '2026-06-15 10:00'); -- 实际写入 events_2026_06 ``` ## 3. 查询：partition pruning ```sql EXPLAIN SELECT count(*) FROM events WHERE occurred_at >= '2026-06-01' AND occurred_at < '2026-07-01'; -- Aggregate -- -> Seq Scan on events_2026_06 -- 只扫一个分区！其它分区直接 skip ``` 如果 WHERE 没限定 partition key，PG 会扫所有分区，性能可能反而比单表差。 **所有查询都要带 partition key 条件**。 ## 4. 自动建未来分区 每月手动建分区容易忘。用 [pg_partman](https://github.com/pgpartman/pg_partman) 扩展： ```sql CREATE EXTENSION pg_partman; SELECT partman.create_parent( p_parent_table => 'public.events', p_control => 'occurred_at', p_type => 'range', p_interval => '1 month', p_premake => 6 -- 提前 6 个月建分区 ); ``` 加个定时任务定期跑维护： ```sql SELECT partman.run_maintenance(); ``` 自动建未来分区 + 自动删过期分区（如果配了 retention）。 ## 5. 删历史数据：秒杀 传统方案：`DELETE FROM events WHERE occurred_at < '2024-01-01'` — 几亿行 DELETE，VACUUM 几小时。 分区方案： ```sql DROP TABLE events_2024_01; -- 或： ALTER TABLE events DETACH PARTITION events_2024_01; -- DETACH 让分区脱离主表但保留物理；可以备份后再 DROP ``` DDL 操作，毫秒级完成 + 释放磁盘空间。 ## 6. 分区策略选择 | 策略 | 适合 | |---|---| | RANGE | 按时间 / 数值范围（最常用） | | LIST | 按枚举值（如 country='CN' 一个分区） | | HASH | 按 hash 模 N，均匀分布；适合 user_id 这种高基数 | ```sql -- LIST CREATE TABLE orders (...) PARTITION BY LIST (country); CREATE TABLE orders_cn PARTITION OF orders FOR VALUES IN ('CN'); CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US'); CREATE TABLE orders_other PARTITION OF orders DEFAULT; -- HASH CREATE TABLE users (...) PARTITION BY HASH (id); CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1); CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2); CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3); ``` ## 7. 索引在分区表里 ```sql -- 在主表上建索引 = 在所有分区上建对应索引 CREATE INDEX events_user_id_idx ON events (user_id); -- 新分区自动继承 CREATE TABLE events_2026_08 PARTITION OF events FOR VALUES FROM ('2026-08-01') TO ('2026-09-01'); \d events_2026_08 -- 看到 user_id 索引自动存在 ``` PG 12 之后这是默认行为；PG 11 需要手动给每个分区单独建。 ## 8. UNIQUE 约束的限制 ```sql -- ❌ 主表 UNIQUE 必须包含 partition key ALTER TABLE events ADD UNIQUE (user_id); -- ERROR -- ✅ ALTER TABLE events ADD UNIQUE (user_id, occurred_at); ``` 跨分区的全局唯一性 PG 原生不支持。如果业务要"id 全表唯一"， 要么把 id 加 partition key 一起当唯一，要么用 UUID（碰撞概率天文）。 ## 9. INSERT 性能 分区表 INSERT 比单表稍慢（PG 要算 routing），但写大表反而更快 （小分区 + 小索引 = 写少）。10 亿行规模分区表能比单表快 5-10 倍写入。 ## 10. ATTACH / DETACH 分区 把已经存在的表挂为新分区： ```sql CREATE TABLE events_2025_archived (... 同 events 结构 ...); -- 数据 COPY 进去... ALTER TABLE events ATTACH PARTITION events_2025_archived FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'); ``` ATTACH 时 PG 会扫描确认数据都满足分区条件（耗时）。生产建议先 `ALTER TABLE ... ADD CONSTRAINT events_2025_check CHECK (occurred_at >= '2025-01-01' AND occurred_at < '2025-02-01')` 让 PG 跳过扫描。 ## 11. 跨分区 query 优化 ```sql -- 这条会扫所有分区 SELECT * FROM events WHERE user_id = 42; -- 加 partition key 限定能 prune SELECT * FROM events WHERE user_id = 42 AND occurred_at >= '2026-01-01'; ``` 设计分区时考虑常见查询的 WHERE 条件。 ## 12. 监控 ```sql -- 各分区大小 SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class WHERE relname LIKE 'events_%' ORDER BY pg_relation_size(oid) DESC; -- 各分区行数（统计估算，快） SELECT relname, reltuples::bigint AS rows FROM pg_class WHERE relname LIKE 'events_%'; ``` ## 踩过的坑 - 分区数过多（> 1000）→ planner 慢，每次查询都要遍历所有分区元数据。 保持 < 1000 分区，或合并老分区。 - 没配 partman maintenance / 没自动建未来分区 → INSERT 找不到分区报错。 应用 down 直到手动建。 - 跨分区 UNIQUE 不可能 → 业务设计阶段就要明确"全表唯一" vs "分区内唯一"。 - 老 PG 9.x 用继承（INHERITS）实现 partition，PG 10+ 的声明式 partition 完全不同 + 更好用。不要混淆。