PostgreSQL 基础教程

下面是一份 “PostgreSQL 详细介绍（超完整、实战友好、架构级别）”，覆盖：

PostgreSQL 是什么
架构与底层原理
存储模型
事务模型（MVCC）
锁机制
查询执行流程
索引类型
分区与扩展性
并行查询
高可用与集群
实际使用场景
最佳实践
与 MySQL、Oracle 的对比

1. PostgreSQL 是什么？

PostgreSQL（简称 Postgres）是当前 最先进的开源关系型数据库之一，同时也是最像 Oracle 的数据库，被称为：

最强开源数据库
世界上功能最完整的关系数据库

特点：

真正的 企业级 数据库
支持 SQL + JSON 混合模型
完整的事务、锁、MVCC
丰富索引类型（远超 MySQL）
可扩展（插件化、扩展、函数）
高水平查询优化器（比 MySQL 强很多）
可作为 OLTP + OLAP 混合场景 选择
PostgreSQL 17 更快，更强，更现代

2. PostgreSQL 体系结构（Architecture）

核心组件：

Client
  |
  | SQL / Protocol
  |
PostgreSQL Server (postgres)
  |
  +-- Parser（语法解析）
  +-- Planner / Optimizer（优化器）
  +-- Executor（执行器）
  +-- Storage Manager（存储引擎）
  +-- WAL（Write-Ahead Log）
  +-- Background Workers（后台进程）
       - checkpointer
       - writer
       - wal writer
       - autovacuum launcher
       - logical replication worker
       - bgworker 扩展

PostgreSQL 是 单进程多子进程模型（不像 MySQL，是单进程多线程）。

一个客户端连接，对应 一个独立进程。

3. PostgreSQL 存储模型

主要文件：

文件	意义
data/base/xxx/yyy	表的数据文件（堆表 Heap）
pg_wal/	写前日志（保证持久化）
pg_xact	事务提交信息（Transaction Status）
pg_multixact	多事务锁信息
pg_statistic	统计信息

关键机制：TOAST（The Oversized-Attribute Storage Technique）

用于存储大文本、大 JSON、大对象，大于 2KB 时自动拆分。

4. PostgreSQL 的 MVCC（多版本并发控制）

PostgreSQL 采用 MVCC + 无锁读（read without lock） 机制。

核心思想：

每行数据包含两个隐藏字段：
- xmin（创建事务 ID）
- xmax（删除事务 ID）
不更新行，而是创建新版本行 -> 多版本
查询会根据事务快照选择可见版本
不需要加读锁 -> 并发性能极高

PostgreSQL MVCC 的优点：

查询不会阻塞写
写不会阻塞读（除非冲突行锁）
高并发下性能稳定

缺点：

会产生大量“死行” -> 依赖 VACUUM 清理

5. VACUUM 与自动清理

VACUUM 用于：

回收死行空间
防止事务 ID wraparound
更新统计信息（ANALYZE）

PostgreSQL 有 autovacuum，无需人工维护。

6. PostgreSQL 锁机制（Locking）

锁类型丰富，是企业级数据库核心能力：

锁类型	用途
Row lock（行锁）	UPDATE、DELETE
Table lock（表锁）	DDL、VACUUM
Advisory lock（咨询锁）	应用层分布式锁
LWLock	内部轻量锁
SpinLock	内部自旋锁
Predicate Lock	Serializable 隔离性

PostgreSQL 行锁之间兼容度更高，死锁更少。

7. PostgreSQL 索引类型（非常强）

PostgreSQL 的索引比 MySQL 强太多：

类型	场景
B-tree	主键、普通索引
Hash	等值匹配（很少用）
GIN	JSON、数组、全文搜索
GiST	地理位置、模糊匹配
BRIN	海量顺序数据（时序）
SP-GiST	树、图、空间结构

尤其 GIN/GiST/BRIN 是 PostgreSQL 独有的优势。

8. PostgreSQL 查询流程

SQL
 ↓
Parser（词法语法分析）
 ↓
Rewriter（规则重写）
 ↓
Planner / Optimizer（生成执行计划）
 ↓
Executor（执行器）
 ↓
返回结果

优化器比 MySQL 更强，支持：

Hash Join
Merge Join
Nested Loop Join
并行查询
Bitmap Scan
上百种成本估计策略

9. PostgreSQL 分区表（Partitioning）

支持三种：

RANGE（按范围，例如 date）
LIST（按枚举，例如 province）
HASH（均匀分布）

特点：

分区是 “物理表”
分区裁剪提高查询性能
适用于 TB 级数据

10. 扩展能力（Postgres 最大优势之一）

PostgreSQL 能加载扩展：

CREATE EXTENSION timescaledb;
CREATE EXTENSION postgis;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gin;
CREATE EXTENSION citext;
...

知名扩展：

扩展	作用
TimescaleDB	时序数据库引擎
PostGIS	地理空间数据库
pg_trgm	模糊匹配（相似度）
uuid-ossp	UUID
citext	不区分大小写字符串
tablefunc	pivot/crosstab

扩展生态极其丰富，是 PostgreSQL 最大杀手级优势。

11. 高可用与集群方案

PostgreSQL 自带流复制（Streaming Replication）：

Primary <- WAL -> Standby

典型方案：

方案	特点
Patroni	最强、最成熟、企业级
pgpool-II	连接池 + 负载均衡
repmgr	主从管理
Native Logical Replication	分表同步、灵活

企业推荐：

Patroni + etcd + HAProxy

高度数据一致性集群，答案就是 Patroni。

12. PostgreSQL 应用场景

高度事务性系统（比 MySQL 更稳）

银行
ERP
订单系统
金融核心系统

大量 JSON 与结构化混合查询

后端 API
配置管理
用户自定义字段

时序数据（TimescaleDB）

监控（Zabbix）
传感器数据
股票行情（你正在做的 up_resaon 类表）

地理系统

GIS、地图（PostGIS）

海量数据分析（OLAP 混合）

支持并行查询、向量化扫描。

13. PostgreSQL 与 MySQL 比较

特性	PostgreSQL	MySQL
SQL 标准	✅ 最高	中等
JSON	✅ 强（jsonb）	弱
事务	✅ 强（MVCC+锁）	中
扩展能力	✅ 超强	弱
索引类型	✅ 很多	少
查询优化器	✅ 强	一般
ACID 一致性	✅ 强	中
聚合&窗口函数	✅ 完整	部分
并行查询	✅ 强	弱
分区表	✅ 成熟	一般
时序数据库	✅ TimescaleDB	❌ 无

14. PostgreSQL 最佳实践（总结）

默认字符类型用 text
JSON 用 jsonb
索引尽量用 BTREE，优化器最熟
大表用分区（RANGE）
时序表用 TimescaleDB
VACUUM 参数调优
使用连接池（PgBouncer）
使用 autovacuum，不手动 vacuum full
主从复制用 Patroni