从零开始学PostgreSQL (一) & PostgreSQL 数据库简介

1.1 什么是Postgresql

PostgreSQL 数据库是功能强大的开源数据库，它支持丰富的数据类型（如JSON和 JSONB类型、数组类型）和自定义类型。PostgreSQL数据库提供了丰富的接口，可以很方便地扩展它的功能，如可以在GIST框架下实现自己的索引类型，支持使用C语言写自定义函数、触发器，也支持使用流行的编程语言写自定义函数。PL/Perl提供了使用 Perl语言写自定义函数的功能，当然还有PL/Python、PL/Java、PL/Tcl等。

1.1.1 Postgresql 的发展历史

•前身Ingres：PostgreSQL的前身是加利福尼亚大学伯克利分校于1977年开始的 Ingres项目。这个项目由著名的数据库科学家Michael Stonebraker领导。在1982年， Michael Stonebraker离开伯克利，并把Ingres商业化，使之成为Relational Technologies公司的一个产品，后来Relational Tecchnologies被Computer Associates （CA） 收购。Ingres是一个非关系型数据库。
•伯克利的Postgres项目：20世纪80年代，数据库系统中的一个主要问题是数据关系维护。1985年，Michael Stonebraker回到伯克利后，为了解决Ingres中的数据关系维护问题，启动了一个“后Ingres”（post-Ingres）项目，这就是Postgres的开端。 Postgres项目由美国国防高级研究计划局（DARPA）、陆军研究办公室（ARO）、国家科学基金会（NSF）以及ESL公司共同赞助。从1986年开始，Michael Stonebraker 教授发表了一系列论文，探讨了新的数据库的结构设计和扩展设计。第一个“演示性”系统在1987年便可使用了，并且在1988年的数据管理国际会议（ACM-SIGMOD）上展示， 在1989年6月发布了版本1以提供给一些外部的用户使用。由于源代码维护的时间日益增加，占用了太多本应该用于数据库研究的时间，为了减少支持的负担，伯克利的Postgres项目在发布版本4.2后正式终止。
•Postgres95：1994年，来自中国香港的两名伯克利研究生 Andrew Yu和Jolly Chen向Postgres中增加了SQL语言的解释器，并将Postgres改名为Postgres95，随后将其源代码发布到互联网上供大家使用，于是Postgres95成为一个开放源码的原伯克利 Postgres代码的继承者。
•PostgreSQL6X：到了1996年，显然“Postgres95”这个名字已经“经不起时间的考验” ，于是又起了一个新名字—PostgreSQL，此名为Postgres与SQL的缩写，即增加了SQL功能的Postgres的意思。同时版本号也重新从6.0开始，也就是说，重新使用伯克利Postgres项目的版本顺序。
•PostgreSQL7.1：PostgreSQL 7.1是继6.5版本之后的又一个巨大的变化。它首先引入了预写式日志的功能，这样，事务就拥有了完善的日志机制，可以提供更好的性能， 还可以实现更优良的备份和灾难恢复的能力（比如联机热备份和宕机后的自动恢复等）。 其次是不再限制文本类型的数据段长度，这在很大程度上解决了PostgreSQL大对象的问题。
•PostgreSQL8X：该版本可以在Windows下运行，它具有一些新特性，比如事务保存点功能、改变字段的类型、表空间、即时恢复（该功能允许对服务器进行连续的备份。既可以恢复到失败那个点，也可以恢复到以前的任意事务）等。此外，也开始支持 Perl服务器端编程语言。
•PostgreSQL9.X：进入9X版本，也标志着PostgreSQL进入了黄金发展阶段。 PostgreSQL9.0于2010年9月20日发布，它大大增强了复制 （replication）的功能，比如增加了流复制（stream replicaction）和HOT standby功能。从9.0版本开始，用户可以很方便地搭建主从数据库。此版本也提供了大版本的命令行升级工具pg_upgrade， 可以方便地从低版本的数据库升级到9.0版本。PostgreSQL9.1发布于2011年9月12日， 在该版本中增加了同步复制（synchronous replication）功能；增加了对外部表的支持；提供了外部模块框架和CREATE EXTENSION的SQL命令，可以更方便地创建外部扩展模块来扩展PostgreSQL数据库的功能；提供了不记录WAL日志表 （unlogged tables） 的功能，这在某些情况下可以大大提高性能；可以在插入、更新、删除中使用次查询 （WITH语句），解决了原先PostgreSQL数据库不能实现Oracle中MERGE INTO语句的问题。PostgreSQL9.2发布于2012年9月10日，增加了级联复制的功能；实现了从备库做全量备份的功能；实现了原先Oracle和SQL Server中的覆盖索引查询功能（即只用在索引中查询数据，不必查数据行）；增加了JSON数据类型，向SQL/NoSQL混合型数据库迈出了关键的一步。2013年9月9日，PostgreSQL9.3版本发布了，增加了物化视图的功能；为JSON类型增加了更多的处理函数的操作符；增加了可更新外部表的功能；增加了postgres_fdw模块，通过此外部表模块可以访问其他PostgreSQL服务器上的表；增加了事件触发器（Oracle系统触发器的功能），增强了数据库的审计功能。2014年12 月18日发布了PostgreSQL9.4版本，增加了JSONB数据类型（Binary JSON的功能）， 提高了JSON的性能；刷新物化视图时不再阻塞读；WAL日志中开始增加逻辑读的功能， 为后续版本中的逻辑复制打下了基础；提供了与Oracle类似的ALTER SYSTEM命令， 方便修改数据库的配置参数。2016年1月7日发布了PostgreSQL9.5版本，增加了块范围索引（即BRIN索引），一种类似于Oracle ExaData一体机中存储索引的功能，在某些情况下它因使用占用空间很小的BRIN索引而大大提升了SQL的性能；增加了表的行级安全的特性，可以控制一个用户只能看见或更新一张表的部分行；多CPU机器性能得到了进一步的提升。2016年9月29日发布了PostgreSQL9.6版本，增加了并行计算的功能， 全表扫描、JOIN查询、聚合操作可以利用多CPU进行并行计算；流复制中可以允许有多个同步的Standby数据库（之前的版本只允许有一个），实现了Standby数据库把日志重做完成后事务才返回的完全同步模式。
•PostgreSQL10.X：10.X版本实现了实用的发布和订阅方式的逻辑复制，让PostgreSQL数据库可以高效实现更灵活的复制功能，如双活功能，以前这些功能都需要通过第三方软件来实现；原先版本的Hash索引不能进行流复制，限制了Hash索引的使用， 现在没有这个限制了；并行查询的功能得到了很大的提升，如支持并行的B-Tree扫描、 Bitmap Heap扫描、并行的Merge JOIN、不相关的并行子查询等；增加了多列统计信息，让多列查询的执行计划更准确；直接支持通过CREATE TABLE语句创建分区表， 不需要用继承的语法创建分区表，大大简化了分区表的创建；在客户端的连接串中支持写多个数据库服务器的地址，连接串中提供了属性target_session_attrs，用于探测后端数据库是主库还是只读备库，以便实现高可用和读写分离的方案。
•PostgreSQL11.X：增加了对just-in-time （JIT）编译的支持，使SQL中的表达式执行效率提高；并行方面的性能得到了较大的增强，如支持并行创建索引、并行HashJOIN、并行CREATE TABLE AS等；存储过程中支持嵌入式事务，加强了对存储过程的支持，在存储过程中可以支持事务的操作，且对分区表进行了增强，如支持了哈希分区表，支持对分区键的更新等。此外，分区表的主键、外键、索引也得到了增强。
• PostgreSQL12.X：大大增强了往分区表里插入和复制数据的性能，对于有很多分区表的查询，其性能也得到了很大的提升；对B-Tree索引的性能进行了优化；对JSON 数据类型开始支持SQL/JSON Path语言，可以更方便地对JSON数据进行检索。

