在 PostgreSQL 裡,當 UUID 當作 Primary Key 時要怎麼處理

繼續清 tab,在「PostgreSQL and UUID as Primary Key (maciejwalkowiak.com)」這邊看到的,原文是討論 PostgreSQL 要怎麼處理 PK 是 UUID 的情況:「PostgreSQL and UUID as primary key」。

文章開頭作者就說了,這篇不是要戰 PK 要不要用 UUID,而是已經決定要用了 (i.e. 通常不是你決定的),在接手以後要怎麼用比較好:

Considering the size of UUID it is questionable if it is a right choice, but often it is not up to us to decide.

This article does not focus on "if UUID is the right format for a key", but how to use UUID as a primary key with PostgreSQL efficiently.

首先是 PostgreSQL 從 8.3 版 (2008 年) 就支援 UUID 的資料型態了,這點從 release note 可以看到:「PostgreSQL 8.3.0」,所以要當 PK 的話沒什麼道理不考慮他。

UUID 的空間上就是 128-bit data (16 bytes),相比於 TEXT 會省蠻多的,尤其 PK 常常會被其他表格 reference 到 (像是 foreign key) 會在其他表格也省下來:

Table that uses text is 54% larger and the index size 85% larger.

第二點則是考慮到 UUID 本身的特性,以前的 UUID 因為是亂數生成的 (通常會用 UUIDv4),對寫入 B-tree 類的資料結構不是很有效率,改用 UUIDv7 (差不多是這兩年陸陸續續發展出來的規格) 會得益於與 timestamp 有關,對 B-tree 寫入的效率會好很多:

Random UUIDs are not a good fit for a B-tree indexes - and B-tree index is the only available index type for a primary key.

B-tree indexes work the best with ordered values - like auto-incremented or time sorted columns.

UUID - even though always looks similar - comes in multiple variants. Java's UUID.randomUUID() - returns UUID v4 - which is a pseudo-random value. For us the more interesting one is UUID v7 - which produces time-sorted values. It means that each time new UUID v7 is generated, a greater value it has. And that makes it a good fit for B-Tree index.

作者的測試可以看到寫入速度與 UUIDv4 相比比快不少:

BUT we can clearly see, that inserting UUID v7 is ~2x faster and inserting regular UUID v4.

總結來說,當 PK 已經決定是 UUID 後,主要就是這兩個重點可以注意的,當然 Hacker News 上更熱鬧的是兩派人馬在吵要用 integer 類的 SERIAL/BIGSERIAL 還是用 UUID,那又是另外一個話題了...

如果早個二十年前對 memory size 斤斤計較的情況下,答案鐵定是 integer 類的,但年代不同了...?

直接在 library 層將 MongoDB 用法轉換成 PostgreSQL 底層的 Pongo

看到這個「Mongo but on Postgres and with strong consistency benefits (github.com/event-driven-io)」算是另外一種用 PostgreSQL 取代 MongoDB 的嘗試,先前其他的方案是 proxy server 的方式實作 (像是 FerretDB),也就是 TCP 裡面傳的東西還是 MongoDB protocol,然後 proxy server 會轉譯成 PostgreSQL 的 SQL 語法。

這個作法的好處是不用管既有 application 是什麼程式語言開發的,另外改動比較少 (改個連線資訊 + 然後把目前還不支援的功能改寫),但缺點是多了一組 service 要維護 (如果是 HA 的話又還要設定 failover 或是 load balancer 的機制)。

Pongo 的作法則是移到 library 這邊做掉,所以就有程式語言的限制了:這個專案是用 TypeScript 開發,所以會是 JavaScript + TypeScript 生態系的方案。

不過好處就很明顯了,少了一組 service 要維護 (如果包括 HA 機制的話可能是兩組或三組),另外因為轉譯的部分在 application 端處理,沒有了 proxy server 也等於少了一個可能的 bottleneck。

這幾天上 Hacker News 後看 commit 頗熱鬧,從 Releases 頁可以看到連續出新版本。

