前言

PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS）。

PostgreSQL最初设想于1986年，当时被叫做Berkley Postgres Project。该项目一直到1994年都处于演进和修改中，直到开发人员Andrew Yu和Jolly Chen在Postgres中添加了一个SQL（Structured Query Language，结构化查询语言）翻译程序，该版本叫做Postgres95，在开放源代码社区发放。

1996年，再次对Postgres95做了较大的改动，并将其作为PostgresSQL6.0版发布。该版本的Postgres提高了后端的速度，包括增强型SQL92标准以及重要的后端特性（包括子选择、默认值、约束和触发器）。

PostgreSQL是一个非常健壮的软件包，有很多在大型商业RDBMS中所具有的特性，包括事务、子选择、触发器、视图、外键引用完整性和复杂锁定功能。另一方面，PostgreSQL也缺少商业数据库中某些可用的特性，如用户定义的类型、继承性和规则。从用户的角度来讲，PostgreSQL惟一不具备的主要特性就是外部连接。

PostgreSQL提供了两种可选模式。一种模式保证如果操作系统或硬件崩溃，则数据将保存到磁盘中，这种模式通常比大多数商业数据库要慢，这是因为它使用了刷新（或同步）方法；另一种模式与第一种不同，它不提供数据保证，但它通常比商业数据库运行得快。

PostgreSQL 的进击

PostgreSQL的Slogan是“世界上最先进的开源关系型数据库”！！！

这句口号不知道从何时起掀起来一股Pg（PostgreSQL 简称“Pg”）热潮，很明显一看就是在怼MySQL那个“世界上最流行的开源关系型数据库”的口号，有碰瓷之嫌。

要我说最能生动体现PG特色的描述应该是：一专多长的全栈数据库。

Pg的特性：

• 稳定性

PostgreSQL的代码质量是被很多人认可的，经常会有人笑称PG的开发者都是处女座。基 本上，PG的一个大版本发布，经过三两个小版本就可以上生产，这是值得为人称道的一个地方。从PostgreSQL漂亮的commit log就可见一斑，而得益于PostgreSQL的多进程架构，一个连接的异常并不影响主进程和其他连接，从而带来不错的稳定性。

• 高性能

在诸多的应用案例和性能测试中，有些性能上的数据，TPCC的性能测试显示PostgreSQL的性能与商业数据库基本在同一个层面上。

• 丰富性

PostgreSQL的丰富性是最值得诉说的地方。

查询类型丰富：且不说HASH\Merge\NestLoop JOIN，还有递归、树形（connect by）、窗口、rollup\cube\grouping sets、物化视图、SQL标准等，还有各种全文检索、规则表达式、模糊查询、相似度等。在这些之外，最重要的是PostgreSQL强大的基于成本的优化器，结合并行执行（并行扫瞄、并行JOIN等）和多种成本因子，带来各种各样丰富灵活高效的查询支持。

数据类型丰富：如高精度numeric, 浮点, 自增序列，货币，字节流，时间，日期，时间戳，布尔， 枚举，平面几何，立体几何，多维几何，地球，PostGIS，网络，比特流，全 文检索，UUID，XML，JSON，数组，复合类型，域类型，范围，树类型，化 学类型，基因序列，FDW, 大对象, 图像等。或者，如果以上不够满足，你可以自定义自己的类型（create type），并且可以针对这些类型进行**运算符重载**，比如实现IP类型的加减乘除（其操作定义依赖于具体实现，意思是：你想让IP的加法是什么样子就是什么样子）。

功能丰富：PostgreSQL有一个无与伦比的特性——插件。其利用内核代码中的Hook，可以让你在不修改数据库内核代码的情况下，自主添加任意功能，如PostGIS、JSON、基因等，都是在插件中做了很多的自定义而又不影响任何内核代码从而满足丰富多样的需求。而PostgreSQL的插件，不计其数。FDW机制更让你可以在同一个PostgreSQL中像操作本地表一样访问其他数据源，如Hadoop、MySQL、Oracle、Mongo等，且不会占用PG的过多资源。

至于其他的，举个简单的例子，PostgreSQL的DDL（如加减字段）是可以在事务中完成的 [PS: PostgreSQL是Catalog-Driven的，DDL的修改基本可以理解为一条记录的修改]。这一点，相信做业务的同学会有体会。

