Name: PostgreSQL实战教程 (it-ebooks) (Z-Library)
Author: Unknown Author

Page 1

(This page has no text content)

Page 2

2 3PostgreSQL实战教程 国产化浪潮之上的PostgreSQL 04 目录 认识PostgreSQL中与众不同的索引 11 PG Ganos时空场景快速开发实践23 高维向量检索技术在PG中的设计与实践36 PostgreSQL监控实战基于Pigsty解决实际监控问题 49 PostgreSQL复制原理及高可用集群63 性能优化和体系化运维 75 微信关注公众号：阿里云数据库 第一时间，获取更多技术干货 阿里云开发者“藏经阁” 海量免费电子书下载

Page 3

4 5PostgreSQL实战教程 国产化浪潮之上的PostgreSQL 为什么数据库是过去式？因为过去正在变革。 关系型数据库始于六七十年代，随着关系数据库理论的成立，诞生了很多伟大的公司，如：甲骨文、微软、IBM DB2、还 有消失的Sybase。从七十年代至今，这些公司基本都处于垄断地位，即使在二十世纪初MySQL和PostgreSQL数据库逐 渐盛行，但仍然无法影响这些历史悠久的公司的市场地位。 这些历史悠久的数据库公司为什么如此坚不可摧，存在以下几个原因： 1. 拥有最优秀的产品 提到关系型数据库时，自然而然联系到业界公认的龙头老大甲骨文。甲骨文的产品无论从性能或稳定性等方面，都是最顶 尖的。 2. 客户需求坚不可摧 由于这些公司历史悠久的品牌影响力与市场认可度，他们的产品成为了许多公司与企业的第一选择。例如当在使用甲骨文 产品的过程中遇到问题时，由于对甲骨文的盲目推崇，公司与企业也不会对问题过于苛责，因为在他们心中这已经是世界 上最好的产品，这也是市场不好的一个地方。 3. 强大的销售体系 这些历史悠久的公司非常善于使用品牌营销进行产品推广与销售，每次的新品发布会总是能用出色的方式吸引众多业内人 士的眼球，即使推出的新品是其他公司已经有类似产品，但这些龙头公司总能用深厚的品牌影响力与悠久的历史为自己背 书，使得众多用户与企业趋之若鹜，痛快买单。 （一）国际市场格局已经发生巨大变化 国际市场格局的变化包括：云数据库、价格、客户需求。 1.云数据库 近年来云数据库对整个市场进行重新洗牌，由于云数据库能够节省成本，许多企业包括政府部门都把数据库迁到云上。 2.价格 从七八十年代至今，像甲骨文等公司的产品价格都十分高昂，用户在以前没有选择的余地，但如今越来越多开源和低廉的 解决方案出现在市场，使得市场价格也在悄然改变。 3.客户需求 过去的客户购买产品的主要是奔着License，使得企业合法化或上市。如今客户虽然仍存在这样的需求，但技术服务的需 求占比越来越重。 结合2019年全球所有数据库的销售额来看，整个市场销售额呈下降趋势，甲骨文在全球市场销售额下降19%+，幅度十分 大。国际数据库市场的巨大变化，为中国的数据库工程师和企业的发展带来很好的机遇与挑战，可以说是最好的时代。 （二）国内市场迎来新机遇 这里结合华泰证券一份公开的数据库研究报告来进行阐述。 报告中显示，目前国内的数据库厂商处于IT产业链的中游，上承软硬件提供商与国外数据库厂商源代码授权，下接应用系 统集成商与最终用户，包含电信、金融、能源和军工等。 一、数据库过去式 二、数据库最好的时代 作者 | 赵振平 图表：IT产业链 资料来源：IDC，华泰证券研究所 应用开发商 集成商 软硬件提供商 电信 金融 能源 军工 公安 财政 ... 最终用户 数据库厂商 国外数据库厂商源代码授权 应用系统集成商 图表：数据库产业链 上游 开发商 基础软件厂商 数据库厂商 操作系统厂商 中间件厂商 网络和硬件厂商 CPU厂商 服务器厂商 网络设备厂商 下游 中游 上游 IT产业链 下游

Page 4

6 7PostgreSQL实战教程 目前国内数据库厂商主要分为三个方向，分别是传统数据库、云数据库和开源数据库，各个方向都有领头羊厂商在领跑数 据库发展。 根据2018年国内数据库市场份额可看出，国内的市场仍由国外市场垄断，国产数据库厂商所占市场份额相加仍与国外公司 相差甚远。 国产数据库 武汉达梦 南大通用 人大金仓 神舟通用 蚂蚁OceanBase 阿里云 腾讯云 百度云 华为Gauss DB 瀚高科技 优炫软件 巨杉数据库 星环科技 云数据库 开源数据库 传统数据库 名次 厂商 市场份额（按销售额） 1 Oracle 2 IBM 48.5% 10.1% 3 Microsoft 9.6% 4 SAP 7.2% 5 Teradata 6 南大通用 7 达梦 2018年国内数据库市场份额 3.9% 2.5% 2.3% 8 神舟通用 9 人大金仓 10 其他 1.5% 1.1% 13.3% 报告指出，随着国内数据库市场的不断发展，2021年市场规模预计达到158.8亿元，并预测在未来的三年继续快速上升。 综上所述，随着国产数据库厂商的不断突破与国内市场规模不断上涨，国内将迎来新的机遇与挑战。 销售额（亿元） 图表：中国数据库管理系统市场规模及预测 0 20 40 60 80 100 120 140 160 180 92.9 2016 105.3 2017 117.6 2018 130.3 2019 144.0 2020 158.8 2021 （一）技术底座 基于PostgreSQL的技术底座，用户可以开发很多东西。 例如开发新的数据库，将PostgreSQL做成产品的一部分，如ERP的一部分或电信信息系统的一部分等。目前已经有许多 成功案例，并且成为国内外很有影响力的公司，比如基于PostgreSQL的EDB，还有已经在美国上市的Greenplum，基 于PostgreSQL也可以开发其他数据库。 这里需要注意的是，PostgreSQL某些地方的功能无法满足用户的需求，用户可利用PostgreSQL特有的插件式机制，在 PostgreSQL开发自己的插件。 三、PostgreSQL是你的新底座 PostgreSQL EDB Greenplum 其他数据库

