PostgreSQL 优势,MySQL 数据库自身的特性并不十分丰富,触发器和存储过程的支持较弱,Greenplum、AWS 的 Redshift 等都是基于 PostgreSQL 开发的

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了PostgreSQL 优势,MySQL 数据库自身的特性并不十分丰富,触发器和存储过程的支持较弱,Greenplum、AWS 的 Redshift 等都是基于 PostgreSQL 开发的，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15712字，纯文字阅读大概需要23分钟。

内容图文

MYSQL数据库（5） 

PostgreSQL 是一个自由的对象-关系数据库服务器（数据库管理系统），功能很强大。包括了可以说是目前世界上最丰富的数据类型的支持，比如 IP 类型和几何类型等等。

发现很多读者都问过这样一个问题：如果打算为项目选择一款免费、开源的数据库，那么你可能会在MySQL与PostgreSQL之间犹豫不定。针对这个问题，我们采访到了即将在Postgres中国用户2016大会上作分享的平安科技数据库技术部总监 汪洋，请他从实践者的角度给出一些经验分享和建议。

InfoQ：对于金融领域的应用来说，使用 PostgreSQL 与使用 mysql 相比，优势有哪些？

汪洋：这个问题的本质回归到为什么平安科技会选择 PostgreSQL 来做为 Oracle 的替代品。

我们并不是没有使用过 MySQL，事实上，从2013年底我们就开始引入和使用 MySQL 数据库，比 PostgreSQL 还早了一年多。但最后，经过评估和实践的检验，发现 PostgreSQL 对于大部分的金融应用场景更加适合，因此还是选择了 PostgreSQL 数据库做为科技的主流开源数据库。

这里面包含有多方面的因素。支付场景是金融领域里较为受关注的部分。平安集团是一家综合性金融服务企业，业务覆盖了包括保险、基金、投资、证券等。保险业务就是最明显的例子，业务逻辑极其复杂，即使是 OLTP 系统，也涉及对大量数据的访问和计算。因此，很多业务逻辑都是在数据库内部通过 package 或者 stored procedure 来实现的，这种处理方式对于 PostgreSQL 来说游刃有余。

我认为 MySQL 属于 Thin Database，而 oracle 和 PostgreSQL 属于 Thick Database。Thin Database 的概念在于轻数据库重应用。换句话说，就是数据库只做为数据的存储，只提供简单的查询访问。而复杂的业务逻辑前移到应用服务器端来完成。MySQL 数据库自身的特性并不十分丰富，例如 innoDB 存储引擎只提供索引组织表形式的数据存储格式，某种程度上限制了它的使用场景。对于触发器和存储过程的支持较弱，并不建议使用。应用的 CRUD 操作尽量通过主键进行，虽然支持二级索引，但通过二级索引操作会有性能损失。在进行关系型数据库中必不可少的表关联操作时，只支持 Nested Loops 关联方法，缺少对  sort merge join 和 hash join 的支持。当关联表超过2张时，MySQL 的优化器有时生成的执行计划不优，造成性能下降。

也正因为如此，我们制定了针对 MySQL 的开发规范，例如表的大小不要超过多大，尽量书写简单查询，通过主键来访问数据，不要写超过2张表相互关联的 SQL 等。MySQL 更加适合业务逻辑简单的 OLTP 应用。而对于 PostgreSQL 来说，无论业务逻辑简单还是复杂，OLTP 还是 OLAP 负载，PostgreSQL 都可以支持，也都有很成熟的产品，很多著名的 OLAP 数据库产品例如 Greenplum、AWS 的 Redshift 等都是基于 PostgreSQL 开发的。PostgreSQL 的查询优化器非常强大，而且对于三种表关联方法 Nested Loops，Sort-Merge Join 和 Hash Join 全部支持，对于复杂的 SQL 语句优化起来也不是问题，而这正是保险行业进行业务逻辑处理需要的特性。

PostgreSQL 还提供了强大的数据库内部 function 支持，而且可以用多种语言编写，对于复杂业务逻辑计算以及大数据量访问完全可以在数据库本地化实现，大大减少了网络交互成本，从而整体提升应用性能。

因此，也有人说 PostgreSQL 不止是一个数据库，更是一个强大的开发平台，足见其功能之丰富。