PostgreSQL最近几年技术发展非常的迅猛，覆盖OLTP,OLAP,NoSQL,搜索,时空,流,图,图像等应用场景，在往全栈数据库的方向发展。帮助企业解决了数据孤岛、数据平台多，同步一致性、延迟，软硬件成本增加等业务痛点，在互联网、金融、物联网、传统企业等领域得到了广泛的应用。PostgreSQL的应用场景丰富，不亚于商用数据库Oracle，常被业界称为“开源界的Oracle”。

成熟的应用可能会用到许许多多的数据组件（功能）：缓存，OLTP，OLAP/批处理/数据仓库，流处理/消息队列，搜索索引，NoSQL/文档数据库，地理数据库，空间数据库，时序数据库，图数据库。传统的架构选型呢，可能会组合使用多种组件，典型的如：Redis + MySQL + Greenplum/Hadoop + Kafuka/Flink + ElasticSearch，一套组合拳基本能应付大多数需求了。不过比较令人头大的就是异构系统集成了：大量的代码都是重复繁琐的搬砖代码，干着把数据从A组件搬运到B组件的事情。

在这里，MySQL就只能扮演OLTP关系型数据库的角色，但如果是PostgreSQL，就可以身兼多职，One handle them all，比如：

• OLTP：事务处理是PostgreSQL的本行
• OLAP：citus分布式插件，ANSI SQL兼容，窗口函数，CTE，CUBE等高级分析功能，任意语言写UDF
• 流处理：PipelineDB扩展，Notify-Listen，物化视图，规则系统，灵活的存储过程与函数编写
• 时序数据：timescaledb时序数据库插件，分区表，BRIN索引
• 空间数据：PostGIS扩展（杀手锏），内建的几何类型支持，GiST索引。
• 搜索索引：全文搜索索引足以应对简单场景；丰富的索引类型，支持函数索引，条件索引
• NoSQL：JSON，JSONB，XML，HStore原生支持，至NoSQL数据库的外部数据包装器
• 数据仓库：能平滑迁移至同属Pg生态的GreenPlum，DeepGreen，HAWK等，使用FDW进行ETL
• 图数据：递归查询
• 缓存：物化视图

在Pg诸多的互联网应用案例的架构中，很多系统都是围绕PostgreSQL设计的。几百万日活，几百万全局DB-TPS，几百TB数据的规模下，数据组件只用了PostgrSQL。独立的数仓，消息队列和缓存都是后来才引入的。而且这只是验证过的规模量级，进一步压榨PG是完全可行的。

因此，在一个很可观的规模内，PostgreSQL都可以扮演多面手的角色，一个组件当多种组件使。虽然在某些领域它可能比不上专用组件，至少都做的都还不赖。而单一数据组件选型可以极大地削减项目额外复杂度，这意味着能节省很多成本。它让十个人才能搞定的事，变成一个人就能搞定的事。

对绝大多数应用而言，终其生命周期都不会有超出Pg能力范围之外的数据量级。为了不需要的规模而设计是白费功夫，实际上这属于过早优化的一种形式。 此外，只有当没有单个软件能满足你的所有需求时，才会存在分拆与集成的利弊权衡。集成多种异构技术是相当棘手的工作，如果真有那么一样技术可以满足你所有的需求，那么使用该技术就是最佳选择，而不是试图用多个组件来重新实现它。

当业务规模增长到一定量级时，可能不得不使用基于微服务/总线的架构，将数据库的功能分拆为多个组件。但PostgreSQL的存在极大地推后了这个权衡到来的阈值，而且分拆之后依然能继续发挥重要作用。

综上所述，PostgreSQL除了可以让你做到几乎所有其他主流关系型数据库能做的事情外，还可以做到很多别的数据库做不到的事情。

如果非要一个简单直接、便于理解的定义，那么你可以理解为一个开源可自由扩充的Oracle。

Pg应用领域

数据库对于一家企业来说，相比其他基础组件占据比较核心的位置。

有很多企业由于最初数据库选型问题，导致一错再错，甚至还有为此付出沉痛代价的。

数据库的选型一定要慎重，但是这么多数据库，该如何选择呢？

何时使用PGSQL？

