我所了解的PostgreSQL

我最早接触PostgreSQL是08年，但那时候是浅尝则止，除了知道它是开源的RDBMS其它就没什么印象了。真正了解PG是从2012年开始，那时富士通开发一款基于PG的一体机，我们的Team开发其中一部分功能。那时候我开始接触PG的代码，很快就被PG严谨优雅的代码给震惊了，甚至决定以后就跟着PG混了。随着对PG的了解，越来越觉得PG是个好东西，简直可以称之为神器。但是，遗憾的是PG在国内的应用却远远不如MySQL，这是PG的遗憾，但更是国内IT业的遗憾。好在这两年情况正在改观，青云和阿里云的RDS支持PG后，对PG的推广又是一大促进。


下面从几个方面说说我对PG的认识。

1.代码品质

这方面PG很出色的，PG的代码简直可以作为C语言编程的教科书。一般开发C语言的人容易被内存释放，错误处理，多平台，多语言，常用数据结构等等这些琐事搞的头大，但PG里这些都被封装的很好。很多地方的代码都不需要释放内存，因为这些内存之后会随着内存上下文一并被回收。代码里也不用判断上次调用的函数有没有出错，因为一但出错，调用就被longjmp出去了，走不到下面那遗憾。这些设计使得PG的代码可以更关注真正的逻辑，更简洁也更容易维护，相伴而来的就是出的BUG更少也更容易追加新特性。

2.多进程架构

PG和Oracle一样是多进程架构，而MySQL是多线程的。有人觉得多线程好，也有人觉得多进程好。
关于多进程和多线程的对比，没必要在这里详细讨论，PG的官方wiki上就有这样的讨论，有兴趣的话可以去看一下。
但是在我看来，多线程是非常邪恶的，至少在C语言编写的复杂软件里面。因为我们有好几次为了找出某个系统里面多线程并发的BUG，都要花上一两个月的时间，最离谱的一次，整整花了大半年才把一个BUG再现了。当然，多进程架构里也有并发的问题，但程序员犯错的机会要少一点，而且一旦发生问题找BUG也相对容易一点。

3.PG社区

PG的发展方向由PG社区几位来自不同企业的真正的技术大牛组成的core team把握着。他们不会屈从与任何一家公司或客户的命令或短期需求，而是从PG长远的发展考虑开发方向。我看到核心成员之一的Tom lane经常会在邮件列表里无情的拒绝掉一些看上去很美的新特性。这些新特性很多是华而不实的（可能也不全是），吸收这些东西，虽然可以满足一小部分需求，但可能会影响PG的稳定性，或者让PG变得臃肿，越来越难维护。得益于PG社区对吸收新特性的严谨态度，现在PG在拥有这么多特性的情况下只有800K代码、某些商业数据库可能光驱动就接近这个数了。可以说PG在功能特性上是重量级的，但体积上，却仍然轻巧，无论是从代码量上看还是从安装后占用的资源来看都是如此。

4.PG驱动社区

PG的驱动基本都是作为独自的开源项目维护的，这样PG的内核社区的精力也可以更集中。但不同驱动的品质也是由差异的，我知道的几个里面，pgjdbc的品质是最好的，里面有很多PG社区的人。psqlODBC内部代码有点乱，很多过时的和重复的东西还放在里面。但是最近Heikki Linnakangas在大刀阔斧的重构psqlODBC的代码，相信会有很大改观。pgjdbc和psqlODBC都是挂着postgresql.org下面的项目，内核社区的人也经常过来关注。其它驱动项目，真的就是独立的了。
值得一提的是Npgsql，这是.net的驱动，它的之前的品质真的不敢恭维，Bug太多，代码太乱。自从12年Francisco Figueiredo Jr.将代码托管到github，并且吸收了roji等牛人加入以后，Npgsql开发进度大大加快，内部代码也做了大量的重构。从2.0到2.1，2.1到2.2，Npgsql的代码几乎改了一半，很多残留了N年的Bug被翻出来迅速修复了。Entity Framework,DDEX,Setup这些非常有用的功能也加进来了。但是Npgsql仍然有硬伤。TableAdapter，CommandBuilder里面有一些Bug，特别在处理一些不常用数据类型或特殊的SQL的时候。getSchema()的结果缺乏完整统一的设计。TransactionScope在设计上就犯了大错，不仅使代码变得异常复杂，而且动不动就启动MSDTC，严重影响性能。另外Entity Framework相关的BUG修正一直不断，看来这个新特性仍然不够稳定。




