引言

PostgreSQL(简称pg)是近几年增长率较快的开源数据库,很多企业由原来的MySQL转向pg,在这里对比这两大开源关系型数据库的优劣,以便使用时快速选型。

# PG与MySQL的一些特性对比
特性 PG MySQL
口号性特点 最先进的开源数据库 最流行的开源数据库
SQL编程能力 强大的SQL能力,包括丰富的统计函数和统计分析,对BI有很好的支持 没有强大的统计功能支持
数据类型 丰富的数据类型支持,包括地理信息、几何图形、json、数组等,json也可以建立索引 在地理信息支持度上不如PG,不支持几何图形等数据类型
事务能力 完整的ACID事务支持 不是完整的支持ACID事务特性
join 支持nested-loop, sort-merge, hash三种类型 只支持nested-loop
Text类型 没有长度限制,可以直接访问,可以索引,可以全文索引 有长度限制
复杂查询 支持窗口函数,支持递归,支持with语句 不支持窗口函数、递归等
索引 多种索引类型,包括b-tree,hash,gin,gist等,可以对模糊查询、正则表达式、地理信息系统等建立索引 主要是b-tree索引
数据复制 支持同步复制,支持流复制 支持异步复制
查询优化器 功能更强大,对子查询的支持更高效 子查询效率不高
7*24 隔一段时间需要进行VACUUM 适用7*24
性能和适用场景 复杂查询 简单业务场景,更高的TPS
运维资源 专业DBA相对较少 众多DBA有丰富的运维经验
大小写 大小写敏感 大小写不敏感
行大小限制 无限制 65535

PG存在的问题和解决方案

序号 问题描述 问题分析 解决方案
1 主从同步WAL方式,主库挂掉,从库有时会启动失败,或者很慢 应该是主库没有保留足够远的xlog数据,导致主从时间线不一致 主库的wal_keep_segments要设置的足够大,个人推荐要保证允许从库挂48小时:wal_keep_segments=(48小时*log增量大小)/16M
2 在大数据量情况下,PG对地理位置信息(附近客户)的查询效率较慢 关系数据库在处理大数据方面天然有劣势 可以进行分库处理,尽量保证单库的数据量在PG的处理能力之内;如果效率还满足不了,采用ES来处理
3 WAL从库只能是只读的,报表比较难处理 如果是双主或者是多主,数据同步是大问题 根据业务进行调整,业务架构不需要双主,BI对从库只有读的需求
4 基于XID的MVCC实现机制,导致需要定时触发VACUUM,导致性能的抖动 基于MVCC的方式导致会产生垃圾,这些垃圾需要在合适的时间被回收 这个需要根据实际情况调整回收的策略,需要DBA配合调优;就像JVM的GC一样
5 大小写敏感,这对于习惯于原来大小写不敏感的编程方式,可能会很不习惯 PG对对象和数据都是大小写敏感的 对象的大小写敏感问题:PG在分析SQL脚本时,对不加双引号的所有对象名转化为小写字母;数据的大小写敏感问题:可以在存储的时候进行转换,或者查询的时候进行转换,可能需要配合建立表达式索引
6 MVCC机制带来的SSD写放大和索引写放大 支持并发读写的数据库都会有写放大问题,不是PG独有 PG的HOT技术,以及基于Heap的存储技术,对写入和索引可以调优写放大的问题;实测也部分验证了这一点
7 PG的复制低效,有写放大 PG的流式复制复制非常高效,支持流的加密和压缩;并且从9.4开始支持逻辑复制
8 备库的MVCC支持较差,查询会与恢复堵塞 基于物理复制或逻辑复制,只要备库拿来使用,都有可能出现查询与恢复冲突的情况。PG对于冲突的处理非常的人性化,你可以选择恢复优先 or 查询优先,设置时间窗口即可。同时PG还支持备库的QUERY反馈机制,主库可以根据备库的QUERY,控制垃圾回收的延迟窗口,避免QUERY和垃圾回收的冲突。
9 跨版本升级较难,跨版本不支持复制 现在的PG对大版本升级已经有很好的支持
10 调优比较复杂 PG的调优参数较多,各个参数还有联动 补充专业DBA,专业指导,积累经验

部分问题可在https://yq.aliyun.com/articles/58421查看。