知识广场

## 起因 我们的小型生产服务用 SQLite（部署简单 + 性能够 + 单文件备份）。 但默认备份方案最多每天 cron `sqlite3 .backup` + scp 异地。两次备份之间 有 24 小时 RPO（最坏丢一天数据）。 `litestream` 是开源工具，把 SQLite 的 WAL 日志实时增量复制到 S3 兼容 存储。RPO 降到秒级，无需改 application 代码。 ## 解决方案 ### 装 ```bash # 二进制安装 LITESTREAM_VERSION=0.3.13 curl -fsSL https://github.com/benbjohnson/litestream/releases/download/v${LITESTREAM_VERSION}/litestream-v${LITESTREAM_VERSION}-linux-amd64.tar.gz \ | sudo tar xz -C /usr/local/bin litestream litestream version ``` 或 Docker：`docker run litestream/litestream`。 ### 配置 `/etc/litestream.yml`： ```yaml dbs: - path: /srv/knowledge/db.sqlite3 replicas: - type: s3 bucket: my-backups path: knowledge endpoint: https://s3.us-east-005.backblazeb2.com region: us-east-005 access-key-id: ${B2_KEY_ID} secret-access-key: ${B2_APP_KEY} retention: 720h # 保留 30 天 snapshot-interval: 24h # 每天全量 snapshot ``` litestream 工作原理： 1. application 写 `db.sqlite3` → SQLite 写 WAL 2. litestream 后台读 WAL 增量帧 3. 每个 WAL 帧立刻上传到 S3（默认 10s 内） 4. 定期（默认 24h）做一次全量 snapshot 5. retention 期外的 snapshot + WAL 自动清理 ### systemd service ```ini # /etc/systemd/system/litestream.service [Unit] Description=Litestream Requires=network.target After=network.target [Service] User=trio Group=trio EnvironmentFile=/etc/litestream.env ExecStart=/usr/local/bin/litestream replicate -config /etc/litestream.yml Restart=on-failure RestartSec=5s StandardOutput=journal StandardError=journal [Install] WantedBy=multi-user.target ``` `/etc/litestream.env`： ```bash B2_KEY_ID=00abc... B2_APP_KEY=K00... ``` ```bash sudo systemctl enable --now litestream journalctl -u litestream -f # 看到 "replicating wal frames..." ``` ### 验证副本 ```bash litestream snapshots /srv/knowledge/db.sqlite3 # replica generation index size created # s3 abc123 5 12345678 2026-05-24T03:30:00Z litestream wal /srv/knowledge/db.sqlite3 | head # replica generation index offset size ``` ### 灾难还原 服务器全没了，新机器恢复： ```bash # 装 litestream + 复制 /etc/litestream.yml + .env 到新机器 litestream restore -o /srv/knowledge/db.sqlite3 \ s3://my-backups/knowledge # 从最新 snapshot + 重放 WAL 到最新状态 # 几秒-几分钟（取决数据量） # 启动 application sudo systemctl start knowledge ``` 时间点恢复： ```bash litestream restore -timestamp 2026-05-20T10:00:00Z \ -o /tmp/db-at-may-20.sqlite3 \ s3://my-backups/knowledge ``` 恢复到任意 30 天内的时间点。 ## 效果 - RPO 从 24h → 10s - 灾难还原 = 一行 `litestream restore` - B2 存储费用：30 天保留 ~2 GB DB 约 $0.10/月 - application 零改动：litestream 在后台独立进程跑 ## 与其它方案对比 | | cron `.backup` + scp | litestream | PG streaming replica | |---|---|---|---| | RPO | 24h | ~10s | ~ms | | 单机部署 | ✅ | ✅ | 需主备 | | 时间点恢复 | ❌ | ✅ | ✅ | | 同步开销 | 0（备份时刻才有） | 极低（WAL 增量） | 中（流复制） | | 成本 | 几乎 0 | 存储费 + 几乎免费 | 备机 + 网络 | SQLite + litestream 在中小规模生产是性价比最高方案之一。 ## 与之前装的 daily backup 共存 之前章节装的 `knowledge-backup.timer` 是每日全量本机存档；litestream 是实时异地复制。两者互补： - 本地 daily 用于"刚才误删一条记录" 快速回滚 - litestream 用于"机器全没了" 灾难恢复 不冲突，都开就好。 ## 性能影响 litestream 读 WAL 是只读操作，对 application 几乎无影响。 WAL 上传是后台 + 异步，application 写延迟不变。 CPU 占用通常 < 1%，内存 < 50 MB。 ## 踩过的坑 1. **SQLite 必须 WAL 模式**：litestream 依赖 WAL。 ```sql PRAGMA journal_mode = WAL; ``` 不是 WAL 时 litestream 报错。Django 我们已经在 settings 里开了。 2. **不能两个 litestream 同时复制同一个 DB**：会写坏。一个机器一个 实例。 3. **同时开 cron `.backup`**：`.backup` 命令本身不影响 WAL，安全； 但 `VACUUM` / `VACUUM INTO` 会重写 DB 让 litestream 失效，需要 重新 init。 4. **B2 / R2 等 S3-兼容 endpoint URL 易写错**：每个区域 endpoint 不同。出错时 `litestream replicate` 报 "no such bucket"。 curl 测试一下访问。 5. **首次 restore 时 generation 不匹配**：DB 被本地写过新数据， litestream WAL 链断了。生产里数据始终从备份 restore，本地不要 手动改。

