Tag: sql

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 處理)

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 找問題...

AWS Aurora Xanadu？

在「Why PostgreSQL High Availability Matters and How to Achieve It (yugabyte.com)」裡面看到 AWS 也在研發類似 GCP 提供的 Spanner 的服務，計畫名稱叫做 Aurora Xanadu：「36328981」。

franckpachot

Google has Spanner. AWS is working on something similar (project Aurora Xanadu). And both have YugabyteDB in their marketplace. Those are Distributed SQL (Global ACID), not Citus. For DataWarehouse which doesn't need ACID, there are other services.

也先把這個連結備份起來，看看後面是不是直接拿這個名字來用？

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.

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

SQL:2023 的新玩意

Hacker News 上看到「SQL: 2023 is finished: Here is what's new (eisentraut.org)」這篇題到了 SQL:2023 標準的新東西，對應的原文在「SQL:2023 is finished: Here is what's new」這邊。

「UNIQUE null treatment (F292)」讓你可以決定 NULL 到底要不要算 unique，剛好跟之前寫過的「PostgreSQL 15 將可以對透過 UNIQUE 限制 NULL 的唯一性了」要做的事情一樣。

「ORDER BY in grouped table (F868)」則是針對沒有出現在 SELECT 的欄位頁可以 ORDER BY，看了一下說明，主要是在 JOIN 的時候限制住了。很明顯的 workaround 是多加上這個欄位，但就代表會增加傳回的資料量。

「GREATEST and LEAST (T054)」這個因為 MIN() 與 MAX() 已經被 aggregate function 用掉了，所以只好另外取名。

「String padding functions (T055)」與「Multi-character TRIM functions (T056)」是熟悉的語法，各家都有對應的 function 可以做，但這次就放進標準化。

「Optional string types maximum length (T081)」是 VARCHAR 可以不用指定大小了，實務上應該是還好？

「Enhanced cycle mark values (T133)」這編提到的 recursive 真的是每次用每次忘，然後 cycle 這個功能就沒看懂了...

「ANY_VALUE (T626)」看起來可以隨機取出資料，搭配 GROUP BY '' 就不用拿 ORDER BY RAND() 這種髒髒的東西出來了？

「Non-decimal integer literals (T661)」與「Underscores in numeric literals (T662)」都是讓數字更好讀以及操作。

後面講了很多 JSON 功能，看起來是 SQL:2016 有先納入一些，但 SQL:2023 補的更完整了。

然後有 Graph 相關的標準也被定義進 SQL:2023，原文介紹的也不是很多，看起來是要跨足過來？

MariaDB 以及 Trac 在 arm64 上的安裝

把一台本來跑在 Vultr 上的機器搬到 AWS 的 us-east-1 上面，除了剛好把 Ubuntu 18.04 換成 Ubuntu 22.04 外，也把本來用 x86-64 架構的機器換成用 ARM 的 t4g.micro (都是 1GB RAM)。

就效能上來說，t4g 機器的效能很不錯，這兩年 blog 跑的也都還算順，先前公司用起來感覺也很好，然後價錢更便宜，另外加上 AWS 的三年 RI 折扣大約是 4 折的價錢，算是會想要換的主因。

在確認應用跑得起來後，買三年 RI 是 $87.15/3y，所以機器本身的費用大約是 $29.05/y，就算加上 8GB 的 EBS (gp3) 空間費用，整體比本來在 Vultr 的 $6/mo 低不少。

上面跑的是我自己的 Trac，想搬到 AWS 上一陣子了，但有幾個不確定的因素，所以連假期間才有空多花一些時間確認。

第一個是 MySQL 的部份，我自己習慣用 Percona Server 的版本，但目前還沒有 arm64 的套件可以直接裝，要用的話就得自己編以及升級。

在 2021 年的時候 blog 搬到 AWS 的時候就遇過了，本來以為這次有機會，但看了一下還是沒支援，所以還是得用 MariaDB。

第二個是 Trac 1.4 只能跑在 Python 2.7 上 (mailing list 上有在討論轉到 Python 3 的事情，但看起來官方的動力也不大...)，這在 18.04 的時代是沒什麼問題，但 22.04 下面不知道會爛掉多少東西。

所以只能繼續用 pyenv 扛著，但已經有預期會遇到問題，加上這次又從 MySQL 轉到 MariaDB，應該也會有些地雷...