我认为，PostgreSQL 的进程体系结构与 MySQL 的线程体系结构的不同点在于更加适合于垂直扩展。现在大家都在谈分布式架构，横向扩展，share nothing，我们不妨微观上来看一下进程和线程架构的不同。以进程为主的体系结构，在操作系统内部来看，就是一个分布式架构，每个进程有自己的地址空间，share nothing，只在需要交互的时候通过 IPC 机制 shared memory 和 semaphore 来进行通讯，大多数情况下都是在处理自己的事务，互不干涉；而以线程为主的体系结构，共享地址空间，相当于 share everything，需要处理好线程间的关系以及对资源的访问控制。因此，创建进程的成本虽然相比创建线程高，但却容易扩展。对于平安如此体量的企业，系统负载非常大，虽然横向扩展非常重要，但垂直扩展能力也是不容置疑需要考虑的因素。而且 PostgreSQL 和 MySQL 的横向扩展能力都需要中间层或者代理层来实现，并不是依靠数据库内核完成，在这一点上两者并没有区别，至少现在如此。这样数据库内核的垂直扩展能力更加重要。如果说到横向扩展技术，MySQL 有 Fabric 和 TDDL，PostgreSQL 近年在分布式上发展也非常之快，有 Postgres-XL，pg_shard，Postgres-XZ 等产品。

还有重要的一点。在谈到 TCO（Total Cost of Ownership）的时候，大家会觉得 PostgreSQL 和 MySQL 两者都是开源产品，并没有差别。但实际上，TCO 除了可能的 License 成本之外，还包括学习成本和运维成本。对于金融领域来讲，过去使用的都是 Oracle、DB2 等商业数据库。之前 Oracle 和 DB2 这些商业化数据库发展了几十年，数据库自身的特性非常丰富，和 PostgreSQL 都属于 Thick Database，也难怪 PostgreSQL 被称为开源领域的 Oracle。大家可以想一下，对于用惯了商业化数据库的开发人员还是 DBA 来讲，哪一种数据库对于他们来讲更加熟悉一些？学习成本更低？多年的经验更加容易转化，复用？运维成本更低？我想答案是显而易见的，这也是为什么平安选择 PostgreSQL 的一个重要考量因素。

InfoQ：当数据量很庞大的时候，平安科技是怎么利用 PostgreSQL 实现异地备份的？

汪洋：平安对于系统的高可用和数据安全是极其重视的。我们结合了多年积累的数据库运维经验，为 PostgreSQL 建立了 MAA（Maximum Availability Architecture）架构。我理解这里的备份应该是广义的备份概念，而不单指的是磁带备份。我们对于 PostgreSQL 的磁带备份是在本地的主库上进行的。由于不支持直接上带，所以先把备份写入到 WOS（Web Object Scaler）分布式存储上，然后再由 WOS 上带。

由于在主库备份会与应用争抢 IO 资源，最初也评估过在本地从库备份，但我们采用的 pg_rman 工具还是需要在主库发起 begin backup 和 end backup 命令，考虑到网络影响，加之当前 IO 资源也比较充足，所以备份暂时还是在主库上进行，后续会考虑将备份操作迁移到从库。异地的数据库备份是通过搭建远程容灾库实现的，使用 PostgreSQL 自身的 Streaming Replication 技术以异步方式传输并应用 WAL 日志来保持本地主库和远程从库之间的数据同步。

这种情况下，主从之间的差异取决于日志量产生的速度，与数据库大小关系不大。当前，平安科技最大的 PostgreSQL cluster 是 3TB，每分钟产生的 WAL 在 80MB 左右，在这样的负载下，本地主库基本上和远程从库保持准同步的关系。

InfoQ：使用开源的 PostgreSQL 数据库，平安科技在保证系统稳定、性能方面做了哪些优化措施？

汪洋：确实，PostgreSQL 是一个开源数据库，我们在引入的时候对于系统的稳定性也尤其关注和重视。加之平安是一个综合金融服务集团，业务范围涵盖金融行业的方方面面，各个业务线之间也有复杂的相互调用关系。如果一个系统出现问题，经常会有“牵一发而动全身”的影响。不仅可能会造成经济损失，也会影响声誉，所以在系统高可用、可靠性和稳定性上面我们做了大量工作。

最初我们采用的一主两从的高可用架构。本地一主一从，远程一从，大家都使用本地磁盘。这样，当主数据库发生问题，可以快速切换到本地从库继续提供服务。可很快我们发现，这样在故障切换时有可能会发生数据丢失！对于某些金融应用来讲，数据丢失是不能接受的。由于考虑到主从之间的网络延迟会导致 OLTP 应用性能下降，主从之间并没有采用同步复制（尽管PostgreSQL支持，这也是优于MySQL的地方）。

