> ## Content Index
> Fetch the complete content index at: https://blog.vercanti.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# PostgreSQL 完全指南
- URL: https://blog.vercanti.com/postgresql-wan-quan-zhi-nan/
- Published: 2026-08-28T14:35:37.000Z
- Updated: 2026-08-28T14:59:12.000Z
- Description: 相关文档：MySQL基础完全指南(/mysql-ji-chu-wan-quan-zhi-nan/) SQLAlchemy完全指南(/sqlalchemy-wan-quan-zhi-nan/) SQLModel完全指南(/sqlmodel-wan-quan-zhi-nan/) 始终使用 TIMESTAMPTZ 而非 TIMESTAMP：TIMESTAMP 不存储时区，跨时区系统会出现时间错乱。TIMESTAMPTZ 内部统一存储为 UTC，读取时按连接时区自动转换。 用 JSONB 而非 JSON：JSON 存储原始文本，每次访问都需解析；JSONB 存储
- Author: yellowdog
- Tags: 数据库

> 官方文档：<https://www.postgresql.org/docs/>  
> 适用版本：PostgreSQL 16（2026-05-07 核实）

相关文档：[MySQL基础完全指南](https://blog.vercanti.com/mysql-ji-chu-wan-quan-zhi-nan/) [SQLAlchemy完全指南](https://blog.vercanti.com/sqlalchemy-wan-quan-zhi-nan/) [SQLModel完全指南](https://blog.vercanti.com/sqlmodel-wan-quan-zhi-nan/)

---

## 1\. 基础概念

### PostgreSQL vs MySQL

| 特性           | PostgreSQL                | MySQL             |
| ------------ | ------------------------- | ----------------- |
| 遵循 SQL 标准    | 严格                        | 宽松                |
| JSON 支持      | 原生 JSONB（可索引）             | JSON（不可索引）        |
| 全文搜索         | 内置                        | 需插件               |
| 窗口函数         | 完整支持                      | 8.0+ 支持           |
| CTE（WITH 子句） | 完整支持                      | 8.0+ 支持           |
| 并发控制         | MVCC，读写互不阻塞               | MVCC，但 MyISAM 不支持 |
| 扩展           | 丰富（PostGIS、TimescaleDB 等） | 少                 |
| 数组类型         | 原生支持                      | 无                 |
| 适合场景         | 复杂查询、数据分析、地理数据            | 互联网高并发读写          |

### 安装（Docker，推荐）

```bash
# 启动 PostgreSQL 容器
docker run -d \
  --name postgres \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  postgres:16

# 连接
psql -h localhost -U myuser -d mydb
# 或
docker exec -it postgres psql -U myuser -d mydb

```

---

## 2\. 数据库与表管理

### 常用 psql 命令

| 命令            | 说明               |
| ------------- | ---------------- |
| \\l           | 列出所有数据库          |
| \\c dbname    | 切换数据库            |
| \\dt          | 列出当前 schema 的所有表 |
| \\d tablename | 查看表结构            |
| \\di          | 列出所有索引           |
| \\du          | 列出所有用户/角色        |
| \\timing      | 显示查询耗时           |
| \\x           | 切换扩展显示模式（长行友好）   |
| \\q           | 退出               |

### DDL 基础

```sql
-- 创建数据库
CREATE DATABASE mydb
    ENCODING 'UTF8'
    LC_COLLATE 'zh_CN.UTF-8'
    LC_CTYPE 'zh_CN.UTF-8';

-- 创建表
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,              -- 自增主键（等同于 INTEGER + SEQUENCE）
    -- 或用 BIGSERIAL（大整数）/ IDENTITY（SQL 标准写法）
    uuid        UUID DEFAULT gen_random_uuid(),  -- UUID 主键
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    age         SMALLINT CHECK (age >= 0 AND age <= 150),
    bio         TEXT,
    score       NUMERIC(5, 2),                   -- 总5位，小数2位
    tags        TEXT[],                          -- 数组类型
    metadata    JSONB,                           -- JSON（推荐 JSONB，可索引）
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMPTZ DEFAULT NOW(),       -- 带时区时间戳（推荐）
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN bio TO description;

```

---

## 3\. 数据类型速查

| 类型               | 说明                    | 示例                   |
| ---------------- | --------------------- | -------------------- |
| SMALLINT         | 2 字节整数（-32768\~32767） | 年龄、状态码               |
| INTEGER / INT    | 4 字节整数                | 通用 ID                |
| BIGINT           | 8 字节整数                | 大表 ID、雪花 ID          |
| SERIAL           | 自增 INTEGER            | 主键                   |
| BIGSERIAL        | 自增 BIGINT             | 大表主键                 |
| NUMERIC(p,s)     | 精确小数                  | 金额                   |
| REAL             | 4 字节浮点                | 近似计算                 |
| DOUBLE PRECISION | 8 字节浮点                | 高精度计算                |
| VARCHAR(n)       | 变长字符串，最大 n            | 名称、邮箱                |
| TEXT             | 无限长字符串                | 文章内容                 |
| CHAR(n)          | 定长字符串（空格填充）           | 固定格式编码               |
| BOOLEAN          | 布尔值                   | 标志位                  |
| DATE             | 日期（无时区）               | 生日                   |
| TIME             | 时间（无时区）               | 营业时间                 |
| TIMESTAMP        | 时间戳（无时区）              | 避免使用                 |
| TIMESTAMPTZ      | 时间戳（带时区，推荐）           | 创建时间                 |
| INTERVAL         | 时间间隔                  | '7 days'             |
| UUID             | UUID                  | 分布式 ID               |
| JSON             | JSON 文本               | 避免使用                 |
| JSONB            | 二进制 JSON（可索引，推荐）      | 动态属性                 |
| ARRAY            | 数组                    | TEXT\[\]、INTEGER\[\] |

---

## 4\. 索引

### 创建索引

```sql
-- B-Tree 索引（默认，适合等值/范围查询）
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at DESC);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 复合索引（字段顺序很重要：等值条件列在前，范围条件列在后）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 部分索引（只索引满足条件的行，节省空间）
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- 配合查询：WHERE LOWER(email) = 'alice@example.com'

-- 并发创建索引（不锁表，生产环境推荐）
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);

```

### GIN 索引（JSONB / 数组 / 全文搜索）

```sql
-- JSONB 字段的 GIN 索引
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- 数组字段的 GIN 索引
CREATE INDEX idx_users_tags ON users USING GIN(tags);

-- 全文搜索 GIN 索引
CREATE INDEX idx_articles_fts ON articles USING GIN(to_tsvector('chinese', title || ' ' || content));

```

---

## 5\. 常用查询技巧

### JSONB 查询

```sql
-- 查询 JSONB 字段中的值
SELECT * FROM users WHERE metadata->>'city' = '北京';
SELECT * FROM users WHERE metadata->'address'->>'city' = '北京';

-- 检查 key 是否存在
SELECT * FROM users WHERE metadata ? 'phone';

-- 包含查询（需要 GIN 索引）
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

-- 更新 JSONB 字段
UPDATE users SET metadata = metadata || '{"verified": true}' WHERE id = 1;
UPDATE users SET metadata = jsonb_set(metadata, '{address,city}', '"上海"') WHERE id = 1;

```

### 数组查询

```sql
-- 包含某个元素
SELECT * FROM users WHERE 'python' = ANY(tags);

-- 包含所有指定元素（数组包含）
SELECT * FROM users WHERE tags @> ARRAY['python', 'sql'];

-- 数组有交集
SELECT * FROM users WHERE tags && ARRAY['python', 'golang'];

-- 追加元素
UPDATE users SET tags = array_append(tags, 'rust') WHERE id = 1;

-- 移除元素
UPDATE users SET tags = array_remove(tags, 'php') WHERE id = 1;

```

### CTE（WITH 子句）

```sql
-- 基础 CTE
WITH recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.name, ro.order_count, ro.total
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
ORDER BY ro.total DESC
LIMIT 10;

-- 递归 CTE（用于树形结构）
WITH RECURSIVE category_tree AS (
    -- 起始节点
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归部分
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

```

### 窗口函数

```sql
-- 排名
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

-- 分组排名（每个部门内排名）
SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 移动平均
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 过去 7 天均值
    ) AS moving_avg_7d
FROM daily_stats;

-- 累计求和
SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_stats;

-- LAG / LEAD（访问相邻行）
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day_change
FROM daily_stats;

```

### UPSERT（INSERT ON CONFLICT）

```sql
-- 存在则更新，不存在则插入
INSERT INTO users (email, name, updated_at)
VALUES ('alice@example.com', 'Alice', NOW())
ON CONFLICT (email) DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = EXCLUDED.updated_at;

-- 存在则忽略
INSERT INTO user_tags (user_id, tag)
VALUES (1, 'python')
ON CONFLICT (user_id, tag) DO NOTHING;

```

---

## 6\. 全文搜索

```sql
-- 创建全文搜索向量列
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

-- 填充向量（英文）
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);

-- 创建 GIN 索引
CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);

-- 触发器自动更新
CREATE TRIGGER update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

-- 搜索
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'python & tutorial') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

```

---

## 7\. 性能优化

### EXPLAIN ANALYZE

```sql
-- 查看执行计划（ANALYZE 实际执行，Buffers 查看缓存命中）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'alice@example.com';

-- 关键指标：
-- Seq Scan：全表扫描（慢），应改为 Index Scan
-- Index Scan：走索引（好）
-- rows=1000 actual rows=50000：估算偏差大，需要 ANALYZE 更新统计
-- cost=0.00..100.00：第一行..最后一行的代价

```

### 统计信息更新

```sql
-- 更新表统计信息（优化器依赖）
ANALYZE users;
ANALYZE;  -- 更新所有表

-- 整理表（回收死元组，更新统计）
VACUUM ANALYZE users;

```

### 常见优化点

```sql
-- 慢查询日志（postgresql.conf）
-- log_min_duration_statement = 1000  -- 记录超过 1 秒的查询

-- 查看当前正在执行的查询
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 seconds'
ORDER BY duration DESC;

-- 查看表大小
SELECT
    relname AS table,
    pg_size_pretty(pg_total_relation_size(relid)) AS total,
    pg_size_pretty(pg_relation_size(relid)) AS data,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

```

---

## 8\. Python 连接（asyncpg + SQLAlchemy）

```python
# 推荐：通过 SQLAlchemy / SQLModel 使用
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:password@localhost:5432/mydb",
    pool_size=10,          # 连接池大小
    max_overflow=20,       # 超出 pool_size 后最多额外创建
    pool_timeout=30,       # 等待连接的超时（秒）
    pool_recycle=1800,     # 连接最大存活时间（秒），防止被服务器断开
    echo=False,
)

# 直接用 asyncpg（更底层，性能更好）
import asyncpg

async def main():
    conn = await asyncpg.connect("postgresql://user:pass@localhost/mydb")
    rows = await conn.fetch("SELECT * FROM users WHERE age > $1", 18)
    for row in rows:
        print(dict(row))
    await conn.close()

# 连接池（推荐）
pool = await asyncpg.create_pool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
)
async with pool.acquire() as conn:
    await conn.execute("INSERT INTO users(name) VALUES($1)", "Alice")

```

---

## 最佳实践

**始终使用 TIMESTAMPTZ 而非 TIMESTAMP**：`TIMESTAMP` 不存储时区，跨时区系统会出现时间错乱。`TIMESTAMPTZ` 内部统一存储为 UTC，读取时按连接时区自动转换。

```sql
-- 正确：带时区时间戳
created_at TIMESTAMPTZ DEFAULT NOW()

-- 错误：无时区，跨时区部署时出问题
created_at TIMESTAMP DEFAULT NOW()

```

**用 JSONB 而非 JSON**：`JSON` 存储原始文本，每次访问都需解析；`JSONB` 存储二进制格式，支持 GIN 索引，查询性能高出数倍。唯一区别是 `JSONB` 不保留键的顺序和重复键。

```sql
-- 正确
metadata JSONB

-- 低效
metadata JSON

```

**建索引使用 CONCURRENTLY 避免锁表**：在生产库大表上 `CREATE INDEX` 会锁住整张表，用 `CONCURRENTLY` 可以在不阻塞读写的情况下构建索引（耗时更长，但对业务零影响）。

```sql
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

```

**UPSERT 用 ON CONFLICT 替代先查后插**：先 SELECT 后 INSERT 存在竞态条件（两个并发事务都查到不存在然后都插入），`ON CONFLICT` 是原子操作。

```sql
-- 正确：原子操作
INSERT INTO users (email, name) VALUES ($1, $2)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- 错误：有竞态条件
-- SELECT → (不存在) → INSERT  并发时可能两个都插入

```

**复合索引列顺序遵循左前缀原则**：复合索引 `(a, b, c)` 可以加速 `WHERE a=?`、`WHERE a=? AND b=?`、`WHERE a=? AND b=? AND c=?`，但无法加速单独的 `WHERE b=?` 或 `WHERE c=?`。等值条件列放前面，范围条件列放后面。

```sql
-- 正确：等值在前，范围在后
CREATE INDEX idx_orders ON orders(user_id, status, created_at DESC);

-- 低效：范围在前，等值在后
CREATE INDEX idx_orders ON orders(created_at, user_id);

```

**asyncpg 连接池关键参数**：

| 参数                                  | 类型    | 默认值   | 说明                       |
| ----------------------------------- | ----- | ----- | ------------------------ |
| min\_size                           | int   | 10    | 连接池初始和最小连接数              |
| max\_size                           | int   | 10    | 连接池最大连接数；并发超出时等待         |
| max\_queries                        | int   | 50000 | 单个连接执行的最大查询数，超出后连接被回收重建  |
| max\_inactive\_connection\_lifetime | float | 300.0 | 空闲连接最大存活秒数，超出后关闭以节省服务器资源 |
| command\_timeout                    | float | None  | 单条语句超时秒数；None 表示不限制      |

```python
pool = await asyncpg.create_pool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
    max_inactive_connection_lifetime=300.0,
    command_timeout=60.0,
)

```

---

## 常见陷阱

### 陷阱：LIKE 前缀通配符导致全表扫描

**现象：** `WHERE name LIKE '%alice%'` 查询慢，EXPLAIN 显示 Seq Scan。

**原因：** B-Tree 索引是按字典序排列的有序结构，前缀通配符 `%` 意味着不知道从哪里开始扫描，索引失效。

**解决：** 后缀通配符 `LIKE 'alice%'` 可以走 B-Tree。任意位置模糊搜索需要 `pg_trgm` 扩展的 GIN 索引，或使用全文搜索。

```sql
-- 走索引
WHERE name LIKE 'alice%'

-- 不走索引 → 改用 pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);
WHERE name ILIKE '%alice%'   -- 此时走 GIN 索引

```

---

### 陷阱：SERIAL 主键有间隙被误以为数据丢失

**现象：** 表中主键不连续（1, 2, 5, 6...），怀疑数据被删除或丢失。

**原因：** SERIAL 底层是独立的 SEQUENCE 对象，序列号一旦分配，事务回滚也不会归还。高并发插入、失败事务都会消耗序列号，产生间隙。

**解决：** 这是 PostgreSQL 的设计行为，不是 bug。业务逻辑不应依赖主键连续。如需严格连续编号，用应用层生成或额外的计数表（会带来并发瓶颈）。

---

### 陷阱：忘记 VACUUM 导致表膨胀

**现象：** UPDATE / DELETE 大量数据后，`pg_total_relation_size()` 返回的表大小不减反增，查询越来越慢。

**原因：** PostgreSQL 的 MVCC 机制不会立即删除旧版本行（死元组），而是等待 VACUUM 回收。高频更新的表若 autovacuum 不及时，死元组堆积会使索引和表文件持续膨胀。

**解决：** 监控死元组数量，必要时手动执行 `VACUUM ANALYZE`；对高频更新表调低 autovacuum 阈值。

```sql
-- 查看死元组数量
SELECT relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 手动清理
VACUUM ANALYZE users;

-- 对特定表强化 autovacuum
ALTER TABLE high_update_table SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- 默认 0.2（20% 死元组才触发）
    autovacuum_analyze_scale_factor = 0.005
);

```

---

### 陷阱：连接数耗尽导致服务拒绝新连接

**现象：** 应用日志报 `FATAL: sorry, too many clients already`。

**原因：** PostgreSQL 默认 `max_connections=100`，每个连接占用约 5–10MB 内存。应用未使用连接池，或连接池 `max_size` 设置过大。

**解决：** 使用 asyncpg / SQLAlchemy 的连接池，并在数据库前部署 PgBouncer 连接池代理。

```bash
# postgresql.conf
max_connections = 200          # 根据内存调整，通常不超过 300

# PgBouncer 配置（应对高并发）
pool_mode = transaction        # 事务级连接复用
max_client_conn = 1000
default_pool_size = 20

```

---

## 参见

- [MySQL基础完全指南](https://blog.vercanti.com/mysql-ji-chu-wan-quan-zhi-nan/)
- [MySQL高级优化](https://blog.vercanti.com/mysql-gao-ji-you-hua/)
- [SQLAlchemy完全指南](https://blog.vercanti.com/sqlalchemy-wan-quan-zhi-nan/)
- [SQLModel完全指南](https://blog.vercanti.com/sqlmodel-wan-quan-zhi-nan/)
- [数据库设计规范](https://blog.vercanti.com/shu-ju-ku-she-ji-gui-fan/)
- [DuckDB完全指南](https://blog.vercanti.com/duckdb-wan-quan-zhi-nan/)