开发期想要一个随手起、不污染主机、带管理界面的 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` 更稳。

DuckDB 是嵌入式分析数据库（"SQLite for analytics"），单二进制， 能直接读 Parquet / CSV / JSON 文件，复杂分析查询比 pandas / Spark 本地模式快得多。 适合：本地数据探索、报表生成、数据科学家"我有 50GB Parquet 在 S3 想跑几条 SQL"。 ## 安装 ```bash uv add duckdb # 或独立 CLI： curl https://install.duckdb.org | sh duckdb --version ``` ## CLI 入门 ```bash duckdb my.db # duckdb shell D SELECT 1 + 1; ┌───────┐ │ ?col0 │ ├───────┤ │ 2 │ └───────┘ D .help D .quit ``` ## 直接查 Parquet（不需要导入） ```sql -- 单文件 SELECT * FROM 'data/sales.parquet' LIMIT 10; -- 多文件 SELECT * FROM 'data/year=*/month=*/*.parquet' LIMIT 10; -- S3 直接读 SET s3_region='us-east-1'; SET s3_access_key_id='...'; SET s3_secret_access_key='...'; SELECT count(*) FROM 's3://bucket/data/*.parquet'; ``` `year=*/month=*` 是 Hive 风格分区路径，DuckDB 自动识别并 prune 不需要 的分区。 ## 与 polars / pandas 互通 ```python import duckdb import polars as pl # 直接查 polars DataFrame（不复制数据，Arrow zero-copy） df = pl.read_csv('users.csv') result = duckdb.sql("SELECT country, COUNT(*) FROM df GROUP BY 1").pl() # 同理 pandas import pandas as pd pdf = pd.DataFrame({...}) result = duckdb.sql("SELECT ... FROM pdf").df() ``` `duckdb.sql(...)` 返回 DuckDBPyRelation，可以 `.pl()` `.df()` `.fetchall()` 转换。 ## 典型用例 ### 1. 探索一份大 CSV ```python import duckdb duckdb.sql(""" SELECT country, COUNT(*) AS rows, AVG(amount) AS avg_amt, QUANTILE_CONT(amount, [0.5, 0.95, 0.99]) AS p50_p95_p99 FROM 'sales.csv' WHERE date >= '2024-01-01' GROUP BY country ORDER BY rows DESC LIMIT 20 """).show() ``` 整个 CSV 在内存里只读一次，DuckDB 自动用所有 CPU 核心。 ### 2. CSV → Parquet 转换 ```python duckdb.sql(""" COPY (SELECT * FROM 'sales.csv') TO 'sales.parquet' (FORMAT PARQUET) """) # 同样一份数据，Parquet 通常 1/3 大小 + 列查询快 10x ``` 按字段分区： ```python duckdb.sql(""" COPY (SELECT * FROM 'sales.csv') TO 'partitioned/' (FORMAT PARQUET, PARTITION_BY (year, month)) """) # 输出：partitioned/year=2024/month=01/data_0.parquet ... ``` ### 3. JOIN 多个文件 ```sql SELECT u.name, COUNT(*) AS n FROM 'users.parquet' u JOIN 'orders.parquet' o ON o.user_id = u.id WHERE o.date >= '2024-01-01' GROUP BY u.name ORDER BY n DESC LIMIT 10; ``` DuckDB 优化器自己决定 hash join / merge join；不需要建索引。 ### 4. 窗口函数 ```sql SELECT user_id, date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY date) AS cum_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM 'orders.parquet' WHERE rn = 1; ``` 完整 SQL:2003 窗口函数。 ### 5. 写回数据库 ```python # 写到 Postgres duckdb.sql("INSTALL postgres; LOAD postgres;") duckdb.sql(""" ATTACH 'host=localhost dbname=mydb' AS pg (TYPE POSTGRES); COPY (SELECT * FROM 'data.parquet') TO pg.public.my_table; """) ``` ## DuckDB 与 SQLite 对比 | 维度 | SQLite | DuckDB | |---|---|---| | 设计 | OLTP（事务） | OLAP（分析） | | 存储 | 行存 | 列存 | | 多核 | 单线程 | 自动并行 | | Parquet | 不直接支持 | 一等公民 | | 文件 | .db 单文件 | .db 单文件，也能完全无文件 | 不要替代——它们解决不同问题。事务用 SQLite，分析用 DuckDB。 ## 持久化 vs 内存 ```python import duckdb # 内存（默认） con = duckdb.connect(':memory:') # 文件 con = duckdb.connect('analytics.db') ``` 文件模式可以 `CREATE TABLE` 持久化数据。内存模式纯查询。 ## 大数据场景：spilling to disk DuckDB 内存不够时自动 spill 到 `tmp/`： ```python duckdb.sql("PRAGMA temp_directory='./tmp_duckdb';") duckdb.sql("PRAGMA memory_limit='4GB';") ``` 跑大 query 不会 OOM，但慢点。比起 Spark 集群部署成本，单机能跑通是 胜利。 ## 与 BI 工具集成 ```bash # Tableau / DBeaver / Metabase 都有 DuckDB connector # JDBC: org.duckdb.DuckDBDriver # ODBC: 官方提供 ``` 数据科学家用 DuckDB 算分析，BI 工具直接连展示。 ## CLI 高级技巧 ```bash # 用 fzf 选 .parquet 文件查 duckdb -c "SELECT * FROM '$(fd -e parquet | fzf)' LIMIT 100" # 输出 JSON duckdb -json -c "SELECT * FROM 'data.parquet' LIMIT 5" # 输出 CSV duckdb -c "COPY (SELECT * FROM 'data.parquet' WHERE x > 0) TO STDOUT WITH (HEADER, FORMAT CSV)" ``` ## 踩过的坑 - 写 Parquet 时 string column 含 NULL → 默认 NULL handling 可能不对。 `COPY ... TO ... (FORMAT PARQUET, COMPRESSION 'zstd')` 显式指定参数。 - S3 大量小文件（< 10MB / 文件）→ 元数据请求开销大。聚合成少量大文件 再扫。 - DuckDB 跨大版本（0.x → 1.x）数据库文件格式有时不兼容；升级前 `EXPORT DATABASE '...'` 备份。 - python 包默认装的是 prebuilt wheel，没装某些 extension（spatial / excel）。`INSTALL spatial; LOAD spatial;` 运行时装。

