PostgreSQL 的 Job Queue、Application Lock 以及 Pub/Sub

Hacker News Daily 上看到一篇講 PostgreSQL 做 Job Queue、Application Lock 以及 Pub/Sub 的方法:「Do You Really Need Redis? How to Get Away with Just PostgreSQL」,對應的討論在「Do you really need Redis? How to get away with just PostgreSQL (atomicobject.com)」這邊可以翻到。

拿 PostgreSQL 跑這些東西的確有點浪費,不過如果是自己的專案,不想要把 infrastructure 搞的太複雜的話,倒是還不錯。

首先是 Job Queue 的部份,從他的範例看起來他是在做 async job queue (不用等回傳值的),這讓我想到很久前寫的 queue service (應該是 2007 年與 2012 年都寫過一次),不過我是用 MySQL 當作後端,要想辦法降低 InnoDB 的 lock 特性。

async job queue 設計起來其實很多奇怪的眉角,主要就是在怎麼處理失敗的狀態。大多數的需求可以放到兩個種類,最常見用的是 at-least-once,保證最少跑一次,大多數從設計上有設計成 idempotence 的都可以往這類丟,像是報表類的 (重複再跑一次昨天的報表是 OK 的),另外每天更新會員狀態也可以放在這邊。

另外少見一點的是 at-most-once 與 exactly-once,最多只跑一次與只跑一次,通常用在不是 idempotence 的操作上,像是扣款之類的,這邊的機制通常都會跟商業邏輯有關,反正不太好處理...

第二個是 Application Lock,跨機器時的 lock 機制,量沒有很大時拿 PostgreSQL 跑還行,再大就要另外想辦法了,馬上想到的是 ZooKeeper,但近年設計的系統應該更偏向用 etcdConsul 了...

最後提到的 Pub/Sub,一樣是在量大的時候拿 PostgreSQL 跑還行,更大的時候就要拿 Kafka 這種專門為了效能而設計出來的軟體出來用...

Amazon RDS 上 PostgreSQL 的不停機升級

Hacker News Daily 上看到「Zero downtime Postgres migration, done right」這篇,講 PostgreSQL 9.5 不停機的前提下升級到 12.5 的方式,而且是在 Amazon RDS 上:

We have successfully used this process to migrate our Postgres databases from version 9.5 to 12.5 on RDS, but the process isn’t restricted to RDS only, and does not depend on anything AWS specific.

然後整個重點就在一張圖:

其實幾個 open source database 在這塊的基本概念都類似,用 replication 的技巧升級。

這邊作者選的是用 Bucardo 同步資料,然後舊的與新的 replication 都是雙向的,這樣在切換應用程式的時候就比較不會有時間差的問題。

這邊值得說的是,PostgreSQL 10 (2017 年十月出) 之後因為有了 logical replication,這種不停機持續性的 replication 選擇就變多了,不一定要用 trigger-based replication。作者這邊應該是因為 PostgreSQL 9.5 的關係,所以需要挑了 Bucardo。

另外一個重點是,如果你可以允許短時間的停機 (十分鐘之類的),那就可以改用單向的 replication 升級。因為你可以先停掉舊的資料庫,確保所有的資料都已經更新到新的資料庫,再把應用程式切換到新的資料庫上。

而這套方法如同作者說的,不限於 AWS 家的產品,其他家也可以使用類似的方法,在傳統實體機房也可以這樣做。

另外在 Hacker News 上的討論「Zero downtime Postgres migration, done right (theblueground.com)」很正常的又在戰 MySQL 在這塊的成熟度好太多,看看就好 XDDD

PostgreSQL 上可以列出建議增加 index 的 extension

Twitter 上看到 Percona 家老大貼他們自家 2019 年的舊文章:「Automatic Index Recommendations in PostgreSQL using pg_qualstats and hypopg」。

先說一下,因為要另外裝 extension,目前 Amazon RDS 上面是沒辦法用的,但自己管理的 PostgreSQL 就可以考慮看看。

看了一下文章裡面介紹的兩個 extension,一個是 HypoPG,另外一個是 pg_qualstats,看起來組合技可以提供不少有用的建議。

目前只有針對 B-tree index 提供建議,但這應該已經相當足夠了,大多數的場景下應該都是用 B-tree:

With this experiment, we see that we can use hypopg and pg_qualstats to automate index recommendations. The automation logic is currently limited to B-Tree Indexes only.

另外看 Percona 的 PostgreSQL 文章都會習慣翻了一下作者的背景 (之前被雷過),看起來作者 Avinash Vallarapu 在今年年初已經離開 Percona,成立自己的公司 MigOps 提供 PostgreSQL 相關的服務了...

Dolt,本機開發測試用的 MySQL server

看到「Dolt is Git for Data!」這個專案,是個在本機上跑的 MySQL server,另外可以在上面的資料進行版本控制,看起來很適合本機開發測試。

首先抓下來可以看到沒幾個檔案 (這是 linux-amd64 版),也可以看到跟 Git 的關係:

