PostgreSQL

PostgreSQL 15 釋出

PostgreSQL 15 出了：「PostgreSQL 15 Released!。

先前提到過「PostgreSQL 15 將可以對透過 UNIQUE 限制 NULL 的唯一性了」，反而沒排上這次 release 的重點，翻了一下的確是排不太上 XD

第一個超大的改善是 sorting：

In this latest release, PostgreSQL improves on its in-memory and on-disk sorting algorithms, with benchmarks showing speedups of 25% - 400% based on which data types are sorted.

在「Speeding up sort performance in Postgres 15」這邊有提到四個改動，裡面很詳細的說明了改動的內容，以及 benchmark 差異。

如果以他列出來的四個進展，應該是第二個「Reduce memory consumption by using generation memory context」這個會最容易遇到，也改善最多：

另外是第三個「Add specialized sort routines for common datatypes」也會有一些：

再來是拿 PostgreSQL 當 OLAP engine 用的時候會發生的第四個「Replace polyphase merge algorithm with k-way merge」：

最開頭第一個「Improvements sorting a single column」的 SELECT col1 FROM tab ORDER BY col1; 這種 case 好像用的很少，限制 SELECT 的部份也只能出現後面 sorting 的 column，但如果遇到的話效能提昇很多：

除了 sorting 的改善以外，另外一個是 WAL 支援 LZ4 與 zstd，這對於有寫入量很大的環境應該會有幫助：

PostgreSQL 15 adds support for LZ4 and Zstandard (zstd) compression to write-ahead log (WAL) files, which can have both space and performance benefits for certain workloads.

正式版出來後，應該會有一些整體性的 benchmark 數字可以看，再來等著看...

OxideDB：另外一套用 PostgreSQL 為底的 MongoDB 相容層

看到 OxideDB 這個專案：

OxideDB is a translation layer that works as a MongoDB database server while using PostgreSQL's JSON capabilities as the underlying data store.

跟之前提到的 MangoDB 有些淵源 (參考「MangoDB：拿 PostgreSQL 當作後端的 MongoDB 相容層」)，順便提一下 MangoDB 後來被要求改名為 FerretDB：「MangoDB 改名為 FerretDB (雪貂)」。

主要的差異在於 OxideDB 只以 PostgreSQL 為底層，另外是用 Rust 寫的：

The project was heavily inspired by FerretDB and is on its early days. The main difference is that there is no intention to support any database other than PostgreSQL (FerretDB is also supporting Tigris) and it's written in Rust, as opposed to Go.

看起來大家都拿 PostgreSQL 在搞事，但這個專案裡面好像沒搜到 GIN 這個關鍵字，不知道是不是連 index 都沒下...

又一份講基本 RDBMS 的文件

前幾天在 Hacker News Daily 看到「Things You Should Know About Databases」這篇文章，裡面講了很多基本的 RDBMS 的概念，另外 Hacker News 上對應的討論在「Things to know about databases (architecturenotes.co)」這邊。

裡面講了 B-tree 與 B+tree 的差異：

不過這點在維基百科上也蠻清楚的文字說明：

A B+ tree can be viewed as a B-tree in which each node contains only keys (not key–value pairs), and to which an additional level is added at the bottom with linked leaves.

另外裡面的 sorted 的那張圖：

這邊的說明不完全正確，在維基百科上的「Database index」這個條目裡面有提到 Non-clustered、Clustered 與 Cluster 三種架構，這邊圖片所表示的是 Non-clustered。在 InnoDB 裡面 data 是照 primary key 順序存放的 (沒有指定時會有一套邏輯選出哪個欄位當 PK，最後的情況是有 hidden key)。

再來就是提到 isolation，這邊也講的比較淺，只提到 ANSI 標準裡面的 SERIALIZABLE、REPEATABLE READ (RR)、READ COMMITTED (RC) 與 READ UNCOMMITTED (RU) 四個，但沒提到像是 SNAPSHOT ISOLATION (SI) 這類的也很常見的標準。

說到 SI，在查 Snapshot isolation 的資料時整理了一下 PostgreSQL 的混亂情況。

在 PostgreSQL 9.0 以及更早前的版本，你指定 SERIALIZABLE 其實只有做到 Snapshot isolation 的等級，到了 9.1+ 後，SERIALIZABLE 才是真正做到 ANSI 定義的強度：

Snapshot isolation is called "serializable" mode in Oracle and PostgreSQL versions prior to 9.1, which may cause confusion with the "real serializability" mode.

另外 ANSI 定義的 isolation level 很難「用」 (但還是值得學起來，算是基本的東西)，實際上的使用都是看各家資料庫對 isolation level 的保證程度來設計。

