数据库三剑客 · 第6节 · PostgreSQL 完整章节

PostgreSQL 完整章节

PostgreSQL 17 · The Most Advanced Open Source DB

PostgreSQL 自称"世界上最先进的开源数据库"，这不是吹牛。它标准 SQL 兼容最好、数据类型最丰富、扩展生态最强，近几年因为 pgvector（AI/RAG）和 PostGIS（地理空间）火得一塌糊涂。如果你想在一个数据库里同时搞定业务、JSON、向量检索、地图分析，PG 就是答案。

4.1 PostgreSQL 简介与安装

引子：PG 源自加州伯克利的 POSTGRES 项目，1986 年起步，比 MySQL 还早。它走的是"学术派、标准派"路线——SQL 标准怎么写，它就怎么实现。这让它在复杂查询、严格场景下特别可靠。

安装：apt / yum / brew / Docker

# macOS（Homebrew）
brew install postgresql@17
brew services start postgresql@17

# Ubuntu
sudo apt install -y postgresql-17

# Docker（最省事）
docker run -d --name pg17 \
  -e POSTGRES_PASSWORD='Pg_2026' \
  -p 5432:5432 \
  postgres:17

# 客户端：psql 命令行 / pgAdmin（官方图形）/ DBeaver
psql -h localhost -U postgres

psql 常用元命令（不用写 SQL 就能看库结构）

postgres=# \l <-- 列出所有库 postgres=# \c shop <-- 切换到 shop 库 shop=# \dt <-- 列出当前库的表 shop=# \d employees <-- 看 employees 表结构 shop=# \dn <-- 列出 schema shop=# \q <-- 退出

两个关键配置文件（和 MySQL 不一样）
文件	管什么
`postgresql.conf`	数据库主配置：端口（默认 5432）、内存、连接数、WAL 等。
`pg_hba.conf`	访问控制：谁（IP/用户）能用什么方式（密码/证书）连进来。配错了就拒连。

4.2 SQL 基础：PG 的"特色武器"

PG 的 SQL 基础和标准一致，但它有一堆别家没有或不优雅的特色语法，写起来特别爽。先看数据类型：

PostgreSQL 的丰富数据类型
类型	干什么
`SERIAL / BIGSERIAL`	自增主键（底层就是序列 + 默认值）。
`TEXT / VARCHAR`	PG 里 TEXT 和 VARCHAR 性能一样，直接用 TEXT 最省心。
`NUMERIC(p,s)`	精确小数，钱用它。
`JSON / JSONB`	JSON 文档；JSONB 是二进制存储，能建索引、能内部查询，生产首选 JSONB。
`数组类型`	`INT[]`、`TEXT[]`，一列存一串值。
`UUID`	原生存 UUID，分布式主键友好。
`范围类型`	`int4range`、`daterange`，存一段区间，查重叠特方便。
`几何类型`	`point`、`circle`、`polygon`，加 PostGIS 后就是完整 GIS。
`枚举`	`CREATE TYPE mood AS ENUM ('sad','ok','happy')`。

PG 特色语法：RETURNING / UPSERT / CTE / 窗口函数

-- 1. RETURNING：INSERT/UPDATE/DELETE 完直接返回被影响的行（MySQL 没有，超好用）
INSERT INTO users(name, email) VALUES('张三','a@x.com') RETURNING id, created_at;

-- 2. UPSERT：存在就更新，不存在就插入（ON CONFLICT）
INSERT INTO users(id, name) VALUES(1,'张三')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- 3. CTE WITH：把复杂查询拆成小段；WITH RECURSIVE 做递归（查组织架构、菜单树）
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS lvl FROM emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.lvl+1
  FROM emp e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

-- 4. 窗口函数：分组内排序/排名（比子查询优雅太多）
SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM emp;

-- 5. DISTINCT ON：每组只留第一行（PG 独门）
SELECT DISTINCT ON (dept) dept, name, salary
FROM emp ORDER BY dept, salary DESC;

-- 6. 分页
SELECT * FROM emp ORDER BY id LIMIT 10 OFFSET 20;

PG 和 MySQL 自增主键的迁移坑

PG 的 SERIAL 在表删空后不会重置计数器；TRUNCATE 会重置，DELETE FROM 不会。从 MySQL 迁数据进 PG 时，记得把序列 setval 到最大值，否则新插入会和已导入的主键冲突报错。

