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

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 写入操作 🥺...

AWS re:Invent 2023 发布了 Aurora Limitless Database，看起来类似 Citus，但其底层依赖 AWS 长久依赖的技术积淀，比如分布式事务使用了类似 Spanner True Time 的 EC2 TimeSync Service，又如利用 Caspian 来动态解决不同 shard 间的数据偏移。但其技术细节不对外公布，这里收集一些公开资料来学习其内部架构。AWS 真的是把 Serverless 做到了极致 👍🏻。 AWS re:Invent 2023 - Monday Night Live Keynote with Peter DeSantis AWS re:Invent 2023 - Achieving scale with Amazon Aurora Limitless Database Join the preview of Amazon Aurora Limitless Database Hacker News