2018年PostgreSQL中国技术大会 为什么我们抛弃MongoDB和 MySQL，选择PgSQL 张启程 qc@aochuang.cn 奥创软件研究院（上海）研发中心 2018年PostgreSQL中国技术大会 任何数据库都很好用 • 我不是一个专业的DBA ，对数据库的理解不够深入，也不 一定准确，在此抛砖引玉，只是结合我们团队业务场景来 分享下我们选择数据库的过程。 • 2018年PostgreSQL中国技术大会 MySQL应用史 • 最开始做的站群系统WEB端使用MySQL： • copy简单快速，批量复制，替换修改简单； • 每个网站一个库，单体数据量小，数据类型固定； • 后来做SCRM系统，也首选MySQL： • 需求经常改动，每次改需求先改数据库； • 数据量越来越大，越来越不了解这只海豚； 2018年PostgreSQL中国技术大会 MySQL+MongoDB • • 迫于无奈，分拆数据，将需要用到事务、调用频繁的数据 继续用MySQL,将IM中聊天记录等数据量大的数据，用 MongoDB存储，减轻MySQL负担； • 这种方式虽然可以暂时解决问题，我们自己用也没有任何 问题，可以结合各自数据库优点，发挥性能。但是我们有 很多客户是需要私有化部署数据库，让每家客户都接受这 种方式，会为甲方增加很多服务器以及运维成本，可能客 户数据量很少，也需要使用双数据库，更新运维都比较麻

未来演进 Greenplum发展史@Alibaba rac1 rac2 rac3 rac n Oracle RAC 11G ODPS ETL建模 交互式分析 AnalyticDB for MySQL 实时数仓 ⚫ All In One数仓去O ⚫ 交互式分析 ⚫ 中小规模实时数仓 小烟枪在阿里的场景裂变 Greenplum发展史@Alibaba 产品云化-服务中小企业  IaaS、PaaS、SaaS皆云化-大势所趋 PostgreSQL 构筑云上数据仓库 1：实时数仓场景 通过 DTS/Kettle/rds_dbsync 实时同步交易库数据到 ADBPG MySQL/PG->DTS->ADBPG 2：离线数仓场景 数据集成按天/小时 批量同步交易库数据ADBPG MySQL/PG/SQLServer/Oracle –>DataX/OSS-> ADBPG 3：大数据计算场景 同步MaxCompute/Hadoop数据，ADBPG做在线分析

数据库总体架构 应用 逻辑复制 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 SQL语句 数据 MYSQL PG ORACLE LOGICAL Broker M1 M2 M3 Mn Master 逻辑解码 产生SQL+数据 MQ消息队列 DB重放语句

contrib/dbmirror improvements (Steven Singer) • New contrib/xml2 (John Gray, Torchbox) • Updated contrib/mysql • New version of contrib/btree_gist (Teodor) • New contrib/trgm, trigram matching for PostgreSQL pgcrypto (Marko Kreen) • Prevent crash in xml (John Gray, Michael Richards) • Update oracle • Update mysql • Update cube (Bruno Wolff III) • Update earthdistance to use cube (Bruno Wolff III) • Update btree_gist Allow inet arrays in /contrib/array (Neil) • GiST fixes (Teodor Sigaev, Neil) • Upgrade /contrib/mysql • Add /contrib/dbsize which shows table sizes without vacuum (Peter) • Add /contrib/intagg, integer

contrib/dbmirror improvements (Steven Singer) • New contrib/xml2 (John Gray, Torchbox) • Updated contrib/mysql • New version of contrib/btree_gist (Teodor) • New contrib/trgm, trigram matching for PostgreSQL pgcrypto (Marko Kreen) • Prevent crash in xml (John Gray, Michael Richards) • Update oracle • Update mysql • Update cube (Bruno Wolff III) • Update earthdistance to use cube (Bruno Wolff III) • Update btree_gist Allow inet arrays in /contrib/array (Neil) • GiST fixes (Teodor Sigaev, Neil) • Upgrade /contrib/mysql • Add /contrib/dbsize which shows table sizes without vacuum (Peter) • Add /contrib/intagg, integer