数据完整性:当可靠性和数据完整性是绝对必要而无需理由时，PostgreSQL是更好的选择。

复杂的自定义过程:如果你需要你的数据库执行自定义过程，可扩展的PostgreSQL是更好的选择。

整合:在将来，如果可能要把整个数据库系统迁移到另一个适当的解决方案（例如Oracle）中，PostgreSQL对于这种切换将是最兼容和易于操作的。

复杂的设计:相比其他的开源和免费的 RDBMS（关系数据库管理系统）实现来说，对于复杂的数据库设计，PostgreSQL提供了大部分的功能和可能性，同时并没放弃其他有价值的地方。

何时不用PGSQL？

速度:如果你需要的只是快速的读取操作, PostgreSQL 不是为此而准备的工具。

简化体制:除非你需要绝对的数据完整性，原子性，一致性，隔离性，耐久性，或复杂的设计，PostgreSQL 对简化体制来说是杀手。

复制:除非你愿意花不少时间，精力和资源，否则对于那些缺乏数据库和系统管理经验的人来说，实现与MySQL的（主从）复制可能不容易。

综合Pg的特性和优势，本文参考互联网资源和一些材料整理了针对Pg的一些应用场景。

1.任意字段组合查询 - ERP、电商、网站、手机APP等业务场景

在一些前端的人机交互页面中，经常会有很多选择框，让用户进行选择，这些选择框可能对应的是数据库表中的不同字段。

这种画面经常出现在ERP，电商，网站，手机APP等场景中。

对于开发人员来说是一件很头疼的事情，因为不知道该对哪些字段创建索引，或者干脆对所有字段都建立索引，给数据库带来较大的性能和维护的问题。

PostgreSQL中有两个技术(gin, bloom索引)，可以完美的解决这类业务场景的问题。

2.高并发、高效率范围查询 - 金融、物联网、智能DNS等业务场景

有些场景，经常要对值进行范围的比对。比如物联网，对传感器上传的值，进行范围比对。智能DNS，需要对来源IP进行判断，并找出其落在哪个IP地址段内。

金融行业，经常要设置一些指标范围，时刻判断指数是否落在某个区间，当一些指数落在某个范围区间时，触发下一步的操作（比如买入或卖出）。

传统的两个字段+复合B树的索引，效率低下，通常8核的机器只能达到3000多的QPS。

PostgreSQL通过(range类型和gist,sp-gist索引)，可以将效率提升20多备，8核的机器可以达到8万的QPS。

3.网格化、矢量化地图 - 地理类应用、LBS社交、导航等业务场景

在GIS系统中，将地图划分成网格。通过编码来简化地理位置的判断（比如相交，包含，距离计算等），但是请注意使用网格带来的问题，比如精度的问题，网格的大小决定了精度，又比如相对坐标的问题，可能无法描述清楚边界的归属。

PostgreSQL可以提供给你更好的选择，矢量化的运算。

在PostGIS中虽然也支持网格对象的描述方式，但是并不是使用这种方法来进行几何运算（比如相交，包含，距离计算等），所以不存在类似的精度问题，个人建议没有强需求的话，不必做这样的网格转换。

如果是多种精度地图的切换（比如多个图层，每个图层代表一种地图精度），建议使用辐射的方式逐渐展开更精细的图层，以点为中心，逐渐辐射。

4.异步消息 - 物联网、WEB、金融等业务场景

电波表是一个非常典型的广播应用，类似的还有组播（注意不是主播哦），类似的应用也很多，比如广播电视，电台等。

在数据库中，其实也有类似的应用，比如利用PostgreSQL数据库的异步消息机制，往数据库的消息通道发送数据，应用程序可以监听对应的消息通道，获取异步消息数据。

通过异步消息在数据库中实现了一对多的广播效果。

在物联网中，也可以有类似的应用，例如结合PostgreSQL的流式计算，当传感器上报的数据达到触发事件的条件时，往异步消息通道发送一则消息，应用程序实时的接收异步消息，发现异常。

这样做的好处很多，即节省了空间（结合流式处理，完全可以轻量化部署），又能提高传播的效率（一对多的传播），程序设计也可以简单化。

在金融行业，也可以有类似的实现，比如对数据的实时流式监测，数据流经一系列的规则，触发异步消息。

5.流式实时数据处理 - 物联网、金融等业务场景

