PostgreSQL Digoal.zhou 6/16/2012 1 - Happy Father's Day 2 2012/6/17 浅析PostgreSQL数据库特点 构建高效,稳定,安全,易扩展的PostgreSQL数据库系统 PostgreSQL容灾 PostgreSQL备份, 还原. PostgreSQL优化 PostgreSQL迁移 PostgreSQL压力测试 PostgreSQL监控 PostgreSQL Caveat PostgreSQL展望 3 PostgreSQL 功能 严格遵循ACID(事务操作原子性(支持savepoint),状态一致性,隔离(read committed, repeatable read, serializable),持久化(fsync xlog)) 在保证ACI的前提下, 降低持久化标准可以提升百倍性能. (异步提交, 异常 DOWN库或DOWN机可能导致wal_buffer里面未flush到xlogfile的信息丢失(最多 wal_writer_delay*3时间的wal信息),不会导致数据库不一致或不可恢复.) two phase commit(例如利用dblink做跨库事务) MVCC, 8个锁级别(DML读写不冲突) 更新优化机制(HOT, 降低索引需要更新的概率, 索引字段没有被更新且被 更新的记录所在的BLOCK有足够的空间存下更新后的记录时符合HOT) 垃圾数据通过VACUUM机制回收(Object's VM file中的块不需要扫描, 触发 full scan除外.vacuum 与DML不冲突) 4 HOT Update Heap Page Index Page LinePoint 1 LinePoint 2 Tuple1 Tuple2 PageHead PageHead ItemPoint t_ctid, HEAP_HOT_UPDATED HEAP_HOT_TUPLE Free Space 5
HOT Update Heap Page Index Page LinePoint 1 LinePoint 2 Tuple1 Tuple2 PageHead PageHead ItemPoint Dead,vacuumed HEAP_HOT_TUPLE Free Space
LP_REDIRECT 6 PostgreSQL 功能 存储(表空间,临时表空间) 字段存储选项(plain, main, external, extended) 支持在线创建,删除索引(不和select,update,delete,insert冲突) 索引维护(支持多个完全一致的索引,使得维护索引更加自由) 索引类型
(btree,hash,gin,gist,sp-gist,btree-gist,btree-gin) 函数语言(C, SQL, plpgsql, plperl, pltcl, plpython, ...) 支持plpgsql 函数debugger 游标(支持[no]scroll, with[out] hold, 支持游标删除,更新(where current of)) 触发器([per row|per statement] INSERT, DELETE, UPDATE[of column,], TRUNCATE) 规则(per statement, do also|instead|nothing) 支持分区表(支持基于触发器,规则的分区表,多层分区支持,支持按照分区约 束优化查询扫描) 7 PostgreSQL 功能 继承(一个表可以继承多个表,也可以被多个表继承,支持多级继承,不允许环 路) 约束(not null, check, unique, primary key, exclude 约束) 数据类型(数字, 货币, 字符, 比特流, 字节流, 时间, 布尔, 枚举, 几何, 网络, 全文检索(中文分词nlpbamboo), UUID, XML, JSON, ARRAY, 自定义复合, range, oid) 内建函数
(字符,时间,数字,聚集,数学,比特,字节流,规则表达式,枚举,几何,网 络,全文检索,序列,JSON,XML,数组,范围,窗口,集合,触发器,系统管理等类 型的函数) 外部数据源表(PostgreSQL,文
件,MySQL,Oracle,Sybase,ODBC,redis,couchDB,...), 支持外部表的JOIN, 支持外部表的统计信息收集, 目前仅支持外部表只读.(外部表在异构平台数 据迁移中非常便利) 支持的几种join方法(hashjoin, nestloop, merge) 8 PostgreSQL 举例 exclusion约束 CREATE TABLE test(id int,geo circle,EXCLUDE USING GIST (geo WITH pg_catalog.&&)); INSERT INTO test
values(1,'<(0,0),2>'::circle); INSERT INTO test values(1,'<(4.1,0),2>'::circle); INSERT INTO test values(1,'<(-1.9,0),2>'::circle); ERROR: conflicting key value violates exclusion constraint \ DETAIL: Key (geo)=(<(-1.9,0),2>) conflicts with existing key (geo)=(<(0,0),2>). 9 c1 c2 c3 Foreign Data Foreign Table(s) NOT NEED Server(s) FDW File Foreign Table(s) User Mapping(s) Server(s) FDW Oracle Foreign Table(s) User Mapping(s) Server(s) FDW MySQL Foreign Table(s) User Mapping(s) Server(s) FDW PostgreSQL Foreign Table(s) User Mapping(s) Server(s) FDW Redis Foreign
Table(s) User Mapping(s) Server(s) FDW(s) Others External Data Source API Conn INFO AUTH INFO TABLE DEFINE 10 PostgreSQL 功能 权限管理(表空间,数据库,schema,表,视图,序列,fdw,域,函数,大对象,复合类 型, 其中表支持列级权限) 权限分配(系统(全局)配置, 数据库级配置, 用户级配置, 会话级, 事务级配置) 在线备份, 还原(基于时间点/事务号/自定义恢复点的恢复)
standby( hot_standby(支持standby在恢复的同时提供查询), warm_standby(恢复时不提供查询), 同步/异步standby(支持事务粒度的), 支持级联的standby,支持暂停和继续恢复, ) 数据块复制(流复制, log shipping ) SQL复制( 触发器(londiste3, slony-I), SQL分发(pgpool-II) ) 基于SQL分发的复制(架设于客户端和数据库之间)需要注意, 必须所有 节点都执行成功事务才能算成功. 否则数据就不一致了. 基于触发器的复制(架设于主库和slave库之间)则不需要考虑跨库事务 的问题. 11 PostgreSQL 功能 Common Table Expression(WITH语法,可以构造逻辑复杂的查询,如递归, 树形查询,支持WITH中使用DELETE,update,select等. ) SQL支持
insert,update,delete returning语法, 适用后续需要对先前记录进 行更改的操作, 如二次确认. 计划器(explain analyze verbose costs buffers timing format [text xml json
相关推荐：
loading