Matarialize中文社区-PostgreSQL 从零到精通学习大纲

PostgreSQL 从零到精通学习大纲

落叶 2026-04-17 09:14:25

1 PostgreSQL,数据库专栏

第一阶段：入门基础 (1-2周)

1. PostgreSQL 简介与安装

1.1 PostgreSQL 概述
- PostgreSQL 历史与发展
- 特性与优势（ACID、扩展性、开源协议）
- 与其他数据库对比（MySQL、Oracle、SQL Server）
1.2 安装部署
- Windows 安装
- Linux (Ubuntu/CentOS) 安装
- macOS 安装
- Docker 容器化部署
- 源码编译安装
1.3 客户端工具
- psql 命令行工具
- pgAdmin 图形界面
- DBeaver、DataGrip 等第三方工具

2. 基础架构与配置

2.1 目录结构
- 数据目录（PGDATA）
- 配置文件位置
- 表空间目录
2.2 核心配置文件
- postgresql.conf（主配置）
- pg_hba.conf（客户端认证）
- pg_ident.conf（用户映射）
2.3 服务管理
- 启动/停止/重启
- 服务状态查看
- 日志管理

第二阶段：SQL 语言精通 (3-4周)

3. SQL 基础

3.1 数据定义语言（DDL）
- CREATE/DROP DATABASE
- CREATE/ALTER/DROP TABLE
- CREATE/ALTER/DROP INDEX
- CREATE/ALTER/DROP VIEW
- 约束管理（PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL）
3.2 数据操作语言（DML）
- SELECT 查询基础
  - WHERE 子句
  - ORDER BY 排序
  - LIMIT/OFFSET 分页
  - DISTINCT 去重
- INSERT 插入数据
- UPDATE 更新数据
- DELETE 删除数据
- MERGE/UPSERT (INSERT ON CONFLICT)
3.3 数据查询语言（DQL）高级
- 聚合函数（COUNT、SUM、AVG、MAX、MIN）
- GROUP BY 分组
- HAVING 过滤分组
- 子查询（标量、行、表子查询）
- 联合查询（UNION、INTERSECT、EXCEPT）

4. 高级 SQL 特性

4.1 连接查询
- INNER JOIN
- LEFT/RIGHT/FULL OUTER JOIN
- CROSS JOIN
- SELF JOIN
- NATURAL JOIN
4.2 窗口函数
- ROW_NUMBER、RANK、DENSE_RANK
- LAG、LEAD、FIRST_VALUE、LAST_VALUE
- SUM/AVG OVER(PARTITION BY)
- 滑动窗口
4.3 公共表表达式（CTE）
- 普通 CTE
- 递归 CTE（树形结构查询）
- 物化 CTE
4.4 高级查询技术
- 条件逻辑（CASE、COALESCE、NULLIF）
- 批量操作（批量插入、更新、删除）
- 返回子句（RETURNING）

第三阶段：数据类型与函数 (2-3周)

5. 数据类型详解

5.1 数值类型
- 整数类型（SMALLINT、INTEGER、BIGINT）
- 浮点类型（REAL、DOUBLE PRECISION）
- 精确数值（NUMERIC、DECIMAL）
- 序列类型（SMALLSERIAL、SERIAL、BIGSERIAL）
5.2 字符类型
- CHAR(n)、VARCHAR(n)、TEXT
- 字符编码与排序规则
5.3 日期时间类型
- DATE、TIME、TIMESTAMP
- INTERVAL（时间间隔）
- 时区处理（TIMESTAMPTZ）
5.4 布尔类型
- TRUE、FALSE、NULL
5.5 二进制类型
- BYTEA
- 大对象（Large Objects）
5.6 网络地址类型
- INET、CIDR、MACADDR
5.7 JSON/JSONB 类型
- JSON 操作符（->、->>、#>、#>>）
- JSONB 索引
- JSON 函数与操作
5.8 数组类型
- 一维/多维数组
- 数组函数与操作符
5.9 范围类型
- INT4RANGE、INT8RANGE、NUMRANGE
- TSRANGE、TSTZRANGE、DATERANGE
5.10 几何类型
- POINT、LINE、LSEG、BOX、PATH、POLYGON、CIRCLE
5.11 复合类型
- 自定义复合类型
- 复合类型的使用

