第1章 PostgreSQL概述 1
1.1 PostgreSQL 11的新颖之处 1
1.1.1 理解新的数据库管理功能 1
1.1.2 改良的索引及更好的优化 3
1.1.3 更好的缓存管理 4
1.1.4 增强的窗口函数 5
1.1.5 引入即时编译 6
1.1.6 增强的分区机制 6
1.1.7 增加对存储过程的支持 7
1.1.8 改进的ALTER TABLE 8
1.2 小结 9
1.3 常见问题 9
第2章 理解事务和锁定 11
2.1 运用PostgreSQL事务 11
2.1.1 处理事务中的错误 13
2.1.2 使用SAVEPOINT 14
2.1.3 事务性DDL 15
2.2 了解基本的锁定 16
2.2.1 避免典型错误和显式锁定 18
2.2.2 考虑可替换的解决方案 20
2.3 使用FOR SHARE和FOR UPDATE 20
2.4 了解事务隔离级别 23
2.5 观察死锁和类似的问题 25
2.6 利用咨询锁 27
2.7 优化存储以及控制清理 28
2.7.1 配置VACUUM和autovacuum 29
2.7.2 观察工作中的VACUUM 31
2.7.3 通过利用snapshot too old来限制事务 34
2.8 小结 34
2.9 常见问题 35
第3章 使用索引 37
3.1 理解简单的查询和代价模型 37
3.1.1 使用EXPLAIN 38
3.1.2 深究PostgreSQL代价模型 40
3.1.3 部署简单的索引 42
3.1.4 使用排序输出 43
3.1.5 高效地使用位图扫描 45
3.1.6 以一种聪明的方式使用索引 46
3.2 使用聚簇表改善速度 48
3.2.1 聚簇表 51
3.2.2 使用只用索引的扫描 51
3.3 了解额外的btree特性 52
3.3.1 组合索引 52
3.3.2 增加函数索引 53
3.3.3 减少空间消耗 54
3.3.4 在建立索引时添加数据 56
3.4 引入操作符类 56
3.4.1 创建新操作符 58
3.4.2 创建操作符类 61
3.4.3 测试自定义操作符类 62
3.5 了解PostgreSQL的索引类型 62
3.5.1 Hash索引 63
3.5.2 GiST索引 63
3.5.3 GIN索引 66
3.5.4 SP-GiST索引 67
3.5.5 BRIN索引 67
3.5.6 增加额外的索引 68
3.6 用模糊搜索实现更好的回答 70
3.6.1 利用pg_trgm 70
3.6.2 加速LIKE查询 72
3.6.3 处理正则表达式 73
3.7 了解全文搜索 73
3.7.1 比较字符串 74
3.7.2 定义GIN索引 75
3.7.3 调试你的搜索 76
3.7.4 收集词统计信息 77
3.7.5 利用排除操作符 78
3.8 小结 79
3.9 常见问题 79
第4章 处理高级SQL 81
4.1 引入分组集 81
4.1.1 装载一些例子数据 81
4.1.2 应用分组集 82
4.1.3 组合分组集和FILTER子句 85
4.2 使用有序集 86
4.3 理解假想聚集 88
4.4 利用窗口函数和分析 89
4.4.1 划分数据 90
4.4.2 在窗口中排序数据 91
4.4.3 使用滑动窗口 92
4.4.4 抽取窗口子句 94
4.4.5 使用内建窗口函数 95
4.5 编写自己的聚集 103
4.5.1 创建简单的聚集 103
4.5.2 为并行查询增加支持 106
4.5.3 改进效率 107
4.5.4 编写假想聚集 109
4.6 小结 110
第5章 日志文件和系统统计信息 111
5.1 收集运行时统计信息 111
5.1.1 检查实时流量 111
5.1.2 检查数据库 114
5.1.3 检查表 117
5.1.4 了解pg_stat_user_tables 118
5.1.5 深入研究索引 119
5.1.6 跟踪后台工作者 120
5.1.7 跟踪、归档以及流 121
5.1.8 检查SSL连接 124
5.1.9 实时检查事务 124
5.1.10 跟踪清理进度 125
5.1.11 使用pg_stat_statements 126
5.2 创建日志文件 129
5.2.1 定义日志目的地和轮转 130
5.2.2 配置syslog 131
5.2.3 记录慢查询 132
5.2.4 定义记录什么以及怎么记录 132
5.3 小结 135
5.4 常见问题 135
第6章 优化查询性能 137
6.1 学习优化器的行为 137
6.1.1 评估连接选项 138
6.1.2 应用转换 140
6.1.3 应用等值约束 141
6.1.4 穷举搜索 141
6.1.5 全都试一遍 142
6.1.6 让处理失败 142
6.1.7 常量折叠 144
6.1.8 理解函数内联 144
6.1.9 连接剪枝 146
6.1.10 加速集合操作 147
6.2 理解执行计划 148
6.2.1 系统地了解计划 149
6.2.2 发现问题 151
6.3 了解并且固定连接 155
6.3.1 正确使用连接 155
6.3.2 处理外连接 156
6.3.3 理解join_collapse_limit变量 157
6.4 启用和禁用优化器设置 158
6.5 数据分区 162
6.5.1 创建分区 162
6.5.2 应用表约束 164
6.5.3 修改继承的结构 166
6.5.4 在分区结构中移进和移出表 166
6.5.5 清理数据 167
6.5.6 理解PostgreSQL 11.0的分区技术 168
6.6 为好的查询性能调整参数 170
6.6.1 加速排序 173
6.6.2 加速管理任务 175
6.7 使用并行查询 176
6.7.1 PostgreSQL能并行做哪些事情？ 179
6.7.2 并行实践 180
6.8 JIT编译 181
6.8.1 配置JIT 181
6.8.2 运行查询 182
6.9 小结 184
第7章 编写存储过程 187
7.1 了解存储过程语言 187
7.1.1 了解基本原理—存储过程与函数 188
7.1.2 函数剖析 189
7.2 了解各种存储过程语言 192
7.2.1 PL/pgSQL简介 193
7.2.2 用PL/pgSQL编写存储过程 210
7.2.3 PL/Perl简介 211
7.2.4 PL/Python简介 218
7.3 改进函数 221
7.3.1 减少函数调用次数 222
7.4 将函数用于多种目的 223
7.5 小结 225
7.6 常见问题 225
第8章 管理PostgreSQL的安全性 227
8.1 管理网络安全性 227
8.1.1 理解绑定地址和连接 228
8.1.2 管理pg_hba.conf 231
8.1.3 处理实例级安全性 235
8.1.4 定义数据库级安全性 239
8.1.5 调整方案级权限 241
8.1.6 使用表 243
8.1.7 处理列级安全性 244
8.1.8 配置默认特权 245
8.2 深入研究行级安全性——RLS 247
8.3 检查权限 251
8.4 重新分配对象以及删除用户 252
8.5 小结 254
8.6 常见问题 254
第9章 处理备份和恢复 255
9.1 执行简单的转储 255
9.1.1 运行pg_dump 255
9.1.2 传递口令和连接信息 257
9.1.3 抽取数据的子集 259
9.2 处理多种数据格式 260
9.3 重放备份 261
9.4 处理全局数据 262
9.5 小结 263
9.6 常见问题 263
第10章 理解备份与复制 265
10.1 理解事务日志 265
10.1.1 查看事务日志 266
10.1.2 理解检查点 267
10.1.3 优化事务日志 267
10.2 事务日志归档与恢复 269
10.2.1 配置归档 269
10.2.2 配置pg_hba.conf文件 270
10.2.3 创建基础备份 271
10.2.4 重放事务日志 274
10.2.5 清理事务日志归档 277
10.3 设置异步复制 278
10.3.1 执行基础设置 279
10.3.2 停止和继续复制 281
10.3.3 检查复制以确保可用性 282
10.3.4 执行故障转移以及理解时间线 285
10.3.5 管理冲突 285
10.3.6 让复制更可靠 287
10.4 升级成同步复制 288
10.5 利用复制槽 291
10.5.1 处理物理复制槽 291
10.5.2 处理逻辑复制槽 293
10.6 使用CREATE PUBLICATION以及CREATE SUBSCRIPTION 296
10.7 小结 298
10.8 常见问题 299
第11章 选取有用的扩展 301
11.1 理解扩展如何工作 301
11.2 利用contrib模块 305
11.2.1 使用adminpack模块 305
11.2.2 应用布隆过滤器 307
11.2.3 部署btree_gist和btree_gin 309
11.2.4 Dblink—考虑逐步淘汰 310
11.2.5 用file_fdw取得文件 311
11.2.6 使用pageinspect检查存储 312
11.2.7 用pg_buffercache研究缓冲 314
11.2.8 用pgcrypto加密数据 316
11.2.9 用pg_prewarm预热缓冲 316
11.2.10 用pg_stat_statements检查性能 318
11.2.11 用pgstattuple检查存储 318
11.2.12 用pg_trgm进行模糊搜索 319
11.2.13 用postgres_fdw连接远程服务器 320
11.3 其他有用的扩展 324
11.4 小结 325
第12章 检修PostgreSQL 326
12.1 着手处理一个陌生的数据库 326
12.2 检查pg_stat_activity 326
12.2.1 处理Hibernate语句 328
12.2.2 搞清楚查询来自何处 329
12.3 检查慢查询 329
12.3.1 检查个体查询 330
12.3.2 用perf深入研究 331
12.4 检查日志 332
12.5 检查缺失的索引 333
12.6 检查内存和I/O 333
12.7 了解值得注意的错误场景 335
12.7.1 面对clog损坏 336
12.7.2 理解检查点消息 337
12.7.3 管理损坏的数据页面 337
12.7.4 粗心的连接管理 338
12.7.5 与表膨胀作斗争 339
12.8 小结 339
12.9 常见问题 340
第13章 迁移到PostgreSQL 341
13.1 把SQL语句迁移到PostgreSQL 341
13.1.1 使用lateral连接 341
13.1.2 使用分组集 342
13.1.3 使用WITH子句——公共表表达式 343
13.1.4 使用WITH RECURSIVE子句 344
13.1.5 使用FILTER子句 344
13.1.6 使用窗口函数 345
13.1.7 使用有序集——WITHIN GROUP子句 346
13.1.8 使用TABLESAMPLE子句 346
13.1.9 使用limit/offset 347
13.1.10 使用OFFSET 348
13.1.11 使用临时表 349
13.1.12 在时间序列中匹配模式 349
13.2 从Oracle转向PostgreSQL 349
13.2.1 使用oracle_fdw扩展移动数据 349
13.2.2 使用ora2pg从Oracle进行迁移 351
13.2.3 常见的陷阱 353
13.2.4 ora_migrator——快速从Oracle迁移到PostgreSQL 355
13.3 从MySQL或MariaDB转向PostgreSQL 355
13.3.1 处理MySQL和MariaDB中的数据 356
13.3.2 迁移数据和模式 360
13.4 小结 363