下面说说和PG开发相关的几个问题

5.事务隔离

事务隔离和性能是一对矛盾，MVCC很好的调和了这对矛盾，所以现在的主流RDBMS中，MVCC几乎是标配。但是各家实现上又有差异，据我所知，目前好像只有PG做到了基于MVCC的可串行化（可串行化指并行事务的结果等价各个事务按某个顺序串行执行）。MySQL在可串行化隔离级别时退化到使用封锁保证数据一致性，而不是MVCC，这对并发性能是个很大的伤害；而Oracle干脆不设防地让事务通过。所以Oracle的“可串行化”是假的，是违背SQL标准的。Oracle的“可串行化”实际上等价于PG的“可重复读”，保证不会出现脏读，不可重复读和幻读，但不能保证执行结果等价于各个事务按某个顺序串行执行（详见：http://blog.chinaunix.net/uid-20726500-id-3900530.html）。而PG的“可串行化”才是严格意义上的可串行化,也是符合SQL标准的可串行化。（详见：http://blog.chinaunix.net/uid-20726500-id-3900541.html）

6.数据类型

跟一些其它数据库比起来，PG的数据类型不仅丰富而且很简洁。丰富不难理解，像jsonb,xml,几何,数组，网络地址，枚举，复合数据类型这些类型在一些特定场景里都是解决问题的利器。至于简洁，看字符类型就知道了。PG只有varchar,char和text三种字符类型，每一种的区别都很明显不像有些数据库搞出那么多花样，比如Oracle有CHAR、VARCHAR、VARCHAR2、LONG、NCHAR、NVARCHAR2、CLOB和NCLOB，这些除了混淆试听，别无益处。而且PG的varchar和char的最大长度都是10M字符，text大概是1G，很少需要用户操心长度限制的问题。


PG中二进制类型是bytea，它等同于其它数据库中BINARY + BLOB，长度可以从1个字节到1个G，不需要用户根据长度选择不同的数据类型。PG中还有一种为二进制大对象做了优化的large object，需要使用特殊的API访问。有些从其它数据库转过来的童鞋用它来来替代BLOB，然后发现不知道怎么从驱动里取large object，于是开始抱怨，其实要以和其它数据库相同的方式使用大对象，使用bytea就好了。


PG的数据类型里也有不完美的地方，比如money和timestamptz。
money其实就是输入输出形式依赖于数据库的lc_monetary参数的numeric。money本身并没有保存币种信息，但是输出时却让你误以为它保存了币种，所以数据库里的同一笔钱，有的人可能看到的是10美元，有的人可能看到的就是10日元。而且，不同驱动处理money的方式可能还不一样，比如：pgjdbc将money映射为java的double，即概数型了；npgsql将money映射为.net的decimal类型。所以，我建议就当PG中没有money这个数据类型，用numeric存储货币。
timestamptz存在类似的问题，timestamptz从字面上看是with timezone的，实际上是假的。timestamptz并没有把timezone信息存下来，它和timestamp的区别在于：timestamp存的是你传给它的时间，没有时区概念，而timestamptz存的是转换成UTC后的时间。也就是说，PG的timestamptz等同于Oracle的TIMESTAMP WITH LOCAL TIME ZONE。虽然大多数场景这已经够用了，但了解其中的差异也是必要的。不过timetz是真的把timezone（准确的说应该是时区的offset）存下来了。

7.区域和字符编码

很多初次接触PG的人会被乱码搞得心烦意乱，其实只要了解了PG处理编码的逻辑，就会发现大部分情况是自己的配置问题，剩下的原因是数据库里真的混进了乱七八糟的字符。要想和乱码说拜拜，记住下面几点基本上就可以解决大部分问题了。
1）数据库务必设为UTF8编码（其它编码想都不要想，省得给自己挖坑）
2）启动数据库的环境也要是UTF8编码
3）导入数据时，数据的编码要和客户端的client_encoding一致
4）jdbc,npgsql，psql_OBDC(unicode版)等驱动的client_encoding固定是UTF8，不能修改。
5）psql默认根据客户端区域环境自动设置client_encoding