Page 5

8 9PostgreSQL实战教程 （二）商业底座 在往年，市场上20岁+的数据库工程师的简历有许多，35岁及以上的数据库工程师简历很少出现。而在近年，越来越多的 35岁+的数据库工程师出现在求职市场，DBA中年危机开始显现。 （一）数据库品牌 虽然PostgreSQL在市场快速发展，但有不少Oracle工程师、DB2工程师、开发人员对PostgreSQL呈观望甚至抵触立 场，原因是觉得学习PostgreSQL的门槛高，学习难度大。 如果将数据库品牌比喻成汽车品牌，Oracle相当于宝马，DB2相当于奔驰，而新兴的PostgreSQL相当于长城汽车。 如果你能够熟练驾驶宝马与奔驰，那么从技术角度来说，通过简单的熟悉与练习，你也能够快速上手长城汽车，因为大部分 的原理都是一样的。因此，对于Oracle工程师、DB2工程师、开发人员来说，PostgreSQL并没有想象中的生涩难懂，相 反，熟练掌握Oracle、DB2的工程师掌握PostgreSQL后，能在当下PostgreSQL盛行的数据库市场中取得极强的竞争力。 （二）类似的体系结构 如上图所示，Oracle与PostgreSQL的架构存在许多相似的地方： ·内存结构类似 对一个数据库工程师来说，20岁是职业生涯的黄金发展时期，强大的学习能力使你能在短时间内成长为公司的骨干。 当到达30岁时，大多数人基本到达数据库职业生涯的巅峰。 当35岁的时候，许多工程师感受到人生的彷徨，在DBA生涯的分岔路上犹豫不决。此时有一部分人选择继续深耕技术，有 一部分转向非技术工作。 当到达45岁时，现实的残酷表明，对比20岁+的年轻人，大多数45+岁的工程师在技术市场竞争力非常小，中年危机显现。 当一个数据库工程师到了30岁以后，技术的硬实力已经无法满足职业生涯发展的需求，更多的软实力才能让自己具备更多 的市场竞争力。这个软实力包括许多部分，例如跟随行业变革前进、优秀团队、沟通能力、团队沟通能力与协同能力等。 四、35岁DBA中年危机 五、PostgreSQL学习方法论 PostgreSQL强大的可塑性与广泛的使用市场使之成为商业底座。 基于PostgreSQL创立的新公司遍布世界各地，这些公司基于PostgreSQL可以做许多新产品，例如EDB。基于 PostgreSQL还可以满足许多需求，例如相关的技术服务、技术支撑、开发服务等。 综上所述，PostgreSQL不仅是硬实力的技术底座，也是高速发展的商业底座。 PostgreSQL 新公司诞生 数据库新产品 数据库技术服务 例如以前很多人做Oracle，也确实能有许多就业方向，但目前市场的Oracle工程师数量众多，市场竞争激烈。随着整个时 代发生变革，市场往开源数据库和国产数据库方向前进，因此PostgreSQL是一个很好的基座。我们跟随行业与时代的变 革而变革，离开舒适区，投身主流领域，才能更好扩展自身职业生涯。 随着数据库市场的不断更新迭代，PostgreSQL产业在中国市场发生巨大变化， PostgreSQL管道铺设的各个行业，以各 种直接或间接的形式渗透到各行各业。在PostgreSQL快速发展的时代，或许35岁的中年危机也是人生契机。 ·进程结构类似 ·用户进程连接方式类似 两者都有高速缓存区，Oracle称为SGA，PostgreSQL称为内存共享，区域也是对应的。 都有日志输写进程、数据库输写进程、归档进程。 当用户连接进来时，在Oracle里面默认分配服务器之间的进程，PostgreSQL也一样。需要排序时，Oracle在PGA里面 进行，PostgreSQL在Work Men里面进行。 22Y 30Y 35Y 45Y 硬实力 软实力

Page 6

10 11PostgreSQL实战教程 ·故障排查的过程类似 Oracle和PostgreSQL都有错误日志文件等。 通过上述架构对比，可以发现Oracle与PostgreSQL十分相似，对于熟练掌握Oracle的工程师来说，可以快速上手 PostgreSQL。 随着国内数据库市场的巨大改革，PostgreSQL逐渐占据市场主导地位，然而Oracle工程师遍地都是，而从业1~2年的 PostgreSQL工程师仍是凤毛麟角。不少工程师已经开始嗅到其中契机，上手PostgreSQL增强自身竞争力，在日渐扩大 的数据库市场占得一席之地。 认识PostgreSQL中 与众不同的索引 （一）索引的作用 这些历史悠久的数据库公司为什么如此坚不可摧，存在以下几个原因： ·索引主要有三个作用： （1）加速TUPLE定位 select * from test01 where k=10; select * from test01 where k>100 and k<200; （2）主键, 唯一约束作用 create table test01(id int primary key, k int, t text); create unique idx_test01_k on test01(k); （3）排序，有索引情况下，不需要重新排序，可以直接访问用 select * from test01 order by k; （二）索引的分类 1.按算法分 按算法分类，索引可分为B-Tree索引、Hash索引、GiST索引、GIN索引与BRIN索引。 ·B-Tree索引（最常见索引） 等值查询：=、IS NULL，IN； 范围查询：>、< 、>=、 <=、BETWEEN AND、 LIKE(开头匹配), ILIKE (大小 写一致的字符开头匹配),~ ·Hash索引 只能等值查询； 等值查询可能B-Tree索引更快； PG10之前，无法在主备之间同步WAL日志。 一、索引总体介绍 作者 | 唐成 ·物理结构类似 Oracle有数据文件、控制文件、表空间、归档日志文件、参数文件、密码文件、认证文件等，PostgreSQL也都对应结 构，只是叫法不同。

