在 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 類的,但年代不同了...?

Peter Zaitsev 抱怨 Oracle 對 MySQL 的態度

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

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

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

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

MySQL 8.4.0 出版

這是 LTS 版本,可以預期後續 Percona 也會準備對應的 distribution 使用。

先查了一下 MySQL 8.0 的 EoL 日期,目前是訂在 2026/04/30,大約還有兩年的時間可以準備。

回來看 8.4,從「What Is New in MySQL 8.4 since MySQL 8.0」這邊可以看到官方整理與 8.0 的差異。

InnoDB 這邊有不少預設參數調整,算是配合現在硬體愈來愈好而調整的數字。

在「MySQL Replication: tagged GTIDs」這邊有提到 replication 時用到的 GTID format 有更新,多了 tag 的欄位。看起來在 upgrade 時會相容舊格式,但要 rollback 或是 downgrade 時得注意到這邊的問題...

然後在「Replication SQL syntax」這邊有提到移除掉很多舊語法,像是 CHANGE MASTER TORESET MASTER 這種語法都被改成新的語法了,要重新背。

整體看起來沒有太多變化?

關於 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 的設計有他當時的限制以及想法,這些是外面的人看不到的,也就不好批評對錯。

SQLite 官方提供的網頁版 playground

Hacker News 上看到「Sqlite3 Utility in the Browser (sqlite.org)」這個,看了一下是官方提供的 playground:「SQLite3 Fiddle」。

https://sqlite.org/fiddle/fiddle.js 這邊可以看到 2022 年就有的東西,在 Internet Archive 上也可以看到也是差不多時間被記錄下來的:「Saved 21 times between August 12, 2022 and January 24, 2024.」。

看起來是用 WebAssembly 包起來的,不過如果是自己的機器,本機跑 sqlite3 好像會方便一些...

MariaDB 11.4.0 preview release?

現在應該是西方年底的假期期間,意外的看到 MariaDB 貼出 11.4.0 preview release 的消息 (當然也有可能是排程發表?):「MariaDB 11.4.0 preview release now available」。

MariaDB 在十月的時候宣佈停掉一些周邊的產品線,「聚焦」回資料庫本體的消息後 (參考「MariaDB 停掉 SkySQL 與 Xpand」),這兩個月累積出來的東西其實不算太多,以這次文章理列出來的來看,主要是以 replication 相關的功能為主。

另外翻了一下維基百科上的資料,MariaDB 最新的 LTS 是 10.11,支援到 2028 年二月。

目前 MariaDB 還是個很微妙的狀態,如果就 engine 來說,重新聚焦有機會拿出跟 InnoDB 可以抗衡的技術嗎?或是在 cluster 以及 sharding 拿出比較奇特的技術?

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

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

Jepsen 回過頭來測試 MySQL 8.0

Hacker News 上看到作者自己貼的:「Jepsen: MySQL 8.0.34 (jepsen.io)」,原文在「MySQL 8.0.34」。

這次的測試不是 Oracle 付費讓 Jepsen 測,而是 Jepsen 這邊自己回頭測試 MySQL 8.0:

This work was performed independently without compensation, and conducted in accordance with the Jepsen ethics policy.

然後意外的流彈 (或是榴彈?) 打下了 AWSRDS,測出 RDS 在 cluster 模式下無法達到 SERIALIZABILITY

As a lagniappe, we show that AWS RDS MySQL clusters routinely violate Serializability.

然後 MySQL 本體則是找到 REPEATABLE-READ (預設 isolation level) 的問題:

Using our transaction consistency checker Elle, we show that MySQL Repeatable Read also violates internal consistency. Furthermore, it violates Monotonic Atomic View: transactions can observe some of another transaction’s effects, then later fail to observe other effects of that same transaction. We demonstrate violations of ANSI SQL’s requirements for Repeatable Read.

文章的前面一大段在寫歷史,解釋 ANSI 當初的 SQL 標準在定義 isolation level 時寫的很差,導致有很多不同的解讀,而且即使到了 SQL:2023 也還是沒有改善。

接著則是提到各家資料庫宣稱的 isolation level 跟 ANSI 定義的又不一樣的問題... (包括了無論怎麼解讀 ANSI 定義的情況)

不過中間有提到 1999 年 Atul Adya 試著正式定義 isolation level,把本來的四個 isolation level 用更嚴謹的方法重新給出相容的定義,這看起來是作者推薦在一般狀況下的替代方案:

In 1999, Atul Adya built on Berenson et al.’s critique and developed formal and implementation-independent definitions of various transaction isolation levels, including those in ANSI SQL. As he notes[.]

這四個會是 PL-1 對應到 READ UNCOMMITTEDPL-2 對應到 READ COMMITTEDPL-2.99 對應到 REPEATABLE-READ,以及 PL-3 對應到 SERIALIZABILITY;而其中 PL-2.99REPEATABLE-READ 在後面也會重複出現多次。

