PG-X

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)....

PostgreSQL 的 random() 函数用于生成一个随机的双精度浮点数，可以将其转换为较小的随机整数，但是双精度浮点数无法表示 bigint 中的所有值1，本文介绍几种 PostgreSQL 生成 bigint 随机数的方法。 UUID 我们可以提取 gen_random_uuid 生成的随机位将其转换为 bigint，如: postgres=# select ('x'||right(uuid_send(gen_random_uuid())::text, 16))::bit(64)::int8; int8 ---------------------- -8048312917378578936 (1 row) 但了解 UUIDv4 的同学一眼就能看出问题，虽然 UUIDv4 中的 122 位都是随机数，但上述方法使用的后 64 位中的前两位一定是 0b10（参考: PostgreSQL 引入 UUIDv7），因此上述方法生成的随机 bigint 一定是个负数。所以我们需要从 UUIDv4 中截取两段数据才能生成一个随机的 bigint: postgres=# CREATE OR REPLACE FUNCTION gen_random_bigint_from_uuid() RETURNS INT8 AS $$ DECLARE bytes text; BEGIN bytes := uuid_send(gen_random_uuid())::text; RETURN ('x' || substring(bytes, 3, 2) || right(bytes, 14))::bit(64)::int8; END; $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# select gen_random_bigint_from_uuid() from generate_series(1,5); gen_random_bigint_from_uuid ----------------------------- 2561119870940783961 -8836220962687819417 -5760399065689643664 -8861236575235818608 7084792777938647791 (5 rows) 注意 UUIDv7 因为只有 62 位的随机值，因此不能直接用来生成 bigint 随机值，不过可以结合 random() 函数进行拼接，此不详述。...

HyperLogLog 是一种计算近似基数计数（cardinality counting）的算法，用于解决统计唯一元素个数的问题（DISTINCT COUNT）。计算集合的确切基数需要与基数成比例的内存量，这对超大数据集来说是不切实际的。概率基数估计在理论误差范围内计算近似基数，其使用的内存要少得多，如 HyperLogLog 算法能够用 1.5kB 的内存估计大于 10^9 量级的基数，标准误差小于 2%。 HyperLogLog 是早期 LogLog 算法的扩展，LogLog 则是基于 1984 年的 Flajolet-Martin 算法发展而来。先来简单了解一下这三种算法。 Flajolet-Martin Flajolet-Martin 算法的基本思想是利用随机化和位运算来估计唯一元素的基数。对数据集中的每个元素进行哈希映射，将元素映射为一个二进制编码。对每个哈希映射值，找到其二进制编码中从右向左的尾部连续零的个数 len，取 R 为 len 的最大值，估算基数值的公式: 2^R / ϕ (ϕ ≈ 0.77351)。 我们生成值域为 [0, 63] 之间的 8 个随机哈希值用作演示: postgres=# select (random() * 64)::int::bit(6) from generate_series(0,7) order by 1; bit -------- 000110 001001 010000 011101 011110 101010 110101 111010 (8 rows) 按照 Flajolet-Martin 算法计算估算的 Cardinality: R = 4 Cardinality: 2^4 / 0....