在物联网、金融行业中，有大量的数据产生，同时需要实时的对数据进行处理。

pipelinedb是基于PostgreSQL的一个流式计算数据库，纯C代码，效率极高(32c机器,单机日处理流水达到了250.56亿条)。同时它具备了PostgreSQL强大的功能基础，正在掀起一场流计算数据库制霸的腥风血雨。

在物联网(IoT)有非常广泛的应用场景，越来越多的用户开始从其他的流计算平台迁移到pipelineDB。

pipelinedb的用法非常简单，首先定义stream(流)，然后基于stream定义对应的transform(事件触发模块)，以及Continuous Views(实时统计模块)

数据往流里面插入，transform和Continuous Views就在后面实时的对流里的数据进行处理，对开发人员来说很友好，很高效。

值得庆祝的还有，所有的接口都是SQL操作，非常的方便，大大降低了开发难度。

6.GIS、图像近似度运算 - 互联网、AR红包、虚拟现实与GIS结合、广告营销等业务场景

AR红包是GIS与图像、社交、广告等业务碰撞产生的一个全新业务场景。需要做广告投放的公司，可以对着广告牌，或者店铺中的某个商品拍照，然后藏AR红包。要找红包的人，需要找到这家店，并且也对准藏红包的物体拍摄，比较藏红包和找红包的两张图片，就可以实现抢红包的流程。

可以想象的空间很多。

使用的核心技术是GIS（地理位置）与图像近似度比较。

PostgreSQL对于这两项技术都可以很好的支持。

7.相似内容搜索、去重 - 互联网、数据公司、搜索引擎等业务场景

在搜索引擎、数据公司、互联网中都会有网络爬虫的产品，或者有人机交互的产品。

有人的地方就有江湖，盗文、盗图的现象屡见不鲜，而更惨的是，盗图和盗文还会加一些水印。也就是说，你在判断盗图、盗文的时候，不能光看完全一致，可能要看的是相似度。这给内容去重带来了很大的麻烦，不过还好，PostgreSQL数据库整合了相似度去重的算法和索引接口，可以方便的处理相似数据。

比如相似的数组、相似的文本、相似的分词、相似的图像的搜索和去重等等。

8.任意字段模糊查询 - 互联网、前端页面、搜索引擎 等业务场景

在一些应用程序中，可能需要对表的所有字段进行检索，有些字段可能需要精准查询，有些字段可能需要模糊查询或全文检索。比如一些前端页面下拉框的勾选和选择。这种需求对于应用开发人员来说，会很蛋疼，因为写SQL很麻烦。

PostgreSQL中可以很好的解决这个问题，适用于任意字符（包括英文、中文、等等）。

9.在线处理、离线分析、在线分析混合需求 - 互联网、传统企业、金融等业务场景

随着数据类型的爆炸式增长，分析型的需求越来越难以满足，主要体现在数据的处理速度方面，而常见的hadoop生态中的处理方式需要消耗大量的开发人员，同时并不能很好的支持品种繁多的数据类型，即使GIS可能也无法很好的支持，更别说诸如人像、X光片、声波、指纹、DNA、化学分子、图谱数据、GIS、三维、多维等等。

且看ApsaraDB产品线的PostgreSQL与HybridDB如何来一招左右互搏，左手在线事务处理，右手数据分析挖掘，解决企业痛处。

而对于分析场景，使用MPP产品HybridDB(基于GPDB)，则可以很好的解决PB级以上的AP需求。

10.用户群体搜索、根据标签圈人 - 电商、广告投放 等业务场景

比如一家店铺，如何找到它的目标消费群体？

要回答这个问题，首先我们需要收集一些数据，比如：

1. 这家店铺以及其他的同类店铺的浏览、购买群体。

2. 得到这些用户群体后，筛选出有同类消费欲望、或者具备相同属性的群体。

PostgreSQL, HybridDB解决了推荐系统的三个核心问题。

精准，属于数据挖掘系统的事情，使用PostgreSQL, Greenplum 的 MADlib机器学习库可以实现。

实时，实时的更新标签，在数据库中进行流式处理，相比外部流处理的方案，节约资源，减少开发成本，提高开发效率，提高时效性。