這次比較意外是在單機上找出問題來,至於 RDS 的部分反倒不是太意外,因為知道 AWS 在底層做了不少 hack,總是會有些 trade off 的?

GitHub 的 MySQL 5.7 升級到 8.0 的細節

GitHub Blog 上面寫了一篇關於 GitHub 怎麼把 MySQL 5.7 升級到 8.0 的過程,有點長度但是裡面有蠻多數字與架構可以看:「Upgrading GitHub.com to MySQL 8.0」。

開頭先順便提一下,看這篇後可以交叉看 GitHub 的 Incident History,有幾次跟 database 有關的事件,雖然不能直接確認與這波升級有關,但心裡可以有個底...

數字與時間的部分主要是這些:

Our fleet consists of 1200+ hosts. It’s a combination of Azure Virtual Machines and bare metal hosts in our data center.

We store 300+ TB of data and serve 5.5 million queries per second across 50+ database clusters.

Preparation for the upgrade started in July 2022 and we had several milestones to reach even before upgrading a single production database.

另外雖然沒有明講,但從文章中其他段落的描述,以及相關的圖片,可以看出來 GitHub 是使用 single-primary (single-master) 的架構,這邊沒有用到 multi-primary (multi-master) 類的架構:

We opted not to do direct upgrades on the primary database host. Instead, we would promote a MySQL 8.0 replica to primary through a graceful failover performed with Orchestrator.

後續升級的部分有點長,第一波關於 read-only replica 的部分雖然有些地方沒講清楚,但基本上大家的作法都大同小異:

比較明顯有疑問的是,第一步為什麼不是直接生一台新的 8.0 觀察 (這樣觀察到的環境才會與後續過程接近),而是 in-place upgrade,而後續開的機器又是 provision。不過這個算是小問題...

比較值得研究的是在第二步與第三步的說明裡面提到的 primary (master) 這塊。

第二步是先改變 topology,這個架構算是蠻特別的的過渡架構,只會維持幾個小時;會把其中一台 8.0 replica 拉起來放在中間,然後再串一台 5.7 replica,接下去再串 5.7/8.0 的 read-only replicas:

第三步把 primary (master) 指到 8.0 上:

這個特別的架構可以推敲出來是想要能夠快速在有狀況時完全 rollback 回 5.7,不過可以馬上想到 8.0 的資料丟到 5.7 上的問題。

MySQL 的慣例是下一個版本的 replication 通常都會通 (像是 5.0 -> 5.1,或是 5.1 -> 5.5,而這邊的例子是 5.7 -> 8.0),這在官方的文件「Replication Compatibility Between MySQL Versions」有提過。

但反過來就不一定了,這也是看到圖時馬上會想到的問題,在文章裡面也有提到:

MySQL supports replication from one release to the next higher release but does not explicitly support the reverse (MySQL Replication compatibility).

所以他們只能在 staging 上演練看看,找出會炸掉的東西,然後得提前先修改完:

When we tested promoting an 8.0 host to primary on our staging cluster, we saw replication break on all 5.7 replicas.

另外一方面,在文章開頭的地方也有提到利用 CI 事先找出問題:

We added MySQL 8.0 to Continuous Integration (CI) for all applications using MySQL. We ran MySQL 5.7 and 8.0 side-by-side in CI to ensure that there wouldn’t be regressions during the prolonged upgrade process. We detected a variety of bugs and incompatibilities in CI, helping us remove any unsupported configurations or features and escape any new reserved keywords.

用這些方法儘量把問題圍堵找出來,而真的遇到在 production 上的問題時,應該是看情況來決定要不要 rollback 回 5.7 整包重來?

就... 看看當作一個有趣的 case study。

MySQL 5.7 已經 EoL

查資料發現忘記這件事情了... 先前就有寫過 MySQL 5.7 到今年十月就 EoL 了:「MySQL 5.7 的支援只到今年十月 (Oct 2023)」。

最後一版是 2023/10/25 釋出的 5.7.44:「Changes in MySQL 5.7.44 (2023-10-25, General Availability)」。

MySQL 5.7.44 is the final release of the MySQL 5.7 series.

然後 MySQL 8.1 也是十月 EoL。

雖然目前已經有 MySQL 8.2 了,但 MySQL 8.0 是 LTS,目前預定支援到 2026 年四月 (大約還有兩年多),所以除非有需要用到 MySQL 8.2 的新功能或是特性,不然應該還是會先繼續用 MySQL 8.0...

然後翻了一下 Percona Server for MySQL,看起來還是沒有提供 ARM 的 binary 可以裝,所以在 ARM 上面的機器比較方便的還是裝 MariaDB 了...