PG-X

A Thriving PostgreSQL Ecosystem.

  • 分享 PostgreSQL 使用技巧
  • 分享 PostgreSQL 实用插件
  • 分享有关 PostgreSQL 的一切

PostgreSQL COPY ON_ERROR

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 增加了如下语法:...

January 20, 2024 · 1 min · 204 words · pg-x

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

January 20, 2024 · 10 min · 2040 words · pg-x

PostgreSQL 有必要增加一个 transaction_timeout 参数吗?

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 参数来限制单个事务的执行时长,设置该参数后的执行效果为:...

December 29, 2023 · 1 min · 171 words · pg-x

PGConf.EU 2023 Digest

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

December 24, 2023 · 3 min · 539 words · pg-x

PostgreSQL 生成随机 bigint

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() 函数进行拼接,此不详述。...

December 23, 2023 · 2 min · 332 words · pg-x

PostgreSQL 中的 HyperLogLog

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

December 17, 2023 · 3 min · 468 words · pg-x

PostgreSQL 引入 UUIDv7

UUID (Universally Unique IDentifier) 在 RFC4122 中定义,占用 16 字节空间,为方便阅读,通常将其表示为由-分隔的五组十六进制字符串。 -- PostgreSQL 13 增加了内置生成随机 UUID 的方法 postgres=# select gen_random_uuid(); gen_random_uuid -------------------------------------- e6e12d78-8b1b-4c5c-90d2-e07dcbdbee73 (1 row) RFC4122 定义了 5 个不同版本的 UUID,这些版本之间的区别在于生成 UUID 所需的输入和输出的位结构,当人们讨论 UUID 时,几乎总是指 UUIDv4: 其结构中的第 7 个字节的高四位为版本号 0b0100, 第 9 个字节的高两位总是为 0b10,其余 122 位为随机值。因此无论用 SQL 还是命令行工具 uuidgen 生成的 UUID,其在上图中第一个橘色的位置始终为 4,第二个橘色的位置为 8, 9, a, b 中的一个。 contrib 下的 uuid-ossp extension 提供了生成五个版本 UUID 的函数。 UUIDv4 存在的问题 当用 UUIDv4 作为主键时,如果大量写入随机的 UUID 值,会导致 B-tree 更多的页面分裂,较低的 filling factor,大量的页面碎片(Fragmentation),占用更多的存储空间。当索引数据大于内存时,缓存命中率也会逐渐降低。由于写入分散,大量页面需要在 Checkpoint 之后写 FPI,进而导致 WAL 的写入放大,WAL 写入量的增加又会更频繁地触发 Checkpoint,又导致更多的 FPI 写入操作 🥺...

December 9, 2023 · 3 min · 592 words · pg-x