6. 函数与操作符

6.1 数学函数
6.2 字符串函数
6.3 日期时间函数
6.4 类型转换函数
6.5 条件函数
6.6 聚合函数
6.7 窗口函数
6.8 自定义函数

第四阶段：数据库设计 (2-3周)

7. 数据库设计理论

7.1 设计原则
- 需求分析
- 概念设计（ER 图）
- 逻辑设计（关系模式）
- 物理设计（存储结构）
7.2 规范化理论
- 第一范式（1NF）
- 第二范式（2NF）
- 第三范式（3NF）
- BC范式（BCNF）
- 第四/第五范式
- 反规范化设计
7.3 表设计最佳实践
- 主键设计策略（自然键 vs 代理键）
- 外键约束使用
- 检查约束应用
- 默认值设置
- NULL 值处理策略

8. 索引设计

8.1 索引类型
- B-Tree 索引（默认）
- Hash 索引
- GiST 索引
- SP-GiST 索引
- GIN 索引（全文搜索、JSONB）
- BRIN 索引（大表）
- 部分索引
- 表达式索引
8.2 索引策略
- 单列索引
- 复合索引（多列索引）
- 唯一索引
- 覆盖索引
- 索引与排序
8.3 索引维护
- 索引创建（CONCURRENTLY）
- 索引重建
- 索引统计信息
- 索引使用情况分析

9. 视图与物化视图

9.1 普通视图
9.2 物化视图
- 创建与刷新策略
- 并发刷新
- 物化视图索引

10. 数据库架构设计

10.1 模式（Schema）管理
- 公共模式
- 私有模式
- 跨模式访问
10.2 分区表
- 范围分区
- 列表分区
- 哈希分区
- 分区维护与管理
10.3 继承表

第五阶段：权限管理 (1-2周)

11. 用户与角色管理

11.1 角色概念
- 登录角色 vs 组角色
- 角色属性（LOGIN、SUPERUSER、CREATEDB、CREATEROLE）
11.2 角色管理
- CREATE/ALTER/DROP ROLE
- 角色继承
- 默认角色

12. 权限体系

12.1 权限类型
- 系统权限（CREATE、CONNECT、TEMPORARY）
- 对象权限（SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER）
- 模式权限（USAGE、CREATE）
- 数据库权限（CONNECT、CREATE、TEMPORARY）
12.2 权限管理命令
- GRANT（授予权限）
- REVOKE（回收权限）
- 权限查看（\dp、\z）
12.3 行级安全策略（RLS）
- 策略创建与启用
- USING 与 WITH CHECK 表达式
- 策略类型（PERMISSIVE、RESTRICTIVE）

13. 连接安全

13.1 pg_hba.conf 配置
- 认证方法（trust、reject、md5、scram-sha-256）
- 连接规则（local、host、hostssl、hostnossl）
13.2 SSL/TLS 配置
- 证书生成
- 强制加密连接

第六阶段：性能优化 (3-4周)

14. 查询优化

14.1 执行计划分析
- EXPLAIN 与 EXPLAIN ANALYZE
- 执行计划解读（Seq Scan、Index Scan、Bitmap Scan）
- 代价估算（启动成本、总成本、行数、宽度）
14.2 查询重写技巧
- 避免隐式类型转换
- 优化 JOIN 顺序
- 子查询优化（EXISTS vs IN）
- 避免 SELECT *
- 使用 LIMIT 减少结果集
14.3 统计信息
- ANALYZE 命令
- 统计信息查看（pg_stats）
- 统计信息设置（default_statistics_target）

15. 服务器配置优化

