Tag: postgresql

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 的情境，分別是 SELECT、COPY TO 以及 ALTER TABLE ADD FOREIGN KEY (PARENT)。

是個更熟悉 PostgreSQL 的路徑？

HashiCorp 內 scale 的方法

去日本前在 Hacker News 上看到「Squeeze the hell out of the system you have」這篇，用作者的名字翻了一下 LinkedIn，看起來講的是 HashiCorp 的 SRE 事情：「Dan Slimmon」。

看的時候可以注意一下，文章裡面的觀點未必要認同，大多是他自己的看法或是想法，但裡面提到很多發生的事情，可以知道 HashiCorp 內目前搞了什麼東西。

從 LinkedIn 的資料可以看到他從 2019 就加入 HashiCorp 了，所以文章一開頭這邊講的同事應該就是 HashiCorp 的同事：

About a year ago, I raised a red flag with colleagues and managers about Postgres performance.

往下看可以看到他們有遇到 PostgreSQL 的效能問題，然後每次都是以 scale up (加大機器) 的方式解決，考慮到 HashiCorp 的產品線，我會猜應該是 Terraform Cloud 這個產品線遇到的狀況。

然後在後面提到的解法則是提到了 codebase 是 Rails，他們花了三個月的時候不斷的重複 profiling + optimizing，包括 SQL 與 PostgreSQL 的設定：

Two engineers (me and my colleague Ted – but mostly Ted) spent about 3 months working primarily on database performance issues. There was no silver bullet. We used our telemetry to identify heavy queries, dug into the (Rails) codebase to understand where they were coming from, and optimized or eliminated them. We also tuned a lot of Postgres settings.

另外一組人則是弄了 read-only replication server，把 loading 拆出去：

Two more engineers cut a path through the codebase to run certain expensive read-only queries on a replica DB. This effort bore fruit around the same time as (1), when we offloaded our single most frequent query (a SELECT triggered by polling web clients).

這兩個方法大幅降低了資料庫的 peak loading，從 90% 降到 30%：

These two efforts together reduced the maximum weekly CPU usage on the database from 90% to 30%.

可以看到都還沒用到 sharding 的技巧，目前硬體的暴力程度可以撐很久 (而且看起來是在沒有投入太多資源在 DB-related tuning 上面)，快撞到的時候也還可以先用 $$ 換效能，然後投入人力開始 profiling 找問題...

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」，可以發現基本的 text、text[] 與 varchar、varchar[] 都有支援，比較特別的是有 jsonb，看起來是對裡面的 text 欄位搜尋。

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

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

另外一個缺點是目前 AWS 的 RDS 與 GCP 的 Cloud 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 支援 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 的保證程度來設計。