PostgreSQL 上對應 pt-online-schema-change 的工具 pg-osc

翻資料的時候翻到「pg-osc: Zero downtime schema changes in PostgreSQL」這篇文章，可以在 PostgreSQL 上做到類似 pt-online-schema-change 的事情，這邊先提一下 pt-online-schema-change 的說明：

ALTER tables without locking them.

不管是 MySQL 還是 PostgreSQL，都會遇到 ALTER TABLE 常常會 lock 的問題，這點主要的影響就是 db migration。

在 dev 環境的機器應該沒什麼問題，資料量都不大，應該是很快就可以跑完；但在 stage 環境時就會開始有狀況了 (假設是從 production 複製過來的資料，表格的大小可能偏大)，但應該還是可以用 downtime 換，慢慢跑，花幾個小時把 db migration 跑完。

可是到了 production 環境時就不太能這樣搞了，這也是一般不太建議在 production 環境裡用現成的 db migration 工具，尤其當資料量偏大的時候。

解這個問題的方法就是透過繞路的方式，不要直接動原來的 table：基本的想法是開一個新的 table，然後一直從舊的 table 搬資料到新的 table 上 (包括應用程式下指令寫到舊的 table 上的資料)，直到最後用一個短暫的 lock 機制來切換 table。

在 MySQL 的世界裡比較有名的是 Percona 的 pt-online-schema-change (trigger-based) 以及 GitHub 的 gh-ost (replication-based)，另外找資料的時候有發現 Facebook 也有丟 OnlineSchemaChange (trigger-based) 出來。

在 PostgreSQL 的世界裡似乎是 pg_repack 這個方案，用了 trigger-based 的方式處理，但之前沒有注意到，是翻 pg-osc 的時候被提到才知道有這個工具。

而這次提到的 pg-osc 則是 2022 年才出的軟體，也是 trigger-based 的方式：

pg-osc uses the concept of shadow tables to perform schema changes. At a high level, it creates a shadow table that looks structurally the same as the primary table, performs the schema change on the shadow table (avoiding any locks since nothing is using this table), copies contents from the primary table to the shadow table and swaps the table names in the end while preserving all changes to the primary table using triggers (via audit table).

另外從 PostgreSQL 的 wiki 上看到「Change management tools and techniques」這頁，裡面看到「Metagration: Logical PostgreSQL Migration」這個工具，看起來好像是 replication-based 的方案，不過還是有用到一些 trigger 做事。

這些方案都先記錄起來好了...

比對兩個表格 (可以是不同的資料庫) 的內容，指出差異處

前幾天看到的東西，不確定是不是在 Hacker News 上，反正在 tab 上幾天了... 但還是附上 Hacker News 的連結：「Show HN: Data Diff – compare tables of any size across databases」，專案的位置在 GitHub 上的 datafold/data-diff。

這是用 Python 寫的工具，安裝可以透過 pip 直接裝，所以也可以用 pipx 之類的工具獨立起來跑。

data-diff 會先拆成多個區塊，然後透過 checksum 的機制判斷兩邊的資料是否相同，不同的部份再取 bisection 分開下去找 (或是更多份，在 Technical Explanation 這個段落有寫到)。

在「Common use-cases」這段有提到幾個常見的使用情境，像是在自動化的環境下可以當作異常監控的工具：

Alerting and maintaining data integrity SLOs. You can create and monitor your SLO of e.g. 99.999% data integrity, and alert your team when data is missing.

另外在 troubleshooting 的情境下當然也很有幫助，可以先確認資料是否有問題，以及資料的哪邊出問題：

Debugging complex data pipelines. When data gets lost in pipelines that may span a half-dozen systems, without verifying each intermediate datastore it's extremely difficult to track down where a row got lost.

這個工具讓我想到 Percona Toolkit 裡面的 pt-table-checksum，不過 pt-table-checksum 只能處理 MySQL replication 的情境，data-diff 看起來通用多了：

目前完整測試過的是 MySQL、PostgreSQL 與 Snowflake，其他的有實做但還沒完整測試過。

看起來還在開發 (後面是商業公司 Datafold)，但先寫下來，之後如果有用到的時候可以回頭看看進展...

GCP 推出 AlloyDB，一套相容 PostgreSQL 協定的資料庫服務

也是在清 RSS reader 的時候翻到的，看起來是在今年的 Google I/O 上發表的服務，AlloyDB：「AlloyDB for PostgreSQL under the hood: Intelligent, database-aware storage」，值得提的是這篇有中文版可以看：「適用於 PostgreSQL 的 AlloyDB 隆重登場：從此擺脫成本高昂的老舊資料庫」。