## 起因 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` 避免单点。

分布式锁是绝大多数后端早晚都要的"防止并发执行某操作"机制。 Redis 是最常用的实现，但实现细节不对会导致**重复执行 / 死锁 / 误释放**。 下面讲两种正确写法。 ## 单实例 SET NX PX（最常用） 适合非关键路径："同一任务同一时刻只跑一份"，可以接受极小概率冲突。 ```python import secrets import redis r = redis.Redis(decode_responses=True) def acquire_lock(key: str, ttl_ms: int) -> str | None: """Return token if locked, None otherwise.""" token = secrets.token_hex(16) ok = r.set(key, token, nx=True, px=ttl_ms) return token if ok else None def release_lock(key: str, token: str) -> bool: """只在 token 匹配时才删 —— 防止误释放别人的锁。""" lua = """ if redis.call('get', KEYS[1]) == ARGV[1] then return redis.call('del', KEYS[1]) else return 0 end """ return bool(r.eval(lua, 1, key, token)) ``` 使用： ```python token = acquire_lock('job:nightly-report', ttl_ms=60000) if not token: print('another worker is running this; skip') return try: do_the_work() finally: release_lock('job:nightly-report', token) ``` ## 三个关键点 1. **TTL 必须**：worker 崩了 / 网断了，锁自动过期，否则死锁 2. **token 必须**：删锁时验证是不是自己的；否则你的锁超时了， 别人拿了同 key 的锁，你回头 del 把别人的锁删了 3. **释放用 Lua**：GET + DEL 不是原子的，中间可能锁到期被别人拿走 不做 token 验证的"`del`" 释放是大坑。 ## 上下文管理器封装 ```python import contextlib @contextlib.contextmanager def redis_lock(key, ttl_ms=30000): token = acquire_lock(key, ttl_ms) if not token: raise RuntimeError(f'lock {key} already held') try: yield finally: release_lock(key, token) # 用法 with redis_lock('job:sync', ttl_ms=120000): do_sync() ``` ## 长任务的 TTL 续期（看门狗） 如果 do_the_work 可能跑超过 ttl，需要在后台周期性续期： ```python import threading, time class WatchdogLock: def __init__(self, key, ttl_ms=30000): self.key, self.ttl_ms, self.token = key, ttl_ms, None self._stop = threading.Event() self._thread = None def __enter__(self): self.token = acquire_lock(self.key, self.ttl_ms) if not self.token: raise RuntimeError('lock busy') self._thread = threading.Thread(target=self._renew, daemon=True) self._thread.start() return self def __exit__(self, *args): self._stop.set() self._thread.join() release_lock(self.key, self.token) def _renew(self): # 每 ttl/3 续一次 while not self._stop.wait(self.ttl_ms / 3 / 1000): lua = """ if redis.call('get', KEYS[1]) == ARGV[1] then return redis.call('pexpire', KEYS[1], ARGV[2]) else return 0 end """ r.eval(lua, 1, self.key, self.token, self.ttl_ms) ``` ## Redlock —— 多 Redis 实例时 如果 Redis 是单机，主从切换的瞬间锁可能丢（旧 master 上有锁， 新 master 上没有）。**Redlock 算法** 在多个独立 Redis 实例上同时 申请锁，多数派同意才算成功。 但 Redlock 有争议（Martin Kleppmann vs antirez 之争）：分布式系统专家 认为它在某些时钟漂移场景下不安全。**结论：除非你真的不能容忍上面这种 极端故障，否则单实例 SET NX PX 已经够好**。 如果一定要 Redlock，用 [redis-py-lock](https://github.com/redis/redis-py) 内置的 `r.lock()` 实现（基于 Redlock）： ```python import redis r = redis.Redis() with r.lock('myresource', timeout=60, blocking_timeout=5) as locked: do_work() ``` 或者多实例版： ```python from redis import Redis from redis.lock import Lock rs = [Redis(host=h) for h in ('r1', 'r2', 'r3')] # 你需要自己写 Redlock 算法：在 majority 上获取 ``` Python 生态用 [`redlock-py`](https://github.com/SPSCommerce/redlock-py) 更省事。 ## 什么时候**不要**用 Redis 锁 - **严格不可重复执行**：付款扣库存、唯一 ID 申领。 数据库的事务 / unique constraint 是真正的源（Redis 锁可能因为时钟 / 网络分区误放过两次）。 - **跨数据中心**：Redis 跨 DC 复制延迟通常 > 锁的 TTL；用 etcd / Zookeeper 的 lease 机制。 - **强一致需求**：见上，用支持 raft / paxos 的工具。 ## 整数 ID / 任务队列防重复 如果你要"任务 X 在 30 分钟内只处理一次"，比锁更简单的是 dedup key： ```python key = f'dedup:taskX:{job_id}' if not r.set(key, '1', nx=True, ex=1800): return # 已经处理过 / 正在处理 do_work() ``` 不需要释放——靠 TTL 自然过期。简单粗暴。 ## 踩过的坑 - 没 token 直接 `del`：worker A 拿锁后任务跑超时锁过期 → worker B 拿到 同 key 锁 → A 任务结束直接 del → A 把 B 的锁删了 → C 又拿到锁 → 重复执行。 - TTL 太短：网络抖动 / GC 暂停 / 数据库慢一下，TTL 到了任务还没完， 下一个 worker 进来。要么调长 TTL 要么用看门狗。 - 用 Redis 锁保护 DB 操作：注意 Redis 锁本身只是建议性，DB 一致性还是 靠 DB 事务 / UNIQUE。锁主要是"避免重复劳动"而不是"保证正确性"。 - redis-py 默认 connection pool 在多线程不安全。每个线程拿自己的 Redis 实例，或者用 redis-py 4+ 的 connection pool（已优化）。