所以跳下去後遇到的問題就跟上面提到的類似，分成兩塊。

在 MariaDB 這邊第一個遇到問題是，雖然官方有提供 APT server，但沒有在 HTTPS server 上放新的 public key，所以一定得從 key server 撈。

但 GnuPG 就是沒有直接從 key server 下載變成檔案的功能，一定要先塞到 keystore 裡面再 export 出來，就覺得很...

所以就冒出利用 mktemp -d 在 /tmp 下產生暫存目錄這樣的寫法，讓 GnuPG 把 keystore 放進去，這樣至少在重開機後就會消失：

export GNUPGHOME=$(mktemp -d); gpg --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0x177F4010FE56CA3336300305F1656F24C74CD1D8; gpg --export 0x177F4010FE56CA3336300305F1656F24C74CD1D8 | sudo tee /etc/apt/trusted.gpg.d/mariadb.gpg > /dev/null; unset GNUPGHOME

這邊為了安全性，還得把官方提供的 0xF1656F24C74CD1D8 換成 0x177F4010FE56CA3336300305F1656F24C74CD1D8。

另外就是整理 MariaDB 需要的 my.cnf 內容，我是拿 Percona Server 5.7 的設定檔來改，只刪掉了跟 GTID 相關的設定就會動了。

而其他 MariaDB 遇到的問題主要是設計改變的問題，在 wiki 上有提到。

接下來是 Trac 1.4 的問題，本來的安裝是用 libmysqlclient-dev，然後再安裝 mysql-python：

sudo apt install -y libmysqlclient-dev
pip install mysql-python PyMySQL Pygments Trac

但單純把 libmysqlclient-dev 換成 libmariadb-dev 後，mysql-python 還是編不動，照著錯誤訊息試著 workaround (像是試著把 /usr/bin/mysql_config 指到 /usr/bin/mariadb_config) 半天還是不過，最後找資料發現要改用 mysqlclient：

sudo apt install -y libmariadb-dev
pip install mysqlclient PyMySQL Pygments Trac

搞定後後續就一路看錯誤訊息解就可以了...

Pony ORM

在 Simon Willison 的 blog 上看到的東西：「Python’s “Disappointing” Superpowers」，裡面提到的原文是「Python’s “Disappointing” Superpowers」這篇，在講 Python 的工具。

雖然是說「disappointing」，但實際上是反義，在原文裡面提到了很多特別的工具，其中 Pony ORM 算是我覺得最有趣的了，他的寫法就非常的 Python：

select(c for c in Customer if sum(c.orders.price) > 1000)

也可以用 lambda 的形式來寫：

Customer.select(lambda c: sum(c.orders.total_price) > 1000)

這樣會產生出對應的 SQL：

SELECT "c"."id"
FROM "customer" "c"
  LEFT JOIN "order" "order-1"
    ON "c"."id" = "order-1"."customer"
GROUP BY "c"."id"
HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000

不會產生 syntax error 的原因是因為他直接解讀 bytecode 分析，產生出對應的 SQL query：

A normal understanding of generator expressions suggests that the select function is consuming a generator. But that couldn’t explain the behaviour here. Instead, it actually introspects the frame object of the calling code, then decompiles the byte code of the generator expression object it finds, and builds a Query based on the AST objects.

用這樣的設計來達到語法的自由度。

看了一下也有一些 integration，像是 Flask 的「Integration with flask」與 FastAPI 的「Integration with FastAPI」。

不過應該是先看看，目前 Python 上用的主力還是 Django，有自己的 ORM 架構...

SQLite 官方自己下來搞 WASM/JS 計畫

先前在「把 SQLite 的 VFS 掛上 WebTorrent 的 PoC Demo」有提過 sql.js 這個專案，把 SQLite 移植到網頁上，這些都算是非官方的社群弄出來的專案。

現在官方直接跳下來玩，宣佈自己也要搞 WASM/JS 了：「sqlite3 wasm docs: About the sqlite3 WASM/JS Subproject」。

Folks have been building sqlite3 for the web since as far back as 2012 but this subproject is the first effort "officially" associated with the SQLite project, created with the goal of making WASM builds of the library first-class members of the family of supported SQLite deliverables.

但不太確定 D. Richard Hipp 的想法，官方支援 WASM/JS 的目的會是什麼？放給社群繼續發展有什麼問題嗎...

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 數字可以看，再來等著看...