contrib/dbmirror improvements (Steven Singer) • New contrib/xml2 (John Gray, Torchbox) • Updated contrib/mysql • New version of contrib/btree_gist (Teodor) • New contrib/trgm, trigram matching for PostgreSQL pgcrypto (Marko Kreen) • Prevent crash in xml (John Gray, Michael Richards) • Update oracle • Update mysql • Update cube (Bruno Wolff III) • Update earthdistance to use cube (Bruno Wolff III) • Update btree_gist Allow inet arrays in /contrib/array (Neil) • GiST fixes (Teodor Sigaev, Neil) • Upgrade /contrib/mysql • Add /contrib/dbsize which shows table sizes without vacuum (Peter) • Add /contrib/intagg, integer

contrib/dbmirror improvements (Steven Singer) • New contrib/xml2 (John Gray, Torchbox) • Updated contrib/mysql • New version of contrib/btree_gist (Teodor) • New contrib/trgm, trigram matching for PostgreSQL pgcrypto (Marko Kreen) • Prevent crash in xml (John Gray, Michael Richards) • Update oracle • Update mysql • Update cube (Bruno Wolff III) • Update earthdistance to use cube (Bruno Wolff III) • Update btree_gist Allow inet arrays in /contrib/array (Neil) • GiST fixes (Teodor Sigaev, Neil) • Upgrade /contrib/mysql 1388 Appendix E. Release Notes • Add /contrib/dbsize which shows table sizes without vacuum (Peter)

contrib/dbmirror improvements (Steven Singer) • New contrib/xml2 (John Gray, Torchbox) • Updated contrib/mysql • New version of contrib/btree_gist (Teodor) • New contrib/trgm, trigram matching for PostgreSQL pgcrypto (Marko Kreen) • Prevent crash in xml (John Gray, Michael Richards) • Update oracle • Update mysql • Update cube (Bruno Wolff III) • Update earthdistance to use cube (Bruno Wolff III) • Update btree_gist Allow inet arrays in /contrib/array (Neil) • GiST fixes (Teodor Sigaev, Neil) • Upgrade /contrib/mysql • Add /contrib/dbsize which shows table sizes without vacuum (Peter) • Add /contrib/intagg, integer

contrib/dbmirror improvements (Steven Singer) • New contrib/xml2 (John Gray, Torchbox) • Updated contrib/mysql • New version of contrib/btree_gist (Teodor) • New contrib/trgm, trigram matching for PostgreSQL pgcrypto (Marko Kreen) • Prevent crash in xml (John Gray, Michael Richards) • Update oracle • Update mysql • Update cube (Bruno Wolff III) • Update earthdistance to use cube (Bruno Wolff III) • Update btree_gist Allow inet arrays in /contrib/array (Neil) • GiST fixes (Teodor Sigaev, Neil) • Upgrade /contrib/mysql • Add /contrib/dbsize which shows table sizes without vacuum (Peter) • Add /contrib/intagg, integer

above. LIMIT and OFFSET The clauses LIMIT and OFFSET are PostgreSQL-specific syntax, also used by MySQL. The SQL:2008 standard has introduced the clauses OFFSET ... FETCH {FIRST|NEXT} ... for the same functionality contrib/dbmirror improvements (Steven Singer) • New contrib/xml2 (John Gray, Torchbox) • Updated contrib/mysql • New version of contrib/btree_gist (Teodor) • New contrib/trgm, trigram matching for PostgreSQL pgcrypto (Marko Kreen) • Prevent crash in xml (John Gray, Michael Richards) • Update oracle • Update mysql • Update cube (Bruno Wolff III) • Update earthdistance to use cube (Bruno Wolff III) • Update btree_gist