## 起因 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 + 查看。

## 起因 第一次用 MongoDB 是从关系数据库转过来：把 PG 里的 users / posts / comments 三张表照搬成三个 collection，每篇 post 存 `user_id` 引用。 查询时 application 端做 3 次 query 拼起来。 最后发现这就是把 MongoDB 当 NoSQL "假 PG" 用，丧失了文档数据库的优势 （也没拿到关系数据库的事务）。 MongoDB 的正确姿势是设计 schema 时考虑**访问模式**（access pattern）： 经常一起读的数据 embed 在一起。 ## 决策框架 按几个维度选 embed vs reference： ### 1. 一对一 / 一对多 / 多对多 - 1:1 或 1:few（< 几十）→ embed - 1:many（百级 +）→ reference（避免文档太大） - many:many → reference ### 2. 是否一起读 / 一起写 - 经常一起读 → embed - 独立访问、生命周期不同 → reference ### 3. 是否会增长 - 有界（如 user.address 通常 1-3 条）→ embed - 无界（comments 可能千万级）→ reference ### 4. 写入频率 - 子数据频繁更新（counter / status） → reference 避免文档反复重写 - 子数据基本不变 → embed ## 实战例子：博客系统 ### 模式 A：扁平 reference（关系数据库 mindset） ```js // users { _id: ObjectId("..."), name: "Alice", email: "..." } // posts { _id: ObjectId("..."), title: "...", body: "...", author_id: ObjectId("...") } // comments { _id: ObjectId("..."), post_id: ObjectId("..."), author_id: ObjectId("..."), body: "..." } ``` 读一篇 post + 它的评论 + 作者：3 次 query + application 拼接。 不是 MongoDB 的优势用法。 ### 模式 B：embed comments 进 post ```js { _id: ObjectId("..."), title: "...", body: "...", author: { _id: ObjectId("..."), name: "Alice" }, // embed 作者基本信息 comments: [ { _id: ObjectId("..."), author: { _id: ObjectId("..."), name: "Bob" }, body: "...", created_at: ISODate(), }, // ... ], created_at: ISODate(), } ``` 一次 `db.posts.findOne({_id})` 拿全部数据。 适合： - 每篇 post 评论数有限（< 几百） - 评论展示几乎总是跟 post 一起读 - 评论不会被独立查询（如"列出某用户所有评论"少见） 不适合： - 评论可能上千（文档 > 16MB 限制） - 评论独立修改频繁（每次更新整个 post 文档） - 需要按评论独立查询 ### 模式 C：分桶（bucket pattern） 如果评论会增长但仍想 embed-like 局部性： ```js // post 主体 { _id, title, body, comment_count: 42 } // comments 分桶 { _id: ObjectId(), post_id: ObjectId("..."), bucket_index: 0, // 第 0 桶 comments: [ { author, body, created_at }, // 桶内最多 100 条 // ... ] } ``` 按时间 / id 范围把评论分批。读最近评论 = 读最后一个 bucket（小文档）。 全量评论 = 多次 query 但每次都是几 KB 而非几 MB。 适合 IoT 时序数据、聊天历史、活动 feed 等。 ### 模式 D：用 sub-collection + 反规范化字段 ```js // posts (主信息 + 反规范化字段) { _id, title, body, author_id, author_name, // 作者名嵌进来避免 join comment_count: 42, last_comment_at: ISODate(), } // comments（独立 collection） { _id, post_id, author_id, author_name, body, created_at } ``` `author_name` 在 post 里冗余存——读 post 列表不需要 join users。 用户改名时要更新所有引用（cost 低频）。 `last_comment_at` / `comment_count` 在 post 上维护，列表页排序方便。 **这是大多数博客 / 社交 app 的 sweet spot**。 ## 实战例子：电商订单 ```js // orders（embed product 当时的 snapshot） { _id, user_id: ObjectId(), items: [ { product_id: ObjectId(), // 下面是下单时 snapshot，product 涨价也不变 sku: "ABC-123", name: "Widget", price: 99.50, quantity: 2, } ], total: 199.00, status: "shipped", shipping_address: { ... }, // embed payment: { method, txn_id }, // embed created_at, updated_at, } ``` 为什么 product 信息要 embed snapshot？产品涨价 / 改名 / 下架后， 历史订单还能显示下单时的价格 / 描述。这是文档数据库优势：把"事实" 冻结在事件发生时。 ## 索引 ```js // 复合索引：常按 user 查最新订单 db.orders.createIndex({ user_id: 1, created_at: -1 }) // 部分索引：只索引未完成订单 db.orders.createIndex( { user_id: 1, created_at: -1 }, { partialFilterExpression: { status: { $in: ["pending", "processing"] } } } ) // 全文搜索 db.posts.createIndex({ title: "text", body: "text" }) db.posts.find({ $text: { $search: "mongodb schema" } }) ``` `explain('executionStats')` 看 query 走没走索引： ```js db.orders.find({user_id: ObjectId("...")}).sort({created_at: -1}).limit(20).explain('executionStats') // IXSCAN + totalKeysExamined ~= nReturned = 健康 // COLLSCAN = 缺索引 ``` ## 事务 MongoDB 4.0+ 支持多文档事务： ```js const session = client.startSession() try { session.startTransaction() await db.collection('orders').insertOne(order, { session }) await db.collection('inventory').updateOne( { _id: productId }, { $inc: { stock: -quantity }}, { session } ) await session.commitTransaction() } catch (e) { await session.abortTransaction() throw e } finally { session.endSession() } ``` 但好的 schema 设计应该让大多数操作**不需要跨文档事务**。 embed 把"原子操作"装进一个文档里，单文档写自然原子。 ## Aggregation pipeline 复杂查询用 aggregation： ```js db.orders.aggregate([ { $match: { status: "shipped" } }, { $unwind: "$items" }, { $group: { _id: "$items.sku", revenue: { $sum: { $multiply: ["$items.price", "$items.quantity"] }}, count: { $sum: "$items.quantity" } }}, { $sort: { revenue: -1 } }, { $limit: 10 }, ]) ``` 像 SQL GROUP BY 但 stages 串成 pipeline。比 application 端聚合快 10-100x （DB 内执行，结果集小）。 ## 何时不用 MongoDB - 强事务 + 多表 join + 复杂报表 → PostgreSQL - 关系紧密 + schema 严格 → 关系库 - 极大写吞吐（百万 ops/s）+ key-value → DynamoDB / Cassandra MongoDB 适合：用户 profile / 内容 / 半结构化 / 易变 schema / fast-iterating 产品。 ## 效果 我们的内容系统迁移到合理设计： - 首页列表 query：3 次 SQL JOIN（PG）→ 1 次 find（Mongo），延迟从 80ms → 12ms - 文档存 markdown 源 + render 后 HTML + metadata 一起，全 cache 友好 - schema 增加新字段无需 migration（直接写新 doc） ## 踩过的坑 1. **文档 > 16MB**：MongoDB 单文档硬上限。无限增长的 array 早晚踩。 2. **数组上的 `$push` + `$slice`**：要限大小： ```js db.posts.updateOne( { _id: postId }, { $push: { comments: { $each: [newComment], $slice: -100 } } } ) ``` 保留最近 100 条评论，老的滚出。 3. **null 和缺字段语义混淆**： ```js { foo: null } // 字段存在，值为 null { /* no foo */ } // 字段缺失 ``` `find({foo: null})` 同时匹配两种。要区分用 `{foo: {$exists: true}}`。 4. **ObjectId vs string**：把 string `_id` 传给 `find({_id: id})` 匹配不到 ObjectId。永远 `new ObjectId(idStr)` 转换。 5. **schema 后期变更**：MongoDB 不强制 schema，但 application 假设 字段存在。改 schema 需要 migrate 旧文档（`updateMany` 加默认值）。 建议生产用 schema validation： ```js db.createCollection("orders", { validator: { $jsonSchema: { ... } } }) ```