Page 7

12 13PostgreSQL实战教程 ·GiST索引 不是一种索引类型，而是一种可以实现自定类型和策略的索引架构； 包含了用于二维几何数据类型的 GiST 操作符类； 包含操作符: @> 图型没有重叠操作符号：<< ·GIN索引 倒排索引，常用在全文检索中； 可高效地检测某值是否存在很多行中； 已实现了用于数组的GIN操作符类：@>、&& ·BRIN索引 块范围索引； 存储放在一个表的连续物理块范围上的值摘要信息，如最大值、最小值； 可以用于：<、<=、=、>=、 > 通常其他数据库没有BRIN索引，是PG的亮点功能。 2.其他分类 PG索引按照其他分类也可分为：唯一索引，部分索引，多列索引和表达式索引，这里不展开作详细介绍。 （五）PostgreSQL中文社区技术认证 目前PostgreSQL中文社区技术认证有三级认证，分别为PCA（认证专员）、PCP（认证专家） PCM（认证大师），可 在社区网站“http://www.postgres.cn”查看。 （三）非阻塞式创建索引 非阻塞式创建索引是PostgreSQL的一大优势。 使用普通方式创建索引时，PostgreSQL会锁定表以防止写入，在此过程中其他用户仍然可以读取表，但是DML等操作被 一直阻塞，直到索引创建完毕，这在大多数的在线数据库中都是不可接受的行为。 鉴于此，PostgreSQL支持不长时间阻塞更新的情况下建立创建索引，这是通过“CREATE INDEX CONCURRENTLY idx_tab01_note on testtab01(note);”选项来实现的。 当该选项被使用时，PostgreSQL会执行表的两次扫描，因此该方法需要更长一些的时间来建索引，尽管如此，这个选项 也是很有用的一个功能。 （四）非阻塞式重建索引 在PostgreSQL的12版本之前，重建索引时不支持Concurrently的参数，可以在同样的列上用Concurrently建一个不同 名的新索引，再把旧索引删除，这样也不阻塞DML等语句。 唯一索引 部分索引 多列索引 表达式索引 （函数索引） 软实力 CREATE INDEX idx_test01_park_k ON test01(k) where k> and k <2000; PCA（认证专员） PCP（认证专家） PCM（认证大师） 上图为BRIN索引的一个例子，我们创建一张表，并顺序插入3000000条记录，然后“create index idx_test01_k_brin” 创建一个索引。默认情况下索引有128个物理块，上面建一个最大值与最小值的摘要信息。除了默认情况，我们又建了64 个数据块与4个数据块的索引，同时我们建了一个普通的B树索引。 如上图所示，此时我们可以查看索引大小，pages_per_range不同值时BRIN索引通常在1MB以下，而普通索引为64M 以上。可以看到，用BRIN创建的索引，无论在哪种情况下，索引的大小都远远小于用B-Tree方式创建的索引。 二、BRIN索引的例子 ·create table test01(id int, t text);insert into test01(id,t) select seq, rpad('',50,'x') from generate_series(1, 3000000) as t(seq); ·create index idx_test01_k_brin_128 on test01 using brin(id); ·create index idx_test01_k_brin_64 on test01 using brin(id) with (pages_per_range=64); ·create index idx_test01_k_brin_4 on test01 using brin(id) with (pages_per_range=4); ·create index idx_test01_k_btree on test01(id); osdba=# select pg_relation_size('idx_test01_k_brin_128'); pg_relation_size ------------- 24576 (1 row) osdba=# select pg_relaion_size('idx_test01_k_brin_64'); ------------- 32768 (1 row) osdba=# select pg_relaion_size('idx_test01_k_brin_4'); ------------- 212992 (1 row) osdba=# select pg_relation_size('idx_test01_k_btree'); pg_relation_size ------------- 67403776 (1 row)

Page 8