另外還有一篇比較偏 PR 的文章也可以看看：「Introducing AlloyDB for PostgreSQL: Free yourself from expensive, legacy databases」，這篇就比較針對的提到了與 AWS 的服務相比，但畢竟是 PR 稿沒有明講 (出事會比較好打模糊戰)，但我猜測是與 Aurora 對比：

AlloyDB was also two times faster for transactional workloads than Amazon’s comparable service.

宣稱在 OLTP 上快了兩倍 (原來的三倍？)，但應該都是以 PostgreSQL 下去改，猜測可能是底層的 storage 與 replication 比較好？

AlloyDB 設計上是考慮了 HTAP (Hybrid transactional/analytical processing) 的使用，所以同時可以提供 OLAP 與 OLTP 的應用：

[...] This makes AlloyDB a great fit for business intelligence, reporting, and hybrid transactional and analytical workloads (HTAP).

直接在一個資料庫內處理 OLAP 與 OLTP 這點的確會讓 AlloyDB 比 AWS 目前能提供的方案方便不少 (然後想一下 BigQuery 團隊...)。

目前在 AWS 對應的方案應該是透過 Redshift 來解決，另外一個方案是透過 Athena 來跑。

最後來看價錢，如果效能變成兩倍但價錢也是兩倍的話，就代表在價格上沒優勢。

先看機器的部份，如果是拿 Aurora 這邊 Intel-based 的 db.r5.24xlarge (96 vCPU + 768 GB RAM) 來算的話是 US$13.92/hr，而如果換算到 AlloyDB 的話是 US$14.94528/hr，相除是 0.9314，大約 7% 的差距，可以算是同一個級距。

如果 Aurora 這邊是拿 ARM-based 的 db.r6g.16xlarge (64 vCPU + 512 GB RAM) 來算的話是 US$8.306/hr，換算到 AlloyDB 的話是 US$9.96352/hr，相除是 0.8336，這邊就差超過 16% 了...

(這邊剛好回顧一下 "Amazon’s comparable service" 這段，不確定他是跟 Intel-based 比還是跟 ARM-based 比，畢竟 ARM 除了比較便宜外，還有效能的提昇)

但最大的差異應該是在 storage 相關的部份。其中 Aurora 這邊的空間與 I/O 是分開收費的，以 us-east-1 來說，storage 是 US$0.10/GB/mo，而 I/O 是 US$0.20/million-requests，在 AlloyDB 這邊來說，Regional cluster storage 是 US$0.0004109/GB/hr (us-east4)，變成是 US$0.295848/GB/mo，兩邊相比後可以算出來對等的計價會是 AWS 的 storage 加上 AWS 給你 1.47M 的 I/O (per GB)。

這樣算起來把資料丟 S3 跑 Athena 可能不會比較貴... (當然效能是另外的主題了)

光就檯面上的資料來看，看起來是個不錯的東西，等後續有人跳進去用看看感想...

Amazon RDS for PostgreSQL 可以掛 155 台 Read Replica

看到 AWS 推出的新「功能」，可以讓 Amazon RDS for PostgreSQL 的 read replica 掛到 155 台：「Amazon RDS for PostgreSQL supports cascaded read replicas for up to 30X more read capacity」。

作法是透過三層架構，每台機器可以堆五台 replica：

Amazon Relational Database Service (Amazon RDS) for PostgreSQL announces support for PostgreSQL 14 with three levels of cascaded read replicas, 5 replicas per instance, supporting a maximum of up to 155 read replicas per source instance.

需要 PostgreSQL 14.1 或是之後的版本：

Starting with Amazon RDS for PostgreSQL 14.1 and higher, read intensive workloads such as data analytics can now benefit from up to 155 cascaded read replicas that offer up to 30 times higher read capacity versus previous versions of PostgreSQL, thereby reducing the load on source instance.

我記得 Amazon RDS for PostgreSQL 的 replica 是 EBS block-level replication，這種搞法還蠻有趣的 XDDD

在 PostgreSQL 上直接掛 ML extension

Hacker News 首頁上看到「Show HN: PostgresML, now with analytics and project management (postgresml.org)」這個專案，可以在 PostgreSQL 上面直接掛 extension 跑 ML algorithm：「PostgresML - an end-to-end machine learning solution」，從 GitHub 上可以看到大多數是 Python 的程式碼。

從 GitHub 頁面上面可以看到這個專案還在比較早期的階段：

