OSC 第 78 期高手问答精华汇总 —— 使用 PostgreSQL 去O

原创
2015/07/17 17:12
阅读数 776

OSCHINA 第 78 期高手问答 ( 6月10日- 6月16日) 我们请来了@osdba 唐成)为大家解答关于使用 PostgreSQL 数据库O 方面的问题。

原文地址:http://www.oschina.net/question/865233_239303 

唐成,拥有十几年数据库、操作系统、存储领域的工作经验,目前任沃趣科技首席数据库架构师。历任阿里巴巴数据库专家、高级数据库专家,从事过阿里巴巴的线上 Oracle、Greenplum、PostgreSQL 数据库的架构设计和运维工作,为阿里巴巴的 PostgreSQL 数据库的布道者,推动阿里云的关系型数据库服务(RDS)中提供 PostgreSQL 数据库服务,为阿里巴巴 PostgreSQL 数据库方面的技术带头人。

PostgreSQL修炼之道:从小工到专家从 SQL 基础、安装配置、数据类型、数据库的逻辑结构等基础知识一直讲到 PostgreSQL 的架构、技术内幕、特色功能、Standby、数据库优化以及一些配套的开源软件的使用,覆盖了PostgreSQL 领域方方面面的知识。书中不仅介绍了与 PostgreSQL 配套的实用性很强的一些主流开源软件(如 PgBouncerSlony-IBucardoPL/Proxypgpool-IIPostgres-XC),还针对 PostgreSQL 的特色功能(如规则系统、正则表达式、序列、GiST索引等内容)进行了总结。


精彩问答

PostgreSQL 基础问题:

@滔哥:作为一名 phper。用习惯了 MySQL, 一直想用 PostgreSQL,从安装到使用,折腾几天发现无法习惯~老师有嘛好的建议吗?

@osdba:其实学习任何新的东西,开始时都有一个心理适应期,过了这段时间就会好的。就象我刚开始使用 Mac 电脑一下。

@cevin:同是 N 年的PHPer。切换到 PostgreSQL 已经有一段时间了。除了一些必须用 MySQL 的地方(老板说要用,总监说要用,客户说要用)都是用 PostgreSQL。pgsql 是强类型,用 mysql 的时候没有在乎过“类型”的问 题,比如 pdo,参数绑定的类型统一是 string。到 pgsql 发现自己有多蠢……                        

@donhui:您好,数据库去O为何选择的是PostgreSQL,而不是Mysql或者其他开源数据库呢?是怎么做的技术选型呢?去O过程中您所遇到的最大问题是什么?谢谢

@osdba:在多数的Oracle数据库都有一定数量的复杂SQL,而PostgreSQL数据库 可以支持这些复杂的SQL。PostgreSQL与Oracle一样都是物理Standby,都是多进程架构,通过我的亲身迁移项目的实践,使用 PostgreSQL去O,应用的架构基本都不需要修改,只要修改少量SQL的语法就可以实现,而使用MySQL,通常都需要把整个应用都重写过才行 (注:小库除外)。