应用慢 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。

PostgreSQL 适合 OLTP（事务），千万行查询还行，几十亿行后聚合就力不从心。 ClickHouse 是俄罗斯 Yandex 开源的列存 OLAP 数据库，专为"几十亿行表 + 复杂 SELECT 聚合"设计。日志分析 / BI / 指标存储常用。 ## 安装 ```bash # Debian / Ubuntu sudo apt install -y apt-transport-https ca-certificates dirmngr GNUPGHOME=$(mktemp -d) sudo GNUPGHOME="$GNUPGHOME" gpg --no-default-keyring --keyring /usr/share/keyrings/clickhouse-keyring.gpg --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 8919F6BD2B48D754 echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list sudo apt update sudo apt install -y clickhouse-server clickhouse-client sudo systemctl enable --now clickhouse-server clickhouse-client ``` 或 docker： ```bash docker run -d --name ch \ -p 8123:8123 -p 9000:9000 \ -v $(pwd)/ch-data:/var/lib/clickhouse \ clickhouse/clickhouse-server ``` ## 建表（关键：选对引擎 + 排序键） ```sql CREATE TABLE events ( event_date Date, event_time DateTime, user_id UInt64, type LowCardinality(String), country LowCardinality(String), amount Float64, payload String, ) ENGINE = MergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, type, user_id) SETTINGS index_granularity = 8192; ``` 关键设计： - **ENGINE = MergeTree**：最常用的引擎，类似 LSM tree，写快读快 - **PARTITION BY**：按月分区，删旧数据 DROP PARTITION 即可 - **ORDER BY**（"主键"）：决定数据物理顺序 + 稀疏索引；按最常 WHERE 的列排序 - **LowCardinality(String)**：枚举类字段（status / country）的压缩 + 加速 ## 插入 ```sql INSERT INTO events VALUES ('2026-05-23', '2026-05-23 10:00:00', 42, 'login', 'CN', 0.0, ''), ('2026-05-23', '2026-05-23 10:01:00', 43, 'purchase', 'US', 99.50, '{...}'); ``` 或批量从 CSV： ```sql INSERT INTO events FROM INFILE 'events.csv.gz' FORMAT CSVWithNames; ``` 或从 JDBC / Python： ```python from clickhouse_driver import Client client = Client('localhost') client.execute( 'INSERT INTO events VALUES', [(d, t, uid, type, country, amt, payload) for ... in batch] ) ``` ClickHouse 偏好 **批量大插入**（每次 > 1000 行），单条 INSERT 性能差。 ## 聚合查询 ```sql -- 国家维度统计昨日金额 SELECT country, count() AS events, sum(amount) AS total, avg(amount) AS avg_amt FROM events WHERE event_date = today() - 1 GROUP BY country ORDER BY total DESC LIMIT 10; ``` 10 亿行 + 单机 16 cores：通常 < 1 秒。同样的查询在 PostgreSQL 几十秒到几分钟。 ```sql -- 时间序列：每小时按 type 的趋势 SELECT toStartOfHour(event_time) AS hour, type, count() FROM events WHERE event_date BETWEEN today() - 7 AND today() GROUP BY hour, type ORDER BY hour, type; ``` ## 函数：ClickHouse 100s 内置 - `uniq(col)` / `uniqExact(col)`：基数（HyperLogLog 估算 / 精确） - `quantile(0.95)(col)`：分位数 - `groupArray(col)` / `groupUniqArray(col)`：聚合成数组 - `topK(10)(col)`：Top N - `argMax(col, by)`：返回 by 最大时 col 的值 ```sql SELECT country, uniq(user_id) AS dau, quantile(0.95)(amount) AS p95, topK(3)(type) AS top_types FROM events WHERE event_date = today() GROUP BY country; ``` ## 物化视图（pre-aggregated） 百亿行表也希望仪表盘秒级响应？预聚合： ```sql CREATE MATERIALIZED VIEW events_daily_mv ENGINE = SummingMergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, country, type) AS SELECT event_date, country, type, count() AS events, sum(amount) AS total FROM events GROUP BY event_date, country, type; ``` 之后插 `events` 表的同时自动累加 daily 表。 仪表盘查 `events_daily_mv` 而不是原表，几毫秒返回。 ## 用 PostgreSQL 表 ```sql -- 从 PG 拉数据（postgres engine） CREATE TABLE pg_users ( id UInt64, name String, email String ) ENGINE = PostgreSQL( 'localhost:5432', 'mydb', 'users', 'user', 'pass' ); SELECT count() FROM pg_users; -- 实时去 PG 查 ``` 或者 ClickHouse 作为 PG 的 OLAP 副本（通过 Kafka / debezium 同步）。 ## 读 Parquet / S3 ```sql SELECT count() FROM s3('s3://bucket/data/*.parquet', 'access_key', 'secret_key', 'Parquet'); -- 不导入，直接查 S3 上的 Parquet ``` ```sql SELECT * FROM file('events.parquet', 'Parquet') LIMIT 10; ``` 让 ClickHouse 当"S3 上 SQL 查询引擎"，类似 DuckDB / Athena。 ## 副本 + 集群 单机能跑 TB 级。继续增长： - **副本（Replica）**：高可用 + 读扩展 - **分片（Shard）**：水平扩容 ```xml <remote_servers> <my_cluster> <shard> <replica><host>ch01</host><port>9000</port></replica> <replica><host>ch02</host><port>9000</port></replica> </shard> <shard> <replica><host>ch03</host><port>9000</port></replica> <replica><host>ch04</host><port>9000</port></replica> </shard> </my_cluster> </remote_servers> ``` ```sql CREATE TABLE events_dist ON CLUSTER my_cluster AS events ENGINE = Distributed(my_cluster, default, events, rand()); ``` 查 `events_dist` 自动并行到所有 shard。 ## 性能 tip - **避免 SELECT ***：列存的核心优势——只读你要的列 - **WHERE 用 ORDER BY 的前缀列**：才能命中稀疏索引 - **批量 INSERT** > 1000 行/次 - **LowCardinality(String)** 给低基数字符串字段 - **`final` 修饰** 用在 ReplacingMergeTree 上，性能差，少用 ## 何时选 ClickHouse - 日志 / 事件 / metric > 1 亿行 - 聚合密集 / OLAP 类查询 - 不需要事务 不适合： - 事务 / 强一致（用 PG） - 频繁 UPDATE / DELETE（ClickHouse 这两个是异步 ALTER） - 小数据（< 1 GB）—— 杀鸡用牛刀 ## 工具 - **clickhouse-client**：CLI（很强） - **clickhouse-cli**（rust 版）：交互体验更好 - **Tabix / DBeaver / Metabase / Superset**：可视化 - **Grafana** 内置 ClickHouse data source ## 踩过的坑 - 单条 INSERT 性能极差（百行/秒）：批量插入百倍速度差距。 - ORDER BY 选错列：所有查询的 WHERE 都不能用上索引，全表扫。 按"最常被 WHERE filter 的列"排。 - UPDATE / DELETE：在 ClickHouse 是 `ALTER TABLE ... UPDATE`， 异步、慢、影响压缩。设计阶段尽量避免。 - 分区数 > 1000 性能下降。partition 颗粒度通常按月。

