PG-X

PostgreSQL 在 2018 年首次出现在 Stack Overflow 开发者调查中，当时有 33% 的开发者报告说他们在使用它，相比之下，那一年最受欢迎的选项是 MySQL，有 59% 的开发者在使用。六年后，PostgreSQL 被 49% 的开发者使用，并且连续第二年成为最受欢迎的数据库。 https://survey.stackoverflow.co/2024/technology#most-popular-technologies-database 在所有的问卷中 48.7% 的开发者反馈在使用 PostgreSQL​，这一比例超过了 MySQL 的 40.3%。 在专业的开发者中，PostgreSQL 的使用比例超过了一半。 不过在学习编程维度和其它开发者中，MySQL 的使用比例占据第一的位置。 报告中还有另外一个维度: Admired: 红色，代表被赞美或被钦佩的程度 Desired: 蓝色，代表期望获得这项技能的程度 PostgreSQL 依旧一骑绝尘，另外报告指出 SQLite 的期望（Desired）得分从去年的第三上升到了第二位，超过了 MySQL。 相信 PostgreSQL 会越来越好，We ❤️ PG 🎊 也祝 MySQL 越来越好​ ⛽️

万圣节问题（Halloween Problem）是数据库系统中的一个现象，它指的是当一个查询检索了一组行，然后修改了其中一行或多行，修改后的行再次满足查询的条件，进而导致在相同的更新操作中再次访问该行。在某些情况下甚至可能导致无限循环。 这个问题最初是由 Don Chamberlin、Pat Selinger 和 Morton Astrahan 在 1976 年的万圣节那天发现，当时他们正在处理的一个查询本应给那些收入低于 25,000 美元的员工加薪 10%，但执行完成后，数据库中所有员工的收入都至少达到了 25,000 美元。这是由于更新过的记录也对查询执行引擎可见，并且继续符合查询条件，记录多次匹配，每次匹配都被加薪 10%，直到它们都超过 25,000 美元。 Halloween Problem 这个名字并没有描述问题的性质，仅仅是因为它被发现的那天恰好是万圣节🎃。 Pat and Morton discovered this problem on Halloween… I remember they came into my office and said, “Chamberlin, look at this. We have to make sure that when the optimizer is making a plan for processing an update, it doesn’t use an index that is based on the field that is being updated....

Postgres 在 x86 架构下的实例是否可以通过 pg_basebackup 同步到 arm 架构？ 在一个群里看到一个问题: pg_basebackup 是对 Postgres 实例数据文件在目的端的一个精确拷贝: pg_basebackup makes an exact copy of the database cluster’s files, while making sure the server is put into and out of backup mode automatically. 所以这个问题几乎等价于: x86 架构下的 pg 数据是否可以直接用 arm 架构下的 postgres 进程来管理？ 群里的讨论中出现了两种对立的观点: 一种认为字节序、对齐(padding)会导致不同架构数据格式的不兼容 一种认为理论上数据库的文件格式可以独立于 cpu 架构，存储格式的字节序跟 cpu 字节序没有直接关系 从 Postgres 的实现来说，第一种观点是正确的；但如果抛开 PG，第二种观点我认为也是一定成立的。 先说第二种观点: 其实这种观点是有依据的，我举一个例子: Parquet 是一种跨平台的数据存储格式，可以在不同的 CPU 架构上使用。这是因为 Parquet 的设计不依赖特定的硬件架构或操作系统，而是通过定义数据的存储结构和元数据来实现数据的高效读写和处理，并提供相关的库对数据进行序列化和反序列化。PG 生态中读写 Parquet 的 FDW 正是通过这些库来实现的。...

In memory of Simon Riggs. PostgreSQL 的核心贡献者 Simon Riggs 于 2024 年 3 月 26 日驾驶私人飞机在杜克斯福德机场坠毁，并在事故中丧生。 Simon 负责过许多 PostgreSQL 的企业级特性，包括时间点恢复（PITR）、热备份（hot standby）和同步复制（synchronous replication）。同时他也是 2ndQuadrant 的创始人，该公司雇佣了许多 PostgreSQL 的开发人员，并后来成为 EDB 的一部分，Simon 在 EDB 担任 Postgres Fellow 直到退休。 为了让更多的人了解或记住 Simon Riggs，这里分享一个 Josh Berkus 讲述的关于 Simon Riggs 有趣的故事。 – Start of the story – 2006~2008 期间，Josh Berkus 和 Simon Riggs 在 Greenplum 共事，一起合作开发 Greenplum 的资源管理系统。有一次，Simon Riggs 飞往加州的 San Rafael，在抵达的第一个晚上，合作伙伴们带他们去了一家冒充法式小酒馆的连锁餐厅，餐厅里有一个龙舌兰酒吧台，这是 Simon 第一次尝试龙舌兰（Josh 印象中 Simon 不太喜欢）。...