不過... 不考慮直接用 PostgreSQL 嗎?

Peter Zaitsev 抱怨 Oracle 對 MySQL 的態度

Percona 家的老大 Peter Zaitsev 寫了一篇紋章抱怨 OracleMySQL 的態度:「Is Oracle Finally Killing MySQL?」。

其實大家都有感覺,2009 年 Oracle 買下 Sun (當時擁有 MySQL 的公司) 後的前幾年還有些動作,後面整個就停住了,這也導致了市場上可以看到明顯的變化,不少人都開始關注並且使用 PostgreSQL

隔壁 MariaDB 透過 SPAC 弄的灰頭土臉後縮編不少,自己弄的 engine 還是跟 InnoDB 有一段差距,功能上也沒有比較優秀的地方...

雲端降低管理成本也是一個讓 PostgreSQL 興起的推手,如果在地端的話,MySQL 的管理工具還是成熟不少 (畢竟就是時間累積出來的),但在雲端上面就接包的好好的了...

Gitea 1.22 預定會有的改變

Gitea 1.22.0 出了 RC:「Gitea 1.22.0 Release Candidate」,裡面整理出 1.22 會有的重大改變。

一個是之前提到的 UI 架構改變:「Gitea 預定淘汰掉 jQuery + Fomantic-UI + Semantic-UI,改用 Tailwind CSS」。

另外看到放掉舊版資料庫的消息,放掉 MySQL 5.7 與 PostgreSQL 10 & 11,這兩個都是 EoL 的版本。

Support for MySQL 5.7, PostgreSQL 10 and 11, and MSSQL 2008 is dropped.

其中 MSSQL 2008 的部分,維基百科的 Microsoft SQL Server 說還有支援 (補一下是 oldid=1220997610 這個版本),不過從微軟官方網站上查「Extended Security Updates for SQL Server and Windows Server」可以看到 2022 年就已經收掉了...

整體上看起來這個版本的重心就是放在 UI,其他的功能比較少一些...

關於 GitLab 的 SQL 設計

今天「My notes on Gitlab's Postgres schema design (2022) (shekhargulati.com)」這篇上 Hacker News 首頁 (看起來因為是在 pool 的關係,在第一頁卡很久...),文章「My Notes on GitLab Postgres Schema Design」是作者在 2022 年七月的時候分析了 GitLabstructure.sql 的資料庫設計整理出來的心得 & 感想,裡面有不少東西,不過這邊想補充個背景知識 (姿勢?):

RDBMS 在系統架構裡面,相較於其他的元件,是個很難 scale out 的東西 (i.e. 加更多機器得到更多效能),所以遇過 scalability 問題的架構師,會很習慣避開在 RDBMS 上面跑各種功能,有其他方式可以做的就拆出去用容易 scale out 的工具來做,非不得已才上 RDBMS。

而就算要塞進 RDBMS 裡的資料,能省的還是要省,畢竟宣稱自動幫你處理資料庫 scale out 的技術 (像是 CockroachDBTiDB) 其實沒想像中萬能,還是需要開發者改寫以前大惡搞的 SQL query (一個 terminal 列不完那種)。