由于主从之间的强一致性没有保证，所以切换时可能会发生数据库丢失，影响 RPO。同时，使用本地盘使得计算和存储强耦合。数据显示，系统出现问题更多的是 CPU 和 RAM 而不是存储。这样，即使不是存储问题，CPU 或者 RAM 的故障也会引发切换导致数据丢失。因此我们对架构进行了优化，一方面从使用本地盘切换为使用共享盘，另一方面为 PostgreSQL 建立了主备集群。这样，在服务器发生故障时，集群可以快速将 Postgres 实例从主机切换到备机，并且因为使用的是共享盘，数据的一致性和完整性得到保障，不会有丝毫的数据丢失。

从性能上来看，共享盘也提供了比本地盘更好的 IO 性能，无论从 IOPS 上还是吞吐量上。在每一次的架构优化调整时，不止从理论上论证其可行性，也都是经过严格的测试验证后才会实施。这点也体现在 Linux IO 调度器的选择上。linux 内核在 IO 调度上有四种策略，分别为 NOOP，CFQ（Complete Fairness Queueing），Deadline 和 Anticipatory。我们分别针对机械磁盘和 SSD 进行了 IO 的基准测试，在测试中模拟了数据库读写负载 IO 的行为，包括随机读、随机写、顺序读、顺序写的不同组合，还包括对不同 IO 大小的测试，在各种组合下观察监控不同 IO 调度策略下的性能数据。根据性能数据制定不同 IO 调度器设置规范。基于机械磁盘，CFQ 和 Deadline 表现优于其他两种策略，但 CFQ 和 Deadline 之间差别不大，为了保持更大的适应性，选择了 CFQ；而对于 SSD 闪存，经过测试 NOOP 和 Deadline 要由于 CFQ 和 Anticipatory，而且 Linux 7 内核已经缺省使用 Deadline，因此在 SSD 上我们采用 Deadline。

对于开源数据库来讲，监控自然也是必不可少的。我们所有的数据库都采用统一的监控框架 Zabbix，极大降低了运维成本。根据 PostgreSQL 自己的特性，结合了多年 Oracle 运维的经验，定制了很多监控指标。例如对慢查询的监控，对于剩余事务年龄的监控用于判断是否需要及早进行 vacuum，以及对 checkpoint 效率的监控等。为了对每一个 cluster 的性能有一个全面的了解，方便进行性能趋势分析和定位问题发生的异常时段，我们借鉴了 Oracle 中非常优秀的 AWR（Automatic Workload Repository）概念，自己开发了 PGAWR 数据库性能报告工具。里面不仅有 OS 的性能数据，也有 DB 整体的负载情况和性能数据，还有按照不同维度排序的 SQL 语句列表，可以对 DB 极其运行主机的情况一目了然，快速诊断问题和发现异常 SQL 语句。

种种，都是为了能够让 PostgreSQL 运行地更稳定，在问题发生时能够快速定位、分析并加以解决，满足金融行业对系统运行和恢复时效的需要。

InfoQ：能不能介绍一下平安科技 PostgreSQL 开源数据库的架构设计，以及有哪些规范？

汪洋：从高可用架构上来看，对于 PostgreSQL 数据库分为两个层面，首先是主机故障，其次是存储故障。之前提到，大部分的问题是由主机故障导致，存储故障只占很少一部分。由于主机和故障发生机率的不对等性，我们进行了计算和存储的解耦。为了防范主机故障，我们采用共享存储，并且建立 Linux 服务器集群在主机发生问题时快速切换。由于计算和存储解耦，也更加方便对主机进行主动维护，大大降低了数据丢失的可能性。虽然存储故障发生机率小，但影响面却很大，所以我们也没有忽视对存储故障的防护。在本地和远程我们都建立了 PostgreSQL 的从库，它们和主库之间都采用异步复制策略。如果主库存储发生问题，短时间无法恢复，就会切到本地从库继续提供服务；如果发生地区性问题，则切换到远程继续提供服务。实现两地三中心的防护。

除了高可用架构规范，在 PostgreSQL 架构规范中我们还定义了：

1. 生产环境所采用的 PostgreSQL 版本号，以及 PostgreSQL 的 Patch 策略和升级策略；
2. 服务器和操作系统用户的命名规范；
3. 数据库应用使用的字符集设置；
4. 每一个 cluster 的卷和目录命名规则；
5. PostgreSQL 数据库的标准参数设置规范。