$ tree
.
├── bin
│   ├── dolt
│   ├── git-dolt
│   └── git-dolt-smudge
└── LICENSES

然後用 bin/dolt sql-server -P 3307 -u root -p passw0rd 跑就可以把一個相容於 MySQL 的伺服器跑在 port 3307,然後用 mysql -h 127.0.0.1 --port 3307 -u root -p 就可以輸入密碼 passw0rd 登入進去:

$ mysql -h 127.0.0.1 --port 3307 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess

可以從 Server version 看到專案是用了 Vitess 實做的 MySQL 界面。

另外測了一下,透過連線所做的變更 (像是 CREATE DATABASECREATE TABLE,以及 CRUD 中的 CUD) 是不會寫回磁碟裡的,嘗試了不同的設定,不管改什麼都是這樣,應該是故意設計成這樣。

在本機跑 test case 測試應該還不錯,會比 SQLite:memory: 更接近 MySQL 一些,不過在 CI 裡的話應該是可以直接把 MySQL 跑起來...

PostgreSQL 的 Fuzzy Matching

在「Fuzzy Name Matching in Postgres」這邊看到 PostgreSQL 下怎麼設計 Fuzzy Matching 的方式,文章裡用的方法主要是出自 PostgreSQL 的文件:「F.15. fuzzystrmatch」。

文章最後的解法是 Soundex + Levenshtein

翻了一下資料,這個領域另外有 NYSIIS (New York State Identification and Intelligence System):

The New York State Identification and Intelligence System Phonetic Code, commonly known as NYSIIS, is a phonetic algorithm devised in 1970 as part of the New York State Identification and Intelligence System (now a part of the New York State Division of Criminal Justice Services). It features an accuracy increase of 2.7% over the traditional Soundex algorithm.

以及 Metaphone

Metaphone is a phonetic algorithm, published by Lawrence Philips in 1990, for indexing words by their English pronunciation. It fundamentally improves on the Soundex algorithm by using information about variations and inconsistencies in English spelling and pronunciation to produce a more accurate encoding, which does a better job of matching words and names which sound similar. As with Soundex, similar-sounding words should share the same keys. Metaphone is available as a built-in operator in a number of systems.

不過這些都是以英文為主,中文的沒特別翻到...

PostgreSQL 的 scale 建議

Hacker News Daily 上看到「Postgres scaling advice for 2021」這篇,講 PostgreSQL 要怎麼 scale,在 Hacker News 上也有對應的討論可以看:「Postgres scaling advice (cybertec-postgresql.com)」。

文章前面先提到分散式系統的複雜度會導致 RDBMS 上的一些假設失效,所以如果可以用單台機器暴力解,就儘量用單台機器來解 (scale up 的情境),裡面就提到了一些「暴力可以解決很多問題」的說明,差不多就是前幾天提到的「Let's Encrypt 升級資料庫伺服器 (AMD YES?)」。

後面提到如果真的要放進分散式的 RDBMS (scale out 的情境),怎麼設計資料結構會比較好。

這邊剛好也可以提一下,量夠大的時候要把 OLTPOLAP 的應用分開,現在有很多 OLAP 資料庫可以選擇,同步的工具也很成熟了,通常效能會比在 OLTP 上面硬跑來的好。

最後提一下,文章裡面對於 transaction per second 可以拉很高,有些假設沒有明寫出來。這需要盡可能把 transaction 拆小,避免常常有 giant transaction 卡住整個資料庫,這點對於一般的系統會需要做不少改寫...

不過最後比較疑惑的是,這種文章怎麼會上 Hacker News 的啊...

用 GPT-3 直接產生對應的 SQL query

Hacker News Daily 上看到的東西,直接給 GPT-3 條件,叫 GPT-3 給出對應的 SQL query,這就有點誇張了...:「Automating My Job with GPT-3」。

這樣丟進去:

Instruction: Given an input question, respond with syntactically correct PostgreSQL. Be creative but the SQL must be correct.

Input: how many users signed up in the past month?

然後這樣出來:

GPT-3 Response: SELECT COUNT(*) FROM users
WHERE signup_time > now() - interval '1 month'

把欄位名稱替換掉就可以用了... 而且接下來就更誇張了,直接要求 GPT-3 也要照著設定的 table schema 給答案:

Instruction: Given an input question, respond with syntactically correct PostgreSQL. Be creative but the SQL must be correct. Only use tables called "users" and "charges". The "users" table has columns: id (integer), signup_dt (timestamp), email (character varying), and plan_type (character varying). The "charges" table has columns: amount (bigint), user_id (integer), and charge_dt (timestamp).

Input: how much revenue did we have in the past 7 days?

然後輸出了:

GPT-3 Response: SELECT SUM(amount) FROM charges WHERE charge_dt > now() - interval '7 days'

接下來是在同樣 instruction 下,跨表格的問題:

Input: how much revenue have we had from users that signed up in the last 6 months?

這時候 INNER JOIN 就跑出來了:

.8 Temperature GPT-3 Response: SELECT SUM(charges.amount) FROM users INNER JOIN charges ON users.id = charges.user_id WHERE signup_dt >= DATE_SUB(now(), INTERVAL '6 months')