而你心裡也有底,如果 scale out 不是條好的路,那麼只好 scale up (i.e. 加大機器的 CPU & RAM),而 scale up 總是有極限,真的遇到自己被迫要處理 sharding 的時候,DBOps/DBA 與 Dev 的臉都很臭... (一堆 JOIN 要改成拉回 application 端自己湊,或是有 ProxySQL 這種東西幫你處理,但是發現 ProxySQL 去後面資料庫拉太多資料幫你組反而很慢 !@#$%)

但另外一方面,現在已經不是 2005 年 64GB RAM 的伺服器是個天價的年代... 硬體的成長已經長到在 AWS 雲端上面可以租到給 SAP 用的 24TB RAM 的機器 (u-24tb1.112xlarge),而地端找個 server 也都有 15TB RAM (POWEREDGE R940),所以很容易把所有資料都塞到記憶體裡面搞,加上 NVMe 的讀寫速度比以前 HDD disk 快多了。

記得這兩件都是現實,然後再回來看文章內容與其他的討論,用不同的現實就會有不同的想法出現。

GitLab 的設計有他當時的限制以及想法,這些是外面的人看不到的,也就不好批評對錯。

Amazon RDS 推出 RDS Extended Support

AWSAmazon RDS 推出了 MySQL 5.7 與 PostgreSQL 11 的 RDS Extended Support 服務:「Your MySQL 5.7 and PostgreSQL 11 databases will be automatically enrolled into Amazon RDS Extended Support」。

直接看官方整理的這張表格比較清楚:

基本上都到 2027Q1 左右,差不多再多支援三年。

另外表上的時間有些接不起來的地方,則是在 Note 的地方說明。

其中 MySQL 5.7 的部分分成兩塊,其中 RDS for MySQL 5.7 的部分是比較清楚的:原來的 RDS standard support 到 2024/02/29,後續從 2024/03/01 馬上接付費的 RDS Extended Support。

Aurora MySQL 2 的 RDS standard support 則是直接一路到 2024/10/31,然後 2024/11/01~2024/11/30 的 RDS Extended Support 不收費,從 2024/12/01 開始收費:

RDS Extended Support for Aurora MySQL 2 starts on November 1, 2024, but will not be charged until December 1, 2024. Between November 1 and November 30, all Aurora MySQL 2 clusters are covered under RDS Extended Support.

而 PostgreSQL 11 的部分都一樣 (RDS for PostgreSQL 11 與 Aurora PostgreSQL 11),原來的 RDS standard support 到 2024/02/29,而 2024/03/01~2024/03/31 的 RDS Extended Support 則是免費的,從 2024/04/01 開始收費:

RDS Extended Support for PostgreSQL 11 starts on March 1, 2024, but will not be charged until April 1, 2024. Between March 1 and March 31, all PostgreSQL 11 instances on Aurora and RDS are covered under RDS Extended Support.

然後費用的部分也查的到了,是用 vCPU-hour 計算的,四條產品線的價位在 us-east-1 的計價是相同的,前面兩年是 $0.1/vCPU/hr,而第三年是 $0.2/vCPU/hr。

由於 RDS 的機器最少是 2 vCPU,所以一台機器至少要多付 $0.2/hr 的費用,這個費用基本上會比 RDS 費用還貴。

這邊給個比較的數字,同樣在 us-east-1 上,2 vCPU + 8GB RAM 的 db.t4g.large 要 $0.129/hr,而一樣 2 vCPU + 8GB RAM 如果是 db.m7g.large 則是 $0.168/hr,都還沒有 RDS Extended Support 貴;要到 r7g.large 這種以記憶體導向的 $0.1071/hr 才差不多跟上一樣的價錢。

另外一個方法應該就是改成自己在 EC2 上架設?這樣成本會因為 RDS 轉 EC2 的下降,整體大約會降到 1/4...

不過應該也會有公司就是用下去,在上面跑的好好而且很賺錢的東西就不想亂動...

AWS 推出 RDS for Db2 以及 Amazon Aurora Limitless Database

資料庫的部分,看到 AWS 在這次 re:Invent 放出「Getting started with new Amazon RDS for Db2」與「Join the preview of Amazon Aurora Limitless Database」這兩則消息。

首先是看到 Db2 這個詞覺得怪,查了以後發現原來是 2017 年改過名字正式的拼法:

The brand name was originally styled as DB/2, then DB2 until 2017 and finally changed to its present form.

這個消息比較像是補產品線,最大的資訊其實是查資料知道 2017 年改成 Db2...

另外的新產品 Amazon Aurora Limitless Database 就比較特別了,我翻了一下先前的 Amazon Aurora Serverless,是屬於利用 read replica 拓展「讀」的承載能力:

Amazon Aurora Serverless is an on-demand, autoscaling configuration for Amazon Aurora. It automatically starts up, shuts down, and scales capacity up or down based on your application's needs.

這次在 Amazon Aurora Limitless Database 裡面直接提到 sharding 技巧了,這就有機會拓展「寫」的承載能力:

Shards are Aurora PostgreSQL DB instances that each store a subset of the data for your database, allowing for parallel processing to achieve higher write throughput. Transaction routers manage the distributed nature of the database and present a single database image to database clients.

這次 preview 推出的是 PostgreSQL 15 的版本:

The preview runs in a new Aurora PostgreSQL cluster with version 15 in the AWS US East (Ohio), US East (N. Virginia), US West (Oregon), Asia Pacific (Tokyo), and Europe (Ireland) Regions.

這代表 database schema 不要設計的太差,是蠻有機會讓 RDS 幫你處理 sharding 底層所有需要的細節?等後續價錢出來可以看看... (應該是 open/public preview 或是 open beta 的階段)

PostgreSQL 的 Logical Replication 還有很多限制...

雖然之前提過很多次 PostgreSQL 的 logical replication,但最近總算是有空實際架設起來測試,發現目前的 logical replication 還在進化的過程,只能算是階段性的產品。

PostgreSQL 16 的「31.6. Restrictions」裡面有列出了目前 logical replication 的限制。

第一條其實是最痛的,不支援各種 DDL 操作,所以像是 CREATE TABLE 或是 ALTER TABLE 都不會同步,這牽扯到 DBOps 的動作需要配合,DB schema 的改變會變得很詭異,需要 case by case 處理,甚至 application 端可能也會需要配合。

The database schema and DDL commands are not replicated.

另外一個頭痛的點是 sequence 資料居然不會同步,這個工具常被用到 SERIAL 類的設計 (雖然 SERIAL 被 deprecated 了),這代表當偵測到 master 掛掉時無法直接 failover,除非有另外處理 sequence 的資料:

Sequence data is not replicated.

翻了資料發現官方 wiki 上有「Logical replication of DDLs」,裡面有今年六月的投影片:「Logical Replication of DDLs」,看起來 DDL 的部分有已經 patch 丟出來 (對 PostgreSQL 15 的 patch),但看了 PostgreSQL 16 的 release notes 裡面還沒看到,看起來還要等...

所以 logical replication 看起來還在演進的過程,目前的限制使得 logical replication 還不到能用的成熟度。

可以繼續觀察看看...

PostgreSQL 的 meme 與對應的解釋

看到「Explaining The Postgres Meme」這篇,很努力在解釋 PostgreSQL 的 meme:

這份 meme 的下半部意外的把很多常見的問題都涵蓋進去了,如果要做教材的話好像是個不錯的起點,不過要注意有些不是專屬 PostgreSQL 的 meme 也有被放進去。

裡面有很多都還不熟悉,之前在 MySQL 陣營太久了,很多東西都不會想要讓 MySQL 處理... (或是無法在在 MySQL 處理)

窮舉 PostgreSQL 的 LOCK 機制

PostgreSQL Lock Conflicts 這份資料窮舉了 PostgreSQL 的 lock 機制,分成兩種方式呈現:

  • 依照 Lock:文內列出 12 種 lock。
  • 依照 Command:文內列出了 67 種指令。

可以交叉查,用 lock 查出有哪些 command 有用到,或是反過來用 command 查會產生那些 lock:

This tool shows all commands and locks in postgres. If you select a command, it lists the locks that it acquires, commands that conflicts with it and commands that are allowed to run concurrently with it (with no conflict or blocking). If you select a lock, it lists commands that acquire the lock and what are the other conflicting locks.

舉個例子來說,Lock 列表裡的第一個,AccessShareLock,這個點進去後可以看到有三個指令會有使用到 AccessShareLock 的情境,分別是 SELECTCOPY TO 以及 ALTER TABLE ADD FOREIGN KEY (PARENT)

是個更熟悉 PostgreSQL 的路徑?