PostgreSQL 17 对 COPY 命令做了一个优化，导入数据（COPY FROM）时可以忽略错误字段进而使得 COPY 命令继续导入后面的数据。虽然是很小的一个特性，但非常实用。 cat << EOF > /tmp/malformed_data.txt 1 {1} 1 a {2} 2 3 {3} 3333333333 4 {a, 4} 4 5 {5} 5 EOF 之前在用 COPY 导入数据时，如果文件中有不符合类型的字段，命令直接报错退出: postgres=# CREATE TABLE check_ign_err (n int, m int[], k int); CREATE TABLE postgres=# copy check_ign_err from '/tmp/malformed_data.txt'; ERROR: invalid input syntax for type integer: " a" CONTEXT: COPY check_ign_err, line 2, column n: " a" postgres=# table check_ign_err; n | m | k ---+---+--- (0 rows) PostgreSQL 17 增加了如下语法:...

PostgreSQL 17 的一个重要特性是在内核层面增加了增量备份（Incremental Backup）的特性，为用户提供更灵活和高效的备份解决方案。 该特性由 EDB 的首席数据库架构师 Robert Haas 实现，详细讨论见: trying again to get incremental backup Why Incremental Backup? PostgreSQL 通过全量备份和 WAL archiving 能够实现 PITR(Point-In-Time Recovery)，从功能完备性角度来讲，也许并不需要增量备份。但对一个 10T 数据量、读多写少的实例，每周（月）进行一次全量备份可能太过繁重。 增量备份则仅备份自上次备份（可以是全量备份或增量备份）以来修改过的文件页面（8K Block），对于数据量大且修改频率较低的数据库实例，增量备份有以下优势： 快速备份：由于增量备份只包含自上次备份以来的变化部分，因此备份速度更快。相比全量备份，增量备份需要拷贝的数据量更小，能够更快地完成备份过程。 空间效率：由于增量备份仅存储自上次备份以来的更改部分，所需的磁盘空间较少，节省存储成本。 快速恢复：在数据丢失或灾难恢复的情况下，从全量+增量备份进行恢复通常比从全量备份+WAL进行恢复更快。将最近的全量备份和后续的增量备份进行合并，相比回放大量的 WAL 日志，恢复时间更短，可以快速使系统恢复正常运行。 EDB 工程师 Jakub Wartak 进行的测试证明了增量备份的必要性: 对一个初始数据量 3.5G 的实例用 pg_bench 持续写入，24 小时生成的 WAL 数据量 77GB，最后一次的增量备份数据量 3.5GB，相对最终的总数据量 4.3GB 差距不明显，说明大部分页面有过改动。 全量+增量备份 恢复的速度（4min18s）却远快于全量备份+WAL（78min）。 当然，增量备份也并非银弹，增量备份文档中有这样一段描述: Incremental backups typically only make sense for relatively large databases where a significant portion of the data does not change, or only changes slowly....

PostgreSQL 中常用的三个 timeout 参数有: statement_timeout、idle_in_transaction_session_timeout 和 idle_session_timeout，它们控制的时间范围如下图所示: statement_timeout 用于限制单个 query 执行的时间 idle_in_transaction_session_timeout 用于限制在事务中闲置的时长 idle_session_timeout 用于限制连接处于空间的时长 前两个参数很早之前就存在了，idle_session_timeout 则是由 PG 14 引入。 PG 14 之前，过多的连接会导致 Snapshot scalability 的问题，当然这包含 idle 连接。PG 14 对此进行了优化，处于 idle 状态的连接不会对性能有太多的影响。 但即使不影响性能，在 pg_stat_activity 视图中看到太多的空闲连接也足够让人恼火，于是中国的 PG 贡献者 Japin Li 提议增加一个 idle_session_timeout，让 DBA 自己控制是否需要自动关闭超时的空闲连接，于是在 PG 14 引入了这个 GUC。 但这三个参数控制不了事务的执行时长，比如下面的查询: postgres=# begin; select pg_sleep(1); select pg_sleep(1); select pg_sleep(1); select pg_sleep(1); commit; BEGIN pg_sleep ---------- (1 row) pg_sleep ---------- (1 row) pg_sleep ---------- (1 row) pg_sleep ---------- (1 row) COMMIT 2022 年末，Andrey Borodin（Yandex Cloud RDS 负责人）发起了一个讨论：增加一个 transaction_timeout 参数来限制单个事务的执行时长，设置该参数后的执行效果为:...

PGConf Europe 是欧洲最大的 PG 会议，PGConf.EU 2023 于 12 月 12-15 日在捷克首都布拉格举办。本文摘录一些我感兴趣的议题。 会议的时间表: https://www.postgresql.eu/events/pgconfeu2023/schedule/ General Elephant in a nutshell - Navigating the Postgres community 101 介绍 PostgreSQL 社区的运作机制 Speaker: Valeria Kaplan Slides: PGconf.EU. 2023_Valeria’s talk - Elephant in a nutshell.pdf DBA Getting the most out of pg_stat_io PG16 引入了 pg_stat_io 视图，提供了很多 I/O 相关的细节，包括扩展、读取和写入，以及与缓冲区缓存相关的统计信息。 Speaker: Daniel Westermann Slides: PGCONFEU-pg_stat_io.pdf Performance tricks you have never seen before-V2 Speaker: Hans-Jürgen Schönig Slides: Performance tips you have never seen before - V2 (1)....