後面的問題也很精彩,看起來之後可以接上 BI dashboard,直接丟句子進去,然後拉各種資料出來視覺化?

Eventbrite 的 MySQL 升級計畫

在 2021 年看到 EventbiteMySQL 升級計畫:「MySQL High Availability at Eventbrite」。

看起來是 2019 年年初的時候 MySQL 5.1 出問題,後續決定安排升級,在 2019 年年中把系統升級到 MySQL 5.7 (Percona Server 版本):

Our first major hurdle was to get current with our version of MySQL. In July, 2019 we completed the MySQL 5.1 to MySQL 5.7 (v5.7.19-17-log Percona Server to be precise) upgrade across all MySQL instances.

然後看起來是直接在 EC2 上跑,不過這邊提到的空間問題就不太確定了,是真的把 EBS 的空間上限用完嗎?比較常使用的 gp2gp3 上限都是 16TB,不確定是不是真的用到接近爆掉了:

Not only was support for MySQL 5.1 at End-of-Life (more than 5 years ago) but our MySQL 5.1 instances on EC2/AWS had limited storage and we were scheduled to run out of space at the end of July. Our backs were up against the wall and we had to deliver!

另外在升級到 5.7 的時候,順便把本來是 INT 的 primary key 都換成 BIGINT

As part of the cut-over to MySQL 5.7, we also took the opportunity to bake in a number of improvements. We converted all primary key columns from INT to BIGINT to prevent hitting MAX value.

然後系統因為舊版的 Django 沒辦法配合 MySQL 5.7,得升級到 Django 1.6 (要注意 Django 1 系列的最新版是 1.11,看起來光是升級到 1.6 勉強會動就升不上去了?):

In parallel with the MySQL 5.7 upgrade we also Upgraded Django to 1.6 due a behavioral change in MySQL 5.7 related to how transactions/commits were handled for SELECT statements. This behavior change was resulting in errors with older version of Python/Django running on MySQL 5.7

然後採用了 GitHub 家研發的 gh-ost 當作改變 schema 的工具:

In December 2019, the Eventbrite DBRE successfully implemented a table ALTER via gh-ost on one of our larger MySQL tables.

看起來主要的原因是有遇到 pt-online-schema-change 的限制 (在「GitHub 發展出來的 ALTER TABLE 方式」這邊有提到):

Eventbrite had traditionally used pt-online-schema-change (pt-osc) to ALTER MySQL tables in production. pt-osc uses MySQL triggers to move data from the original to the “duplicate” table which is a very expensive operation and can cause replication lag. Matter of fact, it had directly resulted in several outages in H1 of 2019 due to replication lag or breakage.

另外一個引入的技術是 Orchestrator,看起來是先跟 HAProxy 搭配,不過他們打算要再換到 ProxySQL

Next on the list was implementing improvements to MySQL high availability and automatic failover using Orchestrator. In February of 2020 we implemented a new HAProxy layer in front of all DB clusters and we released Orchestrator to production!

Orchestrator can successfully detect the primary failure and promote a new primary. The goal was to implement Orchestrator with HAProxy first and then eventually move to Orchestrator with ProxySQL.

然後最後題到了 Square 研發的 Shift,把 gh-ost 包裝起來變成有個 web UI 可以操作:

2021 還可以看到這類文章還蠻有趣的...

產生名次的 SQL

Percona 的「Generating Numeric Sequences in MySQL」這篇在討論產生字串序列,主要是在 MySQL 環境下,裡面看到的技巧「Session Variable Increment Within a SELECT」這組,剛好可以用在要在每個 row 裡面增加名次:

SELECT (@val := @val + 1) - 1 AS value FROM t1, (SELECT @val := 0) AS tt;

另外看到 MariaDBMySQL 8.0 系列因為有多支援各種功能,剛好也可以被拿來用,然後最後也提到了 Percona 自家出的 MySQL 8.0.20-11 將會直接有 SEQUENCE_TABLE() 可以用 (這應該才是 Percona 這篇文章的主要目的,推銷一下自家產品的新功能)。

文章收起來之後遇到可以拿出來參考用...

Amazon DocumentDB 推出相容 MongoDB 4.0 的版本

在「Amazon DocumentDB (with MongoDB compatibility) adds support for MongoDB 4.0 and transactions」這邊看到 AWSAmazon DocumentDB 上推出相容 MongoDB 4.0 的版本。

把年初在 Ptt 上寫的「Re: [請益] 選擇mongoDB或是relational database ??」這篇拿出來講一下,MongoDB 4.0 最大的改進就是 multi-document transactions 了。

不過 AWS 先前推出 DocumentDB (MongoDB) 時看到的限制,大家都猜測是用 PostgreSQL 當底層 (「AWS 推出 MongoDB 服務:Amazon DocumentDB」與「大家在猜 Amazon DocumentDB 的底層是不是 PostgreSQL...」),雖然目前還是不太清楚,但如果這個猜測屬實的話,要推出各種 transaction 的支援完全不是問題 XDDD