1.1.2 Postgresql 数据库的优势

PostgreSQL数据库具有以下优势：
•PostgreSQL数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92 的查询语言，至少实现了SQL：2011标准中要求的179项主要功能中的160项（注：目前没有哪个数据库管理系统能完全实现SQL：2011标准中的所有主要功能）。
•稳定可靠：PostgreSQL是唯一能做到数据零丟失的开源数据库。目前有报道称国内外有部分银行使用PostgreSQL数据库。
•开源省钱：PostgreSQL数据库是开源的、免费的，而且使用的是类BSD协议，在使用和二次开发上基本没有限制。
•支持广泛：PostgreSQL数据库支持大量的主流开发语言，包括C、C++、Perl、 Python、Java、Tcl以及PHP等。
•PostgreSQL社区活跃：PostgreSQL基本上每3个月推出一个补丁版本，这意味着已知的Bug很快会被修复，有应用场景的需求也会及时得到响应。

1.2 Postgresql 数据库与其他数据库的对比

1.2.1 Postgresql 与Mysql的对比

可能有人会问，既然已经有一个人气很高的开源数据库MySQL了，为什么还要使用 PostgreSQL？这主要是因为不同的数据库有不同的特点，应该为合适场景选择合适的数据库。在一些应用场景中，使用MySQL有以下几大缺点。
•复杂SQL支持弱：在MySQL 8.0之前，多表连接查询的方式只支持“Nest Loop” 不支持Hash JOIN和Sort Merge JOIN（注：MySQL8.0版本开始支持Hash JOIN，但不完善，有一些问题。另因为MySQL无完善的基于COST的优化器（CBO），长期来说也会存在一定的问题），不仅如此，它对很多SQL语法都不支持，子查询性能比较低。例如，MySQL不支持单独的sequence，有公司为此还专门开发了统一序号分发中心的软件。
•性能优化工具与度量信息不足：如果MySQL在运行过程中出现问题，性能监控数据较少，维护人员要准确定位问题存在一定的困难。
•MySQL的复制是异步或半同步的逻辑同步，这存在两个问题：一是在大事务下会导致比较大的延迟；二是容易导致数据库的不一致，原因是逻辑复制容易导致数据的不一致性，而MySQL的双层日志会让这个问题变得更复杂，即主备库的复制是通过逻辑层的binlog来实现的，但在存储引擎InnoDB下还有物理的Redo Log层，整个过程比较复杂，比较难保证主备库之间完全一致。由于有两层日志（binlog日志和InnoDB的Redo 日志），因此也很难做到Master/Slave在异常切换过程中的零数据丢失。一些第三方公司改造MySQL源代码以实现同步复制，但这些方案要么是没有开源，要么是已开源却又不是很稳定，所以，对于普通用户来说，如何实现零数据库丢失的同步复制是一个令人头疼的问题。
•在线操作功能较弱：很多在线DDL需要重建表，代价很大，有一些操作还会锁表。 一些大的互联网公司或者修改MySQL源码来实现在线DDL功能，或者通过上层架构来解决这个问题，如先在Slave数据库上把DDL做完，然后把应用从Master库切换到Slave库，再到原先的Master上把DDL做完。对于第一种方法，需要公司有很强的MySQL研发能力，第二种方法则需要公司有较强的开发能力，能设计出较强的应用架构。这对于一些中小型公司来说不太容易实现。
•难以写插件来扩展MySQL的功能：虽然用UDF，或通过外部动态库中的函数来扩展部分功能，但能扩展的功能很有限。如MySQL比较难访问其他数据库中的数据。

