PostgreSQL 大法师
冯若航 @Vonng / Pigsty
PostgreSQL 生态进展，以及开发、使用、运维、管理、诊断、调优的经验分享。
故障档案：PG安装Extension导致无法连接
今天遇到一个比较有趣的Case，客户报告说数据库连不上了，发现是扩展导致的。
CDC 变更数据捕获机理
数据变更捕获是一种很有趣的ETL替代方案，以流式的方式持续收集状态变化事件。
PostgreSQL中的锁
详细介绍PostgreSQL中的各种锁：表级锁、行级锁、页级锁、咨询锁等。
GIN搜索的O(n²)复杂度
GIN索引如果使用很长的关键词列表进行搜索，会导致性能显著下降。本文解释了为什么GIN索引关键词搜索的时间复杂度为O(n²)。
PostgreSQL 常见复制拓扑方案
复制是系统架构中的核心问题之一。
温备：使用pg_receivewal
备份有各种各样的策略，物理备份通常可以分为四种。
PostgreSQL监控系统概览
监控系统是几乎所有运维工作的基础，更亦是驾驭数据库的必备工具，本文简单介绍了一个PostgreSQL的监控系统。
故障档案：pg_dump导致的连接池污染
有时候，组件之间的相互作用会以微妙的形式表现出来。例如使用pg_dump从连接池中导出数据，就可能产生连接池污染的问题。
PostgreSQL数据页面损坏修复
采用二进制编辑的方式修复PostgreSQL数据页，以及如何让一条主键查询出现两条记录来。
关系膨胀的监控与治理
PostgreSQL使用了MVCC作为主要并发控制技术，它有很多好处，但也会带来一些其他的影响，例如关系膨胀。
TimescaleDB 快速上手
TimescaleDB是PostgreSQL的一个扩展插件，提供时序数据库的一些功能。
PipelineDB快速上手
PipelineDB是PostgreSQL的一个扩展插件，提供流式数据处理的相关功能。
故障档案：序列号消耗过快导致整型溢出
如果您在表上用了Interger的序列号，最好还是考虑一下可能溢出的情况。
故障档案：PostgreSQL事务号回卷
XID WrapAround也许是PostgreSQL特有的一种故障。
PostgreSQL的触发器使用注意事项
详细了解PostgreSQL中触发器的管理与使用。
GeoIP 地理逆查询优化
在应用开发中，一个很常见的需求就是GeoIP转换：将请求的来源IP转换为相应的地理坐标，或者行政区划。
PostgreSQL开发规约（2018版）
没有规矩，不成方圆。本文针对PostgreSQL数据库原理与特性，整理了一份开发规范，可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。
PostgreSQL好处都有啥
PostgreSQL的Slogan是"世界上最先进的开源关系型数据库"，要我说最能生动体现PG特色的口号应该是：一专多长的全栈数据库，一招鲜吃遍天。
PostGIS高效解决行政区划归属查询
如何高效解决典型地理逆编码问题：根据用户的经纬度坐标，定位用户的行政区划。
KNN极致优化：从RDS到PostGIS
KNN问题极致优化，从传统关系型设计到PostGIS，实现GIS圈选场景下三万倍的性能提升。
监控PG中的表大小
PostgreSQL中的表对应着许多物理文件，本文介绍如何统计一张表在PostgreSQL的实际大小。
PgAdmin安装配置
PgAdmin是一个管理PostgreSQL的GUI程序，用python写成，但实在是过于古早，需要一些额外配置。
故障档案：快慢不匀雪崩
最近发生了一起匪夷所思的故障，某数据库切走了一半的数据量和负载，结果却因为负载变大被打挂了。
Bash与psql小技巧
一些PostgreSQL与Bash交互的技巧。
故障档案：PG安装Extension导致无法连接
今天遇到一个比较有趣的Case，客户报告说数据库连不上了。报这个错： psql: FATAL: could not load library "/export/servers/pgsql/lib/pg_hint_plan.so": /export/servers/pgsql/lib/pg_hint_plan.so: undefined symbol: RINFO_IS_PUSHED_DOWN 当然，这种错误一眼就知道是插件没编译好，报符号找不到。因此数据库后端进程在启动时尝试加载 …
今天遇到一个比较有趣的Case，客户报告说数据库连不上了。报这个错： psql: FATAL: could not load library "/export/servers/pgsql/lib/pg_hint_plan.so": /export/servers/pgsql/lib/pg_hint_plan.so: undefined symbol: RINFO_IS_PUSHED_DOWN 当然，这种错误一眼就知道是插件没编译好，报符号找不到。因此数据库后端进程在启动时尝试加载 …
CDC 变更数据捕获机理
在实际生产中，我们经常需要把数据库的状态同步到其他地方去，例如同步到数据仓库进行分析，同步到消息队列供下游消费，同步到缓存以加速查询。总的来说，搬运状态有两大类方法：ETL与CDC。 前驱知识 CDC与ETL 数据库在本质上是一个 状态集合，任何对数据库的 变更（增删改）本质上都是对状态的修改。 在实际生产中，我们经常需要把数据库的状态同步到其他地方去，例如同步到数据仓库进行分析，同步到消息队列供下游消费，同步到缓存以加速查询。总的来说，搬运状态有两大类方法：ETL与CDC。 …
在实际生产中，我们经常需要把数据库的状态同步到其他地方去，例如同步到数据仓库进行分析，同步到消息队列供下游消费，同步到缓存以加速查询。总的来说，搬运状态有两大类方法：ETL与CDC。 前驱知识 CDC与ETL 数据库在本质上是一个 状态集合，任何对数据库的 变更（增删改）本质上都是对状态的修改。 在实际生产中，我们经常需要把数据库的状态同步到其他地方去，例如同步到数据仓库进行分析，同步到消息队列供下游消费，同步到缓存以加速查询。总的来说，搬运状态有两大类方法：ETL与CDC。 …
PostgreSQL中的锁
PostgreSQL的并发控制以 快照隔离（SI） 为主，以 两阶段锁定（2PL） 机制为辅。PostgreSQL对DML（SELECT, UPDATE, INSERT, DELETE 等命令）使用SSI，对DDL（CREATE TABLE 等命令）使用2PL。 PostgreSQL有好几类锁，其中最主要的是 表级锁 与 行级锁，此外还有页级锁，咨询锁等，表级锁 通常是各种命令执行时自动获取的，或者通过事务中的 LOCK 语句显式获取；而行级锁则是由 SELECT FOR …
PostgreSQL的并发控制以 快照隔离（SI） 为主，以 两阶段锁定（2PL） 机制为辅。PostgreSQL对DML（SELECT, UPDATE, INSERT, DELETE 等命令）使用SSI，对DDL（CREATE TABLE 等命令）使用2PL。 PostgreSQL有好几类锁，其中最主要的是 表级锁 与 行级锁，此外还有页级锁，咨询锁等，表级锁 通常是各种命令执行时自动获取的，或者通过事务中的 LOCK 语句显式获取；而行级锁则是由 SELECT FOR …
GIN搜索的O(n²)复杂度
GIN索引如果使用很长的关键词列表进行搜索，会导致性能显著下降。本文解释了为什么GIN索引关键词搜索的时间复杂度为O(n^2) Here is the detail of why that query have O(N^2) inside GIN implementation. Details Inspect the index example_keys_idx postgres=# select oid,* from pg_class where relname = …
GIN索引如果使用很长的关键词列表进行搜索，会导致性能显著下降。本文解释了为什么GIN索引关键词搜索的时间复杂度为O(n^2) Here is the detail of why that query have O(N^2) inside GIN implementation. Details Inspect the index example_keys_idx postgres=# select oid,* from pg_class where relname = …
PostgreSQL 常见复制拓扑方案
复制是系统架构中的核心问题之一。 集群拓扑 假设我们使用4单元的标准配置：主库，同步从库，延迟备库，远程备库，分别用字母M,S,O,R标识。 M：Master, Main, Primary, Leader, 主库，权威数据源。 S: Slave, Secondary, Standby, Sync Replica，同步副本，需要直接挂载至主库 R: Remote Replica, Report instance，远程副本，可以挂载到主库或同步从库上 O: Offline，离线延迟备库，可以挂载到主 …
复制是系统架构中的核心问题之一。 集群拓扑 假设我们使用4单元的标准配置：主库，同步从库，延迟备库，远程备库，分别用字母M,S,O,R标识。 M：Master, Main, Primary, Leader, 主库，权威数据源。 S: Slave, Secondary, Standby, Sync Replica，同步副本，需要直接挂载至主库 R: Remote Replica, Report instance，远程副本，可以挂载到主库或同步从库上 O: Offline，离线延迟备库，可以挂载到主 …
温备：使用pg_receivewal
备份是DBA的安身立命之本，也是数据库管理中最为关键的工作之一。有各种各样的备份，但今天这里讨论的备份都是物理备份。物理备份通常可以分为以下四种： 热备（Hot Standby）：与主库一模一样，当主库出现故障时会接管主库的工作，同时也会用于承接线上只读流量。 温备（Warm Standby）：与热备类似，但不承载线上流量。通常数据库集群需要一个延迟备库，以便出现错误（例如误删数据）时能及时恢复。在这种情况下，因为延迟备库与主库内容不一致，因此不能服务线上查询。 冷备（Code Backup）： …
备份是DBA的安身立命之本，也是数据库管理中最为关键的工作之一。有各种各样的备份，但今天这里讨论的备份都是物理备份。物理备份通常可以分为以下四种： 热备（Hot Standby）：与主库一模一样，当主库出现故障时会接管主库的工作，同时也会用于承接线上只读流量。 温备（Warm Standby）：与热备类似，但不承载线上流量。通常数据库集群需要一个延迟备库，以便出现错误（例如误删数据）时能及时恢复。在这种情况下，因为延迟备库与主库内容不一致，因此不能服务线上查询。 冷备（Code Backup）： …
PostgreSQL监控系统概览
PostgreSQL驾驶技巧 PostgreSQL是一个很棒的数据库，但也相当复杂。上手虽简单，但想要用好不容易，想要管理好就更麻烦了。监控系统是几乎所有运维工作的基础，更亦是驾驭数据库的必备工具。用好一个监控系统，理解各种指标背后的意义并不是一件简单的事情，因此本司机决定写一系列文章，来介绍了PostgreSQL监控系统的设计，实施与使用。 0x01 PostgreSQL监控面板 为了帮助读者形成一种直觉，这里展示了在实际环境中，我所使用的监控面板。最为常用的监控面板为“单数据库实例”监控，并 …
PostgreSQL驾驶技巧 PostgreSQL是一个很棒的数据库，但也相当复杂。上手虽简单，但想要用好不容易，想要管理好就更麻烦了。监控系统是几乎所有运维工作的基础，更亦是驾驭数据库的必备工具。用好一个监控系统，理解各种指标背后的意义并不是一件简单的事情，因此本司机决定写一系列文章，来介绍了PostgreSQL监控系统的设计，实施与使用。 0x01 PostgreSQL监控面板 为了帮助读者形成一种直觉，这里展示了在实际环境中，我所使用的监控面板。最为常用的监控面板为“单数据库实例”监控，并 …
故障档案：pg_dump导致的连接池污染
PostgreSQL很棒，但这并不意味着它是Bug-Free的。这一次在线上环境中，我又遇到了一个很有趣的Case：由 pg_dump 导致的线上故障。这是一个非常微妙的Bug，由Pgbouncer，search_path，以及特殊的 pg_dump 操作所触发。 背景知识 连接污染 在PostgreSQL中，每条数据库连接对应一个后端进程，会持有一些临时资源（状态），在连接结束时会被销毁，包括： 本会话中修改过的参数。RESET ALL; 准备好的语句。 DEALLOCATE ALL 打开的游 …
PostgreSQL很棒，但这并不意味着它是Bug-Free的。这一次在线上环境中，我又遇到了一个很有趣的Case：由 pg_dump 导致的线上故障。这是一个非常微妙的Bug，由Pgbouncer，search_path，以及特殊的 pg_dump 操作所触发。 背景知识 连接污染 在PostgreSQL中，每条数据库连接对应一个后端进程，会持有一些临时资源（状态），在连接结束时会被销毁，包括： 本会话中修改过的参数。RESET ALL; 准备好的语句。 DEALLOCATE ALL 打开的游 …
PostgreSQL数据页面损坏修复
PostgreSQL是一个很可靠的数据库，但是再可靠的数据库，如果碰上了不可靠的硬件，恐怕也得抓瞎。本文介绍了在PostgreSQL中，应对数据页面损坏的方法。 最初的问题 线上有一套统计库跑离线任务，业务方反馈跑SQL的时候碰上一个错误： ERROR: invalid page in block 18858877 of relation base/16400/275852 看到这样的错误信息，第一直觉就是硬件错误导致的关系数据文件损坏，第一步要检查定位具体问题。 这里，16400是数据库的 …
PostgreSQL是一个很可靠的数据库，但是再可靠的数据库，如果碰上了不可靠的硬件，恐怕也得抓瞎。本文介绍了在PostgreSQL中，应对数据页面损坏的方法。 最初的问题 线上有一套统计库跑离线任务，业务方反馈跑SQL的时候碰上一个错误： ERROR: invalid page in block 18858877 of relation base/16400/275852 看到这样的错误信息，第一直觉就是硬件错误导致的关系数据文件损坏，第一步要检查定位具体问题。 这里，16400是数据库的 …
关系膨胀的监控与治理
PostgreSQL使用了MVCC作为主要并发控制技术，它有很多好处，但也会带来一些其他的影响，例如关系膨胀。关系（表与索引）膨胀会对数据库性能产生负面影响，并浪费磁盘空间。为了使PostgreSQL始终保持在最佳性能，有必要及时对膨胀的关系进行垃圾回收，并定期重建过度膨胀的关系。 在实际操作中，垃圾回收并没有那么简单，这里有一系列的问题： 关系膨胀的原因？ 关系膨胀的度量？ 关系膨胀的监控？ 关系膨胀的处理？ 本文将详细说明这些问题。 关系膨胀概述 假设某个关系实际占用存储100G，但其中有很 …
PostgreSQL使用了MVCC作为主要并发控制技术，它有很多好处，但也会带来一些其他的影响，例如关系膨胀。关系（表与索引）膨胀会对数据库性能产生负面影响，并浪费磁盘空间。为了使PostgreSQL始终保持在最佳性能，有必要及时对膨胀的关系进行垃圾回收，并定期重建过度膨胀的关系。 在实际操作中，垃圾回收并没有那么简单，这里有一系列的问题： 关系膨胀的原因？ 关系膨胀的度量？ 关系膨胀的监控？ 关系膨胀的处理？ 本文将详细说明这些问题。 关系膨胀概述 假设某个关系实际占用存储100G，但其中有很 …
TimescaleDB 快速上手
官方网站：https://www.timescale.com 官方文档：https://docs.timescale.com/v0.9/main Github：https://github.com/timescale/timescaledb 为什么使用TimescaleDB 什么是时间序列数据？ 我们一直在谈论什么是“时间序列数据”，以及与其他数据有何不同以及为什么？ 许多应用程序或数据库实际上采用的是过于狭窄的视图，并将时间序列数据与特定形式的服务器度量值等同起来： Name: CPU …
官方网站：https://www.timescale.com 官方文档：https://docs.timescale.com/v0.9/main Github：https://github.com/timescale/timescaledb 为什么使用TimescaleDB 什么是时间序列数据？ 我们一直在谈论什么是“时间序列数据”，以及与其他数据有何不同以及为什么？ 许多应用程序或数据库实际上采用的是过于狭窄的视图，并将时间序列数据与特定形式的服务器度量值等同起来： Name: CPU …
PipelineDB快速上手
PipelineDB安装与配置 PipelineDB可以直接通过官方rpm包安装。 加载PipelineDB需要添加动态链接库，在 postgresql.conf 中修改配置项并重启： shared_preload_libraries = 'pipelinedb' max_worker_processes = 128 注意如果不修改 max_worker_processes 会报错。其他配置都参照标准的PostgreSQL PipelineDB使用样例 —— 维基PV数据 -- 创建Stream …
PipelineDB安装与配置 PipelineDB可以直接通过官方rpm包安装。 加载PipelineDB需要添加动态链接库，在 postgresql.conf 中修改配置项并重启： shared_preload_libraries = 'pipelinedb' max_worker_processes = 128 注意如果不修改 max_worker_processes 会报错。其他配置都参照标准的PostgreSQL PipelineDB使用样例 —— 维基PV数据 -- 创建Stream …
故障档案：序列号消耗过快导致整型溢出
0x01 概览 故障表现： 某张使用自增列的表序列号涨至整型上限，无法写入。 发现表中的自增列存在大量空洞，很多序列号没有对应记录就被消耗掉了。 故障影响：非核心业务某表，10分钟左右无法写入。 故障原因： 内因：使用了INTEGER而不是BIGINT作为主键类型。 外因：业务方不了解 SEQUENCE 的特性，执行大量违背约束的无效插入，浪费了大量序列号。 修复方案： 紧急操作：降级线上插入函数为直接返回，避免错误扩大。 应急方案：创建临时表，生成5000万个浪费空洞中的临时ID，修改插入函 …
0x01 概览 故障表现： 某张使用自增列的表序列号涨至整型上限，无法写入。 发现表中的自增列存在大量空洞，很多序列号没有对应记录就被消耗掉了。 故障影响：非核心业务某表，10分钟左右无法写入。 故障原因： 内因：使用了INTEGER而不是BIGINT作为主键类型。 外因：业务方不了解 SEQUENCE 的特性，执行大量违背约束的无效插入，浪费了大量序列号。 修复方案： 紧急操作：降级线上插入函数为直接返回，避免错误扩大。 应急方案：创建临时表，生成5000万个浪费空洞中的临时ID，修改插入函 …
故障档案：PostgreSQL事务号回卷
遇到一次磁盘坏块导致的事务回卷故障： 主库（PostgreSQL 9.3）磁盘坏块导致几张表上的VACUUM FREEZE执行失败。 无法回收老旧事务ID，导致整库事务ID濒临用尽，数据库进入自我保护状态不可用。 磁盘坏块导致手工VACUUM抢救不可行。 提升从库后，需要紧急VACUUM FREEZE才能继续服务，进一步延长了故障时间。 主库进入保护状态后提交日志（clog）没有及时复制到从库，从库产生存疑事务拒绝服务。 摘要 这是一个即将下线老旧库，疏于管理。坏块征兆在一周前就已经出现，没有及 …
遇到一次磁盘坏块导致的事务回卷故障： 主库（PostgreSQL 9.3）磁盘坏块导致几张表上的VACUUM FREEZE执行失败。 无法回收老旧事务ID，导致整库事务ID濒临用尽，数据库进入自我保护状态不可用。 磁盘坏块导致手工VACUUM抢救不可行。 提升从库后，需要紧急VACUUM FREEZE才能继续服务，进一步延长了故障时间。 主库进入保护状态后提交日志（clog）没有及时复制到从库，从库产生存疑事务拒绝服务。 摘要 这是一个即将下线老旧库，疏于管理。坏块征兆在一周前就已经出现，没有及 …
PostgreSQL的触发器使用注意事项
概览 触发器行为概述 触发器的分类 触发器的功能 触发器的种类 触发器的触发 触发器的创建 触发器的修改 触发器的查询 触发器的性能 触发器概述 触发器行为概述：英文，中文 触发器分类 触发时机：BEFORE, AFTER, INSTEAD 触发事件：INSERT, UPDATE, DELETE,TRUNCATE 触发范围：语句级，行级 内部创建：用于约束的触发器，用户定义的触发器 触发模式：origin|local(O), replica(R),disable(D) 触发器操作 触发器的操作通 …
概览 触发器行为概述 触发器的分类 触发器的功能 触发器的种类 触发器的触发 触发器的创建 触发器的修改 触发器的查询 触发器的性能 触发器概述 触发器行为概述：英文，中文 触发器分类 触发时机：BEFORE, AFTER, INSTEAD 触发事件：INSERT, UPDATE, DELETE,TRUNCATE 触发范围：语句级，行级 内部创建：用于约束的触发器，用户定义的触发器 触发模式：origin|local(O), replica(R),disable(D) 触发器操作 触发器的操作通 …
GeoIP 地理逆查询优化
IP归属地查询的高效实现 在应用开发中，一个‘很常见’的需求就是GeoIP转换。将请求的来源IP转换为相应的地理坐标，或者行政区划（国家-省-市-县-乡-镇）。这种功能有很多用途，譬如分析网站流量的地理来源，或者干一些坏事。使用PostgreSQL可以多快好省，优雅高效地实现这一需求。 0x01 思路方法 通常网上的IP地理数据库的形式都是：start_ip, stop_ip , longitude, latitude，再缀上一些国家代码，城市代码，邮编之类的属性字段。大概长这样： …
IP归属地查询的高效实现 在应用开发中，一个‘很常见’的需求就是GeoIP转换。将请求的来源IP转换为相应的地理坐标，或者行政区划（国家-省-市-县-乡-镇）。这种功能有很多用途，譬如分析网站流量的地理来源，或者干一些坏事。使用PostgreSQL可以多快好省，优雅高效地实现这一需求。 0x01 思路方法 通常网上的IP地理数据库的形式都是：start_ip, stop_ip , longitude, latitude，再缀上一些国家代码，城市代码，邮编之类的属性字段。大概长这样： …
PostgreSQL开发规约（2018版）
微信公众号原文 0x00背景 没有规矩，不成方圆。 PostgreSQL的功能非常强大，但是要把PostgreSQL用好，需要后端、运维、DBA的协力配合。 本文针对PostgreSQL数据库原理与特性，整理了一份开发规范，希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。你好我也好，大家都好。 0x01 命名规范 无名，万物之始，有名，万物之母。 【强制】 通用命名规则 本规则适用于所有对象名，包括：库名、表名、表名、列名、函数名、视图名、序列号名、别名等。 对象名务必只使用 …
微信公众号原文 0x00背景 没有规矩，不成方圆。 PostgreSQL的功能非常强大，但是要把PostgreSQL用好，需要后端、运维、DBA的协力配合。 本文针对PostgreSQL数据库原理与特性，整理了一份开发规范，希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。你好我也好，大家都好。 0x01 命名规范 无名，万物之始，有名，万物之母。 【强制】 通用命名规则 本规则适用于所有对象名，包括：库名、表名、表名、列名、函数名、视图名、序列号名、别名等。 对象名务必只使用 …
PostgreSQL好处都有啥
PostgreSQL的Slogan是“世界上最先进的开源关系型数据库”，但我觉得这口号不够响亮，而且一看就是在怼MySQL那个“世界上最流行的开源关系型数据库”的口号，有碰瓷之嫌。要我说最能生动体现PG特色的口号应该是：一专多长的全栈数据库，一招鲜吃遍天嘛。 全栈数据库 成熟的应用可能会用到许许多多的数据组件（功能）：缓存，OLTP，OLAP/批处理/数据仓库，流处理/消息队列，搜索索引，NoSQL/文档数据库，地理数据库，空间数据库，时序数据库，图数据库。传统的架构选型呢，可能会组合使用多种组 …
PostgreSQL的Slogan是“世界上最先进的开源关系型数据库”，但我觉得这口号不够响亮，而且一看就是在怼MySQL那个“世界上最流行的开源关系型数据库”的口号，有碰瓷之嫌。要我说最能生动体现PG特色的口号应该是：一专多长的全栈数据库，一招鲜吃遍天嘛。 全栈数据库 成熟的应用可能会用到许许多多的数据组件（功能）：缓存，OLTP，OLAP/批处理/数据仓库，流处理/消息队列，搜索索引，NoSQL/文档数据库，地理数据库，空间数据库，时序数据库，图数据库。传统的架构选型呢，可能会组合使用多种组 …
PostGIS高效解决行政区划归属查询
微信公众号原文 在应用开发中，很多时候我们需要解决这样一个问题：根据用户的经纬度坐标，定位用户的行政区划。 我们收集到的是诸如 28°00'00"N 100°00'00.000"E 这样的经纬度坐标，但实际感兴趣的是这个点所属的行政区划：（中华人民共和国，云南省，迪庆藏族自治州，香格里拉市）。这种将地理坐标映射到某条记录的操作就称为 地理编码（GeoEncode）。高效实现地理编码是一个很有趣的问题。 本文介绍了该问题的解决与优化方案：能在确保正确性的前提下，能用几兆的空间，110μs的执行时 …
微信公众号原文 在应用开发中，很多时候我们需要解决这样一个问题：根据用户的经纬度坐标，定位用户的行政区划。 我们收集到的是诸如 28°00'00"N 100°00'00.000"E 这样的经纬度坐标，但实际感兴趣的是这个点所属的行政区划：（中华人民共和国，云南省，迪庆藏族自治州，香格里拉市）。这种将地理坐标映射到某条记录的操作就称为 地理编码（GeoEncode）。高效实现地理编码是一个很有趣的问题。 本文介绍了该问题的解决与优化方案：能在确保正确性的前提下，能用几兆的空间，110μs的执行时 …
KNN极致优化：从RDS到PostGIS
灵活应用数据库的功能，可以轻松实现 GIS 圈选场景下三万倍的性能提升。 Level 方法 性能/耗时(ms) 可维护性/可靠性 备注 1 暴力扫表 30,000 - 形式简单 2 经纬索引 35 复杂度/魔数问题 额外复杂度 3 联合索引 10 复杂度/魔数问题 额外复杂度 4 GIST 4 最简表达，完全精确 形式简单，距离更精确，PostgreSQL限定 5 btree_gist 联合索引 1 最简表达，完全精确 形式简单，距离更精确，PostgreSQL限定 场景 互联网中的很多业务都涉 …
灵活应用数据库的功能，可以轻松实现 GIS 圈选场景下三万倍的性能提升。 Level 方法 性能/耗时(ms) 可维护性/可靠性 备注 1 暴力扫表 30,000 - 形式简单 2 经纬索引 35 复杂度/魔数问题 额外复杂度 3 联合索引 10 复杂度/魔数问题 额外复杂度 4 GIST 4 最简表达，完全精确 形式简单，距离更精确，PostgreSQL限定 5 btree_gist 联合索引 1 最简表达，完全精确 形式简单，距离更精确，PostgreSQL限定 场景 互联网中的很多业务都涉 …
监控PG中的表大小
表的空间布局 宽泛意义上的 表（Table），包含了 本体表 与 TOAST表 两个部分： 本体表，存储关系本身的数据，即狭义的关系，relkind='r'。 TOAST表，与本体表一一对应，存储过大的字段，relinkd='t'。 而每个表，又由 主体 与 索引 两个 关系（Relation） 组成（对本体表而言，可以没有索引关系） 主体关系：存储元组。 索引关系：存储索引元组。 每个 关系 又可能会有 四种分支： main: 关系的主文件，编号为0 fsm：保存关于main分支中空闲空间的信 …
表的空间布局 宽泛意义上的 表（Table），包含了 本体表 与 TOAST表 两个部分： 本体表，存储关系本身的数据，即狭义的关系，relkind='r'。 TOAST表，与本体表一一对应，存储过大的字段，relinkd='t'。 而每个表，又由 主体 与 索引 两个 关系（Relation） 组成（对本体表而言，可以没有索引关系） 主体关系：存储元组。 索引关系：存储索引元组。 每个 关系 又可能会有 四种分支： main: 关系的主文件，编号为0 fsm：保存关于main分支中空闲空间的信 …
PgAdmin安装配置
PgAdmin4的安装与配置 PgAdmin是一个为PostgreSQL定制设计的GUI。用起来很不错。可以以本地GUI程序或者Web服务的方式运行。因为Retina屏幕下面PgAdmin依赖的GUI组件显示效果有点问题，这里主要介绍如何以Web服务方式（Python Flask）配置运行PgAdmin4。 下载 PgAdmin可以从官方FTP下载。 postgresql网站FTP目录地址 wget …
PgAdmin4的安装与配置 PgAdmin是一个为PostgreSQL定制设计的GUI。用起来很不错。可以以本地GUI程序或者Web服务的方式运行。因为Retina屏幕下面PgAdmin依赖的GUI组件显示效果有点问题，这里主要介绍如何以Web服务方式（Python Flask）配置运行PgAdmin4。 下载 PgAdmin可以从官方FTP下载。 postgresql网站FTP目录地址 wget …
故障档案：快慢不匀雪崩
最近发生了一起匪夷所思的故障，某数据库切走了一半的数据量和负载。 其他什么都没变，本来还好；压力减小，却在高峰期陷入濒死状态，完全不符合直觉。 但正如福尔摩斯所说，当你排除掉一切不可能之后，剩下的即使再离奇，也是事实。 一、摘要 某日凌晨4点，进行了核心库进行分库迁移，拆走一半的表和一半的查询负载，原库节点规模不变。 当日晚高峰核心库所有热备库（15台）出现连接堆积，压力暴涨，针对性地清理慢查询不再起效。 无差别持续杀查询，有立竿见影的救火效果（22:30后），且暂停后故障立刻重现 …
最近发生了一起匪夷所思的故障，某数据库切走了一半的数据量和负载。 其他什么都没变，本来还好；压力减小，却在高峰期陷入濒死状态，完全不符合直觉。 但正如福尔摩斯所说，当你排除掉一切不可能之后，剩下的即使再离奇，也是事实。 一、摘要 某日凌晨4点，进行了核心库进行分库迁移，拆走一半的表和一半的查询负载，原库节点规模不变。 当日晚高峰核心库所有热备库（15台）出现连接堆积，压力暴涨，针对性地清理慢查询不再起效。 无差别持续杀查询，有立竿见影的救火效果（22:30后），且暂停后故障立刻重现 …
Bash与psql小技巧
一些PostgreSQL与Bash交互的技巧。 使用严格模式编写Bash脚本 使用Bash严格模式，可以避免很多无谓的错误。在Bash脚本开始的地方放上这一行很有用： set -euo pipefail -e：当程序返回非0状态码时报错退出 -u：使用未初始化的变量时报错，而不是当成NULL -o pipefail：使用Pipe中出错命令的状态码（而不是最后一个）作为整个Pipe的状态码1。 执行SQL脚本的Bash包装脚本 通过psql运行SQL脚本时，我们期望有这么两个功能： 能向脚本中传入 …
一些PostgreSQL与Bash交互的技巧。 使用严格模式编写Bash脚本 使用Bash严格模式，可以避免很多无谓的错误。在Bash脚本开始的地方放上这一行很有用： set -euo pipefail -e：当程序返回非0状态码时报错退出 -u：使用未初始化的变量时报错，而不是当成NULL -o pipefail：使用Pipe中出错命令的状态码（而不是最后一个）作为整个Pipe的状态码1。 执行SQL脚本的Bash包装脚本 通过psql运行SQL脚本时，我们期望有这么两个功能： 能向脚本中传入 …