This project is currently a proof of concept. Some important features, which we are currently thinking about or working on, are listed below.

如果是目前要用的話，主要是方便看一些東西吧？可以想到的是掛個 replication 出來跑一些 query，這樣不會影響到 production database 的效能，應該還行...

另外看了一下支援的演算法，主要是以經典的 ML 演算法為主，而且就是套用 Python 上面的套件：XGBoost 與 scikit-learn。

這些演算法算是很好用了，而且掛到 PostgreSQL 裡面會讓使用上方便很多 (少了倒資料的動作，不過就得小心處理 dirty data 了)，然後專案也附上一個 UI 界面可以看一些資料，不過我猜還是用其他生 visualization 的工具會比較豐富一點：

另外一個想法是拿來學習還不錯？老師在上課的時候拿來示範一些演算法，就不用自己再刻很多程式碼...

低 Downtime 將 4TB 的 PostgreSQL 9.6 資料庫升級到 13 的故事

前幾天在 Hacker News 首頁上看到的文章，講怎麼把一個 4TB 的 PostgreSQL 從 9.6 升級到 13 的故事：「How we upgraded our 4TB Postgres database (retool.com)」，原文在「How Retool upgraded our 4 TB main application PostgreSQL database」，翻了一下 LinkedIn，這篇文章的作者 Peter Johnston 在 Retool 掛的是 Security Software Engineer，另外他也有在 Hacker News 上的討論出現 (帳號是 mrbabbage)，可以搜尋翻翻看他的回覆。

看完文章後發現方法的概念其實不難，主要是要找到對的工具來用。基本的想法是先生出一個 initial dump，然後架構 logical replication，接下來就是處理各種因為在 4TB data 這個 scale 下會遇到的問題。

主要用到的工具是 Citus Data 的 Warp：「Citus warp: Database migrations without the pain」，不過這個工具的限制是表格必須都有 single column primary key，所以他們為了這次轉移也有小改 database schema 配合 Warp 的要求：

We had to do a bit of finagling to coax Warp into processing our database. Warp expects all tables to have a single column primary key, so we had to convert compound primary keys into unique constraints and add scalar primary keys. Otherwise, Warp was very straightforward to use.

另外針對比較大的兩個 append-only 的表格 (分別是 2TB 與 x00 GB) 做處理，在 initial dump 的階段不對這兩個表格做 replication，而是透過自製的 Python script 搬移：

To handle the two massive tables we skipped in Warp, we wrote a Python script to bulk transfer data from the old database server to the new.

然後是 foreign key 相關的關閉與重啟，這算是 RDBMS 在大量資料的 dump & restore 的標準作業了：

As you can see from the runbook above, one of the steps we had to do was to turn off and then re-enable foreign key constraint checks.

最後執行下去，整個 downtime 只有十五分鐘：

We scheduled the maintenance window late on Saturday, October 23rd, at the lowest period of Retool cloud traffic. With the configuration described above, we were able to bring up a new database server at version 13 in around 15 minutes, subscribed to changes at our 9.6 primary with logical decoding.

另外也有提到有計畫要 sharding，之後 main database 就有機會被拆小：

We anticipate we’ll have sharded our database by the end of that support window, and be performing our next substantial version upgrades incrementally.

整個計畫的核心概念不難，主要是要怎麼順出來並且執行...

Linode 過了三年，終於想起來要推出 Managed Databases 服務了

看到 Linode 宣佈 Managed Databases 服務：「Linode Managed Databases in Open Beta」。

測試期間不用錢，但目前只有支援 MySQL，其他幾個像是 PostgreSQL、Redis 與 MongoDB 都還沒推出 (在 roadmap 上)：

Our new managed database service is now in open beta for new and existing customers! We currently support MySQL during this beta—with a near-term roadmap to add PostgreSQL, Redis, MongoDB—and plan to include additional features.

搜了一下隔壁 DigitalOcean 的資料，Manage Databases 這條產品線在 2019 年二月推出 PostgreSQL 的版本：「Our Valentine’s Gift to You: Managed Databases for PostgreSQL」，在 2019 年八月推出了 MySQL 與 Redis 的版本：「Take the worry out of managing your MySQL & Redis databases」，然後在 2021 年六月推出了 MongoDB 的版本：「Introducing DigitalOcean Managed MongoDB – a fully managed, database as a service for modern apps」。

不過 Vultr 看起來是還是完全沒有樣子，相比起來 Linode 好像不算慢？

另外看起來 DigitalOcean 是跟 MongoDB 合作，不像 AWS 自己另外用 PostgreSQL 搞了一套 XDDD