PostgreSQL中可以通过fdw方便把Oracle的数据同步过来(见 http://osdbablog.sinaapp.com/525.html  )。

@XiongDennis:PG作为开源数据库的学院派代表,其功能和性能是毋庸置疑的。但很奇怪为什么其流行度一直未能匹配其产品地位。我觉得主要原因有:PG国内技术社区 氛围不够,几个论坛(包括官方)基本上是冷冷清清,而且经常变换地址。新手在遇到问题后不能得到帮助,久而久之也就放弃了PG,转向其他用户更多的DB 了。另一个就是PG在主流开发语言上的接口框架和ORM等选择太少,让想尝鲜的开发人员不能快速的使用起来,做到先尝试、后研究精通的模式。

个人对PG还是很有好感的,一直努力在项目中使用,尽量去替代商业化的DB产品。真切希望PG能够等到广大开发人员的认识、认同,成为开源数据库之中的翘楚。我想,osdba 的这本书正是承担了这种布道者的责任。

@穷二代:请教一个具体的问题,如何用trigger 函数做到,传入表名,查询的参数。如果当表的表没有这一条就插入它,有则更新它。谢谢。

@osdba:TG_TABLE_NAME:触发器所在的表的名称。 TG_TABLE_SCHEMA:触发器所在的表的模式。 TG_NARGS:在CREATE TRIGGER语句里面赋予触发器过程的参。触发器中可以使用一些特殊的变量,如TG_TABLE_NAME代表了表名。

@向前冲:老师好,国内都有哪些公司在试用postgreSQL?深入学习postgreSQL源码前景怎么样?

@osdba:国内已有很多公司把PG用到生产系统中很多年了。如去哪儿网、斯凯网络。深入学习PostgreSQL源码很好的,即使你以后不做数据库开发了,你也可以把PG中的源码拷贝到其它项目中使用。如bcache中的64位crc代码就是拷贝PG中的。

@凌晨4点半:对MYSQL基本熟悉了,正想摸摸PostgreSQL了。刚好也是django的推荐数据库。另外学这个postgresql必须有啥基础才行吗?

@osdba:因为你已使用过MySQL,有一定的基础了,后续需要学习一下数据库的一些原理性的东西,如MVCC以及PG中的一些特色功能,最后再熟悉一下与PG相配套的一些开源软件,如pgpoolII、plproxy、pg-xc、slony-I等等。

@douglarek:您好 ,在拜读您的 PG 修炼之道的时候,看到了分区表一节,PG 的分区表基于表的继承,想问一下跟其他数据库的水平以及垂直分表有什么优缺点呢

@osdba:优化点是更灵活。通过继承可以实现动态的把数据从一个分区中移到另一个分区中(比方说 要重建一个分区,可以新建一个相同规则的分区,把旧分区的数据慢慢的移过来),还可以让不同分区有不同的字段。缺点就是因为分区是一张真正的表,当分区太 多时,访问分区的SQL语句在生成执行计划时需要访问每一张分区表的元数据,导致SQL的硬解析代价变大。

@mystar:请教一个问题,在最新版的9.x里面如何disable外键?改系统表的方式需要superuser的权限。在事务开头申明不使用约束的方式对我们的设计修改太多。

@osdba:无法临时禁止外键。但你可以删除外部,需要的时候再建起来。

PostgreSQL是一个严格的数据库,不允许违反约束的情况出来。不过你可以使用触发器完成这种功能,临时禁止时,可以禁止触发器。

@Gogo58:PostgreSQL 高并发多线程环境下,压力怎么样

@osdba:支持高并发,与Oracle数据库类似都是多进程的架构。

@Gogo58:PostgreSQL 在JAVAweb的传统项目中运用怎么样

@osdba:PostgreSQL可以很好的支持java环境。从Oracle迁移到PG中有一些 注意事项见:http://osdbablog.sinaapp.com/528.html  。常用的连接池软件如c3p0、dbcp以及阿里的Druid 都是全面支持PostgreSQL数据库的。

@godseraph:您好,请问在PostgreSQL中存储Word、pdf、tif图片等二进制文件应该使用哪种数据类型,对单个文件的大小有限制么?这种存储方式在日常读写以及流复制时的性能如何?谢谢!

@osdba:通常不建议存储大的二进制文件到数据库。如果数据量不大,为了方便,当然也可以。通常应该使用bytea类型,最大可以存2G大小的数据。在数据库内部是通过TOAST技术拆分很多行存储在行外的另一张TOAST表中的。通常对复制的影响与其它数据并没有太大的区别。

@骏驰:1、 PostGrep的数据表的一个索引一般最好不超过几个字段? 对于只用于批量删除和更新的表,有什么办法可以提高它的处理速度?2、数据库的空间规划有什么讲究,如表空间、索引表空间之类?3、一张数据表的数据量一 般是最好不超过几行?如果超过的话,有什么好的水平和竖向分割数据方法,这些对性能会有什么样的影响?

@osdba:1. 数据表上的索引数的限定,PostgreSQL与其它数据库并没有太多区别。如果是更新频繁而又需要多个索引的表,建议把fillfactor值设置小一些,如70或50。 2. 如果是批处理的中间表,数据可以丢失,则可以使用unlogged table,速度会快很多。

通常把表和索引的表空间分开来,把索引放到速度快的硬盘上。

PostgreSQL对表的行数基本没有限制。一张表的行数的多少,通常与后面维护这 张表的代价有关。当表中的行数很多时,当删除很多行数据时,就会比较慢,而如何我们把表的数据分区,当需要删除一些过期数据时,只需要删除相应的分区即 可,删除分区比删除一条条数据快多了。通常一张表的数据量维护在5千万行以下。


MySQL 与 PostgreSQL:

@pysnow530:早上好,之前用的一款开源软件openerp用的就是postgresql,但是但是没有仔细去研究数据库,想请教一下,postgresql跟mysql在架构上的主要区别有哪些呢?

@osdba:PostgreSQL是基于共享内存的多进程架构,而MySQL是多线程架构。多进程 架构中进程与进程是隔离的,一个进程的错误不会扩散到整个系统。而多线程架构,所有的内存数据都是共享的,一个线程有可能错误修改另一个线程的数据,导致 一些逻辑错误。所以多线程架构要想保证同样的可靠性对开发人员会有更高的要求,新功能的稳定期也会更长一些。当然多线程会更节省资源,能直接支持更多的连 接。

@mark35:多进程任务在多核cpu上面效率更高,多线程的对多核心利用率相对不高

@吐槽的达达仔:pg 有无类似于GG的准实时日志同步工具?? 公司主要用MYSQL,如何让他们也试试PG呢?

@osdba:这是通过解析redolog的逻辑同步功能,PG目前正在做这一块功能,但还不成熟。如果公司主要用MySQL,只有有来自业务上的挑战,才会让他们实用PG。这些挑战类似:需要复杂SQL,需要存储json数据时,需要地理信息GIS系统时。

@HDR:可以推荐一款MySql数据转换到PostgreSql的工具么?

@osdba:你可以看看wiki这里面的内容:https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

@empireghost:同是关系型数据库,PostgreSQL 相对于mysql有什么优缺点??现在nosql数据库很火,PostgreSQL相对于NoSQL数据库有什么优缺点???

@osdba:PostgreSQL相对于MySQL的优点是功能强大,物理复制技术可以很容易的保 证主备库的数据一致性,缺点是相应的资料较少。相对于nosql,PostgreSQL数据库本身没有集群功能,是一个单机数据库,(pg-xc是一个集 群数据库)。但其对json支持,也可以对json内部的数据结构做索引,所以也可以存储json这种半结构化的数据。

@是非非是非:目前企业中用的最多的还是mysql和oracle,pg对于这2个,能解决它们使用的什么痛点?

@osdba:PostgreSQL既有开源数据库的优点,不存在安全性问题,数据库本身的是免费的。他又有Oracle数据库的强大的功能。还支持一些nosql的特点,可以存一些半结构化的数据(json)。


使用 PostgreSQL 去 O:

@dodojava:P 的性能能达到或者超过 O的吗?是否有调整源码重新编译优化的相关内容?

@osdba:在有些场景下,PG的性能超过O,有些场景下性能不如O。所以不能简单的回答PG的性能超过O还是不如O。我曾经使用过benchmarksql提供的TPCC在同样的一台机器上对比过O和PG,发现PG的测试结果稍比O强一点。

@Lemon-Olivia:PostgreSQL 在集群中表现如何,替换O之后的稳定性可靠吗?

@osdba:我在阿里时,曾经替换过一个几百G大小的数据库,替换后到现在一直工作稳定。

@liyuj:我想问下如果用Postgres替换Oracle的话,除了Oracle特有的组件外,那些方面无法替换或者难度较大?

@osdba:对于一些超大型的Oracle数据库,因为有ASM和RAC,替换起来比较因难 ,通常需要使用一些分库分表的方案,如Postgres-XC,但Postgres-XC需要一定的技术实力才能搞定。另如果用了很多存储过程,也需要花一定的时间进行修改。

@大号黑蚂蚁:老师你好,我们有个项目使用的oracle数据库,有用到存储过程和表分区。在调用某些存储过程分析数据时,很慢。如果迁移到pq的话,为了不降低或提高性能,有什么需要建议或需要注意的?谢谢!

@osdba:需要先找到慢在哪儿,然后就容易对症下药了。通常优化SQL能解决。迁移到PG后,需要测试性能,对慢的SQL进行分析。PG中有一个慢查日志开关,可以把超过多少时间的SQL打到日志中。


其他:

@乌龟壳:您好,请问Greenplum的稳定性等如何,如果公司采用Greenplum的话,需要很多人手去运维吗?谢谢。

@osdba:现在最新版本Greenplum已解决了以前大部分稳定性问题。如果 Greenplum数据库的规模不大,如10台以下,数据量在20T以下,不需要很多人手运维。但如果达到30台以上,几百T以上的数据量,当然需要一定 的人手才能维护好。当然现在规模大的集群都需要有经验的维护人员。

@Peng_JK:postgresql 的 LB/HA 集群方案怎么样,对比 oracle 有什么优势呢?

@osdba:在我写的这本书中,介绍了与PG配合使用的一些开源软件与方案,如 PgBouncer、Slony-I、Bucardo、PLProxy、pgpool-II、Postgres-XC,你需要灵活组织这些开源软件来满足 你的业务需要。其中Postgres-XC现在比较热,是一个分库分库但对用户透明的真正的分布式架构。

其实PG与MySQL一样,数据库本身并不提供LB/HA的功能,需要使用LVS或pacemaker等开源的LB/HA软件。

@bloy:postgresql能提供一套高可用性方案吗??

@osdba:你需要灵活组织PgBouncer、Slony-I、Bucardo、 PLProxy、pgpool-II、Postgres-XC和一些开源高可用套件如pacemaker来实现高可用,网上有很多这样的案例,你可以在网 上搜看一下。当在也可以自己写简单的HA系统,就象我书中在PLProxy那章节中介绍的用python写的高可用方案。

@过马路的蚂蚁:在企业级应用高HA是一个保障,PostgreSQL 这方面好像没有成熟的方案?

@osdba:使用pacemaker配合PostgreSQL的HA是比较成熟的架构。当然所有开源软件都没有提供什么都做好的高可用方案,都需要一些定制的工作或找一相应的商业支持。

@霡霂:老师你好,上面说了六种负载均衡器,哪一种适合适合架构简单,要求功能单一(只要去负载均衡)的项目。可能这样问比较“小白”,上面这些都在什么样的场景下应用,哪一个更通用,哪一个更专精,大概就是想问这个。

@osdba:pgpoolII的通用性比较好一些,网上也有一些与pgpoolII的高可用方案。但pgpoolII性能要差一些。那几种不是负载均衡。负载均衡可以使用LVS

@风起的日子:你好,最近有个类似于商品秒杀的应用,请问使用Pg应该怎么控制库存问题?或者需要什么其他应用来配合Pg来实现该功能?有什么好的建议吗?

另外以前习惯使用SqlServer,经常会有临时写一段Sql,配合几个参数来查询一些数据,但是在Pg里面需要用匿名函数的方法写,而且在里面不让直接使用Select,请问这种场景下应该怎么操作最方便快捷呢?

@osdba:关于商品秒杀,使用PG与其它数据库没有大的不同,只是最好在把表的数据块的空闲空间 留的多一些(fillfactor),这样性能会好很多。PG中的函数不能直接象SQL Server写select就返回结果集,但也有简单的办法返回结果集,你可以看PG中的相关文档。有 “RETURN NEXT expression;”的语法,在函数中循环的使用这个语句就可以返回多行,具体可以见40.6.1.2. :http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

@MingjunYang:唐老师,对于100~500人的企业,单一业务的,有很多信息和文档资料需要录入erp,如果使用PG,对于磁盘存储规划有什么建议吗?还有备份方案选型有什么参考依据吗?

@osdba:选择带cache的Raid卡的机器,把WAL日志和数据文件放到不同的硬盘 上,WAL日志的硬盘在Raid卡上的cache打开,而数据文件在Raid卡上的cache关闭。备份可以使用数据库的热备份,即直接使用 pg_basebackup进行备份,可以2-3天一个全备份,把这期间的WAL日志也备份下来。

@haitaosoft:10年前一个产品用过pgsql(好像是7.x版本),感觉抗压性与db2差不多。

现在数据库一直不能 多写多读(最多是 一写多读,但是写的服务器还是要消耗io同步数据给读的服务器),能否由pgsql率先开发一个 前置中间件(后面是1个写服务器n个读服务器)?

对客户端的协议与数据库协议完全一样,只是写数据时,自动(同时或滞后)写入中间件之后的n个读服务器。这样的一写多读,写服务器就没有任何 同步的开销了。

再进一步,m个这样的前置中间件(每个后面有1+n个服务器),是否就可以实现多写多读了?

@osdba:一个主库,建n个备库,使用流复制(stream replicator)的异步格式,就可以实现你的这个功能。备库只读,主库可以写,主库是异步同步到备库。

@winie:pg数据,在表空间管理,不能增加多个数据文件?PG在权限控制上,如果我只给一个用户分配一个数据库,怎么才能屏蔽掉不让看其他数据库?pg-xl的mpp 架构可用性如何?稳定性如何?

@osdba:PG数据库的表空间实际上对应的是一个目录。而一般每一个目录下mount了一个文件系统。

目前无法让其他用户看到其它的数据库,但可以让此用户无法访问其它的数据库。

性能与Greenplum相比还有差距。Postgres-XL目前还处于beta阶段。真正用到生产系统上还需要等一等,但因为是开源的,稳定性在后期会很快提高上去。

@HYUO:唐老师您好,请问PG在Windows下性能怎么样?与Linux下比如何?另外您说PG采用了多进程模型,在Windows下也是如此?

@osdba:PG在Windows下的版本基本不能用在生产系统中,通常用做一个学习系统。PG在Windows下也是多进程的。

@水母干:你好,想请教一下,在千万级的分页查询中,PG需不需要单独优化?之前一直用mysql,在mysql里千万级分页是个坑...

@osdba:通常如果使用limit做分页,越查询到最后会越慢。一般需要优化,如走到一个索引上,每次查询时,使用上次查询到的位置,这样会好很多。

@段洪义:你好,我公司目前正在使用postgis,最大一张表大约2亿条数据。正在考虑使用postgresql-xc,postgresql-xc目前是否可以在生产环境使用?

@osdba:有一些挑战,需要有一定的技术实力。

@mailbaoer:我们也在实际服务中大量使用pg,最近遇到了一些问题,我们发现每个pg进程占用的内存都非常大,直到把内存吃满,然后kill进程,有时候会发生内存不 够导致全部进程被杀掉的问题,请问这样的问题该怎么解决呢,我们的单机内存已经到达上限,不能再提升了,如果考虑集群方案的话目前比较推荐哪种方式呢,非 常感谢

@osdba:我还没有遇到过你说的这种情况。你检查一下是不是work_mem等参数设置的太大?

@yinhex:比如集群,主从同步之类的跟mysql相比那个更简单设置恩?那个更稳定。p单表多少会出现瓶颈?P库的优化之类的又什么简单的建议?

@osdba:PG使用物理的主从同步,是比较简单的,可以级联。以前MySQL的GTID没有出来之前,三台机器之间的主从复制还是比较复杂的。而PG没有这个问题。

PG使用的是堆表,还可以建部分数据的索引,所以单表的大小基本没有限制,而MySQL是索引组织表,记录太多会有一些性能的下降。但一般为了维护的方便,不建议把一张单表搞的太大,一般在几千万行之下。

@douglarek:您好 ,我们公司在使用 PG 的过程中发现 PG 的读写分离有时候会出现延迟的现象,有时候延迟的比较厉害,请问有什么好的解决方案?谢谢

@osdba:我不知道你的主备库之间是否是用的流复制来做的,还是用基于触发器的如slony-I这样的东西做的。如果是流复制做的,通常是磁盘IO不够,需要优化IO,如换更快的磁盘,如走到xfs文件系统上等优化方法。当然事先需要找一下延迟的原因。

@军师:Postgres-XL 和 Postgres-XC 的区别在那里,该如何选用?

@osdba:Postgres-XL有一些针对数据仓库的优化,对一些大型的跨结点join的 SQL有优化,性能会更好。不过Postgres-XL更新,目前还没有发布正式版,而Postgres-XC不适合数据仓库,只适合OLTP,目前已发 布了几个正式版本。如果现在就要用到生产系统中,还是建议使用Postgres-XC。

@南湖船老大:听说PostgreSQL的主从性能损失很大,没有完美的解决方案,是这样的么?

@osdba:没有这种的说法,或着是你在网上看到这样的信息,但没有正确理解作者的意思。如果使用 异步模式,主库上只有把WAL日志传送到备库的开销,这个操作是不会阻塞主库上的任何操作的,只是占用一些网络带宽和少量的IO。而PostgreSQL 使用的是物理复制,相对于MySQL的逻辑复制开销更小。只有同步复制对主库的性能影响比较大。

原文地址: http://www.oschina.net/question/865233_239303  
展开阅读全文
打赏
0
1 收藏
分享
加载中
更多评论
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部