|
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 配套的实用性很强的一些主流开源软件（如 PgBouncer、Slony-I、Bucardo、PL/Proxy、pgpool-II、Postgres-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 了。另一个就是ＰＧ在主流开发语言上的接口框架和ＯＲＭ等选择太少，让想尝鲜的开发人员不能快速的使用起来，做到先尝试、后研究精通的模式。
个人对ＰＧ还是很有好感的，一直努力在项目中使用，尽量去替代商业化的ＤＢ产品。真切希望ＰＧ能够等到广大开发人员的认识、认同，成为开源数据库之中的翘楚。我想，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 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