14 15PostgreSQL实战教程 索引插入时会导致物理块上有范围次序，用BRIN索引会起到很大的作用。如上图所示，可以看到执行计划快速完成，并 走到BRIN索引上面，这就是BRIN索引的用处，也是PostgreSQL的一大亮点。 下面是一个用GIN索引查找电话号码号主的例子。 假设我们有一张表，记录了IP地址范围对应的地区，给一个公网IP就可以查询出这个IP地址所对应的地区。 （一）普通解决方案 如上图所示，该格式包含IP的ID，IP的起始地址与结束地址，IP所在地区，IP对应的运营商，Inet表示PostgreSQL里 IP地址的范围，例表如下： 假设我们先建一个联系人的表，有上图5个字段。由于每个人可能存在多个联系电话，于是我们将这些信息建成一个数 组。在数组的情况下，无法建立普通索引，但在PostgreSQL中可在数组上建立GIN索引。 在这里我们建了250000行数据，然后我们再给它建了一个GIN索引，用“@>”表示这个数组中包含某个固定电话，这样 就可以查出号码对应的号主。 通过执行计划可以看到，通过在PostgreSQL的数组上建立GIN索引来查找数值时，所需时间非常短，仅需 0.108ms。 三、数组上建GIN索引的例子 四、快速查找某个IP是哪个地区 ------------------------------------------------------------------------------------------ --- Bitmap Heap Scan on test01 (cost=89.00..1508.48 rows=1 width=55) (actual time=2.293..2.354 row=1 loops=1) Recheck Cond: (id = 100) Rows Removed by Index Recheck: 387 Heap Block: lossy=4 -> Bitmap Index Scan on idx_test01_k_brin_4 (cost=0.00..89.00 rows=388 width=0) (actual time=2.226..2.227 rows=40 loops=1) Index Cond: (id =100) Planning Time: 0.225 ms Execution Time: 2.436 ms (8 rows) ------------------------------------------------------------------------------------------ --- Bitmap Heap Scan on contacts (cost=29.69..2298.29 rows=1250 width=95) (actual time=0.079..0.080 row=1 loops=1) Recheck Cond: (phone @> '{13600006688}'::character varying(32)[]) Heap Blocks: exact=1 -> Bitmap Index Scan on idx_contacts_phone (cost=0.00..29.37 rows=1250 width=0) (actual time=0.053..0.053 rows=1 loops=1) Index Cond: (phone @> '{13600006688}'::character varying(32)[]) Planning Time: 0.113 ms Execution Time: 0.108 ms (7 rows) QUERY PLAN QUERY PLAN ·联系人表： ·CREATE TABLE contacts( ·id int primary key, ·name varchar(40), ·phone varchar(32)[], ·address text); create table ipdb1 ( id int, ip_begin inet, ip_end inet, area text, sp text ); 普通做法基本格式 osdba=# insert into contacts select seq, seq, array[seq+13600000000, seq+13600000001] from generate_ series(1, 500000, 2) as seq; INSERT 0 250000 Time: 2368.684 ms (00:02.369) osdba=# CREATE INDEX idx_contacts_phone on contacts using gin(phone); CREATE TNDEX Time: 56196.839 ms (00:56.197) osdba=# SELECT * FROM contacts WHERE phone @> array['13600006688'::varchar(32)]; id | name | phone | address ------+------+--------------------------+--------- 6687 | 6687 | {13600006687,13600006688} | (1 row) Time: 5.345 ms osdba=# explain SELECT * FROM contacts WHERE phone @> array['13600006688'::varchar(32)];

Page 9

16 17PostgreSQL实战教程 可以看到，例如IP地址1.0.1.0到1.0.3.255是来自福建电信。有了这么一个地址库，我们就可以快速查询一个IP所对应的 相关信息。 例如我们想查询36.22.250.214来自哪里，可以输入： select * from ipdb1 where '36.22.250.214'>=ip_begin and '36.22.250.214' <=ip_end; 耗时308ms，得到结果如下，可以看到这个地址来自浙江电信。 通过执行计划可以看到，该SQL是一个并行的全表扫描，CPU占用高。 这种情况的改进方法，是在起始地址上加一个索引： create index idx_ipdb1_ip_begin on ipdb1(ip_begin); 由于索引还是做了范围查询，因此占用资源较多。 （二）终极解决方案 如上方所示，该方案创建一个RANGE类型，RANGE类型表明起始时间与结束时间，然后将IP地址的开始与结束都放在 一个字段中，然后在该字段中建一个GIST索引。 然后在查的范围是包含了某个IP地址，这时走的索引的效率远高于之前的范围查询索引，相当于是一个等值查询。 通过执行计划可看到，加了该索引之后，耗时大幅减少。 此时可以在结束地址上也加一个索引： create index idx_ipdb1_ip_end on ipdb1(ip_end);

Page 10

18 19PostgreSQL实战教程 可以看到，ip_range字段包含了表的起始与截至，此时加入输入： select * from ipdb2 where ip_range @> '36.22.250.214'::inet; 查询 IP 36.22.250.214，可以快速查到对应信息浙江电信。 从上方的执行计划可以看到耗时大幅减少，并且Cost值为8.3，对比之前的268大幅降低。 通过这种方式，当有大量系统要来查询IP地址时，可以有效减少耗时，并降低CPU占用，以上就是GIST用RANGE使用 的一个例子。 如上方所示，首先我们建一张表，插入1000000条测试数据，接着收集统计信息。由于现在表中可能没有索引，走的并行 做全盘扫描，此时执行时间为100~300毫秒。如果关掉并行，执行时间还会更长。 其他数据库中， like是要找两个%中间的数，通常是无能为力，但在PostgreSQL中可以解决这个问题。 首先先装入插件create extension pg_trgm;，之后建一个GIN索引，让Like走'%99999%'。通过执行计划可以看到，这 次执行时间为2ms，效率很高，解决了其他数据库遇到的难题。 PostgreSQL中还有一个黑科技——让Like在'%XXX%'走索引，下面举例说明。 五、让like %XXX%走索引

Page 11