高效，使用PostgreSQL以及数组的GIN索引功能，实现在万亿USER_TAGS的情况下的毫秒级别的圈人功能。

11.位置信息处理、点面判断、按距离搜索、化学数据处理 - 危化品监管等业务场景

危化品的种类繁多。包括如常见的易爆、易燃、放射、腐蚀、剧毒、等等。

由于危化品的危害极大，所以监管显得尤为重要，

1. 生产环节

将各个原来人工监控的环节数字化，使用 传感器、流计算、规则（可以设置为动态的规则） 代替人的监管和经验。

2. 销售环节

利用社会关系分析，在销售环节挖掘不法分子，挖掘骗贷、骗保的虚假交易。利用地理位置跟踪，掌控整个交易的货物运输过程。

3. 仓储环节

仓储环节依旧使用传感器、流计算、应急机制对仓管的产品进行实时的监管，而对于危化品本身，我们已经不能使用普通的数据类型来存储，很幸运的是在PostgreSQL的生态圈中，有专门支持化学行业的RDKit支持，支持存储化合物类型，以及基于化合物类型的数据处理

（包括化学反应，分解等等）。

4. 运输环节

在危化品的运输环节，使用传感器对货车、集装箱内的危化品的指标进行实时的监控，使用流式数据库pipelineDB流式的处理传感器实时上报的数据；使用PostgreSQL+PostGIS+pgrouting 对于货车的形式路径进行管理，绕开禁行路段、拥堵路段。

当出现事故时，使用PostgreSQL的GIS索引，快速的找出附近的应急救助资源（如交警、消防中队、医院、120）。

同时对危化品的货物存储，使用化学物类型存储，可以对这些类型进行更多的约束和模拟的合成，例如可以发现化学反应，防止出现类似天津爆炸事件。

5. 消耗环节

增加剩余量的监控，在闭环中起到很好的作用，达到供需平衡，避免供不应求，或者供过于求的事情发生。

6. 动态指挥中心

在给生产、仓库、物流配送、消耗环节添加了终端、传感器后，就建立了一个全面的危化品监管数据平台。 构建实时的监管全图。

7. 缉毒、发现不法分子等

通过社会关系学分析，结合RDKit插件，在数据库中存储了人的信息，存储了人与化学物的关系（比如购买过），然后，根据社会关系学分析，将一堆的化合物（原材料）结合起来，看看会不会发生反应，生成毒品或危化品。

12.图式数据搜索 - 金融风控、公安刑侦、社会关系、人脉分析 等业务场景

PostgreSQL是一个功能全面的数据库，其中就有一些图数据库产品的后台是使用PostgreSQL的，例如OpenCog， Cayley等。

除了这些图数据库产品，PostgreSQL本身在关系查询，关系管理方面也非常的成熟，十亿量级的关系网数据，3层关系运算仅需毫秒。

还可以用于运算人与人之间的最短关系，穷举关系等。

主要用到的技术plpgsql服务端编程、异步消息、数组、游标等。

13.大量数据的求差集、最新数据搜索, 最新日志数据与全量数据的差异比对, 递归收敛扫描 - 物联网、数据同步、数据清洗、数据合并等业务场景

有一个这样的场景，一张小表A，里面存储了一些ID，大约几百个到万个。

（比如说巡逻车辆ID，环卫车辆的ID，公交车，微公交的ID）。

另外有一张日志表B，每条记录中的ID是来自前面那张小表的，但不是每个ID都出现在这张日志表中，比如说一天可能只有几十个ID会出现在这个日志表的当天的数据中。

（比如车辆的行车轨迹数据，每秒上报轨迹，数据量就非常庞大，但是每天出勤的车辆有限）。

那么我怎么快速的找出今天没有出现的ID呢。

（哪些巡逻车辆没有出现在这个片区，是不是偷懒了？哪些环卫车辆没有出行，哪些公交或微公交没有出行）？

select id from A where id not in (select id from B where time between ? and ?);

select a.id from a left join b on (a.id=b.aid) where b.* is null;

这个QUERY会很慢，通常需要几百秒到几十秒，有什么优化方法呢。

通过PostgreSQL的递归查询，可以高效的解决这个问题(在几亿记录中筛选出与几万记录的逻辑差集)。

优化后只需要10毫秒左右。

