Postgres Conference China 2016 中国用户大会 PostgreSQL WAL日志解析与应用 王硕 山东瀚高基础软件股份有限公司 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 CONTENTS Part 01 Part 02 Part 03 WAL 日志简介

（云Greenplum）低成本，快速 构筑在线企业数据仓库。 • 应用易开发、适配 支持SQL 2003，部分兼容Oracle语法， 支持PL/SQL 存储过程，OLAP窗口函数，事务及数据库标准隔离级别 完备功能和生态，实现应用快速迁移或适配 • PB级数据秒级响应 MPP水平扩展架构，PB级数据查询秒级响应；向量化计算，及列存储智能索引，领先传统数据库引擎性能 3x； 新一代SQL优化器，实现复杂分析语句免调优 • 支持多模分析 for PostgreSQL 优势 ETL 批量处理 • 复杂 SQL 调优 • 海量数据关联聚合 • 支持标准SQL，OLAP窗口函数，存储过程 • 新一代Cascade框架 分布式SQL优化器，复杂查询免调优 • MPP多节点全并行计算，PB级数据秒级响应 • 基于列存储的高性能大表扫描，及高压缩比 在线数据探索 • 高并发 • 任意维度随机查询 • 实时入库更新 • 高吞吐数据写入及更新 数据按冷热分析，支持在线查询OSS分布式云存 储上的格式化数据 AnalyticDB for PostgreSQL 智能分析服务 DataV QuickBI 云上大数据计算平台 大数据平台 数据分析应用 制式化报表 个性化报表 仪表盘 自助查询 宏观分析 模型分析 MaxCompute 生产系统数据库 关系型数据 库A RDS …… 关系型数据 库B RDS 关系型数据

大量客户需要私有化云服务器部署（云更新） • 需求变更频繁，不适合关系型数据库 2018年PostgreSQL中国技术大会 目前服务器架构 2018年PostgreSQL中国技术大会 MySQL应用史 • 最开始做的站群系统WEB端使用MySQL： • copy简单快速，批量复制，替换修改简单； • 每个网站一个库，单体数据量小，数据类型固定； • 后来做SCRM系统，也首选MySQL： • 需求经常改动，每次改需求先改数据库；

pg_size_pre�y (pg_database_size('postgres')); #查看指定数据 库大小，单位经过优化显示 pg_size_pretty ---------------- 7901 kB #列出所有database的大小，单位经过优化显示 postgres=# select pg_database.datname, pg_size_pre�y (pg_da

perform limit 1(FOUND) • 短连接，连接池 • 频繁创建、删除临时表(10.0 ROADMAP) • explain评估结果集, instead count(*)（分页数） • 分页优化cursor instead order by offset • statement_timeout 防止雪崩 • truncate instead delete all data 2016Postgres中国用户大会

备1 消息队列 对数据的写操作 PostgreSQL 动态节点管理 … 核心库 查询库 对数据库的读操作 备2 备份 归档 运维监控 数据库总体架构 应用 逻辑复制 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 SQL语句 数据 MYSQL PG ORACLE

SQL language interpreter to POSTGRES. Under a new name, Postgres95 was subsequently released to the web to find its own way in the world as an open- source descendant of the original POSTGRES Berkeley code asked questions. READMEs README files are available for most contributed packages. Web Site The PostgreSQL web site5 carries details on the latest release and other information to make your work or answered, to share experiences with other users, and to contact the developers. Consult the PostgreSQL web site for details. Yourself! PostgreSQL is an open-source project. As such, it depends on the user

SQL language interpreter to POSTGRES. Under a new name, Postgres95 was subsequently released to the web to find its own way in the world as an open- source descendant of the original POSTGRES Berkeley code asked questions. READMEs README files are available for most contributed packages. Web Site The PostgreSQL web site5 carries details on the latest release and other information to make your work or answered, to share experiences with other users, and to contact the developers. Consult the PostgreSQL web site for details. Yourself! PostgreSQL is an open-source project. As such, it depends on the user

SQL language interpreter to POSTGRES. Under a new name, Postgres95 was subsequently released to the web to find its own way in the world as an open- source descendant of the original POSTGRES Berkeley code asked questions. READMEs README files are available for most contributed packages. Web Site The PostgreSQL web site5 carries details on the latest release and other information to make your work or answered, to share experiences with other users, and to contact the developers. Consult the PostgreSQL web site for details. Yourself! PostgreSQL is an open-source project. As such, it depends on the user

SQL language interpreter to POSTGRES. Under a new name, Postgres95 was subsequently released to the web to find its own way in the world as an open- source descendant of the original POSTGRES Berkeley code asked questions. READMEs README files are available for most contributed packages. Web Site The PostgreSQL web site5 carries details on the latest release and other information to make your work or answered, to share experiences with other users, and to contact the developers. Consult the PostgreSQL web site for details. Yourself! PostgreSQL is an open-source project. As such, it depends on the user