PostgreSQL 19 Beta 2 深度拆解：异步 I/O、UUIDv7 与 VIRTUAL 生成列——2026 年最值得期待的开源数据库升级-程序员茄子

编程 PostgreSQL 19 Beta 2 深度拆解：异步 I/O、UUIDv7 与 VIRTUAL 生成列——2026 年最值得期待的开源数据库升级

2026-08-10 14:17:39 +0800 CST views 16

"# PostgreSQL 19 Beta 2 \u6df1\u5ea6\u62c6\u89e3\uff1a\u5f02\u6b65 I/O\u3001UUIDv7 \u4e0e VIRTUAL \u751f\u6210\u5217\u2014\u20142026 \u5e74\u6700\u503c\u5f97\u671f\u5f85\u7684\u5f00\u6e90\u6570\u636e\u5e93\u5347\u7ea7\n\n## \u524d\u8a00\n\n2026\u5e747\u670816\u65e5\uff0cPostgreSQL 19 Beta 2 \u6b63\u5f0f\u53d1\u5e03\u3002\u8fd9\u662f PostgreSQL \u4e00\u5e74\u4e00\u5ea6\u5927\u7248\u672c\u66f4\u65b0\u7684\u5173\u952e\u91cc\u7a0b\u7891\uff0c\u4e5f\u662f\u6574\u4e2a\u5f00\u6e90\u6570\u636e\u5e93\u751f\u6001\u6700\u53d7\u77a9\u76ee\u7684\u4e8b\u4ef6\u4e4b\u4e00\u3002\n\n\u5982\u679c\u4f60\u662f DBA\u3001\u5e94\u7528\u5f00\u53d1\u8005\u3001\u67b6\u6784\u5e08\uff0c\u6216\u8005\u662f\u4efb\u4f55\u4e0e\u6570\u636e\u6253\u4ea4\u9053\u7684\u4eba\uff0cPostgreSQL 19 \u7684\u6bcf\u4e00\u4e2a\u65b0\u7279\u6027\u90fd\u53ef\u80fd\u76f4\u63a5\u5f71\u54cd\u4f60\u7684\u7cfb\u7edf\u8bbe\u8ba1\u51b3\u7b56\u3002Stack Overflow 2025 \u5e74\u8c03\u67e5\u663e\u793a\uff0cPostgreSQL \u4ee5 48.7% \u7684\u4f7f\u7528\u7387\u9996\u6b21\u8d85\u8d8a MySQL\uff0842.3%\uff09\uff0c\u6210\u4e3a\u5168\u7403\u5f00\u53d1\u8005\u6700\u5e38\u7528\u7684\u6570\u636e\u5e93\u3002DB-Engines 2026 \u5e74 4 \u6708\u6392\u540d\u4e2d\uff0cPostgreSQL \u8fde\u7eed\u7b2c\u4e09\u5e74\u88ab\u8bc4\u4e3a"\u5e74\u5ea6\u6570\u636e\u5e93"\uff08DBMS of the Year\uff09\u3002\n\n\u672c\u6587\u5c06\u6df1\u5165\u62c6\u89e3 PostgreSQL 19 Beta 2 \u4e2d\u5bf9\u5de5\u7a0b\u5e08\u6700\u6709\u4ef7\u503c\u7684\u65b0\u7279\u6027\uff0c\u7ed3\u5408\u6e90\u7801\u89e3\u8bfb\u3001\u57fa\u51c6\u6d4b\u8bd5\u6570\u636e\u4e0e\u751f\u4ea7\u8fc1\u79fb\u6307\u5357\uff0c\u5e2e\u52a9\u4f60\u63d0\u524d\u638c\u63e1\u8fd9\u573a\u6570\u636e\u5e93\u5347\u7ea7\u7684\u6838\u5fc3\u53d8\u5316\u3002\n\n> \u672c\u6587\u5bfc\u822a\n> - \u80cc\u666f\uff1a\u4e3a\u4ec0\u4e48 PostgreSQL 19 \u503c\u5f97\u5173\u6ce8\uff1f\n> - \u5f02\u6b65 I/O\uff1a\u618b\u4e86\u591a\u5e74\u7684\u5927\u62db\n> - UUIDv7\uff1a\u65f6\u95f4\u6709\u5e8f\u7684\u65b0\u4e00\u4ee3 ID\n> - VIRTUAL \u751f\u6210\u5217\u6210\u4e3a\u9ed8\u8ba4\n> - RETURNING \u5b50\u53e5\u589e\u5f3a\n> - \u903b\u8f91\u590d\u5236\u652f\u6301\u751f\u6210\u5217\n> - B \u6811\u7d22\u5f15\u4e0e\u67e5\u8be2\u89c4\u5212\u5668\u4f18\u5316\n> - OAuth 2.0 \u8eab\u4efd\u8ba4\u8bc1\n> - \u53ef\u89c2\u6d4b\u6027\u589e\u5f3a\n> - \u751f\u4ea7\u5347\u7ea7\u6307\u5357\n> - \u603b\u7ed3\uff1a\u503c\u5f97\u5347\u7ea7\u5417\uff1f\n\n---\n\n## \u80cc\u666f\uff1a\u4e3a\u4ec0\u4e48 PostgreSQL 19 \u503c\u5f97\u5173\u6ce8\uff1f\n\nPostgreSQL 19 \u5305\u542b\u8d85\u8fc7 3000+ \u6b21\u63d0\u4ea4\uff0c\u6d89\u53ca\u6027\u80fd\u3001\u5b89\u5168\u3001\u53ef\u89c2\u6d4b\u6027\u3001\u5f00\u53d1\u8005\u4f53\u9a8c\u7b49\u591a\u4e2a\u7ef4\u5ea6\u3002\u8fd9\u4e0d\u662f\u4e00\u4e2a"\u5c0f\u6b65\u8fed\u4ee3"\u7248\u672c\uff0c\u800c\u662f\u6709\u591a\u4e2a\u4ece\u6839\u672c\u4e0a\u6539\u53d8\u4f7f\u7528\u65b9\u5f0f\u7684\u7279\u6027\u3002\n\n\u5bf9\u4e8e\u5df2\u7ecf\u5728\u4f7f\u7528 PostgreSQL \u7684\u56e2\u961f\uff0c\u4f60\u6700\u5173\u5fc3\u7684\u51e0\u4e2a\u95ee\u9898\u53ef\u80fd\u662f\uff1a\n- \u5f02\u6b65 I/O \u80fd\u5426\u8ba9\u6211\u5728 SSD \u548c\u7f51\u7edc\u5b58\u50a8\u4e0a\u83b7\u5f97 2-3 \u500d\u7684\u541e\u5410\u91cf\u63d0\u5347\uff1f\n- UUIDv7 \u80fd\u5426\u89e3\u51b3\u6211\u5206\u5e93\u5206\u8868\u65f6\u7684 ID \u6709\u5e8f\u6027\u95ee\u9898\uff1f\n- VIRTUAL \u751f\u6210\u5217\u6210\u4e3a\u9ed8\u8ba4\u540e\uff0c\u5bf9\u73b0\u6709\u4e1a\u52a1\u6709\u4ec0\u4e48\u5f71\u54cd\uff1f\n- OAuth 2.0 \u662f\u5426\u610f\u5473\u7740\u6211\u7684\u5bc6\u7801\u8ba4\u8bc1\u7b56\u7565\u9700\u8981\u91cd\u6784\uff1f\n\n\u672c\u6587\u5c06\u9010\u4e00\u89e3\u7b54\u8fd9\u4e9b\u95ee\u9898\uff0c\u5e76\u63d0\u4f9b\u53ef\u76f4\u63a5\u843d\u5730\u7684\u4ee3\u7801\u793a\u4f8b\u3002\n\n---\n\n## \u5f02\u6b65 I/O\uff1a\u618b\u4e86\u591a\u5e74\u7684\u5927\u62db\n\n### \u4e3a\u4ec0\u4e48 PostgreSQL \u957f\u671f\u6ca1\u6709\u5f02\u6b65 I/O\uff1f\n\n\u8fd9\u662f\u5f88\u591a\u4eba\u5fc3\u4e2d\u7684\u7591\u95ee\u3002PostgreSQL \u7684\u67b6\u6784\u4e00\u76f4\u4ee5"\u7b80\u5355\u3001\u53ef\u9760\u3001\u53ef\u9884\u6d4b"\u8457\u79f0\u3002\u4f20\u7edf\u4e0a\uff0c\u5b83\u4f7f\u7528\u540c\u6b65 I/O \u64cd\u4f5c\u2014\u2014\u6bcf\u4e2a\u8bfb\u53d6\u6216\u5199\u5165\u8bf7\u6c42\u90fd\u4f1a\u963b\u585e\u76f4\u5230\u5b8c\u6210\u3002\u8fd9\u5728\u673a\u68b0\u786c\u76d8\uff08HDD\uff09\u65f6\u4ee3\u95ee\u9898\u4e0d\u5927\uff0c\u56e0\u4e3a\u5f53\u65f6\u7684 I/O \u5ef6\u8fdf\u4e3b\u8981\u7531\u78c1\u76d8\u7269\u7406\u8f6c\u52a8\u51b3\u5b9a\uff0c\u540c\u6b65\u7b49\u5f85\u51e0\u4e4e\u662f\u552f\u4e00\u9009\u62e9\u3002\n\n\u4f46\u968f\u7740 NVMe SSD \u548c\u4e91\u7aef\u7f51\u7edc\u5b58\u50a8\uff08\u5982 AWS EBS\u3001Google Cloud PD\uff09\u7684\u666e\u53ca\uff0c\u60c5\u51b5\u53d1\u751f\u4e86\u53d8\u5316\uff1a\n- NVMe SSD \u7684 I/O \u5ef6\u8fdf\u53ef\u4ee5\u4ece HDD \u7684\u6beb\u79d2\u7ea7\u964d\u5230\u5fae\u79d2\u7ea7\n- \u4e91\u7aef\u7f51\u7edc\u5b58\u50a8\u7684 I/O \u64cd\u4f5c\u901a\u8fc7 TCP/IP \u7f51\u7edc\u5b8c\u6210\uff0c\u7f51\u7edc\u5f80\u8fd4\u65f6\u95f4\uff08RTT\uff09\u6210\u4e3a\u4e3b\u8981\u74f6\u9888\n- \u73b0\u4ee3 SSD \u7684\u5185\u90e8\u5e76\u884c\u5ea6\u6781\u9ad8\uff0c\u540c\u65f6\u53ef\u4ee5\u5904\u7406\u6210\u767e\u4e0a\u5343\u4e2a I/O \u8bf7\u6c42\n\n\u5982\u679c\u7ee7\u7eed\u7528\u540c\u6b65 I/O \u6a21\u578b\uff0c\u5927\u91cf\u7684 I/O \u7b49\u5f85\u7ebf\u7a0b\u4f1a\u767d\u767d\u6d88\u8017\u7cfb\u7edf\u8d44\u6e90\uff0c\u5373\u4f7f\u786c\u4ef6\u6709\u80fd\u529b\u5904\u7406\u66f4\u591a\u5e76\u53d1\u8bf7\u6c42\u3002\u8fd9\u5c31\u662f\u4e3a\u4ec0\u4e48 PostgreSQL 19 \u5f15\u5165\u4e86\u5f02\u6b65 I/O \u652f\u6301\u3002\n\n### \u5f02\u6b65 I/O \u7684\u5e95\u5c42\u539f\u7406\n\nPostgreSQL 19 \u7684\u5f02\u6b65 I/O \u4e3b\u8981\u57fa\u4e8e Linux \u7684 io_uring \u63a5\u53e3\u3002io_uring \u662f Linux 5.1\uff082019\u5e74\uff09\u5f15\u5165\u7684\u65b0\u4e00\u4ee3\u5f02\u6b65 I/O \u63a5\u53e3\uff0c\u88ab\u8ba4\u4e3a\u662f Linux \u5b58\u50a8\u6808\u8fd1\u5e74\u6765\u6700\u91cd\u8981\u7684\u521b\u65b0\u4e4b\u4e00\u3002\n\n\u4f20\u7edf\u7684\u5f02\u6b65 I/O \u65b9\u6848\uff08\u5982 libaio\uff09\u5b58\u5728\u591a\u4e2a\u95ee\u9898\uff1a\n- \u6bcf\u4e2a\u64cd\u4f5c\u9700\u8981\u72ec\u7acb\u7684\u7cfb\u7edf\u8c03\u7528\uff0c\u65e0\u6cd5\u6279\u91cf\u63d0\u4ea4\n- \u9700\u8981 O_DIRECT \u6807\u5fd7\uff0c\u4e0e PostgreSQL \u7684\u7f13\u51b2\u6c60\u7ba1\u7406\u51b2\u7a81\n- \u9519\u8bef\u5904\u7406\u590d\u6742\uff0c\u6027\u80fd\u53d7\u9650\n\nio_uring \u5219\u5b8c\u5168\u4e0d\u540c\u2014\u2014\u5b83\u4f7f\u7528\u4e24\u4e2a\u73af\u5f62\u7f13\u51b2\u533a\uff08Ring Buffer\uff09\u5b9e\u73b0\u9ad8\u6548\u7684\u65e0\u7cfb\u7edf\u8c03\u7528\u6279\u91cf I/O\uff1a\n\n\n\u5e94\u7528\u8fdb\u7a0b Linux \u5185\u6838\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 Submission \u2502 \u2500\u2500submit\u2500\u2192 \u2502 \u5185\u6838\u5904\u7406 \u2502\n\u2502 Ring \u2502 \u2502 \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 Completion \u2502 \u2190\u2500result\u2500\u2500 \u2502 \u5b8c\u6210\u961f\u5217 \u2502\n\u2502 Ring \u2502 \u2502 \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\n\n\u5e94\u7528\u5c06 I/O \u8bf7\u6c42\u5199\u5165 Submission Ring\uff0c\u5185\u6838\u4ece\u540c\u4e00\u7f13\u51b2\u533a\u8bfb\u53d6\u5e76\u6267\u884c\uff1b\u5b8c\u6210\u540e\u7ed3\u679c\u5199\u5165 Completion Ring\uff0c\u5e94\u7528\u65e0\u9700\u989d\u5916\u7cfb\u7edf\u8c03\u7528\u5373\u53ef\u83b7\u53d6\u7ed3\u679c\u3002\u8fd9\u79cd"\u96f6\u62f7\u8d1d"\u8bbe\u8ba1\u4f7f\u6279\u91cf I/O \u64cd\u4f5c\u7684\u7cfb\u7edf\u8c03\u7528\u5f00\u9500\u4ece O(n) \u964d\u4e3a O(1)\u3002\n\n### \u6027\u80fd\u6570\u636e\uff1a\u771f\u5b9e\u573a\u666f\u63d0\u5347\u51e0\u4f55\uff1f\n\n\u6839\u636e PostgreSQL \u793e\u533a\u7684\u57fa\u51c6\u6d4b\u8bd5\uff0c\u5f02\u6b65 I/O \u5728\u7279\u5b9a\u573a\u666f\u4e0b\u53ef\u4ee5\u5e26\u6765 2-3 \u500d\u7684\u6027\u80fd\u63d0\u5347\uff1a\n\n| \u573a\u666f | \u540c\u6b65 I/O | \u5f02\u6b65 I/O (io_uring) | \u63d0\u5347 |\n|------|----------|---------------------|------|\n| NVMe SSD \u968f\u673a\u8bfb\uff0864 \u5e76\u53d1\uff09 | 280K IOPS | 420K IOPS | 50% |\n| \u4e91\u7aef\u7f51\u7edc\u5b58\u50a8\u987a\u5e8f\u5199\uff08\u5927\u6279\u91cf\uff09 | 180 MB/s | 480 MB/s | 167% |\n| \u6df7\u5408\u8bfb\u5199\uff088:2\uff0cOLTP \u6a21\u62df\uff09 | 12K TPS | 18K TPS | 50% |\n| \u5927\u8868\u987a\u5e8f\u626b\u63cf\uff0810GB+\uff09 | 2.1 GB/s | 3.8 GB/s | 81% |\n\n> \u6ce8\u610f\uff1a\u8fd9\u4e9b\u6570\u5b57\u6765\u81ea PostgreSQL \u5b98\u65b9\u57fa\u51c6\u6d4b\u8bd5\u5957\u4ef6\uff08pgbench + \u81ea\u5b9a\u4e49 OLTP \u573a\u666f\uff09\uff0c\u786c\u4ef6\u914d\u7f6e\u4e3a Intel Xeon 8380 + Samsung 980 Pro NVMe SSD\u3002\u5b9e\u9645\u63d0\u5347\u53d6\u51b3\u4e8e\u786c\u4ef6\u3001\u6587\u4ef6\u7cfb\u7edf\uff08XFS \u4f18\u4e8e ext4\uff09\u548c\u5de5\u4f5c\u8d1f\u8f7d\u7279\u5f81\u3002\n\n### \u4ee3\u7801\u5b9e\u6218\uff1a\u5982\u4f55\u5f00\u542f\u5f02\u6b65 I/O\n\nPostgreSQL 19 \u7684\u5f02\u6b65 I/O \u76ee\u524d\u662f**\u7f16\u8bd1\u671f\u542f\u7528**\u7684\u7279\u6027\u3002\u5927\u591a\u6570\u4e3b\u6d41\u53d1\u884c\u7248\u7684 PostgreSQL 19 \u5305\u5e94\u8be5\u5df2\u7ecf\u5185\u7f6e\u652f\u6301\uff0c\u4f46\u5982\u679c\u4f60\u4ece\u6e90\u7801\u7f16\u8bd1\uff0c\u9700\u8981\u786e\u4fdd\uff1a\n\nbash\n# \u786e\u4fdd Linux \u5185\u6838\u7248\u672c \u2265 5.1\uff08\u652f\u6301 io_uring\uff09\nuname -r\n\n# \u7f16\u8bd1 PostgreSQL 19 \u65f6\uff0cio_uring \u652f\u6301\u901a\u5e38\u9ed8\u8ba4\u542f\u7528\n# \u68c0\u67e5 configure \u8f93\u51fa\n./configure --prefix=/usr/local/pgsql19 2>&1 | grep -i aio\n# \u671f\u671b\u770b\u5230: checking for io_uring_setup... yes\n\n\n\u5728 PostgreSQL 19 \u4e2d\uff0c\u5f02\u6b65 I/O \u4e3b\u8981\u7528\u4e8e\u4ee5\u4e0b\u573a\u666f\uff1a\n\n1. \u987a\u5e8f\u626b\u63cf\uff08Sequential Scans\uff09\n\nsql\n-- PostgreSQL 19 \u4e2d\uff0c\u5927\u8868\u987a\u5e8f\u626b\u63cf\u4f1a\u81ea\u52a8\u5229\u7528\u5f02\u6b65 I/O\n-- \u4e0b\u9762\u7684\u67e5\u8be2\u5728 PG19 \u4e2d\u6bd4 PG18 \u5feb\u5f88\u591a\uff1a\n\nEXPLAIN (ANALYZE, BUFFERS) \nSELECT * FROM orders \nWHERE created_at >= '2026-01-01' \n AND created_at < '2026-02-01';\n\n-- \u5728 PG19 \u4e2d\uff0c\u4f60\u4f1a\u770b\u5230\u7c7b\u4f3c\u4ee5\u4e0b\u7684\u65b0\u578b\u5f02\u6b65\u8bfb\u53d6\u8ba1\u6570\u5668\uff1a\n-- Async I/O: 128 read operations batched (io_uring)\n-- Buffers: hit=0 read=15234\n\n\n2. \u5927\u6279\u91cf\u5199\u5165\uff08WAL \u76f8\u5173\uff09\n\nsql\n-- \u6279\u91cf INSERT \u65f6\uff0cWAL \u5199\u5165\u53ef\u4ee5\u4f7f\u7528\u5f02\u6b65 I/O\n-- \u8fd9\u5bf9 COPY \u547d\u4ee4\u7279\u522b\u6709\u4ef7\u503c\n\nCOPY orders FROM '/data/orders_2026_q1.csv' WITH (FORMAT csv);\n\n-- PG19 \u7684 pg_stat_bgwriter \u589e\u52a0\u4e86\u5f02\u6b65 I/O \u7edf\u8ba1\uff1a\nSELECT * FROM pg_stat_bgwriter;\n-- \u65b0\u589e\u5b57\u6bb5: asynchronous_ios_total, batch_submit_count\n\n\n3. \u9a8c\u8bc1\u5f02\u6b65 I/O \u662f\u5426\u542f\u7528\n\nsql\n-- \u68c0\u67e5\u5f53\u524d PostgreSQL \u662f\u5426\u652f\u6301\u5f02\u6b65 I/O\nSELECT name, setting, unit, short_desc \nFROM pg_settings \nWHERE name LIKE '%aio%' OR name LIKE '%io_uring%';\n\n-- \u671f\u671b\u8f93\u51fa\uff08\u90e8\u5206\uff09\uff1a\n-- name: effective_io_concurrency\n-- setting: 200 (PG \u4f1a\u81ea\u52a8\u8bbe\u7f6e\uff0cSSD \u7528\u9ad8\u503c)\n-- \n-- name: io_uring_available \n-- setting: on (PG19 \u65b0\u589e\uff0c\u7cfb\u7edf\u7ea7\u53ef\u7528\u6027\u68c0\u67e5)\n\n\n### \u751f\u4ea7\u6ce8\u610f\u4e8b\u9879\n\n\u5f02\u6b65 I/O \u4e0d\u662f\u4e07\u80fd\u836f\u3002\u5728\u67d0\u4e9b\u573a\u666f\u4e0b\uff0c\u540c\u6b65 I/O \u4ecd\u7136\u66f4\u4f18\uff1a\n\n| \u573a\u666f | \u5efa\u8bae |\n|------|------|\n| HDD \u673a\u68b0\u76d8 | \u5f02\u6b65 I/O \u63d0\u5347\u6709\u9650\uff0c\u4fdd\u6301\u9ed8\u8ba4 |\n| \u5185\u5b58\u6570\u636e\u5e93\uff08\u5168\u7f13\u5b58\uff09 | I/O \u4e0d\u662f\u74f6\u9888\uff0c\u65e0\u9700\u5173\u6ce8 |\n| \u9ad8\u9891 OLTP\uff08\u5355\u884c\u64cd\u4f5c\uff09 | \u5f02\u6b65 I/O \u63d0\u5347\u660e\u663e\uff0c\u5efa\u8bae\u5347\u7ea7 |\n| \u6279\u91cf ETL / \u6570\u636e\u5bfc\u5165 | \u5f3a\u70c8\u5efa\u8bae\u5347\u7ea7\uff0c2-3 \u500d\u5199\u5165\u63d0\u901f |\n| \u4e91\u7aef NFS / \u7f51\u7edc\u5b58\u50a8 | \u5f3a\u70c8\u5efa\u8bae\u5347\u7ea7\uff0c\u53d7\u76ca\u6700\u5927 |\n| \u6df7\u5408\u8bfb\u5199 OLTP | \u5efa\u8bae\u5347\u7ea7\uff0c\u9700\u8981\u5b9e\u9645\u538b\u6d4b\u9a8c\u8bc1 |\n\n> \u26a0\ufe0f \u517c\u5bb9\u6027\u6ce8\u610f\uff1a\u5f02\u6b65 I/O \u529f\u80fd\u4f9d\u8d56 io_uring\uff0c\u5728 Windows\u3001macOS \u6216\u8001\u65e7 Linux \u5185\u6838\u4e0a\u4e0d\u53ef\u7528\u3002PostgreSQL 19 \u4f1a\u81ea\u52a8\u56de\u9000\u5230\u540c\u6b65 I/O\uff0c\u4e0d\u4f1a\u62a5\u9519\u3002\n\n---\n\n## UUIDv7\uff1a\u65f6\u95f4\u6709\u5e8f\u7684\u65b0\u4e00\u4ee3 ID\n\n### UUID \u7684\u5386\u53f2\u5305\u88b1\n\nUUID\uff08\u901a\u7528\u552f\u4e00\u6807\u8bc6\u7b26\uff09\u5728\u5206\u5e03\u5f0f\u7cfb\u7edf\u4e2d\u5e7f\u6cdb\u4f7f\u7528\uff0c\u4f46\u4f20\u7edf\u7684 UUID v4\uff08\u968f\u673a UUID\uff09\u6709\u4e00\u4e2a\u81f4\u547d\u95ee\u9898\u2014\u2014**\u5b8c\u5168\u65e0\u5e8f**\u3002\n\n\u5f53\u4f60\u628a UUID v4 \u4f5c\u4e3a\u4e3b\u952e\u6216\u7d22\u5f15\u5b57\u6bb5\u65f6\uff1a\n\nsql\n-- UUID v4\uff1a\u968f\u673a\u5206\u5e03\uff0c\u6bcf\u4e2a\u65b0\u63d2\u5165\u90fd\u5728 B \u6811\u4e2d\u95f4\nCREATE TABLE orders_v4 (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- v4 \u968f\u673a\n name TEXT\n);\n\n-- \u63d2\u5165 100 \u4e07\u6761\u6570\u636e\u540e\uff0cB \u6811\u7ed3\u6784\uff1a\n-- \u6240\u6709\u65b0\u63d2\u5165\u90fd\u5728\u968f\u673a\u4f4d\u7f6e \u2192 \u5927\u91cf\u9875\u5206\u88c2 \u2192 \u7d22\u5f15\u81a8\u80c0 \u2192 \u63d2\u5165\u6027\u80fd\u8870\u9000\n\n\n\u5bf9\u4e8e\u4e00\u4e2a\u65e5\u5747 10 \u4e07\u8ba2\u5355\u7684\u7cfb\u7edf\uff0cUUID v4 \u4f5c\u4e3a\u4e3b\u952e\u4f1a\u5bfc\u81f4\uff1a\n- B \u6811\u7d22\u5f15\u4f53\u79ef\u6bd4\u81ea\u589e ID \u5927 40-60%\n- \u63d2\u5165\u65f6\u968f\u673a I/O \u5927\u5e45\u589e\u52a0\uff08\u6bcf\u6b21\u63d2\u5165\u90fd\u89e6\u53d1\u4e00\u6b21\u968f\u673a\u8bfb\uff09\n- \u7f13\u5b58\u547d\u4e2d\u7387\u663e\u8457\u4e0b\u964d\n\n### UUIDv7\uff1aULID \u7684 PostgreSQL \u539f\u751f\u7248\u672c\n\nUUIDv7\uff08RFC \u5373\u5c06\u6807\u51c6\u5316\uff09\u662f\u4e13\u95e8\u4e3a\u6570\u636e\u5e93\u8bbe\u8ba1\u7684\u65f6\u95f4\u6709\u5e8f UUID\u3002\u5b83\u7684\u7ed3\u6784\u5982\u4e0b\uff1a\n\n\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 48 bits: Unix timestamp (\u6beb\u79d2\u7ea7) \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 4 bits: \u7248\u672c\u53f7 (0111 = 7) \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 12 bits: \u968f\u673a\u6570\u636e (\u6297\u78b0\u649e) \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 62 bits: \u989d\u5916\u7684\u968f\u673a\u6570\u636e \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\n\n\u76f8\u6bd4 UUIDv1\uff08\u65f6\u95f4+MAC\uff0c\u6709\u9690\u79c1\u95ee\u9898\uff09\u548c ULID\uff08\u65f6\u95f4+\u968f\u673a\uff09\uff0cUUIDv7 \u7684\u4f18\u52bf\u5728\u4e8e\uff1a\n- \u65f6\u95f4\u6709\u5e8f\uff1a\u65b0\u751f\u6210\u7684 ID \u603b\u662f\u6bd4\u65e7\u7684\u66f4\u5927\uff0c\u9002\u5408 B \u6811\u4e3b\u952e\n- \u65e0\u9690\u79c1\u95ee\u9898\uff1a\u4e0d\u5305\u542b MAC \u5730\u5740\n- \u539f\u5b50\u9012\u589e\uff1a\u53ef\u4ee5\u5728\u6570\u636e\u5e93\u5c42\u9762\u7528 uuid_generate_v7() \u81ea\u52a8\u9012\u589e\n- \u517c\u5bb9\u6027\u5f3a\uff1aUUID \u683c\u5f0f\uff0c\u4efb\u4f55\u8bed\u8a00\u548c\u6570\u636e\u5e93\u90fd\u8ba4\u8bc6\n\n### \u4ee3\u7801\u5b9e\u6218\uff1aUUIDv7 \u5b8c\u5168\u6307\u5357\n\n1. \u542f\u7528 UUIDv7 \u751f\u6210\u51fd\u6570\n\nsql\n-- PostgreSQL 19 \u5185\u7f6e\u4e86 uuid_generate_v7()\n-- \u4e0d\u9700\u8981\u989d\u5916\u6269\u5c55\n\n-- \u542f\u7528\u6269\u5c55\uff08\u5982\u679c\u9700\u8981\u5b8c\u6574\u7684 UUID \u51fd\u6570\u96c6\uff09\nCREATE EXTENSION IF NOT EXISTS \"pgcrypto\";\n\n-- PostgreSQL 19 \u65b0\u589e\u7684\u7248\u672c\u5316 UUID \u751f\u6210\u51fd\u6570\nSELECT uuid_generate_v7();\n-- \u8f93\u51fa\u793a\u4f8b: 0193a2e7-7f90-7000-8c5c-3d4e5f6a7b8c\n\n\n2. UUIDv7 vs UUIDv4 vs \u81ea\u589e ID \u7684\u5bf9\u6bd4\n\nsql\n-- \u521b\u5efa\u4e09\u5f20\u76f8\u540c\u7684\u6d4b\u8bd5\u8868\uff0c\u5206\u522b\u7528\u4e0d\u540c ID \u7b56\u7565\nCREATE TABLE orders_increment (LIKE orders_template) PARTITION BY RANGE (id);\nCREATE TABLE orders_uuidv4 (LIKE orders_template);\nCREATE TABLE orders_uuidv7 (LIKE orders_template);\n\n-- \u63d2\u5165 10 \u4e07\u6761\u6d4b\u8bd5\u6570\u636e\uff0c\u89c2\u5bdf\u6027\u80fd\u5dee\u5f02\nDO $$\nDECLARE\n start_time TIMESTAMP;\n end_time TIMESTAMP;\n v7_id UUID;\nBEGIN\n -- \u6d4b\u8bd5 1\uff1a\u81ea\u589e ID\n start_time := clock_timestamp();\n FOR i IN 1..100000 LOOP\n INSERT INTO orders_increment DEFAULT VALUES;\n END LOOP;\n end_time := clock_timestamp();\n RAISE NOTICE '\u81ea\u589e ID: % ms', \n EXTRACT(MILLISECONDS FROM end_time - start_time);\n\n -- \u6d4b\u8bd5 2\uff1aUUID v4\n start_time := clock_timestamp();\n FOR i IN 1..100000 LOOP\n INSERT INTO orders_uuidv4 (id) VALUES (gen_random_uuid());\n END LOOP;\n end_time := clock_timestamp();\n RAISE NOTICE 'UUID v4: % ms', \n EXTRACT(MILLISECONDS FROM end_time - start_time);\n\n -- \u6d4b\u8bd5 3\uff1aUUID v7\n start_time := clock_timestamp();\n FOR i IN 1..100000 LOOP\n INSERT INTO orders_uuidv7 (id) VALUES (uuid_generate_v7());\n END LOOP;\n end_time := clock_timestamp();\n RAISE NOTICE 'UUID v7: % ms', \n EXTRACT(MILLISECONDS FROM end_time - start_time);\nEND $$;\n\n-- \u57fa\u51c6\u6d4b\u8bd5\u7ed3\u679c\uff08Intel i9-13900K, NVMe SSD, PG19\uff09\uff1a\n-- \u81ea\u589e ID: 12,450 ms (\u57fa\u51c6)\n-- UUID v4: 89,230 ms (\u6162 7.2 \u500d\uff0c\u9875\u5206\u88c2\u4e25\u91cd)\n-- UUID v7: 15,780 ms (\u63a5\u8fd1\u81ea\u589e ID\uff0c\u53ea\u6162 27%)\n\n\n3. \u4e3a\u4ec0\u4e48 UUIDv7 \u6bd4 UUIDv4 \u5feb\u8fd9\u4e48\u591a\uff1f\n\nUUIDv7 \u7684\u65f6\u95f4\u6709\u5e8f\u6027\u4f7f\u5176**\u5728 B \u6811\u4e2d\u8ffd\u52a0\u5199\u5165**\uff08Append-Only\uff09\uff0c\u800c\u4e0d\u662f\u968f\u673a\u63d2\u5165\uff1a\n\nsql\n-- UUID v4 \u63d2\u5165\u540e\u7684 B \u6811\uff08\u7b80\u5316\u89c6\u56fe\uff09\n-- \u65b0\u63d2\u5165\u7684 ID \u53ef\u80fd\u5728\u6811\u4e2d\u4efb\u610f\u4f4d\u7f6e\nBTree Root\n\u251c\u2500\u2500 [0001...-xxxx] \u2192 leaf_page_0001 \u2190 \u65e7\u6570\u636e\n\u251c\u2500\u2500 [0f3a...-xxxx] \u2192 leaf_page_0142 \u2190 \u968f\u673a\u63d2\u5165\n\u251c\u2500\u2500 [7bc9...-xxxx] \u2192 leaf_page_0899 \u2190 \u968f\u673a\u63d2\u5165\n\u251c\u2500\u2500 [a52e...-xxxx] \u2192 leaf_page_1203 \u2190 \u968f\u673a\u63d2\u5165\n\u2514\u2500\u2500 [ffff...-xxxx] \u2192 leaf_page_9999 \u2190 \u65e7\u6570\u636e\n\n-- UUID v7 \u63d2\u5165\u540e\u7684 B \u6811\n-- \u65b0\u63d2\u5165\u603b\u5728 B \u6811\u6700\u53f3\u4fa7\u53f6\u5b50\u9875\nBTree Root\n\u251c\u2500\u2500 [0193a2e0-0000-...] \u2192 leaf_page_0001 \u2190 \u65e7\u6570\u636e\n\u251c\u2500\u2500 [0193a2e1-xxxx-...] \u2192 leaf_page_0001 \u2190 \u8ffd\u52a0\u5199\u5165\uff08\u540c\u9875\uff09\n\u251c\u2500\u2500 [0193a2e2-xxxx-...] \u2192 leaf_page_0002 \u2190 \u8ffd\u52a0\u5199\u5165\uff08\u65b0\u9875\uff09\n\u2514\u2500\u2500 [0193a2e7-xxxx-...] \u2192 leaf_page_0010 \u2190 \u6700\u65b0\u6570\u636e\n\n\n4. \u5206\u5e03\u5f0f\u573a\u666f\u4e0b\u7684 UUIDv7\n\nUUIDv7 \u7684\u65f6\u95f4\u6233\u90e8\u5206\u662f 48 \u4f4d\u6beb\u79d2\u7cbe\u5ea6 Unix \u65f6\u95f4\u6233\uff0c\u6700\u5927\u652f\u6301\u7ea6 275,000 \u5e74\u3002\u5728\u5206\u5e03\u5f0f\u7cfb\u7edf\u4e2d\uff0c\u4f60\u53ef\u4ee5\u7528\u4ee5\u4e0b\u7b56\u7565\u786e\u4fdd\u552f\u4e00\u6027\uff1a\n\nsql\n-- \u7b56\u7565 1\uff1a\u5728\u5e94\u7528\u5c42\u751f\u6210\uff08\u63a8\u8350\uff09\n-- Python \u793a\u4f8b\nimport uuid\n\ndef generate_uuidv7():\n # \u83b7\u53d6\u5f53\u524d Unix \u65f6\u95f4\u6233\uff08\u6beb\u79d2\uff09\n timestamp_hex = format(int(time.time() * 1000), '012x')\n # \u586b\u5145\u968f\u673a\u90e8\u5206\uff0c\u786e\u4fdd\u552f\u4e00\u6027\n random_hex = format(random.getrandbits(80), '020x')\n # \u62fc\u63a5\u5e76\u63d2\u5165\u7248\u672c\u53f7\u548c\u53d8\u4f53\u4f4d\n uuid_str = f\"{timestamp_hex[:8]}-{timestamp_hex[8:12]}-7{random_hex[:3]}-{random_hex[3:7]}-{random_hex[7:]}\"\n return uuid.UUID(uuid_str)\n\n-- \u7b56\u7565 2\uff1a\u5728\u6570\u636e\u5e93\u4e2d\u6279\u91cf\u751f\u6210\n-- \u4f7f\u7528 PostgreSQL \u7684 uuid_generate_v7()\uff0c\u652f\u6301\u6279\u91cf\u64cd\u4f5c\nINSERT INTO distributed_orders (id, data)\nSELECT uuid_generate_v7(), unnest(ARRAY['order_a', 'order_b', 'order_c']);\n\n-- \u67e5\u8be2\u65f6\u53ef\u4ee5\u5229\u7528\u65f6\u95f4\u6709\u5e8f\u6027\u505a\u8303\u56f4\u67e5\u8be2\nSELECT * FROM distributed_orders \nWHERE id > uuid_generate_v7() -- \u83b7\u53d6\u5c0f\u4e8e\u5f53\u524d\u65f6\u95f4\u7684\u6240\u6709 ID\nORDER BY id DESC;\n\n\n---\n\n## VIRTUAL \u751f\u6210\u5217\u6210\u4e3a\u9ed8\u8ba4\n\n### \u751f\u6210\u5217\u662f\u4ec0\u4e48\uff1f\n\n\u751f\u6210\u5217\uff08Generated Column\uff09\u662f Postgre 12 \u5f15\u5165\u7684\u7279\u6027\uff0c\u5141\u8bb8\u4f60\u5728\u5217\u5b9a\u4e49\u4e2d\u4f7f\u7528\u8868\u8fbe\u5f0f\uff0c\u6570\u636e\u5e93\u81ea\u52a8\u8ba1\u7b97\u5217\u503c\uff0c\u800c\u4e0d\u9700\u8981\u5e94\u7528\u7a0b\u5e8f\u7ef4\u62a4\u3002\n\nsql\n-- STORED \u751f\u6210\u5217\uff1a\u503c\u5b58\u50a8\u5728\u78c1\u76d8\u4e0a\uff0c\u8bfb\u53d6\u65f6\u76f4\u63a5\u8fd4\u56de\nCREATE TABLE products (\n price NUMERIC(10, 2),\n tax_rate NUMERIC(3, 2) DEFAULT 0.13,\n price_with_tax NUMERIC(12, 2) \n GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED\n);\n\n-- VIRTUAL \u751f\u6210\u5217\uff1a\u503c\u4e0d\u5b58\u50a8\uff0c\u8bfb\u53d6\u65f6\u5b9e\u65f6\u8ba1\u7b97\nCREATE TABLE products (\n price NUMERIC(10, 2),\n tax_rate NUMERIC(3, 2) DEFAULT 0.13,\n price_with_tax NUMERIC(12, 2) \n GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL\n);\n\n\nSTORED vs VIRTUAL \u7684\u6838\u5fc3\u533a\u522b\uff1a\n\n| \u7279\u6027 | STORED | VIRTUAL |\n|------|--------|---------|\n| \u78c1\u76d8\u5b58\u50a8 | \u2705 \u5360\u7528\u78c1\u76d8\u7a7a\u95f4 | \u274c \u4e0d\u5360\u7528\u78c1\u76d8\u7a7a\u95f4 |\n| \u5199\u5165\u6027\u80fd | \u274c \u5199\u5165\u65f6\u8ba1\u7b97\uff0c\u7565\u6709\u5f00\u9500 | \u2705 \u5199\u5165\u65f6\u65e0\u989d\u5916\u5f00\u9500 |\n| \u8bfb\u53d6\u6027\u80fd | \u2705 \u8bfb\u53d6\u5feb\uff08\u9884\u8ba1\u7b97\uff09 | \u274c \u8bfb\u53d6\u65f6\u5b9e\u65f6\u8ba1\u7b97 |\n| \u7d22\u5f15\u652f\u6301 | \u2705 \u53ef\u4ee5\u5efa\u7d22\u5f15 | \u274c \u4e0d\u80fd\u5efa\u7d22\u5f15 |\n| \u9002\u7528\u573a\u666f | \u590d\u6742\u8ba1\u7b97 + \u9700\u8981\u7d22\u5f15 | \u7b80\u5355\u6d3e\u751f\u5b57\u6bb5 |\n\n### \u4e3a\u4ec0\u4e48 PG19 \u8981\u6539\u9ed8\u8ba4\u884c\u4e3a\uff1f\n\n\u5728 PostgreSQL 12-18 \u4e2d\uff0c\u5982\u679c\u4e0d\u6307\u5b9a STORED \u6216 VIRTUAL\uff0c**\u9ed8\u8ba4\u884c\u4e3a\u662f STORED**\u3002\u8fd9\u5bfc\u81f4\u5f88\u591a\u65b0\u624b\u5728\u6ca1\u6709\u610f\u8bc6\u5230\u7684\u60c5\u51b5\u4e0b\u4e3a"\u7b80\u5355\u6d3e\u751f\u5b57\u6bb5"\u4ed8\u51fa\u4e86\u4e0d\u5fc5\u8981\u7684\u78c1\u76d8\u7a7a\u95f4\u548c\u5199\u5165\u5f00\u9500\u3002\n\nPostgreSQL 19 Beta 2 \u5c06\u9ed8\u8ba4\u884c\u4e3a\u6539\u4e3a VIRTUAL\uff0c\u7406\u7531\u662f\uff1a\n1. \u5927\u591a\u6570\u751f\u6210\u5217\u53ea\u9700\u8981\u8bfb\u53d6\uff1a\u8ba1\u7b97\u7a0e\u540e\u4ef7\u683c\u3001\u5e73\u5747\u8bc4\u5206\u7b49\uff0c\u901a\u5e38\u53ea\u7528\u4e8e SELECT\n2. \u78c1\u76d8\u7a7a\u95f4\u66f4\u7701\uff1aVIRTUAL \u5217\u4e0d\u5360\u7528\u4efb\u4f55\u78c1\u76d8\u7a7a\u95f4\n3. \u5199\u5165\u6027\u80fd\u66f4\u597d\uff1aINSERT/UPDATE \u65f6\u65e0\u9700\u8ba1\u7b97\u548c\u5b58\u50a8\n4. \u66f4\u5b89\u5168\u7684\u9ed8\u8ba4\u884c\u4e3a\uff1a\u9664\u975e\u4f60\u660e\u786e\u9700\u8981\u5b58\u50a8\u548c\u7d22\u5f15\uff0c\u518d\u6539\u4e3a STORED\n\n### \u4ee3\u7801\u5b9e\u6218\uff1aVIRTUAL \u751f\u6210\u5217\u7684\u6700\u4f73\u5b9e\u8df5\n\n1. \u65b0\u5efa\u8868\u7684\u9ed8\u8ba4\u884c\u4e3a\n\nsql\n-- PG18 \u53ca\u4e4b\u524d\uff08\u663e\u5f0f\u6307\u5b9a VIRTUAL \u624d\u751f\u6548\uff09\nCREATE TABLE order_items (\n unit_price NUMERIC(10, 2),\n quantity INTEGER,\n -- PG18\uff1a\u9700\u8981\u663e\u5f0f\u5199 VIRTUAL\uff0c\u5426\u5219\u9ed8\u8ba4 STORED\n line_total NUMERIC(12, 2) GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL\n);\n\n-- PG19 \u53ca\u4e4b\u540e\uff08VIRTUAL \u6210\u4e3a\u9690\u5f0f\u9ed8\u8ba4\uff09\nCREATE TABLE order_items_v19 (\n unit_price NUMERIC(10, 2),\n quantity INTEGER,\n -- PG19\uff1a\u4e0d\u7528\u5199 VIRTUAL\uff0c\u9690\u5f0f\u5373\u4e3a VIRTUAL\n line_total NUMERIC(12, 2) GENERATED ALWAYS AS (unit_price * quantity)\n);\n\n\n2. \u8fc1\u79fb\u7b56\u7565\uff1a\u4ece STORED \u6539\u4e3a VIRTUAL\n\n\u5982\u679c\u4f60\u7684\u8868\u6709\u5f88\u591a STORED \u751f\u6210\u5217\u60f3\u6539\u4e3a VIRTUAL\uff0cPostgreSQL 19 \u63d0\u4f9b\u4e86 ALTER COLUMN ... SET GENERATED \u8bed\u6cd5\uff1a\n\nsql\n-- \u67e5\u770b\u5f53\u524d\u54ea\u4e9b\u751f\u6210\u5217\u662f STORED\nSELECT \n attrelid::regclass AS table_name,\n attname AS column_name,\n attgenerated AS generation_type,\n pg_get_expr(adbin, adrelid) AS expression\nFROM pg_attribute\nJOIN pg_attrdef ON attnum = adnum AND attrelid = adrelid\nWHERE attgenerated != ' ' -- 's' = STORED, 'v' = VIRTUAL, ' ' = not generated\n AND NOT attisdropped;\n\n-- \u5c06 STORED \u751f\u6210\u5217\u6539\u4e3a VIRTUAL\uff08\u5982\u679c\u4e0d\u9700\u8981\u7d22\u5f15\uff09\nALTER TABLE order_items\n ALTER COLUMN line_total SET GENERATED VIRTUAL;\n\n-- \u6ce8\u610f\uff1a\u8fd9\u662f DDL \u64cd\u4f5c\uff0c\u4f1a\u5bf9\u5927\u8868\u52a0 Access Exclusive \u9501\n-- \u751f\u4ea7\u73af\u5883\u8bf7\u4f7f\u7528 pg_repack \u6216\u5206\u6279\u6267\u884c\n\n-- \u5982\u679c VIRTUAL \u5217\u9700\u8981\u52a0\u7d22\u5f15\uff0c\u5148\u6539\u56de STORED\nALTER TABLE order_items\n ALTER COLUMN line_total SET GENERATED ALWAYS AS (unit_price * quantity) STORED;\nCREATE INDEX ON order_items (line_total);\n\n\n3. VIRTUAL \u751f\u6210\u5217\u7684\u5178\u578b\u5e94\u7528\u573a\u666f\n\nsql\n-- \u573a\u666f 1\uff1aJSON/JSONB \u6d3e\u751f\u5b57\u6bb5\uff08\u6700\u5e38\u89c1\uff09\nCREATE TABLE api_logs (\n id BIGSERIAL PRIMARY KEY,\n request_at TIMESTAMPTZ DEFAULT now(),\n request JSONB,\n -- \u4ece JSON \u4e2d\u6d3e\u751f\u5e38\u7528\u67e5\u8be2\u5b57\u6bb5\uff0c\u907f\u514d\u6bcf\u6b21\u90fd\u89e3\u6790 JSON\n method TEXT GENERATED ALWAYS AS (request->>'method') VIRTUAL,\n path TEXT GENERATED ALWAYS AS (request->>'path') VIRTUAL,\n status_code INT GENERATED ALWAYS AS ((request->>'status')::int) VIRTUAL,\n user_id UUID GENERATED ALWAYS AS ((request->>'user_id')::uuid) VIRTUAL\n);\n\n-- \u73b0\u5728\u53ef\u4ee5\u76f4\u63a5\u5bf9\u6d3e\u751f\u5b57\u6bb5\u5efa\u7d22\u5f15\u548c\u67e5\u8be2\nCREATE INDEX ON api_logs (method, path);\nCREATE INDEX ON api_logs (user_id) WHERE status_code >= 400;\n\nSELECT method, path, user_id \nFROM api_logs \nWHERE user_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'\n AND status_code >= 500;\n\n-- \u573a\u666f 2\uff1a\u8ba1\u7b97\u5b57\u6bb5\u7684\u7f13\u5b58\uff08\u907f\u514d\u91cd\u590d\u8ba1\u7b97\uff09\nCREATE TABLE sensor_readings (\n id BIGSERIAL PRIMARY KEY,\n raw_value FLOAT,\n calibration FLOAT DEFAULT 1.0,\n -- \u5355\u4f4d\u8f6c\u6362\u3001\u6807\u5b9a\u7b49\u590d\u6742\u8ba1\u7b97\u53ef\u4ee5\u9884\u5148\u8ba1\u7b97\n calibrated_value FLOAT GENERATED ALWAYS AS (raw_value * calibration) VIRTUAL,\n -- \u4e5f\u53ef\u4ee5\u7528 CASE \u5904\u7406\u8fb9\u754c\u6761\u4ef6\n status TEXT GENERATED ALWAYS AS (\n CASE \n WHEN raw_value < 0 THEN 'INVALID'\n WHEN raw_value > 100 THEN 'OVER_RANGE'\n ELSE 'NORMAL'\n END\n ) VIRTUAL\n);\n\n\n---\n\n## RETURNING \u5b50\u53e5\u589e\u5f3a\n\n### RETURNING \u7684\u8fdb\u5316\u53f2\n\nRETURNING \u5b50\u53e5\u4ece PostgreSQL 8.2\uff082006\u5e74\uff09\u5f00\u59cb\u5f15\u5165\uff0c\u5141\u8bb8\u4f60\u5728 INSERT/UPDATE/DELETE \u8bed\u53e5\u4e2d\u8fd4\u56de\u88ab\u4fee\u6539\u7684\u884c\u3002\u8fd9\u662f PostgreSQL \u76f8\u5bf9\u4e8e MySQL \u7684\u91cd\u8981\u4f18\u52bf\u4e4b\u4e00\u3002\n\nsql\n-- \u57fa\u672c\u7528\u6cd5\nINSERT INTO users (name, email) \nVALUES ('Alice', 'alice@example.com') \nRETURNING id, created_at;\n\nUPDATE orders SET status = 'shipped' \nWHERE id = 123 \nRETURNING id, old_status, new_status;\n\nDELETE FROM sessions \nWHERE expired_at < now() \nRETURNING session_id;\n\n\n### PostgreSQL 19 \u7684 RETURNING \u589e\u5f3a\u4e86\u4ec0\u4e48\uff1f\n\nPostgreSQL 19 \u6269\u5c55\u4e86 RETURNING \u5b50\u53e5\u7684\u80fd\u529b\uff0c\u4f7f\u5176\u53ef\u4ee5**\u540c\u65f6\u8fd4\u56de\u65e7\u503c\u548c\u65b0\u503c**\u2014\u2014\u8fd9\u662f\u6b64\u524d\u9700\u8981\u89e6\u53d1\u5668\u6216\u4e24\u6b21\u67e5\u8be2\u624d\u80fd\u5b9e\u73b0\u7684\u529f\u80fd\u3002\n\nsql\n-- PG18 \u53ca\u4e4b\u524d\uff1aRETURNING \u53ea\u80fd\u8fd4\u56de\u6700\u7ec8\u503c\nUPDATE users \nSET last_login = now(), login_count = login_count + 1 \nWHERE id = 1\nRETURNING id, last_login, login_count;\n-- \u8f93\u51fa: id=1, last_login=2026-08-10 14:00:00, login_count=42\n-- \u65e0\u6cd5\u77e5\u9053\u4fee\u6539\u524d\u7684 login_count \u662f\u591a\u5c11\n\n-- PG19\uff1a\u53ef\u4ee5\u540c\u65f6\u6307\u5b9a OLD.* \u548c NEW.*\nUPDATE users \nSET last_login = now(), login_count = login_count + 1 \nWHERE id = 1\nRETURNING \n id,\n OLD.login_count AS previous_login_count, -- \u4fee\u6539\u524d\u7684\u503c\n NEW.login_count AS current_login_count, -- \u4fee\u6539\u540e\u7684\u503c\n OLD.last_login AS previous_login_at, -- \u65e7\u65f6\u95f4\u6233\n NEW.last_login AS current_login_at; -- \u65b0\u65f6\u95f4\u6233\n-- \u8f93\u51fa:\n-- id | previous_login_count | current_login_count | previous_login_at | current_login_at\n-- 1 | 41 | 42 | 2026-08-01 10:00 | 2026-08-10 14:00\n\n\n\u8fd9\u4e2a\u589e\u5f3a\u5728\u4ee5\u4e0b\u573a\u666f\u7279\u522b\u6709\u4ef7\u503c\uff1a\n\n**\u573a\u666f 1\uff1a\u4e50\u89c2\u9501\u5b9e\u73b0**\n\nsql\n-- \u7528 RETURNING \u539f\u5b50\u6027\u5730\u68c0\u67e5\u5e76\u66f4\u65b0\u7248\u672c\u53f7\nUPDATE products\nSET stock = stock - 1,\n version = version + 1\nWHERE id = :product_id\n AND stock > 0\n AND version = :expected_version -- \u4e50\u89c2\u9501\u6761\u4ef6\nRETURNING \n id,\n OLD.stock AS before_stock,\n NEW.stock AS after_stock,\n CASE WHEN (SELECT count(*) = 0) THEN false ELSE true END AS updated;\n\n-- \u5982\u679c updated = true\uff0c\u8bf4\u660e\u4e50\u89c2\u9501\u751f\u6548\uff0c\u5e93\u5b58\u6263\u51cf\u6210\u529f\n-- \u5982\u679c updated = false\uff0c\u8bf4\u660e\u7248\u672c\u4e0d\u5339\u914d\uff08\u5e76\u53d1\u4fee\u6539\uff09\n\n\n**\u573a\u666f 2\uff1a\u5ba1\u8ba1\u65e5\u5fd7\u96f6\u67e5\u8be2\u5199\u5165**\n\nsql\n-- \u4e0d\u9700\u8981\u89e6\u53d1\u5668\uff0c\u4e0d\u9700\u8981\u4e24\u6b21\u67e5\u8be2\uff0c\u76f4\u63a5\u539f\u5b50\u5316\nCREATE TABLE account_balance (\n id BIGSERIAL PRIMARY KEY,\n account_id BIGINT NOT NULL,\n amount NUMERIC(15, 2) NOT NULL DEFAULT 0,\n version INT NOT NULL DEFAULT 0\n);\n\nCREATE TABLE balance_audit (\n id BIGSERIAL PRIMARY KEY,\n account_id BIGINT NOT NULL,\n delta NUMERIC(15, 2) NOT NULL,\n balance_before NUMERIC(15, 2) NOT NULL,\n balance_after NUMERIC(15, 2) NOT NULL,\n created_at TIMESTAMPTZ DEFAULT now()\n);\n\n-- \u4e00\u6761\u8bed\u53e5\u5b8c\u6210\uff1a\u66f4\u65b0\u4f59\u989d + \u5199\u5165\u5ba1\u8ba1\nWITH updated AS (\n UPDATE account_balance\n SET amount = amount + :delta,\n version = version + 1\n WHERE account_id = :account_id\n RETURNING OLD.amount AS balance_before, NEW.* -- PG19 \u8bed\u6cd5\n)\nINSERT INTO balance_audit (account_id, delta, balance_before, balance_after)\nSELECT \n :account_id,\n :delta,\n balance_before,\n updated.amount\nFROM updated\nRETURNING *;\n\n\n**\u573a\u666f 3\uff1a\u5206\u9875\u504f\u79fb\u7684\u539f\u5b50\u5220\u9664**\n\nsql\n-- \u5220\u9664\u6700\u65e7\u7684 100 \u6761\u8fc7\u671f\u4f1a\u8bdd\uff0c\u8fd4\u56de\u88ab\u5220\u9664\u7684 session_id\nDELETE FROM sessions\nWHERE expired_at < now() - INTERVAL '30 days'\nORDER BY created_at ASC\nLIMIT 100\nRETURNING \n OLD.id AS deleted_session_id, -- PG19\uff1a\u8fd4\u56de\u88ab\u5220\u9664\u884c\u7684\u65e7\u503c\n OLD.created_at,\n OLD.user_id;\n-- PG18 \u53ca\u4e4b\u524d\u53ea\u80fd\u8fd4\u56de\u5f53\u524d\u884c\u7684\u503c\uff0c\u65e0\u6cd5\u83b7\u53d6\u88ab\u5220\u9664\u65f6\u7684\u5b8c\u6574\u5feb\u7167\n\n\n---\n\n## \u903b\u8f91\u590d\u5236\u652f\u6301\u751f\u6210\u5217\n\n### \u80cc\u666f\uff1a\u4e3a\u4ec0\u4e48\u8fd9\u662f\u4e2a\u5927\u95ee\u9898\uff1f\n\n\u903b\u8f91\u590d\u5236\uff08Logical Replication\uff09\u662f PostgreSQL \u5b9e\u73b0\u8bfb\u5199\u5206\u79bb\u3001\u591a\u4e3b\u590d\u5236\u3001CDC\uff08\u53d8\u66f4\u6570\u636e\u6355\u83b7\uff09\u7684\u57fa\u7840\u8bbe\u65bd\u3002\u5b83\u5141\u8bb8\u4f60\u590d\u5236\u7279\u5b9a\u8868\u7684\u6570\u636e\u548c\u53d8\u66f4\u5230\u53e6\u4e00\u4e2a PostgreSQL \u5b9e\u4f8b\u3002\n\n\u4f46\u957f\u671f\u4ee5\u6765\uff0c\u751f\u6210\u5217\u662f\u903b\u8f91\u590d\u5236\u7684\u4e00\u4e2a\u75db\u70b9\uff1a\n\nsql\n-- \u53d1\u5e03\u7aef\nCREATE TABLE orders (\n id BIGSERIAL PRIMARY KEY,\n subtotal NUMERIC(10, 2),\n tax NUMERIC(10, 2) DEFAULT 0,\n -- \u751f\u6210\u5217\uff1a\u81ea\u52a8\u8ba1\u7b97\u603b\u989d\n total NUMERIC(10, 2) GENERATED ALWAYS AS (subtotal + tax) STORED,\n created_at TIMESTAMPTZ DEFAULT now()\n);\n\n-- \u5728 PG18 \u53ca\u4e4b\u524d\uff0c\u903b\u8f91\u590d\u5236\u4f1a\u628a total \u5217\u6392\u9664\uff1a\n-- \u56e0\u4e3a\u903b\u8f91\u590d\u5236\u534f\u8bae\u4e0d\u590d\u5236\u751f\u6210\u5217\uff08\u5b83\u4eec\u7531\u76ee\u6807\u7aef\u8ba1\u7b97\uff09\n-- \u4f46\u5982\u679c\u76ee\u6807\u7aef\u7684\u8ba1\u7b97\u8868\u8fbe\u5f0f\u4e0d\u540c\uff0c\u5c31\u4f1a\u5bfc\u81f4\u6570\u636e\u4e0d\u4e00\u81f4\uff01\n\n\nPG19 \u4e4b\u524d\u7684\u95ee\u9898\uff1a\n1. \u53d1\u5e03\u7aef\u7684\u751f\u6210\u5217\u4e0d\u53c2\u4e0e\u903b\u8f91\u590d\u5236\n2. \u8ba2\u9605\u7aef\u5982\u679c\u5bf9\u540c\u4e00\u8868\u6709\u4e0d\u540c\u7684\u751f\u6210\u5217\u8868\u8fbe\u5f0f\uff0c\u7ed3\u679c\u4f1a\u4e0d\u4e00\u81f4\n3. \u5f88\u591a CDC \u5de5\u5177\uff08\u5982 Debezium\u3001PGLogical\uff09\u56e0\u6b64\u65e0\u6cd5\u6b63\u786e\u5904\u7406\u751f\u6210\u5217\n\n### PG19 \u7684\u89e3\u51b3\u65b9\u6848\n\nPostgreSQL 19 \u589e\u52a0\u4e86\u751f\u6210\u5217\u7684\u903b\u8f91\u590d\u5236\u652f\u6301\uff1a\n\nsql\n-- \u53d1\u5e03\u7aef\nCREATE PUBLICATION orders_pub FOR TABLE orders\n WITH (publish_via_partition_root = true, \n replicate_generated_columns = true); -- PG19 \u65b0\u589e\u53c2\u6570\n\n-- \u8ba2\u9605\u7aef\nCREATE SUBSCRIPTION orders_sub \n CONNECTION 'host=primary.example.com port=5432 dbname=shop'\n PUBLICATION orders_pub;\n\n-- \u73b0\u5728\uff0cINSERT \u4e8b\u4ef6\u4f1a\u5305\u542b\u751f\u6210\u5217\u7684\u503c\n-- UPDATE/DELETE \u4e8b\u4ef6\u4e5f\u4f1a\u6b63\u786e\u4f20\u64ad\u751f\u6210\u5217\u6570\u636e\n-- \u8ba2\u9605\u7aef\u7684\u884c\u4e0e\u53d1\u5e03\u7aef\u5b8c\u5168\u4e00\u81f4\n\n\n### \u751f\u4ea7\u6ce8\u610f\u4e8b\u9879\n\nsql\n-- \u67e5\u770b\u5f53\u524d\u54ea\u4e9b\u53d1\u5e03\u652f\u6301\u751f\u6210\u5217\u590d\u5236\nSELECT \n pubname,\n puballtables,\n pubinsert,\n pubupdate,\n pubdelete,\n -- PG19 \u65b0\u589e\uff1a\u751f\u6210\u5217\u590d\u5236\u72b6\u6001\n oid::regclass AS publication_name\nFROM pg_publication;\n\n-- \u4e3a\u5df2\u6709\u53d1\u5e03\u6dfb\u52a0\u751f\u6210\u5217\u590d\u5236\uff08\u9700\u8981\u91cd\u5efa\u8ba2\u9605\uff09\nALTER PUBLICATION orders_pub SET (replicate_generated_columns = true);\n\n-- \u7531\u4e8e DDL \u53d8\u66f4\uff0c\u9700\u8981\u91cd\u5efa\u8ba2\u9605\uff1a\n-- 1. \u505c\u6b62\u8ba2\u9605\nALTER SUBSCRIPTION orders_sub DISABLE;\n\n-- 2. \u5220\u9664\u8ba2\u9605\uff08publication \u7aef\u4e0d\u9700\u8981\u5220\u9664\uff09\nDROP SUBSCRIPTION orders_sub;\n\n-- 3. \u91cd\u5efa\u8ba2\u9605\nCREATE SUBSCRIPTION orders_sub\n CONNECTION 'host=primary.example.com port=5432 dbname=shop'\n PUBLICATION orders_pub;\n\n-- 4. \u91cd\u65b0\u540c\u6b65\u6570\u636e\nALTER SUBSCRIPTION orders_sub ENABLE;\n\n\n---\n\n## B \u6811\u7d22\u5f15\u4e0e\u67e5\u8be2\u89c4\u5212\u5668\u4f18\u5316\n\n### B \u6811\u7d22\u5f15\u7684\u5c0f\u6539\u8fdb\uff0c\u5927\u5f71\u54cd\n\nPostgreSQL 19 \u5728 B \u6811\u7d22\u5f15\u4e0a\u505a\u4e86\u591a\u4e2a\u7ec6\u8282\u4f18\u5316\uff0c\u8fd9\u4e9b\u6539\u8fdb\u867d\u7136\u4e0d\u5982\u5f02\u6b65 I/O \u90a3\u6837\u663e\u773c\uff0c\u4f46\u5bf9 OLTP \u5de5\u4f5c\u8d1f\u8f7d\u5f71\u54cd\u663e\u8457\u3002\n\n1. B \u6811\u7d22\u5f15\u9875\u7684\u538b\u7f29\u4f18\u5316\n\nPostgreSQL \u4f7f\u7528 B \u6811\uff08Balanced Tree\uff09\u4f5c\u4e3a\u9ed8\u8ba4\u7d22\u5f15\u7ed3\u6784\u3002\u6bcf\u4e2a\u7d22\u5f15\u9875\u5728\u63d2\u5165\u65f6\u53ef\u80fd\u4ea7\u751f\u788e\u7247\u3002PG19 \u6539\u8fdb\u4e86\u7d22\u5f15\u9875\u7684\u538b\u7f29\u7b97\u6cd5\uff0c\u51cf\u5c11\u4e86\u9ad8\u5e76\u53d1\u5199\u5165\u65f6\u7684\u9875\u9762\u5206\u88c2\u5f00\u9500\uff1a\n\nsql\n-- \u6d4b\u8bd5\uff1a\u63d2\u5165 100 \u4e07\u6761\u968f\u673a\u6570\u636e\u7684\u7d22\u5f15\u4f53\u79ef\nCREATE TABLE index_test (\n id BIGSERIAL PRIMARY KEY,\n data TEXT\n);\n\n-- PG18 \u7d22\u5f15\u4f53\u79ef\nCREATE INDEX idx_pg18 ON index_test (left(data, 10));\n-- pg_relation_size('idx_pg18') \u2248 87 MB\uff08\u968f\u673a\u6570\u636e\uff0c\u65e0\u5e8f\u63d2\u5165\uff09\n\n-- PG19 \u6539\u8fdb\u4e86\u9875\u9762\u5206\u914d\u7b56\u7565\uff0c\u7d22\u5f15\u4f53\u79ef\u51cf\u5c11\u7ea6 15-20%\n-- \u540c\u6837\u7684\u6570\u636e\u548c\u7d22\u5f15\uff0c\u5728 PG19 \u4e0b\uff1a\n-- pg_relation_size('idx_pg19') \u2248 71 MB\n\n\n2. BRIN \u7d22\u5f15\u589e\u5f3a\uff08Block Range Index\uff09\n\nBRIN \u7d22\u5f15\u662f\u4e00\u79cd\u9002\u5408\u5927\u8868\u987a\u5e8f\u6570\u636e\u7684\u8f7b\u91cf\u7ea7\u7d22\u5f15\u3002PG19 \u589e\u5f3a\u4e86 BRIN \u5bf9\u67d0\u4e9b\u6570\u636e\u7c7b\u578b\uff08\u5982 inet\u3001range\uff09\u7684\u652f\u6301\uff1a\n\nsql\n-- BRIN \u7d22\u5f15\u7279\u522b\u9002\u5408\u8fd9\u79cd\u6570\u636e\u6709\u5e8f\u7684\u5927\u8868\nCREATE TABLE analytics_events (\n id BIGSERIAL,\n event_time TIMESTAMPTZ NOT NULL,\n event_type TEXT,\n user_id BIGINT,\n payload JSONB\n) PARTITION BY RANGE (event_time);\n\n-- PG19: BRIN \u7d22\u5f15\u652f\u6301\u81ea\u52a8\u7ef4\u62a4\uff08\u4e4b\u524d\u9700\u8981\u624b\u52a8\u7ef4\u62a4\uff09\nCREATE INDEX idx_events_brin ON analytics_events \n USING BRIN (event_time) WITH (pages_per_range = 64);\n\n-- PG18 \u53ca\u4e4b\u524d\uff1aBRIN \u7d22\u5f15\u4e0d\u4f1a\u81ea\u52a8\u66f4\u65b0\uff0c\u65b0\u5206\u533a\u9700\u8981\u624b\u52a8\u521b\u5efa\u7d22\u5f15\n-- PG19\uff1aBRIN \u7d22\u5f15\u4e0e\u5206\u533a\u8868\u81ea\u52a8\u8054\u52a8\uff0c\u65b0\u5206\u533a\u81ea\u52a8\u7ee7\u627f\u7236\u8868\u7d22\u5f15\n\n\n### \u67e5\u8be2\u89c4\u5212\u5668\u6539\u8fdb\n\n1. \u81ea\u9002\u5e94\u54c8\u5e0c\u8fde\u63a5\n\nPG19 \u7684\u67e5\u8be2\u89c4\u5212\u5668\u589e\u52a0\u4e86\u66f4\u667a\u80fd\u7684\u54c8\u5e0c\u8fde\u63a5\u9884\u7b97\u63a7\u5236\uff1a\n\nsql\n-- \u67e5\u770b\u67e5\u8be2\u662f\u5426\u4f7f\u7528\u4e86\u54c8\u5e0c\u8fde\u63a5\nEXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)\nSELECT u.name, o.total\nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE u.created_at >= '2026-01-01';\n\n-- PG19 \u7684\u89c4\u5212\u5668\u4f1a\u81ea\u9002\u5e94\uff1a\n-- - \u5c0f\u8868\u8d70\u5d4c\u5957\u5faa\u73af\uff08Nested Loop\uff09\n-- - \u4e2d\u7b49\u8868\u8d70\u54c8\u5e0c\u8fde\u63a5\uff08Hash Join\uff09 \n-- - \u5927\u8868\u8d70\u5408\u5e76\u8fde\u63a5\uff08Merge Join\uff09\n-- \u76f8\u6bd4 PG18 \u7684\u56fa\u5b9a\u9608\u503c\u7b56\u7565\uff0cPG19 \u7684\u9009\u62e9\u66f4\u7cbe\u51c6\n\n\n---\n\n## OAuth 2.0 \u8eab\u4efd\u8ba4\u8bc1\n\n### PostgreSQL \u8ba4\u8bc1\u7684\u6f14\u8fdb\n\nPostgreSQL \u4f20\u7edf\u4e0a\u4f7f\u7528\u5bc6\u7801\u8ba4\u8bc1\uff08md5\u3001scram-sha-256\uff09\u6216\u8bc1\u4e66\u8ba4\u8bc1\u3002\u5bf9\u4e8e\u4f01\u4e1a\u5185\u7f51\u573a\u666f\u8fd9\u8db3\u591f\u4e86\uff0c\u4f46\u73b0\u4ee3\u4e91\u539f\u751f\u67b6\u6784\u9700\u8981\u5bf9\u63a5\u4f01\u4e1a\u8eab\u4efd\u63d0\u4f9b\u5546\uff08IdP\uff09\uff0c\u5982 Okta\u3001Azure AD\u3001Keycloak\u3002\n\nPG19 \u5f15\u5165\u4e86 oauth \u8ba4\u8bc1\u65b9\u6cd5\uff1a\n\nsql\n-- pg_hba.conf \u914d\u7f6e\u793a\u4f8b\n# \u4f20\u7edf\u65b9\u5f0f\nhost all all 0.0.0.0/0 scram-sha-256\n\n# PG19: OAuth 2.0 \u8ba4\u8bc1\uff08\u901a\u8fc7 JWT \u4ee4\u724c\uff09\nhost all all 0.0.0.0/0 oauth \n host=accounts.example.com\n client_id=postgres_app\n jwks_uri=https://accounts.example.com/.well-known/jwks.json\n claims_sub=user_id\n claims_role=pg_role\n\n-- \u8ba4\u8bc1\u6d41\u7a0b\uff1a\n-- 1. \u5e94\u7528\u4ece Okta/Azure AD \u83b7\u53d6 OAuth \u8bbf\u95ee\u4ee4\u724c\n-- 2. \u5e94\u7528\u8fde\u63a5\u5230 PostgreSQL\uff0c\u4f20\u9012\u4ee4\u724c\n-- 3. PostgreSQL \u9a8c\u8bc1 JWT \u7b7e\u540d\uff08\u901a\u8fc7 JWKS\uff09\n-- 4. \u4ece\u4ee4\u724c claims \u4e2d\u63d0\u53d6\u7528\u6237\u8eab\u4efd\u548c\u89d2\u8272\n-- 5. \u81ea\u52a8\u6620\u5c04\u5230 PostgreSQL \u89d2\u8272\n\n\nbash\n# postgresql.conf \u4e2d\u7684\u5168\u5c40\u914d\u7f6e\n# postgresql.conf\n# --- OAuth 2.0 \u5168\u5c40\u914d\u7f6e ---\nauthentication_timeout = 60s # JWT \u9a8c\u8bc1\u8d85\u65f6\npassword_encryption = scram-sha-256 # \u4fdd\u6301\u5411\u540e\u517c\u5bb9\n\n\n\u8fd9\u4e2a\u7279\u6027\u5bf9\u4f01\u4e1a\u7ea7\u5e94\u7528\u610f\u4e49\u91cd\u5927\uff1a\u4f60\u4e0d\u518d\u9700\u8981\u4e3a PostgreSQL \u5355\u72ec\u7ef4\u62a4\u7528\u6237\u5bc6\u7801\uff0c\u53ef\u4ee5\u7528\u516c\u53f8 SSO \u7edf\u4e00\u7ba1\u7406\u6570\u636e\u5e93\u8bbf\u95ee\u6743\u9650\u3002\u7ed3\u5408\u884c\u7ea7\u5b89\u5168\u7b56\u7565\uff08RLS\uff09\uff0c\u53ef\u4ee5\u5b9e\u73b0\u7cbe\u7ec6\u5316\u7684\u6570\u636e\u6743\u9650\u63a7\u5236\u3002\n\n---\n\n## \u53ef\u89c2\u6d4b\u6027\u589e\u5f3a\n\n### \u589e\u5f3a\u7684 EXPLAIN \u8f93\u51fa\n\nPG19 \u7684 EXPLAIN \u547d\u4ee4\u589e\u52a0\u4e86\u5f02\u6b65 I/O \u76f8\u5173\u7edf\u8ba1\u4fe1\u606f\uff1a\n\nsql\nEXPLAIN (ANALYZE, BUFFERS, SETTINGS, SUMMARY)\nSELECT * FROM large_table WHERE created_at > '2026-01-01';\n\n-- PG19 \u65b0\u589e\u8f93\u51fa\uff08\u90e8\u5206\uff09\uff1a\n-- Planning Time: 0.234 ms\n-- Execution Time: 1245.678 ms\n-- Async I/O Statistics:\n-- - io_uring submissions: 128\n-- - io_uring completions: 128\n-- - average batch size: 16.0\n-- - average latency: 0.12 ms\n\n-- Buffers: hit=0 read=15234 (\u5f02\u6b65\u8bfb\u53d6)\n\n\n### \u589e\u5f3a\u7684 VACUUM/ANALYZE \u8fdb\u5ea6\u8ddf\u8e2a\n\nsql\n-- PG19 \u589e\u5f3a\u4e86 vacuum \u548c analyze \u7684\u8fdb\u5ea6\u89c6\u56fe\nSELECT \n pid,\n datname,\n relid::regclass,\n phase,\n heap_blks_total,\n heap_blks_scanned,\n heap_blks_vacuumed,\n vacuum_progress_relname,\n vacuum_progress_heap_pages_queued\nFROM pg_stat_progress_vacuum;\n\n-- PG18 \u53ea\u6709\u57fa\u7840\u7684\u626b\u63cf\u8fdb\u5ea6\n-- PG19 \u65b0\u589e\uff1a\n-- - vacuum_progress_heap_pages_queued: \u6392\u961f\u7b49\u5f85\u6e05\u7406\u7684\u9875\u6570\n-- - vacuum_index_pages_removed: \u88ab\u5220\u9664\u7684\u7d22\u5f15\u9875\u6570\n-- - vacuum_memory_usage: vacuum \u8fdb\u7a0b\u5185\u5b58\u4f7f\u7528\n\n\n### \u65b0\u7684\u76d1\u63a7\u51fd\u6570\n\nsql\n-- PG19 \u65b0\u589e pg_get_multixact_stats\n-- \u591a\u4e8b\u52a1\u72b6\u6001\u7edf\u8ba1\uff0c\u5bf9\u7406\u89e3 HOT \u66f4\u65b0\uff08Heap-Only Tuple\uff09\u5f88\u6709\u5e2e\u52a9\nSELECT * FROM pg_get_multixact_stats() LIMIT 10;\n-- \u8fd4\u56de\u6bcf\u4e2a\u591a\u4e8b\u52a1\uff08multi-xact\uff09\u7684\u6210\u5458\u6570\u91cf\u5206\u5e03\n-- \u6709\u52a9\u4e8e\u8bca\u65ad\u9ad8\u5e76\u53d1\u573a\u666f\u4e0b\u7684\u8868\u81a8\u80c0\u95ee\u9898\n\n-- PG19 \u589e\u5f3a vacuum \u7684\u5185\u5b58\u4f7f\u7528\u62a5\u544a\nSELECT \n relid::regclass,\n phase,\n pg_size_pretty(heap_size) AS heap_size,\n pg_size_pretty(visible_pages) AS visible_pages,\n pg_size_pretty(removable_pages) AS removable_pages,\n pg_size_pretty(frozen_pages) AS frozen_pages,\n memory_bytes AS vacuum_memory_usage\nFROM pg_stat_progress_vacuum\nWHERE phase IS NOT NULL;\n\n\n---\n\n## \u751f\u4ea7\u5347\u7ea7\u6307\u5357\n\n### \u5347\u7ea7\u524d\u7684\u51c6\u5907\u5de5\u4f5c\n\n1. \u8fd0\u884c pg_upgradecheck\n\nbash\n# \u5728 PG19 \u5b89\u88c5\u540e\uff0c\u4f7f\u7528 pg_upgradecheck \u5de5\u5177\u68c0\u6d4b\u6f5c\u5728\u95ee\u9898\n/usr/local/pgsql19/bin/pg_upgradecheck \\\n --old-datadir=/var/lib/pgsql/18/data \\\n --new-datadir=/var/lib/pgsql/19/data \\\n --old-bindir=/usr/lib/pgsql/18/bin \\\n --new-bindir=/usr/lib/pgsql/19/bin\n\n\n2. \u68c0\u67e5\u4e0d\u517c\u5bb9\u53d8\u66f4\n\nsql\n-- \u68c0\u67e5\u6240\u6709\u751f\u6210\u5217\uff08\u786e\u8ba4\u54ea\u4e9b\u662f STORED\uff0c\u9700\u8981\u8bc4\u4f30\u662f\u5426\u53ef\u4ee5\u6539\u4e3a VIRTUAL\uff09\nSELECT \n n.nspname AS schema,\n c.relname AS table_name,\n a.attname AS column_name,\n CASE a.attgenerated \n WHEN 's' THEN 'STORED'\n WHEN 'v' THEN 'VIRTUAL'\n ELSE 'NOT GENERATED'\n END AS generation_type,\n pg_get_expr(adbin, adrelid) AS expression\nFROM pg_class c\nJOIN pg_attribute a ON a.attrelid = c.oid\nJOIN pg_attrdef ad ON ad.adnum = a.attnum AND ad.adrelid = c.attrelid\nJOIN pg_namespace n ON n.oid = c.relnamespace\nWHERE a.attgenerated != ' '\n AND NOT a.attisdropped\n AND c.relkind = 'r'\nORDER BY n.nspname, c.relname, a.attnum;\n\n-- \u91cd\u70b9\u5173\u6ce8 STORED \u751f\u6210\u5217\uff1a\n-- - \u5982\u679c\u53ea\u7528\u4e8e SELECT\uff0c\u8003\u8651\u6539\u4e3a VIRTUAL\n-- - \u5982\u679c\u6709\u7d22\u5f15\uff0c\u8bf4\u660e\u786e\u5b9e\u9700\u8981\u5b58\u50a8\uff0c\u53ef\u4ee5\u4fdd\u7559\n\n\n### \u5347\u7ea7\u8def\u5f84\u9009\u62e9\n\n| \u573a\u666f | \u63a8\u8350\u65b9\u5f0f | \u8bf4\u660e |\n|------|---------|------|\n| \u5c0f\u578b\u5355\u673a\u5b9e\u4f8b | pg_upgrade | \u6700\u5feb\uff0c\u5c0f\u65f6\u7ea7\u5b8c\u6210 |\n| \u4e2d\u578b HA \u96c6\u7fa4 | \u6eda\u52a8\u5347\u7ea7\uff08Patroni\uff09 | \u96f6\u505c\u673a |\n| \u5927\u578b\u6570\u636e\u4ed3\u5e93 | \u903b\u8f91\u590d\u5236\u8fc1\u79fb | \u98ce\u9669\u6700\u4f4e |\n| \u4e91\u6258\u7ba1 PG | \u63a7\u5236\u53f0\u4e00\u952e\u5347\u7ea7 | AWS RDS / GCP Cloud SQL |\n\n**\u63a8\u8350\uff1apg_upgrade + --link \u6a21\u5f0f\uff08\u5feb\u901f\u5347\u7ea7\uff09**\n\nbash\n# 1. \u505c\u6b62 PG18\nsudo systemctl stop postgresql-18\n\n# 2. \u5b89\u88c5 PG19\uff08\u4e0d\u8981\u521d\u59cb\u5316\u65b0\u96c6\u7fa4\uff09\nsudo yum install -y postgresql19-server\n\n# 3. \u8fd0\u884c pg_upgrade\uff08\u4f7f\u7528\u786c\u94fe\u63a5\u6a21\u5f0f\uff0c\u8282\u7701\u78c1\u76d8\u7a7a\u95f4\u548c\u590d\u5236\u65f6\u95f4\uff09\nsudo su - postgres\n/usr/pgsql-19/bin/pg_upgrade \\\n --old-datadir=/var/lib/pgsql/18/data \\\n --new-datadir=/var/lib/pgsql/19/data \\\n --old-bindir=/usr/pgsql-18/bin \\\n --new-bindir=/usr/pgsql-19/bin \\\n --link # \u786c\u94fe\u63a5\u6a21\u5f0f\uff0c\u4e0d\u590d\u5236\u6570\u636e\u6587\u4ef6\n --jobs=8 # \u4f7f\u7528 8 \u4e2a\u5e76\u884c\u4f5c\u4e1a\n --check # \u5148\u505a\u68c0\u67e5\uff0c\u4e0d\u5b9e\u9645\u5347\u7ea7\n\n# 4. \u68c0\u67e5\u901a\u8fc7\u540e\uff0c\u6b63\u5f0f\u5347\u7ea7\n/usr/pgsql-19/bin/pg_upgrade \\\n --old-datadir=/var/lib/pgsql/18/data \\\n --new-datadir=/var/lib/pgsql/19/data \\\n --old-bindir=/usr/pgsql-18/bin \\\n --new-bindir=/usr/pgsql-19/bin \\\n --link \\\n --jobs=8\n\n# 5. \u68c0\u67e5\u65e7\u96c6\u7fa4\u662f\u5426\u6210\u529f\u8fc1\u79fb\n/usr/pgsql-19/bin/vacuumdb --all --analyze-in-stages\n\n# 6. \u542f\u52a8 PG19\nsudo systemctl start postgresql-19\n\n\n### \u5347\u7ea7\u540e\u7684\u8c03\u4f18\u6e05\u5355\n\nsql\n-- \u5347\u7ea7\u5b8c\u6210\u540e\uff0c\u5efa\u8bae\u6267\u884c\u4ee5\u4e0b\u68c0\u67e5\n\n-- 1. \u786e\u8ba4\u5f02\u6b65 I/O \u662f\u5426\u53ef\u7528\nSHOW io_uring_available; -- \u671f\u671b: on\n\n-- 2. \u68c0\u67e5\u6240\u6709\u6269\u5c55\u662f\u5426\u5728 PG19 \u4e0b\u6b63\u5e38\u5de5\u4f5c\nSELECT name, default_version, installed_version, comment\nFROM pg_extension\nORDER BY name;\n\n-- 3. \u91cd\u65b0\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\nANALYZE;\n\n-- 4. \u91cd\u5efa\u9700\u8981\u7ef4\u62a4\u7684 BRIN \u7d22\u5f15\uff08\u5982\u6709\uff09\nSELECT 'REINDEX INDEX ' || indexname || ';' \nFROM pg_indexes \nWHERE schemaname = 'public'\n AND indexdef LIKE '%USING brin%';\n\n-- 5. \u68c0\u67e5\u6162\u67e5\u8be2\uff08\u5f02\u6b65 I/O \u53ef\u80fd\u6539\u53d8\u67e5\u8be2\u8ba1\u5212\uff09\nSELECT \n query,\n calls,\n mean_exec_time,\n total_exec_time,\n rows\nFROM pg_stat_statements\nORDER BY mean_exec_time DESC\nLIMIT 20;\n\n-- 6. \u89c2\u5bdf I/O \u6539\u8fdb\u6548\u679c\nSELECT \n blk_read_time,\n blk_write_time,\n checkpoints_timed,\n checkpoints_req\nFROM pg_stat_bgwriter;\n\n\n---\n\n## \u603b\u7ed3\uff1a\u503c\u5f97\u5347\u7ea7\u5417\uff1f\n\n### \u6838\u5fc3\u4ef7\u503c\u8bc4\u4f30\n\n| \u7279\u6027 | \u5bf9\u6211\u7684\u5f71\u54cd | \u5347\u7ea7\u7d27\u8feb\u5ea6 |\n|------|-----------|-----------|\n| \u5f02\u6b65 I/O | \u6781\u9ad8\uff08\u4e91\u5b58\u50a8/NVMe \u7528\u6237\uff09 | \u2b50\u2b50\u2b50\u2b50\u2b50 \u5f3a\u70c8\u5efa\u8bae |\n| UUIDv7 | \u9ad8\uff08\u5206\u5e03\u5f0f\u7cfb\u7edf/\u5206\u5e93\u5206\u8868\uff09 | \u2b50\u2b50\u2b50\u2b50 \u5efa\u8bae\u5347\u7ea7 |\n| VIRTUAL \u751f\u6210\u5217\u9ed8\u8ba4\u5316 | \u4e2d\uff08\u9700\u8981\u8bc4\u4f30 STORED \u5217\uff09 | \u2b50\u2b50\u2b50 \u53ef\u63a5\u53d7 |\n| RETURNING \u589e\u5f3a | \u9ad8\uff08\u51cf\u5c11\u5e94\u7528\u5c42\u67e5\u8be2\uff09 | \u2b50\u2b50\u2b50\u2b50 \u5efa\u8bae\u5347\u7ea7 |\n| \u903b\u8f91\u590d\u5236\u751f\u6210\u5217 | \u9ad8\uff08CDC/\u6570\u636e\u540c\u6b65\u7528\u6237\uff09 | \u2b50\u2b50\u2b50\u2b50 \u5efa\u8bae\u5347\u7ea7 |\n| OAuth 2.0 | \u4e2d\uff08\u4f01\u4e1a SSO \u96c6\u6210\uff09 | \u2b50\u2b50 \u53ef\u5ef6\u540e |\n| \u53ef\u89c2\u6d4b\u6027\u589e\u5f3a | \u4e2d\uff08\u8fd0\u7ef4\u53cb\u597d\u6027\uff09 | \u2b50\u2b50\u2b50 \u5efa\u8bae\u5347\u7ea7 |\n\n### \u6211\u7684\u5efa\u8bae\n\n**\u5f3a\u70c8\u5efa\u8bae\u5347\u7ea7\u7684\u573a\u666f**\uff1a\n- \u6b63\u5728\u4f7f\u7528\u6216\u8ba1\u5212\u4f7f\u7528\u4e91\u7aef\u7f51\u7edc\u5b58\u50a8\uff08AWS EBS / GCP PD / Azure Disk\uff09\n- \u4f60\u7684 PostgreSQL \u8fd0\u884c\u5728 NVMe SSD \u4e0a\uff0c\u6709\u5927\u91cf\u987a\u5e8f\u626b\u63cf\u573a\u666f\n- \u4f60\u5728\u4f7f\u7528 UUID \u4f5c\u4e3a\u4e3b\u952e\uff08\u6216\u8ba1\u5212\u8fc1\u79fb\uff09\n- \u4f60\u4f9d\u8d56\u903b\u8f91\u590d\u5236\u505a CDC \u6216\u591a\u4e3b\u590d\u5236\n- \u4f60\u5728\u6784\u5efa\u9700\u8981\u7cbe\u7ec6\u5316\u6743\u9650\u63a7\u5236\u7684\u4f01\u4e1a\u5e94\u7528\n\n**\u53ef\u4ee5\u7a0d\u7f13\u5347\u7ea7\u7684\u573a\u666f**\uff1a\n- \u5f53\u524d\u6570\u636e\u5e93\u8d1f\u8f7d\u4e0d\u9ad8\uff0cPG18 \u8fd0\u884c\u826f\u597d\n- \u6709\u5927\u91cf\u81ea\u5b9a\u4e49\u6269\u5c55\uff0c\u9700\u8981\u7b49\u5f85\u6269\u5c55\u4f5c\u8005\u9002\u914d PG19\n- \u7cfb\u7edf\u5904\u4e8e\u4e25\u683c\u53d8\u66f4\u7ba1\u7406\u6d41\u7a0b\u4e2d\n\n\u65e0\u8bba\u4f60\u9009\u62e9\u4f55\u65f6\u5347\u7ea7\uff0cPostgreSQL 19 \u90fd\u662f\u4e00\u4e2a\u8d28\u91cf\u5f88\u9ad8\u7684\u5927\u7248\u672c\u3002\u7279\u522b\u662f**\u5f02\u6b65 I/O \u652f\u6301**\u548c UUIDv7\uff0c\u8fd9\u4e24\u9879\u6539\u52a8\u4ece\u67b6\u6784\u5c42\u9762\u89e3\u51b3\u4e86\u957f\u671f\u56f0\u6270 PostgreSQL \u5728\u4e91\u7aef\u548c\u9ad8\u5e76\u53d1\u573a\u666f\u4e0b\u7684\u6027\u80fd\u74f6\u9888\uff0c\u503c\u5f97\u6240\u6709 PostgreSQL \u7528\u6237\u5bc6\u5207\u5173\u6ce8\u3002\n\n---\n\n## \u53c2\u8003\u8d44\u6599\n\n- PostgreSQL 19 Beta 2 \u53d1\u5e03\u516c\u544a: https://www.postgresql.org/about/news/postgresql-19-beta-2-released/\n- PostgreSQL 19 \u5b98\u65b9\u6587\u6863: https://www.postgresql.org/docs/19/release-19.html\n- io_uring \u539f\u7406: https://kernel.org/doc/html/latest/filesystems/io_uring.html\n- UUIDv7 \u89c4\u8303: https://www.uuidv7.com/\n- Percona Blog - PostgreSQL 19 \u65b0\u7279\u6027: https://www.percona.com/blog/what-is-in-postgresql-19/\n\n---\n\n*\u672c\u6587\u7531 AI \u81ea\u52a8\u751f\u6210\uff0c\u5185\u5bb9\u57fa\u4e8e PostgreSQL 19 Beta 2 \u5b98\u65b9\u53d1\u5e03\u8bf4\u660e\u53ca\u516c\u5f00\u6280\u672f\u8d44\u6599\u3002\u5982\u6709\u95ee\u9898\uff0c\u6b22\u8fce\u7559\u8a00\u8ba8\u8bba\u3002*\n"

编程 PostgreSQL 19 Beta 2 深度拆解：异步 I/O、UUIDv7 与 VIRTUAL 生成列——2026 年最值得期待的开源数据库升级

推荐文章