MariaDB 嘗試相容於 PostgreSQL 協定的產品

Twitter 上看到的消息,新聞在「MariaDB's Xpand offers PostgreSQL compatibility without the forking drama」這邊:

看起來是 SkySQL 的服務,這樣聽起來不像會 open source... 看起來賣點在於 globally distributed RDBMS 這個部分:

MariaDB is previewing a PostgreSQL-compatible front end in its SkySQL Database-as-a-Service which provides a globally distributed RDBMS on the back end.

再看看後續會不會有更多消息?

測了 PGroonga,PostgreSQL 上的 fulltext search engine

PostgreSQL 的 news 頁上看到「PGroonga 3.0.0 - Multilingual fast full text search」,想到一直沒有測過 PGroonga,就找台機器測了一下。

PGroonga 是以 Groonga 為引擎提供 PostgreSQL 全文搜尋能力的套件,是個能支援 CJK 語系的全文搜尋套件。

可以先看一下支援的 column type 與對應的語法:「Reference manual | PGroonga」,可以發現基本的 texttext[]varcharvarchar[] 都有支援,比較特別的是有 jsonb,看起來是對裡面的 text 欄位搜尋。

另外一個比較特別的是他會去配合 LIKE '%something%' 這樣的語法,對於無法修改的既有程式也會有幫助。

缺點方面,官方有提到產生出來的 index 會比其他的套件大,但畢竟我們在的環境要支援 CJK,場上的選手已經不多了。

另外一個缺點是目前 AWSRDSGCPCloud SQL 看起來都沒支援,要用的話得自己架 & 自己管,也許可以考慮用老方法,replication 接出來?

接下來就是安裝測試了,我在 x86-64 上的 Ubuntu 22.04 上面測試,就照著「Install on Ubuntu | PGroonga」這頁裡面的「How to install for system PostgreSQL」這段就可以了,裝系統的 PostgreSQL 14 以及 postgresql-14-pgroonga,之後要用 PostgreSQL 官方的新版的話可以參考「How to install for the official PostgreSQL」這段的安裝。

後續再到「Tutorial | PGroonga」頁,針對要搜尋的欄位下 index (這邊裱格式 memos,欄位是 content):

CREATE INDEX ON memos USING pgroonga (content);

官方的教學文件裡是用 SET enable_seqscan = off; 關閉 sequence scan,可以用 EXPLAIN 看到使用了 index:

test=# SELECT * FROM memos WHERE content &@ 'engine';
 id |                                content                                 
----+------------------------------------------------------------------------
  2 | Groonga is a fast full text search engine that supports all languages.
(1 row)

test=# EXPLAIN SELECT * FROM memos WHERE content &@ 'engine';
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Scan using memos_content_idx on memos  (cost=0.00..43.18 rows=1 width=36)
   Index Cond: (content &@ 'engine'::text)
(2 rows)

先拔掉 index:

test=# DROP INDEX pgroonga_content_index;
DROP INDEX

接著要塞資料,這邊拿 CQD 生的「中文假文產生器」來用,有 API 可以接比較方便。

test=# SELECT COUNT(*) FROM memos;
 count  
--------
 100000
(1 row)

Time: 15.495 ms

接著多跑幾次測試直接用 LIKE '%台北%' 去找,可以看到大概都在 150ms 以上:

test=# SELECT COUNT(*) FROM memos WHERE content LIKE '%台北%';
 count 
-------
   710
(1 row)

Time: 178.784 ms

接著來建立 index:

test=# CREATE INDEX ON memos USING pgroonga (content);
CREATE INDEX
Time: 17638.124 ms (00:17.638)

再跑幾次同樣的 query,可以看到巨大的改善:

test=# SELECT COUNT(*) FROM memos WHERE content LIKE '%台北%';
 count 
-------
   710
(1 row)

Time: 9.876 ms

把 RabbitMQ 換成 PostgreSQL 的那篇文章...

Hacker News 上看到「SQL Maxis: Why We Ditched RabbitMQ and Replaced It with a Postgres Queue (prequel.co)」這篇文章,原文在「SQL Maxis: Why We Ditched RabbitMQ And Replaced It With A Postgres Queue」這邊,裡面在講他們把 RabbitMQ 換成 PostgreSQL 的前因後果。

文章裡面可以吐嘈的點其實蠻多的,而且在 Hacker News 上也有被點出來,像是有人就有提到他們遇到了 bug (或是 feature) 卻不解決 bug,而是決定直接改寫成用 PostgreSQL 來解決,其實很怪:

In summary -- their RabbitMQ consumer library and config is broken in that their consumers are fetching additional messages when they shouldn't. I've never seen this in years of dealing with RabbitMQ. This caused a cascading failure in that consumers were unable to grab messages, rightfully, when only one of the messages was manually ack'ed. Fixing this one fetch issue with their consumer would have fixed the entire problem. Switching to pg probably caused them to rewrite their message fetching code, which probably fixed the underlying issue.

另外一個吐嘈的點是量的部份,如果就這樣的量,用 PostgreSQL 降低使用的 tech stack 應該是個不錯的決定 (但另外一個問題就是,當初為什麼要導入 RabbitMQ...):

>To make all of this run smoothly, we enqueue and dequeue thousands of jobs every day.

If you your needs aren't that expensive, and you don't anticipate growing a ton, then it's probably a smart technical decision to minimize your operational stack. Assuming 10k/jobs a day, thats roughly 7 jobs per minute. Even the most unoptimized database should be able to handle this.

在同一個 thread 下面也有人提到這個量真的很小,甚至直接不講武德提到可以用 Jenkins 解 XD:

Years of being bullshitted have taught me to instantly distrust anyone who is telling me about how many things they do per day. Jobs or customers per day is something to tell you banker, or investors. For tech people it’s per second, per minute, maybe per hour, or self aggrandizement.

A million requests a day sounds really impressive, but it’s 12req/s which is not a lot. I had a project that needed 100 req/s ages ago. That was considered a reasonably complex problem but not world class, and only because C10k was an open problem. Now you could do that with a single 8xlarge. You don’t even need a cluster.

10k tasks a day is 7 per minute. You could do that with Jenkins.

然後意外看到 Simon Willison 提到了一個重點,就是 RabbitMQ 到現在還是不支援 ACID 等級的 job queuing (尤其是 Durability 的部份),也就是希望 MQ 系統回報成功收到的 task 一定會被處理:

The best thing about using PostgreSQL for a queue is that you can benefit from transactions: only queue a job if the related data is 100% guaranteed to have been written to the database, in such a way that it's not possible for the queue entry not to be written.

Brandur wrote a great piece about a related pattern here: https://brandur.org/job-drain

He recommends using a transactional "staging" queue in your database which is then written out to your actual queue by a separate process.

這也是當年為什麼用 MySQL 幹類似的事情,要 ACID 的特性來確保內容不會掉。

這也是目前我覺得唯一還需要用 RDBMS 當 queue backend 的地方,但原文公司的想法就很迷,遇到 library bug 後決定換架構,而不是想辦法解 bug,還很開心的寫一篇文章來宣傳...

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 支援 LZ4zstd,這對於有寫入量很大的環境應該會有幫助:

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-treeB+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 標準裡面的 SERIALIZABLEREPEATABLE 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) 以及 GitHubgh-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 看起來通用多了:

目前完整測試過的是 MySQLPostgreSQLSnowflake,其他的有實做但還沒完整測試過。

看起來還在開發 (後面是商業公司 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