15.1 内存配置
- shared_buffers（共享缓冲区）
- effective_cache_size（有效缓存大小）
- work_mem（工作内存）
- maintenance_work_mem（维护工作内存）
- huge_pages（大页内存）
15.2 磁盘 I/O 优化
- random_page_cost（随机页成本）
- effective_io_concurrency（有效 I/O 并发）
- 表空间分布
15.3 写入优化
- wal_buffers（WAL 缓冲区）
- checkpoint 配置
- fsync 与 full_page_writes
15.4 连接管理
- max_connections（最大连接数）
- 连接池配置（PgBouncer）

16. 事务与并发控制

16.1 MVCC 机制
- 事务ID（XID）
- 元组可见性规则
- 快照隔离级别
16.2 事务隔离级别
- READ UNCOMMITTED
- READ COMMITTED（默认）
- REPEATABLE READ
- SERIALIZABLE
16.3 锁机制
- 表级锁（ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE）
- 行级锁
- 死锁检测与处理
- advisory locks

17. 数据库维护

17.1 VACUUM 与清理
- VACUUM 工作原理
- 死元组处理
- VACUUM vs VACUUM FULL
- AUTOVACUUM 配置
17.2 日志管理
- WAL 日志归档
- 日志参数配置（log_destination、log_directory）
- 慢查询日志

第七阶段：高级特性 (2-3周)

18. 存储过程与触发器

18.1 PL/pgSQL
- 语法结构
- 变量与常量
- 控制结构（IF、CASE、LOOP、WHILE、FOR）
- 异常处理
18.2 函数与存储过程
- 函数创建与调用
- 存储过程创建与调用
- 返回值类型
- 事务控制
18.3 触发器
- 触发器类型（BEFORE、AFTER、INSTEAD OF）
- 触发器事件（INSERT、UPDATE、DELETE、TRUNCATE）
- 行级与语句级触发器
- 条件触发器
18.4 事件触发器
- DDL 事件捕获

19. 全文搜索

19.1 全文搜索基础
- 文本解析（tsvector、tsquery）
- 搜索配置
19.2 搜索功能
- @@ 操作符
- 排名函数（ts_rank、ts_rank_cd）
- 高亮显示（ts_headline）
- 字典与同义词

20. 扩展模块

20.1 常用扩展
- pg_stat_statements（查询统计）
- pg_trgm（模糊匹配）
- uuid-ossp（UUID 生成）
- PostGIS（地理空间）
- hstore（键值存储）
- pgcrypto（加密函数）
20.2 扩展管理
- CREATE EXTENSION
- 扩展更新与删除

第八阶段：备份恢复与高可用 (2-3周)

21. 备份与恢复

21.1 逻辑备份
- pg_dump（单数据库）
- pg_dumpall（全部数据库）
- 自定义格式备份
- 并行备份
21.2 物理备份
- 冷备份
- 热备份（pg_basebackup）
- 文件系统快照
21.3 时间点恢复（PITR）
- WAL 归档配置
- 基础备份 + WAL 恢复
- 恢复到指定时间点
21.4 恢复策略
- 完全恢复
- 部分恢复
- 灾难恢复演练

22. 高可用与集群

22.1 流复制
- 主从复制原理
- 异步复制
- 同步复制
- 级联复制
22.2 高可用方案
- Patroni + etcd/Consul
- Repmgr
- PGPool-II
- 自动故障转移
22.3 读写分离
- 连接池配置
- 负载均衡策略
22.4 分布式方案
- Citus（水平分片）
- Postgres-XL

第九阶段：监控与运维 (1-2周)

23. 监控体系

23.1 系统视图与统计信息
- pg_stat_database（数据库统计）
- pg_stat_tables（表统计）
- pg_stat_activity（会话监控）
- pg_stat_replication（复制监控）
- pg_locks（锁监控）
23.2 监控工具
- Prometheus + Grafana
- Zabbix
- Nagios
- pg_stat_statements 分析
23.3 性能指标
- QPS/TPS
- 缓存命中率
- 连接数使用率
- 事务回滚率
- 死锁检测

24. 日常运维