此外我们还制定了用户架构规范，在规范中，定义了 PostgreSQL cluster 都应该有哪些标准的初始化用户，例如 DBA 用户，部署专用用户，复制用户，监控用户等。对于每一个 Database，创建属主用户以及应用连接用户，以最小化权限原则授予应用连接用户必需的权限，规避进行危害性操作的风险。对于运营人员，在规范中也有明确的定义，按照职责划分创建不同的专属用户。这样，实现 DBA、运营和开发人员的权限分离，一方面最小化权限将对数据库的可能危害降到最低，另一方面便于后续的审计和追踪。

对于数据库系统的健康、稳定运行，开发规范一样重要。在给开发人员制定的开发规范中，我们分别从命名规范和设计规范两大方面进行了描述。命名规范包括对表、字段、索引、视图等数据库对象的命名要求，而设计规范包括对表、索引、分区表在设计时的一些原则的描述。通过这些规范要求，让开发人员可以相对简单地进行应用系统的设计，降低系统运行时的性能风险。而且，我们还将这些规范集成到了数据库自动化审计平台中，例如在版本提交阶段自动审核数据库版本是否合规，SQL 语句是否包含全表扫描，隐式转换等高风险操作等，进一步加强了质量控制，降低了系统上线后的风险，简化了运维工作。

InfoQ：在后期运维过程中对架构、规范进行了怎样的优化，引入了哪些新技术？目的是什么？

汪洋：PostgreSQL 毕竟在平安引入的时间不长，到今天也差不多1年半的时间。虽然，我们制定了 PostgreSQL 的推广策略，按照系统复杂度、系统重要程度分层级逐步推广，但在推广使用的过程中，还是会遇到这样那样的一些问题。对此，我们要求每一个问题都要找到根本原因，只有这样才能有的放矢地不断调整和优化。

之前提到的从最初的使用主从架构和本地磁盘调整到使用 Linux 服务器集群加共享存储就是一个明显的例子。在这里，我想再分享一个例子。有一次，在运行的一个数据库上，发现产生的 WAL 日志非常之快，数据库本身并不大，只有 100GB，但每小时产生的 WAL 却可达到 300GB 之多。这是一个典型的小库高负载的案例，通过详细分析发现，虽然库本身负载很高，但也和未经优化的 checkpoint 相关参数配置相关。负载高和 checkpoint 的配置形成了恶性循环，导致 WAL 生成量巨大，造成系统磁盘空间告急此类可用性风险。虽然调整过一次参数，但因为未找到根本原因，并没有从根本上解决问题。随着越来越多高负载的重要数据库使用 PostgreSQL，对于以后继续推广存在较大隐患，必须找到根本原因，彻底解决问题。经过对 WAL 生成原理进行研究，对日志进行分析，甚至对 PostgreSQL 源码进行理解，终于对 WAL 生成机制有了较为清晰的了解。并针对性地制定了参数调整策略和未来使用新版本时的规范。问题主要和两个参数相关，一个是 wal segment size，一个是 checkpoint_segments。缺省值设置较低，wal_segment_size 是 16MB，checkpoint_segments 是 32，不太适合运行金融系统的高负载应用。由于 wal segment size 只能在 PostgreSQL 软件编译时调整，所以对于现有系统，只能调整 checkpoint_segments。对于这个特定的数据库，第一次在没有找到根本原因的情况下将 checkpoint_segments 调整到了 128，但没有从根本上解决问题。在分析后，调整到了 512，WAL 生成量大幅减少，达到了预期的效果。在未来使用新的 PostgreSQL 版本时，我们调整了规范，在编译时将 wal segment size 调整为 64MB（缺省的4倍），并且根据系统的负载情况灵活调整从9.5开始引入的参数 max_wal_size，使得系统可以更加稳定地运行金融系统应用。

另外，随着 PostgreSQL 对 JSON 的支持越来越丰富，越来越成熟，我们也开始在 PostgreSQL 上推荐使用 JSON 数据类型。特别要提到的是，在关系型数据库领域，PostgreSQL 的 JSON 支持要比 MySQL 和 Oracle 都早，MySQL 从5.7开始支持 JSON，Oracle 是从 12cR1 将对 JSON 的支持 backport 到了 11.2.0.4，PostgreSQL 则走在了前面。和 Document DB 领域中原生支持 JSON 数据类型的佼佼者 MongoDB 比较，经过测试验证，我们发现 PostgreSQL 的性能并不差。而 mongodb 由于属于 NoSQL，并不支持表（或者说 collection）之间的关联，PostgreSQL 恰好填补这方面的空缺。对于一些有 JSON 文档存储需求，但又需要和关系型或者结构化数据关联的应用场景 PostgreSQL 无疑是非常适合的一款数据库。