同样的方法，还可以用于数据清洗与合并的场景，比如在物联网的环境中，每个传感器，每个小时会上报若干条数据（有新增的，有更新的，有删除的指标等），对于同一个KEY，后台的应用程序只关心最后一条记录。

使用PostgreSQL的递归收敛，每秒可以清洗或合并千万量级的数据。除了物联网，同样适用于数据库之间的数据逻辑同步。

14.数据一致性分享、数据泵 - 跨业务平台实时分享数据等业务场景

在IoT的场景中，有流式分析的需求，也有存储历史数据的需求，同时还有数据挖掘的需求，搜索引擎可能也需要同一份数据，还有一些业务可能也要用到同一份数据。

但是如果把数据统统放到一个地方，这么多的业务，它们有的要求实时处理，有的要求批量处理，有的可能需要实时的更新数据，有的可能要对大数据进行分析。

显然一个产品可能无法满足这么多的需求。

10万级别左右的机器，PostgreSQL 的数据吞吐量可以达到100万条/s以上，同时数据库本身具备了严格的可靠性和一致性保证。

PostgreSQL为分享数据提供了插槽的概念，每个插槽对应一个目标端，支持断点续传，支持多个目标端。用于流式的分享数据是非常好的选择。

15.高并发更新少量记录 - 电商、票务系统 等业务场景

秒杀在商品交易中是一个永恒的话题，从双十一，到一票难求，比的仅仅是手快吗？

其实对于交易平台来说，面对的不仅仅是人肉，还有很多脚本，外挂自动化的抢购系统，压力可想而知。

秒杀的优化手段很多，就拿数据库来说，有用排队机制的，有用异步消息的，有用交易合并的。

PostgreSQL提供了一种ad lock，可以让用户尽情的释放激情，以一台32核64线程的机器为例，每秒可以获取、探测约130万次的ad lock。

试想一下，对单条记录的秒杀操作，达到了单机100万/s的处理能力后，秒杀算什么？100台机器就能处理1亿/s的秒杀请求。

16.动态规划 - 物流配送、打车软件、导航软件、出行软件、高速、高铁 等业务场景

以物流行业为例，PostgreSQL与Greenplum为物流行业应用提供了包括机器学习、路径规划、地理位置信息存储和处理等基础服务。

17.流式同步多副本、极致数据可靠性 - 金融、传统企业、互联网 等业务场景

PostgreSQL基于同步流复制的 任意副本 解决方案，在解决0丢失，高可用以及容灾的问题的同时，还可以提供只读的功能。相比传统的存储解决方案，优势更加明显。

18.会话级资源隔离 - 多租户、云、混合业务资源控制等业务场景

在很多场景中，用户希望可以控制每个连接（会话）的资源使用情况，例如CPU\IOPS\MEMORY等。

PostgreSQL是进程结构，可以通过cgroup很好的实现这个需求，不需要对数据库内核进行改造。另一方面，基于PostgreSQL的产品GPDB，则是在数据库的内核层面实施的控制。

19.数据预测、挖掘 - 金融数据分析、机器学习 等业务场景

PostgreSQL、以及HybridDB(基于GPDB)，等PostgreSQL相关的数据库，都支持MADlib机器学习库，这个库支持机器学习领域常见的算法（例如聚类、线性回归、贝叶斯、文本处理等等）

其中在数据领域用得较多的数据预测，可以使用MADLib的多元回归库，进行数据的预测。

结合plR语言 或者R + pivotalR 、 python + pythonR插件，可以自动将R\python语言的命令转换为MADlib库函数，对数据进行分析。

非常适合使用R或者python对数据进行分析的数据科学家使用。

其特点是高效（数据与运算一体，可以使用LLVM\向量计算等技术优化，同时不需要传播数据，节约了传播的开销）、易用（支持常见的SQL、r, python等编程）。

小结

虽然，Pg并不如它的Slogan所宣扬的“Pg是世界上最先进的开源关系型数据库”，但毫无疑问，Pg在如今的人工智能和大数据时代拥有非常广泛的应用场景和发展前景。

从上图可看出，Pg市场占有率在Oracle、MySQL、MSSQL之后，排第四位，相信随着人工智能和大数据的深入推广，Pg将会发挥出更加出色的作用。

本文部分内容来自于网络，仅供大家参考。