相对MySQL的这些弱点，PostgreSQL有以下几个优点。
•功能强大：支持所有主流的多表连接查询的方式，如“Nest loop”“Hash JOIN” “Sort Merge JOIN”等；支持绝大多数的SQL语法，如CTE（MySQL8.0之前不支持 CTE）。PostgreSQL是笔者见过的对正则表达式支持最强、内置函数也是最丰富的数据库。它的字段类型还支持数组类型。除了可以使用PL/PGSQL写存储过程外，还可以使用各种主流开发语言的语法（如Python语言的PL/Python、Perl语言的PL/Perl来写存储过程）。这些强大的功能可以大大地节约开发资源。很多开发人员在PostgresQL 上做开发时，会发现数据库已实现很多功能，甚至有一些业务功能都不再需要写代码来实现了，直接使用数据库的功能即可解决问题。
•性能优化工具与度量信息丰富：PostgreSQL数据库中有大量的性能视图，可以方便地定位问题（比如可以看到正在执行的SQL，可以通过锁视图看到谁在等待，以及哪条记录被锁定等）。PostgreSQL中设计了专门架构和进程用于收集性能数据，既有物理1/0方面的统计，也有表扫描及索引扫描方面的性能数据。
•在线操作功能好：PostgreSQL增加空值列时，本质上只是在系统表上把列定义上， 无须对物理结构做更新，这就让PostgreSQL在加列时可以做到瞬间完成。PostgreSQL 还支持在线建索引的功能，在创建索引的过程可以不锁更新操作。
•从PostgreSQL9.1开始，支持同步复制（synchronous replication）功能，通过 Master和Slave之间的复制可以实现零数据丢失的高可用方案。
•可以方便地写插件来扩展PostgreSQL数据库的功能：PostgreSQL提供了安装、编写插件的整体框架，如提供了create extension等SQL语句以方便地装载插件；与一个动态库可以很方便地给PostgreSQL添加函数；提供了外部数据源（FDW）的框架和编程接口，根据此框架和编程接口可以方便地编写访问其他数据库和外部数据源的插件。

