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

Source: https://aiven.io/developer/postgresql-jsonb-cheatsheet

从 9.0 开始，PostgreSQL 基金会为每个发布版本创作一个 Release Artwork，从版本 12 开始，PostgreSQL 基金会把这个 Artwork 制作成 challenge coin 送给该版本的所有贡献者留作纪念。 今年的艺术品名称是 a baker making cloud cakes with jays on (pun) the apron，即一个穿着印有松鸟围裙的面包师傅制作云朵蛋糕。 PostgreSQL 选择大象作为 Logo 是因为 “elephants can remember”，所以每个 Artwork 也都以大象为主题。 对于一个开源项目，这种文化非常值得赞扬。通过向贡献者赠送一个小而用心的礼物，让他们感受到强烈的归属感。这种举动表达了对他们的感谢，同时也激励他们继续为项目做出贡献。

I know it seems dumb, but postgres really needs to add the simple developer experience stuff like: SHOW CREATE TABLE; SHOW TABLES; SHOW DATABASES; SHOW PROCESSLIST; CockroachDB added these aliases ages ago. 如上是 Hacker News 今年 5 月的一个帖子，相信 PostgreSQL 的用户都知道，PostgreSQL has its own way!!! 是的，我们用 slash commands，并且热衷这种简洁的方式: MySQL PG show create table t; \d+ t show tables; \d show tables like "%test%" \dt public.*test* show databases; \l show processlist; SELECT * from pg_stat_activity; 不过 slash commands 只能在 psql 中使用，psql 将相应的命令转换成对应的 SQL 查询发送到 server 端处理。大多情况下，这种方式能满足用户的需求，但也有例外，比如:...

PostgreSQL 的多进程架构相对于多线程架构有什么优势？如果你认同 “一个查询的后端进程崩溃不会影响其它连接的后端进程，从而提高了系统稳定性” 是其中一个优势，那请继续看下去 🐘。 postmaster 状态机 postmaster 作为 PostgreSQL 的主进程，在启动时负责创建共享内存和信号量。然而，它自身几乎不直接操作这些资源（当然也有例外，如 PMSignalState），这样的设计使得 postmaster 逻辑更简单、更可靠。当后端进程崩溃时，postmaster 通过重置共享内存和信号量来恢复系统的正常运行。 当 postmaster 接收到客户端请求时，它会立即派生（fork）一个子进程。子进程负责对请求进行权限验证，如果验证成功，则成为一个后端进程，这种设计保证了客户端请求的高效处理。 postmaster 负责启动一系列子进程，包括辅助（Auxilary）进程和普通（Normal）后端进程，其中 startup 进程跟 postmaster 紧密合作来保证数据库的一致性，它调用 StartupXLOG 执行数据库恢复的流程（本文不展开 recovery 的流程）。 postmaster 被实现为一个状态机，父子进程之间通过信号（signal）进行交互，如下是 postmaster 的状态图: postmaster 启动后初始状态为 PM_INIT，完成一些初始化工作后，启动 startup 进程，postmaster 进入 PM_STARTUP 状态 如果 startup 进程完成其工作并正常退出（OS 给 postmaster 发送 SIGCHLD 信号），postmaster 进入 PM_RUN 状态 startup 进程在进行到可以开始 archive recovery 时，给 postmaster 发信号并在共享内存标记 PMSIGNAL_RECOVERY_STARTED，postmaster 处理后进入 PM_RECOVERY 状态 startup 持续重放 wal 日志，如果开启了 Hot Standby，当达到一致状态后，会标记 PMSIGNAL_BEGIN_HOT_STANDBY，postmaster 进入 PM_HOT_STANDBY 状态 postmaster 在 PM_RUN 和 PM_HOT_STANDBY 状态都可以接收请求连接，因此这里把两个状态放在了一起 当有后端进程异常退出后，postmaster 进入 PM_WAIT_BACKENDS 状态，并调用 HandleChildCrash 来通知其它进程退出 所有进程退出后，postmaster 进入 PM_NO_CHILDREN 状态，然后重建共享内存和信号量并启动 startup 进程，postmaster 状态改为 PM_STARTUP 可以看出，一个后端进程的异常退出会影响其它子进程。一个简单的验证方法是使用 kill -9 去杀死一个查询的后端进程，并查看其它后端进程的 pid 是否改变，参考 KILL -9 EXPLAINED FOR POSTGRESQL。...