20 21PostgreSQL实战教程 4.造数据（续） 接着开始造入100000条记录的数据，由于标签是造的数据，所以是随机生成的。 5. 建GIN索引 CREATE INDEX idx_user_tag_tag on user_tag using gin(tag); 造数据完成后，在列上建GIN索引，建立完成后，可在表中快速查询到相应信息。例如查询性格为“外向”和“细心”的 老师，可以通过语句： select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["老师"]}'; 可以很快查到，如下方所示： 如果要查询更为详细的信息，例如性格为“外向”和“细心”而又喜欢“滑雪”和“游泳”的医生，可以通过语句： select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["医生"]}' and tag @>'{"爱好":["滑 雪", "游泳"]}'; 很快查到，如下方所示： 最后我们来看，如何用GIN索引在JSON上做用户画像系统。 1.标签模型 ·职业：农民、工人、IT工程师、理发师、医生、老师、美工、律师、公务员、官员 ·爱好：游泳、乒乓球、羽毛球、网球、爬山、高尔夫球、滑雪、爬山、旅游 ·学历：无学历、小学、初中、高中、中专、专科、本科、硕士、博士 ·性格：外向、内向、谨慎、稳重、细心、粗心、浮躁、自信 首先建立一个简单的标签模型如上，总共分为四类：职业、爱好、学历和性格。 2.建表 CREATE TABLE user_tag(uid serial primary key, tag jsonb); 第二步通过我们建立一张表，第一个字段UID表示用户ID，第二个TAG是打标签，此处打一个JSONB的数据类型。 3.造数据 建完表后，为了查看效果需要造数据，我们写了一些辅助的函数来完成，函数如下： 六、GIN+JSON用户画像

Page 12

22 23PostgreSQL实战教程 如果我们给用户打了这么一个标签，就可通过SQL很快查出对应的标签信息，以上就是用GIN索引做用户画像的一个简单 示例。 更多阿里云PostgreSQL图像识别、人脸识别、相似特征检索、相似人群圈选等精选案例可在https://developer.aliyun. com/article/747642查看。 PG Ganos时空场景快速开发实践 （一）Ganos是什么 Ganos是包含SQL + NoSQL云数据库与大数据的时空数据引擎。 Ganos取名于大地女神盖亚（Gaea） 和时间之神柯罗诺斯 （Chronos），代表“时空”结合。Ganos的使命是将时 空信息处理融入公有云/专有云PaaS服务，成为一种普惠计算。 上图列举了Ganos的许多特性，这些特性有数据库本身的能力，更多的是Ganos赋能给数据库之后的能力。 （二）Ganos支持哪些产品 一、认识Ganos 作者 | 图贲 Ganos特性 GeoSQL 矢量模型 栅格模型 时空模型 百万列 万亿行 ~10亿 QPS 事务支持 高可展 ~PB级 稀疏表 读写毫秒 动态列 R-Tree 高压缩 10:1 分布式 架构 存储计算 分离

Page 13

24 25PostgreSQL实战教程 Ganos不是一个独立的产品，而是以赋能的方式嵌入在云数据库产品中。 如上图所示， Ganos赋能的产品包括RDS PG、PolarDB、ADB PG、Lindorm/HBase以及DLA。其中RDS PG和 PolarDB是事务型关系数据库，既支持事务型应用，也支持轻量级分析，这两款数据库中的Ganos模型和函数功能是最全 面也是最丰富的。 可以将Ganos理解为PostGIS的升级版本PostGIS++，高度兼容PostGIS，其他几款数据库产品ADB Ganos、Lindorm/ HBase Ganos、DLA Ganos更多是面向大数据分析型的场景。 （三）Ganos中丰富的时空模型 相比PostGIS，Ganos在时空模型上具备更多更强的特性和能力。 除了支持传统的几何模型、栅格模型和拓扑网络模型，还扩展支持了网格模型、时空轨迹模型以及点云模型。其中空间网 格模型是Ganos3.0版本推出的新特性，编码标准遵循自然资源部地球空间网格编码规则，是在这个规则基础之上设计和 实现模型。 （一）创建Ganos扩展 ·几何模型 Create extension ganos_geometry cascade; Create extension ganos_geometry_topology; ·栅格模型 Create extension ganos_raster; ·轨迹模型 Create extension ganos_trajectory; ·点云模型 Create extension ganos_pointcloud; ·路径模型 Create extension ganos_trajectory; ·网格码模型 Create extension ganos_geomgrid; ·矢量金字塔 Create extension ganos_geometry pyramid; 在PostgreSQL数据库中使用Ganos需要先创建Ganos的扩展。 上图列举了Ganos中的七大模型以及扩展语句，其中六个模型在上面已做过介绍，此处要额外补充的是矢量金字塔模 型。它是在几何模型基础之上新增的一项黑科技，是为了能够快速显示大规模空间几何数据（千万级以上）而设计的一 种索引结构。矢量金字塔对空间几何数据创建一种稀疏索引，可以动态输出标准的mvt-pbf格式数据，通过Ganos提 供的矢量金字塔，亿条空间几何记录可以实现分钟级索引创建和秒级终端显示，无需进行传统繁琐的切瓦片处理。 创建扩展之后，如上图所示，在数据库中可以通过\dx命令就能查询到所有已经创建的扩展，目前Ganos3.3版本。 （二）矢量、栅格、轨迹入库 创建Ganos扩展之后，接下来要解决数据入库，不同的数据类型有不同的入库方法： ·矢量数据入库 因为兼容postgis生态，可直接使用空间开源工具，包括ogr2ogr、shp2pgsql、QGIS、pg_dump/pg_restore等。 ·栅格、遥感数据入库 （1）Ganos提供入库接口ST_importFrom、ST_createRast； （2）Ganos支持OSS作为存储引擎，这意味着用户可以将栅格原始文件存放在OSS中，Ganos会建立内部高效连接， 其中金字塔索引可选择数据库内建，也可选择适配OSS上已有索引信息； （3）pg_dump/pg_restore时，OSS外部原始文件不需要挪动，不影响PG数据库的使用； （4）入库时支持金字塔内建+外建自由组合； （5）支持批量文件并行入库，支持单幅超大影像切分后并行入库。 ·轨迹数据入库 Ganos提供入库接口ST_makeTrajectory； 支持轨迹点动态追加； 支持点表抽取为轨迹对象。 此外，Ganos支持与商用GIS平台如SuperMap、ArcGIS对接， 矢量数据与栅格/遥感数据可借助其平台直接入库。 （三）PG Ganos如何管理PB级遥感影像 1. PostgresSQL + Ganos + OSS组合 二、如何使用Ganos