现在针对已有的常见外部数据源，如Oracle、MySQL、SQL Server等数据库都有了第三方插件，通过这些第三方插件可以在PostgreSQL数据库中方便地访问外部数据。另外，PostgreSQL还提供了钩子函数的接口，可以实现更强大功能的插件，如p9_ pathman分区表的插件、citus分库分表的插件等。

另外，由于MySQL对SQL语法支持的功能较弱，基本上不适合做数据仓库。虽然也有些厂商开发了MySQL数据仓库的存储引擎（如Infobright），但这个方案只是解决了部分数据仓库的问题，SQL功能弱的问题还是无法完全解决。而且Infobright的社区版本在功能上有很多限制，如不支持数据更新、不支持太多的并发执行（最多支持十几个） 等。而PostgreSQL不仅支持复杂的SQL，还支持大量的分析函数，非常适合做数据仓库。

PostgreSQL数据库中还有一些支持移动互联网的新功能，如空间索引。PostGIS是最著名的一个开源GIS系统，它是PostgreSQL中的一个插件，在PostgreSQL中使用它很方便。通过PostGIS也可以很方便地解决LBS中的一些位置计算问题。

综上所述，PostgreSQL数据库是一个功能强大，又带有移动互联网特征的开源数据库。如果你仅仅是想把数据库作为一个简单的存储软件（一些大的互联网公司就是这样），一些较复杂的功能都想放在应用中来实现，那么选择MySQL或一些NoSQL产品都是合适的。如果你应用的数据访问很简单（如大多数的博客系统），那么后端使用 MySQL也是很合适的。但是如果你的应用不像博客系统那么简单，又不想消耗太多的开发资源，那么PostgreSQL是一个很明智的选择。最有说服力的例子就是图片分享公司 Instagram，在使用“Python+PostgreSQL”架构后，只是十几个人就支撑了整个公司的业务。在数据库中使用PostgreSQL的感觉就像在开发语言中使用Python，会让你的工作变得简洁和高效。

1.2.2 Postgresql 与 Oracle数据库对比

从功能上说，PostgreSQL可以与Oracle数据库媲美。Oracle数据库是目前功能最强大的商业数据库，PostgreSQL则是功能最强大的开源数据库。Oracle在集群功能如 RAC、ASM方面比较强，但PostgtreSQL也有一些比Oracle强的特性，如在索引和可扩展等方面。
PostgreSQL与Oracle有很多相似之处，它们都是使用共享内存的进程结构，客户端与数据库服务器建立一个连接后，数据库服务器就启动一个进程来为这个连接服务。 这与MySQL的线程模型不一样。PostgreSQL与Oracle一样，PostgreSQL的WAL 日志与Oracle的Redo日志都是用于记录物理块数据的变化的，这与MySQL的binlog是不一样的。
PostgreSQL在主备库方面非常完善，可以搭建同步备库、异步备库、延迟备库， 在同步备库中可以配置数据同步到任意个备库上。只读备库在查询与应用日志的冲突解决方面提供了更多的参数控制，让DBA更容易控制只读备库的查询冲突。在配置备库的过程中，PostgreSQLttOracle简单很多，备库的搭建也更灵活。
PostgreSQL与Oracle的不同之处在于，PostgreSQL有更多支持互联网特征的功能。 如PostgreSQL数据类型支持网络地址类型、XML类型、JSON类型、UUID类型以及数组类型，且有强大的正则表达式函数，如where条件中可以使用正则表达式匹配，也可以使用Python、Perl等语言写存储过程等。
另外，PostgreSQL更小巧。PostgreSQL可以在内存很小的机器上完美运行起来， 如在512MB的云主机中，而Oracle数据库基本要在数GB的云主机中才可以运行起来。 Oracle安装包动辄几个GB以上级别，而PostgreSQL的安装包只有几十MB大小。PostgreSQL在任何一个环境都可以轻松地安装。Oracle数据库安装花费的时间是在小时级别，而PostgreSQL在分钟级别就可以完成安装。