PostGreSQL学习笔记

• PostgreSQL被誉为世界上先进的开源数据库，它拥有很长的历史，初是1985年在加利福尼亚大学伯克利分校开发的。
• PostgreSQL是完全由社区驱动的开源项目，由全世界超过1000名贡献者所维护，提供了单个完整功能的版本，而不像MySQL那样提供了多个不同的社区版、商业版与企业版。
• PostgreSQL具有极高的可靠性，支持高事务、数据一致性与完整性。PostgreSQL以实现高可用、负载均衡、复制、针对失败恢复创建热备份以及快照与恢复的能力。
• 适合在线交易系统

特点：

适合严格追求数据安全保障的交易类业务
基于物理复制，严格保证主备数据一致
复制延迟低，通常毫秒级复制延迟
非常情况下故障恢复快，归档日志回放速度70MB/s以上
严格遵循SQL标准(相对于MySQL)，有效防止非法数据入库以及返回错误的结果
商业数据库迁移

• 实时数据分析及OLTP/OLAP混合型业务
• 支持分析函数，CTE等SQL特性
• 全面的统计信息,CBO+GEN的优化器算法，3种JOIN方式, 有力支持高效的复杂查询
• 丰富的索引类型(btree,hash,gin,gist,sp-gist,brin)可适配各类业务场景
• 支持全文检索
• 基于citus的MPP集群部署可水平扩容
• 基于PostgreSQL的PostgGIS是目前的开源GIS软件，有大量的应用案例

与MySQL对比

MySQL 处理树状回复的设计会很复杂, 而且需要写很多代码, 而 Pg 可以高效处理树结构:
Scaling Threaded Comments on Django at Disqus
http://www.slideshare.net/quipo/trees-in-the-database-advanced-data-structures

它可以高效处理图结构, 轻松实现 "朋友的朋友的朋友" 这种功能:
http://www.slideshare.net/quipo/rdbms-in-the-social-networks-age

它可以把 70 种外部数据源 (包括 Mysql, Oracle, CSV, hadoop ...) 当成自己数据库中的表来查询:
Foreign data wrappers

其他优势

自带全文搜索功能 (不用费劲再装一个 elasticsearch 咯):
Full text search in milliseconds with PostgreSQL 不过一些语言相关的支持还不太完善, 有个 bamboo 插件用调教过的 mecab 做中文分词, 如果要求比较高, 还是自己分了词再存到 tsvector 比较好.
它支持 trigram 索引.
trigram 索引可以帮助改进全文搜索的结果: PostgreSQL: Documentation: 9.3: pg_trgm
trigram 还可以实现高效的正则搜索 (原理参考 https://swtch.com/~rsc/regexp/regexp4.html )

Converting MySQL to PostgreSQL

与NoSQL数据库相比：

伴随着对广泛使用的JSON数据交换格式的支持，PostgreSQL开源数据库新版本的出现标志着其将目标指向日益发展的非关系型数据存储NoSQL市场，特别是人气高涨的MongoDB方案-NoSQL数据库解决方案
PostgreSQL 9.4拥有JSON(即JavaScript Simple Object Notation)的原生支持能力。作为一种正迅速成为不同系统之间实现数据共享诉求的格式，JSON通常会使用REST(即Representational State Transfer)协议
PostgreSQL所采用的JSON结构化保存格式被称为JSONB，它能够省去将内容提交至数据库之前先对文件进行重组这一步骤，此设计让PostgreSQL获得了可与MongoDB相比肩的文件提取能力
PostgreSQL还提供一套完整的索引服务、函数以及运算符，旨在帮助用户对JSON数据进行操作
提供一套新型API(即应用程序编程接口)，用于对来自副本流的数据进行解码，从而为第三方软件供应商铺平了敏感数据副本系统的构建之路，其它新功能还包括引入动态背景、数组操作与表函数以及一般性能改进等等

常见场景

地理信息处理扩展 (GIS 扩展不仅限于真实世界, 游戏里的地形什么的也可以), 可以用 Pg 搭寻路服务器和地图服务器 =>PostGIS Spatial and Geographic Objects for PostgreSQL

Citus是什么 ：用于实时分析和在线交易

是PostgreSQL的扩展，可以同PG一同安装，之后通过SQL命令加入到数据库中。

#创建Citus扩展：
CREATE EXTENSION citus;