Page 14

26 27PostgreSQL实战教程 上文中提到，Ganos在云上可以借助OSS存储，它是在引擎层打通的。因此通过“PostgreSQL + Ganos + OSS”组 合，可实现 PB级遥感影像的管理。元数据和部分金字塔数据可以存储在数据库内部，遥感数据原始文件存放在OSS中， 由于OSS存储价格低廉，使得用户的使用成本也大大降低。 2. 遥感影像注册（入库） 只需要按照insert SQL语句直接写入到数据库，将OSS地址传给ST_createRast接口即可。这里需要注意的是OSS与 RDS购买域必须为同一个，比如都是北京区域。 3. 大范围影像拼接、镶嵌 将遥感影像数据注册入库之后，在Ganos也可以通过ST_mosaicFrom、ST_mosaicTo对大范围的影像进行拼接镶嵌等 操作再进行输出，达到管理PB级遥感影像管理的目的。 如上图所示，Ganos管理轨迹数据主要通过轨迹构造、轨迹压缩和轨迹相似性判断。 在Ganos中有原生的轨迹模型叫Trajectory，在创建轨迹表时可直接用这个数据类型。轨迹构造的单独接口ST_ makeTrajectory有很多的重载版本，具体使用方式可在官网的用户手册里进行查看。 轨迹还提供一些轨迹压缩与轨迹相似性判断这些比较重要的接口。轨迹压缩是通过ST_Compress压缩接口实现，压缩 时可以保留重要的轨迹特征点，因此压缩质量会更好。轨迹相似性目前主要支持Lcss算法以及Jaccard的这种路径匹 配算法。 （五）Ganos与开源工具 （四）PG Ganos如何管理轨迹数据 Ganos cloud spatio- temporal DB MapServer SAGA GIS TerraLib TerraVlew GeoNetwork GRASS GIS gvSIG MapNik OpenJump UDig GeoServer QGIS Open- StreetMap

Page 15

28 29PostgreSQL实战教程 如上图所示，Ganos无缝对接兼容PostGIS的各类GIS软件，显示和编辑包括GeoServer、QGIS、uDig、OpenJump 等，这里重点介绍PGAdmin4。 PGAdmin4通过与Ganos集成，能够支持显示Ganos中的矢量和栅格数据，后续也会支持轨迹数据的直接显示。同时， 在PGAdmin4中可以使用Ganos矢量金字塔功能，也就是说在PGAdmin4中可以直接显示上亿级的矢量数据，可以达到 不切片、无障碍浏览效果。（需要PGAdmin4的定制版本可与我们联系，将来PGAdmin4改造代码会开源出来，供大家 下载） （二）空间统计分析 1.数据准备 数据准备包含两个类型的数据：矢量数据与栅格数据。 （1）矢量数据：包含多边形的行政街区数据（表hk_tpu）与点类型的患者案例数据(表hk_cases)。如上图所示，通过矢 量数据可以看到案例患者的性别与年龄、确诊医院、所属街区等。 （一）实战介绍 实战课题 （1）如何通过Ganos快速分析城市结构、社会属性与新冠病毒传播的之间的关系； （2）如何在Ganos中通过轨迹数据追踪患者行程，并挖掘风险点。 实战技能 （1）利用Ganos进行空间统计分析； （2）实现矢量、栅格一体化查询； （3）实现轨迹追踪； （4）实现跨区域时空查询。 实战目的 （1）熟练使用Ganos； （2）学会多源数据融合处理； （3）实现时空场景快速呈现，减少开发成本。 三、进阶实战

Page 16

30 31PostgreSQL实战教程 （2）栅格数据：是具有社会属性的数据，包括NDVI监测数据、建筑密度、建筑高度值等，都以Tif文件形式提供。 2.数据入库 ·矢量数据库入库 ·ogr2ogr -nln hk_tpu -nlt MULTIPOLYGON -geomfield geom -f PostgreSQL PG:"dbname=‘ganos_train_db’host=‘pgm-***.rds.aliyuncs.com'port='1921' user='ganos_train' password='ganos@2021‘“ ”./data/hk_tpu_84.shp” ·ogr2ogr -nln hk_cases …“./data/sick_cases.shp” 矢量数据是用ogr2ogr进行入库，填写的是云上购买的RDS PG的访问参数。 ·栅格数据入库 ·首先，Tif文件上传至OSS； ·其次，执行导入的SQL语句insert into hk_ndvi_rast values(1, st_importfrom('chunktbl', 'OSS://accessKey:accessSecret@oss-cn-****- internal.aliyuncs.com/bucket/data/ndvi_84.tif')); 这里由于影像文件较小，采用的是ST_Importfrom接口，可以将影像文件的所有像素值全部写入到数据库。 3.统计分析 如上图所示，假如要统计街区患者案例较多的街区编号，可以通过st_contains空间查询接口，快速的得到案例排名前5的 街区编号，分别为121、131、212、113、144。 （三）矢栅一体化查询 用Ganos可以进行矢量+栅格一体化查询，提高开发效率。例如查询街区编号为121区域的NDVI监测总值与平均值，查询 某某街区的建筑密度、建筑高度等这类问题。传统的GIS开发实现，上述查询通常需要五个步骤，如下图所示： 如今用Ganos一条SQL语句即可搞定，语句如下： 通过ST_Values接口传入一个栅格对象，接着再传入一个几何对象，指定栅格对象的查询波段，然后就可以统计几何对象 范围内所有的像素值，同时计算它的平均值，极大提高开发效率。 在矢删一体化的基础上，可以分析城市结构、社会属性与新冠病毒传播的之间的关系，以下是通过一条SQL语句查询计算 所有街区的ndvi平均值与案例数之间的关系。 select m.tpu,m.sum as ndvi_sum, m.avg as ndvi_avg, n.cases_count from (select c.tpu as tpu, sum(c.value) as sum,avg(c.value) as avg from (SELECT b.tpu as tpu, ( ST_Values(a.rast, b.geom, 0)).* from hk_ndvi_rast a, hk_tpu b) c group by c.tpu) m, (select b.tpu as tpu ,count(a.id) as cases_count from hk_cases a, hk_tpu b where st_contains(b.geom,a.geom) group by b.tpu) n where m.tpu=n.tpu order by m.avg;