区域也是个值得探讨的问题，PG的哲学大概是能让别人做的就不自己做，所以PG的区域特性是依赖与OS的。
典型的是字符的排序规则，不同OS下可能会由细微差异。不光是排序，有些功能也依赖于区域设置，比如正则表达式匹配，pg_trgm。
关于PG的区域和字符编码，可参考：http://blog.chinaunix.net/uid-20726500-id-4766163.html

8.扩展性

PG的可扩展性做的太强了。存储过程，函数重载，聚合函数，窗口函数，操作符，数据类型，索引类型，FDW，hook这些都可以进行扩展。
谈到FDW，想到我们以前做过的一个功能。是从web上通过PG把CVS日志down下来，开始用函数做的。但是发现点了下载按钮后要等待很长时间页面才有反应，原来函数必须要把所有数据都填充好了才返回，而那个日志文件大概有1个G。后来改用FDW做，等待时间不超过1秒就开始传送数据了。同样的问题在generate_series()上也有，用generate_series()填充测试数据确实很方便，但是如果填充的数据量过于庞大，有可能导致OOM，这时需要分几次调用generate_series()。

9.索引

PG的索引也是一大优势。支持多字段索引，索引组合，表达式索引，部分索引。对一维数据支持btree，对多维数据（比如几何类型）支持R-Tree（通过gist实现）,对集合类型（比如数组）支持倒排索引（通过gin实现），另外还支持空间索引(通过sp-gist实现)。很多集合类型也有基于gist的索引，但其查询性能普遍不如gin，而且有些场景下集合类型的gist的性能会糟糕到和不加索引比起来好不到哪儿去，所以集合类型还是要优先使用gin索引。关键的优点是PG的索引是可扩展的，通过gist，gin或sp-gist的架构你可以设计自己的索引算法。另外PG的索引还支持KNN查询。
曾经有次公司的新人项目演习，布置的题目是会议预约。有新人找我咨询SQL设计的问题，他用的是MySQL，说老是锁或者数据错。这种问题在PG里是小Case，范围类型+排他约束很容易搞定，但在MySQL上想了半天也没找到完美的解决办法，最后只好支招让他用那种很丑陋很低效的常规方法，并且告诉他以后不要再用MySQL了。

10.全文检索

PG内置了全文检索的支持，配合gin索引和KNN查询优化，很多时候完全够用了，不需要再另外折腾一套solr或别的全文检索系统了。但是PG并不能对中文进行分词，所以还需要安装额外的中文分词插件，至于怎么安装，百度一下立马出来。

11.NoSQL

得益于hstore,jsonb数据类型和gin索引，PG完全可以当一个NoSQL来用，而且是支持SQL语句，支持ACID的NOSQL，这是其它任何NOSQL都做不到的。至于PG中NOSQL的性能，如果和KV型的redis比，确实相差很大，可能由十倍左右。但是和文档型的MongoDB比，相差就不大了。根据我测试的结果，插入时MongoDB的速度大概是PG的3倍，查询时MongoDB稍微快一些，但相差不是很大。（之前EnterpriseDB公布了一个测试结果显示，PG的jsonb数据的加载，插入和查询速度普遍是MongoDB的3倍以上，经我确认，是它的测试方法有问题。虽然我是站在PG这一边的，但也要尊重事实！）虽然，在速度上PG的NoSQL输掉了一些，但是PG是保障了ACID的，而其它NoSQL却是在裸奔，所以该选哪一个自己根据需求衡量吧！




PG在运维上和其它成熟的数据库并无本质的区别，该有的基本都有。安全性，可靠性等等并不输昂贵的商业数据库，这方面德哥的介绍很权威。

12.最后谈谈PG的几个不足

分区
  现有的继承表分区，语法繁琐，而且不能支持太多分区，随着分区数增加，生成执行计划的时间急剧增长。
并行
  对于大数据量的分析，单SQL语句的并行执行非常有用，可惜现在PG还不支持并行。好在这个功能很快就会出来了。
垃圾回收
  PG的MVCC会带来垃圾数据，虽然PG内置的自动垃圾回收可以满足大部分场景，但偶尔还是可能会有不让人省心的地方，相信以后会有改善。