24.1 容量规划
- 数据库大小监控
- 表膨胀检测
- 索引大小管理
24.2 版本升级
- 大版本升级（pg_upgrade）
- 小版本升级
- 升级策略与回滚方案
24.3 问题排查
- 慢查询分析
- 连接数耗尽处理
- 事务 ID 回卷
- 表膨胀处理

第十阶段：项目实战 (3-4周)

25. 实战项目

25.1 电商系统数据库设计
- 用户、商品、订单、库存设计
- 购物车实现
- 促销活动设计
25.2 博客/内容管理系统
- 文章、评论、标签设计
- 全文搜索实现
- 分类与归档
25.3 实时数据分析系统
- 时序数据存储
- 物化视图应用
- 窗口函数分析
25.4 地理信息系统
- PostGIS 应用
- 空间查询与计算

26. 性能调优实战

26.1 基准测试
- pgbench 使用
- 自定义压测脚本
26.2 生产环境调优案例
- 慢查询优化实例
- 索引优化实战
- 配置参数调优

27. 生产环境部署

27.1 部署清单
- 操作系统优化
- 数据库配置检查
- 安全加固
27.2 自动化运维
- Ansible 部署脚本
- Docker/K8s 容器化部署
- 备份自动化

学习资源推荐

官方文档

PostgreSQL 官方文档
PostgreSQL Wiki

在线资源

PostgreSQL 中文社区
Stack Overflow
GitHub 优秀项目

实践环境

本地虚拟机
Docker 容器
云数据库服务（AWS RDS、阿里云 RDS）

学习路径建议

第1-2月：重点学习 SQL 基础和数据类型
第3-4月：深入数据库设计和索引优化
第5-6月：掌握性能调优和高级特性
第7-8月：学习高可用和运维监控
第9-10月：实战项目 + 性能调优

技能等级评估

初级：掌握 SQL 基础、表设计、基本查询
中级：掌握索引优化、存储过程、备份恢复
高级：掌握性能调优、高可用架构、分布式部署
专家：源码分析、扩展开发、社区贡献

评论区

评论列表

{{ rItem.user.nickname || rItem.user.username }} @ {{ rItem.toUser.nickname || rItem.toUser.username }}

作者信息

落叶

学无止境，学海无涯

私信专栏

热门专栏

LeetCode 热题 100 - 两数之和

微信小程序开发：实现授权登录，获取用户手机号及用户信息

UniApp 多端部署详解：H5、App 和小程序的发布流程及调试工具

使用 Materialize 实现图文列表布局

MaterializeCSS轮播组件：构建动态网页内容的利器

在网页中使用 Materialize 实现中文日期选择器

PostgreSQL 从零到精通学习大纲

第一阶段：入门基础 (1-2周)

1. PostgreSQL 简介与安装

2. 基础架构与配置

第二阶段：SQL 语言精通 (3-4周)

3. SQL 基础

4. 高级 SQL 特性

第三阶段：数据类型与函数 (2-3周)

5. 数据类型详解

6. 函数与操作符

第四阶段：数据库设计 (2-3周)

7. 数据库设计理论

8. 索引设计

9. 视图与物化视图

10. 数据库架构设计

第五阶段：权限管理 (1-2周)

11. 用户与角色管理

12. 权限体系

13. 连接安全

第六阶段：性能优化 (3-4周)

14. 查询优化

15. 服务器配置优化

16. 事务与并发控制

17. 数据库维护

第七阶段：高级特性 (2-3周)

18. 存储过程与触发器

19. 全文搜索

20. 扩展模块

第八阶段：备份恢复与高可用 (2-3周)

21. 备份与恢复

22. 高可用与集群

第九阶段：监控与运维 (1-2周)

23. 监控体系

24. 日常运维

第十阶段：项目实战 (3-4周)

25. 实战项目

26. 性能调优实战

27. 生产环境部署

学习资源推荐

官方文档

推荐书籍

在线资源

实践环境

学习路径建议

技能等级评估

{{operTitle}}