4.3 索引与优化：种类最丰富的一家

PG 的索引种类是三家里最多的，因为它要对付数组、JSON、几何这些奇葩数据。

PostgreSQL 索引家族
索引类型	用在哪
B 树	默认，等值/范围/排序。
哈希	只支持等值查询。
GiST	几何、范围、最近邻搜索（PostGIS 靠它）。
GIN	倒排索引，JSONB、数组、全文检索的标配。
BRIN	块级摘要索引，超大表（日志、时间序列）轻量级索引。
表达式 / 部分索引	对表达式建索引；只对满足条件的行建索引（如只索引未删除的行）。

JSONB 索引 + 看执行计划 + 慢查询

# JSONB 列建 GIN 索引，查内部字段飞快
CREATE INDEX idx_data_gin ON users USING GIN (data);
SELECT * FROM users WHERE data->>'city' = '南京';

# EXPLAIN ANALYZE：真实跑一遍，看每步实际行数和耗时（比 MySQL EXPLAIN 更硬核）
EXPLAIN ANALYZE SELECT * FROM emp WHERE dept_id=3;

# 收集统计信息（优化器要靠它估算）
ANALYZE emp;

# 慢查询阈值：超过 200ms 就记日志
# postgresql.conf 里： log_min_duration_statement = 200

论VACUUM 与死元组：PG 的家务事

PG 的 MVCC 和 MySQL 不一样：更新/删除一行时，它不物理覆盖旧行，而是留一个"死元组"（dead tuple），旧版本给老事务看。这些死元组会越积越多，表越来越胖，必须用 VACUUM 清理回收空间。PG 有个 autovacuum 后台进程自动干这事，但大表高写入时你可能还是得手动调一调。这是 PG 运维和 MySQL 最大的不同之一。

4.4 事务与隔离级别

PG 也讲 ACID，也用 MVCC，默认隔离级别是读已提交（READ COMMITTED）。它支持 SQL 标准的全部四种隔离级别，还提供一个独门武器——advisory lock（咨询锁）：应用层自己约定一个 key 来加锁，不锁任何数据行，适合做"分布式任务选主""防重复提交"这种场景。

-- 事务
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id=1;
UPDATE account SET balance = balance + 100 WHERE id=2;
COMMIT;   -- 或 ROLLBACK;

-- SAVEPOINT
SAVEPOINT sp1;
ROLLBACK TO sp1;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4.5 PL/pgSQL 与扩展生态

PG 的存储过程语言叫 PL/pgSQL，和 PL/SQL 神似。但 PG 真正可怕的是它的扩展（Extension）生态——一个 CREATE EXTENSION 就能给数据库加一门新本事。

常用扩展（AI/GIS 必备）

# 开启扩展（很多 contrib 版自带）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  # 统计 SQL 性能
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";          # 生成 UUID
CREATE EXTENSION IF NOT EXISTS postgis;               # 地理空间（GIS）
CREATE EXTENSION IF NOT EXISTS vector;                # pgvector：AI/RAG 向量检索！

# 建一个向量列，存 1536 维 embedding（OpenAI 风格）
CREATE TABLE items (
  id BIGINT PRIMARY KEY,
  embedding vector(1536)
);
# 找和某向量最相近的 5 条（<-> 是 L2 距离，越小越像）
SELECT id FROM items ORDER BY embedding <-> '[...1536个数...]' LIMIT 5;

# 数据量大了要建向量近似索引（HNSW），否则全量算距离很慢
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

为什么 AI 圈都在捧 PostgreSQL + pgvector

做 RAG 以前要同时维护一个业务库（MySQL）+ 一个向量库（Milvus/Pinecone），两边数据还要同步，很麻烦。PG + pgvector 让你在同一个事务、同一个数据库里同时存业务数据和向量，还能 JOIN，运维成本骤降。中小规模 RAG 这是最香的方案。规模再大才考虑专门向量库。

4.6 备份恢复：pg_dump 与 PITR

逻辑备份 pg_dump / pg_dumpall

# 备份单库（自定义压缩格式，可并行恢复）
pg_dump -U postgres -Fc shop > shop.dump

# 备份所有库（含角色、表空间）
pg_dumpall -U postgres > all.sql

# 恢复（自定义格式用 pg_restore）
pg_restore -U postgres -d shop -j 4 shop.dump