Page 17

32 33PostgreSQL实战教程 NDVI值表示植被指数，当这个值过大或者过小时，不一定代表人群数多，往往是在中间值时，人口聚集最多。如上图所 示，我们截取了前面几十条以及后面几十条数据，通过结果可以发现NDVI值在为0.2~0.27之间，它的案例数是最聚集 的。 同样通过一条SQL语句，我们可以计算所有街区的建筑高度值与案例数之间的关系，如下所示： 可以看到，在建筑密度比较低的这些街区中，案例分布通常是个位数。在建筑密度比较高的这些街区中，案例数明显增加, 最大值也是分布在建筑密度比较高的这些区域,由此可以反映出来建筑密度值越大,案例数量呈现聚集性。 （四）轨迹追踪 1.用点表构造轨迹表 我们可以将患者的案例形成轨迹，同时通过轨迹追踪患者的行程，挖掘一些潜在的风险点。 如上图所示，我们用案例编号查询行程，可以看到编号为105的案例在不同的时间去过很多场所（案例数据中监测时间记 录不够详细，只具体到某一天）。可以将监测行程点抽取形成一条轨迹数据，通过ST_makeTrajectory，把编号为105的 案例聚合成一条轨迹写入到轨迹表里。 如果想把所有的患者案例聚合成轨迹，将这个查询语句中的where cases =105直接改成Group Bycases就可以实现。 2.轨迹追踪 形成轨迹表后可以进行轨迹追踪，需要经过下列语句： ·轨迹空间显示 ·create table hk_case_traj_geom as select id,st_trajectoryspatial(traj) as geom from hk_case_traj; ·轨迹追踪 ·select traj from hk_case_traj where id = 230; 例如我们查询编号为230的轨迹，可到轨迹表中输入相应编号进行查询，可以快速查询到该编号到过的场所，再根据时间 先后模拟出主要走向，如下方所示：

Page 18

34 35PostgreSQL实战教程 （五）时空查询 如果通过案例查询310区域，那么在案例表中会显示该区域曾经出现过一个患者案例，如下图所示： 街区编号为88（tpu=88）的区域通过st_contains只查询出一个患者案例。 但我们拿到行程轨迹后，可以进行轨迹跨区域时空查询，包括时间空间的交织，挖掘潜在的风险点。 实现语句如下： 通过这种方式，可以查询到穿过310区域的患者轨迹有8条，也就是说有些区域虽然案例数少，但是有其他案例经过该区 域，只是数据没有被挖掘出来。通过轨迹的时空查询将潜在的数据挖掘出来，这个结果会影响该区域的风险等级判断。如 下图： （六）使用接口汇总 ·Ganos Geometry ST_Intersects、ST_Contains 矢量接口：主要是空间关系的判断。 ·Ganos Raster ST_importFrom、ST_createRast、ST_Values、ST_mosaicFrom、ST_ mosaicTo 栅格接口：主要是入库、矢量栅格一体化查询以及拼接镶嵌。 ·Ganos Trajectory ST_makeTrajectory、ST_trajectorySpatial、ST_Intersects 轨迹接口：主要是轨迹构造，轨迹的空间对象，以及轨迹的时空查询。 更多资料，欢迎扫码加入 “Ganos时空云计算”钉钉群 select traj from hk_tpu a, hk_case_traj b where st_intersects(b.traj,‘2020-03-19 00:00:00'::timestamp, ‘2020-03-21 00:00:00'::timestamp, a.geom) and a.tpu = 310;

Page 19

