1.前言
这两天抽空将《PostgreSQL修炼之道 从小工到专家第二版》看完了，因为第一版很早之前就看过，所以看的时候可以速读，较第一版丰富的内容则可以重点看一下。分享一下这本书中的一些特色。2.特色
第一版还是18年看的，同时也是自己的启蒙书。光肉眼看一下厚度，就可以看到第二版较第一版丰富了不少内容。第一版是基于PostgreSQL9.4写的，第二版在原来的基础上同时添加了不少新特性的讲解，这里推荐去看一下唐成老师第十届PostgreSQL大会上分享的《PostgreSQL从10版本开始的新功能详解》，速览各个版本的差异。毕竟PostgreSQL的版本迭代很快，大概每年10月发布一个大版本，同时小版本的迭代速度也很快，基本上每3个月推出一个补丁版本，这意味着已知的Bug很快会被修复，有应用场景的需求也会及时得到响应。而国际社区维护近五个主要版本，可以看到9.6今年就到了support timeline了。目前大版本10、11应用较为广泛。第一章简介就如前文所述，各个版本的新特性，还有与MySQL和Oracle这二者主流关系型数据库做了一个较为详细的对比，所以对于一些正在为选型开源关系型数据库犯难的人，可以参照这一块的对比。首先引用一下原文：如果你仅仅是想把数据库作为一个简单的存储软件(一些大的互联网公司就是这样)，一些较复杂的功能都想放在应用中来实现，那么选择MySQL或一些NoSQL产品都是合适的。如果你应用的数据访问很简单(如大多数的博客系统)，那么后端使用MySQL也是很合适的。但是如果你的应用不像博客系统那么简单，又不想消耗太多的开发资源，那么PostgreSQL是一个很明智的选择。最有说服力的例子就是图片分享公司Instagram，在使用“Python+PostgreSQL”架构后，只是十几个人就支撑了整个公司的业务。个人理解就是(可能不对，望大佬轻喷)：MySQL倾向于使用者的角度，回答的问题是 “你想解决的是什么问题”；而PostgreSQL倾向于理论角度，回答的问题是 “数据库应该如何来解决问题”。从应用场景来说，PostgreSQL更加适合严格的企业应用场景(比如金融、ERP、CRM等等)，但不仅仅限制于此，它还可以存储array和json，可以在array和json上建索引，甚至还能用表达式索引。为了实现文档数据库的功能，设计了jsonb的存储结构，还有hstore等数据格式，特别适用于一些大数据格式的分析，还有杀手锏GIS；你想使用NoSQL、Riak、REACT、Redis、Mongo等的功能吗？PostgreSQL都具备这种能力。而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景，同时由于一定的客观和历史原因，至少我上学时没听说过PostgreSQL，当时不是SQL Server、SQLite就是MySQL，用户基数大，社区活跃度高，个人平时关注的公众号基本上推送的无外乎《MySQL索引优化》、《MySQL索引原理，一篇从头到尾讲清楚》、《几个常见而严重的MySQL问题分析》等等，大都是讲解MySQL的，PostgreSQL的文章还是较少，当然现在有分会还有中文社区的推广，学习PostgreSQL的渠道也越来越多。另外有趣的是，若在Google上搜索“switch postgresql to mysql”时，结果中第一页全是“Switch to PostgreSQL from MySQL”，第二页终于有个是“from PostgreSQL to MySQL”，不过只有它一个，而且原因不是说PostgreSQL不好，而是因为作者MySQL经验多些。当然MySQL也是十分不错的数据库，比如引擎，在架构上MySQL分为两层：上层的SQL层和几个存储引擎(比如InnoDB，MyISAM，Memeory)等，而PostgreSQL只有一个存储引擎，万年被Diss的MVCC，还有PostgreSQL直到8.0才官方支持了Windows系统。https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison和Oracle的对比也引用一下原话：“从功能上说，PostgreSQL可以与Oracle数据库媲美。Oracle数据库是目前功能最强大的商业数据库，PostgreSQL则是功能最强大的开源数据库。Oracle在集群功能如RAC、ASM方面比较强，但PostgtreSQL也有一些比Oracle强的特性，如在索引和可扩展等方面”。但不可置否，总体上来说，开源数据库都不是很完善，商业数据库Oracle在架构和功能方面都还是完善很多的。同时Oracle和PostgreSQL在很多方面很类似，如共享内存、进程模型等，加之目前国内盛行的去“O”，(慢慢的现在变成了减“O”)基本上厂商都是基于PostgreSQL来做定制化开发，基于MySQL的少之又少。虽然类似，但是Oracle的很多高级特性如profile、Vault等等，PostgreSQL都还没有，加之Oracle的生态丰富，齐全的手册、完善的培训机构等等，这些都是Oracle的强大之处。PostgreSQL国际社区有500+Commiter，相信会越来越牛逼。第二章到第六章和第一版没有太大变化，其中第六章的锁这一章节可以细看一下，较第一版添加了不少内容，分析了行级锁、表级锁等，行级锁可以在德哥的github上和阿里的内核分析系列看到一些更详细的内容，《PostgreSQL management - lock inspect (行锁监视 pgrowlocks)》、《PgSQL 原理介绍 PostgreSQL行锁实现》，书籍通过实际案例一步步分析谁锁住了谁以及如何定位等，对于实战一定大有收获。第八、九章没有太大变化，第十章值得细读，添加了很多这些年唐老师积累的PostgreSQL数据库技术内幕方面的新内容，如pg_control文件解析、WAL日志的解析、WAL命名规则和recycle的原理，也和Oracle中的Redo Log也做了一个简单的对比，另外还有CLOG以及tuple中的infomask标志位加速获取事务状态的解析。
第十一章添加了全文检索，由于内置的全文检索功能只能检索英文，于是引进了zhparser以支持中文的全文检索，同时也介绍了从9.6开始引进的另一项强大功能——并行查询，目前PostgreSQL支持的并行种类已经很多，11里面也引入并行create btree index，要启用并行也得合理设置dynamic_shared_memory_type(除了none)，因为并行依托于DSM。另外不能以单用户的模式使用，即psql --single。除此之外，特定场景也不支持并行查询，如1．任何写数据或者锁行的查询均不支持并行，CREATE TABLE ... AS，SELECT INTO，和 CREATE MATERIALIZED VIEW 等创建新表的命令可以并行4．包含 PARALLEL UNSAFE 函数的查询不支持并行5．事务隔离级别为 serializable 时不支持并行。第十二章也较第一版添加了不少内容，如大页优化，逻辑优化fillfactor(记笔记，尤其是update频繁的场景下可以起到很不错的效果)、最佳实践以及SQL语句的优化技巧，有通用的，也有基于PostgreSQL的。这里个人还是倾向于能看到一些关于SQL等价改写、语义语法优化等高阶优化的例子，希望在第三版能看到(hiahia，假如有的话)，这里建议去看一下《罗炳森_SQL等价改写核心思想》，可以学到不少技巧。第十三章较第一版添加了逻辑复制，流复制内容没有太大变化，逻辑复制一块清晰明了，可以快速照着例子上手。书籍后面的章节——架构篇就和第一版没有太大差异了，速读了过去，主要根据各个第三方软件/插件的最新版本进行了一些内容更新3.个人疑惑
看完之后，也分享几点在看的过程中的疑惑之处，首先是P298的关于bitmap scan的讲解，原文是：Recheck Cond(id2 > 10000)，因为多版本的原因，从索引中找出的行从表中读出后还需要再检查一下条件。以前针对bitmap scan，其实原理就是将随机IO转化为顺序IO，如果获取的结果集的占比比较小，但是元组数很多时，Bitmap Index Scan 的性能要比Index Scan 好。这一块是Tom lane的一点解释，核心是传统的index scan每次从索引中去取一个tuple的指针，然后立马去表中取数据，每一次会造成一次随机io。如果数据量较多的情况下，会比较低效。而bitmap scan一次性将符合条件的tuple-pointers全部取出来，然后在内存中进行地址排序，然后去取出数据，这时的读取数据由于进行的地址排序，读取时就变成了顺序的读。其实就是一个随机读转化为顺序读取的过程，但是取出的数据由于进行了地址的排序，就没有顺序。同时，对于limit这种sql，bitmap index scan这种就不适合，因为它一次会取出所有数据。
回答还是生成位图，以便它的任何元素都可以轻松地映射到指向页面的指针？
不，位图与堆页面1：1相对应。好吧，在这种情况下，您似乎可能误解了“位图”的含义。它不是为每个堆页面，每个索引读取或类似内容创建的像“ 101011”这样的字符串。整个位图是一个单一的位数组，具有与要扫描的关系中的堆页面一样多的位。第一次索引扫描会创建一个位图，从所有条目0(false)开始。只要找到与搜索条件匹配的索引条目，该索引条目所指向的堆地址就会作为位图中的偏移量进行查找，并且该位设置为1(true)。因此，位图索引扫描不是直接查找堆页面，而是查找位图中的相应位位置。第二个位图索引扫描以及其他位图索引扫描与其他索引及其上的搜索条件进行相同的操作。然后，将每个位图与在一起。所产生的位图在每个堆页面中都有一个位，其中只有在所有单个位图索引扫描中这些位都为真时，这些位才为真，即，与每个索引扫描匹配的搜索条件。这些是我们唯一需要加载和检查的堆页面。由于每个堆页面可能包含多行，因此我们必须检查每一行以查看其是否符合所有条件-这就是“重新检查条件”部分的含义。所有这些要理解的关键一点是，索引条目中的元组地址指向行的ctid，它是堆页号和堆页内偏移量的组合。位图索引扫描会忽略偏移量，因为它无论如何都会检查整个页面，并在该页面上的任何行符合条件的情况下设置该位。Heap, one square = one page: +---------------------------------------------+ |c____u_____X___u___X_________u___cXcc______u_| +---------------------------------------------+ Rows marked c match customers pkey condition. Rows marked u match username condition. Rows marked X match both conditions. Bitmap Heap Scan on customers (cost=25.76..61.62 rows=10 width=13) (actual time=0.077..0.077 rows=2 loops=1) Recheck Cond: (((username)::text < 'user100'::text) AND (customerid < 1000)) -> BitmapAnd (cost=25.76..25.76 rows=10 width=0) (actual time=0.073..0.073 rows=0 loops=1) -> Bitmap Index Scan on ix_cust_username (cost=0.00..5.75 rows=200 width=0) (actual time=0.006..0.006 rows=2 loops=1) Index Cond: ((username)::text < 'user100'::text) -> Bitmap Index Scan on customers_pkey (cost=0.00..19.75 rows=1000 width=0) (actual time=0.065..0.065 rows=999 loops=1) Index Cond: (customerid < 1000) Bitmap scan from customers_pkey: +---------------------------------------------+ |100000000001000000010000000000000111100000000| bitmap 1 +---------------------------------------------+ One bit per heap page, in the same order as the heap Bits 1 when condition matches, 0 if not Bitmap scan from ix_cust_username: +---------------------------------------------+ |000001000001000100010000000001000010000000010| bitmap 2 +---------------------------------------------+ Once the bitmaps are created a bitwise AND is performed on them: +---------------------------------------------+ |100000000001000000010000000000000111100000000| bitmap 1 |000001000001000100010000000001000010000000010| bitmap 2 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |000000000001000000010000000000000010000000000| Combined bitmap +-----------+-------+--------------+----------+ Used to scan the heap only for matching pages: +---------------------------------------------+ |___________X_______X______________X__________| +---------------------------------------------+ The bitmap heap scan then seeks to the start of each page and reads the page: +---------------------------------------------+ |___________X_______X______________X__________| +---------------------------------------------+ seek------->^seek-->^seek--------->^ and each read page is then re-checked against the condition since there can be >1 row per page and not all necessarily match the condition. 并根据条件重新检查每个读取的页面，因为每页可能有> 1行，并且不一定都符合条件。另外一个是P323页的，原文：“Latest checkpoint location指向的是WAL日志中一条Checkpoint的WAL记录，这条记录中记录了本次Checkpoint事件的一些信息”，我们知道Latest checkpoint’s REDO location是进行redo的位点，而Latest checkpoint location，我在Habr中看到如下解释，也就是说Latest checkpoint location是Checkpoint完成的位点。
4.后话
以上种种就是我个人看完小工到专家第二版的一些记录和疑惑之处，这本书十分给力，详解了PostgreSQL的功能与特色，包含了大量来自实际生产环境的经典案例和经验总结，对于新手来说是一份好的启蒙书，正如书名，从小工到砖家就靠他了。对于老油条也能从中收获不少，值得细细品读。另外值得说的是，PostgreSQL在火的同时(三度荣获年度最受欢迎关系数据库奖项)，个人也看到了不少撕逼现象，主要是MySQL和PostgreSQL的爱好者们互相撕逼Diss，MySQL的Logo是“世界上最流行的开源关系型数据库”，而PostgreSQL则宣称“世界上最先进的开源关系型数据库”，大有碰瓷之嫌，引得无数数据库爱好者互相评头论足，比如pger就叼MySQL的蹩脚AP能力，跑个join都跑不出来，上个千万级数量就要分库分表，要你何用，mysql的爱好者们就不爽了，就叼PostgreSQL的蹩脚MVCC(引擎，此时zheap正在快马加鞭来的路上)，天天表膨胀烦不烦啊，还要vacuum，定期维护表和索引避免膨胀导致性能下降。其实大可不必，绕开需求的技术都是耍流氓，二者都是十分流行与强大的开源关系型数据库，只是由于不同的学派，不同的面向场景，和一定的历史原因而导致了种种，与其互相Diss，还不如关起门，学习PostgreSQL的同时，把MySQL、Oracle等也学习一下，触类旁通，知己知彼，岂不美哉，就算你要Diss我也能针对你的软肋来Diss。