当然，我们对于 PostgreSQL 还在不断地学习，不断地探索。但随着对它的认识越来越深入，我们的信心也在不断增强。特别是前不久 PostgreSQL 9.6 的发布，里面的很多新特性更是让人期待，也让大家看到了 PostgreSQL 的未来！

InfoQ：通过 PostgreSQL+金融的完美结合这一案例，你有哪些心得感想可以和 InfoQ 的读者分享的？

汪洋：2015年可以称之为平安的 PostgreSQL 元年，从2014年底开始接触 PostgreSQL，到2015年初开始立项准备、调研、评估、测试、制定规范、制定推广策略，到真正的使用推广，到现在有超过1000个 PostgreSQL 的实例在运行，一路走来，既为取得的成绩而高兴，同时又感觉殊为不易。但今天我可以告诉大家，当初的选择是正确的，我很庆幸选择了 PostgreSQL 这样一款优秀的数据库产品，并且在团队的努力下能够让 PostgreSQL 在平安扎根，发芽并且茁壮成长。过程中，不是没有人质疑过，但我们相信自己的判断和选择，更重要的是我们相信 PostgreSQL 产品本身能够帮助我们实现支撑金融系统应用的稳定运行，降低数据库运营成本，拥抱开源技术，达到信息自主可控的目标。于是，我们耐心地说服，全力地配合，悉心地指导，以获得大家的认同。渐渐地，事实和数据也证明了 PostgreSQL 是一款非常稳定的高性能数据库产品，能够运行金融级别的应用系统。

每一种数据库产品都有它的应用场景，每一家公司也都有着不同的资源，在选择一款数据库产品时，不能随大溜，跟潮流，一定要根据自身的情况，对包括未来数据库技术的发展，业务的特性，金融监管的要求，公司人员具备的技能等做出全方位的分析，进而选择一款最适合自己的数据库。而且，在数据库技术蓬勃发展的今天，企业可能也会根据不用的应用场景选择多种数据库产品，用以满足存放和访问不同数据类型的需要。这本身也是一个寻找最优化配置的过程，实现价值的最大化。

还有一点我觉得很重要，就是每一种数据库，都要按照它原本的设计思想和设计目标去使用它，而不能把它当成黑盒子。首先要去了解它，然后再去适配它，只有这样才能达到最好的磨合效果，才有可能将性能发挥到极致。反之，则会出现强扭的瓜不甜，导致性能问题。

虽然平安科技的 PostgreSQL 应用规模已经很大，取得了不小的成绩，但平安在 PostgreSQL 数据库上还有很长的路要走。例如如何使用分布式架构来提升 PostgreSQL 的横向扩展能力，支撑 VLDB（Very Large Database），不仅可以运行 OLAP 负载，也可以运行 OLTP 负载；还有如何丰富 PostgreSQL 的性能数据和诊断数据，进一步提升在问题发生时的分析、定位以及解决时效，将对系统的影响程度降到最低。

这些都是我们接下来面临的挑战，对于象平安这样的综合金融服务集团挑战尤其严峻。但可喜的是，我们看到 PostgreSQL 的发展越来越成熟，功能越来越丰富，很多我们希望的特性要么已经被实现，要么就是已经被纳入规划。PostgreSQL 的社区也在不断壮大，日趋活跃，不断有新的公司开始采用 PostgreSQL。在这样的大好形势下，相信 PostgreSQL 一定可以巩固在开源数据库领域的领导地位，不仅成为最先进的开源数据库，而且成为金融服务行业最强大的开源数据库！

PostgreSQL 优势,MySQL 数据库自身的特性并不十分丰富,触发器和存储过程的支持较弱,Greenplum、AWS 的 Redshift 等都是基于 PostgreSQL 开发的

标签：article   fabric   对象   rpo   大型   案例   安全   设计   实践   

本文系统来源：http://www.cnblogs.com/stevendes1/p/7072359.html

内容总结

以上是互联网集市为您收集整理的PostgreSQL 优势,MySQL 数据库自身的特性并不十分丰富,触发器和存储过程的支持较弱,Greenplum、AWS 的 Redshift 等都是基于 PostgreSQL 开发的全部内容，希望文章能够帮你解决PostgreSQL 优势,MySQL 数据库自身的特性并不十分丰富,触发器和存储过程的支持较弱,Greenplum、AWS 的 Redshift 等都是基于 PostgreSQL 开发的所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端