## 起因 Redis 单实例够用阶段过了： - 数据量逼近单机 RAM - 单点故障 = 整服务挂 - 写入 / QPS 接近单机极限 两个官方 HA 方案： - **Sentinel**：master-replica + 自动 failover（数据仍单 master） - **Cluster**：sharded，多 master，自动分片 + failover 下面对比哪种适合哪种场景。 ## Sentinel ``` [client] ↓ (asks sentinel) [sentinel × 3] → 监控 master / replica ↓ [master] ←── async replication ──→ [replica × 2] ``` - 1 master 服务读写 - N replica 异步复制 - 3+ sentinel 监控，master 挂自动选 replica 升 master ### 部署 `sentinel.conf`： ``` port 26379 sentinel monitor mymaster 127.0.0.1 6379 2 # 2 票 = 多数 sentinel down-after-milliseconds mymaster 5000 sentinel failover-timeout mymaster 60000 sentinel parallel-syncs mymaster 1 ``` 3 个 sentinel 跑在不同机器 → 任意 2 个同意才能 failover。 ### 客户端连接 ```python from redis.sentinel import Sentinel sentinel = Sentinel([ ('sentinel1', 26379), ('sentinel2', 26379), ('sentinel3', 26379), ]) master = sentinel.master_for('mymaster') replica = sentinel.slave_for('mymaster') master.set('key', 'val') # 写 replica.get('key') # 读（可能略 stale） ``` client 问 sentinel 当前 master 是谁 → 直连。 failover 时 sentinel 推新 master → client 重连。 ### 优势 - 简单（仍是单 master 模型） - 数据完整性强（无分片复杂性） - 支持所有 Redis command（包括 MULTI/EXEC、Lua、cluster 不支持的） ### 劣势 - 单 master 写 QPS 上限（10w/s 量级） - 单机 RAM 上限（200 GB 算极限） - failover 期间（10-60s）短暂不可写 ## Cluster ``` [client] ↓ (knows slot → node mapping) [node1: slot 0-5460] ←→ [node2: slot 5461-10922] ←→ [node3: slot 10923-16383] ↓ ↓ ↓ [replica1] [replica2] [replica3] ``` - 16384 个 hash slot 分配到 master 节点 - key 哈希到 slot → slot 在哪个节点 - 每 master 自带 replica 做 failover - 无 sentinel（cluster 节点之间 gossip） ### 部署 需要至少 3 master + 3 replica = 6 节点： ```bash redis-cli --cluster create \ 127.0.0.1:7001 127.0.0.1:7002 127.0.0.1:7003 \ 127.0.0.1:7004 127.0.0.1:7005 127.0.0.1:7006 \ --cluster-replicas 1 ``` `redis.conf`： ``` cluster-enabled yes cluster-config-file nodes.conf cluster-node-timeout 5000 appendonly yes ``` ### 客户端 ```python from redis.cluster import RedisCluster rc = RedisCluster(host='node1', port=7001, decode_responses=True) rc.set('key', 'val') rc.get('key') ``` client 知道 slot 映射 → 直接连到对应节点（无 sentinel proxy）。 MOVED redirect 让 client 学新映射。 ### 优势 - 水平扩展：6 节点撑 60w QPS，3 TB RAM - 自动 sharding（hash slot） - failover 同样自动 ### 劣势 - multi-key 操作受限（必须同 slot，hash tag `{...}`） - pub/sub 不跨节点（cluster 模式下 pub/sub 是 broadcast 到所有节点） - 客户端要支持 cluster protocol - 运维复杂（add/remove node 时 reshard） ## 关键区别 | | Sentinel | Cluster | |---|---|---| | 数据分片 | 否（全在 master） | 是（16384 slot） | | 容量上限 | 单机 RAM | 累加 | | 写 QPS 上限 | 单 master | 累加 | | 节点数 | 1m + N replica + 3 sentinel | 3+ master + 3+ replica | | failover 控制 | sentinel 投票 | gossip + 选举 | | multi-key | 完全支持 | 必须同 slot (`{tag}`) | | pub/sub | 正常 | 节点间不传 | | 复杂度 | 中 | 高 | ## 怎么选 - **数据 < 50 GB + 中等 QPS（< 50k）** → Sentinel - **数据 > 100 GB + 高 QPS** → Cluster - **多 key transaction / Lua 复杂** → Sentinel - **缓存场景（多数 key 独立）** → Cluster - **AWS / GCP / managed**：用 ElastiCache / MemoryStore（背后就是这两个） 我们的实际：90% 项目 Sentinel 够。极少几个超大缓存项目用 Cluster。 ## hash tag (cluster multi-key) cluster 默认每 key hash 到不同 slot → MGET/MSET 跨 slot 失败。 `{...}` 强制 key 同 slot： ```python rc.set('{user:42}:profile', '...') rc.set('{user:42}:settings', '...') rc.mget(['{user:42}:profile', '{user:42}:settings']) # OK，同 slot ``` `{user:42}` 部分用来 hash。所有 `{user:42}:*` 在同节点。 设计 key 时考虑：相关 key 用同 tag → 减少跨节点操作。 ## failover 实测 Sentinel：master kill → 5-10 秒 detect + 选新 master + 客户端重连。 期间写失败 + 读 replica 可用。 Cluster：master kill → 类似 5-15 秒 detect + replica 提升。 该 slot 短暂不可写，其它 slot 正常。 两者都不是"零停机"，但都是"短暂不可写"级别。 ## ProxySQL-like proxy？ Redis 没像 ProxySQL 那么常见的官方 proxy。 第三方： - **twemproxy**（Twitter，老） - **codis**（豌豆荚，老） - **predixy**（Cluster proxy，仍维护） 加 proxy 让 client 不需要懂 sentinel/cluster，但多一跳 + 单点。 非必要不引入。 ## 监控 key 指标： - `connected_clients` - `used_memory` / `maxmemory` - `instantaneous_ops_per_sec` - `evicted_keys`（开 maxmemory-policy 时） - `master_link_status`（replica） - `cluster_state`（cluster） Prometheus redis_exporter 一行装。 ## 持久化 `appendonly yes` + `appendfsync everysec` → 最多丢 1 秒数据。 `save 900 1` RDB 备份基础。 混合（aof + rdb）是默认推荐。 HA 也不替代持久化（脑裂 / 整集群挂）。 ## 踩过的坑 1. **Sentinel quorum 配错**：2 sentinel + quorum 2 → 任意 sentinel 挂 → 没法 failover。最少 3 sentinel。 2. **cluster reshard 慢**：迁移大 slot 几小时。期间客户端 MOVED redirect 多 → latency 抖。计划好低峰期。 3. **client 不支持 cluster**：老版本 jedis / lettuce / redis-py 没 cluster 支持。升级 client。 4. **pub/sub in cluster**：消息只在该 channel 所在节点发布。redis 7+ 引入 sharded pub/sub `SPUBLISH` 改善但兼容性问题。 5. **跨 DC**：Redis 不为跨数据中心同步设计。延迟 > 几十 ms 时 replica lag 严重。跨 DC 用应用层方案（Kafka mirror / 应用双写）。

