PostgreSQL 教程
PostgreSQL 是一个功能强大的开源对象关系型数据库系统，以其可靠性、健壮性和丰富的功能而闻名。本教程将带你从零基础开始学习 PostgreSQL，直到掌握其高级特性。
什么是 PostgreSQL？
PostgreSQL（发音为 "post-gres-Q-L"）是一个开源的对象关系型数据库管理系统（ORDBMS）。它最初由加州大学伯克利分校开发，现在由全球开发者社区维护和贡献。PostgreSQL 的名字来源于它的前身 POSTGRES 项目，其中 "Post" 表示它是 INGRES 数据库项目的后续，而 "greSQL" 则代表它支持 SQL 查询语言。
历史与发展
PostgreSQL 的发展历程是数据库领域最悠久的历史之一，其起源可以追溯到 1986 年：
起源阶段（1986-1994）
PostgreSQL 的前身 POSTGRES 项目由迈克尔·斯通布雷克（Michael Stonebraker）教授在加州大学伯克利分校领导开发。斯通布雷克教授是数据库领域的先驱，他之前还领导开发了 INGRES 数据库。POSTGRES 项目的目标是开发一个支持复杂查询、数据完整性和可扩展性的新一代数据库系统。
POSTGRES 在设计之初就采用了很多创新的理念：
- 支持复杂的数据类型和对象
- 强调数据的完整性和一致性
- 提供可扩展的架构，允许用户定义新的数据类型和函数
SQL 支持阶段（1995-1996）
1995 年，POSTGRES 项目添加了对 SQL 查询语言的支持，并将项目重命名为 PostgreSQL。这一改变使得 PostgreSQL 能够更好地与当时主流的数据库系统兼容，同时也为后来的发展奠定了基础。
社区发展阶段（1996-至今）
1996 年，PostgreSQL 项目正式转向社区驱动的开源开发模式。从那时起，PostgreSQL 进入了一个快速发展的阶段：
- 1997 年：发布 PostgreSQL 6.0，标志着项目进入稳定发展阶段
- 2001 年：实现 ACID 合规性，增强了数据可靠性
- 2005 年：发布 8.0 版本，引入了 Windows 原生支持
- 2010 年：发布 9.0 版本，引入流复制和热备份
- 2012 年：发布 9.2 版本，引入 JSON 支持
- 2017 年：发布 10 版本，引入声明式分区和逻辑复制
- 2024 年：发布 17 版本，引入增量 VACUUM、增强的 JSON 支持
- 2025 年：发布 18 版本，引入异步 I/O 子系统、Skip Scan、虚拟生成列等重大改进
截至 2025 年，PostgreSQL 已经有近 40 年的发展历史，拥有超过 725 名活跃贡献者，提交了超过 61,000 次代码修改，拥有超过 70 个本地用户组织，被全球数百万用户使用。
PostgreSQL 的核心特点
PostgreSQL 被称为"世界上最先进的开源关系型数据库"，这并非空穴来风。它具有以下核心特点：
1. 开源免费
PostgreSQL 采用 PostgreSQL License 发布，这是一种类似 MIT/BSD 的宽松开源许可证。这意味着你可以自由地使用、修改和分发 PostgreSQL，无论是用于个人项目还是商业产品，都无需支付任何费用或开放你的源代码。
2. ACID 合规
PostgreSQL 自 2001 年起完全符合 ACID 标准，确保：
- 原子性（Atomicity）：事务是不可分割的工作单位
- 一致性（Consistency）：事务执行前后数据库保持一致状态
- 隔离性（Isolation）：并发事务之间互不干扰
- 持久性（Durability）：事务提交后结果永久保存
3. 丰富的数据类型
PostgreSQL 支持极其丰富的数据类型：
- 基本类型：整数、浮点数、字符串、布尔值
- 货币类型：MONEY
- 字符类型：CHAR、VARCHAR、TEXT
- 二进制类型：BYTEA
- 日期时间类型：DATE、TIME、TIMESTAMP、INTERVAL
- JSON 类型：JSON、JSONB（二进制存储，更适合查询）
- 数组类型：支持任意类型的数组
- 网络地址类型：INET、CIDR、MACADDR
- 几何类型：点、线、圆、多边形
- 范围类型：INT4RANGE、DATERANGE 等
- UUID 类型：支持 UUIDv7
- 自定义类型：枚举、复合类型、域类型
4. 强大的索引支持
PostgreSQL 提供多种索引类型以适应不同的查询场景：
- B-Tree：默认索引类型，适合等值查询和范围查询
- Hash：适合等值查询
- GiST：适合地理空间数据和全文搜索
- GIN：适合数组和 JSONB 查询
- BRIN：适合大型时序数据的块范围索引
- SP-GiST：空间分区 GiST 索引
5. 全文搜索
内置强大的全文搜索引擎，支持：
- 多语言分词
- 词干提取
- 排名计算
- 高亮显示
- 索引加速
6. JSON 支持
PostgreSQL 对 JSON 的支持是其最受欢迎的特性之一：
- 原生 JSON 和 JSONB 数据类型
- 丰富的 JSON 操作符和函数
- JSONB 支持 GIN 索引，实现高效查询
- SQL/JSON 路径表达式
- JSON_TABLE 函数
7. 多版本并发控制（MVCC）
PostgreSQL 使用 MVCC 来处理并发：
- 读写操作互不阻塞
- 不同事务看到数据的不同版本
- 无需手动加锁即可实现高并发
8. 扩展性
PostgreSQL 的扩展性是其核心优势：
- 自定义函数（支持多种语言：PL/pgSQL、PL/Python、PL/Perl 等）
- 自定义数据类型
- 自定义操作符
- 自定义索引方法
- 扩展模块（如 PostGIS 地理信息扩展）
9. 复制与高可用
提供多种数据复制和备份方案：
- 流复制（同步/异步）
- 逻辑复制
- 级联复制
- 时间点恢复（PITR）
- 外部数据包装器（FDW）
10. 安全性
完善的安全机制：
- 基于角色的权限控制
- 行级安全策略
- SSL/TLS 加密连接
- 密码加密存储
- 审计日志
PostgreSQL 18 新特性（2025年发布）
PostgreSQL 18 于 2025 年 9 月发布，带来了多项重大改进：
性能提升：
- 异步 I/O 子系统：全新的异步 I/O 系统可以显著提升顺序扫描、位图堆扫描、VACUUM 等操作的性能
- Skip Scan 索引查找：允许在更多情况下使用多列 B-Tree 索引，即使没有对前列设置限制条件
- 并行 GIN 索引创建：GIN 索引现在可以并行创建，大幅缩短索引构建时间
开发者友好：
- 虚拟生成列：生成列现在可以在读取时计算值，成为默认行为（之前是 STORED 模式）
- RETURNING 支持 OLD/NEW：INSERT、UPDATE、DELETE、MERGE 的 RETURNING 子句现在可以使用 OLD 和 NEW 别名
- 时间约束：PRIMARY KEY、UNIQUE 和 FOREIGN KEY 约束支持 WITHOUT OVERLAPS，实现时间段约束
安全增强：
- OAuth 认证支持：新增 OAuth 认证方法，支持现代身份认证流程
- MD5 密码弃用：MD5 密码认证已被弃用，建议迁移到 SCRAM-SHA-256
运维改进：
- pg_upgrade 保留优化器统计信息：升级时不再需要重新收集统计信息
- 改进的监控能力：新增多个系统视图和函数用于监控 I/O、WAL 等活动
PostgreSQL 17 新特性（2024年发布）
PostgreSQL 17 于 2024 年 9 月发布，主要改进包括：
性能优化：
- 增量 VACUUM：大幅减少 VACUUM 的工作量，特别是对于大型表
- 双向索引：B-Tree 索引可以反向扫描，减少索引数量需求
- 并行查询增强：更多类型的查询可以并行执行
SQL/JSON 增强：
- JSON_TABLE()：将 JSON 数据转换为表格式
- SQL/JSON 构造函数：支持 JSON_OBJECT、JSON_ARRAY 等
- JSON 路径表达式增强：更强大的 JSON 查询能力
存储优化：
- 压缩改进：更好的 TOAST 压缩算法
- 分区表改进：支持更多分区操作
SQL 标准合规性
PostgreSQL 致力于遵循 SQL 标准，同时保持向后兼容。截至 PostgreSQL 18 版本（2025年发布），PostgreSQL 符合 SQL:2023 核心标准的 177 个强制性特性中的至少 170 个。在撰写本文时，还没有任何关系型数据库完全符合这一标准。
PostgreSQL 与 MySQL 的对比
| 特性 | PostgreSQL | MySQL |
|---|---|---|
| 许可证 | PostgreSQL License (MIT 风格) | GPL |
| 标准遵循 | 高度符合 SQL 标准 | 部分符合 |
| 事务支持 | 完整支持（支持 SAVEPOINT） | 支持（InnoDB 引擎） |
| JSON 支持 | 原生支持 JSON 和 JSONB | 支持 JSON 类型 |
| 存储过程 | 支持（多种语言） | 支持 |
| 触发器 | 支持（事件触发器） | 支持 |
| 物化视图 | 支持 | 不支持 |
| 外部表 | 支持 | 不支持（通过 FDW） |
| 并发控制 | MVCC | MVCC（InnoDB） |
PostgreSQL 的应用场景
PostgreSQL 适用于各种应用场景：
- 企业级应用：金融、医疗、政府等需要高度数据完整性的系统
- 数据分析：复杂查询、窗口函数、OLAP 场景
- 地理信息系统（GIS）：通过 PostGIS 扩展支持地理空间数据
- JSON/Web 应用：需要存储半结构化数据的现代 Web 应用
- 科学计算：处理大规模科学数据
- 金融系统：需要 ACID 特性的交易系统
本教程的学习路径
本教程将按照以下顺序帮助你系统学习 PostgreSQL：
基础篇
- 安装和环境配置：在不同操作系统上安装 PostgreSQL
- 基础语法：了解 SQL 基本语法和数据类型
- 表操作：创建、修改、删除表
- 数据查询：SELECT 语句和查询优化
- 数据操作：INSERT、UPDATE、DELETE
进阶篇
- 连接查询：内连接、左连接、右连接等
- 聚合函数：COUNT、SUM、AVG、MAX、MIN
- 子查询：嵌套查询和常用技巧
- 视图：虚拟表的创建和使用
- 索引：提升查询性能的利器
高级篇
- 存储过程和函数：业务逻辑的封装
- 触发器：自动化数据处理
- 事务控制：保证数据一致性
- 并发控制：MVCC 和锁机制
- 复制和高可用：主从复制、读写分离
运维篇
- 备份和恢复：确保数据安全
- 性能优化：查询分析和性能调优
- 安全管理：用户权限和网络安全
为什么选择 PostgreSQL？
- 可靠性：经过 30 多年的发展，PostgreSQL 以其稳定性著称
- 社区活跃：拥有活跃的开发者社区和丰富的文档
- 企业级特性：提供许多企业级功能
- 生态系统丰富：有大量的扩展（如 PostGIS、pgAdmin 等）
- 发展趋势：近年来 PostgreSQL 的受欢迎程度持续上升
准备工作
在学习本教程之前，你需要具备以下基础：
- 基本的 SQL 知识（了解什么是数据库、表、字段）
- 了解命令行操作
- 已安装 PostgreSQL 或有访问 PostgreSQL 服务器的方式
如果你的系统上还没有安装 PostgreSQL，请先查看 安装指南 进行安装。
开始学习
现在，让我们开始 PostgreSQL 学习之旅！首先了解如何 安装 PostgreSQL，然后逐步深入学习各个知识点。
- 动手实践：每学习一个知识点，都在数据库中实际操作
- 理解原理：不仅知道怎么用，还要理解为什么这样设计
- 结合文档：学会查阅 PostgreSQL 官方文档
- 解决问题：在实际项目中应用所学知识
通过本教程，你将掌握 PostgreSQL 的核心概念和实用技能，能够独立进行数据库设计、开发和管理工作。