36 37PostgreSQL实战教程 高维向量检索技术在PG中的设计与 实践 什么是向量检索（近似最近邻检索/ANN） 向量检索是从一堆已知的点中，找出给定P点的最相邻的K个点的过程。这些点可以是：1维点、2维的点、3维的点... ... 这 些点我们统一叫做向量。 高维：维度大于10，小于1000，称之为高维向量。 超高维：维度大于1000，称之为超高维向量。 应用场景一：以图搜图/人脸识别 以图搜图和人脸识别两个应用场景，属于一个范畴。 图片搜索类，比如在淘宝的搜索框后边的拍立淘。拍立淘的功能是：对感兴趣的商品进行拍照，然后搜索到和它相似的商 品。后端实现的技术是，运用深模型比如CNN等对图片进行特征抽取，抽取出来的特征就是高维度向量。比如是256维度 的向量，查询的时候，将拍照的照片进行同样的特征抽取，也取出256维向量检索过程，运用256维的向量在全库中进行 相似的向量查询。如下图所示： 以图搜图和人脸识别这两个过程都运用到了向量检索技术。 应用场景二：推荐 个性化推荐场景的方式类似于以图搜图方式，区别在于，推荐是基于用户的特征查找和用户感兴趣商品的过程。它的技术 实现方式是采用双塔模型等深度模型，对用户进行用户特征的抽取，对商品进行商品特征的抽取。在最后查询阶段，是用 用户的特征在商品的特征库中进行检索。这是个性化推荐的项目检索过程。如下图所示： 应用场景三：基于深度模型的语义检索 基于深度模型的语义检索，它的应用场景也很广泛，比如打开支付宝或者淘宝进行搜索，都运用到了相应的技术。最近比 较火热的bert模型，也适用于这种场景。运用深度模型对词汇进行特征抽取，最后会落到检索过程，还有应用向量检索查 找到与待查询词相匹配的商品。如下图所示： 人脸识别是支付宝的特色产品，它的应用场景就是刷脸支付。刷脸支付的技术实现方式和以图搜图类似。在离线训练CNN 等深度模型，在离线对全库的人脸进行特征抽取，做存库的处理。刷脸时，对采集的这张照片进行人脸特征抽取，运用这 个特征在全库中进行相似点查询。 一、背景介绍 作者 | 杨文（缁尘） distance query

Page 20

38 39PostgreSQL实战教程 通过以上场景可以发现向量检索技术广泛的应用于搜索、推荐、人脸识别等等场景，结合深度学习，技术的发展，向量 检索技术最近这些年也得到了比较快速的发展和广泛的应用。 进行kmeans等聚类方式，将相邻向量使用中心点代替，从而通过倒排索引以及子空间距离计算加速等策略提高检索速度。 基于图：近邻的近邻也很可能是近邻。从随机选择的初始点开始，通过检查邻居里距离query更近的点，把该点当作下一 次迭代需要检查邻居的点，如此不断迭代，通过邻居的邻居，我们会逐渐逼近query。 这4种方式各有优缺点，在实际应用场景中，根据各个业务不同的要求进行选取。下边针对一些典型的算法，对算法原理 进行详细描述。 1. 粗量化（IVFFlat） 算法原理如下图所示，空间中所有的向量，如最左边的向量天然具有聚类的属性。然后进行聚类，把空间分成两个类，如 图所示分成上下两类，每个类存在一个中心点，比如图中用红点表示类簇的中心点，这是离线的处理过程。在线查询时， 根据query和两个中心点进行比较，查到距离哪个中心点最近，只保留中心点所处的类簇，丢弃不相关的类簇。 这种算法优缺点很明显，即算法足够简单，只需要聚类即可。缺点是虽然精度可以足够高，但需要查询的中心点保留很 多，比如图中可以保留两个中心点时，可以达到100%的准确率，这相当于暴力检索，性能不高，即精度换取性能的效率 并不高。适用场景是精度要求很高，但是对查询耗时要求不严格的场景，一般在100毫秒级要求的场景。总结粗量化的特 征是： 要点： 1. 聚类。 2. 查询中心点集合=>遍历对应类簇。 优缺点与适用场景： ·优点：简单 ·缺点：精度高性能不高 ·适用场景：召回精度要求高，但对查询耗时要求不严格（100ms级别）的场景。 向量检索最简单的实现方法是逐一比对方式，简称为暴力搜索方式。这种方式优缺点很明显，优点是准确率足够高，逐一 比对，总能找到最相似的向量，这种方式的准确率是100%。缺点是查询耗时很高，不能应用于工业界的场景中。 ANN检索算法 业界研究出了很多的向量检索算法，向量检索算法简称为ANN检索算法，即近似最近邻的算法，通过这个简称也能发现， 一般的实现方式都是运用精度的损失，来换取性能的大幅提升。 因此一个算法的好坏，可以用转换的效率是否足够高来衡量，能用很小的精度损失来换取大幅的性能提升的ANN算法就是 一个好的算法。目前常用的相应检索算法，总结基本可以分为4大类，基于树、基于哈希、基于量化、基于图，这4个方式 各有优缺点，也各有不同的应用场景。 基于树：KD-tree、KMeans-tree、VP-tree、Ball-tree等等，其共同特点在于利用某种策略，对空间进行逐层切分， 直到树的叶子节点只覆盖空间的一个小的局部。通过这个方式大幅提升检索性能。 基于哈希：局部敏感哈希（Locality Sensitive Hashing）、谱哈希（Spectral Hashing）、球哈希（Sphere Hashing）、锚 点图哈希（AnchorGraph Hashing）等等。他们的共同特征则是通过超平面或者曲面，将空间进行直接切分，并对切分 后得到的各个细小区域进行二进制编码，通过构建哈希表来加速查询。其中，超平面或者曲面可以通过学习或者非学习的 方式得到，其表示我们称之为哈希函数。 基于量化：矢量量化（Vector Quantization）、粗量化（Coarse Product）、积量化（ProductQuantization）及其改 进的最优积量化（Optimised Product Quantization）、复合积量化（Composite Quantization）。量化的思想是对向量 二、向量检索算法/PG自定义索引 centroid1 centroid2 query top1 InvertedList 1 InvertedList 2

Tip the Site

PostgreSQL实战教程 (it-ebooks) (Z-Library)

PostgreSQL实战教程 (it-ebooks) (Z-Library)

Registered users can read the full content for free

AI Reading Assistant

Passage locations

Recommended for You

Tip the Site