SQLite 默认 journal_mode 是 `delete`，写操作要拿整库锁，多写者 直接互相 block。改 WAL（Write-Ahead Logging）后： - 读不阻塞写 - 写不阻塞读 - 多个读者并发 - 单写者（这个仍是 SQLite 的硬限制） 对中小型应用（< 10k QPS）足够把它当生产数据库用。 ## 启用 WAL ```python import sqlite3 conn = sqlite3.connect('app.db') conn.execute('PRAGMA journal_mode = WAL;') conn.execute('PRAGMA synchronous = NORMAL;') conn.execute('PRAGMA busy_timeout = 5000;') conn.execute('PRAGMA cache_size = -64000;') # 64MB cache conn.execute('PRAGMA foreign_keys = ON;') conn.execute('PRAGMA temp_store = MEMORY;') conn.execute('PRAGMA mmap_size = 134217728;') # 128 MB mmap ``` 各 pragma 含义： | pragma | 作用 | 建议值 | |---|---|---| | `journal_mode = WAL` | 改为 Write-Ahead Logging | WAL | | `synchronous = NORMAL` | WAL 同步级（默认 FULL 更安全但慢） | NORMAL | | `busy_timeout` | 等锁的毫秒数（超时返回 SQLITE_BUSY） | 5000 | | `cache_size` | 负数表示 KB，正数表示页数；建议负数 | -64000 (64MB) | | `foreign_keys` | 默认 **关闭** —— 必须显式开 | ON | | `temp_store` | 临时表放内存还是磁盘 | MEMORY | | `mmap_size` | 用 mmap 而不是 read() 读 | 128MB | 每次新连接都要重设这些 pragma（除了 journal_mode 是持久化的）。 ## WAL 后会出现的文件 ``` app.db app.db-wal # WAL log app.db-shm # 共享内存映射 ``` 备份这 3 个文件都要在一致快照里，最好用 `VACUUM INTO` 或 SQLite Online Backup API。 不要简单 `cp app.db backup.db`，可能拿到不一致状态。 ```python # 一致备份： src = sqlite3.connect('app.db') dst = sqlite3.connect('backup.db') with dst: src.backup(dst) ``` ## checkpoint：把 WAL 合并回主库 WAL 不断追加，会越来越大。SQLite 默认 1000 页时自动 checkpoint， 但繁忙系统可能赶不上。手动控制： ```python conn.execute('PRAGMA wal_autocheckpoint = 1000;') # 默认值 conn.execute('PRAGMA wal_checkpoint(PASSIVE);') # 立刻做一次（不阻塞读写） conn.execute('PRAGMA wal_checkpoint(TRUNCATE);') # 完全清空 WAL（要短暂独占） ``` 低峰期跑一次 `TRUNCATE` 比较好。 ## 真实并发测试 ```python # 5 个线程并发读 + 1 个线程写 import threading, sqlite3, time DB = 'test.db' def setup(): c = sqlite3.connect(DB) c.execute('PRAGMA journal_mode = WAL') c.execute('CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY, v TEXT)') for i in range(10000): c.execute('INSERT INTO t (v) VALUES (?)', (f'val{i}',)) c.commit(); c.close() setup() def reader(): c = sqlite3.connect(DB) c.execute('PRAGMA busy_timeout = 5000') for _ in range(1000): c.execute('SELECT count(*) FROM t').fetchone() c.close() def writer(): c = sqlite3.connect(DB) c.execute('PRAGMA busy_timeout = 5000') for i in range(1000): c.execute('INSERT INTO t (v) VALUES (?)', (f'w{i}',)) c.commit() c.close() threads = [threading.Thread(target=reader) for _ in range(5)] + [threading.Thread(target=writer)] t0 = time.time() for t in threads: t.start() for t in threads: t.join() print(f'done in {time.time()-t0:.2f}s') ``` WAL 模式比 delete 模式通常快 3-10 倍（取决于 IO）。 ## SQLITE_BUSY 的根因 + 处理 WAL 模式下还可能 BUSY： 1. **两个写者同时持有写锁**：第二个收到 BUSY。`busy_timeout` 让 SQLite 自动重试 5 秒 2. **DDL 时被读者卡住**：CREATE / ALTER 需要独占，正在 SELECT 的连接会阻塞 DDL 3. **WAL checkpoint(TRUNCATE) 时被读者卡住**：同上 应用层正确做法：捕获 `sqlite3.OperationalError` 重试，或者用 `busy_timeout` 让驱动层重试。 ## Django 配置 ```python # settings.py DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sqlite3', 'NAME': BASE_DIR / 'db.sqlite3', 'OPTIONS': { 'init_command': 'PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;', 'timeout': 20, # busy_timeout (秒) }, } } ``` 或用一个 connection signal 设更多 pragma： ```python from django.db.backends.signals import connection_created def _set_pragmas(sender, connection, **kwargs): if connection.vendor == 'sqlite': with connection.cursor() as cur: cur.execute('PRAGMA journal_mode=WAL;') cur.execute('PRAGMA synchronous=NORMAL;') cur.execute('PRAGMA temp_store=MEMORY;') cur.execute('PRAGMA cache_size=-64000;') cur.execute('PRAGMA mmap_size=134217728;') connection_created.connect(_set_pragmas) ``` ## 什么时候放弃 SQLite - 写 QPS > 1000：单写者是瓶颈 - 多机器需要共享 DB：SQLite 是单机的 - 字段需要 jsonb 索引等高级 PG 特性 - 复杂查询性能：SQLite 优化器较 PG 简单 否则 SQLite 比 PG/MySQL 部署简单一个数量级，单文件备份，绝对值得。 ## 踩过的坑 - WAL 文件不被压缩；WAL 不 checkpoint 时膨胀到几 GB 还在变大。监控 `app.db-wal` 大小，超过 100MB 跑一次 `PRAGMA wal_checkpoint(TRUNCATE)`。 - NFS / SMB 上跑 SQLite **绝对不行**——锁机制基于 fcntl，网络文件系统的 fcntl 不可靠，会有静默数据损坏。 - `PRAGMA foreign_keys = ON` 每个连接都要单独设。Django 默认设了； 自己 sqlite3.connect() 时容易漏。 - 多进程（如 gunicorn 多 worker）共享 SQLite 没问题，但每个 worker 自己的连接都要设 pragma。

单张 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 完全不同 + 更好用。不要混淆。