物理备份 + 时间点恢复（PITR）

# 基础物理备份（在线，不锁库）
pg_basebackup -D /backup/base -U replicator

# 持续归档 WAL（postgresql.conf），是 PITR 的前提
wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'

# 恢复时：还原基础备份 + 配置 recovery_target_time + 重放 WAL 到指定时刻

4.7 高可用与集群

PostgreSQL 高可用生态
组件	干什么
流复制 Streaming Replication	主库把 WAL 实时发给从库。物理复制（块级）或逻辑复制（按表/发布订阅）。
Patroni + etcd	主流高可用方案：Patroni 管主从自动故障切换，etcd 做分布式配置协调。
repmgr	另一套主从管理工具。
pgpool-II	中间件：连接池 + 读写分离 + 查询路由。
PgBouncer	超轻量连接池。PG 每个连接耗内存，高并发下必上 PgBouncer 复用连接。
分区表	按范围/列表/哈希分区，大表拆小，只扫相关分区。

流复制一句话原理

# 主库：建复制用户、开 WAL 发送
CREATE ROLE replicator REPLICATION LOGIN;
# postgresql.conf: wal_level=replica, max_wal_senders>0

# 从库：用 pg_basebackup 拉一份主库基线，放好 recovery 配置后启动，自动连主库收 WAL
pg_basebackup -D /pgdata -h 主库IP -U replicator -R -P

# -R 会自动生成 standby.signal 和连接信息，从库启动即开始持续收流

4.8 PostgreSQL 面试重点 15 题

PostgreSQL 面试 15 题（折叠答案）

1. PostgreSQL 和 MySQL 你选谁？

答案

简单业务、快速上线、生态熟手多选 MySQL；复杂查询、JSON/向量/GIS、要求标准兼容和扩展性选 PG。

2. JSON 和 JSONB 区别？

答案

JSON 原文存储，保留空格/重复键；JSONB 二进制存储，去掉冗余，能建 GIN 索引、能内部字段查询。生产用 JSONB。

3. pgvector 是什么？

答案

PG 的向量检索扩展，让 PG 能存 embedding 并做相似度搜索，是 AI/RAG 的常用方案。

4. SERIAL 和自增序列？

答案

SERIAL 是语法糖，底层自动建一个序列并设为列默认值。分布式环境更推荐 UUID 或 BIGINT+雪花。

5. RETURNING 子句有什么用？

答案

DML 后直接返回被影响行的列（如插入后的 id），省去再查一次。MySQL 8.0 后也支持 RETURNING。

6. ON CONFLICT 是什么？

答案

UPSERT：唯一冲突时改行为更新（DO UPDATE）或忽略（DO NOTHING），对应 MySQL 的 ON DUPLICATE KEY UPDATE。

7. GIN 和 GiST 索引区别？

答案

GIN 是倒排索引，适合 JSONB、数组、全文；GiST 适合几何、范围、最近邻。BRIN 是超大表轻量索引。

8. 什么是 VACUUM？为什么需要？

答案

PG MVCC 不物理覆盖旧行，留死元组；VACUUM 清理死元组回收空间。autovacuum 自动做，但要监控。

9. EXPLAIN 和 EXPLAIN ANALYZE 区别？

答案

EXPLAIN 只展示预估计划；EXPLAIN ANALYZE 真实执行一遍并打印每步实际行数和耗时。

10. PG 默认隔离级别？

答案

读已提交 READ COMMITTED。也支持可重复读、串行化。

11. advisory lock 是什么？

答案

咨询锁，由应用层约定 key 加锁，不锁数据行，适合选主、防重。

12. PgBouncer 干什么？

答案

轻量连接池。PG 每连接开销大，高并发时用它把多个应用连接复用到少量数据库连接上。

13. 流复制是什么？

答案

主库把 WAL 实时流式发给从库重放，实现主从同步，支持物理复制和逻辑复制。

14. CTE（WITH）和子查询区别？

答案

CTE 把查询命名拆开更清晰，可被引用多次，WITH RECURSIVE 支持递归。PG 的 CTE 还能配合 MATERIALIZED。

15. 分区表有哪几种？

答案

范围分区（按时间/数值区间）、列表分区（按枚举值）、哈希分区（均匀打散）。

← 上一节数据库三剑客 · 课程总览下一节 →